Showing posts with label Export-Import. Show all posts
Showing posts with label Export-Import. Show all posts

Monday, March 25, 2013

DBMS_XMLGEN: SAve SQL query result in XML

DBMS_XMLGEN is a useful Oracle package that gives you a query result in XML file. Couple of days ago I have got a task to export all tables of an schema in different XML files. Here  is the PL/SQL code


grant read,write on directory DUMP_DIR to scott;
grant execute on DBMS_XMLGEN to scott;
grant execute on utl_file to scott;

set serveroutput on;
set echo on;
set timing on;
spool export_schema_xml.log;
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  l_output utl_file.file_type;
  l_amount NUMBER default 4000;
  l_offset NUMBER(38) default 1;
  l_length NUMBER(38);
  sqltext VARCHAR2 (4000 CHAR);
  result CLOB;
  l_buffer VARCHAR2 (4000 CHAR);
BEGIN
FOR x IN (SELECT x.table_name FROM user_tables x)
LOOP     
  l_offset :=1;
  sqltext := 'SELECT * FROM '||x.table_name;
  qryCtx :=  dbms_xmlgen.newContext (sqltext);
  DBMS_XMLGEN.SETCONVERTSPECIALCHARS (qryCtx,FALSE);
  result :=  DBMS_XMLGEN.getXML(qryCtx);
  l_output := utl_file.fopen('DUMP_DIR', x.table_name||'.xml', 'w', 32760);
  l_length:=nvl(dbms_lob.getlength(result),0);
  WHILE ( l_offset < l_length AND l_length > 0 )
  LOOP
    DBMS_LOB.READ (result, l_amount, l_offset, l_buffer);
    utl_file.put (l_output, l_buffer);
    utl_file.fflush(l_output);
      utl_file.fflush(l_output);
    l_offset := l_offset + l_amount;
  END LOOP;
  utl_file.fflush(l_output);
  utl_file.fclose(l_output);
  DBMS_XMLGEN.CLOSECONTEXT (qryCtx);
  dbms_output.put_line(x.table_name||' Data Exported');
END LOOP;
END;
/

spool off;

Tuesday, December 27, 2011

TABLE_EXISTS_ACTION

Those who are familiar with oracle data pump may know very well about TABLE_EXISTS_ACTION import (impdp) parameter.

TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}

This parameter is used when you import a table which is already exists in import schema. The default value is 'SKIP', so if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.

Now you may interested about rest of the three values-

APPEND - The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).

TRUNCATE - If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.

REPLACE - This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.

Saturday, July 2, 2011

DATA PUMP: Network mode import

Some time we need to copy one schema from one DB machine to another machine or copy one schema as schema (i.e. remap schema) in same DB. The simplest way is to

(a) export required schema, move dump to remote DB and import [copy in 2 DB]

(b) export required schema and import the dump with schema replace option [copy in same DB]


Scenario ONE

Suppose the source schema is too small (say 1-3 GB) the above process is time consuming and labours compare with task requirements.

Scenario TOW

Suppose the source schema is too big (say 100 GB+) and there is not enough disk space to store the 100G size dump.

In above cases you can use the option of network import. But you need to consider that network import is a slower process because all the data should be travel in connection cable if your target is coping a schema in two different DB machine.

Lets describe how we can achieve this. we move user_info schema from source db to remote db.



STEP 1: grant exp_full_database role to socre shcema [IN REMOTE DB]
conn system/pass;

GRANT EXP_FULL_DATABASE to user_info;

STEP 2: [IN DESTINATION DB] Create destination user and grant necessary roles

conn system/pass;
CREATE USER user_info
IDENTIFIED BY pass
DEFAULT TABLESPACE USER
TEMPORARY TABLESPACE TEMP;

GRANT CONNECT , RESOURCE TO user_info;

STEP 3: [IN DESTINATION DB] grant read/write on dump directory

conn system/pass;

GRANT read,write on DIRECTORY dump_directory to user_info;

Network import does not requer any dump file. This directory is only requer to write the import log file.

STEP 4: [IN DESTINATION DB] create public DB Link

conn system/pass;
CREATE PUBLIC DATABASE LINK SATURN
connect to user_info identified by pass
using '(DESCRIPTION=(
ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remote_db)
(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=orcl.oracle.com)
(server=DEDICATED)))';

STEP 5: [IN DESTINATION DB MACHINE] execute impdp

impdp user_info/pass directory=dump_dir network_link=SATURN logfile=net_import_proddev.log EXCLUDE=GRANT,STATISTICS,SYNONYM,DB_LINK REMAP_SCHEMA=USER_INFO:USER_INFO

Thursday, October 30, 2008

SQL*Loader Part #1

SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. You can use SQL*Loader to do the following:

  1. Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.
  2. Load data from multiple datafiles during the same load session.
  3. Load data into multiple tables during the same load session.
  4. Specify the character set of the data.
  5. Selectively load data (you can load records based on the records' values).
  6. Manipulate the data before loading it, using SQL functions.
  7. Generate unique sequential key values in specified columns.
  8. Use the operating system's file system to access the datafiles.
  9. Load data from disk, tape, or named pipe.
  10. Generate sophisticated error reports, which greatly aid troubleshooting.
  11. Load arbitrarily complex object-relational data.
  12. Use secondary datafiles for loading LOBs and collections.
  13. Use either conventional or direct path loading. While conventional path loading is very flexible, direct path loading provides superior loading performance.
A typical SQL*Loader session takes as input a control file, which controls the behavior of SQL*Loader, and one or more datafiles. The output of SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially, a discard file.


SQL*Loader Parameters

SQL*Loader is invoked when you specify the sqlldr command and, optionally, parameters that establish session characteristics. In situations where you always use the same parameters for which the values seldom change, it can be more efficient to specify parameters using the following methods, rather than on the command line:

  1. Parameters can be grouped together in a parameter file. You could then specify the name of the parameter file on the command line using the PARFILE parameter.
  2. Certain parameters can also be specified within the SQL*Loader control file by using the OPTIONS clause.

Parameters specified on the command line override any parameter values specified in a parameter file or OPTIONS clause.


SQL*Loader Control File

The control file is a text file written in a language that SQL*Loader understands. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, where to insert the data, and more. Although not precisely defined, a control file can be said to have three sections. The first section contains session-wide information, for example:

  1. Global options such as bindsize, rows, records to skip, and so on.
  2. INFILE clauses to specify where the input data is located.
  3. Data to be loaded.

The second section consists of one or more INTO TABLE blocks. Each of these blocks contains information about the table into which the data is to be loaded, such as the table name and the columns of the table. The third section is optional and, if present, contains input data. Some control file syntax considerations to keep in mind are:

  1. The syntax is free-format (statements can extend over multiple lines).
  2. It is case insensitive; however, strings enclosed in single or double quotation marks are taken literally, including case.
  3. In control file syntax, comments extend from the two hyphens (--) that mark the beginning of the comment to the end of the line. The optional third section of the control file is interpreted as data rather than as control file syntax; consequently, comments in this section are not supported.
  4. The keywords CONSTANT and ZONE have special meaning to SQL*Loader and are therefore reserved. To avoid potential conflicts, Oracle recommends that you do not use either CONSTANT or ZONE as a name for any tables or columns.

Data Fields

Once a logical record is formed, field setting on the logical record is done. Field setting is a process in which SQL*Loader uses control-file field specifications to determine which parts of logical record data correspond to which control-file fields. It is possible for two or more field specifications to claim the same data. Also, it is possible for a logical record to contain data that is not claimed by any control-file field specification. Most control-file field specifications claim a particular part of the logical record. This mapping takes the following forms:

  1. The byte position of the data field's beginning, end, or both, can be specified. This specification form is not the most flexible, but it provides high field-setting performance. The strings delimiting (enclosing and/or terminating) a particular data field can be specified. A delimited data field is assumed to start where the last data field ended, unless the byte position of the start of the data field is specified.
  2. The byte offset and/or the length of the data field can be specified. This way each field starts a specified number of bytes from where the last one ended and continues for a specified length.
  3. Length-value datatypes can be used. In this case, the first n number of bytes of the data field contain information about how long the rest of the data field is.


Data Conversion and Datatype Specification

During a conventional path load, data fields in the datafile are converted into columns in the database (direct path loads are conceptually similar, but the implementation is different). There are two conversion steps:

  1. SQL*Loader uses the field specifications in the control file to interpret the format of the datafile, parse the input data, and populate the bind arrays that correspond to a SQL INSERT statement using that data.
  2. The Oracle database accepts the data and executes the INSERT statement to store the data in the database.

The Oracle database uses the datatype of the column to convert the data into its final, stored form. Keep in mind the distinction between a field in a datafile and a column in the database. Remember also that the field datatypes defined in a SQL*Loader control file are not the same as the column datatypes.


Discarded and Rejected Records

Records read from the input file might not be inserted into the database. Such records are placed in either a bad file or a discard file.

The Bad File

The bad file contains records that were rejected, either by SQL*Loader or by the Oracle database. If you do not specify a bad file and there are rejected records, then SQL*Loader automatically creates one. It will have the same name as the data file, with a.bad extension. Some of the possible reasons for rejection are discussed in the next sections.

SQL*Loader Rejects

Datafile records are rejected by SQL*Loader when the input format is invalid. For example, if the second enclosure delimiter is missing, or if a delimited field exceeds its maximum length, SQL*Loader rejects the record. Rejected records are placed in the bad file.

Oracle Database Rejects

After a datafile record is accepted for processing by SQL*Loader, it is sent to the Oracle database for insertion into a table as a row. If the Oracle database determines that the row is valid, then the row is inserted into the table. If the row is determined to be invalid, then the record is rejected and SQL*Loader puts it in the bad file. The row may be invalid, for example, because a key is not unique, because a required field is null, or because the field contains invalid data for the Oracle datatype.

Please go SQL*Loader Part #2 for more details


Related Topic:
  1. SQL*Loader Part #1
  2. SQL*Loader Part #2

SQL*Loader Part #2

The Discard File

As SQL*Loader executes, it may create a file called the discard file. This file is created only when it is needed, and only if you have specified that a discard file should be enabled. The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file. The discard file therefore contains records that were not inserted into any table in the database. You can specify the maximum number of such records that the discard file can accept. Data written to any database table is not written to the discard file.

Log File and Logging Information

When SQL*Loader begins execution, it creates a log file. If it cannot create a log file, execution terminates. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load.

Conventional Path Loads

During conventional path loads, the input records are parsed according to the field specifications, and each data field is copied to its corresponding bind array. When the bind array is full (or no more data is left to read), an array insert is executed. SQL*Loader stores LOB fields after a bind array insert is done. Thus, if there are any errors in processing the LOB field (for example, the LOBFILE could not be found), the LOB field is left empty. Note also that because LOB data is loaded after the array insert has been performed, BEFORE and AFTER row triggers may not work as expected for LOB columns. This is because the triggers fire before SQL*Loader has a chance to load the LOB contents into the column. For instance, suppose you are loading a LOB column, C1, with data and that you want a BEFORE row trigger to examine the contents of this LOB column and derive a value to be loaded for some other column, C2, based on its examination. This is not possible because the LOB contents will not have been loaded at the time the trigger fires.

Direct Path Loads

A direct path load parses the input records according to the field specifications, converts the input field data to the column datatype, and builds a column array. The column array is passed to a block formatter, which creates data blocks in Oracle database block format. The newly formatted database blocks are written directly to the database, bypassing much of the data processing that normally takes place. Direct path load is much faster than conventional path load, but entails several restrictions.

A parallel direct path load allows multiple direct path load sessions to concurrently load the same data segments (allows intrasegment parallelism). Parallel direct path is more restrictive than direct path.


Invoking SQL*Loader

When you invoke SQL*Loader, you can specify certain parameters to establish session characteristics. Parameters can be entered in any order, optionally separated by commas. You specify values for parameters, or in some cases, you can accept the default without entering a value. For example:

SQLLDR CONTROL=sample.ctl, LOG=sample.log, BAD=baz.bad, DATA=etc.dat
USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dsc, DISCARDMAX=5

If you invoke SQL*Loader without specifying any parameters, SQL*Loader displays a help screen similar to the following. It lists the available parameters and their default values.


Alternative Ways to Specify Parameters

If the length of the command line exceeds the size of the maximum command line on your system, you can put certain command-line parameters in the control file by using the OPTIONS clause. You can also group parameters together in a parameter file. You specify the name of this file on the command line using the PARFILE parameter when you invoke SQL*Loader.

Examples:

1. One can load data into an Oracle database by using the sqlldr utility. Invoke the utility as follows:

sqlldr username@server/password control=loader.ctl
sqlldr username/password@server control=loader.ctl

This sample control file (loader.ctl) will load an external data file containing delimited data:
-------------------------------------------------------------------------------------------------------------------------------------
load data
infile 'c:\data\mydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
-----------------------------------------------------------------------------------------------------------------------------------
The mydata.csv file may look like this:

10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
-----------------------------------------------------------------------------------------------------------------------------------

2. Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file:
--------------------------------------------------------------------------------------------------------------------------------------
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
-------------------------------------------------------------------------------------------------------------------------------------

3. Data can be modified as it loads into the Oracle Database. One can also populate columns with static or derived values. However, this only applies for the conventional load path (and not for direct path loads). Here are some examples:
--------------------------------------------------------------------------------------------------------------------------------
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
--------------------------------------------------------------------------------------------------------------------------------------
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)
------------------------------------------------------------------------------------------------------------------------------------

