ORACLE 数据逻辑导入导出

导出库：sysdba格式的单引号需要转义，转义可以用\或者外加双引号

exp \'sys/DSY_ljo_1960 as sysdba\' owner='USER1','USER2','USER3' statistics=none compress=y file=db_upay.dmp log=db_upay.log

查询所有的表空间

select t.tablespace_name
  from all_tables t
 where t.owner in
       ('USER1','USER2','USER3')
union
select s.tablespace_name
  from all_indexes s
 where s.owner in
       ('USER1','USER2','USER3');

导入库

登录toad 获取表空间和用户脚本：

DROP TABLESPACE TBSPAY_PAYMENT INCLUDING CONTENTS AND DATAFILES;


CREATE TABLESPACE TBSPAY_PAYMENT DATAFILE 
  '/u01/oradata/tstuf1/tbspay_payment.dbf' SIZE 512M AUTOEXTEND ON NEXT 10M MAXSIZE 10G
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;


DROP TABLESPACE TBSPAY_SYSTEM INCLUDING CONTENTS AND DATAFILES;


CREATE TABLESPACE TBSPAY_SYSTEM DATAFILE 
  '/u01/oradata/tstuf1/tbspay_system.dbf' SIZE 512M AUTOEXTEND ON NEXT 10M MAXSIZE 10G
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;


DROP TABLESPACE UPAY_AUIMS INCLUDING CONTENTS AND DATAFILES;


CREATE TABLESPACE UPAY_AUIMS DATAFILE 
  '/u01/oradata/tstuf1/UPAY_AUIMS1.dbf' SIZE 128M AUTOEXTEND ON NEXT 16M MAXSIZE 10G,
  '/u01/oradata/tstuf1/UPAY_AUIMS2.dbf' SIZE 128M AUTOEXTEND ON NEXT 16M MAXSIZE 10G
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;


DROP TABLESPACE TBSPAY_CASHPOOL INCLUDING CONTENTS AND DATAFILES;


CREATE TABLESPACE TBSPAY_CASHPOOL DATAFILE 
  '/u01/oradata/tstuf1/tbspay_cashpool.dbf' SIZE 512M AUTOEXTEND ON NEXT 10M MAXSIZE 10G
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;


DROP USER UPAY_AUIMS CASCADE;


CREATE USER UPAY_AUIMS
  IDENTIFIED BY VALUES 'upay_auims'
  DEFAULT TABLESPACE UPAY_AUIMS
  TEMPORARY TABLESPACE TEMP;


  GRANT CONNECT TO UPAY_AUIMS;
  GRANT RESOURCE TO UPAY_AUIMS;
  GRANT SELECT_CATALOG_ROLE TO UPAY_AUIMS;
  ALTER USER UPAY_AUIMS DEFAULT ROLE ALL;
  GRANT CREATE VIEW TO UPAY_AUIMS;
  ALTER USER UPAY_AUIMS QUOTA UNLIMITED ON UPAY_AUIMS;




DROP USER UPAY_CASHPOOL CASCADE;


CREATE USER UPAY_CASHPOOL
  IDENTIFIED BY VALUES 'upay_cashpool'
  DEFAULT TABLESPACE TBSPAY_CASHPOOL
  TEMPORARY TABLESPACE TEMP;
  GRANT CONNECT TO UPAY_CASHPOOL;
  GRANT DBA TO UPAY_CASHPOOL;
  ALTER USER UPAY_CASHPOOL DEFAULT ROLE ALL;
  GRANT SELECT ANY TABLE TO UPAY_CASHPOOL;
  GRANT UNLIMITED TABLESPACE TO UPAY_CASHPOOL;




DROP USER UPAY_PAYMENT CASCADE;


CREATE USER UPAY_PAYMENT
  IDENTIFIED BY VALUES 'upay_payment'
  DEFAULT TABLESPACE TBSPAY_PAYMENT
  TEMPORARY TABLESPACE TEMP;
  GRANT CONNECT TO UPAY_PAYMENT;
  GRANT DBA TO UPAY_PAYMENT;
  ALTER USER UPAY_PAYMENT DEFAULT ROLE ALL;
  GRANT SELECT ANY TABLE TO UPAY_PAYMENT;
  GRANT UNLIMITED TABLESPACE TO UPAY_PAYMENT;


DROP USER UPAY_SYSTEM CASCADE;


CREATE USER UPAY_SYSTEM
  IDENTIFIED BY VALUES 'upay_system'
  DEFAULT TABLESPACE TBSPAY_SYSTEM
  TEMPORARY TABLESPACE TEMP;
  GRANT CONNECT TO UPAY_SYSTEM;
  GRANT DBA TO UPAY_SYSTEM;
  ALTER USER UPAY_SYSTEM DEFAULT ROLE ALL;
  GRANT SELECT ANY TABLE TO UPAY_SYSTEM;
  GRANT UNLIMITED TABLESPACE TO UPAY_SYSTEM;


导入数据：

imp system/oracle statistics=none commit=y fromuser='USER1' touser='USER1' file=db_upay.dmp log=imp_upay_auims.log 
imp system/oracle statistics=none commit=y fromuser='USER2' touser='USER2' file=db_upay.dmp log=imp_upay_cashpool.log 
imp system/oracle statistics=none commit=y fromuser='USER3' touser='USER3' file=db_upay.dmp log=imp_upay_payment.log