| Unable to claim space for the operating system [message #566478] |
Sat, 15 September 2012 06:46  |
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
|
Senior Member |
|
|
Hi,
We are in the process of gaining some space in our database. We identified a tablespace that occupied around 400GB of space. The datas are required for us. Hence, we truncated (with drop storage) the tables, gathered statistics and shrinked the tables. After doing that we found the datafile occupied around 32GB of space. We tried to resize it to 5 GB and we got "ORA-03297: file contains used data beyond requested RESIZE value". We could see that size in dba_free_space against that tablespace. We would like to give back everything to OS.
We also tried exporting the tablespace without rows, dropped the tablespace and re-created and imported again. But the datafile grows to around 32G and we are unable to resize it.
We are sure that the tablespace has only the table definitions without data. But we do not know why is it occupying around 32G of space and we could see that size in dba_free_space. We would like to give back everything to OS. How can we do that?
Regards,
Antony
|
|
|
|
|
|
| Re: Unable to claim space for the operating system [message #566481 is a reply to message #566480] |
Sat, 15 September 2012 07:19  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
You haven't given much information. However, from your statement that the tablespace was 400G and your reference to "the datafile", ie one of them, can I assume that it is a bigfile tablespace? Usually when creating a bigfile tablespace, there is an assumption that objects within it are going to be large. So one would usually define it with a large uniform extent size. If your uniform extent is, for example, 1G and then you create 32 tables you will need a 32G file, even if all the tables are empty.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
