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;