SYSAUX表空间中的WRH$_SQLTEXT，WRH$_SQL_PLAN 移动LOBSEGMENT类型

SELECT A.TABLE_NAME,   
       A.COLUMN_NAME,   
       B.SEGMENT_NAME,   
       B.SEGMENT_TYPE,   
       B.TABLESPACE_NAME,   
       B.BYTES / 1024 / 1024,   
       B.BLOCKS,   
       B.EXTENTS   
  FROM dba_LOBS A, dba_SEGMENTS B   
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME   
ORDER BY B.BYTES DESC;  
表名             字段名                                                   占用空间M
WRH$_SQLTEXT SQL_TEXT SYS_LOB0000006331C00004$$ LOBSEGMENT SYSAUX 2320 296960 263
WRH$_SQL_PLAN OTHER_XML SYS_LOB0000006339C00038$$ LOBSEGMENT SYSAUX 2264 289792 280

 

WRH$_SQLTEXT：保存的是快照期间的SQL。
SQL>SELECT * FROM WRH$_SQLTEXT;
   
WRH$_SQL_PLAN：保留了快照期间SQL的执行计划。
SQL>SELECT * FROM WRH$_SQL_PLAN

 

SQL> TRUNCATE TABLE WRH$_SQLTEXT;
Table truncated.

SQL> TRUNCATE TABLE WRH$_SQL_PLAN;
Table truncated.

 

--清除awr的历史记录,shared pool及buffer cache
 exec dbms_workload_repository.drop_snapshot_range(xxx,xxx);
 alter system flush shared_pool;
 alter system flush buffer_cache;

--清除dba_hist_sql_plan视图,实际上清除wrh$_sql_plan, wrh$_sqltext, wrh$_sqlstat
 truncate table wrh$_sql_plan;

--清除dba_hist_sql_sqltext以及dba_hist_sqlstat视图
 truncate table wrh$_sqltext;
 truncate table wrh$_sqlstat;

 select count(*) from dba_hist_sql_plan;

　　COUNT(*)

　　----------

　　0

 select count(*) from dba_hist_sqltext;

　　COUNT(*)

　　----------

　　0

  

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 543, high_snap_id => 544, dbid => 3580899532);

select * from dba_tablespaces
select 'ALTER TABLE '||owner||'.'||table_name||' MOVE TABLESPACE NNC_DATA01_NEW;'  from dba_tables where tablespace_name='NNC_DATA01'

select  'ALTER INDEX '||owner||'.'||INDEX_NAME||' REBUILD TABLESPACE NNC_DATA02_NEW;' from dba_indexes 

where owner='xxx' and tablespace_name='NNC_DATA02'


select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments 
where owner='xxx' and tablespace_name='NNC_DATA01'
and SEGMENT_TYPE='LOBSEGMENT';


SELECT A.TABLE_NAME,    
       A.COLUMN_NAME,    
       B.SEGMENT_NAME,    
       B.SEGMENT_TYPE,    
       B.TABLESPACE_NAME,    
       B.BYTES / 1024 / 1024,    
       B.BLOCKS,    
       B.EXTENTS    
  FROM dba_LOBS A, dba_SEGMENTS B    
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME   
and b.segment_name in (select SEGMENT_NAME
from dba_segments 
where owner='xxx' and tablespace_name='NNC_DATA01'
and SEGMENT_TYPE='LOBSEGMENT'); 



--ALTER TABLE NCV56.tablename MOVE TABLESPACE INDEX01 LOB(FILECONTENT) STORE AS(TABLESPACE INDEX01_NEW);
SELECT 'ALTER TABLE NCV56.'||A.TABLE_NAME||' MOVE TABLESPACE NNC_DATA01 LOB('||COLUMN_NAME||') STORE AS(TABLESPACE NNC_DATA01_NEW);'   
  FROM dba_LOBS A, dba_SEGMENTS B    
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME   
and b.segment_name in (select SEGMENT_NAME
from dba_segments 
where owner='xxx' and tablespace_name='NNC_DATA01'
and SEGMENT_TYPE='LOBSEGMENT')
ORDER BY B.BYTES DESC;