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;
- 重新编译失效对象
@?/rdbms/admin/utlrp.sql
