Oracle数据库清理用户及表空间图文教程
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1.背景</a></li><li><a href="#_label1">2. 实验清理</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">2.1 查询：清理前表空间</a></li><li><a href="#_lab2_1_1">2.2 查询：清理前的数据文件</a></li><li><a href="#_lab2_1_2">2.3 查询：清理前的对象</a></li><li><a href="#_lab2_1_3">2.4 查询：清理前空闲空间</a></li></ul><li><a href="#_label2">3.删除用户</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_4">3.1 删除用户</a></li><li><a href="#_lab2_2_5">3.2 删除用户 cascade</a></li></ul><li><a href="#_label3">4.删除后查询</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_6">4.1 查询：清理后表空间 dba_extents</a></li><li><a href="#_lab2_3_7">4.2 查询：清理后的数据文件</a></li><li><a href="#_lab2_3_8">4.3查询：清理后的对象</a></li><li><a href="#_lab2_3_9">4.4 查询：清理后表空间 dba_free_space</a></li></ul><li><a href="#_label4">5.批量清理其他用户</a></li><ul class="second_class_ul"></ul><li><a href="#_label5">6.清理表空间及数据文件</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_10">6.1查询</a></li><li><a href="#_lab2_5_11">6.2 方式一：缩小数据文件</a></li><li><a href="#_lab2_5_12">6.3方式二：删除表空间及数据文件</a></li></ul><li><a href="#_label6">总结</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>1.背景</h2><p>今天需要清理一台服务器中之前的库，目前不再使用，以便释放空间。</p>
<blockquote><p>如：清理 NH_MCRO_COLLECT 用户</p></blockquote>
<p class="maodian"><a name="_label1"></a></p><h2>2. 实验清理</h2>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>2.1 查询：清理前表空间</h3>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275057.png" /></p>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>2.2 查询：清理前的数据文件</h3>
<div class="jb51code"><pre class="brush:sql;">select * from dba_data_files t where t.tablespace_namelike 'NH_MCRO_COLLECT_DATA%'</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275066.png" /></p>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>2.3 查询：清理前的对象</h3>
<div class="jb51code"><pre class="brush:sql;">select object_type,count(*) from all_objects where owner='NH_MCRO_COLLECT' group by object_type;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275051.png" /></p>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>2.4 查询：清理前空闲空间</h3>
<div class="jb51code"><pre class="brush:sql;">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;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275044.png" /></p>
<p class="maodian"><a name="_label2"></a></p><h2>3.删除用户</h2>
<p class="maodian"><a name="_lab2_2_4"></a></p><h3>3.1 删除用户</h3>
<div class="jb51code"><pre class="brush:sql;">drop user nh_mcro_collect ；</pre></div>
<p>执行后报错：提示如下</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275092.png" /></p>
<p>原因：是因为该用户下有数据库对象时，删除该用户就需要使用drop user cascade.</p>
<p class="maodian"><a name="_lab2_2_5"></a></p><h3>3.2 删除用户 cascade</h3>
<div class="jb51code"><pre class="brush:sql;">drop user nh_mcro_collect cascade;</pre></div>
<blockquote><p>本次共删除3个用户：</p>
<p>nh_mcro_collect、NH_MCRO_COLLECT_02、NH_MCRO_COLLECT_03</p></blockquote>
<p>drop user NH_MCRO_COLLECT_02 cascade;</p>
<p>在plsql工具中点击右键删除，也一样。需要确认删除其 objects, 点否则没有任何操作。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275024.png" /></p>
<p class="maodian"><a name="_label3"></a></p><h2>4.删除后查询</h2>
<p class="maodian"><a name="_lab2_3_6"></a></p><h3>4.1 查询：清理后表空间 dba_extents</h3>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275077.png" /></p>
<p>确实少了nh_mcro_colllect用户的表空间。</p>
<p>3个用户都删后，dba_extents 没有占用了</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275079.png" /></p>
<p class="maodian"><a name="_lab2_3_7"></a></p><h3>4.2 查询：清理后的数据文件</h3>
<div class="jb51code"><pre class="brush:sql;">select * from dba_data_files t where t.tablespace_namelike 'NH_MCRO_COLLECT_DATA%'</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275049.png" /></p>
<p>发现数据文件和之前一样，大小没有变化</p>
<p class="maodian"><a name="_lab2_3_8"></a></p><h3>4.3查询：清理后的对象</h3>
<div class="jb51code"><pre class="brush:sql;">select object_type,count(*) from all_objects where owner='NH_MCRO_COLLECT' group by object_type;</pre></div>
<p>查询结果：为空。 已清理对象</p>
<p class="maodian"><a name="_lab2_3_9"></a></p><h3>4.4 查询：清理后表空间 dba_free_space</h3>
<div class="jb51code"><pre class="brush:sql;">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%';</pre></div>
<p>清理前后对比： </p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275084.png" /></p>
<p class="maodian"><a name="_label4"></a></p><h2>5.批量清理其他用户</h2>
<div class="jb51code"><pre class="brush:sql;">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%'
</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>6.清理表空间及数据文件</h2>
<p class="maodian"><a name="_lab2_5_10"></a></p><h3>6.1查询</h3>
<div class="jb51code"><pre class="brush:sql;">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 = '你的表名'
;</pre></div>
<p>发现都已清理</p>
<p class="maodian"><a name="_lab2_5_11"></a></p><h3>6.2 方式一：缩小数据文件</h3>
<p>可以缩小数据文件 或删除表空间</p>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<blockquote><p> 注意：减小数据文件不能比已经使用的大小更小，所以要先查询已经使用的大小。</p></blockquote>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275093.png" /></p>
<p>批量缩小表空间：</p>
<div class="jb51code"><pre class="brush:sql;">select 'alter database datafile '''||T.file_name||''' resize 100M;' AA,T.*
from dba_data_files t
where t.tablespace_namelike 'NH_MCRO%' AND T.tablespace_name <>'NH_MCRO_FINANCE_DATA';</pre></div>
<p class="maodian"><a name="_lab2_5_12"></a></p><h3>6.3方式二：删除表空间及数据文件</h3>
<div class="jb51code"><pre class="brush:sql;">drop tablespace NH_MCRO_ASSECT_DATA including contents and datafiles cascade constraint;</pre></div>
<p>此方式不可逆，必须小心执行。通过使用<code>DROP TABLESPACE</code>命令，可以删除不再需要的表空间及其内容，同时可以选择删除关联的数据文件和相关约束。确保在删除表空间之前，备份数据并评估其对业务的影响。</p>
<p>最终结果：</p>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091808275077.png" /></p>
<p>实验验证：ok</p>
<p class="maodian"><a name="_label6"></a></p><h2>总结</h2>
頁:
[1]