当前位置: 动力学知识库 > 问答 > 编程问答 >

windows - How create Batch script to automate DB Dump import into Oracle 11g database with username and password?

问题描述:

I need to create a batch script for these following tasks:

  1. Drop the oracle User

  2. Create the Oracle User the user

  3. Grant permission as dba from sqlplus cammand prompt

  4. Importing Oracle 11g dump

And from the command prompt I want to import the Oracle 11g dump.

In normal way these steps I follow always:

step 1: Open the command prompt and start SQL*Plus:

 C:\user>sqlplus sys/[email protected] as sysdba;

step 2: Droping existing User

 SQL:> DROP USER TEST CASCADE;

step 3: Creating User and granting dba permission,

 SQL:> CREATE USER TEST identified by test123 default tablespace users temporary tablespace temp quota unlimited on users;

SQL:> GRANT dba to TEST;

SQL:> COMMIT;

SQL:> EXIT;

step 4: Back to command prompt importing the oracle dump on newly created user.

imp userid ='TEST/[email protected]' file='D:\Dumps\Oracle\Imported\exp_TEST1_at_MYDEV_20121108.dmp' log='D:\Dumps\OracleLog\NewImp160913_run.log' rows=y full=y ignore=y;

Using these above steps I am able to drop the user, create the user and import the oracle 11g dump successfully.

I need to do these tasks using a batch script.

网友答案:

As a SQLPLUS script (thebatch.sql):

DROP USER TEST CASCADE;
CREATE USER TEST identified by test123
    default tablespace users temporary tablespace temp
    quota unlimited on users;
GRANT dba to TEST;
HOST imp userid ='TEST/[email protected]' file='D:\Dumps\Oracle\Imported\exp_TEST1_at_MYDEV_20121108.dmp'  log='D:\Dumps\OracleLog\NewImp160913_run.log' rows=y full=y ignore=y; 
QUIT

Now, I'm not a DOS scripting expert, but you could have a SQL script (the_drop_crt_user.sql) as follows:

DROP USER TEST CASCADE;
CREATE USER TEST identified by test123
        default tablespace users temporary tablespace temp
        quota unlimited on users;
GRANT dba to TEST;
QUIT

Then a DOS script that runs the sql script and then runs the IMP:

SQLPLUS sys/[email protected] as sysdba @ the_drop_crt_user.sql
IMP userid=.....
分享给朋友:
您可能感兴趣的文章:
随机阅读: