ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oracle数据库巡检

oracle数据库巡检

原创 Linux操作系统 作者：baizhaoxian 时间：2008-10-26 11:52:42 0 删除 编辑

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.



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. 控制文件及其状态


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. 磁盘排序(<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. 表空间状态及其大小使用情况


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. 数据文件状态及其大小使用情况


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. 不使用临时文件的临时表空间


TABLESPACE_NAME CONTENTS

0 rows selected.



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


TABLESPACE_NAME FILE_NAME STATUS

0 rows selected.



20. 处于恢复模式的文件


TABLESPACE_NAME FILE_NAME

0 rows selected.



21. 含有50个以上的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 相对于段当前已分配字节过大(>=2倍)或过小(<10%)的Segments


Type OWNER SEGMENT_NAME BYTES NEXT_EXTENT Percent(Next/Bytes)

0 rows selected.



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


SEGMENT_TYPE OWNER SEGMENT_NAME TABLESPACE_NAME PARTITION_NAME Size(MB) EXTENTS MAX_EXTENTS

0 rows selected.



26. 已经分配超过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. 没有主键的非系统表


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. 没有索引的外键


OWNER TABLE_NAME CONSTRAINT_NAME COLUMN_NAME

0 rows selected.



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


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. 没有授予给任何角色和用户的角色


ROLE

0 rows selected.



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


USERNAME

0 rows selected.



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


USERNAME
SYSTEM 
OUTLN 

2 rows selected.



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


PROFILE

0 rows selected.



37. 没有和Package相关联的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的触发器


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. 当前未执行且下一执行日期已经过去的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.

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/14136468/viewspace-478149/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 常用命令
下一篇： Oracle10G常用维护语句
请登录后发表评论 登录
全部评论
baizhaoxian

注册时间：2008-10-23

  • 博文量
    29
  • 访问量
    132756

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593