Re: ORACLE user names and tables

In article <1992Jan15.131655.32_at_hhcs.gov.au>, pihlab_at_hhcs.gov.au writes...
>
>> ... 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.
>>
>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.

I found that you could reuse user entries by directly accessing the data dictionary table with SQL scripts and UPDATEing rows. Oracle advises against ever directly touching the data dictionary tables and I tend to agree with them except for this specific case. I did this regularily when I was DBA for the Canadaian subsiduary of a major database vendor, your milage may vary.

--
    Norman Soley, Specialist, Professional Software Services, ITC District
    Digital Equipment of Canada                   soley_at_trooa.enet.dec.com
    Opinions expressed are mine alone  and do not reflect those of Digital 
    Equipment Corporation or my cat Marge.