Oralce Database Health Check (Performance)

List Contents

1. 數據庫概要
2. 參數文件(是spfile還是pfile)
3. 非默認的參數
4. 控制文件及其狀態(tài)
5. 表空間及數據文件
6. 重做日志文件信息
7. 內存分配概況
8. Library Cache Reload Ratio(<1%)
9. Data Dictionary Miss Ratio(<15%)
10. 共享池使用概況
11. 共享池建議
12. DB Buffer Cache(Default) Hit Ratio(>90%)
13. DB Buffer Cache Advice
14. 磁盤(pán)排序(<5%)
15. Log Buffer latch Contention(<1%)
16. 表空間狀態(tài)及其大小使用情況
17. 數據文件狀態(tài)及其大小使用情況
18. 不使用臨時(shí)文件的臨時(shí)表空間
19. 無(wú)效的數據文件(offline)
20. 處于恢復模式的文件
21. 含有50個(gè)以上的Extent且30%以上碎片的表空間
22. 表空間上的I/O分布
23. 數據文件上的I/O分布
24. Next Extent 相對于段當前已分配字節過(guò)大(>=2倍)或過(guò)小(<10%)的Segments
25. Max Extents(>1)已經(jīng)有90%被使用了的Segments
26. 已經(jīng)分配超過(guò)100 Extents的Segments
27. 因表空間空間不夠將導致不能擴展的Objects
28. 沒(méi)有主鍵的非系統表
29. 沒(méi)有索引的外鍵
30. 建有6個(gè)以上索引的非系統表
31. 指向對象不存在的Public同義詞
32. 指向對象不存在的非Public同義詞
33. 沒(méi)有授予給任何角色和用戶(hù)的角色
34. 將System表空間作為臨時(shí)表空間的用戶(hù)(除Sys外)
35. 將System表空間作為默認表空間的用戶(hù)(除Sys外)
36. 沒(méi)有授予給任何用戶(hù)的profiles
37. 沒(méi)有和Package相關(guān)聯(lián)的Package Body
38. 被Disabled的約束
39. 被Disabled的觸發(fā)器
40. Invalid Objects
41. 執行失敗或中斷的Jobs
42. 當前未執行且下一執行日期已經(jīng)過(guò)去的Jobs
43. 含有未分析的非系統表的Schemas
44. 含有未分析的非系統分區表的Schemas
45. 含有未分析的非系統索引的Schemas
46. 含有未分析的非系統分區索引的Schemas
47. 死鎖檢測
48. top I/O Wait
49. top 10 wait
50. Top 10 bad SQL
51. Top most expensive SQL (Buffer Gets by Executions)
52. Top most expensive SQL (Physical Reads by Executions)
53. Top most expensive SQL (Rows Processed by Executions)
54. Top most expensive SQL (Buffer Gets vs Rows Processed)

1. 數據庫概要

DB Name	Global Name	Host Name	Instance Name	Restricted Mode	Archive Log Mode
ORCL10G	ORCL10G	CHINA-1257BBDF2	orcl10g	NO	NOARCHIVELOG

1 rows selected.

2. 參數文件(是spfile還是pfile)

Parameter_File
F:\ORACLE\10.1.0\DATABASE\SPFILEORCL10G.ORA

1 rows selected.

3. 非默認的參數

NAME	pvalue
background_dump_dest	F:\ORACLE\ADMIN\ORCL10G\BDUMP
compatible	10.1.0.2.0
control_files	F:\ORACLE\ORADATA\ORCL10G\CONTROL01.CTL, F:\ORACLE\ORADATA\ORCL10G\CONTROL02.CTL, F:\ORACLE\ORADATA\ORCL10G\CONTROL03.CTL
core_dump_dest	F:\ORACLE\ADMIN\ORCL10G\CDUMP
db_block_size	8192
db_cache_size	4194304
db_domain
db_file_multiblock_read_count	8
db_name	orcl10g
java_pool_size	0
job_queue_processes	10
large_pool_size	4194304
nls_language	SIMPLIFIED CHINESE
nls_territory	CHINA
open_cursors	50
pga_aggregate_target	10485760
processes	20
remote_login_passwordfile	EXCLUSIVE
shared_pool_size	33554432
sort_area_size	65536
undo_management	AUTO
undo_tablespace	UNDOTBS1
user_dump_dest	F:\ORACLE\ADMIN\ORCL10G\UDUMP

