Re: ORACLE V7 user names and tables

In article <1992Jan23.114136.38_at_hhcs.gov.au>, pihlab_at_hhcs.gov.au writes:
>

>> I work with several different "flavors" of databases, and with the
>> others, 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!

Every semester, I have to clean up about 400 student users from one of our academic databases. Since v6.0.33, I could not use DROP TABLESPACE INCLUDING CONTENT because of errors ORA-00604 and ORA-02273. So I now have got automatic procedures to delete/drop/revoke everything belonging to a user, but they are painfully slow due to referential integrity checking. Moreover, I still have to perform quite a few manual tasks relating to reorganizing tablespaces.

Late last year, I inquired to ORACLE about whether there would be some kind of COMPRESS or UPDATE function in V&7. The answer I got was DROP USER CASCADE would drop all objects (tables, synonyms, sequences, indexes, views) belonging to the user as well as deleting all his/her entries in menu, forms, reportwriter tables. Space would be freed for use but no defragmentation.

Can those sites that have got V7 check out whether DROP USER CASCADE can delete rows in menu, forms and reportwriter tables. Is it fast? Whether DROP TABLESPACE xxx INCLUDING CONTENTS works under V7. Can it also perform DROP USER CASCADE implicitly? Are there any VERIFY, CLEAN-UP functions in V7? Any database reorganization without full export and import?

Thanks in advance.

Howhie Ling
Computing Services Dept.
Queensland University of Technology
Brisbane
Australia Received on Fri Jan 24 1992 - 16:37:41 CET