Export a PK with autocreated index.

user2094682

Im using SQLDeveloper  Versión 3.2.20.09

Is there a way of generate an export DDL script to preserve the original authomatic created index of a primary key?. When I generate the export DDL script it always generates explicit indexes no matter if they were originally created implicity or explicity.

My problem is related whit this migration issue https://forums.oracle.com/message/1223619#1223619 but in my case I'm using the script on a different instance with the same versión of Oracle (10.2g). I know that using explicit DROP INDEX option on ALTER DROP statement resolves the problem, but I thought tha withthe export facility, I'd have exactly the same DB on both servers.

Thanks in advance.

Unknown

Please post a specific, simple example of the DDL you are getting and the DDL you want to get.

user2094682

/*******************************************************************************
*CASE A
*******************************************************************************/
--A.1 Manual Script
CREATE TABLE TAB_A(
pk number(*,0),
x number,
y varchar(20),
constraint TAB_A_PK primary key (pk)
);
--A.2 Export Script
--------------------------------------------------------
--  DDL for Table TAB_A
--------------------------------------------------------

  CREATE TABLE "TAB_A"
   ( "PK" NUMBER(*,0),
"X" NUMBER,
"Y" VARCHAR2(20)
   ) ;
/  
--------------------------------------------------------
--  DDL for Index TAB_A_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "TAB_A_PK" ON "TAB_A" ("PK")
  ;
/ 
--------------------------------------------------------
--  Constraints for Table TAB_A
--------------------------------------------------------

  ALTER TABLE "TAB_A" ADD CONSTRAINT "TAB_A_PK" PRIMARY KEY ("PK") ENABLE;
/

/*******************************************************************************
* CASE B
*******************************************************************************/
--B.1 Manual Script
CREATE TABLE TAB_B(
pk number(*,0),
x number,
y varchar(20)
);

ALTER TABLE TAB_B ADD constraint TAB_B_PK primary key (pk);
--B.2 Export Script
--------------------------------------------------------
--  DDL for Table TAB_B
--------------------------------------------------------

  CREATE TABLE "TAB_B"
   ( "PK" NUMBER(*,0),
"X" NUMBER,
"Y" VARCHAR2(20)
   );
/  
--------------------------------------------------------
--  DDL for Index TAB_B_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "TAB_B_PK" ON "TAB_B" ("PK");
/ 
--------------------------------------------------------
--  Constraints for Table TAB_B
--------------------------------------------------------

  ALTER TABLE "TAB_B" ADD CONSTRAINT "TAB_B_PK" PRIMARY KEY ("PK") ENABLE;
/

/*******************************************************************************
* CASE C
********************************************************************************/
--C.1 Manual Script
CREATE TABLE TAB_C(
pk number(*,0),
x number,
y varchar(20)
);

CREATE UNIQUE INDEX TAB_C_PK ON TAB_C(pk);
ALTER TABLE TAB_C ADD constraint TAB_C_PK primary key (pk) USING INDEX TAB_C_PK;
--C.2 Export Script
--------------------------------------------------------
--  DDL for Table TAB_C
--------------------------------------------------------

  CREATE TABLE "TAB_C"
   ( "PK" NUMBER(*,0),
"X" NUMBER,
"Y" VARCHAR2(20)
   ) ;
/
--------------------------------------------------------
--  DDL for Index TAB_C_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "TAB_C_PK" ON "TAB_C" ("PK")
  ;
/
--------------------------------------------------------
--  Constraints for Table TAB_C
--------------------------------------------------------

  ALTER TABLE "TAB_C" ADD CONSTRAINT "TAB_C_PK" PRIMARY KEY ("PK") ENABLE;
/

/*******************************************************************************
  * THE PROBLEM: When I execute an alter script to change the pk on tables that

* were created with the manual version, I only get the error "ORA-02264: name already used by an existing..."

* on TAB_C, but if I execute the alter script over tables created with the exported

* version, then I get the same error but on all tables.

* Using DROP INDEX option works as expected in the three tables on both versions

* of script, but I think SQLDeveloper should generate the a better script cause

* Oracle decides deleting an index depending if it was previously created or not.

*******************************************************************************/
ALTER TABLE TAB_A DROP PRIMARY KEY;
ALTER TABLE TAB_A ADD CONSTRAINT TAB_A_PK PRIMARY KEY(pk,x,y);

ALTER TABLE TAB_B DROP PRIMARY KEY;
ALTER TABLE TAB_B ADD CONSTRAINT TAB_B_PK PRIMARY KEY(pk,x,y);

ALTER TABLE TAB_C DROP PRIMARY KEY;
ALTER TABLE TAB_C ADD CONSTRAINT TAB_B_PK PRIMARY KEY(pk,x,y);