Oracle空間清理及表空間擴容

1.1.1Oracle空間清理及表空間擴容

1：查看錶空間使用率

SELECT a.tablespace_name "表空間名",

total / 1024 / 1024 "表空間大小單位M",

free / 1024 / 1024 "表空間剩餘大小單位M",

(total - free) / 1024 / 1024 "表空間使用大小單位M",

Round((total - free) / total, 4) * 100 "使用率 [[%]]"FROM (SELECT tablespace_name,Sum(bytes) free 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;

2：日終性能測試前對於一些類似於his表，settinit表都可以直接清理掉

------HS_HIS_DATA;HS_SETTINIT_DATA;HS_FIL_DATA 可以全部幹掉

------例如清理hs_settinit用戶，注意盡量使用truncate，不易產生碎片

這裡有必要提一下drop truncate 以及 delete 的區別

Truncate:其作用會刪除表所有的記錄同時釋放佔用的空間，期間不會產生碎片，不產生日誌，速度快，其缺點是不能回滾，刪除的數據不能恢復；

Delete：delete可以有where條件，而且可以回滾，不可以回收索引佔有的表空間

Drop：其作用將表的所有屬性全部幹掉，且對應表所佔用的空間全釋放掉

begin

for cur_tables in(select *

from user_tables)

loop

execute immediate 'truncate table hs_settinit.' || cur_tables.table_name;

end loop;

end;

3：查看用戶默認的表空間.

select username,default_tablespace from dba_users;

4：查看要擴展的表空間使用的數據文件路徑與名字

select * from dba_data_files where tablespace_name like 'HS_HIS_DATA%';

5：查詢oracle本次修改的數據文件及其編號。

select file#,name from v$datafile;

6：查找該數據文件的最大塊號。語句如下：

select max(block_id) from dba_extents where file_id=15;

select * fromdba_extentswhere file_id=15;

7：顯示SXSJ表空間每個數據塊的大小。

select tablespace_name,blocK_size,status,contents from dba_tablespaces where tablespace_name like 'HS_HIS_DATA%';---8192

計算該數據塊佔用的物理空間

語句如下：

select 472536*8/1024 from dual;

---執行結果為： 3691，就是3個G左右

例如更改hisdat.dbf表空間大小

alter database datafile '/ora10g/ora11g/ora11gdata/pbox21/hisdat.dbf' resize 1000M; 執行失敗

說明：之所以執行失敗是因為當初我們執行修改數據文件為1G，但是實際數據已經佔用了3G多了，不能修改為1G，只能比實際值大些。

select file#,name from v$datafile;

8：查一下佔用最大塊（segment 472536）的是什麼。語句如下：

select distinct owner, segment_name, segment_type,tablespace_name from dba_extents where file_id =15 and block_id=472536;

此時我們把這張表drop掉，記住一定要先備份表結構等信息，然後再重新導入表結構等數據信息，再重新查看這張表會發現佔用的塊減小了；如此反覆操作我們可以把佔用較大的快都減小，對應的表空間時間物理佔用空間也減小了，可以相應的優化資料庫空間；

佔用較大的快原因：產生的一些碎片等原因，例如DELETE 的話連HMW都不會降低的，其實數據文件在系統一級就是一個已經指定的文件；

9：查看錶空間是否為自動擴展（性能測試時盡量關閉表空間自動擴展）

select tablespace_name,file_name,autoextensible from dba_data_files ;

10：總結修改語句語法

開啟自動擴展功能語法：

alter database datafile '對應的數據文件路徑信息' autoextend on;

關閉自動擴展功能語法：

alter database datafile '/u01/app/oracle/oradata/orcl/assetdat.dbf' autoextend off;