Droping all user's from u database at a time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Droping all user's from u database at a time

  1. #1
    Join Date
    Aug 2001
    Location
    cuddapah
    Posts
    145

    Cool

    !hi
    I tested these script on oracle9i DATABASE on winNT Platform. it works succesfully .


    set timing on
    set serveroutput on

    create or replace procedure all_drop is

    c_n integer;
    string varchar2(100);

    cursor cc is select username from dba_users where username not in ('SYS','SYSTEM');
    x number:=0;
    begin

    for i in cc loop

    string:='drop user '||i.username ||' cascade';
    c_n :=dbms_Sql.open_cursor;

    dbms_sql.parse(c_n,string,dbms_sql.native);

    dbms_sql.close_cursor(c_n);
    x:=x+1;
    end loop;
    dbms_output.put_line('Total No of user drop from u database :: '|| x);
    end all_drop ;

  2. #2
    Join Date
    Nov 2000
    Posts
    9

    EXECUTE IMMEDIATE ????


    Why didn't you use EXECUTE IMMEDIATE?
    Michael T Hudacko
    Senior Oracle DBA

  3. #3
    Join Date
    Jul 2002
    Posts
    6

    Exclamation A better WHERE clause

    There are several 'users' created in 9i starter db that should NOT be deleted. a few examples: OUTLN, AURORA..., MDSYS, DBSNMP, etc.

    A better shoice of a WHERE clause might be where default_tablespace<>'SYSTEM'
    vaidis
    Senior Oracle DBA

  4. #4
    Join Date
    Feb 2003
    Location
    Kolkata, India
    Posts
    33
    Why don't you try to drop the respective tablespaces instead.
    I feel that's a more efficient way to deal ain't it?
    DROP TABLESPACE INCLUDING CONTENTS;

  5. #5
    Join Date
    Feb 2004
    Location
    India
    Posts
    18
    I think dropping tablespaces does not drop users.
    U have user a say YYY who has a default tablespace as say XXX.
    Drop that tablespace using
    Drop tablespace XXX including contents and datafiles;
    drops the tablespace XXX but not the user YYY.


    Kaushik

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width