Toad World® Forums

I think I've broken the export file browser!

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 =============================/

It appears I cannot copy & paste correctly! What hope for my databases! :frowning:

This is the output from the SHOW=Y run of imp for the dump file in question:

REM CREATE TABLE “NORMAN”.“PARENT” (“ID” NUMBER(5, 0), “STUFF”

REM VARCHAR2(100)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

REM TABLESPACE “USERS” LOGGING NOCOMPRESS ;

CONNECT NORMAN;

CREATE UNIQUE INDEX “NORMAN”.“U_PARENT” ON “PARENT” (“STUFF” ) PCTFREE 10

INITRANS 2 MAXTRANS 255 TABLESPACE “USERS” LOGGING ;

REM ALTER TABLE “NORMAN”.“PARENT” ADD CONSTRAINT “U_PARENT” UNIQUE

REM (“STUFF”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE

REM “USERS” LOGGING ENABLE ;

Which does actually answer the question I was wanting to know, the Unique constraints are not enabled in NOVALIDATE mode. Sigh!

Cheers,

Norm. [TeamT]

Hi Norm,

Send me the file and I’ll see what I can do with it.

Thanks,

-John

Thanks John, what’s the email address please?

Cheers,

Norm. [TeamT]

john.dorlon@quest.com

Thanks very much.

Well, I can get rid of those “junk” statements in the next version of Toad, but even examining the dml file with a hex editor, I don’t see anything in it to differentiate an “enable novalidate” constraint from an “enable validate” constraint.

Morning John,

the problem was only the junk stuff that appeared in the browser and in the generated script which I created for comparison and to be able to paste into the email.

To determine if a constraint is created with VALIDATE or NOVALIDATE, which is why I needed a small dump file, you only have to use Toad’s browser or run a SHOW=YES INDEXFILE=whatever against the dump file.

If the constraint name is known, on windows ‘FIND /i “constraint_name_here” dump_file_name’ and the command Oracle will use will be listed showing the constraint being enabled in whichever mode it is in the dump file.

I have discovered that Primary Key and Unique constraints are enabled VALIDATE all the time, even if they were exported NOVALIDATE. Referential Integrity constraints are enabled according to how they were configured in the exporting database. This sort of makes sense, but in the real world, my data are from production tables where they are already validated so the constraints could be enabled NOVALIDATE and much time could be saved on the import. (Did I mention that some tables are seriously over indexed - only the large tables of course!)

I’m currently working to get a database refresh (in the cloud on Azure on Windows 7) from a Solaris database exp file. It’s takes 89 hours but with various tricks and methods, I’ve got it down to 34 hours so far. More testing is under way and the export file browser has been a godsend! Of course, having to wait for a day or two for test results is a tad frustrating - but it allows me to think up even more weird ways to make things run faster!

Whoever reverse engineered the file format deserves a pint of finest Herefordshire Cider in my opinion! [:)]

Cheers,

Norm. [TeamT]

I have discovered that Primary Key and Unique constraints are enabled VALIDATE all the time, even if they were exported NOVALIDATE.

Ah - Sorry Norm! I missed that! Retesting with a check constraint, I do see NOVALIDATE in the export file!

Here’s the man who deserves that pint:

au.linkedin.com/…/guy-le-mar-1558b62a