For more examples please go here :

Related Topic:
  1. SQL*Loader Part #1
  2. SQL*Loader Part #2

Tuesday, September 16, 2008

Using QUERY parameter in expdp/impdp

In data pump export import we can use SQl query to filter exported/imported data.
The parameter we use for this is 'QUERY'. But parameter QUERY requer proper formating (quotes)
Incorrect usage of single or double quotes for the QUERY parameter can result FOLLOWING errors:

ORA-39001: invalid argument value
ORA-00933: SQL command not properly ended
LRM-00111: no closing quote for value ''
ORA-06502: PL/SQL: numeric or value error


1. USING 'QUERY' in Parameter file

The preferred method of using QUERY parameter is in a parameter file. Put double quotes around the WHERE clause.

parameter file exp_emp.par contains,

TABLES:emp_salary
DUMPFILE=dump_dir
QUERY= emp_salary:"WHERE emp_id IN (SELECT id from emp
where join_date> to_date('2004-01-01','YYYY-MM-DD')
AND dept='SALSE')"

2. USING 'QUERY' parameter on Command line

Suppose you want to create a subset of the table based on some criteria, e.g. “SALARY>10000”, you would issue

$ expdp query=employees:"where salary>10000" tables=employees

This can also take the ORDER BY clause to create the dumpfile in a sorted order. Suppose you want to dump the EMPLOYEES table order by SALARY, here is how the complete command looks like (with the unix required escape characters – backslahes):

