ORACLE数据库SYSTEM表空间无用资源释放
公司的现有项目使用ORACLE 11g数据库,在长期的生产过程中,积累了大量的数据内容和数据表。客户这边每天又在跑导出任务(EXPDP)备份数据,经常遇到SYSTEM表空间不足导致导出任务死机,这样就不得不人工手动关闭导出任务。由于接触ORACLE的时间不长,早期一直使用扩sytem表空间数据文件的方法来应对,最近越来越频繁死机,没办法还是觉得应该好好处理一下,看看是什么情况导致的。
问题描述
EXPORT任务导出死机这个问题,以前一直没有重视,现在才发现以前手动关闭的任务太多空间没有释放,所以现在才遇到了SYSTEM表空间一直吃紧,导致做大量SQL运算的过程和写库的过程中客户端程序异常。
解决方式:
一、EXPORT死机原因很明确,就是SYSTEM表空间不足了,既然导出任务有问题,那就先关闭导出任务,下面是关闭导出任务的步骤:
先那些任务可能是异常后手动关闭或者没有关闭的吧,我是在Windows Server 2012系统中,在CMD窗口操作:
1.设置对应的实例后,使用SYS/SYSTEM登录数据库。
conn sys/password as sysdba;
服务器上直接使用:sqlplus / as sysdba;
2.查找正在运行的Data Pump作业,可以通过执行以下SQL查询来查找:
SELECT job_name, state FROM dba_datapump_jobs;
这将列出所有Data Pump作业及其状态。找到你想要终止的作业的job_name和state。如果状态是EXECUTING,则表示作业正在运行。
3.使用expdp命令并附加到该作业上,(未登录库状态输入)命令格式如下:
expdp system/密码 attach=作业名
4.然后使用STOP_JOB命令来终止它。在expdp的交互模式下,你可以使用以下命令:
>STOP_JOB=IMMEDIATE
5.确认作业终止:终止作业后,再次执行SQL查询来确认作业状态已经改变,不再是EXECUTING。
SELECT job_name, state FROM dba_datapump_jobs;
二、删除异常任务占用的表空间
导出任务临时数据一般是在SYSTEM表空间中,这也是我们需要处理的核心问题。
1.查询表空间使用情况
SELECT * FROM
(SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,ROUND(BYTES/1024/1024/1024,2) AS SEG_SIZE_GB
FROM DBA_SEGMENTS DS
WHERE TABLESPACE_NAME = ‘SYSTEM’
ORDER BY BYTES DESC
) WHERE ROWNUM <= 100
SEGMENT_TYPE,表示对应的数据段类型,如果是LOBSEGMENT则为二进制对象,另外还有TABLE(表对象),INDEX(索引对象)…。其实主要内容就是这三类,尤其是当业务数据中有大量的BLOB或者CLOB字段时,LOBSEGMENT对象将占据大量空间。
此图可以看到,这些LOBSEGMENT对象已经干到20多G了,难怪我加了几十个SYSTEM表空间文件了。
2.如果是TABLE对象,那么SEGMENT_NAME就对应其表名就行了。如果是LOBSEGMENT或INDEX对象,那么还要去查其对应的表名:
SELECT owner, table_name, column_name FROM dba_lobs WHERE segment_name = ‘SYS_LOBxxxxxxxxxx$$’;
“SYS_LOBxxxxxxxxxx$$”为具体要查询的SEMENT_NAME,如我的数据查询就如下:
查到它的表名是XXXXXXX0910800
3.根据前面查找到的异常表任务可以将表名与任务名对应上,或者能直接确定这个表就是不需要的内容了,那么就可以运行删除语句将表删除:
3.1截断表或直接删除表:可以使用 TRUNCATE TABLE 命令来快速删除表中的所有行,并释放 LOBSEGMENT 占用的空间:
TRUNCATE TABLE table_name; ------截断表(只清空内容等)
DROP TABLE table_name; -------删除表(特别明确可以删除,其会将索引、表结构等都删除)
3.2清理回收站:如果表被截断或删除,相应的空间可能被移动到回收站。执行 PURGE 命令来清除回收站并释放空间:
PURGE TABLE table_name;
或者清空整个回收站:
PURGE RECYCLEBIN;
结语
到这里就基本结束了,这都是实际项目中根据具体情况来处理数据的,可能跟大家的具体需求不太一样,大家按需采取即可。这里再次提醒: