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: 25050
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: 64152
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: 64152
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: 64152
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: create database service in linux by copy database files from windows
Next Topic: Trace procedure
Goto Forum:
  


Current Time: Sat Dec 10 20:30:29 CST 2016

Total time taken to generate the page: 0.25712 seconds