OraFAQ Forum: Server Administration » Will Drop User cascade deallocate the space.

Home » RDBMS Server » Server Administration » Will Drop User cascade deallocate the space.

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

Will Drop User cascade deallocate the space. [message #288235]

Sun, 16 December 2007 01:20

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,

Report message to a moderator

Re: Will Drop User cascade deallocate the space. [message #288236 is a reply to message #288235]

Sun, 16 December 2007 01:30

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

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

Report message to a moderator

Re: Will Drop User cascade deallocate the space. [message #288247 is a reply to message #288244]

Sun, 16 December 2007 06:18

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: Will Drop User cascade deallocate the space. [message #288279 is a reply to message #288235]

Sun, 16 December 2007 22:20

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.

Report message to a moderator

Re: Will Drop User cascade deallocate the space. [message #288286 is a reply to message #288279]

Sun, 16 December 2007 22:43

ibnadam
Messages: 7
Registered: December 2007

Junior Member

Thanks for your kind replies.

Regards

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 10:38:48 CDT 2024