Oracle表空間維護總結

1. 概念：
表空間：最大的邏輯儲存檔案，與物理上的一個或多個資料檔案對應，每個資料庫至少擁有一個表空間，表空間的大小等於構成表空間的所有資料檔案的大小總和，用於儲存使用者在資料庫中儲存的所有內容。

2. 種類：
分為基本表空間、臨時表空間、大檔案表空間、非標準資料塊表空間和撤銷表空間。
基本表空間：用於儲存使用者的永久性資料
臨時表空間：排序、彙總時產生的臨時資料
大檔案表空間：儲存大型資料，如LOB
非標準資料塊表空間：建立資料塊大小不同的表空間
撤銷表空間：儲存事務的撤銷資料，在資料恢復時使用

3. 系統預設表空間：
system：系統表空間，用於儲存系統的資料字典、系統的管理資訊和使用者資料表等。
sysaux：輔助系統表空間，減少系統表空間負荷，體改系統作業效率，Oracle系統自動維護，一般不用於儲存資料結構。
temp：臨時表空間。
undotbsl：撤銷表空間，用於在自動撤銷管理方式下儲存撤銷資訊。
users：使用者表空間。

4. 表空間的狀態
表空間的狀態屬性主要有線上（online），離線（offline），只讀（read only）和讀寫（read write）。SQL> select 
-- 檢視錶空間的狀態
select tablespace_name,status from dba_tablespaces;
-- 更改表空間狀態
alter tablespace XXX offline/online/read only/read write;

5. 建立表空間語句：
create [temporary|undo]tablespace tablespace_name[datafile|tempfile] 'filename' size
size K|M[reuse] //已經存在是否指定reuse
[autoextend off|on //資料檔案是否自動擴充套件
[next number K|M maxsize unlimited|number K|M]
][……]
[mininum extent number K|M]
[blocksize number K] //初始化引數資料庫大小，只能用於持久表空間
[online|offline] //online表空間可用
[logging|nologging]
[force logging] 
[default storage storage] //設定預設儲存引數
[compress|nocompress] //壓縮資料段內數值
[premanent|temporary] //持久儲存資料庫物件|臨時
[extent management dictionary|local //資料字典管理方式|本地管理，一般本地
[autoallocate|uniform size number K|M]]
[segment space management auto|manual]; //表空間段的管理方式自動|手動

-- 建立臨時表空間：
create temporary tablespace XXXX tempfile 'XXXXXXXtemp.dbf' size 50m 
autoextend on next 50m maxsize 20480m extent management local;

-- 建立資料表空間：

create tablespace KMYQ datafile '/oradata/testdb2/KMYQ01.dbf' size 200m 
autoextend off
segment space management auto 
extent management local 
uniform size 4M;

-- 建立臨時表空間建立使用者並指定表空間：
create user XXXX identified by XXXXX default tablespace XXX temporary tablespace XXXX_temp;

6. 預設表空間：
初始狀態下（未修改）預設永久性表空間為system，臨時為temp
-- 查詢預設表空間：
select default_tablespace from user_users;

-- 查詢預設新使用者表空間與預設臨時表空間
SQL> col PROPERTY_VALUE for a40

select property_name,property_value
from database_properties where property_name
in ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

-- 修改預設臨時表空間:
alter database default tablespace XXXX;

7. 檢視錶空間物理檔案的名稱及大小 
SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
ORDER BY tablespace_name;

8. 檢視錶空間的使用情況

資料表空間使用率：
SELECT a.tablespace_name, 
a.bytes/(1024*1024) total_M, 
b.bytes/(1024*1024) used_M, 
c.bytes/(1024*1024) free_M, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name;

臨時表空間使用率：
SELECT temp_used.tablespace_name, 
total - used as "Free_M", 
total as "Total_M", 
round(nvl(total - used, 0) * 100 / total, 3) "Free percent" 
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used 
FROM GV_$TEMP_SPACE_HEADER 
GROUP BY tablespace_name) temp_used, 
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total 
FROM dba_temp_files 
GROUP BY tablespace_name) temp_total 
WHERE temp_used.tablespace_name = temp_total.tablespace_name;

9. 查詢表空間每天的使用情況

select a.name, b.* 
from v$tablespace a, 
(select tablespace_id, 
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime, 
max(tablespace_usedsize * 8 / 1024) used_size 
from dba_hist_tbspc_space_usage 
where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) > 
trunc(sysdate - 30) group by tablespace_id, 
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) order by 
tablespace_id, trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b 
where a.ts# = b.tablespace_id ;

10. 表空間擴容
-- 修改建立的資料檔案的大小
SQL> col file_name for a60
SQL> select file_name,bytes/1024/1024 from dba_data_files;
SQL> alter database datafile '/home/oracle/app/oradata/orcl/users01.dbf'resize 51M;
SQL> select file_name,bytes/1024/1024 from dba_data_files;

-- 增加表空間的資料檔案
SQL> alter tablespace andy add datafile '/home/oracle/app/oradata/orcl/andy02.dbf'size 1M 
autoextend on next 1m maxsize 2m ;

11. 刪除表空間
-- 刪除所有資料庫物件與刪除資料檔案
drop tablespace XXX including contents and contents;

12. 重新命名錶空間
alter tablespace tablespace_name rename to new_table_name;
alter tablespace andy rename to newandy;

13. 移動表空間的資料檔案
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'NEWANDY'; 
SQL> alter tablespace newandy offline;
[oracle@11g ~]$ mv /home/oracle/app/oradata/orcl/andy01.dbf /home/oracle/app/oradata/andy01.dbf
SQL> alter tablespace newandy rename datafile '/home/oracle/app/oradata/orcl/andy01.dbf' to '/home/oracle/app/oradata/andy01.dbf';
SQL> alter tablespace newandy online;
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'NEWANDY';

14. 修改表空間的自動擴充套件性
SQL> select tablespace_name,status,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
SQL> alter database datafile file_name autoextend off|on [next number K|M maxsize unlimited|number K|M]
SQL> select tablespace_name,status,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;