Just, declare your tablespace name in V_TBLSPC variable:
SET SERVEROUTPUT ON
DECLARE
V_STMT VARCHAR2 (500);
V_TBLSPC VARCHAR2 (30) := 'CZD';
CURSOR C1
IS
SELECT FILE_ID
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = V_TBLSPC;
BEGIN
FOR LINE IN C1
LOOP
SELECT 'ALTER DATABASE DATAFILE '
|| ''''
|| D.FILE_NAME
|| ''''
|| ' RESIZE '
|| NVL (CEIL (D.BYTES / 1024 / 1024 - TAKE_BACK.TAKE_BACK_MB),
D.BYTES / 1024 / 1024)
|| 'M;'
SQL
INTO V_STMT
FROM DBA_DATA_FILES D,
(SELECT SUM (BYTES) / 1024 / 1024 TAKE_BACK_MB
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = V_TBLSPC AND FILE_ID = LINE.FILE_ID
AND BLOCK_ID >=
NVL (
(SELECT (A.BLOCK_ID + (A.BYTES / B.BLOCK_SIZE))
FROM DBA_EXTENTS A, DBA_TABLESPACES B
WHERE A.BLOCK_ID =
(SELECT MAX (BLOCK_ID)
FROM DBA_EXTENTS
WHERE FILE_ID = LINE.FILE_ID
AND TABLESPACE_NAME =
V_TBLSPC)
AND A.FILE_ID = LINE.FILE_ID
AND A.TABLESPACE_NAME = V_TBLSPC
AND B.TABLESPACE_NAME = V_TBLSPC),
0)) TAKE_BACK
WHERE D.FILE_ID = LINE.FILE_ID;
DBMS_OUTPUT.PUT_LINE (V_STMT);
END LOOP;
END;
/
A sample output of the above script is:
ALTER DATABASE DATAFILE '/oracle67/oradata/czd20.dbf' RESIZE 4994M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd05.dbf' RESIZE 3091M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd06.dbf' RESIZE 3092M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd07.dbf' RESIZE 3094M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd08.dbf' RESIZE 3093M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd09.dbf' RESIZE 3090M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd01.dbf' RESIZE 3095M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd10.dbf' RESIZE 2048M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd02.dbf' RESIZE 3096M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd03.dbf' RESIZE 3090M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd04.dbf' RESIZE 3096M;
ALTER DATABASE DATAFILE '/oracle40/oradata/czd11.dbf' RESIZE 4995M;
ALTER DATABASE DATAFILE '/oracle43/oradata/czd12.dbf' RESIZE 9998M;
ALTER DATABASE DATAFILE '/oracle48/oradata/czd13.dbf' RESIZE 8093M;
ALTER DATABASE DATAFILE '/oracle52/oradata/czd14.dbf' RESIZE 3996M;
ALTER DATABASE DATAFILE '/oracle54/oradata/czd15.dbf' RESIZE 14996M;
ALTER DATABASE DATAFILE '/oracle58/oradata/czd16.dbf' RESIZE 4000M;
ALTER DATABASE DATAFILE '/oracle59/oradata/czd17.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle62/oradata/czd18.dbf' RESIZE 13497M;
ALTER DATABASE DATAFILE '/oracle66/oradata/czd19.dbf' RESIZE 15497M;
ALTER DATABASE DATAFILE '/oracle69/oradata/czd21.dbf' RESIZE 11993M;
ALTER DATABASE DATAFILE '/oracle71/oradata/czd22.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle75/oradata/czd23.dbf' RESIZE 6996M;
ALTER DATABASE DATAFILE '/oracle78/oradata/czd24.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle76/oradata/czd25.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle78/oradata/czd26.dbf' RESIZE 3995M;
ALTER DATABASE DATAFILE '/oracle81/oradata/czd27.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle81/oradata/czd28.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle87/oradata/czd29.dbf' RESIZE 24985M;
ALTER DATABASE DATAFILE '/oracle88/oradata/czd30.dbf' RESIZE 24641M;
ALTER DATABASE DATAFILE '/oracle89/oradata/czd31.dbf' RESIZE 13641M;
ALTER DATABASE DATAFILE '/oracle90/oradata/czd32.dbf' RESIZE 8961M;
No comments:
Post a Comment