Home » RDBMS Server » Server Administration » Dropping User (Oracle 7 Oracle10G)
Dropping User [message #408129] Sun, 14 June 2009 09:44 Go to next message
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..
Re: Dropping User [message #408130 is a reply to message #408129] Sun, 14 June 2009 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 25581
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.
Re: Dropping User [message #408132 is a reply to message #408129] Sun, 14 June 2009 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65155
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Dropping User [message #408134 is a reply to message #408132] Sun, 14 June 2009 10:16 Go to previous messageGo to next message
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??
Re: Dropping User [message #408135 is a reply to message #408134] Sun, 14 June 2009 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65155
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Dropping User [message #409123 is a reply to message #408134] Fri, 19 June 2009 08:45 Go to previous messageGo to next message
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.
Re: Dropping User [message #410549 is a reply to message #408129] Sun, 28 June 2009 09:33 Go to previous messageGo to next message
selvakumarmvs
Messages: 12
Registered: February 2009
Junior Member
try this

drop user user_name cascade;
Re: Dropping User [message #410550 is a reply to message #410549] Sun, 28 June 2009 09:36 Go to previous message
Michel Cadot
Messages: 65155
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How does this answer to the question?
If OP dropped the user then he already knows your statement.

Regards
Michel
Previous Topic: Oracle Patchset 10gR2 10.2.0.3
Next Topic: Rc-50004 error when executing adcfgclone.pl
Goto Forum:
  


Current Time: Wed Aug 23 20:17:50 CDT 2017

Total time taken to generate the page: 0.06152 seconds