Export Import Of Application Context Oracle Explained

Exporting and importing is a fairly commonly used procedure by many database administrators to export database objects and import them back into other databases but recently while exporting we came across a issue where application contexts were missing while doing a schema only export, where as if you do a complete database export(FULL=Y option) with application context oracle, expdp is able to export application contexts also into dump. So in this article we will see how to do a complete export of a single schema to another database with application contexts.

What is Export Import:

Data Pump Export(expdp) is a utility provided by Oracle(previous version exp) for unloading data and metadata into a set of operating system files called a dump file set. The dump file set can be imported only by the Data Pump Import utility or impdp to import and expdp to export. There are many Data Pump Export Modes for example

1. Full Export Mode
2. Schema Mode
3. Table Mode
4. Tablespace Mode
5. Transportable Tablespace Mode

Here we will be working with schema mode export option and will learn how to export the application contexts safely so that after importing the dump we will also get the specific application contexts also recreated in other database. Transferring database schema contexts are very important as absent of application contexts might introduce application flow errors and performance issues.

To import complete database with context you can use the below FULL=Y option.

expdp SYSTEM/Welcome1@DB12C schemas=ABC full=Y DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_CONTEXT.dmp LOGFILE=EXP_CONTEXT.log

To export complete schema with context follow below steps:

Assumptions:

– Schema user name: ABC
– export directoty location: /home/oracle/dump
– export directory name: exp_dir
– DBA User Name: SYSTEM
– Database SID: DB12C
1. Create directory to export the dump file.

CREATE OR REPLACE DIRECTORY exp_dir AS '/home/oracle/dump';

2. Grant read and write on directory to the export user ABC.

GRANT READ, WRITE ON DIRECTORY exp_dir TO ABC;

3. Export the schema first.

expdp SYSTEM/Welcome1@DB12C schemas=ABC DIRECTORY=exp_dir DUMPFILE=EXP_CONTEXT_1.dmp LOGFILE=EXP_CONTEXT_1.log

4. Login as the schema user ABC and execute below query to generate all the application contexts queries for schema user ABC. Save all queries to create_context.sql file for future use.

SELECT dbms_metadata.get_ddl('CONTEXT', namespace) ddl from dba_context where schema = 'ABC'

BONUS TIP: To check all existing contexts in database use “SELECT * FROM dba_context”

To import complete schema with context follow below steps:

Assumptions:

– DUMP file located at /home/oracle/dump folder.
1. Create directory to store and import the dump file.

CREATE OR REPLACE DIRECTORY imp_dir AS '/home/oracle/dump';

2. Grant read and write on directory to the import user ABC.

GRANT READ, WRITE ON DIRECTORY imp_dir TO ABC;

3. Create the schema user and provide the necessary privileges. Example script create_schema.sql provided below.

$ sqlplus /nolog
SQL> conn /as sysdba;
SQL>@create_schema.sql

Content of create_schema.sql:

whenever sqlerror continue;
spool create_schema.log
define datafile_data_path = '/home/oracle/app/oracle/oradata';
define datafile_index_path = '/home/oracle/app/oracle/oradata';
define temp_tablespace = 'TEMP';
define username = 'ABC';
define password = 'ABC';
define data_tablespace = 'ABC_DATA';
define index_tablespace = 'ABC_IDX';
define data_tablespace_filename = 'ABC_DATA.dbf';
define index_tablespace_filename = 'ABC_IDX.dbf';

-- DROP USER &&username cascade;
-- DROP TABLESPACE &&data_tablespace INCLUDING CONTENTS AND DATAFILES;
-- DROP TABLESPACE &&index_tablespace INCLUDING CONTENTS AND DATAFILES;
-- quit;

---- ***************************************************
-- Creation of tablespaces
---- ***************************************************

CREATE TABLESPACE &&data_tablespace DATAFILE '&&datafile_data_path/&&data_tablespace_filename' SIZE 2048M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE &&index_tablespace DATAFILE '&&datafile_data_path/&&index_tablespace_filename' SIZE 1024M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

---- ***************************************************
-- Creation of users and giving privileges
---- ***************************************************

create user &&username identified by &&password default tablespace &&data_tablespace temporary tablespace &&temp_tablespace PROFILE DEFAULT;
GRANT DEBUG CONNECT SESSION TO &&username;
GRANT CREATE PROCEDURE TO &&username;
GRANT CREATE ANY JOB TO &&username;
GRANT ALTER SESSION TO &&username;
GRANT CREATE SESSION TO &&username;
GRANT DEBUG ANY PROCEDURE TO &&username;
GRANT CREATE ANY TYPE TO &&username;
GRANT CREATE ANY INDEX TO &&username;
GRANT CREATE SEQUENCE TO &&username;
GRANT CREATE VIEW TO &&username;
GRANT CREATE TABLE TO &&username;
GRANT SELECT ANY DICTIONARY TO &&username;
GRANT CREATE ANY TRIGGER TO &&username;
GRANT CREATE TYPE TO &&username;
GRANT CREATE JOB TO &&username;
GRANT CREATE TRIGGER TO &&username;
grant create any context to &&username;
ALTER USER &&username QUOTA UNLIMITED ON &&data_tablespace;
GRANT SELECT ANY DICTIONARY TO &&username;
ALTER USER &&username QUOTA UNLIMITED ON &&index_tablespace;
GRANT CREATE ROLE TO &&username;
GRANT CREATE SYNONYM TO &&username;
GRANT CREATE DATABASE LINK TO &&username;
--
-- You can add all privileges in this section
--
grant execute on utl_file to &&username;
grant execute on dbms_sql to &&username;
grant execute on dbms_lob to &&username;
grant execute on sys.dbms_lock to &&username;

prompt Tablespace / User creation Completed.

spool off;
quit;

4. Once schema with tabelspaces are created successfully, execute below query to import the exported schema:

impdp SYSTEM/Welcome1@DB12C schemas=ABC directory=imp_dir dumpfile=EXP_CONTEXT_1.dmp remap_schema=ABC:ABC REMAP_TABLESPACE=ABC_DATA:ABC_DATA REMAP_TABLESPACE=ABC_IDX:ABC_IDX logfile=IMP_CONTEXT_1.log TRANSFORM=oid:n TRANSFORM=SEGMENT_ATTRIBUTES:n

5. Login as the schema user ABC and execute below query to create the application contexts for user ABC.

sqlplus ABC/ABC@DB12C @create_context.sql

6. The above query will create all the contexts related to user ABC.

7. To check if contexts are created or not you can use below query.

SELECT * FROM dba_context

Once you complete all above steps you will be able to import both all schema objects for user ABC and all contexts assigned to ABC. If you want to learn more on implementing application contexts then you can go through the linked oracle thread.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.