This is some code how to correctly shrink a datafile.<\/p>\n
set linesize 1000 pagesize 0 feedback off trimspool on\r\nwith\r\n hwm as (\r\n -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )\r\n select \/*+ materialize *\/ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks\r\n from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn\r\n ),\r\n hwmts as (\r\n -- join ts# with tablespace_name\r\n select name tablespace_name,relative_fno,hwm_blocks\r\n from hwm join v$tablespace using(ts#)\r\n ),\r\n hwmdf as (\r\n -- join with datafiles, put 5M minimum for datafiles with no extents\r\n select file_name,nvl(hwm_blocks*(bytes\/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes\r\n from hwmts right join dba_data_files using(tablespace_name,relative_fno)\r\n )\r\nselect\r\n case when autoextensible='YES' and maxbytes>=bytes\r\n then -- we generate resize statements only if autoextensible can grow back to current size\r\n '\/* reclaim '||to_char(ceil((bytes-hwm_bytes)\/1024\/1024),999999)\r\n ||'M from '||to_char(ceil(bytes\/1024\/1024),999999)||'M *\/ '\r\n ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes\/1024\/1024)||'M;'\r\n else -- generate only a comment when autoextensible is off\r\n '\/* reclaim '||to_char(ceil((bytes-hwm_bytes)\/1024\/1024),999999)\r\n ||'M from '||to_char(ceil(bytes\/1024\/1024),999999)\r\n ||'M after setting autoextensible maxsize higher than current size for file '\r\n || file_name||' *\/'\r\n end SQL\r\nfrom hwmdf\r\nwhere\r\n bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed\r\norder by bytes-hwm_bytes desc\r\n\/<\/pre>\n","protected":false},"excerpt":{"rendered":"This is some code how to correctly shrink a datafile. set linesize 1000 pagesize 0 feedback off trimspool on with hwm as ( — get highest block id from each datafiles ( from x$ktfbue as we don’t need all joins from dba_extents ) select \/*+ materialize *\/ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-149","post","type-post","status-publish","format-standard","hentry","category-oracle-database"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/149","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=149"}],"version-history":[{"count":2,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/149\/revisions"}],"predecessor-version":[{"id":304,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/149\/revisions\/304"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=149"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}