Created
June 14, 2016 13:49
-
-
Save greenkey/4995de0bd65661dbd46a5140a3a3ce4c to your computer and use it in GitHub Desktop.
Shrink an oracle tablespace
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
It is not possible to shrink a tablespace that's being used, in order to reclaim the unused space you can use this script (remember to change the value of OLD_TBSP variable) | |
The script essentially does the following: | |
- create a new tablespace appending "_TMP" at the end, it's going to use a datafile with the same name, adding (or incrementing) an id; | |
- move all the object from the old tablespace to the new one; | |
(this means that on the filesystem there should be enough space for the new datafile, consider also the extra space for the index rebuild!) | |
- prints the commands to execute in order to drop the old tablespace and rename the new. | |
At the end it is possible that the space on the filesystem is not freed, follow the instructions here: http://serverfault.com/questions/501963/how-to-recover-free-space-on-deleted-files-without-restarting-the-referencing-pr | |
*/ | |
DECLARE | |
EXEC_IMM VARCHAR(255) := ''; | |
OLD_TBSP VARCHAR(80) := 'TABLE_SPACE_TO_BE_SHRINKED'; | |
NEW_TBSP VARCHAR(80); | |
TBSP_FILE VARCHAR(250); | |
TBSP_FILE_ID NUMBER; | |
BEGIN | |
NEW_TBSP := OLD_TBSP || '_TMP'; | |
SELECT | |
FILE_NAME, | |
nvl(regexp_replace(TBSP_FILE, '^(.*[^0-9])(_([0-9]+))*(\.[a-z]+)$', '\3'), 0) + 1 | |
INTO TBSP_FILE, TBSP_FILE_ID | |
FROM DBA_DATA_FILES | |
WHERE TABLESPACE_NAME = OLD_TBSP; | |
SELECT regexp_replace(TBSP_FILE, '^(.*[^0-9])(_([0-9]+))*(\.[a-z]+)$', '\1_' || TBSP_FILE_ID || '\4') | |
INTO TBSP_FILE | |
FROM DUAL; | |
BEGIN | |
EXECUTE IMMEDIATE 'CREATE TABLESPACE ' || NEW_TBSP || ' DATAFILE ''' || TBSP_FILE || | |
''' SIZE 100M AUTOEXTEND ON NEXT 100M'; | |
EXCEPTION | |
WHEN OTHERS THEN | |
DBMS_OUTPUT.put_line(' WARNING: tablespace might already exist '); | |
END; | |
FOR SEG IN (SELECT DISTINCT | |
nvl(I.TABLE_OWNER, S.OWNER) AS OWNER, | |
S.SEGMENT_NAME, | |
S.SEGMENT_TYPE, | |
S.PARTITION_NAME, | |
nvl(L.TABLE_NAME, I.TABLE_NAME) AS TABLE_NAME, | |
nvl(L.COLUMN_NAME, C.COLUMN_NAME) AS COLUMN_NAME, | |
P.PARTITION_NAME AS REAL_PARTITION_NAME | |
FROM DBA_SEGMENTS S | |
LEFT OUTER JOIN DBA_LOBS L | |
ON L.OWNER = S.OWNER AND L.SEGMENT_NAME = S.SEGMENT_NAME | |
LEFT OUTER JOIN DBA_LOB_PARTITIONS P | |
ON P.TABLE_OWNER = L.OWNER AND P.TABLE_NAME = L.TABLE_NAME AND P.COLUMN_NAME = L.COLUMN_NAME | |
AND S.PARTITION_NAME = P.LOB_PARTITION_NAME | |
LEFT OUTER JOIN DBA_INDEXES I | |
ON I.OWNER = S.OWNER AND I.INDEX_NAME = S.SEGMENT_NAME | |
LEFT OUTER JOIN DBA_LOBS C | |
ON C.INDEX_NAME = I.INDEX_NAME | |
WHERE S.TABLESPACE_NAME = OLD_TBSP AND S.SEGMENT_NAME NOT LIKE 'BIN$%' | |
AND S.SEGMENT_TYPE NOT IN ('LOBINDEX', 'INDEX PARTITION') | |
ORDER BY S.SEGMENT_TYPE DESC) LOOP | |
DBMS_OUTPUT.put_line(SEG.SEGMENT_TYPE || ' ' || SEG.SEGMENT_NAME); | |
IF SEG.SEGMENT_TYPE = 'INDEX' | |
THEN | |
EXEC_IMM := 'ALTER INDEX ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" REBUILD TABLESPACE ' || NEW_TBSP || | |
' ONLINE'; | |
ELSIF SEG.SEGMENT_TYPE = 'INDEX SUBPARTITION' | |
THEN | |
EXEC_IMM := | |
'ALTER INDEX ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" REBUILD SUBPARTITION ' || SEG.PARTITION_NAME || | |
' TABLESPACE ' || NEW_TBSP || ''; | |
ELSIF SEG.SEGMENT_TYPE = 'INDEX PARTITION' | |
THEN | |
EXEC_IMM := | |
'ALTER INDEX ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" REBUILD PARTITION ' || SEG.PARTITION_NAME || | |
' TABLESPACE ' || NEW_TBSP || ''; | |
ELSIF SEG.SEGMENT_TYPE = 'LOB PARTITION' | |
THEN | |
EXEC_IMM := | |
'alter table ' || SEG.OWNER || '."' || SEG.TABLE_NAME || '" move partition ' || SEG.REAL_PARTITION_NAME || | |
' lob (' || SEG.COLUMN_NAME || ') store as ( tablespace ' || NEW_TBSP || ' )'; | |
ELSIF SEG.SEGMENT_TYPE = 'LOBSEGMENT' | |
THEN | |
EXEC_IMM := 'ALTER TABLE ' || SEG.OWNER || '."' || SEG.TABLE_NAME || '" MOVE LOB(' || SEG.COLUMN_NAME || | |
') STORE AS (TABLESPACE ' || NEW_TBSP || ')'; | |
ELSIF SEG.SEGMENT_TYPE = 'TABLE' | |
THEN | |
EXEC_IMM := 'ALTER TABLE ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" MOVE TABLESPACE ' || NEW_TBSP || ''; | |
ELSIF SEG.SEGMENT_TYPE = 'TABLE PARTITION' | |
THEN | |
EXEC_IMM := | |
'ALTER TABLE ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" MOVE PARTITION ' || SEG.PARTITION_NAME || | |
' TABLESPACE ' || NEW_TBSP || ' NOLOGGING'; | |
ELSIF SEG.SEGMENT_TYPE = 'TABLE SUBPARTITION' | |
THEN | |
EXEC_IMM := | |
'ALTER TABLE ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" MOVE SUBPARTITION ' || SEG.PARTITION_NAME || | |
' TABLESPACE ' || NEW_TBSP || ' PARALLEL (DEGREE 2) '; | |
END IF; | |
DBMS_OUTPUT.put_line(EXEC_IMM); | |
EXECUTE IMMEDIATE EXEC_IMM; | |
END LOOP; | |
FOR I IN (SELECT * | |
FROM DBA_INDEXES | |
WHERE TABLESPACE_NAME = OLD_TBSP) LOOP | |
EXECUTE IMMEDIATE 'ALTER INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' REBUILD TABLESPACE ' || NEW_TBSP || ''; | |
END LOOP; | |
DBMS_OUTPUT.put_line('Execute the following commands:'); | |
DBMS_OUTPUT.put_line('DROP TABLESPACE ' || OLD_TBSP || ' INCLUDING CONTENTS AND DATAFILES;'); | |
DBMS_OUTPUT.put_line('ALTER TABLESPACE ' || NEW_TBSP || ' RENAME TO ' || OLD_TBSP || ';'); | |
END; | |
/ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment