共计 2698 个字符，预计需要花费 7 分钟才能阅读完成。
/*****************************************************************
原因：由于原导出数据库没有整理表空间其中主要包括两方面，一是用户产生太多的 DELETE，致使表的高位线（HWM）在很高的位置，所以尽管数据量很小，但是占据的表空间很大，二是索引没有重建，频繁的删除以及更新使得索引越来越大，REBUILD 索引是个很必要的事情
*****************************************************************/
–** 优先处理 CUX 客制化对象
–Step1
–Tablespace Summary
SELECT A.TABLESPACE_NAME,
A.TOTAL M_TOTAL,
NVL (B.USED, 0) M_USED,
NVL ((B.USED / A.TOTAL) * 100, 0) PCT_USED,
A.FILE_NAME
FROM (SELECT TABLESPACE_NAME,
SUM (BYTES) / (1024 * 1024) TOTAL,
WMSYS.WM_CONCAT (FILE_NAME) FILE_NAME
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, BYTES / (1024 * 1024) USED
FROM SYS.SM$TS_USED) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
–AND A.TABLESPACE_NAME LIKE ‘CUX%’
ORDER BY NVL ((B.USED / A.TOTAL) * 100, 0) DESC;
–Tablespace Objects Detail
SELECT A.TABLESPACE_NAME,
A.TOTAL M_TOTAL,
NVL (B.USED, 0) M_USED,
NVL ((B.USED / A.TOTAL) * 100, 0) PCT_USED,
A.FILE_NAME,
C.OJBECT_TYPE,
C.OJBECT_NAME,
C.M_OBJ_USED,
NVL ((C.M_OBJ_USED / A.TOTAL) * 100, 0) PCT_OBJ_USED
FROM (SELECT TABLESPACE_NAME,
SUM (BYTES) / (1024 * 1024) TOTAL,
WMSYS.WM_CONCAT (FILE_NAME) FILE_NAME
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, BYTES / (1024 * 1024) USED
FROM SYS.SM$TS_USED) B,
(SELECT TABLESPACE_NAME,
SEGMENT_NAME OJBECT_NAME,
SEGMENT_TYPE OJBECT_TYPE,
(BYTES / 1024 / 1024) M_OBJ_USED
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN (‘TABLE’, ‘INDEX’)) C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
ORDER BY NVL ((B.USED / A.TOTAL) * 100, 0) DESC;
–Step2
————————————-
– 释放高水位线 HWM
– 只对有清理过数据的表对象执行（耗时过长）
DECLARE
L_SQL1 VARCHAR2 (1000);
L_SQL2 VARCHAR2 (1000);
BEGIN
FOR R IN (SELECT (OWNER || ‘.’ || SEGMENT_NAME) OBJ
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = ‘TABLE’ AND TABLESPACE_NAME LIKE ‘CUX%’
AND SEGMENT_NAME IN(‘CUX_INV_ISSUE_OA_LOG’))
LOOP
L_SQL1 := ‘alter table ‘ || R.OBJ || ‘ enable row movement’;
L_SQL2 := ‘alter table ‘ || R.OBJ || ‘ shrink space’;
DBMS_OUTPUT.PUT_LINE (R.OBJ);
EXECUTE IMMEDIATE L_SQL1;
EXECUTE IMMEDIATE L_SQL2;
END LOOP;
END;
——————————-
/*
– 暂不应用
–Rebuild Index
DECLARE
L_SQL VARCHAR2 (1000);
BEGIN
FOR R IN (SELECT (OWNER||’.’||SEGMENT_NAME) OBJ
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = ‘INDEX’ AND TABLESPACE_NAME = ‘CUX_INDEX’)
LOOP
L_SQL := ‘alter index ‘ || R.OBJ || ‘ rebuild online’;
DBMS_OUTPUT.PUT_LINE (L_SQL);
EXECUTE IMMEDIATE L_SQL;
END LOOP;
END;
*/
– 删除表同时删除回收站 Shift+Delete
–DROP TABLE CUX.CUX_INV_ISSUE_OA_LOG_TEST1 PURGE;
–alter database datafile ‘filename’ resize size;
更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-07/133610.htm