23 rows selected.

4. 控制文件及其狀態(tài)

NAME	STATUS
F:\ORACLE\ORADATA\ORCL10G\CONTROL01.CTL
F:\ORACLE\ORADATA\ORCL10G\CONTROL02.CTL
F:\ORACLE\ORADATA\ORCL10G\CONTROL03.CTL

3 rows selected.

5. 表空間及數據文件

TABLESPACE_NAME	FILE_NAME	Total Size(MB)	Auto
SYSAUX	F:\ORACLE\ORADATA\ORCL10G\SYSAUX01.DBF	50	YES
SYSTEM	F:\ORACLE\ORADATA\ORCL10G\SYSTEM01.DBF	250	YES
UNDOTBS1	F:\ORACLE\ORADATA\ORCL10G\UNDOTBS01.DBF	100	YES
USERS	F:\ORACLE\ORADATA\ORCL10G\USERS01.DBF	5	YES

4 rows selected.

6. 重做日志文件信息

GROUP#	Redo File	TYPE	STATUS	Size(MB)
1	F:\ORACLE\ORADATA\ORCL10G\REDO01.LOG	ONLINE	CURRENT	10
2	F:\ORACLE\ORADATA\ORCL10G\REDO02.LOG	ONLINE	INACTIVE	10
3	F:\ORACLE\ORADATA\ORCL10G\REDO03.LOG	ONLINE	INACTIVE	10

3 rows selected.

7. 內存分配概況

NAME	value(Byte)
Fixed Size	787388
Variable Size	40893508
Database Buffers	4194304
Redo Buffers	262144
lock_sga	FALSE
large_pool_size	4194304
java_pool_size	0

7 rows selected.

8. Library Cache Reload Ratio(<1%)

LC_Reload_Ratio%
1.0126

1 rows selected.

9. Data Dictionary Miss Ratio(<15%)

DC_Miss_Ratio%
23.667

1 rows selected.

10. 共享池使用概況

Used(MB)	Size(MB)	Avail(MB)	Used(%)
6.37	32	25.63	19.91

1 rows selected.

11. 共享池建議

Shared Pool Size(estimate)	Factor	Libarary Cache Size	time Saved
20	0.625	4	7
24	0.75	5	7
28	0.875	5	7
32	1	5	7
36	1.125	5	7
40	1.25	5	7
44	1.375	5	7
48	1.5	5	7
52	1.625	5	7
56	1.75	5	7
60	1.875	5	7
64	2	5	7

12 rows selected.

12. DB Buffer Cache(Default) Hit Ratio(>90%)

BC_Hit _Ratio
87.6955

1 rows selected.

13. DB Buffer Cache Advice

Pool Name	BLOCK_SIZE	Buffer Size	Factor	Phy_Read_Factor	ESTD_PHY_READS
DEFAULT	8192	4	1	1	1820
DEFAULT	8192	8	2	0.6078	1106

2 rows selected.

14. 磁盤(pán)排序(<5%)

Sort(Disk)	Sort(Memory)	Disk_Sort_Ratio％
0	1357	0

1 rows selected.

15. Log Buffer latch Contention(<1%)

Redo Name	GETS	MISSES	IMMEDIATE_GETS	IMMEDIATE_MISSES	Miss_Ratio％	Immediate Misses Ratio%
redo copy	10	0	160	0	0	0
redo allocation	260	0	160	0	0	0

2 rows selected.

16. 表空間狀態(tài)及其大小使用情況

Name	Status	Type	Size (MB)	Used (MB)	Used％
SYSAUX	ONLINE	PERMANENT	50.000	47.750	95.50
SYSTEM	ONLINE	PERMANENT	250.000	206.625	82.65
UNDOTBS1	ONLINE	UNDO	100.000	4.750	4.75
USERS	ONLINE	PERMANENT	5.000	0.063	1.25
TEMP	ONLINE	TEMPORARY	0.000	0.000	0.00

5 rows selected.

