Home » RDBMS Server » Server Utilities » Tablespace and Schema info from dumpfile (Oracle 11 on Windows 2008)
Tablespace and Schema info from dumpfile [message #625398] Tue, 07 October 2014 13:55 Go to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Can someone tell me how to import METADATA to a file ?
I would like to use this to identify the tablespaces that I need to create for a full database import.

All I have is a dumpfile.

Later I will do a full database import as follows but I want to escape the errors of tablespace not available.

impdp system/?????@datahome FULL=YES LOGFILE=logfile1.log directory=dpump_dir dumpfile=dumpfile1.dmp 


Please advise.
Re: Tablespace and Schema info from dumpfile [message #625399 is a reply to message #625398] Tue, 07 October 2014 14:13 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
unwilling or incapable to just Read The Fine Manual?

http://docs.oracle.com/database/121/SUTIL/dp_import.htm#SUTIL300

[oracle@localhost ~]$ impdp help=yes

Import: Release 11.2.0.2.0 - Production on Tue Oct 7 12:12:08 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC.
Valid keyword values are: [Y] and N.

CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keywords are: SKIP_CONSTRAINT_ERRORS.

DIRECTORY
Directory object to be used for dump, log and SQL files.

DUMPFILE
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.

ESTIMATE
Calculate job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Import everything from source [Y].

HELP
Display help messages [N].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of import job to create.

LOGFILE
Log file name [import.log].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [N].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file.

PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].

QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_DATAFILE
Redefine data file references in all DDL statements.

REMAP_SCHEMA
Objects from one schema are loaded into another schema.

REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.

REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.

REUSE_DATAFILES
Tablespace will be initialized if it already exists [N].

SCHEMAS
List of schemas to import.

SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.

SKIP_UNUSABLE_INDEXES
Skip indexes that were set to the Index Unusable state.

SOURCE_EDITION
Edition to be used for extracting metadata.

SQLFILE
Write all the SQL DDL to a specified file.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STREAMS_CONFIGURATION
Enable the loading of Streams metadata

TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to import.

TARGET_EDITION
Edition to be used for loading metadata.

TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.

TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.

TRANSPORT_DATAFILES
List of data files to be imported by transportable mode.

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [N].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.

VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.


[oracle@localhost ~]$

Re: Tablespace and Schema info from dumpfile [message #625402 is a reply to message #625399] Tue, 07 October 2014 14:27 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
BlackSwan,

I am not surprised that you are able to copy and paste documentation. However, neither you or the documentation (I had read before) answers my question,
how to import METADATA to a file. Now be a hero and share some syntax .

jxh
Re: Tablespace and Schema info from dumpfile [message #625403 is a reply to message #625402] Tue, 07 October 2014 14:28 Go to previous messageGo to next message
John Watson
Messages: 8022
Registered: January 2010
Location: Global Village
Senior Member
If you read the doc, you will find that the INCLUDE directive does what you want.

--
update: with SQLFILE

[Updated on: Tue, 07 October 2014 14:30]

Report message to a moderator

Re: Tablespace and Schema info from dumpfile [message #625404 is a reply to message #625403] Tue, 07 October 2014 14:33 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
>However, neither you or the documentation (I had read before) answers my question, how to import METADATA to a file.


what do you see when you just SEARCH what I posted for the keyword METADATA?
Re: Tablespace and Schema info from dumpfile [message #625406 is a reply to message #625402] Tue, 07 October 2014 14:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

jxh461 wrote on Tue, 07 October 2014 21:27
BlackSwan,

I am not surprised that you are able to copy and paste documentation. However, neither you or the documentation (I had read before) answers my question,
how to import METADATA to a file. Now be a hero and share some syntax .

jxh


Quote:
SQLFILE
Write all the SQL DDL to a specified file.


Read the documentation is not sufficient, you have to try to understand it too.

[Updated on: Tue, 07 October 2014 14:59]

Report message to a moderator

Re: Tablespace and Schema info from dumpfile [message #625409 is a reply to message #625406] Tue, 07 October 2014 16:14 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Here is an example of how to answer my question.

Run the following command and the sql file that is produced will have the queries to create the tablespaces among other objects.

impdp system/******@database directory=dpump_dir dumpfile=dumpfile.dmp sqlfile=sqlfile.sql keep_master=Y nologfile=Y 


You see. Now I will be a hero to anyone who seeks this information.

Thanks anyway.
Re: Tablespace and Schema info from dumpfile [message #625410 is a reply to message #625409] Wed, 08 October 2014 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 66638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And we are your hero as we learn you how to learn and find the answer by yourself. Smile

[Updated on: Wed, 08 October 2014 01:33]

Report message to a moderator

Re: Tablespace and Schema info from dumpfile [message #625542 is a reply to message #625410] Thu, 09 October 2014 15:45 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
So the purpose of this forum is to direct inquiries to documentation ?
Confused
Re: Tablespace and Schema info from dumpfile [message #625544 is a reply to message #625542] Thu, 09 October 2014 16:05 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
why should we spend our time writing here what is already documented elsewhere?

If you desire to repost the Documentation set here, please proceed to do so.
Re: Tablespace and Schema info from dumpfile [message #625551 is a reply to message #625542] Fri, 10 October 2014 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 66638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

jxh461 wrote on Thu, 09 October 2014 22:45
So the purpose of this forum is to direct inquiries to documentation ?
Confused


No, its purpose is to learn you how to learn and find by yourself and then learn how to search in the documentation and read it in the first point.

We prefer to push people to smartness rather than to be dependent on our answers. Smile

[Updated on: Fri, 10 October 2014 00:40]

Report message to a moderator

Re: Tablespace and Schema info from dumpfile [message #625573 is a reply to message #625544] Fri, 10 October 2014 08:55 Go to previous message
jxh461
Messages: 185
Registered: March 2005
Senior Member
I must admit you do a good job of pointing people to documentations. Laughing

Thanks.
Previous Topic: expdp ora-39127 lbacsys missing
Next Topic: could not open parameter file 'imp_woc.par'
Goto Forum:
  


Current Time: Tue Oct 15 11:00:13 CDT 2019