Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Resize datafile
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
NULL, NULL, block_id start_block, blocks num_blocks, block_id + blocks -1 end_block, bytes/1024 kb, 'Free' free
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 :
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