oracle expdp导致system表空间满

今天下午，项目经理反馈有套11204版本数据库无法使用了，立刻登录检查环境发现SYSTEM表空间使用率99.99%了

TABLESPACE_NAME MAXSIZE_MB ACTUALSIZE_MB USED_MB FREESPACE_MB SPACE USAGE ----------------- ---------- ------------- ---------- ------------ ------------- SYSTEM 32767 32767 32766 1 99.99%

到这里以为是审计数据太大导致的，但查询发现没开审计，如下图，第一次遇到该种情况

SYS > show parameter audit_trail

NAME TYPE VALUE ------------- ------- ---------- audit_trail string NONE SYS> SYS > select count(*) sum from AUD$;

SUM ---------- 0

查看SYSTEM表空间下面大表

SQL> set pagesize 199 linesize 199; col SEGMENT_NAME for a30; select * from (Select Segment_Name,Sum(bytes)/1024/1024/1024 From dba_Extents where TABLESPACE_NAME=SYSTEM Group By Segment_Name order by 2 desc ) WHERE ROWNUM <= 10 ;

SEGMENT_NAME SUM(BYTES)/1024/1024/1024 ------------------------------ ------------------------- SYS_LOB0000261488C00045$$ .432617188 SYS_LOB0000269830C00045$$ .432617188 SYS_LOB0000275177C00045$$ .430664063 SYS_LOB0000270224C00045$$ .4296875 SYS_LOB0000264343C00045$$ .4296875 SYS_LOB0000260763C00045$$ .4296875 SYS_LOB0000274880C00045$$ .4296875 SYS_LOB0000268850C00045$$ .4296875 SYS_LOB0000259802C00045$$ .4296875 SYS_LOB0000277331C00045$$ .4296875

10 rows selected.

继续排查，发现应该expdp备份失败导致的问题

SQL>select owner,table_name from dba_lobs where SEGMENT_NAME=SYS_LOB0000261488C00045$$;

OWNER TABLE_NAME ------------------------------ ---------------------------------------- SYS SYS_EXPORT_FULL_46

查看expdp备份的log，确实是备份失败导致的

[root@hydb1 ~]# tail -30 /tmp/expdp_orcl20230615.log ORA-39171: Job is experiencing a resumable wait. ORA-01691: unable to extend lob segment SYS.SYS_LOB0000277473C00045$$ by 1024 in tablespace SYSTEM ORA-39171: Job is experiencing a resumable wait. ORA-01691: unable to extend lob segment SYS.SYS_LOB0000277473C00045$$ by 1024 in tablespace SYSTEM ORA-39171: Job is experiencing a resumable wait. ORA-01691: unable to extend lob segment SYS.SYS_LOB0000277473C00045$$ by 1024 in tablespace SYSTEM ORA-39171: Job is experiencing a resumable wait. ORA-01691: unable to extend lob segment SYS.SYS_LOB0000277473C00045$$ by 1024 in tablespace SYSTEM ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CREATE_OBJECT_ROWS [TABLE] TABLE:"XGFW"."STD_TS_ZSJX" ORA-30032: the suspended (resumable) statement has timed out ORA-01691: unable to extend lob segment SYS.SYS_LOB0000277473C00045$$ by 1024 in tablespace SYSTEM

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.KUPW$WORKER", line 9721

----- PL/SQL Call Stack ----- object line object handle number name 0x42d2b08d0 21979 package body SYS.KUPW$WORKER 0x42d2b08d0 9742 package body SYS.KUPW$WORKER 0x42d2b08d0 8638 package body SYS.KUPW$WORKER 0x42d2b08d0 11712 package body SYS.KUPW$WORKER 0x42d2b08d0 2808 package body SYS.KUPW$WORKER 0x42d2b08d0 10422 package body SYS.KUPW$WORKER 0x42d2b08d0 1824 package body SYS.KUPW$WORKER 0x3c7e83b50 2 anonymous block

Job "SYS"."SYS_EXPORT_FULL_70" stopped due to fatal error at Thu Jun 15 21:42:28 2023 elapsed 0 02:02:22

把所有备份失败所产生的表清理干净，释放了SYSTEM表空间

SQL> drop table sys.SYS_EXPORT_FULL_01 purge; .......... .......... .......... SQL> drop table sys.SYS_EXPORT_FULL_70 purge;

查看定时任务，确实sys用户备份的

expdp "/ as sysdba" directory=dir_dump dumpfile=orcl_20230609_022001.dmp logfile=expdp_orcl_"$DAY".log full=y