Home » RDBMS Server » Server Utilities » Partition Export Syntax(4 Merged) (11g)
Partition Export Syntax(4 Merged) [message #516977] Thu, 21 July 2011 10:27 Go to next message
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 #516979 is a reply to message #516977] Thu, 21 July 2011 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Any particular reason for using PL/SQL instead of command line?

>I am not getting the syntax right.
my car won't go.
tell me how to make my car go.

It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.
Re: Partition Export Syntax [message #516983 is a reply to message #516977] Thu, 21 July 2011 10:42 Go to previous messageGo to next message
jaspreet30s
Messages: 11
Registered: January 2006
Junior Member
Hi
The reason I am using command line , is I will make the filter criteria dynamically , which would not be possible via command line
Re: Partition Export Syntax [message #516984 is a reply to message #516983] Thu, 21 July 2011 10:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
one implementation approach is to first make it work, then make it fancy.
If you can not make expdp work from command line for single hard coded case,
I doubt you'll make it work via PL/SQL.
post working example from command line
Re: Partition Export Syntax(4 Merged) [message #516989 is a reply to message #516977] Thu, 21 July 2011 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel
Re: Partition Export Syntax [message #516990 is a reply to message #516984] Thu, 21 July 2011 11:02 Go to previous messageGo to next message
jaspreet30s
Messages: 11
Registered: January 2006
Junior Member
expdp test_schema/test123 tables=TEST_SCHEMA.PARTITION_TEST:part_2007 directory=data_pump_dir dumpfile=part_2007.dmp
Re: Partition Export Syntax [message #516992 is a reply to message #516990] Thu, 21 July 2011 11:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>expdp test_schema/test123 tables=TEST_SCHEMA.PARTITION_TEST:part_2007 directory=data_pump_dir dumpfile=part_2007.dmp
I suggest that it is a Good Thing to always produce LOGFILE.

please post OS directory listing showing details for "part_2007.dmp" file

what is OS name & version?
Re: Partition Export Syntax [message #516993 is a reply to message #516992] Thu, 21 July 2011 11:22 Go to previous messageGo to next message
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
Re: Partition Export Syntax [message #516994 is a reply to message #516993] Thu, 21 July 2011 11:42 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 21 July 2011 18:01
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel

Previous Topic: exporting metadata backup
Next Topic: "SYS.DBMS_EXPORT_EXTENSION" does not exist
Goto Forum:
  


Current Time: Fri Mar 29 10:07:14 CDT 2024