$ expdp arup/arup directory=demo_dir dumpfile=employees.dmp
query=employees:\"where salary\>10000\ order by salary" tables=employees

Tuesday, September 2, 2008

Monitor Data Pump Job

Some times it is important to monitor long running datapump job. well, there are several ways to that :

1. Executing Interactive parameter (STATUS) in command line impdp/expdp.

2. Through datapump API

2. alternatively you can run a sql quarry :

select x.job_name,b.state,b.job_mode,b.degree
, x.owner_name,z.sql_text, p.message
, p.totalwork, p.sofar
, round((p.sofar/p.totalwork)*100,2) done
, p.time_remaining
from dba_datapump_jobs b
left join dba_datapump_sessions x on (x.job_name = b.job_name)
left join v$session y on (y.saddr = x.saddr)
left join v$sql z on (y.sql_id = z.sql_id)
left join v$session_longops p ON (p.sql_id = y.sql_id)
WHERE y.module='Data Pump Worker'
AND p.time_remaining > 0;

Wednesday, July 16, 2008

Data pump Import (impdp) patt #2

2. Parameter File Interface: Enables you to specify command-line parameters in a parameter file. The only exception is the PARFILE parameter because parameter files cannot be nested. The use of parameter files is recommended if you are using parameters whose values require quotation marks. See Use of Quotation Marks On the Data Pump Command Line.


3. Interactive-Command Interface: Stops logging to the terminal and displays the Export prompt, from which you can enter various commands, some of which are specific to interactive-command mode. This mode is enabled by pressing Ctrl+C during an export operation started with the command-line interface or the parameter file interface. Interactive-command mode is also enabled when you attach to an executing or stopped job. For a complete description of the commands available in interactive-command


ADD_FILE - Add additional dump files.

CONTINUE_CLIENT - Exit interactive mode and enter logging mode.

EXIT_CLIENT - Stop the export client session, but leave the job running.

FILESIZE - Redefine the default size to be used for any subsequent dump files.

HELP - Display a summary of available commands.

KILL_JOB - Detach all currently attached client sessions and kill the current job.

PARALLEL - Increase or decrease the number of active worker processes for the current job. This command is valid only in the Enterprise Edition of Oracle Database 10g.

START_JOB - Restart a stopped job to which you are attached.

STATUS - Display detailed status for the current job and/or set statusinterval.

STOP_JOB - Stop the current job for later restart.



Data Pump Import Modes


Full Import Mode

A full import is specified using the FULL parameter. In full import mode, the entire content of the source (dump file set or another database) is loaded into the target database. This is the default for file-based imports. You must have the IMP_FULL_DATABASE role if the source is another database.
Cross-schema references are not imported for non-privileged users. Example:

impdp system/password DIRECTORY=dpump_dir DUMPFILE=expfull.dmp FULL=Y

Schema Mode

A schema import is specified using the SCHEMAS parameter. In a schema import, only objects owned by the specified schemas are loaded. The source can be a full, table,tablespace, or schema-mode export dump file set or another database. If you have the IMP_FULL_DATABASE role, then a list of schemas can be specified and the schemas themselves (including system privilege grants) are created in the database in addition to the objects contained within those schemas. Cross-schema references are not imported for non-privileged users unless the other schema is remapped to the current schema. Example


impdp hr/hr SCHEMAS=hr DIRECTORY=dump_dir
DUMPFILE=hr-20080107.dmp

impdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr
ESTIMATE_ONLY = Y EXCLUDE=TABLE:"like 'EMP":"EMP_2005'"

impdp system/pssword SCHEMAS=hr DIRECTORY=dump_dir
DUMPFILE=hr20080120.dmp REMAP_SCHEMA=hr:test

impdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr INCLUDE=TABLE:"like'EMP_%'" DUMPFILE=nr_emp_tables.dmp TABLE_EXISTS_ACTION=REPLACE

Table Mode

A table-mode import is specified using the TABLES parameter. In table mode, only the specified set of tables, partitions, and their dependent objects are loaded. The source can be a full, schema, tablespace, or table-mode export dump file set or another database. You must have the IMP_FULL_DATABASE role to specify tables that are not in your own schema.

impdp hr/hr TABLES=emp DIRECTORY=dump_dir CONTENT=DATA_ONLY DUMPFILE=emp-20080106.dmp

impdp hr/hr TABLES=login DIRECTORY=dump_dir DUMPFILE=hr_login_table.dmp TABLE_EXISTS_ACTION=REPLACE


Tablespace Mode

A tablespace-mode import is specified using the TABLESPACES parameter. In tablespace mode, all objects contained within the specified set of tablespaces are loaded, along with the dependent objects. The source can be a full, schema, tablespace, or table-mode export dump file set or another database. For unprivileged users, objects not remapped to the current schema will not be processed.

impdp system/password DIRECTORY=dpump_dir DUMPFILE=tbs.dmp
TABLESPACES=tbs_4, tbs_5, tbs_6


