按使用者進行資料庫邏輯遷移

這個實驗存在一些問題，只是個人的學習筆記。
一，匯出所需匯出的使用者
SELECT 'EXP FILE=' || USERNAME || '.DMP LOG=' ||USERNAME || '.LOG OWNER=' || USERNAME || '
' FROM ALL_USERS T
WHERE T.CREATED >(SELECT R.CREATED FROM ALL_USERS R WHERE R.USERNAME = 'SYSTEM') + 1/24;

EXP FILE=FULL.DMP LOG=FULL.LOG OWNER=OGG,HR,OE,IX,SH,PM,BI,PAN1,PAN2

EXP FILE=HR.DMP LOG=HR.LOG OWNER=HR
EXP FILE=OE.DMP LOG=OE.LOG OWNER=OE
EXP FILE=IX.DMP LOG=IX.LOG OWNER=IX
EXP FILE=SH.DMP LOG=SH.LOG OWNER=SH
EXP FILE=PM.DMP LOG=PM.LOG OWNER=PM
EXP FILE=BI.DMP LOG=BI.LOG OWNER=BI
EXP FILE=PAN1.DMP LOG=PAN1.LOG OWNER=PAN1
EXP FILE=PAN2.DMP LOG=PAN2.LOG OWNER=PAN2
EXP FILE=SYSMAN.DMP LOG=SYSMAN.LOG OWNER=SYSMAN

----二，在目標庫上建立所需要的使用者並賦予許可權，建立所需要的表空間
---1，建立所需的使用者

SELECT 'CREATE USER ' || USERNAME || ' IDENTIFIED BY oracle;'
FROM ALL_USERS T
WHERE T.CREATED >
(SELECT R.CREATED FROM ALL_USERS R WHERE R.USERNAME = 'SYSTEM') + 1/24;

CREATE USER HR IDENTIFIED BY oracle;
CREATE USER OE IDENTIFIED BY oracle;
CREATE USER IX IDENTIFIED BY oracle;
CREATE USER SH IDENTIFIED BY oracle;
CREATE USER PM IDENTIFIED BY oracle;
CREATE USER BI IDENTIFIED BY oracle;
CREATE USER OGG IDENTIFIED BY oracle;
CREATE USER PAN1 IDENTIFIED BY oracle;
CREATE USER PAN2 IDENTIFIED BY oracle;

USER HR IDENTIFIED BY oracle;
User created.
USER OE IDENTIFIED BY oracle;
User created.
CREATE USER IX IDENTIFIED BY oracle;
User created.
USER SH IDENTIFIED BY oracle;
User created.
USER PM IDENTIFIED BY oracle;
User created.
USER BI IDENTIFIED BY oracle;
User created.
USER OGG IDENTIFIED BY oracle;
User created.
CREATE USER PAN1 IDENTIFIED BY oracle;
User created.
USER PAN2 IDENTIFIED BY oracle;
User created.

----2，建立表空間
SELECT 'CREATE TABLESPACE ' || NAME || ' DATAFILE  /u01/app/oracle/oradata/'  || NAME ||
       '.DBF ' ||
       'SIZE 1000M AUTOEXTEND ON NEXT 300M MAXSIZE UNLIMITED;'
  FROM V$TABLESPACE
 WHERE NAME NOT IN ('SYSTEM', 'UNDOTBS1', 'SYSAUX', 'USERS', 'TEMP');

CREATE TABLESPACE EXAMPLE DATAFILE  '/u01/app/oracle/oradata/EXAMPLE.DBF' SIZE 1000M AUTOEXTEND ON NEXT 300M

MAXSIZE UNLIMITED;
CREATE TABLESPACE TBS_GGUSER DATAFILE  '/u01/app/oracle/oradata/TBS_GGUSER.DBF' SIZE 100M AUTOEXTEND ON NEXT 30M

MAXSIZE UNLIMITED;
CREATE TABLESPACE TBS1 DATAFILE  '/u01/app/oracle/oradata/TBS1.DBF' SIZE 100M AUTOEXTEND ON NEXT 30M MAXSIZE

UNLIMITED;
CREATE TABLESPACE TBS2 DATAFILE  '/u01/app/oracle/oradata/TBS2.DBF' SIZE 100M AUTOEXTEND ON NEXT 30M MAXSIZE

UNLIMITED;
CREATE TABLESPACE OGG DATAFILE  '/u01/app/oracle/oradata/OGG.DBF' SIZE 100M AUTOEXTEND ON NEXT 30M MAXSIZE

UNLIMITED;

---執行結果
TABLESPACE TBS_GGUSER DATAFILE  '/u01/app/oracle/oradata/TBS_GGUSER.DBF' SIZE 100M AUTOEXTEND ON

