Home » RDBMS Server » Server Administration » Unable to claim space for the operating system (Oracle Database 10.2.0.4 - Solaris 10)
Unable to claim space for the operating system [message #566478] Sat, 15 September 2012 06:46 Go to next message
jesuisantony
Messages: 162
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 #566480 is a reply to message #566478] Sat, 15 September 2012 07:04 Go to previous messageGo to next message
jesuisantony
Messages: 162
Registered: July 2006
Location: Chennai
Senior Member
Note: The datas are not required.
Re: Unable to claim space for the operating system [message #566481 is a reply to message #566480] Sat, 15 September 2012 07:19 Go to previous messageGo to next message
John Watson
Messages: 8499
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.
Re: Unable to claim space for the operating system [message #566482 is a reply to message #566478] Sat, 15 September 2012 07:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I would look into table and tablespace definitions.
Re: Unable to claim space for the operating system [message #566488 is a reply to message #566482] Sat, 15 September 2012 12:15 Go to previous messageGo to next message
jesuisantony
Messages: 162
Registered: July 2006
Location: Chennai
Senior Member
Ok in that case, I should modify the table creation statements with minimum extent size and I need to create all the tables manually. Even export/import is not going to help. Is my understanding right ???
Re: Unable to claim space for the operating system [message #566489 is a reply to message #566488] Sat, 15 September 2012 13:25 Go to previous message
BlackSwan
Messages: 26759
Registered: January 2009
Location: SoCal
Senior Member
some, many most professional applications have static SQL files for all objects creation; including tables.
Previous Topic: ORA-00603: ORACLE server session terminated by fatal error
Next Topic: Unable to extend extent (2 Merged)
Goto Forum:
  


Current Time: Sat Feb 27 16:15:18 CST 2021