Dropping all the objects for a user
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Dropping all the objects for a user

  1. #1
    Join Date
    Aug 2002
    Posts
    1

    Question

    How to drop all the objects for a user? The user object should remain after the dropping of all the objects owned by it.


  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    uh? you have to drop all objects and the objects have to remain?

    hmm hmm hmm

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by sh_sonu
    The user object should remain after the dropping of all the objects owned by it.
    You mean to say.. you need to cleanup the USER. and DON't want to DROP USER (SCHEMA) (USER=SCHEMA)

    1. connect to the USER whos objects you want to drop.
    2. spool output of following sql

    SELECT 'DROP '||object_type||' '||object_name ||';' FROM user_objects

    3. and execute statements generated by spool file.

    You may get some errors while dropping objects having dependancies.. that u have to take care..

    HTH

    Sameer

    [Edited by Sameer on 08-26-2002 at 04:30 AM]

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Do you mean the USER must remain after the objects are gone?

    One way is to drop the user with the cascade option then recreate it. The other is to write some PL/SQL to identify and drop all the objects doing stuff like:

    BEGIN
    FOR cur_rec IN (SELECT object_type, object_name FROM user_objects) LOOP
    BEGIN
    EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' ' || cur_rec.object_name;
    EXCEPTION
    WHEN OTHER THEN
    NULL;
    END;
    END LOOP;
    END;
    /

    This may need a bit of work as I've not got a server available to check it at the moment.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Sep 2000
    Posts
    305
    execute this script you can also modify it as per your requirment

    set heading off
    set trimspool on
    set feedback off
    set echo off
    set termout off
    set pagesize 1000
    declare
    cursor cur_table_name is
    select distinct table_name
    from user_constraints;
    V_Tablename varchar2(100);

    begin
    open cur_table_name;
    loop
    fetch cur_table_name into V_Tablename;
    exit when cur_table_name%notfound;
    declare
    cursor cur_constraints is
    select CONSTRAINT_NAME
    from user_constraints where CONSTRAINT_TYPE='R'
    and table_name=V_Tablename;
    V_Constraint_name varchar2(100);
    begin
    open cur_constraints;
    loop
    fetch cur_constraints into V_Constraint_name;
    Exit when cur_constraints%notfound;
    EXECUTE IMMEDIATE 'alter table ' || V_Tablename ||
    ' drop constraint '||V_Constraint_name;
    end loop;
    close cur_constraints;
    end;
    end loop;
    close cur_table_name;
    end;
    /
    declare
    cursor cur_alltable_name is
    select table_name
    from cat where table_type='TABLE';

    V_AllTables varchar2(100);

    begin
    open cur_alltable_name;
    loop
    fetch cur_alltable_name into V_AllTables;
    exit when cur_alltable_name%notfound;
    EXECUTE IMMEDIATE 'drop table '|| V_AllTables;
    end loop;
    close cur_alltable_name;
    end;
    /

    declare
    cursor cur_sequence_name is
    select table_name
    from cat where table_type='SEQUENCE';
    V_AllTables varchar2(100);

    begin
    open cur_sequence_name;
    loop
    fetch cur_sequence_name into V_AllTables;
    exit when cur_sequence_name%notfound;
    EXECUTE IMMEDIATE 'drop sequence '|| V_AllTables;
    end loop;
    close cur_sequence_name;
    end;
    /
    declare
    cursor cur_function_name is
    select distinct name
    from user_source where type='FUNCTION';
    V_AllTables varchar2(100);

    begin
    open cur_function_name;
    loop
    fetch cur_function_name into V_AllTables;
    exit when cur_function_name%notfound;
    DBMS_OUTPUT.PUT_LINE(V_AllTables);
    EXECUTE IMMEDIATE 'drop function '|| V_AllTables;
    end loop;
    close cur_function_name;
    end;
    /
    declare
    cursor cur_procedure_name is
    select distinct name
    from user_source where type='PROCEDURE';
    V_AllTables varchar2(100);

    begin
    open cur_procedure_name;
    loop
    fetch cur_procedure_name into V_AllTables;
    exit when cur_procedure_name%notfound;
    EXECUTE IMMEDIATE 'drop procedure '|| V_AllTables;
    end loop;
    close cur_procedure_name;
    end;
    /
    declare
    cursor cur_procedure_name is
    select distinct name
    from user_source where type='PACKAGE BODY';
    V_AllTables varchar2(100);

    begin
    open cur_procedure_name;
    loop
    fetch cur_procedure_name into V_AllTables;
    exit when cur_procedure_name%notfound;
    EXECUTE IMMEDIATE 'drop PACKAGE BODY '|| V_AllTables;
    end loop;
    close cur_procedure_name;
    end;
    /
    declare
    cursor cur_procedure_name is
    select distinct name
    from user_source where type='PACKAGE';
    V_AllTables varchar2(100);

    begin
    open cur_procedure_name;
    loop
    fetch cur_procedure_name into V_AllTables;
    exit when cur_procedure_name%notfound;
    EXECUTE IMMEDIATE 'drop PACKAGE '|| V_AllTables;
    end loop;
    close cur_procedure_name;
    end;
    /

    declare
    cursor cur_view is
    select view_name
    from user_views;
    V_AllTables varchar2(100);

    begin
    open cur_view;
    loop
    fetch cur_view into V_AllTables;
    exit when cur_view%notfound;
    EXECUTE IMMEDIATE 'drop view '|| V_AllTables;
    end loop;
    close cur_view;
    end;
    /
    set heading on
    set feedback on
    set echo on
    set termout on



    SHAILENDRA

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Personally, I'd

    DROP USER username CASCADE;

    and then re-create the user as it was. Quickest and most simplistic.
    OCP 8i, 9i DBA
    Brisbane Australia

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