9 Replies Latest reply: May 14, 2013 11:37 PM by 894936 RSS

    Script for Truncate tables.

    894936
      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
          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
            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
              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
                add this in execute immediate..

                SELECT object_name FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE'
                • 5. Re: Script for Truncate tables.
                  BluShadow
                  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
                    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
                      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
                        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
                          Thank you, i got a solution.