oracle 表空間滿了排查和解決(ORA-1653: unable to extend table test by 128 in tab)
users表空間異常佔滿處理
問題描述：
日常查詢數據庫alert日誌，發現報錯信息ORA-1653: unable to extend table AXJ_REDIS.USSD_UNREPORT_FAIL by 128 in tablespace USERS，users表空間已滿，無法擴展。
問題分析：
USERS表空間是默認用戶表空間，在創建一個用戶並沒有指定此用戶使用表空間時，該用戶所有信息都會放入到users表空間中，使用查詢表空間語句：select file_name,tablespace_name,bytes/1024/1024 "bytes MB",maxbytes/1024/1024 "maxbytes MB" from dba_data_files where tablespace_name='USERS'; 查詢users表空間，發現已佔滿 , 使用sql：select t.TABLE_NAME,t.NUM_ROWS from all_tables t where tablespace_name='USERS' order by num_rows desc; 查詢使用USERS表空間的表，按行級降序排序，發現多個表使用USERS表空間，存在大量數據導致USER表佔滿
問題處理：
1、擴展表空間： alter datafile 『/oracle/oradata/dbaxj/users01.dbf』 resize 30G; 2、擴展到最大30G文件無法繼續擴展，可增加數據文件：alter tablespace users add datafile 'users02.dbf' size 1024m autoextend on next 1024m maxsize 30G; 3、truncate刪除無用表釋放空間，假如未釋放，對TEST表進行收縮shrink，執行下面三個語句： 啟用行遷移：alter table TEST enable row movement; shrink表test：alter TABLE TEST shrink SPACE; 關閉行遷移：alter table TEST DISABLE row movement; 註：數據被刪除後（無論是 delete 還是 truncate table），數據文件大小不會縮小， Oracle 「高水位」所致(可以具體瞭解)，想要降低數據文件大小需降低高水位的正確做法是先降低HWM，再確定實際佔有大小，再resize數據文件，執行如下4個語句： (1)查詢表空間文件編號：select file#, name from v$datafile; (2)根據文件 ID 查詢這個數據文件最大數據塊(data block)的編號：select max(block_id) from dba_extents where file_id=4; (3)計算該表空間實際佔用的空間，先查詢數據塊大小： select value from v$parameter where name='db_block_size'，咱默認是8192. (4)計算實際佔用磁盤大小： select 65673 * 8 / 1024 from dual; (5)把數據文件大小resize到比實際佔用磁盤大小大一些就行了： alter database datafile '/oracle/oradata/dbaxj/users01.dbf' resize 600m; 這樣數據文件大小就變小了，節約空間 4、需要使用的表，修改表空間alter table USSD_UNREPORT_FAIL move tablespace new_tablespace，建表時需養成習慣，指定好表空間
// *Cause: Cannot shrink the segment because it is not in auto segment space // managed tablespace or it is not a data, index or lob segment. // *Action: Check the tablespace and segment type and reissue the statement
// *Cause: Cannot shrink the segment because it is not in auto segment space // managed tablespace or it is not a data, index or lob segment. // *Action: Check the tablespace and segment type and reissue the statement
413 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
413 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
手工建庫時，未將表空間數據文件設置為自動擴展引起的錯誤：ORA-01653: unable to extend * in tablespace * 的解決：
查看數據庫alert日誌文件時，發現出現大量如下的錯誤：
Sun Dec 01 10:00:42 2013
ORA-1653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 8 in tablespace SYSAUX
Errors in file /u01/app/Oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_j000_15569.trc:
ORA-01653: unable to extend table . by in tablespace
ORA-01653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 8 in tablespace SYSAUX
Sun Dec 01 11:00:27 2013
ORA-1653: unable to extend table SYS.WRI$_ADV_PARAMETERS by 128 in tablespace SYSAUX
Sun Dec 01 12:00:32 2013
ORA-1653: unable to extend table SYS.WRI$_ADV_PARAMETERS by 128 in tablespace SYSAUX
Sun Dec 01 13:00:36 2013
ORA-1653: unable to extend table SYS.WRI$_ADV_PARAMETERS by 128 in tablespace SYSAUX
Sun Dec 01 14:00:40 2013
ORA-1653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 8 in tablespace SYSAUX
ORA-1653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 8 in tablespace SYSAUX
Sun Dec 01 14:00:41 2013
ORA-1653: unable to extend table SYS.WRI$_ADV_PARAMETERS by 128 in tablespace SYSAUX
Sun Dec 01 15:00:45 2013
ORA-1653: unable to extend table SYS.WRH$_IOSTAT_FILETYPE by 8 in tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1653).
MMON (emergency) purge of WR snapshots (188) and older
Sun Dec 01 15:00:49 2013
SYS@ bys3>col file_name for a40
SYS@ bys3>select file_name,autoextensible,increment_by from dba_data_files; --查數據文件是否設置autoextend on屬性要從 dba_data_files 查
FILE_NAME AUT INCREMENT_BY
---------------------------------------- --- ------------
/u01/oradata/bys3/system01.dbf NO 0
/u01/oradata/bys3/sysaux01.dbf NO 0
/u01/oradata/bys3/undotbs01.dbf NO 0
/u01/oradata/bys3/user01.dbf NO 0
SYS@ bys3>select TABLESPACE_NAME,sum(bytes/1024/1024) from dba_free_space group by tablespace_name; 通過這語句查各表空間使用率，因SYSAUX沒有FREE SPACE，在這沒顯示。
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS1 58.0625
USERS 48.6875
SYSTEM 155.375
確定問題後，解決方法：將sysaux表空間的數據文件屬性改為自動擴展 autoextend on
SYS@ bys3>alter tablespace sysaux autoextend on; ---此語句只能修改大文件表空間時使用。
alter tablespace sysaux autoextend on
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace SYSAUX
SYS@ bys3>alter database datafile '/u01/oradata/bys3/sysaux01.dbf' autoextend on; --使用此語句修改數據文件的屬性
Database altered.
SYS@ bys3>select file_name,autoextensible,increment_by from dba_data_files; ---修改後查詢數據文件屬性，已經更改為autoextend on
FILE_NAME AUT INCREMENT_BY
---------------------------------------- --- ------------
/u01/oradata/bys3/system01.dbf NO 0
/u01/oradata/bys3/sysaux01.dbf YES 1
/u01/oradata/bys3/undotbs01.dbf NO 0
/u01/oradata/bys3/user01.dbf NO 0
SYS@ bys3>select TABLESPACE_NAME,sum(bytes/1024/1024) free_mb from dba_free_space group by tablespace_name; --修改後查詢表空間剩余，SYSAUX已經自動擴展了一個區--64K. 詳見：http://blog.csdn.net/q947817003/article/details/11370881
TABLESPACE_NAME FREE_MB
------------------------------ ----------
SYSAUX .0625 ---這裏即擴展了一個extent,
UNDOTBS1 58.0625
USERS 48.6875
SYSTEM 155.375
SELECT * FROM dba_data_files WHERE TABLESPACE_NAME='APPS_TS_TX_DATA';
將原有表空間擴充檔案上限：
alter tablespace APPS_TS_TX_DATA datafile '/u01/install/APPS/data/a_txn_data01.dbf' RESIZE 10G;
新增表空間檔案：
alter tablespace APPS_TS_TX_DATA add datafile 'a_txn_data05.dbf' size 1024m autoextend on next 1024m maxsize 10G;
擴展原有表空間檔案容量上限：
alter database datafile '/u01/install/APPS/data/a_txn_data01.dbf' autoextend on next 1024m maxsize 10G;
alter database datafile '/u01/install/APPS/data/a_txn_data02.dbf' autoextend on next 1024m maxsize 10G;
alter database datafile '/u01/install/APPS/data/a_txn_data03.dbf' autoextend on next 1024m maxsize 10G;
alter database datafile '/u01/install/APPS/data/a_txn_data04.dbf' autoextend on next 1024m maxsize 10G;
alter database datafile '/u01/install/APPS/data/a_txn_data05.dbf' autoextend on next 1024m maxsize 10G;
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;
如何查看各個表空間佔用磁盤情況？
SQL> col tablespace format a20
SQL> select
b.file_id 文件ID號，
b.tablespace_name 表空間名，
b.bytes 字節數，
(b.bytes-sum(nvl(a.bytes，0))) 已使用，
sum(nvl(a.bytes，0)) 剩餘空間，
sum(nvl(a.bytes，0))/(b.bytes)*100 剩餘百分比
from dba_free_space a，dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name，b.file_id，b.bytes
order by b.file_id
如何知道數據褲中某個表所在的tablespace?
select tablespace_name from user_tables where table_name='TEST'；
select * from user_tables中有個字段TABLESPACE_NAME，（oracle）；
select * from dba_segments where …；
怎麼可以看到數據庫有多少個tablespace？
select * from dba_tablespaces;
如何查看數據文件放置的路徑？
col file_name format a50
select tablespace_name，file_id，bytes/1024/1024，file_name from dba_data_files order by file_id;
如何查看現有回滾段及其狀態？
SQL> col segment format a30
SQL> SELECT SEGMENT_NAME，OWNER，TABLESPACE_NAME，SEGMENT_ID，FILE_ID，STATUS FROM DBA_ROLLBACK_SEGS
如何知道表在表空間中的存儲情況？
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_nam;
