Oracle数据泵导入导出实战与表空间创建

导出数据库：
Expdp system/SYSTEM@ecpm dumpfile=EXPDP_EPCMDB_2017-03-17_1521.DMP logfile=EXPDP_EPCMDB_2017-03-17_1521.LOG schemas=FMIS9999 comparesion=ALL version=11.1.0
Expdp system/SYSTEM@ecpm dumpfile=FMISTMP.DMP logfile=FIMIS9999.LOG schemas=FMISTMP comparesion=ALL version=11.1.0
Expdp system/SYSTEM@ecpm dumpfile=ODS.DMP logfile=FIMIS9999.LOG schemas=ODS comparesion=ALL version=11.1.0

-----------------------------------若数据库没有执行按照以上步骤执行------------------------------------------------
以系统管理员SYS/SYS、SYSTEM/SYSTEM 登录

1、创建表空间，注意创建表空间存储位置(例如北京房山地址：)
CREATE TABLESPACE FMIS9999 DATAFILE 'C:\app_server\oradb\ecpmorcl\oradata\ECPMBJFS\FMIS9999_1.dbf' Size 1024M AUTOEXTEND ON NOLOGGING ONLINE PERMANENT BLOCKSIZE 16384 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT Auto;
CREATE TABLESPACE FMISCOMP DATAFILE 'C:\app_server\oradb\ecpmorcl\oradata\ECPMBJFS\fmiscomp_1.dbf' Size 200M AUTOEXTEND ON NOLOGGING ONLINE PERMANENT BLOCKSIZE 16384 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT Auto;
CREATE TABLESPACE FMISINDEX DATAFILE 'C:\app_server\oradb\ecpmorcl\oradata\ECPMBJFS\fmisindex_1.dbf' Size 200M AUTOEXTEND ON NOLOGGING ONLINE PERMANENT BLOCKSIZE 16384 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT Auto;
CREATE TABLESPACE FMIS_LOB DATAFILE 'C:\app_server\oradb\ecpmorcl\oradata\ECPMBJFS\fmislob_1.dbf' Size 200M AUTOEXTEND ON NOLOGGING ONLINE PERMANENT BLOCKSIZE 16384 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT Auto;
CREATE TABLESPACE FMIS_LARGE DATAFILE 'C:\app_server\oradb\ecpmorcl\oradata\ECPMBJFS\fmislarge_1.dbf' Size 200M AUTOEXTEND ON NOLOGGING ONLINE PERMANENT BLOCKSIZE 16384 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT Auto;
CREATE TABLESPACE ODS DATAFILE 'C:\app_server\oradb\ecpmorcl\oradata\ECPMBJFS\ODS_1.dbf' Size 200M AUTOEXTEND ON NOLOGGING ONLINE PERMANENT BLOCKSIZE 16384 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT Auto;
CREATE TABLESPACE FMISTMP DATAFILE 'C:\app_server\oradb\ecpmorcl\oradata\ECPMBJFS\fmistmp_1.dbf' Size 200M AUTOEXTEND ON NOLOGGING ONLINE PERMANENT BLOCKSIZE 16384 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT Auto;

2、增加PLUBLIC用户授权

GRANT EXECUTE ON DBMS_RLS TO PUBLIC;
GRANT EXECUTE ON DBMS_SESSION TO PUBLIC;



3、创建3个用户（ODS、FMISTMP、FMIS9999）及授权

CREATE USER ODS IDENTIFIED BY ODS QUOTA UNLIMITED ON ODS;
BEGIN 
EXECUTE IMMEDIATE 'GRANT RESOURCE TO ODS';
EXECUTE IMMEDIATE 'GRANT SELECT ANY TABLE TO ODS';

EXECUTE IMMEDIATE 'GRANT SELECT ANY DICTIONARY TO ODS';
EXECUTE IMMEDIATE 'GRANT SELECT ANY SEQUENCE TO ODS';
EXECUTE IMMEDIATE 'GRANT CONNECT TO ODS';
EXECUTE IMMEDIATE 'GRANT EXECUTE ANY PROCEDURE TO ODS';
End;
/

CREATE USER FMISTMP IDENTIFIED BY FMISTMP 
DEFAULT TABLESPACE FMISTMP TEMPORARY TABLESPACE "TEMP" PROFILE "DEFAULT"
QUOTA UNLIMITED ON FMISTMP ACCOUNT Unlock;
BEGIN 
EXECUTE IMMEDIATE 'GRANT CONNECT TO FMISTMP';
EXECUTE IMMEDIATE 'GRANT SELECT ANY TABLE TO FMISTMP';
EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO FMISTMP';
EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO FMISTMP';
EXECUTE IMMEDIATE 'GRANT CREATE VIEW TO FMISTMP';
EXECUTE IMMEDIATE 'GRANT CREATE PUBLIC SYNONYM TO FMISTMP';
EXECUTE IMMEDIATE 'GRANT DROP PUBLIC SYNONYM TO FMISTMP';
End;
/

CREATE USER FMIS9999 IDENTIFIED BY FMIS9999 
DEFAULT TABLESPACE FMIS9999 TEMPORARY TABLESPACE TEMP PROFILE Default;
Begin
EXECUTE IMMEDIATE 'GRANT SELECT ANY DICTIONARY TO FMIS9999';
EXECUTE IMMEDIATE 'GRANT ALTER ANY TABLE TO FMIS9999';
EXECUTE IMMEDIATE 'GRANT ALTER SYSTEM TO FMIS9999';
EXECUTE IMMEDIATE 'GRANT ALTER ANY PROCEDURE TO FMIS9999';
EXECUTE IMMEDIATE 'GRANT CONNECT TO FMIS9999';
EXECUTE IMMEDIATE 'GRANT CREATE ANY PROCEDURE TO FMIS9999';