Home » RDBMS Server » Backup & Recovery » tablespace resize
tablespace resize [message #549271] Thu, 29 March 2012 05:58 Go to next message
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.
Re: tablespace resize [message #549284 is a reply to message #549271] Thu, 29 March 2012 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: tablespace resize [message #549293 is a reply to message #549284] Thu, 29 March 2012 07:10 Go to previous messageGo to next message
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.
Re: tablespace resize [message #549301 is a reply to message #549293] Thu, 29 March 2012 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: tablespace resize [message #549330 is a reply to message #549301] Thu, 29 March 2012 09:30 Go to previous messageGo to next message
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.
Re: tablespace resize [message #549335 is a reply to message #549330] Thu, 29 March 2012 10:53 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: RMAN - Time Interval Backup and Restore
Next Topic: RESTORE DATABASE SKIP TABLESPACE
Goto Forum:
  


Current Time: Fri Feb 23 16:45:26 CST 2018

Total time taken to generate the page: 0.02600 seconds