OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Dropping User (Oracle 7 Oracle10G)

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

Dropping User [message #408129]

Sun, 14 June 2009 09:44

sudh11482
Messages: 16
Registered: September 2007
Location: Lucknow

Junior Member

Dear All,

Im using Oracle 7 on Unix platform and Oracle 10G on Linux 5.2 platform.In both the versions of Oracle whenever I dropped the user,the space allocated to it does not get free.
I checked the space alloted for the tablespaces by using DBA packages that tells about the space occupied and space available.
Does'nt Oracle free the space once the user is dropped or when it is freed??

Thanks in advance..

Report message to a moderator

Re: Dropping User [message #408130 is a reply to message #408129]

Sun, 14 June 2009 09:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Does'nt Oracle free the space once the user is dropped or when it is freed??
OS disk space is returned to OS after datafile is actually deleted.

Report message to a moderator

Re: Dropping User [message #408132 is a reply to message #408129]

Sun, 14 June 2009 10:05

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

Senior Member
Account Moderator

Space is freed and returned back to the tablespace for other allocation.

Please read
Database Concepts
Chapter 3 Tablespaces, Datafiles, and Control Files

Regards
Michel

Report message to a moderator

Re: Dropping User [message #408134 is a reply to message #408132]

Sun, 14 June 2009 10:16

sudh11482
Messages: 16
Registered: September 2007
Location: Lucknow

Junior Member

thanks for reply..
I used to keep the default tablespace as USERS.
when i used to see the space using dba_data_files and dba_free_space packages the space is not reduced after the user is dropped....
am i not looking in the right dba package??

Report message to a moderator

Re: Dropping User [message #408135 is a reply to message #408134]

Sun, 14 June 2009 10:18

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

Senior Member
Account Moderator

Copy and paste what you do and what you see don't try to explain it.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: Dropping User [message #409123 is a reply to message #408134]

Fri, 19 June 2009 08:45

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Quote:

Im using Oracle 7 on Unix platform and Oracle 10G on Linux 5.2 platform.In both the versions of Oracle whenever I dropped the user,the space allocated to it does not get free.

You may be misunderstanding how Oracle works.
When you create a user and grant them a quota on a tablespace, the amount of free space in that tablespace does not change - no blocks are set aside and reserved for that users exclusive access.
The quota is used as a value to check against when allocating extents in the tablespace.

Report message to a moderator