テーブル縮小

■自動拡張されたテーブルスペースをどこまで縮小可能か？
-- 9i以上用
SELECT FILE_NAME, ((E.BLOCK_ID - 1) * T.BLOCK_SIZE + E.BYTES) / 1024 BYTE_K
FROM DBA_TABLESPACES T,DBA_DATA_FILES D,(
SELECT
DISTINCT
FILE_ID
,TABLESPACE_NAME
,MAX(BLOCK_ID) OVER (PARTITION BY FILE_ID) BLOCK_ID
,FIRST_VALUE(BYTES) OVER (PARTITION BY FILE_ID ORDER BY BLOCK_ID
DESC) BYTES
FROM DBA_EXTENTS
WHERE TABLESPACE_NAME = UPPER('&&TSNAME')
) E
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
AND D.TABLESPACE_NAME = E.TABLESPACE_NAME
AND D.FILE_ID = E.FILE_ID
AND T.TABLESPACE_NAME = UPPER('&&TSNAME')
;

-- 8i用
SELECT D.NAME, ((E.BLOCK_ID - 1) * D.BLOCK_SIZE + E.BYTES) /1024 BYTE_K
FROM DBA_TABLESPACES T,V$DATAFILE D,DBA_EXTENTS E
WHERE D.FILE# = E.FILE_ID
AND T.TABLESPACE_NAME = E.TABLESPACE_NAME
AND T.TABLESPACE_NAME = UPPER('&&TSNAME')
AND (E.FILE_ID,E.BLOCK_ID) IN (
SELECT FILE_ID,MAX(BLOCK_ID)
FROM DBA_EXTENTS S
WHERE T.TABLESPACE_NAME = S.TABLESPACE_NAME
GROUP BY FILE_ID)
;