CODEWITHSUNDEEP
databaseoracle-databasetablespace
Spark
Spark

Reputation: 11

Need to clean users01.dbf in Oracle 12c

I have Oracle Database 12c in a docker container. At one point, the space in the tablespace expanded (the users01.dbf file became 32GB in size) and I made a new file for this tablespace - users02.dbf. I analyzed the tables and indexes that occupy the most space and made them truncate. I see that the size of the largest tables and indexes has decreased, but the users01.dbf and users02.dbf files remain the same size:

Here is a screenshot:

screenshot

Left size before, right after truncate command. How can I clean or reduce the size of users01.dbf and users02.dbf files and not break the database.

Upvotes: 1

Views: 2985

Answers (2)

Popeye
Popeye

Reputation: 35900

I am using the one query to find out the actual size that can be shrunk from the data file.

I am using it for almost 4 years and I don't know from where I got it but Yes, I got that script from one of the good blogs of the oracle.

Following script will generate the series of command which can be executed on DB to shrunk the data file or we can say reclaim the free space from the data file (only if more than 1 MB can be reclaimed) to perfect size without any error.

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
/

It will generate the commands something like the following:

/* reclaim    1934M from    2048M */ alter database datafile 'C:\APP\TEJASH\VIRTUAL\ORADATA\ORCL\DATAFILE\TEJASH_DATAFILE_01.DBF' resize 115M;
/* reclaim     158M from     200M */ alter database datafile 'C:\APP\TEJASH\VIRTUAL\ORADATA\ORCL\DATAFILE\UNDO_DF_02.DBF' resize 43M;
/* reclaim      59M from    1060M */ alter database datafile 'C:\APP\TEJASH\VIRTUAL\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_G9K5LYTT_.DBF' resize 1002M;
/* reclaim       3M from     840M */ alter database datafile 'C:\APP\TEJASH\VIRTUAL\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_G9K5KK2J_.DBF' resize 838M;

You can directly execute all of them and you do not have to worry about calculating anything by yourself.

Please note that this script will work on the data files for which autoextensible is ON

I hope this will help you out.

Cheers!!

Upvotes: 1

Adnan Bhatti
Adnan Bhatti

Reputation: 3480

alter database datafile 'path_to_datafile/users01.dbf' resize 150M;

Repeat same for 02. Make sure path and file names are correct.

Since you truncated tables it should have dropped the segments altogether, so file should be able to shrink. If you get ORA-03297: file contains used data beyond requested RESIZE value then it means you have data at 150M mark so you should try increasing resize limit more until error goes away.

As always you shouldn't be doing stuff directly on Production but test it out.

Upvotes: 1

Related Questions