To understand Sysadmin activities like Menu,DataGroup,Request Group creation & assign to User

Administrating Oracle application, its important to understand the core components.In oracle application, lets take some of them start with some of the

• Responsibilities
• Menus
• Profile Options
• Concurrent Manager
• Security Concepts

EXP / IMP – EXPDP / IMPDP ( DATAPUMP )

These tools are used to load data from one oracle database to another database.

from ORACLE 10G oracle have new export datapump and import datapump utilities.

Difference between EXPORT and EXPORT DATAPUMP

1) export / import technology was client based. i.e all dump, log, other files are created on the client by default.

2) The  DATAPUMP technology was server based. All dump files, log files , and other files are created on the server side by default.

3) Datapump manages multiple parallel streams of data to activate maximum through put. So it improves the performance.

4) we can interrupt expdp / impdp jobs in the middle and resume then.

5) we can restart failed expdp / impdp jobs.

6) The Ability to estimate how much time and space an export job would consume, without actually performing the export.

The export dump file contains objects in the following order:

1. Type definitions
2. Table definitions
3. Table data
4. Table indexes
5. Integrity constraints, views, procedures, and triggers
6. Bitmap, function-based, and domain indexes

Invoking Export and Import

You can run Export and Import tool in two modes

Command Line Mode

Interactive Mode

When you just type exp or imp at o/s prompt it will run in interactive mode i.e. these tools will prompt you for all the necessary input. If you supply command line arguments when calling exp or imp then it will run in command line mode

Command Line Parameters of Export tool

You can control how Export runs by entering the EXP command followed by various arguments.

To specify parameters, you use keywords:

Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

The Export and Import tools support four modes of operation

FULL               :Exports all the objects in all schemas
OWNER             :Exports objects only belonging to the given OWNER
TABLES           :Exports Individual Tables
TABLESPACE  :Export all objects located in a given TABLESPACE.

Scenarios 

1) Exporting full database

2) exporting schemas ( nothing but Users)

3) Exporting Individual Tables

4) Exporting  Tablespace.

Example of Exporting Full Database

The following example shows how to export full database

$exp USERID=scott/tiger FULL=y FILE=myfull.dmp

In the above command, FILE option specifies the name of the dump file, FULL option specifies that you want to export the full database, USERID option specifies the user account to connect to the database. Note, to perform full export the user should have DBA or EXP_FULL_DATABASE privilege.

 Example of Exporting Schemas

To export Objects stored in a particular schemas you can run export utility with the following arguments

$exp USERID=scott/tiger OWNER=(SCOTT,ALI) FILE=exp_own.dmp

The above command will export all the objects stored in SCOTT and ALI’s schema.

Exporting Individual Tables

To export individual tables give the following command

$exp USERID=scott/tiger TABLES=(scott.emp,scott.sales) FILE=exp_tab.dmp

This will export scott’s emp and sales tables.

Exporting Tablespaces

To export individual tablespace give the following command

exp userid=scott/tiger TABLESPACES=(tbsp1, tbsp2, ... tbsp#) file=exp_tablespace.dmp

Query Mode

From Oracle 8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. You may need to escape special chars on the command line, for example: query=\”where deptno=10\”. Look at these examples:

exp scott/tiger tables=emp query="where deptno=10"

exp scott/tiger file=abc.dmp tables=abc query=\"where sex=\'f\'\" rows=yes

Exporting Consistent Image of the tables

If you include CONSISTENT=Y option in export command argument then, Export utility will export a consistent image of the table i.e. the changes which are done to the table during export operation will not be exported.

Usage of a parameter file:

exp userid=scott/tiger@orcl parfile=export.txt

… where export.txt contains:

BUFFER=10000000
FILE=account.dmp
FULL=n
OWNER=scott
GRANTS=y
COMPRESS=y

Export to multiple files

From Oracle8i, the export utility supports multiple output files. This feature enables large exports to be divided into files whose sizes will not exceed any operating system limits (FILESIZE= parameter). When importing from multi-file export you must provide the same filenames in the same sequence in the FILE= parameter. Look at this example:

exp SCOTT/TIGER FILE=D:F1.dmp,E:F2.dmp FILESIZE=10m LOG=scott.log

EXPORT, IMPORT are used for the following tasks:

• backup ORACLE data in operating system files.
• restore tables that where dropped
• save space or reduce fragmentation in the database
• move data from one owner to another

Export  tool will extracts the tables and puts it into the dump file. This dump file is transferred to the target database.

At the target database the Import tool will copy the data from dump file to the target database.

Keyword                               Description (Default)

————————————————————–

Most useful

USERID                                 username/password

FILE                                       output files (EXPDAT.DMP)

DIRECT                                 direct path (N)

LOG                                        log file of screen output

ROWS                                    export data rows (Y)

FULL                                    export entire file (N)

OWNER                                list of owner usernames

TABLES                                 list of table names

STATISTICS                       analyze objects (ESTIMATE)

PARFILE                             parameter filename

CONSTRAINTS                  export constraints (Y)

QUERY                                  select clause used to export a subset of a table

TABLESPACES                    list of tablespaces to export