17. 數據文件狀態(tài)及其大小使用情況

TableSpace Name	File Name	Status	Auto	Size (MB)	Used (MB)	Used %
SYSAUX	F:\ORACLE\ORADATA\ORCL10G\SYSAUX01.DBF	AVAILABLE	YES	50.000	47.750	95.50
SYSTEM	F:\ORACLE\ORADATA\ORCL10G\SYSTEM01.DBF	AVAILABLE	YES	250.000	206.625	82.65
UNDOTBS1	F:\ORACLE\ORADATA\ORCL10G\UNDOTBS01.DBF	AVAILABLE	YES	100.000	4.750	4.75
USERS	F:\ORACLE\ORADATA\ORCL10G\USERS01.DBF	AVAILABLE	YES	5.000	0.063	1.25

4 rows selected.

18. 不使用臨時(shí)文件的臨時(shí)表空間

TABLESPACE_NAME	CONTENTS

0 rows selected.

19. 無(wú)效的數據文件(offline)

TABLESPACE_NAME	FILE_NAME	STATUS

0 rows selected.

20. 處于恢復模式的文件

TABLESPACE_NAME	FILE_NAME

0 rows selected.

21. 含有50個(gè)以上的Extent且30%以上碎片的表空間

TABLESPACE_NAME	PCT_FRAGMENTED	SEGMENTS	HOLES

0 rows selected.

22. 表空間上的I/O分布

TS_NAME	FILE_NAME	PHY_READS	PHY_BLOCKREADS	PHY_WRITES	PHY_BLOCKWRITES
SYSTEM	F:\ORACLE\ORADATA\ORCL10G\SYSTEM01.DBF	1530	4011	69	77
SYSAUX	F:\ORACLE\ORADATA\ORCL10G\SYSAUX01.DBF	62	98	22	23
UNDOTBS1	F:\ORACLE\ORADATA\ORCL10G\UNDOTBS01.DBF	43	43	35	36
USERS	F:\ORACLE\ORADATA\ORCL10G\USERS01.DBF	7	7	2	2

4 rows selected.

23. 數據文件上的I/O分布

Table Space	File Name	Phys Rds	% Phys Rds	Phys Wrts	% Phys Wrts
SYSTEM	F:\ORACLE\ORADATA\ORCL10G\SYSTEM01.DBF	1532	93.19	69	53.91
UNDOTBS1	F:\ORACLE\ORADATA\ORCL10G\UNDOTBS01.DBF	43	2.62	35	27.34
SYSAUX	F:\ORACLE\ORADATA\ORCL10G\SYSAUX01.DBF	62	3.77	22	17.19
USERS	F:\ORACLE\ORADATA\ORCL10G\USERS01.DBF	7	0.43	2	1.56

4 rows selected.

24. Next Extent 相對于段當前已分配字節過(guò)大(>=2倍)或過(guò)小(<10%)的Segments

Type	OWNER	SEGMENT_NAME	BYTES	NEXT_EXTENT	Percent(Next/Bytes)

0 rows selected.

25. Max Extents(>1)已經(jīng)有90%被使用了的Segments

SEGMENT_TYPE	OWNER	SEGMENT_NAME	TABLESPACE_NAME	PARTITION_NAME	Size(MB)	EXTENTS	MAX_EXTENTS

0 rows selected.

26. 已經(jīng)分配超過(guò)100 Extents的Segments

SEGMENT_TYPE	OWNER	SEGMENT_NAME	EXTENTS	PARTITION_NAME

0 rows selected.

27. 因表空間空間不夠將導致不能擴展的Objects

TABLESPACE_NAME	OWNER	Segment Name	EXTENTS	NEXT_EXTENT_KB	TS_FREE_KB	TS_GROWTH_MB

0 rows selected.

28. 沒(méi)有主鍵的非系統表

OWNER	TABLE_NAME
WMSYS	SYS_IOT_OVER_9255
WMSYS	WM$ADT_FUNC_TABLE
WMSYS	WM$BATCH_COMPRESSIBLE_TABLES
WMSYS	WM$CONS_COLUMNS
WMSYS	WM$LOCKROWS_INFO
WMSYS	WM$MW_TABLE
WMSYS	WM$NEXTVER_TABLE
WMSYS	WM$REPLICATION_DETAILS_TABLE
WMSYS	WM$RIC_LOCKING_TABLE
WMSYS	WM$TMP_DBA_CONSTRAINTS
WMSYS	WM$WORKSPACE_PRIV_TABLE

11 rows selected.

29. 沒(méi)有索引的外鍵

OWNER	TABLE_NAME	CONSTRAINT_NAME	COLUMN_NAME

0 rows selected.

30. 建有6個(gè)以上索引的非系統表

TABLE_OWNER	TABLE_NAME	INDEX_COUNT

0 rows selected.

31. 指向對象不存在的Public同義詞

SYNONYM_NAME	TABLE_OWNER	TABLE_NAME
GV$TRANSPORTABLE_PLATFORM	SYS	GV$_TRANSPORTABLE_PLATFORM

1 rows selected.

32. 指向對象不存在的非Public同義詞

OWNER	SYNONYM_NAME	TABLE_OWNER	TABLE_NAME
SYS	DEF$_SCHEDULE	SYSTEM	DEF$_SCHEDULE

1 rows selected.

33. 沒(méi)有授予給任何角色和用戶(hù)的角色

ROLE

0 rows selected.

34. 將System表空間作為臨時(shí)表空間的用戶(hù)(除Sys外)

USERNAME

0 rows selected.

35. 將System表空間作為默認表空間的用戶(hù)(除Sys外)

USERNAME
SYSTEM
OUTLN

2 rows selected.

36. 沒(méi)有授予給任何用戶(hù)的profiles

PROFILE

0 rows selected.

37. 沒(méi)有和Package相關(guān)聯(lián)的Package Body

OWNER	OBJECT_NAME

0 rows selected.

38. 被Disabled的約束

OWNER	TABLE_NAME	CONSTRAINT_NAME	CONSTRAINT_TYPE
SYSTEM	LOGMNR_ATTRCOL$	LOGMNR_ATTRCOL$_PK	P
SYSTEM	LOGMNR_ATTRIBUTE$	LOGMNR_ATTRIBUTE$_PK	P
SYSTEM	LOGMNR_CCOL$	LOGMNR_CCOL$_PK	P
SYSTEM	LOGMNR_CDEF$	LOGMNR_CDEF$_PK	P
SYSTEM	LOGMNR_COL$	LOGMNR_COL$_PK	P
SYSTEM	LOGMNR_COLTYPE$	LOGMNR_COLTYPE$_PK	P
SYSTEM	LOGMNR_DICTIONARY$	LOGMNR_DICTIONARY$_PK	P
SYSTEM	LOGMNR_DICTSTATE$	LOGMNR_DICTSTATE$_PK	P
SYSTEM	LOGMNR_ICOL$	LOGMNR_ICOL$_PK	P
SYSTEM	LOGMNR_IND$	LOGMNR_IND$_PK	P
SYSTEM	LOGMNR_INDCOMPART$	LOGMNR_INDCOMPART$_PK	P
SYSTEM	LOGMNR_INDPART$	LOGMNR_INDPART$_PK	P
SYSTEM	LOGMNR_INDSUBPART$	LOGMNR_INDSUBPART$_PK	P
SYSTEM	LOGMNR_LOB$	LOGMNR_LOB$_PK	P
SYSTEM	LOGMNR_LOBFRAG$	LOGMNR_LOBFRAG$_PK	P
SYSTEM	LOGMNR_OBJ$	LOGMNR_OBJ$_PK	P
SYSTEM	LOGMNR_TAB$	LOGMNR_TAB$_PK	P
SYSTEM	LOGMNR_TABCOMPART$	LOGMNR_TABCOMPART$_PK	P
SYSTEM	LOGMNR_TABPART$	LOGMNR_TABPART$_PK	P
SYSTEM	LOGMNR_TABSUBPART$	LOGMNR_TABSUBPART$_PK	P
SYSTEM	LOGMNR_TS$	LOGMNR_TS$_PK	P
SYSTEM	LOGMNR_TYPE$	LOGMNR_TYPE$_PK	P
SYSTEM	LOGMNR_USER$	LOGMNR_USER$_PK	P

