This discussion is archived
9 Replies Latest reply: Jun 21, 2008 2:02 AM by 532188 RSS

drop user cascade

DBA2011 Newbie
Currently Being Moderated
I want to drop all users created on 19-JUN-08, there are 100+ users got created with unsuccessful import.

Is there single command to drop all of them

SQL>drop user cascade where created='19-JUN-08';
  • 1. Re: drop user cascade
    449681 Newbie
    Currently Being Moderated
    I would do it using below.

    select 'drop user '||username||' cascade;' from dba_users where trim(created)='19-JUN-08'

    Thanks,
    Ankit.
  • 2. Re: drop user cascade
    247514 Expert
    Currently Being Moderated
    I would do it using below.

    select 'drop user '||username||' cascade;' from
    dba_users where trim(created)='19-JUN-08'

    Thanks,
    Ankit.
    Of course when you run the command make sure your session nls_date_format is 'DD-MON-YY' etc.
  • 3. Re: drop user cascade
    DBA2011 Newbie
    Currently Being Moderated
    select 'drop user '||username||' cascade;' from dba_users where trim(created)='19-JUN-08'

    this will only select , will not drop all of them, i am looking to drop all users cascad on this date...so that i can run my imp again after unsuccessful attempt
  • 4. Re: drop user cascade
    532188 Newbie
    Currently Being Moderated
    May be you will think yourself a little?

    begin
    for x in (SELECT 'drop user ' || username || ' cascade' as drop_sql FROM dba_users WHERE to_char(created, 'DD-MON-YY') = '19-JUN-08') loop
    execute immediate x.drop_sql;
    end loop;
    end;
    /
  • 5. Re: drop user cascade
    247514 Expert
    Currently Being Moderated
    Yes, Pavel gave a good example how it's done.

    If you are not comfortable to run the drop user on the fly, you can spool out the result, double check it and run.
  • 6. Re: drop user cascade
    DBA2011 Newbie
    Currently Being Moderated
    SQL> select 'drop user '||username||' cascade;' from dba_users where trim(created)='21-JUN-08';

    'DROPUSER'||USERNAME||'CASCADE;'
    -------------------------------------------------
    drop user TEST1 cascade;
    drop user TEST cascade;

    SQL> begin
    2 for 1 in (SELECT 'drop user ' || username || ' cascade' as drop_sql FROM dba_users WHERE to_char(created, 'DD-MON-YY') = ''21-JUN-08')
    loop
    3 execute immediate 1.drop.sql;
    4 end loop;
    5 end;
    6 /
    ERROR:
    ORA-01756: quoted string not properly terminated


    what i am doing wrong? & what i should user for x
  • 7. Re: drop user cascade
    247514 Expert
    Currently Being Moderated
    Why you want to replace x with 1 ?

    x is just a variable name, any legal Oracle variable name will do. Certainly 1 is not valid variable name.
  • 8. Re: drop user cascade
    Letme2bDBA Newbie
    Currently Being Moderated
    I think SELECT command should be use rather than script.

    You can Set Heading off and PageSize to 1000 and set spool on.

    When you will do all of this and run query then you will have a resultant file (specified with spool e.g. spool /test.txt [linux] c:\test.txt [win] ) which contains some thing like this
    drop user a cascade.

    Please try this because all select list would be dumped into destination file.
    dont forget to execute spool off.

    Aqeel Nawaz
  • 9. Re: drop user cascade
    532188 Newbie
    Currently Being Moderated
    You are wrong ALSO in double '' against single ' in SQL (after ****):

    ...
    2 for 1 in (SELECT 'drop user ' || username || ' cascade' as drop_sql FROM dba_users WHERE to_char(created, 'DD-MON-YY') = **** ''21-JUN-08')
    ...