NEXT 30M MAXSIZE UNLIMITED;
CREATE TABLESPACE TBS1 DATAFILE  '/u01/app/oracle/oradata/TBS1.DBF' SIZE 100M AUTOEXTEND ON NEXT 30M MAXSIZE

UNLIMITED;
CREATE TABLESPACE TBS2 DATAFILE  '/u01/app/oracle/oradata/TBS2.DBF' SIZE 100M AUTOEXTEND ON NEXT 30M MAXSIZE

UNLIMITED;
Tablespace created.
>
Tablespace created.
>
Tablespace created.
TABLESPACE OGG DATAFILE  '/u01/app/oracle/oradata/OGG.DBF' SIZE 100M AUTOEXTEND ON NEXT 30M

MAXSIZE UNLIMITED;
Tablespace created.

----3，賦角色
SELECT 'GRANT '|| granted_role ||' to '|| grantee||';' FROM dba_role_privs WHERE grantee IN

('OGG','HR','OE','IX','SH','PM','BI','PAN1','PAN2');

GRANT CONNECT to PM;
GRANT CONNECT to IX;
GRANT DBA to OGG;
GRANT AQ_ADMINISTRATOR_ROLE to IX;
GRANT GGS_GGSUSER_ROLE to OGG;
GRANT RESOURCE to HR;
GRANT CONNECT to PAN2;
GRANT CONNECT to OGG;
GRANT RESOURCE to PAN2;
GRANT RESOURCE to PM;
GRANT RESOURCE to OE;
GRANT XDBADMIN to OE;
GRANT RESOURCE to PAN1;
GRANT SELECT_CATALOG_ROLE to SH;
GRANT PLUSTRACE to HR;
GRANT CONNECT to PAN1;
GRANT RESOURCE to BI;
GRANT RESOURCE to OGG;
GRANT RESOURCE to SH;
GRANT RESOURCE to IX;
GRANT CWM_USER to SH;
GRANT SELECT_CATALOG_ROLE to IX;
GRANT AQ_USER_ROLE to IX;

 

---4，賦tab許可權
SELECT 'grant '||PRIVILEGE||' on '||table_name||' to '||grantee||';' FROM dba_tab_privs WHERE grantee IN

('OGG','HR','OE','IX','SH','PM','BI','PAN1','PAN2');

