Importing and Exporting Oracle Data for DBAs

As a DBA, you are constantly asked to create test schemas from production and sometimes move the entire database. In this course, you'll look at the Oracle Data Pump, a powerful tool which can help you perform these tasks easily and effectively.
Course info
Level
Beginner
Updated
Nov 1, 2016
Duration
3h 29m
Table of contents
Course Overview
Overview
Understanding Oracle Data Pump
Moving Schemas & Metadata
Moving Specific Tablespaces, Suspending, Resuming, and Monitoring Jobs
Moving Specific Objects and Filtering Data
Moving Databases & Transportable Tablespaces
Configuring Advanced Import and Export Parameters
Working with Oracle Multitenant Architecture
Description
Course info
Level
Beginner
Updated
Nov 1, 2016
Duration
3h 29m
Description

DBA are often asked to quickly create test schemas from production, or to move schemas, tablespaces, and tables between instances. In this course, Importing and Exporting Oracle Data for DBAs, you will learn how to move data and metadata between Oracle instances easily and effectively. First, you will explore the Oracle Data Pump tool, which is a very powerful and feature-rich tool. It provides a very fine-grained control for whatever you want to move, from the whole database to just a table with specific rows. Next, you will look at how to move schemas, tablespaces, and even the entire database. You will also explore how to move specific objects and the rich filtering capabilities of this tool. Finally, you will get a high-level overview of the transporting option to move data, which increases the performance significantly, and also briefly touch upon how Oracle Data Pump works with the Multitenant Architecture introduced with Oracle 12c. By the end of this course, you'll have a strong knowledge of importing and exporting Oracle data using the Oracle Data Pump.

About the author
About the author

Experienced technologist, with expertise in various aspects of software development lifecycle, architecting software solutions and software development.

More from the author
Oracle REST Data Services
Beginner
5h 31m
Feb 13, 2018
Getting Productive with SQL Developer
Beginner
3h 53m
Apr 20, 2017
More courses by Pankaj Jain
Section Introduction Transcripts
Section Introduction Transcripts

Overview
As Database Administrators we are constantly asked to create test instances from productions, refresh test schemas, move data to data warehousing environments, and sometimes just move specific objects and data rows to satisfy business requirements. Of the various tools and methods Oracle provides, in this course we are specifically going to discuss the Oracle Data Pump, a great tool, which can help us accomplish these tasks in a very efficient manner. Hello. My name is Pankaj Jain and welcome to this course on Importing and Exporting Oracle Data for DBAs.

Understanding Oracle Data Pump
Oracle Data Pump is a very powerful tool for moving data and metadata between Oracle databases. Let us take a closer look at its utility. Hello. My name is Pankaj Jain and welcome to this module on Understanding Oracle Data Pump. Let us take a high level view first to see how the module is doing to flow. We will start off by talking about some of the key features of Oracle Data Pump which makes it so powerful. Then we will understand it conceptually in terms of its major components and how it works. To keep it interesting and easy to understand, we will use a task-based approach for learning this module and we will introduce our company scenario. We will then provide an overview of the various Data Pump modes which provide you great flexibility and control in what you want to move. We will take a look at the full, schema, tablespace, table, and transportable tablespace modes. Next we will understand how Data Pump behind the scenes accesses data. For instance, using SQL loader direct path or conventional path, external table method, transportable tablespace, way of moving data, etc. Having an understanding as to which is the fastest method and what conditions might restrict Oracle Data Pump from using certain methods will be helpful as you work with this tool. Next we will tackle the issue of Data Pump export and import when the source and destination databases are of different versions and the use of the version parameter. This module should give you a fair understanding of Oracle Data Pump and then form the basis of what we are going to learn in the featured modules. So why you should learn about Oracle Data Pump? Let us see some of its key features.

Moving Schemas & Metadata
Having obtained a basic understanding of Oracle Data Pump, let us now dive into actually doing some real work tasks with it. Hello. My name is Pankaj Jain and welcome to this module on Moving Schemas and Metadata. In this module, along with our junior DBA, Sam, we will be performing tasks and get a better understanding of Oracle Data Pump along the way. We will start off by seeing how to move an entire schema with both the data and metadata using Oracle Data Pump. This can be handy to create a test schema. Next we will see how to find all the SQL statements which the import dump utility is going to execute by generating a SQL file. We can modify the SQL file and run the commands manually if we want. This provides us a great level of control. Next we will see how to move just the schema metadata. Then we will move just the schema data. We will see how to move multiple schemas and map them to target schemas. Along the way we will also take a look at some command line parameters which can enable us to do these tasks effectively and of course we will have several demos along the way to help make these concepts concrete. So let us dive right in.

