有些小白在操作數(shù)據(jù)庫時(shí)隨便新建表空間，并且喜歡直接設(shè)置最大值把表空間文件直接設(shè)置成32G，而后在導(dǎo)庫的時(shí)候又沒進(jìn)行正確操作導(dǎo)致新建的表空間并未使用，而把數(shù)據(jù)又全部導(dǎo)入users表空間了，這樣就造成數(shù)據(jù)庫服務(wù)器磁盤空間的浪費(fèi)，對于這種情況有兩種解決方案，把表空間減小或者直接刪除。

首先附一個(gè)查詢表空間使用率的SQL吧（需要有dba權(quán)限的用戶）：

select a.tablespace_name,
       total,
       free,
       total - free as used,
       substr(free / total * 100, 1, 5) as "FREE%",
       substr((total - free) / total * 100, 1, 5) as "USED%"
  from (select tablespace_name, sum(bytes) / 1024 / 1024 as total
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) / 1024 / 1024 as free
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by a.tablespace_name;

此條SQL可以非常直觀的看到數(shù)據(jù)庫中各個(gè)表空間的實(shí)時(shí)使用率情況，在數(shù)據(jù)庫導(dǎo)庫的時(shí)候也可以定時(shí)刷新進(jìn)行查看（但不包括臨時(shí)表空間）。

一、修改表空間數(shù)據(jù)文件大小

1、查詢表空間數(shù)據(jù)文件

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name

2、找到表空間對應(yīng)的數(shù)據(jù)文件后對數(shù)據(jù)文件的大小進(jìn)行調(diào)整

alter database datafile '/oracle/oradata/ora16/DATA01.dbf' resize 100m  --注意替換表空間路徑

在修改表空間大小的時(shí)候有可能會報(bào)錯(cuò)，報(bào)錯(cuò)信息**“ORA-03297: 文件包含在請求的 RESIZE 值以外使用的數(shù)據(jù)”**，由于表空間中數(shù)據(jù)并非按順序分布，因此在刪減掉的表空間中可能也存在數(shù)據(jù)，就會有面的報(bào)錯(cuò)信息，這個(gè)問題的解決方案我以后會在博客中更新；

臨時(shí)的替代解決方案就是將表空間大小的數(shù)值稍微改大點(diǎn)重試，比如由10G修改為100M時(shí)報(bào)錯(cuò)，可以先修改為500M，如果不報(bào)錯(cuò)就再減小…

二、刪除數(shù)據(jù)庫表空間及文件

刪除表空間及數(shù)據(jù)文件的SQL：

drop tablespace XXX including contents and datafiles;

刪除表空間之前記得斷開所有用戶連接，否則無法刪除，會報(bào)錯(cuò)。

刪除表空間之后在數(shù)據(jù)庫服務(wù)器上使用df -lh(Linux)/df -g(aix)上查看磁盤空間容量發(fā)現(xiàn)和刪除前的是一樣的，并沒有變化。

用lsof -n | grep deleted找到剛才刪除的dbf文件（后邊會有一個(gè)deleted的標(biāo)識），查看對應(yīng)的pid，直接kill掉就可以了。

這是因?yàn)檫@些被刪除的表空間數(shù)據(jù)文件還被數(shù)據(jù)庫進(jìn)程所持有，在進(jìn)程關(guān)閉之前磁盤空間是不會釋放的，如果想要驗(yàn)證該方法的可用性把數(shù)據(jù)庫重啟一次就可以了，數(shù)據(jù)庫重啟的效果等同于使用kill命令。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評論