Oracle Object Detail ...
Oracle objects info ..
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300
col owner for a15
col object_type for a15
col object_name for a30
col temporary for a10
col generated for a20
select owner,
object_name,
-- subobject_name,
-- object_id,
-- data_object_id,
object_type,
created,
last_ddl_time,
-- timestamp,
status,
temporary,
generated,
secondary,
-- namespace,
edition_name
from dba_objects
where upper(object_name) like upper('%&OBJECT_NAME%')
order by owner, object_name;
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
col owner for a15
col object_type for a15
col object_name for a30
col temporary for a10
col generated for a20
select owner,
object_name,
-- subobject_name,
-- object_id,
-- data_object_id,
object_type,
created,
last_ddl_time,
-- timestamp,
status,
temporary,
generated,
secondary,
-- namespace,
edition_name
from dba_objects
where 1=1
and last_ddl_time > sysdate -1 ---- last one day
order by owner, object_name;
compile the objects ...
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set pagesize 300 linesize 300
col info for a80
select
decode( object_type, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' ) info ,
'-- ', status,created,last_ddl_time
from dba_objects a
where 1=1
and STATUS = 'INVALID'
and OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE','TRIGGER', 'VIEW' )
and owner not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by object_type,object_name;
Object with TableSpace
set pagesize 300 linesize 150
col owner for a25
col object_name for a30
col SEGMENT_NAME for a25
select object_name,o.owner,tablespace_name ,o.object_type, o.status,count(*) from dba_objects o,dba_segments s
where 1=1
-- and STATUS='INVALID'
--and o.OWNER='OWNER'
and s.segment_name = o.object_name
and s.segment_type = o.object_type
and s.owner = o.owner
and upper(object_name) like upper('%&OBJECT_NAME%')
group by object_name,o.owner,o.object_type, o.status ,tablespace_name
order by owner
/
OBJECT_NAME OWNER TABLESPACE_NAME OBJECT_TYPE STATUS COUNT(*)
------------------------------ ------------------------- ------------------------------ ----------------------- ------- ----------
EMP ANUJ TEST TABLE VALID 1
EMP ANUJ1 USERS TABLE VALID 1
PK_EMP ANUJ1 USERS INDEX VALID 1
EMP ANUJ10 USERS TABLE VALID 1
PK_EMP ANUJ10 USERS INDEX VALID 1
EMP ANUJ3 USERS TABLE VALID 1
set linesize 500 pagesize 300
col OWNER for a20
col OBJECT_NAME for a20
col stime for a27
select u.name as owner, o.name as object_name,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
39, 'LOB PARTITION', 40, 'LOB SUBPARTITION',
43, 'DIMENSION',
44, 'CONTEXT', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA', 'UNKNOWN') as type,
o.ctime, o.mtime,
to_char(o.stime, 'DD-MM-YYYY HH24:MI:SS') stime,
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID') as status
from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and (o.type# not in (1 , 10) or
(o.type# = 1 and 1 = (select 1 from sys.ind$ i
where i.obj# = o.obj#
and i.type# in (1, 2, 3, 4, 6, 7, 9))))
--and O.NAME like '%Queue%'
;
