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: