datapump export at schema level and table partition level

experts,

i have a problem with partition table export with data pump. i am planning to perform partition export by using data pump. here is the parameter i am using.

i have created datapump directory like this
set feed on echo on time on
20:32:51 BMVZWSTG:SYS> CREATE OR REPLACE DIRECTORY CDSVZW_TAB_EXP AS '/opt/mis2/oracle/data/BMVZWSTG/db21/CDSVZW_TAB_EXP';

Directory created.

20:32:54 BMVZWSTG:SYS> grant READ, WRITE ON DIRECTORY CDSVZW_TAB_EXP to CDSVZW;

Grant succeeded.

my parameter file contain
DUMPFILE=RAWTXN_200710_exp.dmp
DIRECTORY=CDSVZW_TAB_EXP
LOGFILE=RAWTXN_200710_exp.log
SCHEMAS=CDSVZW
TABLES=CDSVZW.RAWTXN:RAWTXN_200710
QUERY=CDSVZW.RAWTXN:"WHERE ROWNUM<100"
EXCLUDE=INDEXES,CONSTRAINTS,TRIGGERS
JOB_NAME=export_rawtxn_200710_job
PARALLEL=4

when in ran the expdp i got following error

BMVZWSTG-<ORACLE>-13% expdp cdsvzw/xxxxx@BMVZWSTG parfile=expdb_rawtxn_200710.par

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 04 May, 2009 20:31:05

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
UDE-00010: multiple job modes requested, schema and tables.

i am trying to test the export functionlity by export 100 rows initially. and import those dump file in other database. it every thing looks file i will move my work in prod.

please help me where is the problem.

LVL 1
pavan27Asked:
Who is Participating?
 
mrjoltcolaConnect With a Mentor Commented:
Verify your QUERY parameter, it has 2 sets of quotes, single quotes and double quotes. Just try double quotes.


QUERY=CDSVZW.RAWTXN:"WHERE ROWNUM<100"

Open in new window

0
 
mrjoltcolaConnect With a Mentor Commented:
Don't combine SCHEMAS and TABLES params. Use one of the other.
0
 
mrjoltcolaConnect With a Mentor Commented:
I meant "one or the other"
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pavan27Author Commented:
Hi,

Thanks for reply. now i have changed parameter file like this

DUMPFILE=RAWTXN_200710_exp.dmp
DIRECTORY=CDSVZW_TAB_EXP
LOGFILE=RAWTXN_200710_exp.log
TABLES=CDSVZW.RAWTXN:RAWTXN_200710
QUERY=CDSVZW.RAWTXN:'"WHERE ROWNUM<100"'
JOB_NAME=export_rawtxn_200710_job
PARALLEL=4

i have made some changes in the par file. i was removed schema parameters and  exclude list.
i got some more error.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "CDSVZW"."EXPORT_RAWTXN_200710_JOB":  cdsvzw/********@BMVZWSTG parfile=expdb_rawtxn_200710.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11.65 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-31693: Table data object "CDSVZW"."RAWTXN":"RAWTXN_200710" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
LPX-314: an internal failure occurred
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "CDSVZW"."EXPORT_RAWTXN_200710_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for CDSVZW.EXPORT_RAWTXN_200710_JOB is:
  /opt/mis2/oracle/data/BMVZWSTG/db21/CDSVZW_TAB_EXP/RAWTXN_200710_exp.dmp
Job "CDSVZW"."EXPORT_RAWTXN_200710_JOB" completed with 1 error(s) at 22:03:21


in my case i need to exclude triggers, constraints and indexes on the partition.

could you please help me to skip the index,triggers and constraints.

0
 
tzxie2000Connect With a Mentor Commented:
Verify your parfile,if you want to exclude triggers,contraints,and indexes on the partition,follow the below:
EXCLUDE=TRIGGER:"IN ('TRIG1', 'TRIG2')", INDEX:"= 'INDX1'", REF_CONSTRAINT
And modify your QUERY like this:
QUERY=CDSVZW.RAWTXN:"WHERE ROWNUM<100"
0
 
tzxie2000Connect With a Mentor Commented:
Modify exclude in parfile like this:
exclude=index,trigger,constraint
0
 
pavan27Author Commented:
Experts,

Thanks for suggestion. i was able to export the entire partition data. but not some records in a particular partitions.
could you please suggest the modifications in my parameter file.

DUMPFILE=RAWTXN_200710_exp.dmp
DIRECTORY=CDSVZW_TAB_EXP
LOGFILE=RAWTXN_200710_exp.log
TABLES=CDSVZW.RAWTXN:RAWTXN_200710
JOB_NAME=export_rawtxn_200710_job
EXCLUDE=TRIGGER:"LIKE 'RTX%'",INDEX:"LIKE 'RAWTXN%'"
PARALLEL=4

now i want to export some of the records on RAWTXN_200710 PARTITION. what parameter i need to change in the parameter file.

2. i want to eliminate the statistics too. statistics are taking long time. how do i mention in exclude list .

3. i am planning to one schema level export. because i am planning to drop one schema, before dropping that schema i am taking schema level export. my question is here
do i need to take export of all the objects in the export dump.

do i need to take export dump as system user?
0
 
mrjoltcolaConnect With a Mentor Commented:
>>now i want to export some of the records on RAWTXN_200710 PARTITION. what parameter i need to change in the parameter file.

You are already exporting that partition, according to your parameter file.


>>2. i want to eliminate the statistics too. statistics are taking long time. how do i mention in exclude list .

Add STATISTICS to the EXCLUDE comma separated list


>>3. i am planning to one schema level export. because i am planning to drop one schema, before dropping that schema i am taking schema level export. my question is here
>>do i need to take export of all the objects in the export dump.

I don't know, do you wish to save those objects? You know that when you drop a schema the objects are gone forever. So you make the decision whether to export or back them up.


>>do i need to take export dump as system user?

Not unless you are doing a full export or multiple schemas. You can usually just use the schema user to export the particular schema.
0
 
pavan27Author Commented:
Thanks for reply.

yes i have successfully exported partition. my requirement is

i need to export some of the records in the partition. for example, i need to export only 1000 records in the rawtxn_200710 partition. what is the parameter i need to add here.

yes i am taking export backup of the schema and trying to save it on 7 years retention policy on tape.
0
 
mrjoltcolaConnect With a Mentor Commented:
Try this:
QUERY=CDSVZW.RAWTXN:RAWTXN_200710:"WHERE ROWNUM<1000"

Open in new window

0
 
pavan27Author Commented:
Experts,

i have one more question regarding directories,

i have created one data pump directories. now i want to drop that directories. how can i drop that directories.

is there any pre check do i need to follow before drop the directory.
0
 
mrjoltcolaConnect With a Mentor Commented:
Good form is to ask another question, if it is a different topic.

You can create multiple directories.

Dropping the directory will only drop the Oracle definition, not the actual filesystem directory, but there may be programs / scripts dependant on it. If you are the one who created it, then you can drop it with no worries, but if it is a default directory, it is likely used by a standard Oracle module, and dropping it will break that module.

select owner, directory_name, directory_path from dba_directories;

The name should give you an indication how the application.

For example: DATA_PUMP_DIR is created by default, and is the default data pump directory if not specified, so it is not recommend to drop that one permanently.
0
 
pavan27Author Commented:
Thanks for information,

i have create one directory for my use. here are the directories i have.
BMVZWSTG:SYSTEM> l
  1* select owner, directory_name, directory_path from dba_directories
BMVZWSTG:SYSTEM> /

OWNER      DIRECTORY_NAME   DIRECTORY_PATH
---------- ---------------- -------------------------------------------------------
SYS        DATA_PUMP_DIR    /opt/mis2/oracle/product/10.2.0/rdbms/log/
SYS        CDSVZW_TAB_EXP   /opt/mis2/oracle/data/BMVZWSTG/db21/CDSVZW_TAB_EXP
SYS        RAWTXN_DMP       /opt/mis2/oracle/work/BMVZWPRD/rawtxn_dmp
SYS        TIMEZDIF_DIR     /opt/mis2/oracle/product/10.2.0/oracore/zoneinfo
BMVZWSTG:SYSTEM>

i was created "CDSVZW_TAB_EXP". can i drop this directory by using

drop directory CDSVZW_TAB_EXP;

and remove this directory at OS level. is this good action?



0
 
mrjoltcolaConnect With a Mentor Commented:
>>i was created "CDSVZW_TAB_EXP". can i drop this directory by using
>>
>>drop directory CDSVZW_TAB_EXP;

Of course. Dropping it in Oracle will not remove it from OS. Actually Oracle does not check existence of it until you try to use it.

>>and remove this directory at OS level. is this good action?

That is the usual way.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.