数据库监控告警,SYSTEM表空间使用率很高.检查发现LOB字段使用了很大的空间,定位到LOB字段属于SYS_EXPORT_FULL_%表,参考(Doc ID 1626201.1)步骤处理后,表空间使用率大幅下降.
查询使用空间比较多的对象
set linesize 500 pagesize 10
col owner for a20
col segment_name for a30
select owner,segment_name,round(sum(bytes/1024/1024/1024),2) GB from dba_segments where tablespace_name='SYSTEM' group by owner,segment_name order by 3;
OWNER SEGMENT_NAME GB
-------------------- ------------------------------ ----------
SYS SYS_LOB0001301589C00045$$ 6.19
SYS SYS_LOB0011275889C00045$$ 6.46
检查LOB对象属于什么对象
select owner,table_name from dba_lobs where SEGMENT_NAME='SYS_LOB0011275889C00045$$';
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS SYS_EXPORT_FULL_08
确定dump作业没有在运行
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
COL owner.object for a50
SELECT owner_name, job_name, rtrim(operation) "OPERATION",
rtrim(job_mode) "JOB_MODE", state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------ -----------------
SYS SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_02 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_03 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_04 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_05 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_06 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_07 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_08 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_09 EXPORT FULL NOT RUNNING 0
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------ -----------------
SYS SYS_EXPORT_FULL_10 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_11 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_12 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_FULL_13 EXPORT FULL NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0
SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------------------- -------------------------------------------
VALID 9766998 TABLE SYS.SYS_EXPORT_FULL_01
VALID 9865781 TABLE SYS.SYS_EXPORT_FULL_02
VALID 9875786 TABLE SYS.SYS_EXPORT_FULL_03
VALID 11162854 TABLE SYS.SYS_EXPORT_FULL_04
VALID 11164797 TABLE SYS.SYS_EXPORT_FULL_05
VALID 11249730 TABLE SYS.SYS_EXPORT_FULL_06
VALID 11273872 TABLE SYS.SYS_EXPORT_FULL_07
VALID 11275889 TABLE SYS.SYS_EXPORT_FULL_08
VALID 11277903 TABLE SYS.SYS_EXPORT_FULL_09
VALID 11281930 TABLE SYS.SYS_EXPORT_FULL_10
VALID 11304143 TABLE SYS.SYS_EXPORT_FULL_11
VALID 11312135 TABLE SYS.SYS_EXPORT_FULL_12
VALID 11314148 TABLE SYS.SYS_EXPORT_FULL_13
VALID 9374980 TABLE SYS.SYS_EXPORT_SCHEMA_01
VALID 9316834 TABLE SYS.SYS_EXPORT_SCHEMA_02
生成删除表的命令
SELECT 'DROP TABLE '||o.owner||'.'||object_name||' PURGE;'
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%';
将表删除后,SYSTEM表空间使用率使用率已经正常.
注意
不要删除正在运行的dump作业的主表,删除后dump作业会异常终止.
标签：FULL,name,SYSTEM,SYS,VALID,EXPORT,空间,TABLE,使用率 From： https://www.cnblogs.com/idealx/p/18024565