Removing Oracle Users

[Scott Buckingham]

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.

[A. Clay Stephenson]

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.

[Steven Gillard_2]

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]

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

[Scott Buckingham]

I don't seem to have the DBA_Segments table. This must be created from a script or a view of some sort?

[Tom Geudens · Accepted solution]

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

[Scott Buckingham]

It's amazing what you can do when are logged in correctly!! Thanks to all for your prompt and accurate (as always) responses!