All you want to know about a given database object.
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;
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.
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;.