| How to consolidate space in tablespace? [message #60283] |
Fri, 30 January 2004 07:48  |
Tony
Messages: 190
Registered: June 2001
|
Senior Member |
|
|
Hi,
I have a database that was created by someone before me. One of the tablespaces has 5 datafiles with each 8 Gig size. But currently the database only uses less than 30% of each of these files. Which means out of 40Gigs only 15Gigs is used. But the usage is spread across all the files. How do I consolidate the files so that I can either
1) Reduce the number of files from 5 to 3 (or)
2) Reduce the size of each of those files from 8gigs to 4 gigs.
Each file contains only approx. 2.3 Gigs of data now.
Even though the usage is less than 4 gigs, when I try to change the value from 8 Gigs to 4 Gigs I get the following error:
ORA-03297:file contains used data beyond requested RESIZE value.
Its weird , as the file contains only about 2 Gigs of data!!!
|
|
|
|
| Re: How to consolidate space in tablespace? [message #60288 is a reply to message #60283] |
Fri, 30 January 2004 10:50  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
You cant reduce the number of files(ie you cant drop them .The drop datafile command doesnt take the file out of dictionary, merely sets it OFFLINE or in RECOVER status).
However you can reduce the size of the datafiles to the maximum extent possible .
the error you are getting indicates that ...
03297, 00000, "file contains used data beyond requested RESIZE value"
// *Cause: Some portion of the file in the region to be trimmed is
// currently in use by a database object
// *Action: Drop or move segments containing extents in this region prior to
// resizing the file, or choose a resize value such that only free
// space is in the trimmed.
Even if the overall size of the extents in that datafile is around 2GB of data, they are not contigous in the file and hence you cant reduce to a point lesser than the extent of the maximum used block_id in the file.
-Thiru
|
|
|
|
| Re: How to consolidate space in tablespace? [message #60296 is a reply to message #60288] |
Fri, 30 January 2004 12:19 |
Tony
Messages: 190
Registered: June 2001
|
Senior Member |
|
|
Thanks for the info. The "Action" for this message in your reply says "*Action: Drop or move segments containing extents in this region prior to
// resizing the file, "
If it is not too much trouble, could you please let me know how I would go about drop/moving segments in the region??
|
|
|
|
| Re: How to consolidate space in tablespace? [message #60302 is a reply to message #60296] |
Fri, 30 January 2004 14:34 |
Tony
Messages: 190
Registered: June 2001
|
Senior Member |
|
|
|
Never mind. I got the process from certain websites. Its more tedius. Calculating segments/blocks and then determining where to chop it. I'm planning on trying to run 'Reorg' on the database using OEM. Our database is 9.2.0.4.0. Maybe doing re-org will defragment things in the data files and then I probably can cut it in half.
|
|
|
|
| Re: How to consolidate space in tablespace? [message #60306 is a reply to message #60302] |
Sat, 31 January 2004 03:19 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
I dont know which website you referred to,but it may not be as tedious as you think.
For eg, there are cases,where you can do this with a simple move. For eg)
thiru@9.2.0:SQL>select sum(bytes) from dba_extents where file_id=8;
SUM(BYTES)
----------
43712512
-- It shows I have only 43MB used up in this file
-- But when I try to resize to 60M ,it fails ,as seen here
thiru@9.2.0:SQL>alter database datafile 8 resize 60m;
alter database datafile 8 resize 60m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
-- Now I move a table contained in this datafile, into the same tablespace
thiru@9.2.0:SQL>alter table t3 move;
Table altered.
thiru@9.2.0:SQL>alter database datafile 8 resize 60m;
Database altered.
-- Now the resize succeeds .
There are other full reorg measures like creating a new tablespace followed by exp/imp or MOVEing all the objects and dropping the old tablespace,that can be done.
|
|
|
|
| Re: How to consolidate space in tablespace? [message #60307 is a reply to message #60306] |
Sat, 31 January 2004 03:23 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
BTW, this tablespace has only one datafile and so I am not moving anywhere else. Its on the same datafile as before.
thiru@9.2.0:SQL>select distinct file_id from dba_Extents where segment_name='T3' and owner='THIRU';
FILE_ID
----------
8
The table just got moved to the lower blocks on the file,allowing the resize.
|
|
|
|
| Re: How to consolidate space in tablespace? [message #60335 is a reply to message #60306] |
Mon, 02 February 2004 10:04 |
Tony
Messages: 190
Registered: June 2001
|
Senior Member |
|
|
|
Hi, Thanks for the reply. I don't think moving object by object is viable for me. We have hundreds of thousands of objects inside. Is there a any quicker way of telling (for e.g "move all objects from tablespace1/file1 to tablespace1/file2" ).
|
|
|
|
| Re: How to consolidate space in tablespace? [message #60339 is a reply to message #60335] |
Mon, 02 February 2004 14:38 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi
you dont have to move all the objects. You will need to find out how your objects are distributed in your tablespace/datafile(certain tools will show you these details or use dba_extents) and then move those objects (depending on the size) that are located last,which will allow you to resize the datafile.
If you are concerned about heavy fragmentation in this tablespace,you may want to consider creating a new one and move all the objects(using a generated script) or exp/imp.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
