Title Author Info Source
Resizing Tablespaces with Oracle8i and Oracle9i Martin Bracher (martin.bracher@trivadis.com) Technical Background Info and Trivadis Scripts From the Trivadis AI9-A Course
Problem
You may have a space problem on a hard drive and would like to shrink some data files which may have been created too large. Or you may have your big tables partitioned by year and have created each partition in its own tablespace which you want to shrink to its actually needed size at the end of the year. Now, what is the minimum size for "resizing" a data file?
Your Solution?
You want to shrink the TEST tablespace. First you check the data files it contains:
SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST'; FILE_ID FILE_NAME ---------- ---------------------------------------------------------24 C:\TEMP\TEST.DBF
How big is the file?
SQL> select bytes from dba_data_files 2 where file_name = 'C:\TEMP\TEST.DBF'; BYTES ---------209715200
How much free space will there be inside? (Do I have to check in DBA_FREE_SPACE or in DBA_LMT_FREE_SPACE or in DBA_DMT_FREE_SPACE or in DBA_SEGMENTS or DBA_LMT_USED_EXTENTS or DBA_DMT_USED_EXTENS or in UET$, FET$, TS$, etc.?) (locally managed, dictionary managed?)
SQL> select sum(blocks) from dba_lmt_free_space where file_id = 24;
SUM(BLOCKS) ----------28144
How big is the block size of these tablespaces?
SQL> select block_size from dba_tablespaces 2 where tablespace_name = 'TEST'; BLOCK_SIZE ---------4096
OK, then there are 28,144 * 4096 bytes of free space. That is 115,277,824 bytes of free space. OK. File size minus free space: 209,715,200 – 115,277,824 = 94,437,376. That's the "used space." This is the size I should be able to shrink the file to:
SQL> alter database datafile 'C:\TEMP\TEST.DBF' resize 94437376; alter database datafile 'C:\TEMP\TEST.DBF' resize 94437376 * ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value
Bad luck! Doesn't seem to be that "easy"...
Our Solution
Step 1: Take part in the Trivadis AI9-A course for an explanation of the information described here. Step 2: Download the lstsres.sql script from www.trivadis.com:
SQL> @lstsres Wrote file temp.tmp Resize of datafiles DB1.TRIVADIS.COM 17.02.2003 18:03 - 20.02.2003 11:52 (65.8 h) Tablespace -------------------CWMLITE DATA DATA_AUTO DATA_LM DATA_LMU DRSYS EXAMPLE Filename --------------------------------------D:\ORACLE\ORADATA\DB1\CWMLITE01.DBF D:\ORACLE\ORADATA\DB1\DATA01.DBF D:\ORACLE\ORADATA\DB1\DATA_AUTO01.DBF D:\ORACLE\ORADATA\DB1\DATA_LM01.DBF D:\ORACLE\ORADATA\DB1\DATA_LMU01.DBF D:\ORACLE\ORADATA\DB1\DRSYS01.DBF D:\ORACLE\ORADATA\DB1\EXAMPLE01.DBF Actual KB --------20480 459776 128 10240 10240 20480 137024 Shrink KB --------9856 459736 can't be shrinked 128 1088 9920 can't be shrinked
INDX ODM OPTW SYSTEM TEST TOOLS UNDOTBS USERS
D:\ORACLE\ORADATA\DB1\INDX01.DBF D:\ORACLE\ORADATA\DB1\ODM01.DBF D:\ORACLE\ORADATA\DB1\OPTW01.DBF D:\ORACLE\ORADATA\DB1\SYSTEM01.DBF C:\TEMP\TEST.DBF D:\ORACLE\ORADATA\DB1\TOOLS01.DBF D:\ORACLE\ORADATA\DB1\UNDOTBS01.DBF D:\ORACLE\ORADATA\DB1\USERS01.DBF
25600 9408 128 332800 204800 61440 450560 62720
128 can't be shrinked can't be shrinked 325592 184384 60480 447552 60416
Ah! TEST can be shrunk to a minimum of 184,384 K:
SQL> alter database datafile 'C:\TEMP\TEST.DBF' resize 184384K; Database altered. SQL>
Done! Maybe we can trick Oracle?
SQL> alter database datafile 'C:\TEMP\TEST.DBF' resize 184382K; Database altered. SQL>
Yes, we can! Or, maybe not?
SQL> select bytes/1024 from v$datafile where name='C:\TEMP\TEST.DBF'; BYTES/1024 ---------184384 SQL>
No, it doesn't work. Oracle rounds up to the next complete block. If you prefer our solution, come and visit our AI9-A course. Architecture, internal information and administration of Oracle9i for DBAs and developers. We don't just talk efficiency, we also deliver the necessary tools...
It's so easy to make such a script...
Have a look at the main command and decide for yourself ☺
select , , , tablespace_name tablespace file_name decode(actual_size,-1,'n/a (offline)',actual_size) actual decode(shrink_to,-1,'n/a' ,null,'can''t be shrinked' ,actual_size,'can''t be shrinked' , shrink_to) shrink_to
from ( select -- locally managed files that can be resized
-- (last used block 0 union -- Offline LMT select d.tablespace_name , d.file_id, d.file_name, -1 , -1 -- -1 => n/a, null => cannot be resized from dba_data_files d where bytes is null -- Offline LMT: Size is unknown union -- dictionary managed select df.tablespace_name , a.file# , df.file_name , df.bytes/1024 , case when nvl(min(a.bl-1),df.blocks) > 2 --nvl: no free space then nvl(min(a.bl-1),df.blocks)*(df.bytes/df.blocks)/1024 else 2*(df.bytes/df.blocks)/1024 end from (select f.ts#, f.file#, block# bl from sys.fet$ f start with block#=(select d.blocks-f.length+1 m from dba_data_files d where f.file#=d.file_id) --end of free space area = end of datafile connect by prior block#=length+block# --if area before is also free ) a , dba_data_files df , dba_tablespaces ts where df.file_id = a.file# (+) -- a no rows -> no free space and df.tablespace_name = ts.tablespace_name and ts.extent_management='DICTIONARY' group by df.tablespace_name, a.file#, df.file_name, df.blocks, df.bytes ) order by tablespace_name, file_name;
lstsres.sql can run under Oracle8i and Oracle9i and supports all different kinds of tablespaces (locally, dictionary, space management auto, etc.). Downloading from www.trivadis.com is free of charge. We wish you much success and efficiency! Martin Bracher Trivadis AG Martin Bracher Papiermühlestrasse 159 CH-3063 Ittigen b. Bern, Switzerland Internet: http://www.trivadis.com e-mail: martin.bracher@trivadis.com Tel: +41 31 928 09 60 Fax: +41 31 928 09 64