Re: ORACLE user names and tables

> ... if you decide to delete a user and all of that user's associated
> data, it's quite easy , but in ORACLE, once you create a user name, it's
> there for life, it seems, unless someone can tell me otherwise.
>
> It seems to me that this would be a VERY basic function, being able to undo
> what you do, or to delete a user and that user's tables with one command,
> but I HAVE NEVER FOUND IT!
>
> Any help is DEEPLY appreciated, thanks!

I assume you're talking about Oracle V6+.

Oracle Corp. seem to build "building blocks" and expect you to do all the building. Its only when they notice someone making money with a particular arrangement of building blocks that they decide to make one themselves. But hey, that's life in a capitalist society 8^).

Under V5 and V6 you will need to put together a SQL script to search for and drop all objects belonging to that user and then revoke access for that user. Under V5, the user would then disappear from the database completely whereas under V6 you still have a user entry.

I agree with you that it should have gotten rid of the user entry too but, hey who are we to tell Oracle Corp. which way to rotate the world. I'm hoping that they enhance this oversight out in a later release.

The only way I've found to get rid of a user entry is to do the above object purge and revoke the user AND THEN take a full EXPORT of your database, rebuild it and IMPORT it all again ... the user disappears. A lot of work for a little satisfaction so I don't bother anymore; it's only one record. If you have a very volatile list of users being added and then removed from your system then try giving them names from a list (USER1, USER2 ... USER321 etc) and you have to keep track of which ones are still active and which are available for reassignment.

Bruce... pihlab_at_hhcs.gov.au Received on Wed Jan 15 1992 - 14:16:55 CET