Howdy!
In 13.2.0.258, a script generated for a table that has a PK with a different name than its supporting index does not generate the index with the correct name. In my case, it's important as the ERP system (JDEdwards) that creates all database objects refers to them by specific names. I can supply a case in a few hours, but wanted to get this out here before I forgot...
Thanks!
Rich
Hi Rich,
I just made this table and index and it came out OK. Let me know what you did differently.
CREATE TABLE EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
CREATE UNIQUE INDEX IDX_EMP ON EMP
(EMPNO);
ALTER TABLE EMP ADD (
CONSTRAINT PK_EMP
PRIMARY KEY
(EMPNO)
USING INDEX IDX_EMP
ENABLE VALIDATE);
-John
Hey John,
Here's my table, index, and PK:
CREATE TABLE MY_TABLE
(
MY_COL NUMBER,
MY_DATA NCHAR(2)
)
TABLESPACE USERS;
CREATE UNIQUE INDEX MY_INDEX ON MY_TABLE
(MY_COL)
TABLESPACE USERS;
ALTER TABLE MY_TABLE ADD (
CONSTRAINT MY_PK
PRIMARY KEY
(MY_COL)
USING INDEX MY_INDEX
ENABLE VALIDATE);
And here's the result I get from the RHS script tab in the SB:
CREATE TABLE MY_TABLE
(
MY_COL NUMBER,
MY_DATA NCHAR(2)
)
TABLESPACE USERS
NOCACHE
RESULT_CACHE (MODE DEFAULT)
/
-- There is no statement for index MY_INDEX.
-- The object is created when the parent object is created.
ALTER TABLE MY_TABLE ADD (
CONSTRAINT MY_PK
PRIMARY KEY
(MY_COL)
USING INDEX
TABLESPACE USERS
ENABLE VALIDATE)
/
-- There is no statement for index MY_INDEX.
-- The object is created when the parent object is created.
In this case, the index needs to be named "MY_INDEX", but the script will create one called "MY_PK" instead.
Thanks!
Rich
huh, weird, that's basically the same that I did but with a different result. I wonder if there is something about DB that's doing something different in data dictionary.
What's the DB version?
12.1.0.2 The XML behind DBMS_METADATA did change between 11.2 and 12.1. There's a bug 20217704 to fix at least one of the anomalies, but I decided not to install it...
Thanks!
Rich
Hi Rich,
Sorry for the delay.
From SB RHS Script Tab, go to the script options dialog, then "Tables" tab. Uncheck "No constraint indexes"
That ought to get it.
Doh! I saw that and had changed it, but it made no difference. I didn't record my steps, but after taking a look at it again, I think a refresh of the RHS may have been needed post-change.
Thanks much, John! 
Rich
