Home » RDBMS Server » Server Utilities » Drop all users in the database
Drop all users in the database [message #249255] Tue, 03 July 2007 15:52 Go to next message
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 messageGo to next message
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 #249425 is a reply to message #249255] Wed, 04 July 2007 07:45 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

I have a question here in your answer Please.What is cascade means.

Again Thanks alot
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
Michel Cadot
Messages: 64103
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
Previous Topic: Reading Specific colums from data file
Next Topic: sql loader - ROWS value being changed during execution
Goto Forum:
  


Current Time: Sun Dec 04 19:08:45 CST 2016

Total time taken to generate the page: 0.04110 seconds