Truncate Oracle schema

Dear Oracle gurus,

I am looking of a solution where a oracle stored procedure can take schema name as input parameter and truncate all the tables inside the schema. The truncate should also take care of any referential integrity between tables in that particular schema.

Thanks in advance
Divya
Divya RajagopalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
This is very dangerous and I don't know why you would want to do it.  But, this should work.  Didn't test every possible scenario and read the comment on enabling constraints after process completes.  This also does not account for constraints that were disabled before the process started and will try to enable them again.  Basically, this was just thrown together.  It works, but if I was going to really do it, I would only enable the constraints that I disabled.  You could pretty easily keep track and even have the first statement that generates the disable statements also generate the enable statements and then just store them somewhere.  Keep in mind this needs to be run from an account with very elevated privileges as you need to have access to all schemas.
DECLARE 
    v_owner VARCHAR(100) := 'EE'; 
BEGIN 
    -- disable constraints 
    FOR c IN (SELECT 'alter table "' 
                     || owner 
                     ||'"."' 
                     || table_name 
                     || '" disable constraint "' 
                     || constraint_name 
                     || '"' sql_stmt 
              FROM   dba_constraints a 
              WHERE  r_owner = v_owner 
                     AND constraint_type = 'R') LOOP 
        BEGIN 
            EXECUTE IMMEDIATE c.sql_stmt; 
        END; 
    END LOOP; 

    -- truncate tables 
    FOR c IN (SELECT 'truncate table "' 
                     || owner 
                     || '"."' 
                     || table_name 
                     || '"' sql_stmt 
              FROM   dba_tables 
              WHERE  owner = v_owner) LOOP 
        BEGIN 
            EXECUTE IMMEDIATE c.sql_stmt; 
        END; 
    END LOOP; 

    -- enable constraints 
    -- NOTE:  Cannot enable constraints for other schemas as those tables were not truncated 
    FOR c IN (SELECT 'alter table "' 
                     || owner 
                     ||'"."' 
                     || table_name 
                     || '" enable constraint "' 
                     || constraint_name 
                     || '"' sql_stmt 
              FROM   dba_constraints 
              WHERE  owner = v_owner 
                     AND r_owner = v_owner 
                     AND constraint_type = 'R' 
                     AND status = 'DISABLED') LOOP 
        BEGIN 
            EXECUTE IMMEDIATE c.sql_stmt; 
        END; 
    END LOOP; 
END; 

/ 

Open in new window

Divya RajagopalAuthor Commented:
Thank you Sir. This code worked. I actually created as stored proc out of it.

CREATE OR REPLACE PROCEDURE TruncTables(v_owner VARCHAR2)
IS
<< Your code>

I executed from command line and it works perfectly.

exec TruncTables('TESTPY1')  

TESTtPY1 is my schema and I was able to truncate data in all the tables in the schema.

I tried calling the same storeproc from Python script and I am getting the following error:
cur.callproc('TruncTables',('TESTPY1'))
cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TRUNCTABLES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


I am not sure why it keeps saying wrong number or types of arguments and it works fine when executed from TOAD.

Any thoughts , I will really appreciate.

Thanks
DIvya
johnsoneSenior Oracle DBACommented:
I really don't know about using Python.

There is an oracle reference here, which seems to imply that you should be doing this:

cur.callproc('TruncTables',['TESTPY1'])

However, I'm not really sure if that is correct syntax with Python or not.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
I can't help you with Python syntax but I strongly agree with Johnsone's comment: "This is a very dangerous procedure!"  It would be much safer if you add a check at the top of the procedure for the schema name that was passed in, and you only allow this to continue if the schema name is a name that you want this to happen with.  For example, if you want this to work for schemas named 'TEST' and 'SAMPLE'  only you could add this logic:
CREATE OR REPLACE PROCEDURE TruncTables(v_owner VARCHAR2)
 IS
BEGIN
  if upper(v_owner) in ('TEST','SAMPLE') then
    null;
  else
   raise_application_error(-20001,'Error - Incorrect table owner');
  end if;
  [your code]
end;

Open in new window

Divya RajagopalAuthor Commented:
johnsone - You rock :-) . Thanks a bunch sir.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.