Transportable Tablespace Mode

A transportable tablespace import is specified using the TRANSPORT_TABLESPACES parameter. In transportable tablespace mode, the metadata from a transportable tablespace export dump file set or from another database is loaded. The datafiles specified by the TRANSPORT_DATAFILES parameter must be made available from the
source system for use in the target database, typically by copying them over to the target system.


impdp system/password DIRECTORY=dpump_dir DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=Y LOGFILE=tts.log



Data pump Import (impdp) patt #1

Data Pump Import is a utility for loading an export dump file set into a target system. The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set.

Import can also be used to load a target database directly from a source database with no intervening dump files. This allows export and import operations to run concurrently, minimizing total elapsed time. This is known as a network import.

Data Pump Import enables you to specify whether a job should move a subset of the data and metadata from the dump file set or the source database (in the case of a network import), as determined by the import mode. This is done using data filters and metadata filters, which are implemented through Import commands

Invoking Data Pump Import

The Data Pump Import utility is invoked using the impdp command. The
characteristics of the import operation are determined by the import parameters you specify. These parameters can be specified either on the command line or in a parameter file.

Data Pump Import Interfaces

You can interact with Data Pump Import by using a command line, a parameter file, or an interactive-command mode.

1.Command-Line Interface: Enables you to specify the Import parameters directly on the command line. For a complete description of the parameters available in the command-line interface, see Parameters Available in Import's Command-Line Mode


ParameterDescription
Default
attach
connects a client session to a current running data pump import job

content
filters what is import: data_only, metadata_only , all
all
directory
destination of log and dump files
data_pump_dir
dumpfile
name of the dump file

estimate
method used to estimate dump file size : blocks or statistics

estimate_only
Y/N. instruct data_pump whether data should be imported or estimated.
N
exclude
exclude objects and data from being imported.

flashback_scn
scn of the database to flash back to to during imported

full
Y/N. import all data and metadata in a full mode import
N
help
Y/N. display a list of avillable commands and options
N
include
specify which objects and data will be exported

job_name
name of the job
system generated
logfile
name of log file
expdp.log
network_link
source database link for a data pump job importing a remote database

nologfile
a Y/N flag used to suppress log file creation

parallel
set number of workers for the export job
1
parfile
name the parameter file to use

query
filters row from table during the import

schemas
name the schema to be exported for schema mode import

remap_datafiles
change the name fo source datafiles

remap tablespace
change the name fo source tablespace

remap_schema
import data into a new schema

skip_unusable_indexes
a N/Y flag

sql file
name of file in which DDL of the import will be write

table_exists_action
instruct import how to proceed if the table being imported is exist. Values include skip,append,truncate and replace
skip
status
displays detailed status of the data pump job

tables
list of tables and partitions to be exported for table mode import

tablespace
list of tablespaces to import in tablespace mode

transport_full_check
Specifies whether the tablespaces being imported should be verified as a self-contained set.

transport_tablespaces
specifies a transportable tablespace mode import

transport_datafiles
list of datafiles to be imported during a transportable tablespace mode import

version
specifies the version of the database.



Please go part#2

Sunday, July 13, 2008

Data pump Export (expdp) patt #2

2. Parameter File Interface: Enables you to specify command-line parameters in a parameter file. The only exception is the PARFILE parameter, because parameter files cannot be nested.

3. Interactive-Command Interface: Stops logging to the terminal and displays the Export prompt, from which you can enter various commands, some of which are specific to interactive-command mode. This mode is enabled by pressing Ctrl+C during an export operation started with the command-line interface or the parameter file interface. Interactive-command mode is also enabled when you attach to an executing or stopped job. For a complete description of the commands available in interactive-command

ADD_FILE - Add additional dump files.

CONTINUE_CLIENT - Exit interactive mode and enter logging mode.

EXIT_CLIENT - Stop the export client session, but leave the job running.

FILESIZE - Redefine the default size to be used for any subsequent dump files.

HELP - Display a summary of available commands.

KILL_JOB - Detach all currently attached client sessions and kill the current job.

PARALLEL - Increase or decrease the number of active worker processes for the current job. This command is valid only in the Enterprise Edition of Oracle Database 10g.

START_JOB - Restart a stopped job to which you are attached.

STATUS - Display detailed status for the current job and/or set statusinterval.

STOP_JOB - Stop the current job for later restart.


Data Pump Export Modes: One of the most significant characteristics of an export operation is its mode, because the mode largely determines what is exported. Export provides different modes for unloading different portions of the database. The mode is specified on the command line, using the appropriate parameter. The available modes are as follows:

1. Full Export Mode: A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. Example:

expdp system/password DIRECTORY=dpump_dir DUMPFILE=expfull.dmp FULL=Y

2. Schema Mode: A schema export is specified using the SCHEMAS parameter. This is the default export mode.
Example:

expdp hr/hr SCHEMAS=hr DIRECTORY=dump_dir DUMPFILE=hr.dmp FLASHBACK_SCN=65610662

expdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr
EXCLUDE=TABLE:"like'EMP'" DUMPFILE=hr_exclude_emp.dmp

expdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr
INCLUDE=TABLE:"like'EM%'" DUMPFILE=hr.dmp


expdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr
ESTIMATE_ONLY = Y EXCLUDE=TABLE:"like 'EMP":"EMP_2005'"

3. Table Mode : A table export is specified using the TABLES parameter. In table mode, only a specified set of tables, partitions, and their dependent objects are unloaded.
Example:

expdp hr/hr DIRECTORY=dump_dir SCHEMAS=prod7
INCLUDE=TABLE:"like'HR_%'" DUMPFILE=hr_tables.dmp

expdp hr/hr TABLES=login,emp,salary DIRECTORY=dump_dir
DUMPFILE=hr_table.dmp


expdp hr/hr TABLES=emp DIRECTORY=dump_dir
DUMPFILE=emp-20080106.dmp FLASHBACK_SCN=65619542


expdp hr/hr DIRECTORY=dump_dir DUMPFILE=emp.dmp
TABLES=EMP:EMP_PART_2005

expdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr
EXCLUDE=TABLE:"like'EMP'" EXCLUDE=TABLE:"SALARY'"



4. Tablespace Mode : A tablespace export is specified using the TABLESPACES parameter. In tablespace mode, only the tables contained in a specified set of tablespaces are unloaded. If a table is unloaded, its dependent objects are also unloaded. Both object metadata and data
are unloaded. In tablespace mode, if any part of a table resides in the specified set, then that table and all of its dependent objects are exported. Privileged users get all tables.
Example:

expdp system/password DIRECTORY=dpump_dir DUMPFILE=tbs.dmp
TABLESPACES=tbs_4, tbs_5, tbs_6



5. Transportable Tablespace Mode : A transportable tablespace export is specified using the TRANSPORT_TABLESPACES parameter. In transportable tablespace mode, only the metadata for the tables (and their dependent objects) within a specified set of tablespaces are unloaded. This allows
the tablespace datafiles to then be copied to another Oracle database and incorporated using transportable tablespace import. This mode requires that you have the EXP_FULL_DATABASE role.
Unlike tablespace mode, transportable tablespace mode requires that the specified tables be completely self-contained. That is, the partitions of all tables in the set must also be in the set. Transportable tablespace exports cannot be restarted once stopped. Also, they cannot have a degree of parallelism greater than 1.
Example:

expdp system/password DIRECTORY=dpump_dir DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=Y LOGFILE=tts.log



Data pump Export (expdp) patt #1

Data Pump Export is a utility 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. The dump file set can be imported on the same system or it can be moved to another system and
loaded there.

The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format.

Invoking Data Pump Export :
The Data Pump Export utility is invoked using the expdp command. The characteristics of the export operation are determined by the Export parameters you specify. These parameters can be specified either on the command line or in a parameter file.

Data Pump Export Interfaces: You can interact with Data Pump Export by using a command line, a parameter file, or an interactive-command mode.

1.Command-Line Interface: Enables you to specify most of the Export parameters directly on the command line. Parameters Available in Export's
Command-Line Mode



ParameterDescription
Default
attach
connects a client session to a current running data pump export job

content
filters what is export: data_only, metadata_only , all
all
directory
destination of log and dump files
data_pump_dir
dumpfile
name of the dump file

estimate
method used to estimate dump file size : blocks or statistics

estimate_only
Y/N. instruct data_pump whether data should be exported or estimated.
N
exclude
exclude objects and data from being exported.

filesize
maximum size of each dump file

flashback_scn
scn of the database to flash back to to during exported

flushback_time
timestamp of the database to flash back to to during exported
full
Y/N. export all data and metadata in a full mode export
N
help
Y/N. display a list of avillable commands and options
N
include
specify which objects and data will be exported

job_name
name of the job
system generated
logfile
name of log file
expdp.log
network_link
source database link for a data pump job exporting a remote database

nologfile
a Y/N flag used to suppress log file creation

parallel
set number of workers for the export job
1
parfile
name the parameter file to use

query
filters row from table during the export

schemas
name the schema to be exported for schema mode export

status
displays detailed status of the data pump job

tables
list of tables and partitions to be exported for table mode export

tablespace
list of tablespaces to export in tablespace mode

transport_full_check
Specifies whether the tablespaces being exported should be verified as a self-contained set.

transport_tablespaces
specifies a transportable tablespace mode export

version
specifies the version of the database.



please go to Part 2

Thursday, May 22, 2008

ORACLE DATA PUMP Part #3

EVEN MORE ADVANCED FEATURES OF ORACLE DATA PUMP

Beyond the command-line and performance features of Oracle Data Pump are new capabilities that DBAs will find invaluable. A couple of prominent features are described here.

Interactive Command-Line Mode

