OTN forum question about windows schedule batch

Today morning OTN user asked ,How do we do following scenario

“TASK: Create a script that would backup, truncate, and import data on the 2 schema of DEV server.
This script will be attached in the Scheduled Task in Windows.

I am task to periodically refresh a developement environment using the prod database for users testing
Every weekend, I have to export the PROD schema from the prod server and import it to the DEV schema at the dev server.

I am task to create a scheduled batch job (.bat) to do this activity at the DEV server side only. The PROD export file backup is assumed the be in place at dev server c:\prod1.dmp

1. export DEV schema (backup first)
2. kill all oracle user in dev server.
3. truncate all tables in DEV schema.
4. import the prod dump.

I have simple .bat script for #1,3,4 (e.g. exp dev_schema/dev_schema file=c:\dev_bak(date).dmp log=c:\dev_bak(date).log rows=y)

How do I make variable date in the backupfile?

Can you help me create .bat to kill oracle users? Can my truncate get error if there are users accessing the tables?

Do you have sample .bat script that check first the first program if successful, then proceed to next program? “

http://forums.oracle.com/forums/thread.jspa?threadID=2212289&tstart=0

I just worked out about the scenario and after done it I just posted here, It may get a idea for OTN user.

SQL> create table emptest(city varchar2(20));

Table created.

SQL> insert into emptest values('trichy');

1 row created.

SQL> insert into emptest values('dubai');

1 row created.

SQL> insert into emptest values('khobar');

1 row created.

SQL> insert into emptest values('dammam');

1 row created.

SQL> insert into emptest values('riyadh');

1 row created.

SQL> insert into emptest values('chennai');

1 row created.

SQL> commit;

Commit complete.

SQL>

Now I’m going to export particular table rows only

SQL> $exp tempuser/tempuser file=d:\temp\tempuser.dmp log=d:\temp\tempuser.log tables=emptest 
rows=y

Export: Release 11.2.0.2.0 - Beta on Mon Apr 25 10:58:16 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        EMPTEST          6 rows exported
Export terminated successfully without warnings.

SQL>

Now I’m going to write windows batch file for What OTN user asked
Open notepad and write it following scripts and Saved as D:\temp\exp.bat

C:

set oracle_sid=rmantbs

exp tempuser/tempuser file=d:\temp\tempuser01.dmp log=d:\temp\tempuser01.log direct=y consistent=y
 statistics='none'

sqlplus / as sysdba @D:\temp\killsession.sql

sqlplus tempuser/tempuser @D:\temp\truncate.sql

imp tempuser/tempuser file=d:\temp\tempuser.dmp log=d:\temp\tempuser.log tables=emptest 
rows=y ignore=y
 

Here I want explain about the above commands
C: –> I’m going to run from D:\temp path, so that I move to C: path
set oracle_sid=rmantbs — Set database name
exp — Export schema data
sqlplus / as sysdba @D:\temp\killsession.sql — Here I attached one killsession script here, this script killed who logged this particular schema,
Killsession.sql contain following

create or replace procedure kill_session( p_sid in number, p_serial# in number )
as
begin
 for x in ( select *
 from v$session 
 where username = 'TEMPUSER'
 and sid = p_sid
 and serial# = p_serial# )
 loop
 execute immediate 'alter system kill session ''' || 
 p_sid || ',' || p_serial# || '''';
 dbms_output.put_line( 'Alter session done' );
 end loop;
end;
/

exit
 
I got this script from http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6633782199868

and then
sqlplus tempuser/tempuser @D:\temp\truncate.sql — Here I attached one truncate.sql script here, this script used to truncate particular table and exit from sql prompt
truncate.sql script contain following

truncate table emptest;

exit
 

and finally
imp — I just imported table rows into particular truncate table.


Now It’s works fine.It may get idea for you, you can develop from this one 🙂