oracle表空間管理維護

1. 新表空間的建立
語法格式:
CREATE TABLESPACE 表空間名 DATAFILE 檔案識別符號[,檔案識別符號]...
[DEFAULT STORAGE(儲存配置引數)]  [ONLINE\OFFLINE];

其中:檔案識別符號=’檔名’[SIZE整數[K\M][REUSE]

2. 修改表空間配置
語法格式:
ALTER TABLESPCE 表空間名 (ADD DATAFILE 檔案識別符號[,檔案識別符號]...
\RENAME DATAFILE ’檔名’[,’檔名’]... TO ’檔名’[,’檔名’]...
\DEFAULT STORAGE(儲存配置引數)
\ONLINE\OFFLINE[NORMAL\IMMEDIATE]
\(BEGIN\END)BACKUP);

3. 刪除表空間
語法格式:
DROP TABLESPACE 表空間名[INCLUDING CONTENTS AND DATAFILES];

4. 檢查表空間使用情況
查詢永久表空間剩餘及其剩下空間百分比, sql如下:
SQL>select t1.tname "tablespace", t2.total "total/M", t1.free "free/M", t2.total-t1.free "used/M", (t1.free/t2.total)*100 free_percent from ( select sum(nvl(a.bytes,0))/(1024*1024) free, a.tablespace_name tname from dba_free_space a group by a.tablespace_name) t1, (select sum(b.bytes)/(1024*1024) total, b.tablespace_name tname from dba_data_files b group by b.tablespace_name) t2 where t1.tname=t2.tname;

tablespace               total/M     free/M      used/M       FREE_PERCENT
------------------------------  ----------  ---------- ---------- ------------
UNDOTBS1                  300   276.6875    23.3125    92.2291667
SYSAUX                       890   320.0625   569.9375   35.9620787
INDTBS                        110    109.875       .125         99.8863636
……………………………………………………………………
查詢臨時表空間剩餘, sql如下:
SQL>select tablespace_name,sum(BYTES_USED/1024/1024) "used/M",sum(BYTES_FREE/1024/1024) "free/M" from v$temp_space_header group by tablespace_name;

TABLESPACE_NAME    used/M     free/M
--------------------      ----------   ----------
TEMP                            105           995

5. 擴充套件表空間
經常會遇到表空間不足而達到Alert Level:  WARNING or CRITICAL
Using OMF files management on ASM, the datafiles by default are created with autoextending enabled on a maxsize of 32G. Calculating tablespace usage is based on how much autoextensible space is available.
首先檢視錶空間檔名、是否擴充套件、最大值等。
SQL>col file_name for a50
SQL>select file_name, autoextensible,maxbytes||'' from dba_data_files where tablespace_name='NETCOOL_DATA';

FILE_NAME                                                          AUT         MAXBYTES||''
--------------------------------------------------  -----------  -------------
+DATA/racdb/datafile/netcool_data.344.722943125    YES 34359721984

方法一、增加資料檔案
SQL>alter tablespace NETCOOL_DATA add datafile '+DATA';

Tablespace altered.

SQL>select file_name, autoextensible,maxbytes/1024/1024/1024 GB from dba_data_files where tablespace_name='NETCOOL_DATA';

FILE_NAME                                                             AUT      GB
--------------------------------------------------  ---  ----------
+DATA/racdb/datafile/netcool_data.341.778582069    YES 31.9999847
+DATA/racdb/datafile/netcool_data.344.722943125    YES 31.9999847

方法二、手動增加資料檔案尺寸並確保可擴充套件
SQL>alter database datafile '+DATA/racdb/datafile/netcool_data.344.722943125' resize 300M;

Database altered.

SQL>select file_name, autoextensible,bytes/1024/1024 Mb, STATUS from dba_data_files where tablespace_name='NETCOOL_DATA';

FILE_NAME                             AUT     MB STATUS
------------------------------------------------------------ --- ---------- ---------
+DATA/racdb/datafile/netcool_data.341.778582069          YES    100 AVAILABLE
+DATA/racdb/datafile/netcool_data.344.722943125          YES    300 AVAILABLE

如果相關資料檔案沒有自動擴充套件，啟用語句如下:
SQL>alter database datafile '+DATA/racdb/datafile/netcool_data.344.722943125' autoextend on maxsize 32000M;

6. 查詢資料庫預設永久表空間
SQL>select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';

修改預設永久表空間
SQL> alter database default tablespace NETCOOL_DATA;

Database altered.
<!-- @page { margin: 0.79in } P { margin-bottom: 0.08in } -