Resize datafile

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Tue, 23 Oct 2001 08:48:39 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA71E46D0@lnewton.leeds.lfs.co.uk>

Roland,

The most convenient way to resize a datafile is to use the command (as system) :

alter database datafile 'full_path_to_datafile' resize n;

Where 'n' is the new size in bytes. You can add a suffix of 'k' or 'm' for kbytes and mbytes to save typing !

This works in 7.3. and above, but there is a bug in 7.3 where a datafile which has been resized to a smaller size cannot be used if the database is cloned. When the clone is opened, there is a message displayed that 'file x is not the same size as the controlfile thinks it is!' or words to that effect.

Assuming you get an error from the alter database command which says 'there are n blocks of data above the requested size' then you have a problem. The error message says it all, and you will have to find out what is above the requested size and either drop it, or move it out of the way. So, find out what is in your data file :

SELECT tablespace_name,

             owner, 
             segment_name, 
             block_id start_block, 
             blocks num_blocks, 
             block_id + blocks -1 end_block, 
             bytes/1024 kb, '' free

FROM sys.dba_extents
WHERE file_id = &file_id
UNION SELECT tablespace_name,

            NULL, 
            NULL, 
            block_id start_block, 
            blocks num_blocks, 
            block_id + blocks -1 end_block, 
            bytes/1024 kb, 'Free' free

FROM sys.dba_free_space
WHERE file_id = &file_id

ORDER BY start_block DESC;

This will show you a map of the datafile in question starting at the highest block number and working downwards. If the top end of the list shows a free block or blocks, then you can resize down to that block. Simply take the start block number, say 122456, subtract 1 and multiply by your db_block_size parameter to get the new size in Kbytes, and feed that number into the alter database .... resize command shown above.

If, on the other hand, the top block is not a free block, then you still have a number of options :

if the object in question is an index, rebuild it into a different tablespace, resize the datafile and rebuild back into the correct tablespace.
if the object is a table AND your database is 8i (8.1.5) or greater, alter table .... move tablespace ....., resize the datafile and move the table back to its correct location.
if there are a large number of objects above the free blocks, then your best bet is export that user/users owning those objects, drop the objects, resize the datafile, import the objects.

HTH. Regards, Norman.

Norman Dunbar			EMail:	Norman.Dunbar_at_LFS.co.uk
Database/Unix administrator	Phone:	0113 289 6265
				Fax:	0113 289 3146
Lynx Financial Systems Ltd.	URL:	http://www.Lynx-FS.com
------------------------------------------------------------------------

-----Original Message-----
From: Roland Carlsson [mailto:roland.c_at_swetravel.se] Posted At: Tuesday, October 23, 2001 8:13 AM Posted To: server
Conversation: Resize datafile
Subject: Resize datafile

Hi!
Is there a way to optimize a datafile/tablespace. An test didn't run as expected so I got a tablespace/datafile that got very large. Must i export
all the users for that tablespace to another tablespace and remove the first
one or is there a more convient way of doing this? I can take a couple hours
of downtime for the database.

Thanks in advance
Roland Carlsson Received on Tue Oct 23 2001 - 02:48:39 CDT