Hi,
oracle 11.2.0.1 OS windows 2008 R2

I want to automate the import from production to test.

1) export the production schema
2) import in to test server?

How can i automate that currently i am doing it manually as follow:

1) expdb the production schema
2) kill all connection on the test server to test schema
3) drop test user cascade;
4) recreate user;
5) impdb the production schema to test:

but i want it to automated or scheduled so i dont; have to log in every night!!

Please guide

thanks

A quick & dirty way to do this is to create a .bat file that calls SQL scripts. Then create a Windows scheduled task, to run the .bat file at whatever time/frequency you want.

hi,

how can i do that. i need assistance on that.
thanks

Create as many SQL scripts as you need, putting the requisiste SQL command/s in each script - don't forget to end each script with an extra '/' or the script won't close once it's run.

Then create a .bat file. In the file have a line for each sql script that you wish to run, looking something like this:

sqlplus <username>/<password>@<db_name> @<scriptname>

Then use the Windows Scheduler to create a job that runs the .bat file at whatever time/frequency you want.

You say that you are already doing the steps manually, so you already know what the commands are - it's just a case of inserting them into scripts. There are loads of examples available on the internet - just google for them.

Hi,

I tried and the problem with follwing script is keep looping please help me:

i am saving the script as test.sql

and then calling in batch file as following:

sqlplus user/password@instance as sysdbda @E:/script/killsession.sql

and the killsession .sql is as follow:

begin
for sessions in ( select sid, serial#
from v$session
where schemaname = 'schemaname')
loop
execute immediate 'alter system kill session '''||sessions.sid||','||sessions.serial#||'''';
end loop;
end;


please help!!

>I tried and the problem with follwing script is keep looping

a LOOP without any EXIT will LOOP forever

Hi,

I do have exit in the code but when i try to run command prompt it ask me to input on 10::::

begin
for sessions in ( select sid, serial#
from v$session
where schemaname = 'test')
loop
execute immediate 'alter system kill session '''||sessions.sid||','||sessions.serial#||'''';
end loop;
exit;
end;

when EXIT exists outside of LOOP & END LOOP, it never gets executed.

If/when EXECUTE IMMEDIATE ever succeeds, the whole instance will CRASH & Terminate;
since all Oracle background sessions will be stopped.

Hi,

I tried that but when i try to launch cmd it gets to sql connect fine but then it stands at 10 , why!

I am just killing the user connected to the test schema not whole instance?

then enter slash ("/") character

where do i need to enter / ?

it works.

thanks