| Partition Export Syntax(4 Merged) [message #516977] |
Thu, 21 July 2011 10:27  |
jaspreet30s
Messages: 11
Registered: January 2006
|
Junior Member |
|
|
Hi
I want to export a table partition via dbms_datapump.
I am not getting the syntax right.
Below is my code for reference
create or replace
PROCEDURE p_datapump_test
AS
l_dp_handle NUMBER;
l_last_job_state VARCHAR2 (30) := 'UNDEFINED';
l_job_state VARCHAR2 (30) := 'UNDEFINED';
l_sts ku$_status;
l_job_name VARCHAR2 (100);
l_dirname VARCHAR2 (100);
l_filename VARCHAR2 (100);
l_date TIMESTAMP;
l_seq NUMBER := EXP_SEQ.NEXTVAL ;
l_raise EXCEPTION;
BEGIN
l_job_name := 'BZ_' || l_seq;
l_filename := 'expfile_'||l_seq;
-- sets the job name
l_dp_handle :=
DBMS_DATAPUMP.OPEN (operation => 'EXPORT',
job_mode => 'TABLE',
remote_link => NULL,
job_name => l_job_name,
VERSION => 'LATEST'
);
--specify the database directory and the filename for the export file
DBMS_DATAPUMP.add_file (handle => l_dp_handle,
filename => l_filename||'.dmp',
DIRECTORY => 'DATA_PUMP_DIR'
);
DBMS_DATAPUMP.add_file (handle => l_dp_handle,
filename => l_filename || '.LOG',
DIRECTORY => 'DATA_PUMP_DIR',
filetype => DBMS_DATAPUMP.ku$_file_type_log_file
);
--specify the tables that I want to export. (ALL_INVENTORY)
DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle,
NAME => 'NAME_EXPR',
VALUE => 'IN (''PARTITION_TEST'')'
);
DBMS_DATAPUMP.data_filter (handle => l_dp_handle,
NAME => 'SUBQUERY',
VALUE => 'WHERE SUPPLY_INSTANCE_KEY IN (10,20)' );
DBMS_DATAPUMP.start_job (l_dp_handle);
DBMS_DATAPUMP.detach (l_dp_handle);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Partition Export Syntax [message #516993 is a reply to message #516992] |
Thu, 21 July 2011 11:22  |
jaspreet30s
Messages: 11
Registered: January 2006
|
Junior Member |
|
|
Sir due to company restrictions , I do not have access to dir where dump file is created , I take DBA help to get the logfile and dump file.
Below is the info from expdp
C:\Documents and Settings\xxxxxxx>expdp TEST_SCHEMA/abcdef_dimes
tables=TEST_SCHEMA.PARTITION_TEST:part_2007 directory=ods_pump_dir dumpfile=part_2008.dmp
Export: Release 11.2.0.1.0 - Production on Thu Jul 21 21:46:22 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "TEST_SCHEMA"."SYS_EXPORT_TABLE_01": TEST_SCHEMA/********
tables=TEST_SCHEMA.PARTITION_TEST:part_2007 directory=ods_pump_dir dumpfile=part_2008.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST_SCHEMA"."PARTITION_TEST":"part_2007" 113.6 KB 90 rows
Master table "TEST_SCHEMA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST_SCHEMA.SYS_EXPORT_TABLE_01 is:
/CRI/oradumps/part_2008.dmp
Job "TEST_SCHEMA"."SYS_EXPORT_TABLE_01" successfully completed at 12:16:55
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
