Schema Compare - Identity Column

Toad for Oracle
1

Hi

I am trying to move a table from test to prod.

The table contains a identity column defined as COL_PK NUMBER GENERATED BY DEFAULT AS IDENTITY

When I execute a Schema Compare then the resulting script returns MASTER_APP_FACT_DIM_ID NUMBER GENERATED AS IDENTITY. It is missing BY DEFAULT.

Is this normal behavior? Is it possible to have Toad create the script for the table with the full definition?

Br

An

2

What version of Toad are you using? I just tried this in the beta and it worked as expected for me.

I created these tables:

create table comp1.comp_test
(col1 number GENERATED by default AS IDENTITY,
col2 number,
col3 date);

create table comp2.comp_test
(col1 number GENERATED always AS IDENTITY,
col2 number,
col3 date);

and then when I ran the schema compare, this was the sync script:

ALTER TABLE COMP2.COMP_TEST
MODIFY(COL1 GENERATED BY DEFAULT AS IDENTITY ( MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP));

If your set up was different, please provide details so I can reproduce it.

Thanks.

4

What version of Toad are you using?

For me, when the target table does not exist, the sync script creates it with this statement:

CREATE TABLE COMP2.COMP_TEST
(
COL1 NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP),
COL2 NUMBER,
COL3 DATE
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NO INMEMORY
NOCACHE
RESULT_CACHE (MODE DEFAULT)
NOPARALLEL
MONITORING;

6

I tried Toad 12.12, against Oracle 12.2, with storage and other options unchecked. I got this result:

CREATE TABLE COMP2.COMP_TEST
(
COL1 NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP),
COL2 NUMBER,
COL3 DATE
);

Please send me the full DDL for your table. Maybe there is some other factor.

8

I can reproduce this now.

If you uncheck “sequences” in the object types to compare, it will work.

I will have this fixed soon.

9

Fixed for next beta and 13.1. Thank you for reporting this and for your patience while I tried to figure it out!