garden2.jpg
All you want to know about a given database object.

Oracle Dynamic Views relating to objects.

Find Oracle database objects.

Looking for a given database object.

Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.

1) Find object detail.

select OBJECT_ID,OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS,OBJECT_ID,

to_char(created,’hh24:mi dd-mon-yy’) created,

Timestamp,

to_char(LAST_DDL_TIME,’hh24:mi dd-mon-yy’) last_ddl

from dba_objects

where OBJECT_NAME like upper(‘&name’);

2) When was it analyzed?

select OWNER,TABLE_NAME,

to_char(LAST_ANALYZED,’hh24:mi dd-mon-yy’) LAST_ANALYZED,

MONITORING,GLOBAL_STATS,SAMPLE_SIZE,NUM_ROWS

from dba_tables

where OWNER = ‘&OWNER’

and TABLE_NAME = ‘&OBJECT_NAME’;

3) If object is a table, lookup indexes.

select c.index_name

,c.table_name,c.column_name,

c.column_position,

i.INDEX_TYPE,

to_char(i.LAST_ANALYZED,’hh24:mi dd-mon-yy’) LAST_ANALYZED,

to_char(dbms_metadata.get_ddl(‘INDEX’,i.index_name,i.OWNER)) ddl

from dba_ind_columns c, dba_indexes i

where c.table_name = upper(‘&OBJECT_NAME’)

and i.INDEX_NAME=c.index_name

and i.owner=c.INDEX_OWNER

and i.TABLE_OWNER=c.TABLE_OWNER

and c.INDEX_OWNER = upper(‘&OWNER’)

order by 1,4;

4) Associated Constraints.

select a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,

b.CONSTRAINT_TYPE T,

b.DELETE_RULE,b.CONSTRAINT_NAME,b.STATUS,

b.SEARCH_CONDITION

from dba_cons_columns a, dba_constraints b

where a.OWNER = upper(‘&OWNER’)

and a.table_name = upper(‘&OBJECT_NAME’)

and a.table_name=b.table_name

and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME

and a.owner=b.owner

order by 5,3,1,2;

5) Is parent to.

select distinct p.TABLE_NAME parent,c.R_CONSTRAINT_NAME parent_cons,c.TABLE_NAME child,

c.CONSTRAINT_NAME child_cons,c.status,col.COLUMN_NAME child_col

from dba_constraints p,dba_constraints c, dba_cons_columns col

where p.CONSTRAINT_NAME=c.R_CONSTRAINT_NAME

and p.owner=c.owner

and p.owner = upper(‘&OWNER’)

and p.TABLE_NAME = upper(‘&OBJECT_NAME’)

and col.CONSTRAINT_NAME=c.CONSTRAINT_NAME

and col.owner=p.owner;

6) Is child of.

select distinct p.TABLE_NAME parent,c.R_CONSTRAINT_NAME parent_cons,c.TABLE_NAME child,

c.CONSTRAINT_NAME child_cons,c.status,col.COLUMN_NAME child_col

from dba_constraints p,dba_constraints c, dba_cons_columns col

where p.CONSTRAINT_NAME=c.R_CONSTRAINT_NAME

and p.owner=c.owner

and c.owner = upper(‘&OWNER’)

and c.TABLE_NAME = upper(‘&OBJECT_NAME’)

and col.CONSTRAINT_NAME=c.CONSTRAINT_NAME

and col.owner=p.owner;

7) It is currently locked?

select session_id,

oracle_username locker,

o.OBJECT_ID,object_name,object_type,process,DECODE (l.locked_mode

, 0, ‘None’

, 1, ‘Null’

, 2, ‘Row-S’

, 3, ‘Row-X’

, 4, ‘Share’

, 5, ‘S/Row-X’

, 6, ‘Exclusive’) lock_mode

from v$locked_object l,dba_objects o

where l.object_id=o.object_id

and o.owner = upper(‘&OWNER’)

and object_name = upper(‘&OBJECT_NAME’)

order by 2;

8) Storage definition.

select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,INITIAL_EXTENT,

NEXT_EXTENT,MAX_EXTENTS,BUFFER_POOL,sum(EXTENTS),round(sum(BYTES)/1048576) SIZE_MB

from dba_segments

where OWNER = upper(‘&OWNER’)

and SEGMENT_NAME = upper(‘&OBJECT_NAME’)

group by SEGMENT_NAME,SEGMENT_TYPE, TABLESPACE_NAME, INITIAL_EXTENT,NEXT_EXTENT, MAX_EXTENTS, BUFFER_POOL;

9) Is it cached?

select * from v$db_object_cache

where OWNER = upper(‘&OWNER’)

and NAME = upper(‘&OBJECT_NAME’);

10) Object DDL.

select dbms_metadata.get_ddl(i.OBJECT_TYPE,i.OBJECT_NAME,i.OWNER) ddl

from dba_objects i

where i.object_name = upper(‘&OBJECT_NAME’)

and i.OBJECT_ID = &OBJECT_ID

and i.owner = upper(‘&OWNER’);
11) Describe or column structure.

select owner,table_name,column_name,data_type,data_length,nullable,density

from dba_tab_columns

where owner= upper(‘&OWNER’)

and TABLE_NAME= upper(‘&OBJECT_NAME’)

order by column_name;

12) Sessions currently using it.

select s.sid,s.username,s.status,

sa.sql_text txt

from v$session s,

v$sqlarea sa

where s.sql_address=sa.address(+)

and s.sql_hashvalue=sa.hash_value(+)

and sa.sql_text like ‘%’||upper(‘&OBJECT_NAME’)||’%';

13) Dependency. (This could run for over 30 minutes for some objects).

SELECT object_name, object_type, owner, status, last_ddl_time

FROM dba_objects

WHERE ( object_name, object_type ) IN ( SELECT referenced_name, referenced_type

FROM dba_dependencies

WHERE OWNER= upper(‘&OWNER’)

and name = upper(‘&OBJECT_NAME’)

and TYPE in (‘FUNCTION’, ‘MATERIALIZED VIEW’, ‘PACKAGE’, ‘PACKAGE BODY’, ‘PROCEDURE’,

‘TABLE’, ‘VIEW’)

)

order by object_type;.

Latest Blog Items

email

Thank you for your interest in idbasolutions.

Contact us using this email: contact.us@idbasolutions.com