Results 1 to 3 of 3

Thread: How to drop all tables from a user

  1. #1
    Junior Member
    Join Date
    Dec 2006
    Answers
    5

    How to drop all tables from a user

    Do help anybody about the query how to drop all tables from a user without droping the user.


  2. #2
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: How to drop all tables from a user

    Hi akmohanty,

    Connect to the user of which the tables have to be dropped.
    Using the following select query, the script for dropping all the tables can be prepared.
    SQL> select 'drop table ' || tname || ' ;' from tab ;

    Spool the output in a file & the file has to be executed, so that all the tables of the user will be dropped.
    U can use "set heading off" so that the headings display will be eliminated.

    Hope it helps u.

    Last edited by Innila; 03-05-2007 at 01:41 AM.
    *** Innila ***

  3. #3
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: How to drop all tables from a user

    create or replace PROCEDURE drop_many_tables
    (tablename IN VARCHAR2)
    IS
    cur integer;
    begin
    cur:= dbms_sql.OPEN_CURSOR();
    for t in (select object_name from user_objects where object_type='TABLE' and object_name like tablename) loop
    execute immediate
    'drop table ' ||t.object_name|| ' cascade constraints';
    end loop;
    dbms_sql.close_cursor(cur);
    end drop_many_tables;
    Try this ...............


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact