Home » RDBMS Server » Server Administration » Added lots of space to a tablespace (Sun Solaris, oracle 10.2.0.3.0)
Added lots of space to a tablespace [message #522120] Tue, 06 September 2011 13:15 Go to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Mistakenly I added lot of datafiles with autextend on option.. I realized later and then tried to resize the datafiles to a minumum space but got the below error

ORA-03214: File Size specified is smaller than minimum required.

How to resolve this problem to reclain the space back?

Report message to a moderator

Re: Added lots of space to a tablespace [message #522121 is a reply to message #522120] Tue, 06 September 2011 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

03214, 00000, "File Size specified is smaller than minimum required"
// *Cause: File Size specified for add/resize datafile/tempfile does not
//         allow for the minimum required of one allocation unit.
// *Action: Increase the specification for file size


alternatively remove all objects from tablespace & then DROP the tablespace

Report message to a moderator

Re: Added lots of space to a tablespace [message #522123 is a reply to message #522120] Tue, 06 September 2011 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Export, drop the objects, resize, import.

Regards
Michel

Report message to a moderator

Re: Added lots of space to a tablespace [message #522279 is a reply to message #522123] Wed, 07 September 2011 20:15 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
Would you try to Shrink your objects.

Report message to a moderator

Re: Added lots of space to a tablespace [message #522280 is a reply to message #522279] Wed, 07 September 2011 20:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Would you try to Shrink your objects.
post SQL to accomplish above
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Added lots of space to a tablespace [message #522282 is a reply to message #522280] Wed, 07 September 2011 21:53 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
I think that shrink space can make space to smaller.

Report message to a moderator

Re: Added lots of space to a tablespace [message #522283 is a reply to message #522282] Wed, 07 September 2011 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>I think that shrink space can make space to smaller.

I think I can teach my goat to fly.

Report message to a moderator

Re: Added lots of space to a tablespace [message #522293 is a reply to message #522282] Thu, 08 September 2011 01:24 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
If you read up on the ALTER TABLE...SHRINK SPACE command, you will find that it refers to reducing the size of a table following deletion of rows. Will that assist you in your current situation?

Report message to a moderator

Re: Added lots of space to a tablespace [message #522342 is a reply to message #522293] Thu, 08 September 2011 14:00 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi lg123,

Please tell you are working in shared server or dedicated server?

Please try to move data into other tablepace before shrinking your tablespace size and try to maintain new tablespace on different location.Then tune your database and find out latch contention as well using v$system_event view.After getting latch contention you can find tuning opportunities for that location and tune that place.

Report message to a moderator

Re: Added lots of space to a tablespace [message #522348 is a reply to message #522342] Thu, 08 September 2011 14:43 Go to previous message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Then tune your database and find out latch contention as well using v$system_event view.After getting latch contention you can find tuning opportunities for that location and tune that place.

Once again, what this has to do with the question?
(And what is the relation between location and lacth contention???? Don't answer to thid, this is not the question of the topic).

Could you please think about the question and your answer before posting it.

Regards
Michel

[Updated on: Sun, 30 March 2014 10:07]

Report message to a moderator

Previous Topic: Crawling Database (merged 2)
Next Topic: "How to test row movement" & "partition issue" merged
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Apr 29 15:08:44 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.