Home » RDBMS Server » Server Utilities » ORACLE DATAPUMP table_exists_action - only tables are replaced ? (Oracle Database ,10g, Unix)
ORACLE DATAPUMP table_exists_action - only tables are replaced ? [message #349934] Tue, 23 September 2008 09:27 Go to next message
smordi
Messages: 2
Registered: September 2008
Junior Member
Hi everyone!
I need to make a datapump from schema A(export) to B(import A).

There exists the parameter "table_exists_action" with the option "replace". The documentations say, that the TABLES are replaced. So if a table t_adress already exists in schema A, then the table t_adress is dropped and afterwards initialized completly new with fresh datas from the dump-file.
Is it right, that ONLY tables are replaced? What about all the other objects like e.g. packages?

If other objects are not replaced... do there exist other possibilities to replace them?

Best regards
smordi

fyi ... just saw a similar question here:
http://www.orafaq.com/forum/m/236656/130262/?srch=table_exists_action#msg_236656

[Updated on: Tue, 23 September 2008 13:02]

Report message to a moderator

Re: ORACLE DATAPUMP table_exists_action - only tables are replaced ? [message #350656 is a reply to message #349934] Thu, 25 September 2008 17:35 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

The possible values have the following effects:

SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

APPEND loads rows from the source and leaves existing rows unchanged.

TRUNCATE deletes existing rows and then loads rows from the source.

REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

The following considerations apply when you are using these options:

When you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.

When you use SKIP, APPEND, or TRUNCATE, existing table-dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. For REPLACE, the dependent objects are dropped and re-created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.

When you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action.

The existing table is loaded using the external tables access method if the existing tables have active constraints and triggers. However, be aware that if any row violates an active constraint, the load fails and no data is loaded.

If you have data that must be loaded, but may cause constraint violations, consider disabling the constraints, loading the data, and then deleting the problem rows before reenabling the constraints.

When you use APPEND, the data is always loaded into new space; existing space, even if available, is not reused. For this reason, you may wish to compress your data after the load.

Restrictions

TRUNCATE cannot be used on clustered tables or over network links.

Re: ORACLE DATAPUMP table_exists_action - only tables are replaced ? [message #350657 is a reply to message #350656] Thu, 25 September 2008 17:37 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


I think TABLE_EXISTS_ACTION only for tables not package.

You can go with EXCLUDE & INCLUDE parameter for package.

Babu
Re: ORACLE DATAPUMP table_exists_action - only tables are replaced ? [message #350731 is a reply to message #350656] Fri, 26 September 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Babu,

Instead of just copying the documentation it is better to post the link to it then you don't limit what we can read to what you choose to post.

Link is:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref365

Regards
Michel
Re: ORACLE DATAPUMP table_exists_action - only tables are replaced ? [message #350809 is a reply to message #349934] Fri, 26 September 2008 08:28 Go to previous messageGo to next message
smordi
Messages: 2
Registered: September 2008
Junior Member
Thanks so far.
Think, I am going to do that manually.

If somebody has already a good script for deleting all objects depending to one given user, it would be a pleasure for me to safe lots of time. So... don't hesitate and post this script Smile

Best



Re: ORACLE DATAPUMP table_exists_action - only tables are replaced ? [message #350843 is a reply to message #350809] Fri, 26 September 2008 12:58 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Hello Mr.Michael,

Sure. Hereafter I'll port exact link.

Thank you in advance.

Babu
Previous Topic: Silent Export
Next Topic: How to take logical incremental backup
Goto Forum:
  


Current Time: Tue Dec 24 10:09:52 CST 2024