shrink space tablespace


COLUMN SHRINK_DATAFILES FORMAT A75 WORD_WRAPPED
COLUMN VALUE NEW_VAL BLKSIZE
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size';

SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) ||
'M;' SHRINK_DATAFILES FROM DBA_DATA_FILES DBADF,
(SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS*&&BLKSIZE/1024/1024)- CEIL((NVL(HWM,1)* &&BLKSIZE)/1024/1024 ) > 0;

SHRINK_DATAFILES
-------------------------------------------------------------------------
ALTER DATABASE DATAFILE '/db/oradata/GOLDTEST/centestdata01.dbf'
RESIZE 9139M;

ALTER DATABASE DATAFILE '/db/oradata/GOLDTEST/centestidx01.dbf'
RESIZE 14871M;

.....

Increase the suggested value to avoid potential errors

ALTER DATABASE DATAFILE '/db/oradata/GOLDTEST/centestdata01.dbf' resize 10000M;

Database altered.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: