topic Re: Removing Oracle Users in Operating System - HP-UX

Removing Oracle Users

Scott Buckingham — Tue, 04 Dec 2001 16:51:06 GMT

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.

Re: Removing Oracle Users

A. Clay Stephenson — Tue, 04 Dec 2001 16:55:54 GMT

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.

Re: Removing Oracle Users

Steven Gillard_2 — Tue, 04 Dec 2001 16:58:34 GMT

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

Re: Removing Oracle Users

Craig Rants — Tue, 04 Dec 2001 17:02:20 GMT

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

Re: Removing Oracle Users

Scott Buckingham — Tue, 04 Dec 2001 17:05:05 GMT

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

Re: Removing Oracle Users

Tom Geudens — Tue, 04 Dec 2001 17:19:40 GMT

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

Re: Removing Oracle Users

Scott Buckingham — Tue, 04 Dec 2001 18:07:03 GMT

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