pavan27
asked on
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/BMV ZWSTG/db21 /CDSVZW_TA B_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:RAWTX N_200710
QUERY=CDSVZW.RAWTXN:"WHERE ROWNUM<100"
EXCLUDE=INDEXES,CONSTRAINT S,TRIGGERS
JOB_NAME=export_rawtxn_200 710_job
PARALLEL=4
when in ran the expdp i got following error
BMVZWSTG-<ORACLE>-13% expdp cdsvzw/xxxxx@BMVZWSTG parfile=expdb_rawtxn_20071 0.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.
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/BMV
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
DIRECTORY=CDSVZW_TAB_EXP
LOGFILE=RAWTXN_200710_exp.
SCHEMAS=CDSVZW
TABLES=CDSVZW.RAWTXN:RAWTX
QUERY=CDSVZW.RAWTXN:"WHERE
EXCLUDE=INDEXES,CONSTRAINT
JOB_NAME=export_rawtxn_200
PARALLEL=4
when in ran the expdp i got following error
BMVZWSTG-<ORACLE>-13% expdp cdsvzw/xxxxx@BMVZWSTG parfile=expdb_rawtxn_20071
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:RAWTX N_200710
JOB_NAME=export_rawtxn_200 710_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?
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
DIRECTORY=CDSVZW_TAB_EXP
LOGFILE=RAWTXN_200710_exp.
TABLES=CDSVZW.RAWTXN:RAWTX
JOB_NAME=export_rawtxn_200
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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/1 0.2.0/rdbm s/log/
SYS CDSVZW_TAB_EXP /opt/mis2/oracle/data/BMVZ WSTG/db21/ CDSVZW_TAB _EXP
SYS RAWTXN_DMP /opt/mis2/oracle/work/BMVZ WPRD/rawtx n_dmp
SYS TIMEZDIF_DIR /opt/mis2/oracle/product/1 0.2.0/orac ore/zonein fo
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?
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/1
SYS CDSVZW_TAB_EXP /opt/mis2/oracle/data/BMVZ
SYS RAWTXN_DMP /opt/mis2/oracle/work/BMVZ
SYS TIMEZDIF_DIR /opt/mis2/oracle/product/1
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for reply. now i have changed parameter file like this
DUMPFILE=RAWTXN_200710_exp
DIRECTORY=CDSVZW_TAB_EXP
LOGFILE=RAWTXN_200710_exp.
TABLES=CDSVZW.RAWTXN:RAWTX
QUERY=CDSVZW.RAWTXN:'"WHER
JOB_NAME=export_rawtxn_200
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_20
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_D
Total estimation using BLOCKS method: 11.65 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/O
ORA-31693: Table data object "CDSVZW"."RAWTXN":"RAWTXN_
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/I
Processing object type TABLE_EXPORT/TABLE/CONSTRA
Processing object type TABLE_EXPORT/TABLE/INDEX/S
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATIST
Master table "CDSVZW"."EXPORT_RAWTXN_20
**************************
Dump file set for CDSVZW.EXPORT_RAWTXN_20071
/opt/mis2/oracle/data/BMVZ
Job "CDSVZW"."EXPORT_RAWTXN_20
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.