SQL & PL/SQL

1. The following code either hangs or takes a second less than forever to run. Why? Does this work in other databases?

SELECT
tc.column_name,
tc.data_type,
tc.data_length
FROM
all_cons_columns cc,
all_constraints c,
all_tab_columns tc
WHERE c.owner = USER
AND c.table_name = 'MY_TABLE_NAME'
AND c.constraint_type = 'P'
AND c.owner = cc.owner
AND c.constraint_name = cc.constraint_name
AND cc.owner = tc.owner
AND cc.table_name = tc.table_name
AND cc.column_name = tc.column_name
ORDER BY tc.column_id;

NOTE: To try this on your database, change the table_name to any table_name (ALL CAPS) that exists on your schema.

The following code is the work around code.

SELECT
tc.column_name,
tc.data_type,
tc.data_length
FROM all_tab_columns tc
WHERE (owner,table_name,column_name) IN
(SELECT
cc.owner,
cc.table_name,
cc.column_name
FROM
all_cons_columns cc,
all_constraints c
WHERE c.owner = USER
AND c.table_name = 'MY_TABLE_NAME'
AND c.constraint_type = 'P'
AND c.owner = cc.owner
AND c.constraint_name = cc.constraint_name)
ORDER BY tc.column_id;


2. When doing an explain plan that involves tables all_cons_columns, all_constraints, or all_tab_columns such as the two examples above, I get the following error. Why is this if I have SELECT ANY TABLE privilege?

all_tab_columns tc
*
ERROR at line 11:
ORA-01039: insufficient privileges on underlying objects of the view


3. Why do some data dictionary tables have a COLUMN_NAME length of 4000 instead of 30?

SQL> desc all_cons_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
POSITION NUMBER