Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : June-2018

**********************************************************************************


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


I am certified Oracle Cloud Architect:
Oracle Cloud Infrastructure 2018 Certified Architect Associate
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate

Search This Blog

Monday, September 12, 2011

Oracle shrink datafiles to reclaim space

worth using below or You can use TOAD if you have it installed.

select value from v$parameter where name = 'db_block_size'
/
set lines 300 pagesize 1000;
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and a.file_name like '+AMPCDP_DATA_01%'
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

No comments: