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%'
;
object search from Web
====
set linesize 500 pagesize 300
undef object_string
define object_string='EMP'
col oname format a42 head 'Object Name'
col suboname format a30 head 'Sub Object Name'
col objtype format a10 head 'Obj Type' trunc
col sta format a03 head 'Sta' trunc
col syn format a40 head 'Synonym'
col created_pdt format a14 head 'Creation PDT'
col lastddl_pdt format a14 head 'Last DDL PDT'
col tlastana_pdt format a14 head 'Table|Last Ana PDT'
col ilastana_pdt format a14 head 'Index|Last Ana PDT'
col objid format 99999999 head 'Object Id'
col ar format a02 head '=>'
col cf format a01 head 'x'
col df format a01 head 'x'
col tf format a01 head 'x'
col if format a01 head 'x'
col sep format a01 head '|'
select
o.con_id
,decode(s.synonym_name,null,' ',s.owner||'.'||s.synonym_name) syn
,decode(s.synonym_name,null,' ','=>') ar
,o.owner||'.'||o.object_name oname
,o.object_type objtype
--,o.subobject_name suboname
,o.status sta
,o.object_id objid
,'|' sep
,to_char(new_time(o.created,'GMT','PDT'),'YY/MM/DD HH24:MI') created_pdt
,decode(sign(sysdate-(o.created+2/24)),-1,'x',' ') cf
,'|' sep
,to_char(new_time(o.last_ddl_time,'GMT','PDT'),'YY/MM/DD HH24:MI') lastddl_pdt
,decode(sign(sysdate-(o.last_ddl_time+2/24)),-1,'x',' ') df
,'|' sep
,to_char(new_time(t.last_analyzed,'GMT','PDT'),'YY/MM/DD HH24:MI') tlastana_pdt
,decode(sign(sysdate-(t.last_analyzed+2/24)),-1,'x',' ') tf
,'|' sep
,to_char(new_time(i.last_analyzed,'GMT','PDT'),'YY/MM/DD HH24:MI') ilastana_pdt
,decode(sign(sysdate-(i.last_analyzed+2/24)),-1,'x',' ') if
,'|' sep
from cdb_objects o
,cdb_synonyms s
,cdb_tables t
,cdb_indexes i
where 1=1
-- and (o.object_name like upper('%&&object_string%') or s.synonym_name like upper('%&&object_string%'))
and (o.object_name like upper('&&object_string%') or s.synonym_name like upper('&&object_string%'))
and o.object_type not in ('SYNONYM')
and o.object_type not like '%PARTITION%'
--and o.owner not in ('SYS','SYSTEM')
and o.owner = s.table_owner (+)
and o.object_name = s.table_name (+)
and o.owner = t.owner (+)
and o.object_name = t.table_name (+)
and o.owner = i.owner (+)
and o.object_name = i.index_name (+)
order by o.owner||'.'||o.object_name
;
col oname format a40
col subobj format a30
col objtype format a10 trunc
col sta format a03 trunc
col syn format a20
col created format a15
col lastddl format a15
col objid format 9999999999
col ar format a02 head '=>'
break on syn
select decode(s.synonym_name,null,' ',s.owner||'.'||s.synonym_name) syn
,decode(s.synonym_name,null,' ','=>') ar
,o.owner||'.'||o.object_name oname
,subobject_name subobj
,object_type objtype
,status sta
,object_id objid
,to_char(created,'dd-mm-YY HH24:MI:SS') created
,to_char(last_ddl_time,'dd-mm-YY HH24:MI:SS') lastddl
from cdb_objects o
,cdb_synonyms s
where 1=1
-- and (o.object_name like upper('%&&object_string%') or s.synonym_name like upper('%&&object_string%'))
and (o.object_name like upper('&&object_string%') or s.synonym_name like upper('&&object_string%'))
and o.owner = s.table_owner (+)
and o.object_name = s.table_name (+)
and object_type not like '%PARTITION%'
and object_type not in ('SYNONYM')
order by o.owner||'.'||o.object_name
,s.owner||'.'||s.synonym_name
,object_type
;
ynonym => Object Name SUBOBJ Obj Type Sta Object Id CREATED LASTDDL
-------------------------------------------- -- ---------------------------------------- ------------------------------ ---------- --- ----------- --------------- ---------------
ANUJ.EMP TABLE VAL 224527 27-02-18 06:54: 27-02-18 07:19:
27 16
ANUJ1.EMP TABLE VAL 201415 09-01-18 15:48: 09-01-18 15:49:
44 23
ANUJ10.EMP TABLE VAL 202855 12-01-18 13:13: 12-01-18 13:14:
