用expdp impdp迁移用户

计算schema在原库的空间大小

select SEGMENT_TYPE,sum(a.bytes)/1024/1024/1024 from USER_EXTENTS a  group by A.SEGMENT_TYPE;

建立表空间或扩张新库的表空间

CREATE TABLESPACE orcl_index_o1 DATAFILE 
   SIZE 10240M AUTOEXTEND OFF,
   SIZE 10240M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK OFF;

新库建立schema、赋权限

 

CREATE USER hr01
  IDENTIFIED BY   hr01
  DEFAULT TABLESPACE orcl_index_01
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 1 Role for hr01 
 -- Grant/Revoke role privileges 
grant connect to hr01;
grant ctxapp to hr01;
grant resource to hr01;
grant select_catalog_role to hr01;
-- Grant/Revoke system privileges 
grant administer database trigger to hr01;
grant alter session to hr01;
grant comment any table to hr01;
.
.
.
.
.
.
.

建立impdp目录(也可以使用已有的directory)

create or replace directory expdp_dir as '/dmp/lihq';

将读写directory的权限给新用户

GRANT READ,WRITE ON DIRECTORY expdp_dir TO hr;

expdp导出

$ORACLE_HOME/bin/expdp hr/"hr123!"@orclhr status=120 schemas=hr logfile=hrexpdp0322.log DIRECTORY=EXPDP_DIR DUMPFILE=hr0322  PARALLEL=3

impdp导入

impdp hr01/hr01@orcl  schemas=hr status=120 remap_schema=hr:hr01 dumpfile=hr01.dmp parallel=3 remap_tablespace=orcl_data:orcl_data_01,orcl_index:orcl_INDEX_01 LOGFILE=hr2dw316.log  DIRECTORY=impdp_dir