Oracle数据库清理用户及表空间图文教程

俺是这个屯里土生土长的羊 · 发表于 2025-9-18 08:35:05

1.背景

今天需要清理一台服务器中之前的库，目前不再使用，以便释放空间。

如：清理 NH_MCRO_COLLECT 用户

2. 实验清理

2.1 查询：清理前表空间

SELECT --segment_name "表名",
       --segment_type "对象类型",
       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 查询：清理前的数据文件

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

2.3 查询：清理前的对象

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 ；

执行后报错：提示如下

原因：是因为该用户下有数据库对象时，删除该用户就需要使用drop user cascade.

3.2 删除用户 cascade

drop user nh_mcro_collect cascade;

本次共删除3个用户：

nh_mcro_collect、NH_MCRO_COLLECT_02、NH_MCRO_COLLECT_03

drop user NH_MCRO_COLLECT_02 cascade;

在plsql工具中点击右键删除，也一样。需要确认删除其 objects, 点否则没有任何操作。

4.删除后查询

4.1 查询：清理后表空间 dba_extents

SELECT --segment_name "表名",
       --segment_type "对象类型",
       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;

确实少了nh_mcro_colllect用户的表空间。

3个用户都删后，dba_extents 没有占用了

4.2 查询：清理后的数据文件

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

发现数据文件和之前一样，大小没有变化

4.3查询：清理后的对象

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

查询结果：为空。已清理对象

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%';

清理前后对比：

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.清理表空间及数据文件

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 = '你的表名'
;

发现都已清理

6.2 方式一：缩小数据文件

可以缩小数据文件或删除表空间

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;

注意：减小数据文件不能比已经使用的大小更小，所以要先查询已经使用的大小。

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方式二：删除表空间及数据文件

drop tablespace NH_MCRO_ASSECT_DATA including contents and datafiles cascade constraint;

此方式不可逆，必须小心执行。通过使用DROP TABLESPACE命令，可以删除不再需要的表空间及其内容，同时可以选择删除关联的数据文件和相关约束。确保在删除表空间之前，备份数据并评估其对业务的影响。

最终结果：

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;

实验验证：ok

[oracle] Oracle数据库清理用户及表空间图文教程

目录