Moving Specific Tablespaces, Suspending, Resuming, and Monitoring Jobs
Oracle Data Pump provides a great degree of control to specify what we want to export and import as well as to monitor and control executing tasks. Hello. My name is Pankaj Jain and welcome to this module on moving specific tablespaces, suspending, resuming, and monitoring jobs. Tom again has some tasks ready for Sam, so along with Sam, let us learn some other useful features and options of Oracle Data Pump. We will start off by seeing how to execute the tablespace mode operations using the tablespace parameter. We will also learn the usage of the remap_tablespace command line parameter. Now Oracle also has the transportable tablespace option to transfer objects and data. We will talk about that in a later module. Next we will see how we can suspend an executing job, monitor its progress, resume a stopped job, as well as kill the job if necessary. These capabilities provide a lot of convenience to the DBAs, flexibility in controlling jobs, as well as providing good visibility into the job progress.

Moving Specific Objects and Filtering Data
Oracle Data Pump allows us to be really specific in what we want to export or import and it can go down to the level of specific tables or even specific rows from specific tables. Hello. My name is Pankaj Jain and welcome to this module on moving specific objects and filtering data. In this module, we will further examine the table mode for the export and import, which allows us to specify tables and specific partitions or subpartitions we want to export or import. We will look at several useful command line parameters along the way like the table_exists_action which specifies the action to do if the table being imported already exists and the content parameter which allows us to specify what we want to export or import, data, metadata, or both. We will then look at the partition options, which allow us to specify how to handle the partitions on import. We will look at ways for filtering the metadata using the exclude or include parameter and ways to filter specific data rows by using the query parameter, which literally allows us to write where clauses against tables to get specific data rows. We will also look at the sample parameter. Alright. Let's get started and look at the fine-grain control Data Pump provides.

Moving Databases & Transportable Tablespaces
If you have a need, Oracle Data Pump allows you to move an entire database from one instance to another and provides you several options to do that. Hello. My name is Pankaj Jain and welcome to this module on moving databases and transportable tablespaces. In this module we will take a high-level overview of the transportable data options available with Oracle. The transportable options significantly increase the performance or speed of transfer and Oracle provides several levels of transportable options to suit our needs. We will start off with the full transportable export and import options, which allow us to copy the entire database from one instance to another. Next we will export the transportable tablespace option, which as its name indicates, helps with transporting just some specific tablespaces. Finally, we will discuss the mechanics of the transportable tables option which provides us control towards transferring specific tables, or their partitions or subpartitions if we so desire. Throughout, we will look at use cases suitable for each option, along with talking about the steps to achieve them and the limitations.

Configuring Advanced Import and Export Parameters
We have looked at a lot of useful feature and options with Oracle Data Pump already; however, there are still quite a few export and import parameters which can help you achieve better data protection, reduced storage, etc. In this module we will take a look at a few of them. Hello. My name is Pankaj Jain and welcome to this module on Configuring Advanced Import and Export Parameters. In this module we will start looking at some of the other useful parameters available in Oracle Data Pump. We will start off exploring with Sam the parameters which can help with reducing storage space used by the dump files. Next we will look at some parameters which can help increase data protection. Next we will look at a parameter which can help us detect unusable indexes. Finally we will look at some parameters which can help with queries used to look at committed data at a point of time in the past. So let's get started.

Working with Oracle Multitenant Architecture
Oracle introduced the multitenant architecture with Oracle Database 12c. The multitenant architecture contains a container database and multiple pluggable databases. Let us see how the data pump will work with this architecture. Hello. My name is Pankaj Jain and welcome this module on Working with Oracle Multitenant Architecture. In this module we will quickly go over the Oracle multitenant architecture and talk about ways to transfer a non-container database so the one from Oracle version 11g into a container database architecture as a pluggable database. We will talk about some considerations when using Oracle Data Pump with pluggable databases. We will also discuss the ways to transfer pluggable databases within the same container database or across different container databases using Oracle Data Pump. Alright, let's get started.