14 Replies Latest reply on Jan 18, 2006 11:10 PM by 3073

    Truncate all tables in schema

    3073
      Hi,
      I want to truncate all tables (300 tables) in a schema. There are some enabled constraints in this schema. I want to keep all tables attached objects (like indexes, constraints, etc). I’d like to create a procedure to do this job. What's the safe and right way for this?

      I am thinking as follows:
      1- Retrieve all user constraints name using user_constrants view and then disable them.
      2- Retrieve all user tables name using user_tables view and then Truncate them.
      3- Enable all constraints that were disabled in step 1.

      Is this a safe and right way?
      Let me know your thought please. Thanks.
        • 1. Re: Truncate all tables in schema
          Dmytro Dekhtyaryuk
          Hallo,
          yes, I think it's a right way.

          Don't forget to analyze the tables after truncate.

          Regards
          Dmytro
          • 2. Re: Truncate all tables in schema
            dainge
            1- Retrieve all user constraints name using user_constrants view and then disable them.
            It's enough to disable the foreign key constraints. That will also make your procedure easier to write as only one syntax is needed.
            • 3. Re: Truncate all tables in schema
              12826
              To analyze all the tables, just use

              DBMS_STATS.GATHER_SCHEMA_STATS('schema name')
              • 4. Re: Truncate all tables in schema
                3073
                Thanks for your replies.
                One question, why I should analyze the schema after all?
                Thanks.
                • 5. Re: Truncate all tables in schema
                  12826
                  You would analyze to keep the statistics on the tables current. Helps performance. You can analyze after loading data as well - preferably
                  • 6. Re: Truncate all tables in schema
                    3073
                    Great.Thanks for your help.
                    • 7. Re: Truncate all tables in schema
                      6363
                      Why analyze after truncating all the tables? What use is querying a totally empty schema? Will performance matter?

                      I suppose these tables could be queried along with none empty tables in another schema, but that would be unusual to run a query where half the data has been indiscriminately wiped out.

                      Of course if the tables are reloaded the schema should be analyzed after the load.
                      • 8. Re: Truncate all tables in schema
                        Dmytro Dekhtyaryuk
                        Why analyze after truncating all the tables? What use
                        is querying a totally empty schema? Will performance
                        matter?

                        I suppose these tables could be queried along with
                        none empty tables in another schema,
                        3360, you are right, for this purpose. We had several cases, that CBO chooses wrong plan by JOIN between empty and non-empty table, why it used statistic , gathered before truncate.

                        Besides that, it is very fast to analyze empty tables :-)

                        Regards
                        Dmytro
                        • 9. Re: Truncate all tables in schema
                          6363
                          3360, you are right, for this purpose. We had several
                          cases, that CBO chooses wrong plan by JOIN between
                          empty and non-empty table, why it used statistic ,
                          gathered before truncate.
                          Okay I guessed that might be an issue.

                          >
                          Besides that, it is very fast to analyze empty tables
                          :-)
                          Yes, agreed, cannot argue with that.

                          The other possibility though is that the data will get reloaded before the tables are queried on a regular basis. In this case analyzing after the load, if the data is significantly different, will be more important.
                          • 10. Re: Truncate all tables in schema
                            Dmytro Dekhtyaryuk
                            The other possibility though is that the data will
                            get reloaded before the tables are queried on a
                            regular basis. In this case analyzing after the
                            load
                            , if the data is significantly different,
                            will be more important.
                            3360, I am fully agree.

                            Regards
                            Dmytro
                            • 11. Re: Truncate all tables in schema
                              3073
                              I came up with an issue when I wanted to truncate all tables in schema. Looks like Some tables’ name are not valid like A!, A-B, etc (I don't know how they are created). This causes my loop to terminate. How can I validate a table name before Truncate statement in loop so I can use IF statement to skip non-valid table names.
                              Thanks.
                              • 12. Re: Truncate all tables in schema
                                12826
                                The table was probably created using double qoutes.
                                declare
                                v_new_tab_name varchar2(35);
                                for c1 in (select table_name from user_tables) loop
                                   begin
                                       execute immediate ( 'truncate table '||C1.Table_Name );
                                   exception
                                      when others then
                                          v_new_tab_name := '"' || C1.Table_Name|| '"' ;
                                          execute immediate (' truncate table '||v_new_tab_name) ;
                                   end ;
                                end loop;
                                end;
                                /                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                                • 13. Re: Truncate all tables in schema
                                  Michel SALAIS
                                  You have to put these table names in double quotes ' " ' to go with it. I must say that any script using dictionary to do something like that should do it ...
                                  • 14. Re: Truncate all tables in schema
                                    3073
                                    Thanks. Double quote helped.