EXP / IMP Oracle 資料庫

EXP 資料庫

C:\>exp system/bankpro@oracle9i file=C:\invoice.exp log=C:\invoice.log owner=invoice indexes=y rows=y constraints=y feedback=1000 tts_full_check=y

IMP 資料庫 1.先以具DBA角色權限以上之使用者登入oracle資料庫

C:\>sqlplus system/bankpro@oracle9i

2.執行Script,目的建立TableSpace & User

SQL> start c:\createtablespace.sql;

CREATE TABLESPACE INVOICE DATAFILE
'C:\ORACLE\ORADATA\ORACLE9I\INVOICE.ORA' SIZE 200M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER INVOICE
IDENTIFIED BY VALUES 'CC58BA69EDD100F1'
DEFAULT TABLESPACE INVOICE
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 17 Roles for INVOICE
GRANT DBA TO INVOICE;
GRANT CONNECT TO INVOICE;
GRANT ORA_ASPNET_MEM_FULLACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_MEM_BASICACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_PERS_FULLACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_PROF_FULLACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_SMAP_FULLACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_MEM_REPORTACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_PERS_BASICACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_PROF_BASICACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_ROLES_FULLACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_SESSN_FULLACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_WEVNT_FULLACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_PERS_REPORTACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_PROF_REPORTACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_ROLES_BASICACCESS TO INVOICE WITH ADMIN OPTION;
GRANT ORA_ASPNET_ROLES_REPORTACCESS TO INVOICE WITH ADMIN OPTION;
ALTER USER INVOICE DEFAULT ROLE ALL;
-- 5 System Privileges for INVOICE
GRANT CREATE SESSION TO INVOICE;
GRANT CREATE ANY TABLE TO INVOICE;
GRANT SELECT ANY TABLE TO INVOICE;
GRANT CREATE ANY PROCEDURE TO INVOICE;
GRANT UNLIMITED TABLESPACE TO INVOICE;

3.開始執行TableSpace的匯入

C:\>imp system/bankpro@oracle9i fromuser=INVOICE touser=INVOICE file=C:\invoice.exp log=C:\invoice.log ignore=y indexes=y rows=y constraints=y feedback=1000

4. 重建Role對應

-- 6 Object Privileges for INVOICE GRANT DELETE, INSERT, SELECT, UPDATE ON  EXCHANGE.COMPANY TO INVOICE;GRANT DELETE, INSERT, SELECT, UPDATE ON  EXCHANGE.SALESINVOICE TO INVOICE;GRANT DELETE, INSERT, SELECT, UPDATE ON  EXCHANGE.SALESINVOICEDETAIL TO INVOICE;GRANT DELETE, INSERT, SELECT, UPDATE ON  EXCHANGE.SALESINVOICENG TO INVOICE;GRANT DELETE, INSERT, SELECT, UPDATE ON  HIS.B2CINVOICEACTIONHISTORY TO INVOICE;GRANT DELETE, INSERT, SELECT, UPDATE ON  HIS.B2CINVOICEDETAILACTIONHISTORY TO INVOICE;
-- 3 Object Privileges for EINVOICEACCOUNT GRANT DELETE, INSERT, SELECT, UPDATE ON  EXCHANGE.COMPANY TO EINVOICEACCOUNT;GRANT DELETE, INSERT, SELECT, UPDATE ON  HIS.B2CINVOICEACTIONHISTORY TO EINVOICEACCOUNT;GRANT DELETE, INSERT, SELECT, UPDATE ON  HIS.B2CINVOICEDETAILACTIONHISTORY TO EINVOICEACCOUNT;
GRANT ORA_ASPNET_MEM_BASICACCESS TO ORA_ASPNET_MEM_FULLACCESS;GRANT ORA_ASPNET_MEM_REPORTACCESS TO ORA_ASPNET_MEM_FULLACCESS;GRANT ORA_ASPNET_PERS_BASICACCESS TO ORA_ASPNET_PERS_FULLACCESS;GRANT ORA_ASPNET_PERS_REPORTACCESS TO ORA_ASPNET_PERS_FULLACCESS;GRANT ORA_ASPNET_PROF_BASICACCESS TO ORA_ASPNET_PROF_FULLACCESS;GRANT ORA_ASPNET_PROF_REPORTACCESS TO ORA_ASPNET_PROF_FULLACCESS;GRANT ORA_ASPNET_ROLES_REPORTACCESS TO ORA_ASPNET_ROLES_FULLACCESS;GRANT ORA_ASPNET_ROLES_BASICACCESS TO ORA_ASPNET_ROLES_REPORTACCESS;

5.重建AspNet Provider

C:\>sqlplus system/bankpro@oralce9i

將建置aspnet provider之script copy至c:\aspnet\路徑下!

SQL> start c:\aspnet\InstallAllOracleASPNETProviders.sql;

6.將有錯誤之procduce & function重新編譯過後即可!