linux中oracle的日常維護命令
轉載地址：http://blog.csdn.net/tianlesoftware/article/details/4792786
1. 檢查Oracle的程式
$ ps -ef|grep "ora_"|grep -v grep
oracle 5998 1 0 11:15:59 ? 0:01 ora_j000_PPRD10
oracle 2968 1 0 21:16:57 ? 0:00 ora_q000_PPRD10
oracle 2927 1 0 21:16:33 ? 0:00 ora_pmon_PPRD10
oracle 2933 1 0 21:16:34 ? 0:07 ora_dbw0_PPRD10
oracle 2945 1 0 21:16:34 ? 0:02 ora_mmon_PPRD10
oracle 2931 1 0 21:16:33 ? 0:00 ora_mman_PPRD10
oracle 2949 1 0 21:16:34 ? 0:00 ora_d000_PPRD10
oracle 2970 1 0 21:16:57 ? 0:00 ora_q001_PPRD10
oracle 2935 1 0 21:16:34 ? 0:05 ora_lgwr_PPRD10
oracle 2951 1 0 21:16:34 ? 0:00 ora_s000_PPRD10
oracle 2939 1 0 21:16:34 ? 0:06 ora_smon_PPRD10
oracle 2957 1 0 21:16:47 ? 0:00 ora_qmnc_PPRD10
oracle 2943 1 0 21:16:34 ? 0:05 ora_cjq0_PPRD10
oracle 2947 1 0 21:16:34 ? 0:00 ora_mmnl_PPRD10
oracle 2937 1 0 21:16:34 ? 0:18 ora_ckpt_PPRD10
oracle 2941 1 0 21:16:34 ? 0:00 ora_reco_PPRD10
oracle 2929 1 0 21:16:33 ? 0:00 ora_psp0_PPRD10
在檢查Oracle的程式命令輸出後，輸出顯示至少應包括以下一些程式：
? Oracle寫資料檔案的程式，輸出顯示為：“ora_dbw0_ORCL”
? Oracle寫日誌檔案的程式，輸出顯示為：“ora_lgwr_ORCL”
? Oracle監聽例項狀態的程式，輸出顯示為：“ora_smon_ORCL”
? Oracle監聽客戶端連線程式狀態的程式，輸出顯示為：“ora_pmon_ORCL”
? Oracle進行歸檔的程式，輸出顯示為：“ora_arc0_ORCL”
? Oracle進行檢查點的程式，輸出顯示為：“ora_ckpt_ORCL”
? Oracle進行[url=javascript:;]恢復[/url]的程式，輸出顯示為：“ora_reco_ORCL”
2 . 檢視資料庫的例項：
SQL> select instance_name,status,version,database_status from v$instance;
INSTANCE_NAME STATUS VERSION DATABASE_STATUS
---------------- ------------ ----------------- -----------------
PPRD10 OPEN 10.2.0.4.0 ACTIVE
其中“STATUS”表示Oracle當前的例項狀態，必須為“OPEN”；DATABASE_STATUS”表示Oracle當前資料庫的狀態，必須為“ACTIVE”。
3 . 檢視資料庫的日誌模式，開啟模式
SQL> select name,log_mode,open_mode from v$database;
NAME LOG_MODE OPEN_MODE
--------- ------------ ----------
PPRD10 NOARCHIVELOG READ WRITE
其中“LOG_MODE”表示Oracle當前的歸檔方式。“ARCHIVELOG”表示資料庫執行在歸檔模式下，“NOARCHIVELOG”表示資料庫執行在非歸檔模式下。
4 . 檢視資料庫alert_SID.log 中的ora-的錯誤資訊：
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i ora-
WARNING: inbound connection timed out (ORA-3136)
WARNING: inbound connection timed out (ORA-3136)
ORA-12012: error on auto execute of job 78677
ORA-20820: ORA-20820:
ORA-06512: at "PS_SYSTEM.ROW_VALIDATOR_T", line 912
ORA-06512: at "PS_SYSTEM.ROW_VALIDATOR_T", line 924
ORA-06512: at "WORKORDER.INVENTORY_UTL", line 1260
ORA-12012: error on auto execute of job 78677
4.1 查alert_SID.log中的err的資訊：
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i err
Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_2666.trc:
ORA-12012: error on auto execute of job 78677
: 6000101: Error occurred when rolling Inventory date
Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_11886.trc:
ORA-12012: error on auto execute of job 78677
: 6000101: Error occurred when rolling Inventory date
Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_21375.trc:
ORA-12012: error on auto execute of job 78677
: 6000101: Error occurred when rolling Inventory date
4.2 查alert_SID.log的fail的資訊：
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i fail
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
5. 檢查控制檔案狀態
SQL> select status,name from v$controlfile;
STATUS NAME
------- ------------------------------------------------------------------------
/data/app/oracle/oradata/PPRD10/control01.ctl
/data/app/oracle/oradata/PPRD10/control02.ctl
/data/app/oracle/oradata/PPRD10/control03.ctl
輸出結果應該有3條以上（包含3條）的記錄，“STATUS”應該為空。狀態為空表示控制檔案狀態正常。
6. 查詢日誌狀態
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- -------------------------------------------------------------
1 /data/app/oracle/oradata/PPRD10/redo01.log
2 /data/app/oracle/oradata/PPRD10/redo02.log
3 /data/app/oracle/oradata/PPRD10/redo03.log
輸出結果應該有3條以上(包含 3 條)記錄,”STATUS”應該為非”INVALID”，非“DELETED”。
7 . 查詢資料狀態：
SQL> select file#,status,name from v$datafile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
2 ONLINE /u01/app/oracle/oradata/orcl/undotbs01.dbf
3 ONLINE /u01/app/oracle/oradata/orcl/sysaux01.dbf
4 ONLINE /u01/app/oracle/oradata/orcl/users01.dbf
5 ONLINE /u01/app/oracle/oradata/orcl/example01.dbf
6 ONLINE /u01/app/oracle/oradata/orcl/perfstat.dbf
7 ONLINE /u01/app/oracle/oradata/orcl/risenet.dbf
“ONLINE"表示正常線上狀態
8 . 查詢表空間狀態
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
PERFSTAT ONLINE
RISENET ONLINE
“ONLINE”表示正常線上狀態
9 . 檢查Oracle所有回滾段的狀態
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU10$ ONLINE
_SYSSMU9$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU5$ ONLINE
_SYSSMU4$ ONLINE
_SYSSMU3$ ONLINE
_SYSSMU2$ ONLINE
_SYSSMU1$ ONLINE
11 rows selected.
10 .檢查Oracle初始化檔案中相關的引數值
SQL>select resource_name,current_utilization,max_utilization,initial_allocation ,limit_value from v$resource_limit;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LI
------------------------------ ------------------- --------------- ---------- --
processes 26 31 150
sessions 30 37 170
enqueue_locks 23 31 2300
enqueue_resources 23 49 968 U
ges_procs 0 0 0
ges_ress 0 0 0 U
ges_locks 0 0 0 U
ges_cache_ress 0 0 0 U
ges_reg_msgs 0 0 0 U
ges_big_msgs 0 0 0 U
ges_rsv_msgs 0 0 0
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LI
------------------------------ ------------------- --------------- ---------- --
gcs_resources 0 0 0
gcs_shadows 0 0 0
dml_locks 0 68 748 U
temporary_table_locks 0 3 UNLIMITED U
transactions 2 11 187 U
branches 0 0 187 U
cmtcallbk 0 2 187 U
sort_segment_locks 0 3 UNLIMITED U
max_rollback_segments 11 11 187
max_shared_servers 1 1 UNLIMITED U
parallel_max_servers 0 0 40
22 rows selected.
11. 檢查Oracle各個表空間的增長情況
Select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
from (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B
where A.tablespace_name=B.tablespace_name;
SQL> Select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
2 from (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B
where A.tablespace_name=B.tablespace_name;
3
TABLESPACE_NAME USED_PERCENT
------------------------------ ------------
UNDOTBS1 33.25
SYSAUX 99.0364583
RISENET .0125
USERS 62.5
SYSTEM 98.6067708
EXAMPLE 68.25
PERFSTAT 28.7625
7 rows selected.
12 . 檢查一些擴充套件異常的物件
select segment_name,segment_type,tablespace_name,
(extents/max_extents)*100 Percent from sys.DBA_segments
where max_extents!=0 and (extents/max_extents)*100>=95
order by percent;
SQL> select segment_name,segment_type,tablespace_name,
2 (extents/max_extents)*100 Percent
3 from sys.DBA_segments
4 where max_extents!=0 and (extents/max_extents)*100>=95
5 order by percent;
no rows selected
如果有記錄返回，則這些物件的擴充套件已經快達到它定義時的最大擴充套件值。對於這些物件要修改它的儲存結構引數.
13 . 檢查system表空間內的內容
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and owner!='SYS'
and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM'
and owner!='SYS' and owner!='SYSTEM';
SQL> select distinct(owner) from dba_tables
2 where tablespace_name='SYSTEM' and
3 owner!='SYS' and owner!='SYSTEM'
4 union
5 select distinct(owner) from dba_indexes
6 where tablespace_name='SYSTEM' and
7 owner!='SYS' and owner!='SYSTEM';
OWNER
------------------------------
MDSYS
OLAPSYS
OUTLN
如果有記錄返回，則表明system表空間記憶體在一些非system和sys使用者的物件。應該進一步檢查這些物件是否與我們應用相關。如果相關請把這些物件移到非System表空間，同時應該檢查這些物件屬主的預設表空間值，
14. 檢查物件的下一擴充套件與表空間的最大擴充套件值
select a.table_name,a.next_extent,a.tablespace_name
from all_tables a,
(select tablespace_name,max(bytes) as big_chunk
from dba_free_space group by tablespace_name) f
where f.tablespace_name=a.tablespace_name
and a.next_extent>f.big_chunk
union
select a.index_name,a.next_extent,a.tablespace_name
from all_indexes a,
(select tablespace_name,max(bytes) as big_chunk
from dba_free_space
group by tablespace_name) f
where f.tablespace_name=a.tablespace_name
and a.next_extent>f.big_chunk;
SQL> select a.table_name,a.next_extent,a.tablespace_name
2 from all_tables a,
3 (select tablespace_name,max(bytes) as big_chunk
4 from dba_free_space group by tablespace_name) f
5 where f.tablespace_name=a.tablespace_name
6 and a.next_extent>f.big_chunk
7 union
8 select a.index_name,a.next_extent,a.tablespace_name
9 from all_indexes a,
10 (select tablespace_name,max(bytes) as big_chunk
11 from dba_free_space
12 group by tablespace_name) f
13 where f.tablespace_name=a.tablespace_name
14 and a.next_extent>f.big_chunk;
no rows selected
如果有記錄返回，則表明這些物件的下一個擴充套件大於該物件所屬表空間的最大擴充套件值，需調整相應表空間的儲存引數
1. 檢查Oracle的程式
$ ps -ef|grep "ora_"|grep -v grep
oracle 5998 1 0 11:15:59 ? 0:01 ora_j000_PPRD10
oracle 2968 1 0 21:16:57 ? 0:00 ora_q000_PPRD10
oracle 2927 1 0 21:16:33 ? 0:00 ora_pmon_PPRD10
oracle 2933 1 0 21:16:34 ? 0:07 ora_dbw0_PPRD10
oracle 2945 1 0 21:16:34 ? 0:02 ora_mmon_PPRD10
oracle 2931 1 0 21:16:33 ? 0:00 ora_mman_PPRD10
oracle 2949 1 0 21:16:34 ? 0:00 ora_d000_PPRD10
oracle 2970 1 0 21:16:57 ? 0:00 ora_q001_PPRD10
oracle 2935 1 0 21:16:34 ? 0:05 ora_lgwr_PPRD10
oracle 2951 1 0 21:16:34 ? 0:00 ora_s000_PPRD10
oracle 2939 1 0 21:16:34 ? 0:06 ora_smon_PPRD10
oracle 2957 1 0 21:16:47 ? 0:00 ora_qmnc_PPRD10
oracle 2943 1 0 21:16:34 ? 0:05 ora_cjq0_PPRD10
oracle 2947 1 0 21:16:34 ? 0:00 ora_mmnl_PPRD10
oracle 2937 1 0 21:16:34 ? 0:18 ora_ckpt_PPRD10
oracle 2941 1 0 21:16:34 ? 0:00 ora_reco_PPRD10
oracle 2929 1 0 21:16:33 ? 0:00 ora_psp0_PPRD10
在檢查Oracle的程式命令輸出後，輸出顯示至少應包括以下一些程式：
? Oracle寫資料檔案的程式，輸出顯示為：“ora_dbw0_ORCL”
? Oracle寫日誌檔案的程式，輸出顯示為：“ora_lgwr_ORCL”
? Oracle監聽例項狀態的程式，輸出顯示為：“ora_smon_ORCL”
? Oracle監聽客戶端連線程式狀態的程式，輸出顯示為：“ora_pmon_ORCL”
? Oracle進行歸檔的程式，輸出顯示為：“ora_arc0_ORCL”
? Oracle進行檢查點的程式，輸出顯示為：“ora_ckpt_ORCL”
? Oracle進行[url=javascript:;]恢復[/url]的程式，輸出顯示為：“ora_reco_ORCL”
2 . 檢視資料庫的例項：
SQL> select instance_name,status,version,database_status from v$instance;
INSTANCE_NAME STATUS VERSION DATABASE_STATUS
---------------- ------------ ----------------- -----------------
PPRD10 OPEN 10.2.0.4.0 ACTIVE
其中“STATUS”表示Oracle當前的例項狀態，必須為“OPEN”；DATABASE_STATUS”表示Oracle當前資料庫的狀態，必須為“ACTIVE”。
3 . 檢視資料庫的日誌模式，開啟模式
SQL> select name,log_mode,open_mode from v$database;
NAME LOG_MODE OPEN_MODE
--------- ------------ ----------
PPRD10 NOARCHIVELOG READ WRITE
其中“LOG_MODE”表示Oracle當前的歸檔方式。“ARCHIVELOG”表示資料庫執行在歸檔模式下，“NOARCHIVELOG”表示資料庫執行在非歸檔模式下。
4 . 檢視資料庫alert_SID.log 中的ora-的錯誤資訊：
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i ora-
WARNING: inbound connection timed out (ORA-3136)
WARNING: inbound connection timed out (ORA-3136)
ORA-12012: error on auto execute of job 78677
ORA-20820: ORA-20820:
ORA-06512: at "PS_SYSTEM.ROW_VALIDATOR_T", line 912
ORA-06512: at "PS_SYSTEM.ROW_VALIDATOR_T", line 924
ORA-06512: at "WORKORDER.INVENTORY_UTL", line 1260
ORA-12012: error on auto execute of job 78677
4.1 查alert_SID.log中的err的資訊：
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i err
Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_2666.trc:
ORA-12012: error on auto execute of job 78677
: 6000101: Error occurred when rolling Inventory date
Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_11886.trc:
ORA-12012: error on auto execute of job 78677
: 6000101: Error occurred when rolling Inventory date
Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_21375.trc:
ORA-12012: error on auto execute of job 78677
: 6000101: Error occurred when rolling Inventory date
4.2 查alert_SID.log的fail的資訊：
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i fail
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
5. 檢查控制檔案狀態
SQL> select status,name from v$controlfile;
STATUS NAME
------- ------------------------------------------------------------------------
/data/app/oracle/oradata/PPRD10/control01.ctl
/data/app/oracle/oradata/PPRD10/control02.ctl
/data/app/oracle/oradata/PPRD10/control03.ctl
輸出結果應該有3條以上（包含3條）的記錄，“STATUS”應該為空。狀態為空表示控制檔案狀態正常。
6. 查詢日誌狀態
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- -------------------------------------------------------------
1 /data/app/oracle/oradata/PPRD10/redo01.log
2 /data/app/oracle/oradata/PPRD10/redo02.log
3 /data/app/oracle/oradata/PPRD10/redo03.log
輸出結果應該有3條以上(包含 3 條)記錄,”STATUS”應該為非”INVALID”，非“DELETED”。
7 . 查詢資料狀態：
SQL> select file#,status,name from v$datafile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
2 ONLINE /u01/app/oracle/oradata/orcl/undotbs01.dbf
3 ONLINE /u01/app/oracle/oradata/orcl/sysaux01.dbf
4 ONLINE /u01/app/oracle/oradata/orcl/users01.dbf
5 ONLINE /u01/app/oracle/oradata/orcl/example01.dbf
6 ONLINE /u01/app/oracle/oradata/orcl/perfstat.dbf
7 ONLINE /u01/app/oracle/oradata/orcl/risenet.dbf
“ONLINE"表示正常線上狀態
8 . 查詢表空間狀態
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
PERFSTAT ONLINE
RISENET ONLINE
“ONLINE”表示正常線上狀態
9 . 檢查Oracle所有回滾段的狀態
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU10$ ONLINE
_SYSSMU9$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU5$ ONLINE
_SYSSMU4$ ONLINE
_SYSSMU3$ ONLINE
_SYSSMU2$ ONLINE
_SYSSMU1$ ONLINE
11 rows selected.
10 .檢查Oracle初始化檔案中相關的引數值
SQL>select resource_name,current_utilization,max_utilization,initial_allocation ,limit_value from v$resource_limit;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LI
------------------------------ ------------------- --------------- ---------- --
processes 26 31 150
sessions 30 37 170
enqueue_locks 23 31 2300
enqueue_resources 23 49 968 U
ges_procs 0 0 0
ges_ress 0 0 0 U
ges_locks 0 0 0 U
ges_cache_ress 0 0 0 U
ges_reg_msgs 0 0 0 U
ges_big_msgs 0 0 0 U
ges_rsv_msgs 0 0 0
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LI
------------------------------ ------------------- --------------- ---------- --
gcs_resources 0 0 0
gcs_shadows 0 0 0
dml_locks 0 68 748 U
temporary_table_locks 0 3 UNLIMITED U
transactions 2 11 187 U
branches 0 0 187 U
cmtcallbk 0 2 187 U
sort_segment_locks 0 3 UNLIMITED U
max_rollback_segments 11 11 187
max_shared_servers 1 1 UNLIMITED U
parallel_max_servers 0 0 40
22 rows selected.
11. 檢查Oracle各個表空間的增長情況
Select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
from (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B
where A.tablespace_name=B.tablespace_name;
SQL> Select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
2 from (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B
where A.tablespace_name=B.tablespace_name;
3
TABLESPACE_NAME USED_PERCENT
------------------------------ ------------
UNDOTBS1 33.25
SYSAUX 99.0364583
RISENET .0125
USERS 62.5
SYSTEM 98.6067708
EXAMPLE 68.25
PERFSTAT 28.7625
7 rows selected.
12 . 檢查一些擴充套件異常的物件
select segment_name,segment_type,tablespace_name,
(extents/max_extents)*100 Percent from sys.DBA_segments
where max_extents!=0 and (extents/max_extents)*100>=95
order by percent;
SQL> select segment_name,segment_type,tablespace_name,
2 (extents/max_extents)*100 Percent
3 from sys.DBA_segments
4 where max_extents!=0 and (extents/max_extents)*100>=95
5 order by percent;
no rows selected
如果有記錄返回，則這些物件的擴充套件已經快達到它定義時的最大擴充套件值。對於這些物件要修改它的儲存結構引數.
13 . 檢查system表空間內的內容
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and owner!='SYS'
and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM'
and owner!='SYS' and owner!='SYSTEM';
SQL> select distinct(owner) from dba_tables
2 where tablespace_name='SYSTEM' and
3 owner!='SYS' and owner!='SYSTEM'
4 union
5 select distinct(owner) from dba_indexes
6 where tablespace_name='SYSTEM' and
7 owner!='SYS' and owner!='SYSTEM';
OWNER
------------------------------
MDSYS
OLAPSYS
OUTLN
如果有記錄返回，則表明system表空間記憶體在一些非system和sys使用者的物件。應該進一步檢查這些物件是否與我們應用相關。如果相關請把這些物件移到非System表空間，同時應該檢查這些物件屬主的預設表空間值，
14. 檢查物件的下一擴充套件與表空間的最大擴充套件值
select a.table_name,a.next_extent,a.tablespace_name
from all_tables a,
(select tablespace_name,max(bytes) as big_chunk
from dba_free_space group by tablespace_name) f
where f.tablespace_name=a.tablespace_name
and a.next_extent>f.big_chunk
union
select a.index_name,a.next_extent,a.tablespace_name
from all_indexes a,
(select tablespace_name,max(bytes) as big_chunk
from dba_free_space
group by tablespace_name) f
where f.tablespace_name=a.tablespace_name
and a.next_extent>f.big_chunk;
SQL> select a.table_name,a.next_extent,a.tablespace_name
2 from all_tables a,
3 (select tablespace_name,max(bytes) as big_chunk
4 from dba_free_space group by tablespace_name) f
5 where f.tablespace_name=a.tablespace_name
6 and a.next_extent>f.big_chunk
7 union
8 select a.index_name,a.next_extent,a.tablespace_name
9 from all_indexes a,
10 (select tablespace_name,max(bytes) as big_chunk
11 from dba_free_space
12 group by tablespace_name) f
13 where f.tablespace_name=a.tablespace_name
14 and a.next_extent>f.big_chunk;
no rows selected
如果有記錄返回，則表明這些物件的下一個擴充套件大於該物件所屬表空間的最大擴充套件值，需調整相應表空間的儲存引數
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28371090/viewspace-1172434/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle OGG日常維護Oracle
- rac叢集日常維護命令
- mc日常維護
- 【PDB】Oracle pdb維護常用sql命令OracleSQL
- Linux日常命令Linux
- PG日常維護（一）
- OGG日常維護文件
- 伺服器的日常維護方式伺服器
- 史上最全近百條Oracle DBA日常維護SQL指令碼指令OracleSQL指令碼
- 膝上型電腦日常維護的六個常識，筆記本日常維護小竅門筆記
- ORACLE OGG運維及日常監控Oracle運維
- Linux 系統日常運維 9 大技能，搞定 90% 日常運維Linux運維
- DBA日常維護SQL指令碼_自己編寫的SQL指令碼
- Docker Swarm 日常運維命令筆記DockerSwarm運維筆記
- Lync日常運維常用命令運維
- 個人積累linux 日常命令Linux
- 5S管理在辦公電腦日常維護中的應用
- Oracle RAC日常運維-ASM磁碟擴容Oracle運維ASM
- Oracle RAC日常運維-DATA磁碟組故障Oracle運維
- 伺服器維護日常需做哪些工作?伺服器
- oracle常用維護查詢Oracle
- Linux命令中互動式命令都有哪些？linux運維基礎Linux運維
- Postgresql日常運維-安裝(Linux)01SQL運維Linux
- 黑龍江、鄭州、招聘、精通PHP的日常搭建維護PHP
- 伺服器日常維護需注意哪些事項？伺服器
- Redis日常運維-常用命令彙總Redis運維
- ORACLE LOB大欄位維護Oracle
- ORACLE user profile配置/管理/維護Oracle
- Oracle 常用運維命令整理Oracle運維
- Linux 日常操作命令集合 -1程式操作Linux
- 資料庫常用維護命令資料庫
- 日常命令
- Oracle RAC日常運維-NetworkManager導致叢集故障Oracle運維
- Oracle統計資訊的收集和維護Oracle
- ORACLE 11G 維護視窗和自動維護任務Oracle
- oracle 11g data guard維護Oracle
- 所用的日常 Git 命令Git
- 【運維必看】Linux命令之lsblk命令運維Linux
- 集中管理伺服器軟體日常維護工具六款伺服器