Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Removing Oracle Users

SOLVED
Go to solution
Scott Buckingham
Regular Advisor

Removing Oracle Users

What is the proper way to remove old users from an Oracle database? I was hoping that removing them using OEM would free up some space in our DBF files, but that does not appear to be the case (they look as full as ever!) I really want to just get rid of some old data that is no longer used. This is an Oracle 7.3.4 DB on an HP-UX 10.20 machine.
Long time dabbler, first time Admin / DBA
6 REPLIES
A. Clay Stephenson
Acclaimed Contributor

Re: Removing Oracle Users

Hi Scott:

svrmgrl and connect as sys or system

drop user bill;
OR drop user bill cascade;

The 2nd form will drop the user plus remove any objects owned by the user so be careful with that one.

If it ain't broke, I can fix that.
Steven Gillard_2
Honored Contributor

Re: Removing Oracle Users

Are you sure that the users you've dropped were the main space wasters?

Try the following query:

select owner, sum(bytes) from dba_segments group by owner;

That will display each users space usage in bytes.

Regards,
Steve
Craig Rants
Honored Contributor

Re: Removing Oracle Users

How are determining the space differences. There have been a few threads going around lately that talk about a sparse file system issue where one command will see the allocation of space as opposed to the actual size of the space used. I may not have explained this just right, but it could be a consideration if you feel you have properly removed your oracle users.

Good Luck,
C
"In theory, there is no difference between theory and practice. But, in practice, there is. " Jan L.A. van de Snepscheut
Scott Buckingham
Regular Advisor

Re: Removing Oracle Users

I don't seem to have the DBA_Segments table. This must be created from a script or a view of some sort?
Long time dabbler, first time Admin / DBA
Tom Geudens
Honored Contributor
Solution

Re: Removing Oracle Users

Hi,
DBA_SEGMENTS is always created together with the database. However, it is a view and maybe the user your connected with doesn't have a synonym for it.
Try ... from SYS.DBA_SEGMENTS instead ...

Hope it helps,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
Scott Buckingham
Regular Advisor

Re: Removing Oracle Users

It's amazing what you can do when are logged in correctly!! Thanks to all for your prompt and accurate (as always) responses!
Long time dabbler, first time Admin / DBA