EXP/IMP批量用户导入导出

如果在做数据迁移时，用户量特别大的时候，可以考虑利用脚本批量完成导入导出配置

1.用户导出：

SYS@orcl> set linesize 999

SYS@orcl> set pagesize 999

SYS@orcl> select 'exp \''/ as sysdba\'' buffer=65535 file=/oracle_exp/expdata/'||username||'.dmp log=/oracle_exp/explog/'||username||'.log'  from dba_users where username in ('SCOTT','HR','SH','OE');

'EXP\''/ASSYSDBA\''BUFFER=65535FILE=/ORACLE_EXP/EXPDATA/'||USERNAME||'.DMPLOG=/ORACLE_EXP/EXPLOG/'||USERNAME||'.LOG'

------------------------------------------------------------------------------------------------------------------------------------------------------

exp \'/ as sysdba\' buffer=65535 file=/oracle_exp/expdata/SCOTT.dmp log=/oracle_exp/explog/SCOTT.log

exp \'/ as sysdba\' buffer=65535 file=/oracle_exp/expdata/HR.dmp log=/oracle_exp/explog/HR.log

exp \'/ as sysdba\' buffer=65535 file=/oracle_exp/expdata/OE.dmp log=/oracle_exp/explog/OE.log

exp \'/ as sysdba\' buffer=65535 file=/oracle_exp/expdata/SH.dmp log=/oracle_exp/explog/SH.log

2.创建表空间：

SYS@orcl> select 'create tablespace "'||tablespace_name||'" datafile ''/data_01/oradata/orcl/'||tablespace_name||'_01.dbf'' size 2g;' from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','USERS');

'CREATETABLESPACE"'||TABLESPACE_NAME||'"DATAFILE''/DATA_01/ORADATA/ORCL/'||TABLESPACE_NAME||'_01.DBF''SIZE2G;'

----------------------------------------------------------------------------------------------------------------------------------

create tablespace "TEMP1" datafile '/data_01/oradata/orcl/TEMP1_01.dbf' size 2g;

create tablespace "ZYX" datafile '/data_01/oradata/orcl/ZYX_01.dbf' size 2g;

create tablespace "bt-data" datafile '/data_01/oradata/orcl/bt-data_01.dbf' size 2g;

create tablespace "BASE" datafile '/data_01/oradata/orcl/BASE_01.dbf' size 2g;

create tablespace "TEMP2" datafile '/data_01/oradata/orcl/TEMP2_01.dbf' size 2g;

create tablespace "EXAMPLE" datafile '/data_01/oradata/orcl/EXAMPLE_01.dbf' size 2g;

6 rows selected.

3.创建用户：

SYS@orcl> select 'create user "'||username||'" identified by "'||username||'" default tablespace "'||default_tablespace||'";' from dba_users where  username in ('SCOTT','HR','SH','OE') order by default_tablespace;

'CREATEUSER"'||USERNAME||'"IDENTIFIEDBY"'||USERNAME||'"DEFAULTTABLESPACE"'||DEFAULT_TABLESPACE||'";'

------------------------------------------------------------------------------------------------------------------------------------------------

create user "SCOTT" identified by "SCOTT" default tablespace "USERS";

create user "SH" identified by "SH" default tablespace "USERS";

create user "OE" identified by "OE" default tablespace "USERS";

create user "HR" identified by "HR" default tablespace "USERS";

4.授权用户：

SYS@orcl>  select 'grant connect,resource to "'||username||'";' from dba_users where  username in ('SCOTT','HR','SH','OE');

'GRANTCONNECT,RESOURCETO"'||USERNAME||'";'

-----------------------------------------------------------

grant connect,resource to "SCOTT";

grant connect,resource to "HR";

grant connect,resource to "OE";

grant connect,resource to "SH";

5.导入用户：

SYS@orcl> select 'imp \''/ as sysdba\'' buffer=65535 commit=y file=/oracle_exp/expdata/'||username||'.dmp log=/oracle_exp/implog/'||username||'.log fromuser='||username||' touser='||username from dba_users where  username in ('SCOTT','HR','SH','OE');

'IMP\''/ASSYSDBA\''BUFFER=65535COMMIT=YFILE=/ORACLE_EXP/EXPDATA/'||USERNAME||'.DMPLOG=/ORACLE_EXP/IMPLOG/'||USERNAME||'.LOGFROMUSER='||USERNAME||'TOUSER='||USERNAME

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

imp \'/ as sysdba\' buffer=65535 commit=y file=/oracle_exp/expdata/SCOTT.dmp log=/oracle_exp/implog/SCOTT.log fromuser=SCOTT touser=SCOTT

imp \'/ as sysdba\' buffer=65535 commit=y file=/oracle_exp/expdata/HR.dmp log=/oracle_exp/implog/HR.log fromuser=HR touser=HR

imp \'/ as sysdba\' buffer=65535 commit=y file=/oracle_exp/expdata/OE.dmp log=/oracle_exp/implog/OE.log fromuser=OE touser=OE

imp \'/ as sysdba\' buffer=65535 commit=y file=/oracle_exp/expdata/SH.dmp log=/oracle_exp/implog/SH.log fromuser=SH touser=SH