grant WRITE on SS_OE_XMLDIR to OE;
grant READ on SS_OE_XMLDIR to OE;
grant SELECT on IND$ to OGG;
grant SELECT on OPQTYPE$ to OGG;
grant WRITE on LOG_FILE_DIR to SH;
grant READ on LOG_FILE_DIR to SH;
grant SELECT on V_$PARAMETER to OGG;
grant SELECT on CON$ to OGG;
grant READ on DATA_FILE_DIR to SH;
grant SELECT on V_$SESSION to OGG;
grant SELECT on V_$TRANSACTION to OGG;
grant SELECT on V_$INSTANCE to OGG;
grant SELECT on DBA_USERS to OGG;
grant EXECUTE on DBMS_APPLY_ADM to IX;
grant EXECUTE on DBMS_AQ to IX;
grant EXECUTE on DBMS_AQADM to IX;
grant SELECT on USER$ to OGG;
grant SELECT on DBA_OBJECTS to OGG;
grant SELECT on DBA_OBJECT_TABLES to OGG;
grant SELECT on DBA_RECYCLEBIN to OGG;
grant SELECT on ENC$ to OGG;
grant SELECT on SEG$ to OGG;
grant SELECT on SEQ$ to OGG;
grant SELECT on CCOL$ to OGG;
grant SELECT on CDEF$ to OGG;
grant SELECT on COL$ to OGG;
grant SELECT on COLTYPE$ to OGG;
grant SELECT on DBA_ERRORS to OGG;
grant SELECT on DBA_INDEXES to OGG;
grant SELECT on DBA_IND_COLUMNS to OGG;
grant SELECT on DBA_LOBS to OGG;
grant SELECT on DBA_LOG_GROUPS to OGG;
grant SELECT on DBA_LOG_GROUP_COLUMNS to OGG;
grant SELECT on DBA_SEQUENCES to OGG;
grant SELECT on V_$DATABASE to OGG;
grant EXECUTE on DBMS_SESSION to OGG;
grant EXECUTE on DBMS_STATS to SH;
grant EXECUTE on DBMS_STATS to IX;
grant EXECUTE on DBMS_STATS to PM;
grant EXECUTE on DBMS_STATS to OE;
grant EXECUTE on DBMS_STATS to HR;
grant EXECUTE on UTL_FILE to OGG;
grant SELECT on TAB$ to OGG;
grant SELECT on TABPART$ to OGG;
grant SELECT on TABSUBPART$ to OGG;
grant SELECT on OBJ$ to OGG;
grant SELECT on DBA_CLUSTERS to OGG;
grant SELECT on DBA_CLU_COLUMNS to OGG;
grant SELECT on DBA_CONSTRAINTS to OGG;
grant SELECT on DBA_CONS_COLUMNS to OGG;
grant WRITE on SUBDIR to OE;
grant READ on SUBDIR to OE;
grant SELECT on DBA_SYNONYMS to OGG;
grant SELECT on DBA_TABLES to OGG;
grant SELECT on DBA_TABLESPACES to OGG;
grant SELECT on DBA_TAB_COLS to OGG;
grant SELECT on DBA_TAB_COLUMNS to OGG;
grant SELECT on DBA_TAB_PARTITIONS to OGG;
grant SELECT on DBA_TAB_SUBPARTITIONS to OGG;
grant SELECT on DBA_TRIGGERS to OGG;
grant EXECUTE on DBMS_PROPAGATION_ADM to IX;
grant EXECUTE on DBMS_AQ_BQVIEW to IX;
grant EXECUTE on DBMS_CAPTURE_ADM to IX;
grant EXECUTE on DBMS_STREAMS_ADM to IX;
grant EXECUTE on DBMS_FLASHBACK to OGG;
grant EXECUTE on DBMS_FLASHBACK to IX;
grant SELECT on QT74031_BUFFER to IX;
grant SELECT on QT74047_BUFFER to IX;
grant SELECT on HIST_HEAD$ to OGG;
grant READ on MEDIA_DIR to PM;
grant SELECT on NLS_SESSION_PARAMETERS to OGG;
grant UPDATE on DEPT_OGG to OGG;
grant INSERT on DEPT_OGG to OGG;
grant DELETE on DEPT_OGG to OGG;
grant SELECT on EMP to PAN1;
grant SELECT on EMP to PAN2;
grant UPDATE on EMP_OGG to OGG;
grant INSERT on EMP_OGG to OGG;
grant DELETE on EMP_OGG to OGG;
grant REFERENCES on COUNTRIES to OE;
grant SELECT on COUNTRIES to OE;
grant SELECT on DEPARTMENTS to OE;
grant REFERENCES on EMPLOYEES to OE;
grant SELECT on EMPLOYEES to OE;
grant SELECT on JOBS to OE;
grant SELECT on JOB_HISTORY to OE;
grant REFERENCES on LOCATIONS to OE;
grant SELECT on LOCATIONS to OE;
grant SELECT on BOMBAY_INVENTORY to BI;
grant SELECT on CUSTOMERS to BI;
grant SELECT on CUSTOMERS to PM;
grant SELECT on INVENTORIES to BI;
grant SELECT on INVENTORIES to PM;
grant SELECT on CAL_MONTH_SALES_MV to BI;
grant SELECT on CHANNELS to BI;
grant SELECT on COSTS to BI;
grant SELECT on SALES to BI;
grant SELECT on COUNTRIES to BI;
grant SELECT on CUSTOMERS to BI;
grant SELECT on FWEEK_PSCAT_SALES_MV to BI;
grant SELECT on PRODUCTS to BI;
grant SELECT on PROMOTIONS to BI;
grant SELECT on ORDERS to BI;
grant SELECT on ORDERS to PM;
grant SELECT on ORDER_ITEMS to BI;
grant SELECT on ORDER_ITEMS to PM;
grant SELECT on PRODUCTS to BI;
grant SELECT on PRODUCT_DESCRIPTIONS to BI;
grant SELECT on PRODUCT_DESCRIPTIONS to PM;
grant SELECT on PRODUCT_INFORMATION to BI;
grant REFERENCES on PRODUCT_INFORMATION to PM;
grant SELECT on PRODUCT_INFORMATION to PM;
grant SELECT on PRODUCT_PRICES to BI;
grant SELECT on PROMOTIONS to BI;
grant SELECT on SYDNEY_INVENTORY to BI;
grant SELECT on TORONTO_INVENTORY to BI;
grant SELECT on WAREHOUSES to BI;
grant SELECT on WAREHOUSES to PM;
grant SELECT on TIMES to BI;
grant WRITE on GGS_DDL_TRACE to OGG;
grant READ on GGS_DDL_TRACE to OGG;
grant EXECUTE on GGS_DDL_TRACE to OGG;

 

---5，賦sys許可權
SELECT 'grant '|| PRIVILEGE || ' to ' ||grantee||';' FROM dba_sys_privs WHERE grantee IN

