9 Replies Latest reply: Jun 21, 2008 4:02 AM by 532188 RSS

    drop user cascade

    DBA2011
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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')
                          ...