一、查看表实际使用情况
DECLARE
v_sql VARCHAR2(1000);
v_used NUMBER;
v_total NUMBER;
BEGIN
FOR cur IN (SELECT *
FROM (SELECT a.owner, a.segment_name,
round(SUM(a.bytes) / 1024 / 1024 / 1024) total_byte
FROM dba_segments a
WHERE a.tablespace_name = 'TBS_NOPART_11'
AND a.segment_name<>'ODS_CUST_GBCALLCDR_D_COPY'
GROUP BY a.owner, a.segment_name
ORDER BY round(SUM(a.bytes) / 1024 / 1024 / 1024) DESC)
WHERE rownum <= 10)
LOOP
v_sql := 'SELECT /*+ parallel(a)*/ COUNT(DISTINCT dbms_rowid.rowid_block_number(ROWID) ||
dbms_rowid.rowid_relative_fno(ROWID))
FROM ' || cur.owner || '.' || cur.segment_name || ' a';
EXECUTE IMMEDIATE v_sql
INTO v_used;
SELECT SUM(a.blocks)
INTO v_total
FROM dba_segments a
WHERE a.segment_name = cur.segment_name
AND a.owner = cur.owner;
dbms_output.put_line(cur.owner || '.' || cur.segment_name || ' 总大小 ' ||
cur.total_byte || ' total_blocks ' || v_total ||
' used_blocks ' || v_used);
END LOOP;
END;
二、调整表HWM的几种方法。
1、导出、导入数据
exp/imp expdp/impdp
2、TABLE MOVE(9I)
alter table table_name move ...
aleter table move 只是给表中所有数据搬到新的存储空间上,就相当于把A房间内乱七八糟的东西整理起来放到B房间里面然后再把A房间让出来给别人,这样你不但让出了整个A房间而且仍然只占用一个房间,但是你的东西(数据)也整齐了可能只占用B房间很少的一部分
也在同一表空间move——效果很好必须重建索引 , 可以直接在本tbs上. eg: alter table table_name move; 需要对index rebuild ;
3、SHRINK SPACE(10G)
alter table <tablename> enable row movement;
alter table <tablename> shrink space;