FilesExpand file tree

 main

/

find_objects.sql

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

63 lines (59 loc) · 2.22 KB

 main

/

find_objects.sql

Copy path

Top

File metadata and controls

• Code
• Blame

63 lines (59 loc) · 2.22 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

--

-- List objects matching specified name pattern (except PARTITIONS)

-- Objects which were affected in the last 2 hours will be flagged

--

@plusenv

undef object_string

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 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 dba_objects o

,dba_synonyms s

,dba_tables t

,dba_indexes i

where (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

;