import oracle utility_How to use Oracle utility EXPORT/IMPORT

How to use Oracle utility EXPORT/IMPORT

In this case, we will use Oracle export/import to refresh database schema from production to DEV. Oracle data pump can enable parallel processing for higher speed. Data pump is recommanded. But, Dump files generated by the new Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility. Oracle Data Pump is available only on Oracle Database 10g release 1 (10.1) and later. For this scenario, we use oracle 8, 9i and 10g version for different system combined.

For the performance consideration, we drop the schema owner and rebuild before importing. Following steps are applied:

1) Export the LOY schema from MA_PRD on CTMSUN09’s /db/exp/.

2) Drop tthe LOY schema in MA_DEV on CTMSUN14.

3) Create the LOY user in MA_DEV on CTMSUN14.

4) Import the LOY schema to MA_DEV from CTMSUN09 remotely, i.e. imp system@MA_DEV on CTMSUN09.

Before drop a user, we need to log off or kill connected session with corresponding users:

Scripts:

exp username/password wner=LOY file=maace_20100805.dmp log=maace_20100805.log buffer=50000 feedback=10000 direct=y

drop user LOY cascade;

Create user LOY:

CREATE USER LOY

IDENTIFIED BY VALUES '5F9B00F845914BF1'

DEFAULT TABLESPACE DATA

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

-- 4 Roles for LOY

GRANT DBA TO LOY;

GRANT LOY_ARRAY_TYPE TO LOY WITH ADMIN OPTION;

GRANT RESOURCE TO LOY;

GRANT CONNECT TO LOY;

ALTER USER LOY DEFAULT ROLE ALL;

-- 8 System Privileges for LOY

GRANT CREATE ROLE TO LOY;

GRANT SELECT ANY TABLE TO LOY;

GRANT UNLIMITED TABLESPACE TO LOY;

GRANT SELECT ANY DICTIONARY TO LOY;

GRANT SELECT ANY SEQUENCE TO LOY;

GRANT SELECT ANY TRANSACTION TO LOY;

GRANT CREATE VIEW TO LOY;

GRANT CREATE ANY SYNONYM TO LOY;

-- 1 Object Privilege for LOY

GRANT SELECT ONSYS.AUD$ TO LOY;

imp username/password file=maace_20100805.dmp log=imp_maace_schema.log buffer=50000 fromuser=LOY touser=LOY commit=y grants=y indexes=y rows=y ignore=y compile=y

After restoration:

SQL> exec dbms_java.grant_permission( 'LOY', 'SYS:java.io.FilePermission', '/bin/sh', 'execute' );

PL/SQL procedure successfully completed.

--Grant permisison for Java external job execution

SQL> grant dba to loy;

Grant succeeded.