Comparing live schema with schema snapshot

Toad for Oracle & DevOps Toolkit
1

Hi John,

Running Exadata Edition with the DB Admin Module (64-bit): 26.1.193.8012

We have a new ongoing requirement to compare schemas from 2 databases that we cannot connect to simultaneously. Have discovered that this is tedious at best.

  1. Tables and constraints amongst other objects have been selected from both the export creation and the schema compare. However, the NOT NULL table constraints are flagged as diffs, despite the flagged columns being NOT NULL in both schemas. Did I miss something?
  2. Is there an easy way to save settings and later reload these settings for the export and comparison to ensure that we are performing this comparison consistently each time?

Regards,
Doug

2

Hi Doug,

  1. Maybe this is a Toad bug. Can you give me some steps to reproduce the not null false diff?

  2. Yes, automation designer does exactly this. Set up your schema compares from there. It will remember settings.

Having said that.....Are you using snapshot files to compare the schemas? I think this is what you mean by "Export", but I just want to clarify. The steps would be

  1. Use Generate Schema Script to create snapshot files of one or both schemas
  2. Use Schema Compare to compare the snapshot files
3

Using Snapshot for the remote schema and live connection for the local schema.

Snapshot config:

image001.png1088×562 45.5 KB

Compare config:

image002.png1133×437 34.3 KB

BTW, all constraints were selected:

image003.png130×101 1.95 KB

4

I am continuing to troubleshoot. You will be pleased to know that comparing the live schema from the same database where I took the snapshot shows 0 diffs.

5

Yeah that's one of the things we test here. I'll try to reproduce the not null problem tomorrow. It probably won't be too difficult to fix once I can reproduce it.

6

Drilling down on one of the diffs… Left side is the live database and right side is from the snapshot file:

image
image1472×162 29 KB

7

Hi Doug,

I created an empty schema in 2 databases, then ran the script below to create a few tables with not null constraints created in various ways (part of a PK, not part of a PK, named, not named, etc).

Then I made a snapshot file of one schema, and used that as a source to compare to the other schema (actually, I tried it as both source and target)

Generate Schema Script didn't find any differences.

Can you give me a "create table" script that I can put in each schema to reproduce the problem?

Thanks

John

CREATE TABLE A_NN_TEST
(
  PK_COL                       NUMBER,
  NN_COL1                      NUMBER           NOT NULL,
  NN_COL2                      NUMBER CONSTRAINT A_NN_TEST_NN_COL2 NOT NULL,
  NN_MULTICOL_CONSTRAINT_COL1  NUMBER,
  NN_MULTICOL_CONSTRAINT_COL2  NUMBER,
  COL4                         NUMBER,
  A_XML                        SYS.XMLTYPE      NOT NULL,
  A_XML2                       SYS.XMLTYPE,
  FAKE_NN_COL                  NUMBER,
  FAKE_NN_COL2                 NUMBER
)
LOGGING 
NOCOMPRESS 
NOCACHE;


CREATE UNIQUE INDEX A_NN_TEST_PK ON A_NN_TEST
(PK_COL)
LOGGING;

ALTER TABLE A_NN_TEST ADD (
  CONSTRAINT A_NN_TEST_CHK
  CHECK (NN_MULTICOL_CONSTRAINT_COL1 IS NOT NULL)
  ENABLE VALIDATE
,  CONSTRAINT A_NN_TEST_FAKE_NN
  CHECK ("FAKE_NN_COL" IS NOT NULL)
  ENABLE VALIDATE
,  CONSTRAINT A_NN_TEST_MULTICOL_CONSTRAINT
  CHECK (NN_MULTICOL_CONSTRAINT_COL1 IS NOT NULL AND NN_MULTICOL_CONSTRAINT_COL2 IS NOT NULL)
  ENABLE VALIDATE
,  CONSTRAINT A_NN_TEST_NO_COL_CHK
  CHECK (3 > 2)
  ENABLE VALIDATE
,  CHECK ("FAKE_NN_COL2" IS NOT NULL)
  ENABLE VALIDATE
,  CHECK (5 > 4)
  ENABLE VALIDATE
,  CONSTRAINT A_NN_TEST_PK
  PRIMARY KEY
  (PK_COL)
  USING INDEX A_NN_TEST_PK
  ENABLE VALIDATE);


CREATE TABLE TEST_TBL1
(
  COL1  NUMBER,
  COL2  NUMBER,
  COL3  NUMBER                                  NOT NULL,
  COL4  NUMBER                                  NOT NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE;


CREATE TABLE TEST_TBL2
(
  COL1  NUMBER,
  COL2  NUMBER
)
LOGGING 
NOCOMPRESS 
NOCACHE;


ALTER TABLE TEST_TBL2 ADD (
  PRIMARY KEY
  (COL1)
  ENABLE VALIDATE);


CREATE TABLE TEST_TBL3
(
  COL1  NUMBER                                  NOT NULL,
  COL2  NUMBER
)
LOGGING 
NOCOMPRESS 
NOCACHE;


ALTER TABLE TEST_TBL3 ADD (
  PRIMARY KEY
  (COL1)
  ENABLE VALIDATE);