This discussion is archived
9 Replies Latest reply: May 14, 2013 9:37 PM by 894936 RSS

Script for Truncate tables.

894936 Newbie
Currently Being Moderated
First requirement: I need to create a script which truncates tables in the XYZ Schema with the below mentioned some of tables in XYZ schema

Create a script to truncate all tables in the XYZ schema. This includes the following tables:
•     ART_SUP_T
•     BATCH_DEF_T.................and so on ( 50 to 60 tables)
  • 1. Re: Script for Truncate tables.
    sb92075 Guru
    Currently Being Moderated
    891933 wrote:
    First requirement: I need to create a script which truncates tables in the XYZ Schema with the below mentioned some of tables in XYZ schema
    
    Create a script to truncate all tables in the XYZ schema. This includes the following tables:
    •     ART_SUP_T
    •     BATCH_DEF_T.................and so on ( 50 to 60 tables)
    Handle:     891933
    Status Level:     Newbie
    Registered:     Oct 17, 2011
    Total Posts:     66
    Total Questions:     24 (3 unresolved)

    when do you plan on actually writing your own code?

    writing a simple TRUNCATE statement should not be that difficult.

    Do you know how to Read The Fine Manual to learn the correct syntax?

    do similar to below
    SQL> select 'select count(*) from '||table_name ||';' from user_tables;
    
    'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
    ----------------------------------------------------
    select count(*) from SUB_NEMP;
    select count(*) from NEMP;
    select count(*) from SQL1;
    select count(*) from BALANCES;
    select count(*) from TAB1;
    select count(*) from ATTENDANCE;
    select count(*) from PDATES;
    select count(*) from STU_PLAN;
    select count(*) from TABLE1;
    select count(*) from RANGE;
    
    10 rows selected.
  • 2. Re: Script for Truncate tables.
    894936 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your comments...Truncating is simple... but i want to truncate only few of the selected tables....
    declare
    cursor v_cursor is select username from schematotruncate;
     v_username varchar(32);
    v_tablename varchar(32) := 't10';
    begin
    open v_cursor;
    if v_cursor%isopen then
    loop
    fetch v_cursor into v_username;
    exit when v_cursor%notfound;
    execute immediate 'truncate table ' || v_username || '.' || v_tablename;
    end loop;
    end if;
    end;
  • 3. Re: Script for Truncate tables.
    sb92075 Guru
    Currently Being Moderated
    891933 wrote:
    Hi,

    Thanks for your comments...Truncating is simple... but i want to truncate only few of the selected tables....
    Create a script to truncate all tables
    You can not truncate Parent Table when Child Table contains rows.
  • 4. Re: Script for Truncate tables.
    971895 Journeyer
    Currently Being Moderated
    add this in execute immediate..

    SELECT object_name FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE'
  • 5. Re: Script for Truncate tables.
    BluShadow Guru Moderator
    Currently Being Moderated
    891933 wrote:
    Hi,

    Thanks for your comments...Truncating is simple... but i want to truncate only few of the selected tables....
    So what is the actual problem you are having in doing that?

    You've explained a requirement, but you haven't explained what the issue is.
  • 6. Re: Script for Truncate tables.
    jeneesh Guru
    Currently Being Moderated
    sb92075 wrote:

    You can not truncate Parent Table when Child Table contains rows.
    Even if data is not there in Child table also, you cannot TRUNCATE parent table, right?

    You will get
    ORA-02266: unique/primary keys in table referenced by enabled foreign keys
  • 7. Re: Script for Truncate tables.
    894936 Newbie
    Currently Being Moderated
    Hi can you help me on truncating tables.
    
    For example i have table A,B,C... which are the parents tables.
    
    So i want to truncate these three tables by using some dynamic script...
    
    so can you help me by providing some sample script.
  • 8. Re: Script for Truncate tables.
    BluShadow Guru Moderator
    Currently Being Moderated
    891933 wrote:
    Hi can you help me on truncating tables.
    
    For example i have table A,B,C... which are the parents tables.
    
    So i want to truncate these three tables by using some dynamic script...
    
    so can you help me by providing some sample script.
    Why would you need a dynamic script to truncate a known list of tables?

    Explain your problem, or nobody can help.
  • 9. Re: Script for Truncate tables.
    894936 Newbie
    Currently Being Moderated
    Thank you, i got a solution.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points