Skip to Main Content

Why I am getting Alter table statement and after copy and run the alter table statement inside my US

Erhan_torontoSep 4 2013 — edited Sep 4 2013

Oracle 11gR2

-------------------------------------------

When I run below Query.... I am getting Alter table statement and after copy and run the alter table statement inside my USER2... I supposed to get nothing when second time run the below query. but some reason I am still getting Alter Table statement...What is the reason?

select REGEXP_REPLACE(dbms_metadata_diff.compare_alter('TABLE','STUDENT','STUDENT','USER2','USER1'),('USER2...'),'', 1, 0, 'i') from dual

statement

  ALTER TABLE "STUDENT" DROP CONSTRAINT "STU_ZIP_FK"

  ALTER TABLE "STUDENT" ADD CONSTRAINT "STU_ZIP_FK" FOREIGN KEY ("ZIP") REFERENCES "USER1"."ZIPCODE"("ZIP") ENABLE

User1 _student Table

CREATE TABLE "USER1"."STUDENT"

( "STUDENT_ID" NUMBER(8,0) CONSTRAINT "STU_STUDENT_ID_NNULL" NOT NULL ENABLE,

"SALUTATION" VARCHAR2(5),

"FIRST_NAME" VARCHAR2(25),

"LAST_NAME" VARCHAR2(25) CONSTRAINT "STU_LAST_NAME_NNULL" NOT NULL ENABLE,

"STREET_ADDRESS" VARCHAR2(50),

"ZIP" VARCHAR2(5) CONSTRAINT "STU_ZIP_NNULL" NOT NULL ENABLE,

"PHONE" VARCHAR2(15),

"EMPLOYER" VARCHAR2(50),

"REGISTRATION_DATE" DATE CONSTRAINT "STU_REGISTRATION_DATE_NNULL" NOT NULL ENABLE,

"CREATED_BY" VARCHAR2(30) CONSTRAINT "STU_CREATED_BY_NNULL" NOT NULL ENABLE,

"CREATED_DATE" DATE CONSTRAINT "STU_CREATED_DATE_NNULL" NOT NULL ENABLE,

"MODIFIED_BY" VARCHAR2(30) CONSTRAINT "STU_MODIFIED_BY_NNULL" NOT NULL ENABLE,

"MODIFIED_DATE" DATE CONSTRAINT "STU_MODIFIED_DATE_NNULL" NOT NULL ENABLE,

CONSTRAINT "STU_PK" PRIMARY KEY ("STUDENT_ID")

User2_student Table

CREATE TABLE "USER2"."STUDENT"

( "STUDENT_ID" NUMBER(8,0) CONSTRAINT "STU_STUDENT_ID_NNULL" NOT NULL ENABLE,

"SALUTATION" VARCHAR2(5),

"FIRST_NAME" VARCHAR2(25),

"LAST_NAME" VARCHAR2(25) CONSTRAINT "STU_LAST_NAME_NNULL" NOT NULL ENABLE,

"STREET_ADDRESS" VARCHAR2(50),

"ZIP" VARCHAR2(5) CONSTRAINT "STU_ZIP_NNULL" NOT NULL ENABLE,

"PHONE" VARCHAR2(15),

"EMPLOYER" VARCHAR2(50),

"REGISTRATION_DATE" DATE CONSTRAINT "STU_REGISTRATION_DATE_NNULL" NOT NULL ENABLE,

"CREATED_BY" VARCHAR2(30) CONSTRAINT "STU_CREATED_BY_NNULL" NOT NULL ENABLE,

"CREATED_DATE" DATE CONSTRAINT "STU_CREATED_DATE_NNULL" NOT NULL ENABLE,

"MODIFIED_BY" VARCHAR2(30) CONSTRAINT "STU_MODIFIED_BY_NNULL" NOT NULL ENABLE,

"MODIFIED_DATE" DATE CONSTRAINT "STU_MODIFIED_DATE_NNULL" NOT NULL ENABLE,

CONSTRAINT "STU_PK" PRIMARY KEY ("STUDENT_ID")

Comments
Post Details
Added on Sep 4 2013
7 comments
237 views