Home » RDBMS Server » Server Utilities » export/import and NOVALIDATE constraints (11.2.0.1)
export/import and NOVALIDATE constraints [message #476481] Thu, 23 September 2010 10:05 Go to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
I need to copy some big tables using export/import, I'm tuning the import stage.

As well as all the usual tricks, I would like to change the constraints to rely enabled novalidate, because I know the data is good and using NOVALIDATE should reduce the time it takes for the import to enable the constraints. But the NOVALIDATE clause gets lost during the export import cycle. This is a sample script
drop table t1 purge;
create table t1(c1 number) segment creation immediate;
create index i1 on t1(c1);
alter table t1 add constraint t1pk primary key (c1);
alter table t1 modify constraint t1pk rely enable novalidate;
select constraint_name,validated,status,rely from user_constraints;
host exp jon/jon file=t1.dmp tables=t1
drop table t1;
host imp jon/jon file=t1.dmp tables=t1
select constraint_name,validated,status,rely from user_constraints;

When I run it, you can see that export/import puts the constraint into VALIDATE:
jw> conn jon/jon
Connected.
jw> drop table t1 purge;

Table dropped.

jw> create table t1(c1 number) segment creation immediate;

Table created.

jw> create index i1 on t1(c1);

Index created.

jw> alter table t1 add constraint t1pk primary key (c1);

Table altered.

jw> alter table t1 modify constraint t1pk rely enable novalidate;

Table altered.

jw> select constraint_name,validated,status,rely from user_constraints;

CONSTRAINT_NAME                VALIDATED     STATUS   RELY
------------------------------ ------------- -------- ----
T1PK                           NOT VALIDATED ENABLED  RELY

jw> host exp jon/jon file=t1.dmp tables=t1

Export: Release 11.2.0.1.0 - Production on Thu Sep 23 15:51:27 2010

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                             T1          0 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

jw> drop table t1;

Table dropped.

jw> host imp jon/jon file=t1.dmp tables=t1

Import: Release 11.2.0.1.0 - Production on Thu Sep 23 15:51:28 2010

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing JON's objects into JON
. importing JON's objects into JON
. . importing table                           "T1"          0 rows imported
Import terminated successfully without warnings.

jw> select constraint_name,validated,status,rely from user_constraints;

CONSTRAINT_NAME                VALIDATED     STATUS   RELY
------------------------------ ------------- -------- ----
BIN$dBYtPfOaRLqxfkUIVJUP8w==$0 NOT VALIDATED ENABLED  RELY
T1PK                           VALIDATED     ENABLED  RELY

jw>

And there it is: the dropped constraint was NOT VALIDATED, the imported constraint is VALIDATED. Does anyone have an explanation for this?

Re: export/import and NOVALIDATE constraints [message #476770 is a reply to message #476481] Sat, 25 September 2010 16:46 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Why not use 'datapump' (expdp/impdp)

Check out the 'EXCLUDE=CONSTRAINT,REF_CONSTRAINT' impdp options in the fine Oracle® Database Utilities (3 Data Pump Import) manual.



[Updated on: Sat, 25 September 2010 23:31] by Moderator

Report message to a moderator

Re: export/import and NOVALIDATE constraints [message #476786 is a reply to message #476770] Sun, 26 September 2010 02:09 Go to previous messageGo to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
I can't use data pump because the files are coming from 9i. I tried indexes=n, but that is disastrous because you get system generated indexes for the constraints, which are always unique. Of course I tried indexes=n and constraints=n, then re-creating them from an indexfile. The index file is this:
C:\Users\john\home>more<jon.idx

REM  CREATE TABLE "JON"."T1" ("C1" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS
REM  1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
REM  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
REM  LOGGING NOCOMPRESS ;
REM  ... 0 rows
CONNECT JON;
CREATE UNIQUE INDEX "JON"."T1PK" ON "T1" ("C1" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ;
REM  ALTER TABLE "JON"."T1" ADD CONSTRAINT "T1PK" PRIMARY KEY ("C1") RELY
REM  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536
REM  NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM  DEFAULT) TABLESPACE "USERS" LOGGING ENABLE ;
As you see, I lose not only the NOVALIDATE on the constraint, but also the NONUNIQUE and the NOLOGGING on the index. So the results will be dreadful, unless I put the file through sed to make appropriate edits. Or I suppose I could try to use dbms_metadata to get the DDL out. But I'd have to do that on the 9i system, which I would prefer not to touch.
Thank you for replying.
Re: export/import and NOVALIDATE constraints [message #476958 is a reply to message #476786] Mon, 27 September 2010 08:14 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
John Watson wrote on Sun, 26 September 2010 03:09
... I tried indexes=n, but that is disastrous because you get system generated indexes for the constraints, which are always unique. Of course I tried indexes=n and constraints=n, then re-creating them from an indexfile.
... etc ...
As you see, I lose not only the NOVALIDATE on the constraint, but also the NONUNIQUE and the NOLOGGING on the index. So the results will be dreadful, ...
... etc ...

a) Why would you want a constraint "NOVALIDATE"?

b) If the original constraint was UNIQUE, and you want it NONUNIQUE (you cannot have PK/FK constraints non-unique), then you have to DROP the constraint and create a non-unique index.

c) You can execute a script afterwards to set the indexes NOLOGGING.



[Updated on: Mon, 27 September 2010 08:22] by Moderator

Report message to a moderator

Re: export/import and NOVALIDATE constraints [message #476966 is a reply to message #476958] Mon, 27 September 2010 09:08 Go to previous messageGo to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
Perhaps I didn't explain clearly enough in my first post.

a) Validating constraints takes a very long time if done during an import, and is unnecessary if the constraint was validated in the source database.
b) I always enforce unique and primary key constraints with pre-created non-unique indexes, I thought every DBA did! If you enforce them with unique indexes, they can never be disabled or deferred without losing the index, which is pretty disastrous.
c) To reduce the time taken for the import, the indexes need to be nologging during creation, it is no help to change them afterwards.

So it looks as though the only way to accelerate the indexing and constraint creation phase of an import is not to do it during the import at all, but with scripts afterwards.
icon6.gif  Re: export/import and NOVALIDATE constraints [message #476975 is a reply to message #476966] Mon, 27 September 2010 11:02 Go to previous message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
(a)&(c)
That is why you use the indexes=n and constraints=n options.
If you already have the schema pre-created, then you disable the constraints and drop the indexes before import.

After import you enable the constraints and create the indexes.

(b) I don't understand this point (b) it does not seem logical.


[Updated on: Mon, 27 September 2010 11:20] by Moderator

Report message to a moderator

Previous Topic: data pump import error
Next Topic: export
Goto Forum:
  


Current Time: Mon Dec 23 10:33:45 CST 2024