OraFAQ Forum: Server Utilities » Drop all users in the database

Home » RDBMS Server » Server Utilities » Drop all users in the database

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Drop all users in the database [message #249255]

Tue, 03 July 2007 15:52

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

Report message to a moderator

Re: Drop all users in the database [message #249257 is a reply to message #249255]

Tue, 03 July 2007 16:03

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

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

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Reading Specific colums from data file
Next Topic:	sql loader - ROWS value being changed during execution

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 05:41:06 CST 2025