Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2005
    Posts
    53

    Unanswered: automate data import from production to test

    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

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    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.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jun 2005
    Posts
    53
    hi,

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

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    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.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Jun 2005
    Posts
    53
    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!!

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I tried and the problem with follwing script is keep looping

    a LOOP without any EXIT will LOOP forever
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2005
    Posts
    53
    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;

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jun 2005
    Posts
    53
    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?

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    then enter slash ("/") character
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jun 2005
    Posts
    53
    where do i need to enter / ?

    it works.

    thanks

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> declare
    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;
    /  2    3    4    5    6    7    8    9   10   11  
    exit;
    *
    ERROR at line 9:
    ORA-06550: line 9, column 1:
    PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside a loop
    ORA-06550: line 9, column 1:
    PL/SQL: Statement ignored
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •