高水位线处理

1. 查询高水位情况

查询块大小

SELECT value
FROM   v$parameter
WHERE  name = 'db_block_size';

查询10G水位线上的对象

SELECT *
FROM   dba_extents
WHERE  tablespace_name = 'TBS_NAME'
       AND block_id >= 10 * 1024 * 1024 * 1024 / 8192
ORDER  BY block_id DESC;

2. 清理分区表

移动分区表

SELECT 'alter table '
       || owner
       || '.'
       || segment_name
       || ' move partition '
       || partition_name
       || ';'
FROM   dba_extents
WHERE  tablespace_name = 'TBS_NAME'
       AND block_id >= 1500000
       AND segment_type = 'TABLE PARTITION'
       AND segment_name = 'SEG_NAME'
ORDER  BY block_id DESC;

删除分区表

SELECT 'alter table '
       || owner
       || '.'
       || object_name
       || ' drop partition '
       || subobject_name
       || ';'
FROM   dba_objects
WHERE  object_name = '&TABLE_NAME'
       AND created < sysdate - 15
       AND object_type = 'TABLE PARTITION';

3. 移动表

SELECT 'alter table '
       || owner
       || '.'
       || segment_name
       || ' move;'
FROM   dba_extents
WHERE  tablespace_name = 'TBS_NAME'
       AND block_id >= 1500000
       AND segment_type = 'TABLE'
       AND segment_name = 'SEG_NAME'
ORDER  BY block_id DESC;

4. 移动lob字段

SELECT 'alter table  '
       || dl.owner
       || '.'
       || dl.table_name
       || ' move lob('
       || dl.COLUMN_NAME
       || ') store as (tablespace TBS_NAME);'
FROM   dba_extents de,
       dba_lobs dl
WHERE  de.tablespace_name = 'TBS_NAME'
       AND de.block_id >= 1500000
       AND de.segment_type = 'LOBSEGMENT'
       AND de.owner = dl.owner
       AND de.segment_name = dl.segment_name
ORDER  BY de.block_id DESC;

5. 重新编译失效对象

@?/rdbms/admin/utlrp.sql