Morning All,
I’m using Toad 12.1.0.22 with the DB Admin Module. (I’m finally working on a contract, at a company who uses up to date tools!) [;)] Well, except Oracle on “Windows in the cloud”.
I ran the following script in Toad to create a user, a table, play around with a constraint, and I then used11.2.0.4 exp to export the table. I then browsed the resultant dump file with Toad’s export file browser. I wanted to check that Unique constraints in NOVALIDATE mode are exported and imported in the same mode. Referential Integrity constraints are, primary key constraints are always imported in ENABLE mode.
create user norman identified by norman
default tablespace users;
grant create session, dba to norman;
create table norman.parent(id number(5), stuff varchar2(100));
alter table norman.parent add constraint U_PARENT unique (stuff);
alter table norman.parent modify constraint u_parent novalidate;
I then exported it with the command line:
exp tables=norman.parent file=test.dmp rows=n buffer=1024000
This is what the export browser creates as a DDL script generated from the export file, and what it shows under CONSTRAINTS in the browser itself:
CONNECT NORMAN;
CREATE TABLE “PARENT”
(
“ID” NUMBER(5, 0),
“STUFF” VARCHAR2(100)
)
TABLESPACE “USERS” LOGGING NOCOMPRESS;
CREATE UNIQUE INDEX “U_PARENT”
ON “PARENT”
(
“STUFF”
)
TABLESPACE “USERS” LOGGING;
BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,’“U_PARENT”’,NULL,NULL,NULL,0,0,0,0,0,0,0,6); END;
/
ALTER TABLE “PARENT”
ADD CONSTRAINT “U_PARENT” UNIQUE
(
“STUFF”
) USING INDEX
TABLESPACE “USERS” LOGGING
** ENABLE METRICSTreferential integrity constraints**
METRICET 0
METRICSTtriggers
METRICET 0
METRICSTbitmap, functional and extensible indexes
METRICET 0
METRICSTposttables actions
METRICET 0
**METRICSTPost-inst procedural actions **
METRICET 0
METRICSTDeferred analyze commands;
ANALYZE INDEX “U_PARENT” ESTIMATE STATISTICS;
I’ve highlighted in red the errors. I’ve also trimmed the various warnings and comments that are generated regarding use of the script. I suspect that something internal to parsing the file has lost its place.
I have a copy of the dump file if required.
Cheers,
Norm. [TeamT]
/******************************************************************************* Oracle Export File DDL Extraction File Name: H:\exports\azdba01\test.dmp Server Version: 11.02.00 Export Mode: Table Client Character Set: WE8ISO8859P1 Export User: FCS Export Date: Thu May 19 10:27:7 2016
This script may contain CONNECT statements. If you run this script in SQL*Plus you will need to alter these statements.
Disclaimer: The contents of this SQL script have been produced from an Oracle Export file. Some statements in the export file may not have been syntactically correct and will therefore fail when executed. Some statements may use undocumented Oracle features or package calls and therefore only work with specific versions of Oracle.
Quest Software accepts no liability for any consequences resulting from the use of this script. *******************************************************************************/CONNECT NORMAN;
CREATE TABLE “PARENT” ( “ID” NUMBER(5, 0), “STUFF” VARCHAR2(100)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE “USERS” LOGGING NOCOMPRESS;
CREATE UNIQUE INDEX “U_PARENT” ON “PARENT” ( “STUFF” ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE “USERS” LOGGING;
BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,’“U_PARENT”’,NULL,NULL,NULL,0,0,0,0,0,0,0,6); END;/
ALTER TABLE “PARENT” ADD CONSTRAINT “U_PARENT” UNIQUE ( “STUFF”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE “USERS” LOGGING ENABLE METRICSTreferential integrity constraintsMETRICET 0METRICSTtriggersMETRICET 0METRICSTbitmap, functional and extensible indexesMETRICET 0METRICSTposttables actionsMETRICET 0METRICSTPost-inst procedural actions METRICET 0METRICSTDeferred analyze commands;
ANALYZE INDEX “U_PARENT” ESTIMATE STATISTICS;
/=========================== End of DDL Extracted =============================/
