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 🙂
DBA 