You have much more control in monitoring and controlling Data Pump jobs with interactive command-line mode. Because Data Pump jobs run entirely on the server, you can start an export or import job, detach from it, and later reconnect to the job to monitor its progress. Here are some of the things you can do while in this mode:
  1. See the status of the job. All of the information needed to monitor the job’s execution is available.
  2. Add more dump files if there is insufficient disk space for an export file. Change the default size of the dump files.
  3. Stop the job (perhaps it is consuming too many resources) and later restart it (when more resources become available).
  4. Restart the job. If a job was stopped for any reason (system failure, power outage), you can attach to the job and then restart it.
  5. Increase or decrease the number of active worker processes for the job. (Enterprise Edition only.)
  6. Attach to a job from a remote site (such as from home) to monitor status.

Network Mode

Data Pump gives you the ability to pass data between two databases over a network (via a database link), without creating a dump file on disk. This is very useful if you’re moving data between databases, like data marts to data warehouses, and disk space is not readily available. Note that if you are moving large volumes of data, Network mode is probably going to be slower than file mode. Network export creates the dump file set on the instance where the Data Pump job is running and extracts the metadata and data from the remote instance. Network export gives you the ability to export read-only databases. (Data Pump Export cannot run locally on a read-only instance because the job requires write operations on the instance.) This is useful when there is a need to export data from a standby database.

Generating SQLFILES

In original Import, the INDEXFILE parameter generated a text file which contained the SQL commands necessary to recreate tables and indexes that you could then edit to get a workable DDL script. With Data Pump, it’s a lot easier to get a workable DDL script. When you run Data Pump Import and specify the SQLFILE parameter, a text file is generated that has the necessary DDL (Data Definition Language) in it to recreate all object types, not just tables and indexes. Although this output file is ready for execution, the DDL statements are not actually executed, so the target system will not be changed. SQLFILEs can be particularly useful when pre-creating tables and objects
in a new database. Note that the INCLUDE and EXCLUDE parameters can be used for tailoring sqlfile output. For example, if you want to create a database that contains all the tables and indexes of the source database, but that does not include the same constraints, grants, and other metadata, you would issue a command as follows:

>impdp username/password DIRECTORY=dpumpdir1 DUMPFILE=expfull.dmp SQLFILE=dpump_dir2:expfull.sql INCLUDE=TABLE,INDEX

The SQL file named expfull.sql is written to dpump_dir2 and would include SQL DDL that could be executed in another database to create the tables and indexes as desired.

Interested To learn datapump, then go through the two topics

1. Data Pump Export
2. Data Pump Import

ORACLE DATA PUMP Part #2

Export of an entire database to a dump file with all GRANTS, INDEXES, and data

> exp username/password FULL=y FILE=dba.dmp GRANTS=y INDEXES=y ROWS=y

> expdp username/password FULL=y INCLUDE=GRANT INCLUDE=INDEX DIRECTORY=dpump_dir1 DUMPFILE=dba.dmp CONTENT=ALL

Data Pump offers much greater metadata filtering than original Export and Import. The INCLUDE parameter allows you to specify which object (and its dependent objects) you want to keep in the export job. The EXCLUDE parameter allows you to specify which object (and its dependent objects) you want to keep out of the export job. You cannot mix the two parameters in one job. Both parameters work with Data Pump Import as well, and you can use different INCLUDE and EXCLUDE options for different operations on the same dump file.

Tuning Parameters

Unlike original Export and Import, which used the BUFFER, COMMIT, COMPRESS, CONSISTENT, DIRECT, and RECORDLENGTH parameters, Data Pump needs no tuning to achieve maximum performance. Data Pump chooses the best method to ensure that data and metadata are exported and imported in the most efficient manner. Initialization parameters should be sufficient upon installation.

Moving data between versions

The Data Pump method for moving data between different database versions is different from the method used by original Export and Import. With original Export, you had to run an older version of Export to produce a dump file that was compatible with an older database version. With Data Pump, you use the current Export version and simply use the VERSION parameter to specify the target database version. You cannot specify versions earlier than Oracle Database 10g (since Data Pump did not exist before 10g).

> expdp username/password TABLES=hr.employees VERSION=10.1 DIRECTORY =dpump_dir1 DUMPFILE=emp.dmp

Data Pump Import can always read dump file sets created by older versions of Data Pump Export. Note that Data Pump Import cannot read dump files produced by original Export.


Maximizing the Power of Oracle Data Pump

Data Pump works great with default parameters, but once you are comfortable with Data Pump, there are new capabilities that you will want to explore.

Parallelism

Data Pump Export and Import operations are processed in the database as a Data Pump job, which is much more efficient that the client-side execution of original Export and Import. Now Data Pump operations can take advantage of the server’s parallel processes to read or write multiple data streams simultaneously (PARALLEL is only available in the Enterprise Edition of Oracle Database.) The number of parallel processes can be changed on the fly using Data Pump’s interactive command-line mode. You may have a certain number of processes running during the day and decide to change that number if more system resources become available at night (or vice versa).

For best performance, you should do the following:
  1. Make sure your system is well balanced across CPU, memory, and I/O.
  2. Have at least one dump file for each degree of parallelism. If there aren’t enough dump files, performance will not be optimal because multiple threads of execution will be trying to access the same dump file.
  3. Put files that are members of a dump file set on separate disks so that they will be written and read in parallel.
  4. For export operations, use the %U variable in the DUMPFILE parameter so multiple dump files can be automatically generated.
