Home » RDBMS Server » Server Administration » Will Drop User cascade deallocate the space.
Will Drop User cascade deallocate the space. [message #288235] Sun, 16 December 2007 01:20 Go to next message
ibnadam
Messages: 7
Registered: December 2007
Junior Member
Dear Experts,

This is my first post. I have a doubt regarding Drop user xxx cascade.I know that,

Delete: Will only delete the rows and does not deallocate space.
Truncate: will delete rows and does deallocate space.
Drop: will delete all rows, constraint and defination but what about deallocation of space.

When we issue this statement

SQL> Drop user xxx cascade. will it give back all the space occupied by the objects of xxx schema.

I request you to clarify with sql statments, i mean the space being deallocated if it deallocates.
Regards,

Re: Will Drop User cascade deallocate the space. [message #288236 is a reply to message #288235] Sun, 16 December 2007 01:30 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

DDL statement reset HWM(High Water Mark) so yes space is deallocated when user/schemas is drop.

[Updated on: Sun, 16 December 2007 01:30]

Report message to a moderator

Re: Will Drop User cascade deallocate the space. [message #288244 is a reply to message #288236] Sun, 16 December 2007 05:09 Go to previous messageGo to next message
ibnadam
Messages: 7
Registered: December 2007
Junior Member

Thanks,

Can we query dba_data_file to get the notice of space deallocation. will it shows the space deallocation over database?

select sum(bytes)/(1024*1024) from dba_data_files ;

sum(bytes)
-------------
234.234

some thing like above.

Please help
Re: Will Drop User cascade deallocate the space. [message #288247 is a reply to message #288244] Sun, 16 December 2007 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba_data_files gives file size.
check dba_free_space to get free space inside the data file.

Regards
Michel
Re: Will Drop User cascade deallocate the space. [message #288279 is a reply to message #288235] Sun, 16 December 2007 22:20 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Delete and truncate is for dropping tables' rows. They does not drop entire schema or table. And the keyword drop is for drop entire thing like dropping a table or dropping a schema. So entire space is deallocated.
Re: Will Drop User cascade deallocate the space. [message #288286 is a reply to message #288279] Sun, 16 December 2007 22:43 Go to previous message
ibnadam
Messages: 7
Registered: December 2007
Junior Member
Thanks for your kind replies.


Regards
Previous Topic: Tablespace Extend error
Next Topic: truncate not resetting high water mark
Goto Forum:
  


Current Time: Sun Dec 04 02:28:04 CST 2016

Total time taken to generate the page: 0.14513 seconds