Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Moovarkku.

Asked: February 16, 2003 - 9:40 pm UTC

Last updated: June 26, 2009 - 9:09 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Dear Tom,
Good Morning. Thank you so much for your tips and helps for DBAs.
Do we have any option to resize the datafile. Except export and import tables.

One more thing... all data files are 500MB... in each file i have 200M to 300M freespace... But HWM is reached upto 480M...
Database is in archive log mode and Database is Live 24/7 any good idea which help to resize the datafile.
My Database version is 8.1.7... Actually my tablespace size is
20GB... To make the things better i moved some huge tables into seperate Tablespace... But the first tablespace have the freespace... I need to free those space from OS Level...
What i have in my mind is if it was able to free the size and Move HWM to the maximum freespace of the datafile then i could resize that datafile will release the freespace in OS Level.
Here is the sample file of my Datafile
file [ SEGMENT NAME ] extent block ID+ Segsize(KB)
12 empty 0 2 104962 419840
12 REQ_SERVICE 2 104962 112642 30720
12 empty 0 112642 113922 5120
12 CLIENT_BRANCH 1 113922 116482 10240
12 empty 0 116482 117762 5120
12 OS_BATCH_ITEMS 9 117762 122882 20480
12 empty 0 122882 125442 10240
12 CC_IMPORT_DATA 13 125442 125698 1024
12 empty 0 125698 128001 9212

In this datafile i have total empty space is more than 400MB at begining of this datafile. For this file HWM is upto 490MB. So i cannot resize this datafile. Could you give some idea to resize the datafiles like this.

Thank you
Mudhalvan M.M

and Tom said...

why not create a new empty LOCALLY MANAGED tablespace.

Move the tables into there.

drop the old tablespaces.

and given that you must have a habit of dropping/truncing lots of stuff frequently (only way to get into your situation described above) consider having more tablespaces with fewer objects or putting those things you drop/create over and over or truncate into their OWN tablespace so as to avoid these holes in the future.


  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Subbarao, February 18, 2003 - 5:00 am UTC

I have the same scenario in my LMT.( I have created some tables in the LMT tablespace and later on dropped many tables in that LMT tablespaces), as such I have holes in the datafile, Now there are no further additions into this tablespace, Now I want to free-up the space, how can I do.
Thanks in Advance.

Tom Kyte
February 18, 2003 - 7:57 am UTC

see above.

you could try alter table T move on the segment(s) that are way up there -- as space tends to get reused at the head of the file in an LMT but basically -- the same advice above applies.

Datafile resizing- run a verify?

trevor welch, June 23, 2003 - 1:43 am UTC

Hi Tom,

Can I ask what would you do after resizing a datafile?
I mean in regards to ensuring nothing adverse happened,
would you run a db verify?


Tom Kyte
June 23, 2003 - 8:09 am UTC

i do nothing, i just use it. unless there was an error, it is OK.

Dave, June 23, 2003 - 9:59 am UTC

By moving a segment that is at the end of a datafile (LMT) to another TS, or to an available space towards the beginning of the file, will thatshrink the HWM and allow the datafile to be shrunk?

Tom Kyte
June 23, 2003 - 11:17 am UTC

as long as you get rid of the extent at the "end" of the file, yes.


ATUL, February 16, 2005 - 11:54 pm UTC


I wants to know that the files that are resized in the last one day.. Last added file i can find using creation_date ,but how to find the resize details.. can you let me know ? ( Note : i dont want to see the alert log )
I am using 9i


Tom Kyte
February 17, 2005 - 7:47 am UTC

footnote to note: then you will be out of luck as far as I know.

Is this a bug?

RJ, October 22, 2006 - 8:34 am UTC

Hi Tom:

Oracle9i Enterprise Edition Release - 64bit Production

I've been rebuilding indexes to move them from occupying just a single LMT with manual space management to several new tablespace with AutomaticSegment Space Mgmnt, initially based on size.

Then I resized the old tablespace down to smallest I can get it. Free.sql says its only 128K but checking on the OS side tells me I still have the original 23GB file.

Q1 : Could this be a bug? Could this be time for Support?
Q2 : Aside from checking dba_segments/dba_users for any segment/users still using a tablespace, is there any more checks I could do to verify that it is safe to drop a tablespace?

Thanks as usual

Tom Kyte
October 22, 2006 - 4:08 pm UTC

could be an OS bug sure. not know what OS you use, how you asked the OS, whatever - I cannot comment (yes, use support)

if you drop the tablespace:

drop tablespace t;

and it doesn't complain - it is safely dropped. If anything was in there, it would not drop it.


Kubilay, April 25, 2007 - 11:35 am UTC

Hi Tom

I have a question with regards to the resize of datafiles after a TABLE COMPRESSION and ALTER TABLE ... MOVE.

I am trying to claim space back from the datawarehouse after table compression on some very large tables in a tablespace. I use Oracle 10g on Solaris. I want to compress the tables within their own tablespace, contrary to what you suggest in the first follow-up post above.

Will the command 'ALTER TABLE X MOVE PARTITION Z', after a table compression on X, bring down the HWM so that I can resize the datafiles and claim back space from the database thus reduce backup times, save space etc?

I have tested it like this:

1- As SYS I create the tablespace used in the test

SQL> create tablespace app01 datafile '/u11/oradata/testdb1/app01.dbf' size 5M autoextend on ;

Tablespace created.

2- I create a Partitioned table called Y

