The Anti-Kyte

Oracle – for when it was like that when you got there

Main menu

Skip to primary content
Skip to secondary content
  • Home
  • About

Tag Archives: PARTITION_EXPR

DBMS_DATAPUMP – why quote delimiters are your friend

Posted on June 30, 2014 by mikesmithers
2

So, the World Cup is in full swing.
Now the lesser teams have fallen by the wayside ( England), we can get on with enjoying a feast of footie.
As well as a glut of goals, the current tournament has given us a salutory reminder of the importance of diet for elite athletes.
After predictably (and brilliantly) destroying England single-handedly, Luis Suaraz found himself a bit peckish and nipped out for an Italian. Now the whole world seems to be commenting on his eating habits.
Like Luis, you may find yourself thinking that you’ve bitten off more than you can chew when confronted by DBMS_DATAPUMP.

The documentation does offer some help…to an extent. However, the whole thing can seem a bit fiddly, especially if you’re used to the more traditional command-line interface for Datapump.

What follows is a tour through DBMS_DATAPUMP based on my own (sometimes painful) experience, broken down into bite-sized chunks.
Much of the functionality to filter object types and even data is common to both Exports and Imports.
So, the approach I’ve taken is to cover the Export first, with a view to finally producing a Full Database export.
I’ve then used the Import process against this to demonstrate some of the package’s filtering capabilities.
So, what’s on the menu today ?

  • Privileges required to run a DBMS_DATAPUMP job from your current schema and for the whole database
  • Running a consistent export
  • Running datapump jobs in the background
  • Monitoring running jobs
  • Importing from one schema to another
  • Specifying the types of objects to include in Exports and Imports
  • Specifying subsets of data
  • DDL only Jobs
  • How to Kill a Datapump Job

The full code examples have all been written and tested on Oracle XE 11gR2.
I’ve tried to maximise the use of in-line hard-coded values and minimise the number of variables in an attempt to make the code easier to follow.
Also, in these examples I’ve made use of the default DATA_PUMP_DIR directory object, but you can use any directory object to which you have the appropriate privileges.

For dessert, there are a couple of other DBMS_DATAPUMP features that I have found useful that are specific to Enterprise Edition ( in one case, with the Partitioning Option) ;

  • Including specific table partitions
  • Parallel processing

So, a fair bit to get through then. I hope you have an apetite… Continue reading →

Advertisements
Posted in Oracle, PL/SQL | Tagged archivelog mode, database_export_objects, dba_datapump_jobs, dbms_datapump examples, dbms_datapump.add_file, dbms_datapump.data_filter, dbms_datapump.detach, dbms_datapump.log_entry, dbms_datapump.metadata_filter, dbms_datapump.metadata_remap, dbms_datapump.open, dbms_datapump.set_parallel, dbms_datapump.set_parameter, dbms_datapump.start_job, dbms_datapump.stop_job, dbms_datapump.wait_for_job, DDL Only import, drop sequence, EXCLUDE_PATH_EXPR, external table, flashback_scn, INCLUDE_PATH_EXPR, INCLUDE_ROWS, NAME_LIST, ora-31634, PARTITION_EXPR, REF_CONSTRAINTS, remap_schema, Running a Datapump job in the background, schema_export_objects, SCHEMA_EXPR, scn, SUBQUERY, TABLE_EXISTS_ACTION, table_export_objects, tail a logfile from sql, timestamp_to_scn, user_datapump_jobs, v$database.current_scn, v$database.log_mode | 2 Replies

Search

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 286 other followers

Popular Ramblings

  • Oracle - Pinning table data in the Buffer Cache
  • Solved – The Mystery of SQLDeveloper and the Missing ocijdbc11
  • V$SGA_TARGET_ADVICE – The Need for Speed
  • Defaulting Null values in SQL*Loader
  • Installing SQLDeveloper and SQLCL on CentOS

Ramblings by Category

Past Ramblings

Pages

  • About

Blogroll

  • Connor McDonald
  • Grassroots Oracle
  • Jeff Kemp
  • Jeff Smith
  • Red Database Security Blog
Advertisements
Blog at WordPress.com.
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy