Drop all users in the database [message #249255] |
Tue, 03 July 2007 15:52 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
qasim845
Messages: 95 Registered: March 2007 Location: Philadelphia
|
Member |
|
|
Is there anyway i can get the script which will drop all user from the database which i created by using the following script. Everything which i created by this script, dropped out.
Actually by mistaken i import the user from the test database, but i need it from production database.
select 'create user '||upper(USERNAME)||' identified by '||USERNAME||chr(10)||' DEFAULT TABLESPACE USERS '||CHR(10)||
' TEMPORARY TABLESPACE TEMP1 '||CHR(10)||
' PROFILE DEFAULT'||CHR(10)||
' ACCOUNT UNLOCK;'||CHR(10)||
' GRANT SELECT_CATALOG_ROLE TO '||USERNAME||';'||CHR(10)||
' ALTER USER '||USERNAME||' DEFAULT ROLE ALL;'||CHR(10)||
' GRANT CREATE SYNONYM TO '||USERNAME||';'||CHR(10)||
' ALTER USER '||USERNAME||' QUOTA UNLIMITED ON USERS;'||CHR(10)||CHR(10)
from dba_users;
Oracle 9.2.6.0
Thanks in advance
|
|
|
Re: Drop all users in the database [message #249257 is a reply to message #249255] |
Tue, 03 July 2007 16:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
DreamzZ
Messages: 1666 Registered: May 2007 Location: Dreamzland
|
Senior Member |
|
|
SQL> SET HEADING OFF
SQL> spool /export/home/oracle/dropuser.sql
SQL> ;
1 SELECT 'DROP USER '||USERNAME||' CASCADE ;' FROM DBA_USERS
2* where username not in ('SYS','SYSTEM','DBSNMP','DIP','OUTLN')
SQL> /
DROP USER NIWS_USER CASCADE ;
DROP USER RPT CASCADE ;
DROP USER ORACLE CASCADE ;
DROP USER GPAR CASCADE ;
DROP USER NIWSRPT CASCADE ;
DROP USER TSMSYS CASCADE ;
6 rows selected.
SQL> spool off
SQL> set heading on
SQL>
SQL> !ls /export/home/oracle/dropuser.sql
/export/home/oracle/dropuser.sql
SQL>@/export/home/oracle/dropuser.sql
[Updated on: Tue, 03 July 2007 16:08] Report message to a moderator
|
|
|
|
Re: Drop all users in the database [message #249428 is a reply to message #249425] |
Wed, 04 July 2007 08:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
It means "including all objects owned by the user".
It is mandatory if the user is a owner.
Btw, this is in the documentation: SQL Reference.
Regards
Michel
|
|
|