Reverse engineering existing database: Group tables and indices
paulkatich opened this issue · 29 comments
I am currently using the Obevo deploy.bat and newreveng command to reverse engineer existing database. I see lot of issues with indices placed in incorrect sql files. If this is a big database. The refactor can take time. Is there a better way to isolate indices and place them in correct sql files ?
It is possible; we do it for other dbms types, but possibly we haven't considered some edge cases in Oracle.
Can you send a couple table and index examples for me to start with?
Below is an example DDL. As you see The table pulls all the indices from "FP_LEGAL_ENTITIES" even though it is creating DDL for "FP_LEGAL_ENTITY_NAMES ". This is happening for multiple tables.
`//// CHANGE name=change0
CREATE TABLE FP_LEGAL_ENTITY_NAMES
( LEGAL_ENTITY_NAME_ID NUMBER(36,0),
LEGAL_NAME VARCHAR2(80),
CHANGED_DT DATE,
LEGAL_ENTITY_ID NUMBER(36,0),
NAME_TYPE_ID NUMBER(36,0),
END_DT DATE,
COMPANY_NAME VARCHAR2(80),
TITLE VARCHAR2(5),
FIRST_NAME VARCHAR2(20),
MIDDLE_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(30),
SUFFIX VARCHAR2(3),
CREATED_DTM DATE DEFAULT sysdate,
CREATED_BY VARCHAR2(30) DEFAULT user,
CHANGED_DTM DATE DEFAULT sysdate,
CHANGED_BY VARCHAR2(30) DEFAULT user,
CREATED_BY_LE_ID NUMBER(36,0),
CREATED_BY_OPERATING_AS_LE_ID NUMBER(36,0),
CREATED_BY_ORG_UNIT_LE_ID NUMBER(36,0),
CONSTRAINT FP_LENA_PK PRIMARY KEY (LEGAL_ENTITY_NAME_ID)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01 ENABLE,
CONSTRAINT FP_LENA_LENT_FK FOREIGN KEY (LEGAL_ENTITY_ID)
REFERENCES FP_LEGAL_ENTITIES (LEGAL_ENTITY_ID) ENABLE,
CONSTRAINT FP_LENA_NATY_FK FOREIGN KEY (NAME_TYPE_ID)
REFERENCES FP_NAME_TYPES (NAME_TYPE_ID) ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE XCOSD
GO
//// CHANGE name=change1
CREATE OR REPLACE FORCE EDITIONABLE EDITIONING VIEW "ATLANTIS"."FP_LEGAL_ENTITY_NAMES#" ("LEGAL_ENTITY_NAME_ID", "LEGAL_NAME", "CHANGED_DT", "LEGAL_ENTITY_ID", "NAME_TYPE_ID", "END_DT", "COMPANY_NAME", "TITLE", "FIRST_NAME", "MIDDLE_NAME", "LAST_NAME", "SUFFIX", "CREATED_DTM", "CREATED_BY", "CHANGED_DTM", "CHANGED_BY", "CREATED_BY_LE_ID", "CREATED_BY_OPERATING_AS_LE_ID", "CREATED_BY_ORG_UNIT_LE_ID") AS
select LEGAL_ENTITY_NAME_ID LEGAL_ENTITY_NAME_ID, LEGAL_NAME LEGAL_NAME, CHANGED_DT CHANGED_DT, LEGAL_ENTITY_ID LEGAL_ENTITY_ID, NAME_TYPE_ID NAME_TYPE_ID, END_DT END_DT, COMPANY_NAME COMPANY_NAME, TITLE TITLE, FIRST_NAME FIRST_NAME, MIDDLE_NAME MIDDLE_NAME, LAST_NAME LAST_NAME, SUFFIX SUFFIX, CREATED_DTM CREATED_DTM, CREATED_BY CREATED_BY, CHANGED_DTM CHANGED_DTM, CHANGED_BY CHANGED_BY, CREATED_BY_LE_ID CREATED_BY_LE_ID, CREATED_BY_OPERATING_AS_LE_ID CREATED_BY_OPERATING_AS_LE_ID, CREATED_BY_ORG_UNIT_LE_ID CREATED_BY_ORG_UNIT_LE_ID from FP_LEGAL_ENTITY_NAMES
GO
//// CHANGE INDEX name=FP_LENA_PK
CREATE UNIQUE INDEX FP_LENA_PK ON FP_LEGAL_ENTITY_NAMES (LEGAL_ENTITY_NAME_ID)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01
GO
//// CHANGE name=change2
CREATE INDEX "ATLANTIS"."FP_LENT_BTC_LEI_LN_IDX" ON FP_LEGAL_ENTITIES ("BUSINESS_TYPE_CD", "LEGAL_ENTITY_ID", "LEGAL_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "XCOSX_LARGE_01"
GO
//// CHANGE name=change3
CREATE INDEX "ATLANTIS"."FP_LENT_BUS_TYPE_LEID_I" ON FP_LEGAL_ENTITIES ("BUSINESS_TYPE_CD", "LEGAL_ENTITY_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "XCOSX_SMALL_01"
GO
//// CHANGE name=change4
CREATE INDEX "ATLANTIS"."FP_LENT_CHANGED_DTM" ON FP_LEGAL_ENTITIES ("CHANGED_DTM")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "XCOSX_LARGE_01"
GO
//// CHANGE name=change5
CREATE INDEX "ATLANTIS"."FP_LENT_COMPANY_NAME_I" ON FP_LEGAL_ENTITY_NAMES ("COMPANY_NAME", "LEGAL_ENTITY_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "XCOSX_SMALL_01"
GO`
Example 2:
`//// CHANGE name=change0
CREATE TABLE FP_LE_LOOKUP
( LE_LOOKUP_ID NUMBER(36,0),
LE_IDENTIFIER VARCHAR2(80),
LEGAL_ENTITY_ID NUMBER(36,0),
DATA_SOURCE VARCHAR2(10),
DATA_SOURCE_ID NUMBER(36,0),
CREATED_DTM DATE DEFAULT sysdate,
CREATED_BY VARCHAR2(30) DEFAULT user,
CHANGED_DTM DATE DEFAULT sysdate,
CHANGED_BY VARCHAR2(30) DEFAULT user,
CREATED_BY_LE_ID NUMBER(36,0),
CREATED_BY_OPERATING_AS_LE_ID NUMBER(36,0),
CREATED_BY_ORG_UNIT_LE_ID NUMBER(36,0),
CONSTRAINT FP_LELO_PK PRIMARY KEY (LE_LOOKUP_ID)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_LARGE_01 ENABLE,
CONSTRAINT FP_LELO_LENT_FK FOREIGN KEY (LEGAL_ENTITY_ID)
REFERENCES FP_LEGAL_ENTITIES (LEGAL_ENTITY_ID) ON DELETE CASCADE ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE XCOSD
GO
//// CHANGE name=change1
CREATE OR REPLACE FORCE EDITIONABLE EDITIONING VIEW "ATLANTIS"."FP_LE_LOOKUP#" ("LE_LOOKUP_ID", "LE_IDENTIFIER", "LEGAL_ENTITY_ID", "DATA_SOURCE", "DATA_SOURCE_ID", "CREATED_DTM", "CREATED_BY", "CHANGED_DTM", "CHANGED_BY", "CREATED_BY_LE_ID", "CREATED_BY_OPERATING_AS_LE_ID", "CREATED_BY_ORG_UNIT_LE_ID") AS
select LE_LOOKUP_ID LE_LOOKUP_ID, LE_IDENTIFIER LE_IDENTIFIER, LEGAL_ENTITY_ID LEGAL_ENTITY_ID, DATA_SOURCE DATA_SOURCE, DATA_SOURCE_ID DATA_SOURCE_ID, CREATED_DTM CREATED_DTM, CREATED_BY CREATED_BY, CHANGED_DTM CHANGED_DTM, CHANGED_BY CHANGED_BY, CREATED_BY_LE_ID CREATED_BY_LE_ID, CREATED_BY_OPERATING_AS_LE_ID CREATED_BY_OPERATING_AS_LE_ID, CREATED_BY_ORG_UNIT_LE_ID CREATED_BY_ORG_UNIT_LE_ID from FP_LE_LOOKUP
GO
//// CHANGE INDEX name=FP_LELO_PK
CREATE UNIQUE INDEX FP_LELO_PK ON FP_LE_LOOKUP (LE_LOOKUP_ID)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_LARGE_01
GO
//// CHANGE name=change2
CREATE INDEX "ATLANTIS"."FP_LENA_LEGAL_NAME_I" ON FP_LEGAL_ENTITY_NAMES ("LEGAL_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "XCOSX_SMALL_01"
GO
//// CHANGE name=change3
CREATE INDEX "ATLANTIS"."FP_LENA_LENT_FK_I" ON FP_LEGAL_ENTITY_NAMES ("LEGAL_ENTITY_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "XCOSX_SMALL_01"
GO
//// CHANGE name=change4
CREATE INDEX "ATLANTIS"."FP_LENA_NATY_FK_I" ON FP_LEGAL_ENTITY_NAMES ("NAME_TYPE_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "XCOSX_SMALL_01"
GO
`
@shantstepanian let me know if these examples are enough or do you need more ? .
This should suffice, thanks
To confirm the requirements:
- we will still ensure that the indexes end up in the same file as the table they belong to
- we will just be fixing the logic that is assigning indexes to the wrong table files
I also see you have a view in the same file, eg FP_LE_LOOKUP#
That should be in a different file; I suspect the # in the name is throwing off the logic. I'll fix that too
we will still ensure that the indexes end up in the same file as the table they belong to
--Correct.
we will just be fixing the logic that is assigning indexes to the wrong table files
--correct
Fixes committed - you can give this snapshot a try - https://oss.sonatype.org/content/repositories/snapshots/com/goldmansachs/obevo/obevo-cli/master-SNAPSHOT/obevo-cli-master-20190501.001845-17-dist.zip
May be we still need improvements. Below is an example not grouping tables and indices.
//// CHANGE name=change0
CREATE TABLE FP_RUN_NUMBERS
( ID NUMBER(36,0) NOT NULL ENABLE,
RUN_NAME VARCHAR2(25) NOT NULL ENABLE,
RUN_NUM NUMBER(36,0) DEFAULT 1 NOT NULL ENABLE,
COMMENTS VARCHAR2(2000) NOT NULL ENABLE,
CREATED_DTM DATE DEFAULT sysdate,
CREATED_BY VARCHAR2(30) DEFAULT user,
CHANGED_DTM DATE DEFAULT sysdate,
CHANGED_BY VARCHAR2(30) DEFAULT user,
CREATED_BY_LE_ID NUMBER(36,0),
CREATED_BY_OPERATING_AS_LE_ID NUMBER(36,0),
CREATED_BY_ORG_UNIT_LE_ID NUMBER(36,0),
CONSTRAINT FP_RUNR_PK PRIMARY KEY (ID)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01 ENABLE,
CONSTRAINT FP_RUNR_UK UNIQUE (RUN_NAME)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01 ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE XCOSD
GO
//// CHANGE name=change1
CREATE OR REPLACE FORCE EDITIONABLE EDITIONING VIEW "ATLANTIS"."FP_RUN_NUMBERS#" ("ID", "RUN_NAME", "RUN_NUM", "COMMENTS", "CREATED_DTM", "CREATED_BY", "CHANGED_DTM", "CHANGED_BY", "CREATED_BY_LE_ID", "CREATED_BY_OPERATING_AS_LE_ID", "CREATED_BY_ORG_UNIT_LE_ID") AS
select ID ID, RUN_NAME RUN_NAME, RUN_NUM RUN_NUM, COMMENTS COMMENTS, CREATED_DTM CREATED_DTM, CREATED_BY CREATED_BY, CHANGED_DTM CHANGED_DTM, CHANGED_BY CHANGED_BY, CREATED_BY_LE_ID CREATED_BY_LE_ID, CREATED_BY_OPERATING_AS_LE_ID CREATED_BY_OPERATING_AS_LE_ID, CREATED_BY_ORG_UNIT_LE_ID CREATED_BY_ORG_UNIT_LE_ID from FP_RUN_NUMBERS
GO
//// CHANGE INDEX name=FP_RUNR_PK
CREATE UNIQUE INDEX FP_RUNR_PK ON FP_RUN_NUMBERS (ID)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01
GO
//// CHANGE INDEX name=FP_RUNR_UK
CREATE UNIQUE INDEX FP_RUNR_UK ON FP_RUN_NUMBERS (RUN_NAME)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01
GO
//// CHANGE name=change2
CREATE INDEX "ATLANTIS"."FP_SALE_CONT_FK_I" ON FP_SALES ("CONTRACT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "XCOSX_SMALL_01"
GO
//// CHANGE name=change3
CREATE INDEX "ATLANTIS"."FP_SCOA_COAP_FK_I" ON FP_SCORE_ANSWERS ("CONTRACT_APPLICATION_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "XCOSX_SMALL_01"
GO
Per the binary provided in your other ticket on triggers, let me know if you still face issues here
If you enable debug mode, you should see "DEBUG COMMENT" lines appear in your code files like so:
//// CHANGE name=change3
-- DEBUG COMMENT: secondaryName=null; newPatternMatch=true; objectType=FUNCTION; originalObjectName=SP3
CREATE INDEX "ATLANTIS"."FP_SCOA_COAP_FK_I" ON FP_SCORE_ANSWERS ("CONTRACT_APPLICATION_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "XCOSX_SMALL_01"
GO
I'd expect that each index change should have "newPatternMatch=true". If it has it as false, then there is some issue w/ the parsing preventing it from getting picked up (and thus it would just get added to the current object being worked on
If you see issues, then I'd like to see the full file contents where you see 1) an index misplaced in a wrong file 2) the newPatternMatch value being set to false
I added debug flag. But I dont see any debug information. Below is the command.
deploy.bat NEWREVENG -dbType ORACLE -debugLogEnabled
Can you provide the full command (with sensitive information hidden as needed)
I tried the following locally with the latest master-SNAPSHOT and it worked for me
deploy NEWREVENG -mode SCHEMA -inputPath C:\Users\shantstepanian\IdeaProjects\obevo\obevo-db-impls\obevo-db-oracle\src\test\resources\reveng\oracle\input.sql -dbType ORACLE -outputPath C:\Users\shantstepanian\IdeaProjects\obevo\obevo-db-impls\obevo-db-oracle\target\outputReveng -dbSchema DBDEPLOY01 -debugLogEnabled
A note on the DEBUG COMMENT line - it is possible for some changes within a file to not have it, but each file is expected to have at least one such line in it
I find this comment only in view,synonym, trigger folders only. I dont see them in table folder.
deploy NEWREVENG -mode SCHEMA -dbType ORACLE -outputPath "C:\atlantisschema" -dbSchema ATLANTIS -dbHost host -dbPort 1521 -dbServer DEV1 -jdbcUrl jdbc:oracle:thin:@host:1521:DEV1 -username atlantis -password **** -debugLogEnabled
You need to delete the table files (or just delete your whole output folder) before reverse-engineering to have the table files regenerated. (The logic currently overwrites the rerunnable files but not the table files; I can see how the behavior difference can be confusing; I will address this confusion in the future)
Given this symptom, I'd now guess that the index creation should be fine now; I believe that your previous run with my latest patch had not regenerated the files. Nonetheless, use the -debugLogEnabled flag with your latest run just in case
This works great thank you. I no longer see like over 200 issues in the reveng process. Can you please make one more change ? The index name has conflict with table or primary key . May be add index or IDX to index name ? In below example FP_APPT_PK conflicts with table primary FP_APPT_PK .
//// CHANGE name=change0
-- DEBUG COMMENT: secondaryName=null; newPatternMatch=true; objectType=TABLE; originalObjectName=FP_APPLICATION_TYPES
CREATE TABLE FP_APPLICATION_TYPES
( APPLICATION_TYPE_ID NUMBER(36,0) NOT NULL ENABLE,
NAME VARCHAR2(30) NOT NULL ENABLE,
DESCRIPTION VARCHAR2(255),
CREATED_DTM DATE DEFAULT SYSDATE,
CREATED_BY VARCHAR2(30) DEFAULT USER,
CHANGED_DTM DATE DEFAULT SYSDATE,
CHANGED_BY VARCHAR2(30) DEFAULT USER,
CREATED_BY_LE_ID NUMBER(36,0),
CREATED_BY_OPERATING_AS_LE_ID NUMBER(36,0),
CREATED_BY_ORG_UNIT_LE_ID NUMBER(36,0),
CONSTRAINT FP_APPT_PK PRIMARY KEY (APPLICATION_TYPE_ID)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01 ENABLE,
CONSTRAINT FP_APPT_UK1 UNIQUE (NAME)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01 ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE XCOSD
GO//// CHANGE INDEX name=FP_APPT_PK
-- DEBUG COMMENT: secondaryName=FP_APPT_PK; newPatternMatch=true; objectType=TABLE; originalObjectName=FP_APPLICATION_TYPES
CREATE UNIQUE INDEX FP_APPT_PK ON FP_APPLICATION_TYPES (APPLICATION_TYPE_ID)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01
GO
Great to hear about the improvement!
Yes, we should be able to add that, but will need a few days
I've made the change to remove the redundant "CREATE UNIQUE INDEX" statements for primary keys (see commit). You just need to go by the PRIMARY KEY declaration in the "create table" statement
Note that unique indexes that are not primary keys are unaffected; they will still show up in the reverse-engineering
You can try the snapshot from this link:
https://github.com/goldmansachs/obevo/releases/download/master-SNAPSHOT/obevo-cli-master-SNAPSHOT-dist.zip
It is failing on instances like below. Can we avoid these problems ?
`//// CHANGE name=change0
CREATE TABLE DEV2_STAT_FP_CONTRACTS
( STATID VARCHAR2(128),
TYPE CHAR(1),
VERSION NUMBER,
FLAGS NUMBER,
C1 VARCHAR2(128),
C2 VARCHAR2(128),
C3 VARCHAR2(128),
C4 VARCHAR2(128),
C5 VARCHAR2(128),
C6 VARCHAR2(128),
N1 NUMBER,
N2 NUMBER,
N3 NUMBER,
N4 NUMBER,
N5 NUMBER,
N6 NUMBER,
N7 NUMBER,
N8 NUMBER,
N9 NUMBER,
N10 NUMBER,
N11 NUMBER,
N12 NUMBER,
N13 NUMBER,
D1 DATE,
T1 TIMESTAMP (6) WITH TIME ZONE,
R1 RAW(1000),
R2 RAW(1000),
R3 RAW(1000),
CH1 VARCHAR2(1000),
CL1 CLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE XCOSD
LOB (CL1) STORE AS BASICFILE (
TABLESPACE XCOSD ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING )
GO
//// CHANGE name=change1
CREATE TABLE DEV2_STAT_FP_CONTRACTS
( STATID VARCHAR2(128),
TYPE CHAR(1),
VERSION NUMBER,
FLAGS NUMBER,
C1 VARCHAR2(128),
C2 VARCHAR2(128),
C3 VARCHAR2(128),
C4 VARCHAR2(128),
C5 VARCHAR2(128),
C6 VARCHAR2(128),
N1 NUMBER,
N2 NUMBER,
N3 NUMBER,
N4 NUMBER,
N5 NUMBER,
N6 NUMBER,
N7 NUMBER,
N8 NUMBER,
N9 NUMBER,
N10 NUMBER,
N11 NUMBER,
N12 NUMBER,
N13 NUMBER,
D1 DATE,
T1 TIMESTAMP (6) WITH TIME ZONE,
R1 RAW(1000),
R2 RAW(1000),
R3 RAW(1000),
CH1 VARCHAR2(1000),
CL1 CLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE XCOSD
LOB (CL1) STORE AS BASICFILE (
TABLESPACE XCOSD ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING )
GO
//// CHANGE INDEX name=DEV2_STAT_FP_CONTRACTS
CREATE INDEX DEV2_STAT_FP_CONTRACTS ON DEV2_STAT_FP_CONTRACTS (STATID, TYPE, C5, C1, C2, C3, C4, VERSION)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSD
GO
CREATE INDEX DEV2_STAT_FP_CONTRACTS ON DEV2_STAT_FP_CONTRACTS (STATID, TYPE, C5, C1, C2, C3, C4, VERSION)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSD
GO
`
So here is the problem. Looks like oracle creates index for a primary automatically. We cant have create index for primary keys.
So indexes are automatically created by Oracle for such cases:
APC: For primary key and unique key unless such indexes already exist.
APC: For LOB storage and XMLType.
Gary: For table with a nested table.
Jim Hudson: For materialized view.
https://stackoverflow.com/questions/2154091/in-which-cases-will-oracle-create-indexes-automatically
Your example doesn't look like a case of a primary key problem. But it seems like the reverse engineering is duplicating the sqls in this file
I suspect it is related to my fix, so I'll investigate
Below is an example for unique key. Can you please fix this ? Looks like the index is being created for unique key. Can you please work on this issue first ? we see lot of sql files failing because of below unique problem.
com.gs.obevo.api.platform.DeployerRuntimeException: Could not execute DDL:
for artifact [[[Object [FP_ANNOUNCEMENT_TYPES]; ChangeName [FP_ATYP_UK]; Type [TABLE]; LogicalSchema [ATLANTIS]]]] while executing SQL: [[[
CREATE UNIQUE INDEX FP_ATYP_UK ON FP_ANNOUNCEMENT_TYPES (DESCRIPTION)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01
]]]
at com.gs.obevo.db.impl.core.changetypes.DbSimpleArtifactDeployer.deployArtifact(DbSimpleArtifactDeployer.java:57)
at com.gs.obevo.db.impl.core.changetypes.AbstractDbChangeTypeBehavior$1.value(AbstractDbChangeTypeBehavior.java:66)
at com.gs.obevo.db.impl.core.changetypes.AbstractDbChangeTypeBehavior$1.value(AbstractDbChangeTypeBehavior.java:63)
at com.gs.obevo.db.impl.platforms.AbstractSqlExecutor.executeWithinContext(AbstractSqlExecutor.java:62)
at com.gs.obevo.db.impl.core.changetypes.AbstractDbChangeTypeBehavior.deploy(AbstractDbChangeTypeBehavior.java:63)
at com.gs.obevo.impl.ChangeTypeBehaviorRegistry.deploy(ChangeTypeBehaviorRegistry.java:66)
at com.gs.obevo.impl.command.DeployChangeCommand.execute(DeployChangeCommand.java:35)
at com.gs.obevo.impl.ExecuteDeployStrategy.deploy(ExecuteDeployStrategy.java:36)
at com.gs.obevo.impl.MainDeployer.doExecute(MainDeployer.kt:451)
at com.gs.obevo.impl.MainDeployer.executeInternal(MainDeployer.kt:298)
at com.gs.obevo.impl.MainDeployer.execute(MainDeployer.kt:93)
at com.gs.obevo.impl.context.AbstractDeployerAppContext.deploy(AbstractDeployerAppContext.java:139)
at com.gs.obevo.db.cmdline.DbDeployerMain.start(DbDeployerMain.java:79)
at com.gs.obevo.cmdline.AbstractMain.start(AbstractMain.java:129)
at com.gs.obevo.dist.Main$5.value(Main.java:185)
at com.gs.obevo.dist.Main$5.value(Main.java:182)
at com.gs.obevo.dist.Main.execute(Main.java:119)
at com.gs.obevo.dist.Main.execute(Main.java:87)
at com.gs.obevo.dist.Main.main(Main.java:69)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1707)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1670)
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:310)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at com.gs.obevo.db.impl.core.jdbc.JdbcHelper.updateInternal(JdbcHelper.java:95)
at com.gs.obevo.db.impl.core.jdbc.JdbcHelper.update(JdbcHelper.java:70)
at com.gs.obevo.db.impl.core.changetypes.DbSimpleArtifactDeployer.deployArtifact(DbSimpleArtifactDeployer.java:54)
at com.gs.obevo.db.impl.core.changetypes.AbstractDbChangeTypeBehavior$1.value(AbstractDbChangeTypeBehavior.java:66)
at com.gs.obevo.db.impl.core.changetypes.AbstractDbChangeTypeBehavior$1.value(AbstractDbChangeTypeBehavior.java:63)
at com.gs.obevo.db.impl.platforms.AbstractSqlExecutor.executeWithinContext(AbstractSqlExecutor.java:62)
at com.gs.obevo.db.impl.core.changetypes.AbstractDbChangeTypeBehavior.deploy(AbstractDbChangeTypeBehavior.java:63)
at com.gs.obevo.impl.ChangeTypeBehaviorRegistry.deploy(ChangeTypeBehaviorRegistry.java:66)
at com.gs.obevo.impl.command.DeployChangeCommand.execute(DeployChangeCommand.java:35)
at com.gs.obevo.impl.ExecuteDeployStrategy.deploy(ExecuteDeployStrategy.java:36)
at com.gs.obevo.impl.MainDeployer.doExecute(MainDeployer.kt:451)
at com.gs.obevo.impl.MainDeployer.executeInternal(MainDeployer.kt:298)
at com.gs.obevo.impl.MainDeployer.execute(MainDeployer.kt:93)
at com.gs.obevo.impl.context.AbstractDeployerAppContext.deploy(AbstractDeployerAppContext.java:139)
at com.gs.obevo.db.cmdline.DbDeployerMain.start(DbDeployerMain.java:79)
at com.gs.obevo.cmdline.AbstractMain.start(AbstractMain.java:129)
at com.gs.obevo.dist.Main$5.value(Main.java:185)
at com.gs.obevo.dist.Main$5.value(Main.java:182)
at com.gs.obevo.dist.Main.execute(Main.java:119)
at com.gs.obevo.dist.Main.execute(Main.java:87)
at com.gs.obevo.dist.Main.main(Main.java:69)
at com.gs.obevo.db.impl.core.jdbc.JdbcHelper.updateInternal(JdbcHelper.java:109)
at com.gs.obevo.db.impl.core.jdbc.JdbcHelper.update(JdbcHelper.java:70)
at com.gs.obevo.db.impl.core.changetypes.DbSimpleArtifactDeployer.deployArtifact(DbSimpleArtifactDeployer.java:54)
... 18 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1707)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1670)
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:310)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at com.gs.obevo.db.impl.core.jdbc.JdbcHelper.updateInternal(JdbcHelper.java:95)
... 20 more
//// CHANGE name=change0
CREATE TABLE FP_ANNOUNCEMENT_TYPES
( ANNOUNCEMENT_TYPE_ID NUMBER(36,0) NOT NULL ENABLE,
DESCRIPTION VARCHAR2(255) NOT NULL ENABLE,
IS_VALID VARCHAR2(1) DEFAULT 'Y' NOT NULL ENABLE,
CREATED_DTM DATE DEFAULT sysdate,
CREATED_BY VARCHAR2(30) DEFAULT user,
CHANGED_DTM DATE DEFAULT sysdate,
CHANGED_BY VARCHAR2(30) DEFAULT user,
CREATED_BY_LE_ID NUMBER(36,0),
CREATED_BY_OPERATING_AS_LE_ID NUMBER(36,0),
CREATED_BY_ORG_UNIT_LE_ID NUMBER(36,0),
CONSTRAINT FP_ATYP_PK PRIMARY KEY (ANNOUNCEMENT_TYPE_ID)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01 ENABLE,
CONSTRAINT FP_ATYP_UK UNIQUE (DESCRIPTION)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01 ENABLE,
CONSTRAINT AVCON_849485_IS_VA_000 CHECK (IS_VALID IN ('Y', 'N')) ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE XCOSD
GO
//// CHANGE INDEX name=FP_ATYP_UK
CREATE UNIQUE INDEX FP_ATYP_UK ON FP_ANNOUNCEMENT_TYPES (DESCRIPTION)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE XCOSX_SMALL_01
GO
Thanks for the additional use case - will take a look and get back to you by early next week
You can give this a try: https://github.com/goldmansachs/obevo/releases/download/master-SNAPSHOT/obevo-cli-master-SNAPSHOT-dist.zip
FYI, we tried this out against the use cases you mentioned (see TABLE_INDEX_TEST in this commit)
The reveng process is broken. The interim file is blank. We ran it for 2 hours but no output in the interim file. Can you please look in to this ?
What Oracle version are you on?
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Try the link again, just added a fix: https://github.com/goldmansachs/obevo/releases/download/master-SNAPSHOT/obevo-cli-master-SNAPSHOT-dist.zip
If that still doesn't work, if it is possible to execute the following queries and to send the results (they will just return the object names and types), that will be helpful. Or at least the row counts for each
query1.txt
query2.txt
Download link for the binaries (https://github.com/goldmansachs/obevo/releases/download/master-SNAPSHOT/obevo-cli-master-SNAPSHOT-dist.zip) no longer work, I get Page Not Found.
Apologies - it should work now
How is your reverse engineering coming along?
Let me know if you face further issues and if we can close the ticket