23 rows selected.

39. 被Disabled的觸發(fā)器

OWNER	TABLE_NAME	TRIGGER_NAME
SYS		NO_VM_ALTER
SYS		NO_VM_CREATE
SYS		NO_VM_DROP

3 rows selected.

40. Invalid Objects

OWNER	OBJECT_NAME	OBJECT_TYPE

0 rows selected.

41. 執行失敗或中斷的Jobs

JOB	Last Date	This Date	BROKEN	FAILURES	SCHEMA_USER	WHAT

0 rows selected.

42. 當前未執行且下一執行日期已經(jīng)過(guò)去的Jobs

JOB	Last Date	This Date	BROKEN	FAILURES	SCHEMA_USER	WHAT

0 rows selected.

43. 含有未分析的非系統表的Schemas

Schema
WMSYS

1 rows selected.

44. 含有未分析的非系統分區表的Schemas

Schema

0 rows selected.

45. 含有未分析的非系統索引的Schemas

Schema
WMSYS

1 rows selected.

46. 含有未分析的非系統分區索引的Schemas

Schema

0 rows selected.

47. 死鎖檢測

TABLE_NAME	SESSION_ID	SERIAL#	ACTION	OSUSER	AP_PROCESS_ID	DB_PROCESS_ID

0 rows selected.

48. top I/O Wait

EVENT	SEGMENT_TYPE	SEGMENT_NAME	FILE_ID	BLOCK_ID	BLOCKS

0 rows selected.

49. top 10 wait

EVENT	Prev	Curr	Total
rdbms ipc message	0	8	8
Queue Monitor Wait	0	1	1
SQL*Net message to client	1	0	1
wakeup time manager	0	1	1
smon timer	0	1	1
pmon timer	0	1	1

6 rows selected.

50. Top 10 bad SQL

EXECUTIONS	SORTS	COMMAND_TYPE	DISK_READS	SQL_TEXT
5	2	3	7779	Select a.tablespace_name, a.owner, decode(a.partition_name, null, a.segment_name, a.segment_name || '.' || a.partition_name) "Segment Name", a.extents, round(next_extent/1024) next_extent_kb, round(b.free / 1024) ts_free_kb, round(c.morebytes / 1024 / 1024) ts_growth_mb from dba_segments a, (Select df.tablespace_name, nvl(max(fs.bytes), 0) free from dba_data_files df, dba_free_space fs where df.file_id = fs.file_id (+) group by df.tablespace_name) b, (Select tablespace_name, max(maxbytes - bytes) morebytes, sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible from dba_data_files group by tablespace_name) c where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name and ((c.autoextensible = 0) or ((c.autoextensible > 0) and (a.next_extent > c.morebytes))) and a.next_extent > b.free order
5	3	3	2025	select s.tablespace_name, round(100 * f.hole_count / (f.hole_count + s.seg_count)) pct_fragmented, s.seg_count segments, f.hole_count holes from (Select tablespace_name, count(*) seg_count from dba_segments group by tablespace_name) s, (Select tablespace_name, count(*) hole_count from dba_free_space group by tablespace_name) f where s.tablespace_name = f.tablespace_name and s.tablespace_name in (Select tablespace_name from dba_tablespaces where contents = 'PERMANENT') And s.tablespace_name not in ('SYSTEM') and 100 * f.hole_count / (f.hole_count + s.seg_count) > 30 and s.seg_count > 50
5	1	3	2015	Select InitCap(SEGMENT_TYPE) "Type", OWNER, SEGMENT_NAME, BYTES, NEXT_EXTENT, ROUND(100 * NEXT_EXTENT / BYTES) "Percent(Next/Bytes)" FROM DBA_SEGMENTS WHERE ((ROUND(100 * NEXT_EXTENT / BYTES) < 10) OR (ROUND(100 * NEXT_EXTENT / BYTES) >= 200)) AND SEGMENT_TYPE NOT IN ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') order by 2,3,1
5	1	3	1939	Select segment_type, owner, Segment_name, Tablespace_name, partition_name, round(bytes /1024/1024) "Size(MB)", extents, max_extents From dba_segments where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') and extents >= (1 - ( 10 / 100)) * max_extents and max_extents > 1 order by bytes / max_extents desc
5	0	3	1899	Select segment_type, owner, segment_name, extents, partition_name from dba_segments where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') and extents > 100
5	3	3	1066	SELECT acc.owner,acc.table_name,acc.constraint_name,acc.column_name FROM all_cons_columns acc, all_constraints ac WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R' and acc.owner not in ('SYS','SYSTEM') AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN (SELECT acc.owner, acc.table_name, acc.column_name, acc.position FROM all_cons_columns acc, all_constraints ac WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R' MINUS SELECT table_owner, table_name, column_name, column_position FROM all_ind_columns) ORDER BY acc.owner,acc.table_name, acc.constraint_name,acc.column_name
5	3	3	549	Select owner, table_name from dba_tables where owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') minus Select owner, table_name from dba_constraints where constraint_type = 'P' and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB')
5	2	3	485	Select table_owner, table_name, count(*) index_count from dba_indexes where table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') having count(*) > 6 group by table_owner, table_name order by 3 desc
5	1	3	398	Select distinct owner "Schema" from DBA_indexes where leaf_blocks is null and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') order by 1