create table y parallel nologging partition by range (last_ddl_time)
partition y2000 values less than (to_date('01-01-2001', 'DD-MM-YYYY')) tablespace app01,
partition y2001 values less than (to_date('01-01-2002', 'DD-MM-YYYY')) tablespace app01,
partition y2002 values less than (to_date('01-01-2003', 'DD-MM-YYYY')) tablespace app01,
partition y2003 values less than (to_date('01-01-2004', 'DD-MM-YYYY')) tablespace app01,
partition y2004 values less than (to_date('01-01-2005', 'DD-MM-YYYY')) tablespace app01,
partition y2005 values less than (to_date('01-01-2006', 'DD-MM-YYYY')) tablespace app01,
partition y2006 values less than (to_date('01-01-2007', 'DD-MM-YYYY')) tablespace app01,
partition y2007 values less than (to_date('01-01-2008', 'DD-MM-YYYY')) tablespace app01
select * from dba_objects;

Table created.

Space after creation of the table.

TABLESPACE_NAME                 Total (MB) Free (MB)  PCT_USED EXTENSIBLE Max Size (MB)
------------------------------ ----------- --------- --------- ---------- -------------
APP01                                   12         2        88 YES               32,768

3- I compress the table within the same tablespace

alter table Y move partition Y2000 compress nologging parallel;
alter table Y move partition Y2001 compress nologging parallel;
alter table Y move partition Y2002 compress nologging parallel;
alter table Y move partition Y2003 compress nologging parallel;
alter table Y move partition Y2004 compress nologging parallel;
alter table Y move partition Y2005 compress nologging parallel;
alter table Y move partition Y2006 compress nologging parallel;
alter table Y move partition Y2007 compress nologging parallel;

Space after compression is done.

TABLESPACE_NAME                 Total (MB) Free (MB)  PCT_USED EXTENSIBLE Max Size (MB)
------------------------------ ----------- --------- --------- ---------- -------------
APP01                                   16        12        24 YES               32,768

4- I issue the command ALTER TABLE... MOVE for each partition within the tablespace to reorganise extents within the segment.

alter table Y move partition Y2001 ;
alter table Y move partition Y2002 ;
alter table Y move partition Y2003 ;
alter table Y move partition Y2004 ;
alter table Y move partition Y2005 ;
alter table Y move partition Y2006 ;
alter table Y move partition Y2007 ;

Space after the operation.

TABLESPACE_NAME                 Total (MB) Free (MB)  PCT_USED EXTENSIBLE Max Size (MB)
------------------------------ ----------- --------- --------- ---------- -------------
APP01                                   16        11        29 YES               32,768

It seems that this resets the HWM, cause after I do this, I can resize the datafiles. How can I see this?

5- As SYS I check datafiles and resize if possible.

set lines 600
set pages 49999
column file_name format a100
SELECT fs.file_id , f.bytes, f.file_name
,sum(fs.bytes)/(1024*1024) free
FROM dba_free_space fs, dba_Data_files f
where fs.file_id=f.file_id
GROUP BY f.bytes, f.file_name, fs.file_id
order by 4 desc

  FILE_ID      BYTES FILE_NAME                              FREE
---------- ---------- ------------------------------- ----------
         5   16252928 /u11/oradata/testdb1/app01.dbf          11

Resizing the datafile of app01.

SQL> alter database datafile '/u11/oradata/testdb1/app01.dbf' resize 6M;

Database altered.

FILE_ID    BYTES      FILE_NAME                            FREE
---------- ---------- ------------------------------ ----------
         5    6291456 /u11/oradata/testdb1/app01.dbf        1.5

Conclusion: I have gained OS space after table compression, less disk usage in the system and faster backups!

My question to you briefly is:

Is it true that the step 4 above will always move the HWM down and will allow me to resize datafiles? If HWM is going down how can I see that?

Many Thanks!

Tom Kyte
April 25, 2007 - 1:20 pm UTC

no, it will not be assured.

If your goal is "smallest file possible" you would:

a) create new tablespace with datafiles set to autoextend on, start them "small"
b) alter move the segments into new tablespace
c) drop old one

It will be hit or miss with your approach in a single tablespace, it'll likely 'tend' to move the files to the front - but all it takes is ONE EXTENT stuck out the end to make this not work

Problem occuring while decreasing the size of the datafile

Venkat, May 20, 2009 - 9:00 am UTC

Hi Tom,
The tablespace size is 4GB and used space is 500MB.I am trying to resize the datafile to 2GB but it is throwing the following error.
ORA-03297: file contains used data beyond requested RESIZE value.
I am using with both alter tablespace,alter database commands and i tried after the recyclebin also.
Please suggest for the same

Table compression

Arch, June 23, 2009 - 7:16 am UTC

Hi Tom,
I would like to recommend table compression for look up tables for performance improvements.
We are currently using 10g Standard edition - is this feature available in Standard edition?
Please advise.
Tom Kyte
June 26, 2009 - 9:09 am UTC

no it is not (table compression is not)

and compression would probably not make lookups any faster. Typically people do lookups via a single column primary key so it would be

a) 3 IO's against index
b) 1 IO against table

in general to retrieve a value. compressing the table would not change that. If you full scanned the lookup frequently, AND the lookup was HUGE (bigger than say 5% of the buffer cache), then it might make sense.

You could look as HASH CLUSTERS (not partitioning, CLUSTERS), there you could make the lookup from primary key to data take 1 IO (no index needed to retrieve data)