Should I be using this to export the data using the SQL Developer ‘Data Pump Export Wizard’ connected as a DBA

dockeroracle

I am trying to learn more about Oracle databases in my spare time. I have Oracle Enterprise available in a Docker Container. I am a developer rather than a DBA. I have plenty of experience with SQL Server.

I am trying to use the data pump feature. So far I have done this:

Created a user called dummy and created a table (Dummy.table1).
Added a few rows to table1.
Created a user called admin and given this user dba access.
Login to SQL Developer as admin and select: View/DBA.
Connect to my database and use Data Pump Export Wizard to create a dump file.
Generate a .sql file from the .dmp file.

I have tried all four options (Database; Tablespaces; Schemas and Tabeles) under: 'Types to be exported in data pump', selecting 'Data and DDL' each time, however I cannot see the INSERT statements, which should be created for Dummy.table1. Why is this?

The only thing I can think of is that there is a menu item called: Tools\Database Export. Should I be using this to export the data? I would of thought that the data should be exported when using the Data Pump Export Wizard; connected as a DBA?

Best Answer

Data Pump stores definitions and data in dumpfiles with a binary format specific to Oracle. Data Pump can be used to generate DDL SQL statements (create user, table, view, trigger, etc.), but not for the data in tables.

If you want INSERT statements, then yes, use the Database Export feature of SQL Developer instead of Data Pump.

Related Solutions

ORA-39142: incompatible version number 3.1 error work-around

Here's an interesting blog entry from Burleson computing that appears potentially useful for your circumstance.

From the site:

There are two cases where it is possible to import into an earlier release:

1 - Using the version parameter in Data Pump. See my notes on importing between different releases of Oracle with Data Pump export/import

2 - Using a exp dump file with a Data Pump impdp utility.

How to find the schema name of a data pump dmp file

Use the sqlfile= parameter of impdp to generate a file containing all of the DDL/DML in the dump.

For example:

[oracle@oel61 ~]$ impdp phil/phil directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt

Import: Release 11.2.0.2.0 - Production on Wed Mar 13 15:15:03 2013

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "PHIL"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "PHIL"."SYS_SQL_FILE_FULL_01":  phil/******** directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "PHIL"."SYS_SQL_FILE_FULL_01" successfully completed at 15:15:05

[oracle@oel61 ~]$

Looking for CREATE USER DDL statements in the file should show you what schemas are needed in order to perform an import.

For example, from my test dump:

-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "PHIL" IDENTIFIED BY VALUES 'S:924B2E756404611021428644B4DF06A4A7BAB886837FCCFA510151E0FC44;181446AE258EE2F6'
      DEFAULT TABLESPACE "PHILDATA"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "PHIL";
GRANT CREATE SESSION TO "PHIL";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "PHIL";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "PHIL" DEFAULT ROLE ALL;

The only drawback is that if the .dmp file is large the resulting SQL dump will be huge.

Related Question