Skip to Main Content
  • Questions
  • After import/export rowdependecies is lost

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Zilvinas.

Asked: August 09, 2016 - 7:04 am UTC

Last updated: August 10, 2016 - 9:44 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Viewed 1000+ times

You Asked

Hello,

We did dupm and load of our schema using impdp.
And few months later we noticed that all tables that in original database where created with rowdependencies
in new database are created without without rowdependencies.

1. Is this possible? What we done wrong?

Now we need to rename/create/load/drop tables with all foreign keys.
That is very tedious and risky task.
What it makes especially tedious is constraint names.
After alter table rename all constraint names is left the same and new table cannot be created with same names.
Of course it is possible to rename all constraints, but it is quite error prone.

2. Is there any less tedious and error prone way to make tables with rowdependencies?

and Chris said...

Datapump certainly can preserve the rowdependencies property. So either you hit a bug or someone made a mistake.

You can't alter a table to change the rowdependencies property. You need to recreate it.

You could use DBMS_redefinition to do this. This can help reduce downtime. And the procedure copy_table_dependents transfers the grant, indexes, triggers and constraints for you if you want!

http://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPLS67523
https://oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rowdependencies

Zilvinas, August 09, 2016 - 3:16 pm UTC

Thank you for your answer.

Dump/Load was done by our adminstrators. I can't imagine what they could do wrong. I've searched some ipmdp parameters to influence rowdependensies but did not found any.
Chris Saxon
August 09, 2016 - 3:35 pm UTC

There are some bugs relating to losing table properties with impdp in MOS.

Could they have take some manual steps in the process? e.g. pre-creating the tables?

Zilvinas, August 10, 2016 - 7:45 am UTC

We have found out what was wrong.
Administrators have some dificulties and supplied parameter TRANSFORM=SEGMENT_ATTRIBUTES:n


SEGMENT_ATTRIBUTES - If the value is specified as y, then segment attributes (physical attributes, storage attributes, tablespaces, and logging) are included, with appropriate DDL
Connor McDonald
August 10, 2016 - 9:44 am UTC

Thanks for letting us know

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.