OraFAQ Forum: Server Administration » Unable to claim space for the operating system

Home » RDBMS Server » Server Administration » Unable to claim space for the operating system (Oracle Database 10.2.0.4 - Solaris 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Unable to claim space for the operating system [message #566480 is a reply to message #566478]

Sat, 15 September 2012 07:04

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Note: The datas are not required.

Report message to a moderator

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: 8976
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.

Report message to a moderator

Re: Unable to claim space for the operating system [message #566482 is a reply to message #566478]

Sat, 15 September 2012 07:22

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I would look into table and tablespace definitions.

Report message to a moderator

Re: Unable to claim space for the operating system [message #566488 is a reply to message #566482]

Sat, 15 September 2012 12:15

jesuisantony
Messages: 166
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 ???

Report message to a moderator

Re: Unable to claim space for the operating system [message #566489 is a reply to message #566488]

Sat, 15 September 2012 13:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

some, many most professional applications have static SQL files for all objects creation; including tables.

Report message to a moderator

Previous Topic:	ORA-00603: ORACLE server session terminated by fatal error
Next Topic:	Unable to extend extent (2 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 03:39:15 CDT 2025