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