Forum Stats

  • 3,826,853 Users
  • 2,260,713 Discussions
  • 7,897,102 Comments

Discussions

Export a PK with autocreated index.

user2094682
user2094682 Member Posts: 8
edited Oct 24, 2013 12:28PM in SQL Developer

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.

Answers

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


  • /*******************************************************************************
    *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);

This discussion has been closed.