Oracle數(shù)據(jù)庫清理用戶及表空間圖文教程

更新時(shí)間：2025年09月18日 08:35:05 作者：風(fēng)123456789～

在Oracle數(shù)據(jù)庫中,刪除用戶和表空間是一個(gè)常見的操作,但需要注意一些步驟和細(xì)節(jié),下面這篇文章主要介紹了Oracle數(shù)據(jù)庫清理用戶及表空間的相關(guān)資料,需要的朋友可以參考下

1.背景

今天需要清理一臺(tái)服務(wù)器中之前的庫，目前不再使用，以便釋放空間。

如：清理 NH_MCRO_COLLECT 用戶

2. 實(shí)驗(yàn)清理

2.1 查詢：清理前表空間

SELECT --segment_name "表名",
       --segment_type "對(duì)象類型",
       T.owner,T.tablespace_name,
       sum(bytes) / 1024 / 1024 "占用空間（MB）" --select *
  FROM dba_extents t
 WHERE 1=1-- t.tablespace_name like 'NH_MCRO%' 
 AND (t.owner like 'NH_MCRO_COLLECT%' or t.tablespace_name like 'NH_MCRO_COLLECT_%')
 --AND segment_name = 'NHTC_SUBJECT_INFO'
 GROUP BY T.owner,T.tablespace_name
    --,segment_name,segment_type
 ORDER BY 1;

2.2 查詢：清理前的數(shù)據(jù)文件

select * from dba_data_files t where t.tablespace_name  like 'NH_MCRO_COLLECT_DATA%'

2.3 查詢：清理前的對(duì)象

select object_type,count(*) from all_objects where owner='NH_MCRO_COLLECT' group by object_type;

2.4 查詢：清理前空閑空間

SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
     D.TOT_GROOTTE_MB "表空間大小(M)",
     D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
     TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",
     F.TOTAL_BYTES "空閑空間(M)",
     F.MAX_BYTES "最大塊(M)"
  FROM (SELECT TABLESPACE_NAME,
       ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
        ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
        FROM SYS.DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) F,
        (SELECT DD.TABLESPACE_NAME,
         ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
      FROM SYS.DBA_DATA_FILES DD
      GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;

3.刪除用戶

3.1 刪除用戶

drop user nh_mcro_collect ；

執(zhí)行后報(bào)錯(cuò)：提示如下

原因：是因?yàn)樵撚脩粝掠袛?shù)據(jù)庫對(duì)象時(shí)，刪除該用戶就需要使用drop user cascade.

3.2 刪除用戶 cascade

drop user nh_mcro_collect cascade;

本次共刪除3個(gè)用戶：

nh_mcro_collect、NH_MCRO_COLLECT_02、NH_MCRO_COLLECT_03

drop user NH_MCRO_COLLECT_02 cascade;

在plsql工具中點(diǎn)擊右鍵刪除，也一樣。需要確認(rèn)刪除其 objects, 點(diǎn)否則沒有任何操作。

4.刪除后查詢

4.1 查詢：清理后表空間 dba_extents

SELECT --segment_name "表名",
       --segment_type "對(duì)象類型",
       T.owner,T.tablespace_name,
       sum(bytes) / 1024 / 1024 "占用空間（MB）" --select *
  FROM dba_extents t
 WHERE 1=1-- t.tablespace_name like 'NH_MCRO%' 
 AND (t.owner like 'NH_MCRO_COLLECT%' or t.tablespace_name like 'NH_MCRO_COLLECT_%')
 --AND segment_name = 'NHTC_SUBJECT_INFO'
 GROUP BY T.owner,T.tablespace_name
    --,segment_name,segment_type
 ORDER BY 1;

確實(shí)少了nh_mcro_colllect用戶的表空間。

3個(gè)用戶都刪后，dba_extents 沒有占用了

4.2 查詢：清理后的數(shù)據(jù)文件

select * from dba_data_files t where t.tablespace_name  like 'NH_MCRO_COLLECT_DATA%'

發(fā)現(xiàn)數(shù)據(jù)文件和之前一樣，大小沒有變化

4.3查詢：清理后的對(duì)象

select object_type,count(*) from all_objects where owner='NH_MCRO_COLLECT' group by object_type;

查詢結(jié)果：為空。已清理對(duì)象

4.4 查詢：清理后表空間 dba_free_space

SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
     D.TOT_GROOTTE_MB "表空間大小(M)",
     D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
     TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",
     F.TOTAL_BYTES "空閑空間(M)",
     F.MAX_BYTES "最大塊(M)"
  FROM (SELECT TABLESPACE_NAME,
       ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
        ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
        FROM SYS.DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) F,
        (SELECT DD.TABLESPACE_NAME,
         ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
      FROM SYS.DBA_DATA_FILES DD
      GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 and d.TABLESPACE_NAME like 'NH_MCRO%';

清理前后對(duì)比：

5.批量清理其他用戶

select T.username,T.default_tablespace,'DROP USER '||username||'   cascade;'  AA
from dba_users t 
where t.username like 'NH_MCRO%' 
AND T.username NOT LIKE 'NH_MCRO_FINANCE%'

6.清理表空間及數(shù)據(jù)文件

6.1查詢

SELECT T.owner,table_name, tablespace_name
FROM dba_tables T
WHERE tablespace_name like 'NH_M%'
AND T.tablespace_name<>'NH_MCRO_FINANCE_DATA';

SELECT segment_name AS table_name, tablespace_name
FROM dba_segments T
WHERE T.tablespace_name<>'NH_MCRO_FINANCE_DATA'
AND tablespace_name like 'NH_M%'
--segment_type = 'TABLE' AND segment_name = '你的表名'
;

發(fā)現(xiàn)都已清理

6.2 方式一：縮小數(shù)據(jù)文件

可以縮小數(shù)據(jù)文件或刪除表空間

alter database datafile '/home/u01/app/oracle/oradata/ORCL/orclpdb1/nh_mcro_collect_data001.dbf' resize 100M;

或刪除

drop tablespace CCDD01 including contents and datafiles cascade constraint;

注意：減小數(shù)據(jù)文件不能比已經(jīng)使用的大小更小，所以要先查詢已經(jīng)使用的大小。

SELECT file_name,a.file_id, SUM(a.bytes)/1024/1024 AS MB
        FROM dba_extents a JOIN dba_data_files b ON a.file_id=b.file_id GROUP BY file_name,a.file_id;

批量縮小表空間：

select 'alter database datafile '''||T.file_name||''' resize 100M;' AA,T.* 
from dba_data_files t 
where t.tablespace_name  like 'NH_MCRO%' AND T.tablespace_name <>'NH_MCRO_FINANCE_DATA';

6.3方式二：刪除表空間及數(shù)據(jù)文件

drop tablespace NH_MCRO_ASSECT_DATA including contents and datafiles cascade constraint;

此方式不可逆，必須小心執(zhí)行。通過使用DROP TABLESPACE命令，可以刪除不再需要的表空間及其內(nèi)容，同時(shí)可以選擇刪除關(guān)聯(lián)的數(shù)據(jù)文件和相關(guān)約束。確保在刪除表空間之前，備份數(shù)據(jù)并評(píng)估其對(duì)業(yè)務(wù)的影響。

最終結(jié)果：

SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
     D.TOT_GROOTTE_MB "表空間大小(M)",
     D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
     TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",
     F.TOTAL_BYTES "空閑空間(M)",
     F.MAX_BYTES "最大塊(M)"
  FROM (SELECT TABLESPACE_NAME,
       ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
        ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
        FROM SYS.DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) F,
        (SELECT DD.TABLESPACE_NAME,
         ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
      FROM SYS.DBA_DATA_FILES DD
      GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;

實(shí)驗(yàn)驗(yàn)證：ok