Exclude Parameter in Oracel Data-Pump

Environment : 11gR2 on Windows 2008 Server



>> Export full=y backup and Skip One Table Using ' Exclude ' Parameter.

D:\ iectest_exp_full > expdp directory=iectest_exp_full dumpfile=abc  EXCLUDE=TABLE:\"IN\(\'SHSHTDTA'\)\" full=y


>> From Full=Y backup Skip two Table..

D:\ iectest_exp_full > expdp directory=iectest_exp_full dumpfile=ahph EXCLUDE=TABLE:\"IN\(\'AHTRNHST','PHTRNHST'\)\" full=y

                                             

 >> Skipping 2 Tables from Schema Level Backup

D:\ iectest_exp_full > expdp directory=iectest_exp_full dumpfile=abcd EXCLUDE=TABLE:\"IN\(\'AHTRNHST','PHTRNHST'\)\" schemas=HMIS



Happy Learning...

ORA- 31687 / 31688 ERROR While performing Schema Level Backup Using Data-Pump

E:\hmis_schema>IMPDP DUMPFILE=HMIS.DMP DIRECTORY=HMIS_SCHEMA REMAP_SCHEMA=HMIS:HMIS

Import: Release 11.2.0.3.0 - Production on Wed Sep 21 12:52:05 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31687: error creating worker process  with worker id 1
ORA-31687: error creating worker process  with worker id 1
ORA-31688: Worker process  failed during startup.


Note:- Data-Pump Uses Streams Pool So Increase the Size of Streams Pool, 
           But I increased   total SGA size.

> Check First What is the size of SGA.

SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;

TOTAL SGA (GB)
--------------
    1.94439697

> Increase the Size of SGA.

SQL> alter system set sga_max_size=10g scope=spfile;

System altered.

SQL>  alter system set memory_max_target=12g scope=spfile;

System altered.

SQL>  alter system set memory_target=12g scope=spfile;

System altered.


Now You Can Start Using Data-Pump Job.......


Happy Learning..

How to check Table Creation Date in Oracle

SQL> SELECT OWNER,OBJECT_NAME,CREATED FROM DBA_OBJECTS WHERE                                       OBJECT_NAME='PHTRNHST';

OWNER       OBJECT_NAME       CREATED
----------- ----------------- ------------------------------
PUBLIC          PHTRNHST             12-JAN-15



>> How to Check Table Names Which Starts from SHSH.


SQL> select table_name from all_tables where table_name like 'SHSH%';

TABLE_NAME
------------------------------
SHSHDTA_KK
SHSHTDTA
SHSHTDTA_BACKUP
SHSHTDTA_ORIGINAL
SHSHTDTA_PART
SHSHTDTL
SHSHTDTLLAST
SHSHTFLD
SHSHTGRP
SHSHTMRLIST
SHSHTNAM
SHSHTOLE
SHSHTPIC
SHSHTSNT
SHSHTDTL

15 rows selected.

Happy Learning....