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  |
John Watson
Messages: 8931
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 scriptdrop 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 #476786 is a reply to message #476770] |
Sun, 26 September 2010 02:09  |
John Watson
Messages: 8931
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 ;
Thank you for replying.
|
|
|
|
| Re: export/import and NOVALIDATE constraints [message #476958 is a reply to message #476786] |
Mon, 27 September 2010 08:14 |
 |
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 |
John Watson
Messages: 8931
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.
|
|
|
|
 Re: export/import and NOVALIDATE constraints [message #476975 is a reply to message #476966] |
Mon, 27 September 2010 11:02 |
|
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 |
|
|
|
Goto Forum:
Current Time: Wed Apr 24 08:17:14 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
