We have 7 test databases which we load with data from production. Regularly we have to trucate the tables and bulk load data from a data feed application. Is there anyway of using sql or pl/sql to write a program that will truncate all the tables or individually when needed? This will be done by operation staff at night. Also need one for export/import
Thanks in advance
You can setup a batch file that can run a schedule to run @ night
NOTE: this is using Windows
first run an sql file that will truncate the tables you want to delete data out of.
the run the sqlldr to import the data into Oracle.
REM truncate tables
sqlplus scott/tiger @c:\temp\truncate.sql
REM bulkload data into Oracle
sqlplus userid=scott/tiger contol=d:\temp\test.ctl log=d:\temp\log\test.log
Within the body of the "truncate.sql"
truncate table temp1
truncate table temp2
truncate table temp3
hope this helps
in a PL/SQL-Procedure or script, you can use the following command:
EXECUTE IMMEDIATE 'truncate table dingsda';
Assuming that you do not want to clear all tables of the database, but just of one schema in an instance, use a Cursor-Loop feeded by 'select table_name from user_tables' and concat the table_name to your truncate-command, then execute it:
CURSOR c_tables IS
SELECT table_name FROM user_tables;
FOR r_tables IN c_tables LOOP
EXECUTE IMMEDIATE 'truncate table '||r_tables.table_name;
To schedule, you could (on Unix) use the cron to start the scripts via an sql-plus call, or use the DBMS_JOB-package.
Click Here to Expand Forum to Full Width