Exclude and Include parameter of Data Pump

The Exclude and Include feature of Data Pump can filter to load/unload certain objects .This so-called 'Metadata filtering' is implemented through the EXCLUDE and INCLUDE parameters.The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. 

All object types for the given mode of export will be included except those specified in an EXCLUDE statement. If an object is excluded, all of its dependent objects are also excluded. For example, excluding a table will also exclude all indexes and triggers on the table.The name clause must be separated from the object type  with a colon and enclosed in double quotation marks, because single-quotation marks are required to delimit the name strings Double quotes and single quotes usage. 

The EXCLUDE and INCLUDE parameters are mutually exclusive i.e, It is not possible to specify both the INCLUDE parameter and the EXCLUDE parameter in the same job.

The basic syntax for both parameters is the same.

INCLUDE=object_type[:name_clause] [, ...]
EXCLUDE=object_type[:name_clause] [, ...]

Few examples are :

EXCLUDE=SEQUENCE, TABLE:"IN ('EMP', 'DEPT')"
EXCLUDE=INDEX:"= 'MY_INDX'"
INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"
INCLUDE=FUNCTION, PACKAGE, TABLE:"= 'EMP'" 
INCLUDE=PROCEDURE:\"=\'PROC1\'\",FUNCTION:\"=\'FUNC1\'\"

To run this job without a parameter file, we need to escape the special characters. Incorrect escaping can result in errors.

for example  :   INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"

To determine which objects are dependent, e.g. for a TABLE, we can run the following query,

SQL> select  named, object_path, comments FROM database_export_objects WHERE object_path LIKE 'TABLE/%';

While Excluding Users specifying EXCLUDE=USER excludes only the definitions of users, not the objects contained within users' schemas. To exclude a specific user and all objects of that user, specify a filter such as the following (where SCOTT is the schema name of the user we want to exclude):

EXCLUDE=SCHEMA:\"='SCOTT'\"

So, we can export as also use below command to execute export .

C:\> expdp system/xxxx directory=datapump dumpfile=expdp_scott.dmp  logfile=expdp_scott.log schemas=scott  INCLUDE=TABLE:\"IN \(SELECT object_name FROM scott.expdp_table WHERE owner=\'SCOTT\' AND object_type=\'TABLE\'\)\"

Enjoy   J J J

How Data Pump Export Parameters Map to Those of the Original Export Utility

Here are the mapping of Data Pump Export parameters to original Export parameters. In some cases, because of feature redesign, the original Export parameter is no longer needed, so there is no Data Pump parameter to compare it to. Also, some of the parameter names may be the same, but the functionality is slightly different. The parameter are as 

Original Export Parameter	Comparable Data Pump Export Parameter
BUFFER	A parameter comparable to BUFFER is not needed.
COMPRESS	A parameter comparable to COMPRESS is not needed.
CONSISTENT	A parameter comparable to CONSISTENT is not needed. Use FLASHBACK_SCN and FLASHBACK_TIME for this functionality.
CONSTRAINTS	EXCLUDE=CONSTRAINT
DIRECT	A parameter comparable to DIRECT is not needed. Data Pump Export automatically chooses the best method (direct path mode or external tables mode).
FEEDBACK	STATUS
FILE	DUMPFILE
FILESIZE	FILESIZE
FLASHBACK_SCN	FLASHBACK_SCN
FLASHBACK_TIME	FLASHBACK_TIME
FULL	FULL
GRANTS	EXCLUDE=GRANT
HELP	HELP
INDEXES	EXCLUDE=INDEX
LOG	LOGFILE
OBJECT_CONSISTENT	A parameter comparable to OBJECT_CONSISTENT is not needed.
OWNER	SCHEMAS
PARFILE	PARFILE
QUERY	QUERY
RECORDLENGTH	A parameter comparable to RECORDLENGTH is not needed because sizing is done automatically.
RESUMABLE	A parameter comparable to RESUMABLE is not needed. This functionality is automatically provided for users who have been granted the EXP_FULL_DATABASE role.
RESUMABLE_NAME	A parameter comparable to RESUMABLE_NAME is not needed. This functionality is automatically provided for users who have been granted the EXP_FULL_DATABASE role.
RESUMABLE_TIMEOUT	A parameter comparable to RESUMABLE_TIMEOUT is not needed. This functionality is automatically provided for users who have been granted the EXP_FULL_DATABASE role.
ROWS=N	CONTENT=METADATA_ONLY
ROWS=Y	CONTENT=ALL
STATISTICS	A parameter comparable to STATISTICS is not needed. Statistics are always saved for tables.
TABLES	TABLES
TABLESPACES	TABLESPACES (Same parameter; slightly different behavior)
TRANSPORT_TABLESPACE	TRANSPORT_TABLESPACES (Same parameter; slightly different behavior)
TRIGGERS	EXCLUDE=TRIGGER
TTS_FULL_CHECK	TRANSPORT_FULL_CHECK
USERID	A parameter comparable to USERID is not needed. This information is supplied as the username and password when you invoke Export.
VOLSIZE	A parameter comparable to VOLSIZE is not needed.

Enjoy  :-) 

RMAN : Create Script Command

Create Script command is one of the Rman command which is used to stored script in recovery catalog.A stored script may be local or global. A local script is created for the current target database only, whereas a global script is available for use with any database registered in the recovery catalog.

We can use a stored script as an alternative to a command file for managing frequently used sequences of RMAN commands. The script is stored in the recovery catalog rather than on the file system.

The commands allowable within the brackets of the CREATE SCRIPT command are the same commands supported within a RUN block. Any command that is legal within a RUN command is permitted in the stored script.

Here, in this scenario we will perform the following steps :
1.) Create   Script
2.) Replace Script
3.) Execute Script 

Create Script  :  Assume that we want to create a local stored script for backing up database "noida". we start RMAN, connect to "noida" as TARGET, and connect to a recovery catalog. we create a stored script called rman_backup . 

C:\>rman
Recovery Manager: Release 11.1.0.6.0 - Production on Sat May 7 15:15:02 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
RMAN> connect target sys/ramtech@noida
connected to target database: NOIDA (DBID=1503672566)
RMAN> connect catalog  rcat/rcat@catdb
connected to recovery catalog database

RMAN> Create script rman_backup

2> Comment "backup Whole Database and archived redo logs "

3> {

4> backup incremental level 0

5> format "D:\rman_bkp\%U"

6> Database plus archivelog;

7> }

Replace Scripts :  To update stored scripts, we use the REPLACE SCRIPT command. If we are replacing a local script, then we must be connected to the target database that we connected to when we created the script. If the script does not already exist, then RMAN creates it.

RMAN> replace script rman_backup 
{
  backup database plus archivelog  skip inaccessible ;
}
         We can update global scripts by specifying the GLOBAL keyword .

Execute Script :   We use the EXECUTE SCRIPT command to run a stored script. If GLOBAL is specified, then a global script with this name must already exist in the recovery catalog; otherwise, RMAN returns error RMAN-06004. If GLOBAL is not specified, then RMAN searches for a local stored script defined for the current target database. If no local script with this name is found, then RMAN searches for a global script by the same name and executes it if one is found.

To execute a stored script run EXECUTE SCRIPT. This command requires a RUN block, as shown in the following example:


RMAN> run  
{ 
  excute script rman_backup; 
}


Enjoy  : -)