计算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