ORACLE synonym table describe constraints

Question

I would like to see the constraints defined in a synonym of a table.

When a type

SQL> DESCRIBE table_name;

It shows me only the number of each column, the not null flag and the size. How can I know all the constraints for each column? How can I know also if the synonyms are either public or private?

Thanks, Alvaro.

SOLUTION

With @Aramillo helps and adapting it to my needs, I figure out how to do it. I want to see the Constraints defined for a table but not according to the USER. There are two options, with ALL_CONSTRAINTS and ALL_CONS_COLUMNS.

ALL_CONSTRAINTS has not the column COLUMN_NAME in order to know the column's name which the constraints is set to. [ALL_CONS_COLUMNS] has.

Query:

SELECT * FROM all_cons_columns WHERE table_name = 'table_name';

About the private or public synonym, as the links above say,

ALL_CONSTRAINTS/ALL_CONS_COLUMNS describes constraint definitions on tables accessible to the current user.

So I guess if it shows data either the synonym is public or you are the correct user for the private synonym.

score 2 · Accepted Answer · answered Sep 22 '14 at 16:02

This query gives you all constraints of a synonym with name REGION_SYN_P for the current user:

SELECT constraint_name,
       c.table_name,
       synonym_name,
       S.OWNER
  FROM user_constraints C, all_SYNONYMS S
 WHERE     C.TABLE_NAME = S.TABLE_NAME
       AND S.TABLE_OWNER = C.OWNER
       AND S.Synonym_name = 'REGION_SYN_P'

If you want to know if is public or not, you just have to check owner column, in case it's a public synonym the owner will be PUBLIC.

You can use all_constraints instead user_constraints if you need query the constraints in other schemas. I hope this helps.

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

the following is for Oracle. Query the so called static data dictionary: oracle constrains

work it out with DBA_CONSTRAINTS USER_CONSTRAINTS or DBA_CONS_COLUMNS USER_CONS_COLUMNS

stack link for Oracle

edited May 23 '17 at 11:49

Community

1
1

answered Sep 22 '14 at 14:42

ninjabber

341
1
7

1

There is no `information_schema` in Oracle. And there is no `use` statement. – a_horse_with_no_name Sep 22 '14 at 14:47
When I type INFORMATION_SCHEMA:`SQL> use INFORMATION_SCHEMA; unknown command beginning "use INFORM..." - rest of line ignored.` as @a_horse_with_no_name says, it seems that there is not such table in Oracle (neither `use` statement) – alvgarvilla Sep 22 '14 at 14:52
and instead of `use` what is used with Oracle? – alvgarvilla Sep 22 '14 at 15:31
click on the link I provided (oracle constrains) but you will have to understand the data dictionary which is the equivalent of information_schema – ninjabber Sep 22 '14 at 15:32
-1 for not getting rid of the MySQL specific code in a question tagged Oracle – Frank Schmitt Sep 23 '14 at 10:43
as requested. you can keep your -1. I'm not gonna cry ;) – ninjabber Sep 23 '14 at 17:00

ORACLE synonym table describe constraints

2 Answers2