('OGG','HR','OE','IX','SH','PM','BI','PAN1','PAN2');

grant CREATE VIEW to HR;
grant QUERY REWRITE to OE;
grant CREATE RULE SET to IX;
grant CREATE CLUSTER to IX;
grant CREATE SYNONYM to BI;
grant UNLIMITED TABLESPACE to HR;
grant UNLIMITED TABLESPACE to OE;
grant CREATE INDEXTYPE to IX;
grant CREATE TRIGGER to IX;
grant CREATE PROCEDURE to IX;
grant ALTER SESSION to IX;
grant CREATE SEQUENCE to SH;
grant ALTER SESSION to SH;
grant DROP ANY DIRECTORY to OGG;
grant UNLIMITED TABLESPACE to PAN1;
grant CREATE DATABASE LINK to HR;
grant CREATE OPERATOR to IX;
grant CREATE SEQUENCE to IX;
grant CREATE SYNONYM to SH;
grant UNLIMITED TABLESPACE to BI;
grant ALTER SESSION to BI;
grant SELECT ANY TABLE to OGG;
grant ALTER ANY TABLE to OGG;
grant UNLIMITED TABLESPACE to PAN2;
grant CREATE SYNONYM to OE;
grant CREATE RULE to IX;
grant CREATE VIEW to IX;
grant CREATE SYNONYM to IX;
grant UNLIMITED TABLESPACE to IX;
grant CREATE TABLE to SH;
grant UNLIMITED TABLESPACE to SH;
grant CREATE SESSION to SH;
grant ALTER SESSION to OGG;
grant CREATE SEQUENCE to HR;
grant CREATE MATERIALIZED VIEW to OE;
grant CREATE DATABASE LINK to OE;
grant CREATE TYPE to IX;
grant CREATE TABLE to IX;
grant CREATE DATABASE LINK to SH;
grant CREATE TABLE to BI;
grant CREATE SESSION to HR;
grant CREATE DATABASE LINK to IX;
grant CREATE CLUSTER to SH;
grant CREATE ANY DIRECTORY to OGG;
grant UNLIMITED TABLESPACE to OGG;
grant ALTER SESSION to HR;
grant CREATE VIEW to OE;
grant CREATE SESSION to OE;
grant CREATE DIMENSION to SH;
grant CREATE VIEW to SH;
grant UNLIMITED TABLESPACE to PM;
grant CREATE DATABASE LINK to BI;
grant CREATE VIEW to BI;
grant CREATE CLUSTER to BI;
grant CREATE SESSION to BI;
grant FLASHBACK ANY TABLE to OGG;
grant SELECT ANY DICTIONARY to OGG;
grant CREATE SYNONYM to HR;
grant SELECT ANY DICTIONARY to IX;
grant CREATE SESSION to IX;
grant QUERY REWRITE to SH;
grant CREATE MATERIALIZED VIEW to SH;
grant CREATE SEQUENCE to BI;
grant CREATE SESSION to OGG;

--三，匯入使用者到目標庫
SELECT 'IMP FILE=' || USERNAME || '.DMP LOG=' ||
     USERNAME || '.LOG FROMUSER=' || USERNAME || ' TOUSER=' || USERNAME || '
'
  FROM ALL_USERS T
 WHERE T.CREATED >
       (SELECT R.CREATED FROM ALL_USERS R WHERE R.USERNAME = 'SYSTEM') + 1/24;

IMP FILE=full.DMP LOG=full_imp.LOG FROMUSER=(OGG,HR,OE,IX,SH,PM,BI,PAN1,PAN2)

IMP FILE=OGG.DMP LOG=OGG.LOG FROMUSER=OGG TOUSER=OGG
IMP FILE=HR.DMP LOG=HR.LOG FROMUSER=HR TOUSER=HR
IMP FILE=OE.DMP LOG=OE.LOG FROMUSER=OE TOUSER=OE
IMP FILE=IX.DMP LOG=IX.LOG FROMUSER=IX TOUSER=IX
IMP FILE=SH.DMP LOG=SH.LOG FROMUSER=SH TOUSER=SH
IMP FILE=PM.DMP LOG=PM.LOG FROMUSER=PM TOUSER=PM
IMP FILE=BI.DMP LOG=BI.LOG FROMUSER=BI TOUSER=BI
IMP FILE=PAN1.DMP LOG=PAN1.LOG FROMUSER=PAN1 TOUSER=PAN1
IMP FILE=PAN2.DMP LOG=PAN2.LOG FROMUSER=PAN2 TOUSER=PAN2
IMP FILE=SYSMAN.DMP LOG=SYSMAN.LOG FROMUSER=SYSMAN TOUSER=SYSMAN