Datafile Shrink and Movement 

 Datafile Shrink

Run the below query on tablespace level and see if that returns anything. If you still get the same error and then try to move the data files to different file system.

set pages 0

set lin 150

set verify off

column file_name format a60 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"

column savings format 999,990 heading "Poss.|Savings"

column sum format 999,999,999

break on report

compute sum of savings on report

column value new_val blksize

select value from v$parameter where name = 'db_block_size'

/

select file_name,

       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

       ceil( blocks*&&blksize/1024/1024) currsize,

       ceil( blocks*&&blksize/1024/1024) -

       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

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 tablespace_name='&ts_name'

/

Datafile Movement

ALTER TABLESPACE CUSTOM_TBLS READ ONLY;

ALTER TABLESPACE CUSTOM_TBLS OFFLINE NORMAL;

cp /uat3/uat3db/DATA/CUSTOM_TBLS_1_UAT3DB.dbf /uat1/uat3/uat3db/DATA/CUSTOM_TBLS_1_UAT3DB.dbf

chown oracle:oinstall /uat1/uat3/uat3db/DATA/CUSTOM_TBLS_1_UAT3DB.dbf

alter tablespace CUSTOM_TBLS rename datafile '/uat3/uat3db/DATA/CUSTOM_TBLS_1_UAT3DB.dbf' TO '/uat1/uat3/uat3db/DATA/CUSTOM_TBLS_1_UAT3DB.dbf' ;

alter tablespace CUSTOM_TBLS online;

ALTER TABLESPACE CUSTOM_TBLS READ WRITE;

rm -f /uat3/uat3db/DATA/CUSTOM_TBLS_1_UAT3DB.dbf

-- df -h  --> affiche l espace disque

Resize your Oracle datafiles down to the minimum 

 Your datafiles have grown in the past but now you want to reclaim as much space as possible, because you are short on filesystem space, or you want to move some files without moving empty blocks, or your backup size is too large. ALTER DATABASE DATAFILE … RESIZE can reclaim the space at the end of the datafile, down to the latest allocated extent.

But if you try to get lower, you will get:
ORA-03297: file contains used data beyond requested RESIZE value

So, how do you find this minimum value, which is the datafile’s high water mark?
You have the brute solution: try a value. If it passes, then try a lower value. If it failed, then try a higher one.
Or there is the smart solution: find the datafile high water mark.

You can query DBA_EXTENTS to know that. But did you try on a database with a lot of datafiles? It runs forever. Because DBA_EXTENTS is doing a lot of joins that you don’t need here. So my query directly reads SYS.X$KTFBUE which is the underlying fixed table that gives extent allocation in Locally Managed Tablespaces.

Note that the query may take a few minutes when you have a lot of tables, because the information is on disk, in each segment header, in the bitmaps used by LMT tablepaces. And you have to read all of them.

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 ktfbuefno,ktfbuesegtsn

 ),

 hwmts as (

  -- join ts# with tablespace_name

  select name tablespace_name,relative_fno,hwm_blocks

  from hwm join v$tablespace using(ts#)

 ),

 hwmdf as (

  -- join with datafiles, put 5M minimum for datafiles with no extents

  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes

  from hwmts right join dba_data_files using(tablespace_name,relative_fno)

 )

select

 case when autoextensible='YES' and maxbytes>=bytes

 then -- we generate resize statements only if autoextensible can grow back to current size

  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '

   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'

 else -- generate only a comment when autoextensible is off

  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

   ||'M from '||to_char(ceil(bytes/1024/1024),999999)

   ||'M after setting autoextensible maxsize higher than current size for file '

   || file_name||' */'

 end SQL

from hwmdf

where

 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed

order by bytes-hwm_bytes desc

/

and here is a sample output:
/* reclaim    3986M from    5169M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_undotbs1_o9pfojva_.dbf' resize 1183M;

/* reclaim    3275M from   15864M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_apcpy_o5pfojni_.dbf' resize 12589M;

/* reclaim    2998M from    3655M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_cpy_qt_oepfok3n_.dbf' resize 657M;

/* reclaim    2066M from    2250M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_undotbs2_olpfokc9_.dbf' resize 185M;

/* reclaim     896M from    4000M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_cpy_ocpfok3n_.dbf' resize 3105M;

You get directly the resize statements, with the reclaimable space in comments.

A few remarks about my query:

·         I generate the resize statements only for datafiles which are autoextensible. This is because I want to be sure that the datafiles can grow back to their original size if needed.

·         When datafile is not autoextensible, or maxsize is not higher than the current size, I only generate a comment.

·         When a datafile has no extents at all I generate a resize to 5MB. I would like to find the minimum possible size (without getting ORA-3214) but my test do not validate yet what is documented in MOS. If anyone has an idea, please share.

·         There is probably a way to get that high water mark in a cheaper way. Because the alter statement gives the ORA-03297 much quicker. Information is probably available in the datafile headers, without going to segment headers, but I don’t know if it is exposed in a safe way. If you have an idea, once again, please share.