9 rows selected.

51. Top most expensive SQL (Buffer Gets by Executions)

BUFFER_GETS	EXECUTIONS	GETS_PER_EXEC	HASH_VALUE	SQL_TEXT
1117445	1	1117445	2096533749	Select s.synonym_name, s.table_owner, s.table_name from sys.DBA_synonyms s where not exists (Select 'x' from sys.DBA_objects o where o.owner = s.table_owner and o.object_name = s.table_name) and db_link is null and s.owner = 'PUBLIC' order by 1
216328	1	216328	2308518879	Select pb.owner, pb.object_name from dba_objects pb where pb.object_type = 'PACKAGE BODY' and not exists (Select 1 from dba_objects p where p.object_type = 'PACKAGE' and p.owner = pb.owner and p.object_name = pb.object_name) order by 1,2

2 rows selected.

52. Top most expensive SQL (Physical Reads by Executions)

DISK_READS	EXECUTIONS	READS_PER_EXEC	HASH_VALUE	SQL_TEXT

0 rows selected.

53. Top most expensive SQL (Rows Processed by Executions)

ROWS_PROCESSED	EXECUTIONS	ROWS_PER_EXEC	HASH_VALUE	SQL_TEXT

0 rows selected.

54. Top most expensive SQL (Buffer Gets vs Rows Processed)

BUFFER_GETS	rows_processed	EXECUTIONS	LOADS	AVG_COST	SQL_TEXT
1117445	1	1	1	1117445	Select s.synonym_name, s.table_owner, s.table_name from sys.DBA_synonyms s where not exists (Select 'x' from sys.DBA_objects o where o.owner = s.table_owner and o.object_name = s.table_name) and db_link is null and s.owner = 'PUBLIC' order by 1
216328	0	1	1	216328	Select pb.owner, pb.object_name from dba_objects pb where pb.object_type = 'PACKAGE BODY' and not exists (Select 1 from dba_objects p where p.object_type = 'PACKAGE' and p.owner = pb.owner and p.object_name = pb.object_name) order by 1,2
17191	0	1	1	17191	Select InitCap(SEGMENT_TYPE) "Type", OWNER, SEGMENT_NAME, BYTES, NEXT_EXTENT, ROUND(100 * NEXT_EXTENT / BYTES) "Percent(Next/Bytes)" FROM DBA_SEGMENTS WHERE ((ROUND(100 * NEXT_EXTENT / BYTES) < 10) OR (ROUND(100 * NEXT_EXTENT / BYTES) >= 200)) AND SEGMENT_TYPE NOT IN ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') order by 2,3,1
15335	0	1	1	15335	Select segment_type, owner, Segment_name, Tablespace_name, partition_name, round(bytes /1024/1024) "Size(MB)", extents, max_extents From dba_segments where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') and extents >= (1 - ( 10 / 100)) * max_extents and max_extents > 1 order by bytes / max_extents desc
11712	0	1	1	11712	Select segment_type, owner, segment_name, extents, partition_name from dba_segments where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') and extents > 100

5 rows selected.