Skip to content

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? “

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.


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 

Export: Release - 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 - 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.


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


set oracle_sid=rmantbs

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

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 )
 for x in ( select *
 from v$session 
 where username = 'TEMPUSER'
 and sid = p_sid
 and serial# = p_serial# )
 execute immediate 'alter system kill session ''' || 
 p_sid || ',' || p_serial# || '''';
 dbms_output.put_line( 'Alter session done' );
 end loop;

I got this script from

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;


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 🙂

6 Comments Post a comment
  1. Thanks Azar for good explanation.

    April 26, 2011
  2. narayanareddy #

    could u explain following issues pls?
    how to changes meta data,
    what type of changes doing with metadata,
    i am trying to consolidate the data but its not consolidate its happens error why?
    i am trying to loading metadata but its not loading what the problem?

    May 19, 2011
  3. marinapolevayarrr #

    Привет милые люди,мой новый сайт с моими танцами…

    May 27, 2011
  4. Luna #

    Ребята , подскажите , кто знает или сталкивался.

    Есть потребность купить украшение с бриллиантом массой более карата, но осознаю, что это стоит очень дорого и мне не по карману.

    Но читала , что есть облагороженные бриллианты, которые ничем не отличаются от обычных, но стоят меньше в

    два раза.

    Кто-нибудь вообще держал такие в руках, они правда красивы ?

    June 5, 2011

Trackbacks & Pingbacks

  1. Today morning OTN user asked ,How do we do following scenario | Oracle in Thai | Oracle in Thai | Oracle User Group in Thailand |

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: