OraFAQ Forum: Backup & Recovery » tablespace resize

Home » RDBMS Server » Backup & Recovery » tablespace resize

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

tablespace resize [message #549271]

Thu, 29 March 2012 05:58

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

respect to all,
i have a "prj_tbl" named tablespace in a user's schema in my database. i have given 100mb size to the "prj_tbl" at the time of its creation and auto extend is open for 10mb,and now this tablespace is using nearly 3.6mb space for its data and remaining space is free,now i want to reduce the size of "prj_tbl" tablespace to 20mb and when i tried to resize it then ,

it throws an error- "'ora-03297 file contains used data beyond requested resize value oracle"

i think this message showing that my new size is small than the size of data on my tablespace but it shows free space nearly 96mb then it means my new size is larger than the size of data on tablespace.then please tell me how should i reduce the size. thanx in advance.

Report message to a moderator

Re: tablespace resize [message #549284 is a reply to message #549271]

Thu, 29 March 2012 06:54

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The message tells you that there are data above the point you want to decrease the space. It is not related how much free space you have.
In this case, export the objects, drop them, resize the file and reimport the objects.

Regards
Michel

Report message to a moderator

Re: tablespace resize [message #549293 is a reply to message #549284]

Thu, 29 March 2012 07:10

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

thank you very much sir for your awesome reply,
i want to ask that is there possible to drop all the objects in one go from one user's schema without dropping the user(means truncate a schema) or we can only drop them manually. thanx again.

Report message to a moderator

Re: tablespace resize [message #549301 is a reply to message #549293]

Thu, 29 March 2012 07:38

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In one statement, no but it is easy in an automatic script, search for "delme" here or on AskTom site, I posted a script, custom it as you want.

Regards
Michel

Report message to a moderator

Re: tablespace resize [message #549330 is a reply to message #549301]

Thu, 29 March 2012 09:30

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

thank you very much sir,
almost all the objects were deleted by delete statements generated by your script,but some objects were still in the scema and when i tried to delete them ,an error was thrown " ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop" then i dropped these objects also after run this query in sys-------
"alter session set events '10851 trace name context forever, level 2';" , then could you please explain me what was done by this query. thanx again.

Report message to a moderator

Re: tablespace resize [message #549335 is a reply to message #549330]

Thu, 29 March 2012 10:53

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Never use events when you do not knw what it is for.
New question, create a new topic.

For the script, you're right it currently does not handles queue table, I note it and will fix it.

Regards
Michel

Report message to a moderator

Previous Topic:	RMAN - Time Interval Backup and Restore
Next Topic:	RESTORE DATABASE SKIP TABLESPACE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 00:42:15 CST 2025