> expdp username/password DIRECTORY=dpump_dir1 JOB_NAME=hr DUMPFILE=par_exp%u.dmp PARALLEL=4

REMAP

REMAP_TABLESPACE – This allows you to easily import a table into a different tablespace from which it was originally exported. The databases have to be 10.1 or later.

> impdp username/password REMAP_TABLESPACE=tbs_1:tbs_6 DIRECTORY =dpumpdir1 DUMPFILE=employees.dmp

REMAP_DATAFILES – This is a very useful feature when you move databases between platforms that have different file naming conventions. This parameter changes the source datafile name to the target datafile name in all SQL
statements where the source datafile is referenced. Because the REMAP_DATAFILE value uses quotation marks, it’s best to specify the parameter within a parameter file.

The parameter file, payroll.par, has the following content:

DIRECTORY=dpump_dir1
FULL=Y
DUMPFILE=db_full.dmp
REMAP_DATAFILE=”’C:\DB1\HRDATA\PAYROLL\tbs6.dbf’:’/db1/hrdata/pa
yroll/tbs6.dbf’”

You can then issue the following command:

> impdp username/password PARFILE=payroll.par

Continued in ORACLE DATA PUMP Part #3

ORACLE DATA PUMP Part #1

Oracle Data Pump is the replacement for the original Export and Import utilities. Available starting in Oracle Database 10g, Oracle Data Pump enables very high-speed movement of data and metadata from one database to another. The Data Pump Export and Import utilities have a similar look and feel to theoriginal utilities, but they are much more efficient and give you greater control and management of import and export jobs.

There are two new concepts in Oracle Data Pump that are different from original Export and Import.

1. Directory Objects: Data Pump differs from original Export and Import in that all jobs run primarily on the server using server processes. These server processes access files for the Data Pump jobs using directory objects that identify the location of the files. The directory objects enforce a security model that can be used by DBAs to control access to these files.

2. Interactive Command-Line Mode: Besides regular operating system command-line mode, there is now a very powerful interactive command-line mode which allows the user to monitor and control Data Pump Export and Import operations.

Creating Directory Objects

In order to use Data Pump, the database administrator must create a directory object and grant privileges to the user on that directory object. If a directory object is not specified, a default directory object called data_pump_dir is provided. The default data_pump_dir is available only to privileged users unless access is granted by the DBA.

The following SQL statement creates a directory object named dump_dir that is mapped to a directory located at /u01/dumps.

>sulplus / as sysdba
SQL> CREATE DIRECTORY dump_dir AS ‘/u01/dumps/’;

After a directory is created, you need to grant READ and WRITE permission on the directory to other users. For example, to allow the Oracle database to read and to write to files on behalf of user scott in the directory named by dump_dir, you must execute the following command:

SQL> GRANT READ,WRITE ON DIRECTORY dump_dir TO scott;

Note that READ or WRITE permission to a directory object means only that the Oracle database will read or write that file on your behalf. You are not given direct access to those files outside of the Oracle database unless you have the appropriate operating system privileges. Similarly, the Oracle database requires permission from the operating system to read and write files in the directories. Once the directory access is granted, the user scott can export his database objects with command arguments:

>expdp username/password DIRECTORY=dpump_dir1 dumpfile=scott.dmp

Comparison of command-line parameters from Original Export and Import to Data Pump

Data Pump commands have a similar look and feel to the original Export and Import commands, but are different. Below are a few examples that demonstrate some of these differences.

Import of tables from scott’s account to jim’s account

Original Import:
> imp username/password FILE=scott.dmp FROMUSER=scott TOUSER=jim TABLES=(*)
Data Pump Import:
> impdp username/password DIRECTORY=dpump_dir1 DUMPFILE=scott.dmp TABLES=scott.emp REMAP_SCHEMA=scott:jim

Continued in ORACLE DATA PUMP Part #2

Thursday, May 8, 2008

External Tables

External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.

First create a sample text file named test.txt which contains

CHINA,CHI,
CANADA,CA,
BANGLADESH,BD,
INDIA,IND,
AUSTRALIA,AUS,

Next create a directory object which points to the location of the files:

> conn / as sysdba;

SQL>CREATE OR REPLACE DIRECTORY DUMP_DIR AS 'C:\temp\';
SQL> GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO scott;

Next create the metadata for the external table using the CREATE TABLE..ORGANIZATION EXTERNAL syntax:


SQL> CREATE TABLE countries(
country_name VARCHAR2(50),
country_code VARCHAR2(5)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY dump_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
country_name CHAR(50),
country_code CHAR(5)
)
)
LOCATION ('test.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

Table created.


SQL> select * from countries;

COUNTRY_NAME COUNT
-------------------------------------------------- -----
CHINA CHI
CANADA CA
BANGLADESH BD
INDIA IND
AUSTRALIA AUS

SQL>

TO drop the table

SQL > drop table countries;