平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“Oracle数据库清理用户及表空间图文教程”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

目录

• 1.背景
• 2. 实验清理
• 2.1 查询：清理前表空间
• 2.2 查询：清理前的数据文件
• 2.3 查询：清理前的对象
• 2.4 查询：清理前空闲空间
• 3.删除用户
• 3.1 删除用户
• 3.2 删除用户 cascade
• 4.删除后查询
• 4.1 查询：清理后表空间 dba_extents
• 4.2 查询：清理后的数据文件
• 4.3查询：清理后的对象
• 4.4 查询：清理后表空间 dba_free_space
• 5.批量清理其他用户
• 6.清理表空间及数据文件
• 6.1查询
• 6.2 方式一：缩小数据文件
• 6.3方式二：删除表空间及数据文件
• 总结

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清理用户及表空间内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多兼容脚本之家！

您可能感兴趣的文章:

• Oracle 11g如何清理数据库的历史日志详解
• Linux下Oracle归档日志自动清理脚本代码(sh)
• oracle自动清理archivelog文件的具体方法
• Oracle 如何规范清理v$archived_log记录实例详解
• Oracle日志定期清理
• 理解这一步时，oracle采用adrci清理日志文件的操作指南(trace文件，incident文件，listener log文件)
• oracle表空单清理常用代码段整理