oracle数据库巡检

 

Oralce Database Health Check (Performance)

List Contents

1. 数据库概要
2. 参数文件(是spfile还是pfile)
3. 非默认的参数
4. 控制文件及其状态
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. 磁盘排序(<5%)
15. Log Buffer latch Contention(<1%)
16. 表空间状态及其大小使用情况
17. 数据文件状态及其大小使用情况
18. 不使用临时文件的临时表空间
19. 无效的数据文件(offline)
20. 处于恢复模式的文件
21. 含有50个以上的Extent且30%以上碎片的表空间
22. 表空间上的I/O分布
23. 数据文件上的I/O分布
24. Next Extent 相对于段当前已分配字节过大(>=2倍)或过小(<10%)的Segments
25. Max Extents(>1)已经有90%被使用了的Segments
26. 已经分配超过100 Extents的Segments
27. 因表空间空间不够将导致不能扩展的Objects
28. 没有主键的非系统表
29. 没有索引的外键
30. 建有6个以上索引的非系统表
31. 指向对象不存在的Public同义词
32. 指向对象不存在的非Public同义词
33. 没有授予给任何角色和用户的角色
34. 将System表空间作为临时表空间的用户(除Sys外)
35. 将System表空间作为默认表空间的用户(除Sys外)
36. 没有授予给任何用户的profiles
37. 没有和Package相关联的Package Body
38. 被Disabled的约束
39. 被Disabled的触发器
40. Invalid Objects
41. 执行失败或中断的Jobs
42. 当前未执行且下一执行日期已经过去的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.

Top

2. 参数文件(是spfile还是pfile)

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

1 rows selected.

Top

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.

Top

4. 控制文件及其状态

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

3 rows selected.

Top

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.

Top

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.

Top

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.

Top

8. Library Cache Reload Ratio(<1%)

LC_Reload_Ratio%
1.0126

1 rows selected.

Top

9. Data Dictionary Miss Ratio(<15%)

DC_Miss_Ratio%
23.667

1 rows selected.

Top

10. 共享池使用概况

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

1 rows selected.

Top

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.

Top

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

BC_Hit _Ratio
87.6955

1 rows selected.

Top

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.

Top

14. 磁盘排序(<5%)

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

1 rows selected.

Top

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.

Top

16. 表空间状态及其大小使用情况

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.

Top

17. 数据文件状态及其大小使用情况

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.

Top

18. 不使用临时文件的临时表空间

TABLESPACE_NAME	CONTENTS

0 rows selected.

Top

19. 无效的数据文件(offline)

TABLESPACE_NAME	FILE_NAME	STATUS

0 rows selected.

Top

20. 处于恢复模式的文件

TABLESPACE_NAME	FILE_NAME

0 rows selected.

Top

21. 含有50个以上的Extent且30%以上碎片的表空间

TABLESPACE_NAME	PCT_FRAGMENTED	SEGMENTS	HOLES

0 rows selected.

Top

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.

Top

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.

Top

24. Next Extent 相对于段当前已分配字节过大(>=2倍)或过小(<10%)的Segments

Type	OWNER	SEGMENT_NAME	BYTES	NEXT_EXTENT	Percent(Next/Bytes)

0 rows selected.

Top

25. Max Extents(>1)已经有90%被使用了的Segments

SEGMENT_TYPE	OWNER	SEGMENT_NAME	TABLESPACE_NAME	PARTITION_NAME	Size(MB)	EXTENTS	MAX_EXTENTS

0 rows selected.

Top

26. 已经分配超过100 Extents的Segments

SEGMENT_TYPE	OWNER	SEGMENT_NAME	EXTENTS	PARTITION_NAME

0 rows selected.

Top

27. 因表空间空间不够将导致不能扩展的Objects

TABLESPACE_NAME	OWNER	Segment Name	EXTENTS	NEXT_EXTENT_KB	TS_FREE_KB	TS_GROWTH_MB

0 rows selected.

Top

28. 没有主键的非系统表

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.

Top

29. 没有索引的外键

OWNER	TABLE_NAME	CONSTRAINT_NAME	COLUMN_NAME

0 rows selected.

Top

30. 建有6个以上索引的非系统表

TABLE_OWNER	TABLE_NAME	INDEX_COUNT

0 rows selected.

Top

31. 指向对象不存在的Public同义词

SYNONYM_NAME	TABLE_OWNER	TABLE_NAME
GV$TRANSPORTABLE_PLATFORM	SYS	GV$_TRANSPORTABLE_PLATFORM

1 rows selected.

Top

32. 指向对象不存在的非Public同义词

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

1 rows selected.

Top

33. 没有授予给任何角色和用户的角色

ROLE

0 rows selected.

Top

34. 将System表空间作为临时表空间的用户(除Sys外)

USERNAME

0 rows selected.

Top

35. 将System表空间作为默认表空间的用户(除Sys外)

USERNAME
SYSTEM
OUTLN

2 rows selected.

Top

36. 没有授予给任何用户的profiles

PROFILE

0 rows selected.

Top

37. 没有和Package相关联的Package Body

OWNER	OBJECT_NAME

0 rows selected.

Top

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.

Top

39. 被Disabled的触发器

OWNER	TABLE_NAME	TRIGGER_NAME
SYS		NO_VM_ALTER
SYS		NO_VM_CREATE
SYS		NO_VM_DROP

3 rows selected.

Top

40. Invalid Objects

OWNER	OBJECT_NAME	OBJECT_TYPE

0 rows selected.

Top

41. 执行失败或中断的Jobs

JOB	Last Date	This Date	BROKEN	FAILURES	SCHEMA_USER	WHAT

0 rows selected.

Top

42. 当前未执行且下一执行日期已经过去的Jobs

JOB	Last Date	This Date	BROKEN	FAILURES	SCHEMA_USER	WHAT

0 rows selected.

Top

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

Schema
WMSYS

1 rows selected.

Top

44. 含有未分析的非系统分区表的Schemas

Schema

0 rows selected.

Top

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

Schema
WMSYS

1 rows selected.

Top

46. 含有未分析的非系统分区索引的Schemas

Schema

0 rows selected.

Top

47. 死锁检测

TABLE_NAME	SESSION_ID	SERIAL#	ACTION	OSUSER	AP_PROCESS_ID	DB_PROCESS_ID

0 rows selected.

Top

48. top I/O Wait

EVENT	SEGMENT_TYPE	SEGMENT_NAME	FILE_ID	BLOCK_ID	BLOCKS

0 rows selected.

Top

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.

Top

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.

Top

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.

Top

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

DISK_READS	EXECUTIONS	READS_PER_EXEC	HASH_VALUE	SQL_TEXT

0 rows selected.

Top

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

ROWS_PROCESSED	EXECUTIONS	ROWS_PER_EXEC	HASH_VALUE	SQL_TEXT

0 rows selected.

Top

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.