Skip to Main Content

Why My alter statement doesnt work?

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 I  second time run my query. but some reason I am still getting Alter Table statement...What is the reason?

Error:

ORA-00942: table or view does not exist

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

My query to get Alter statement (First time Run this )

select dbms_metadata_diff.compare_alter('TABLE','STUDENT','STUDENT','USER2','USER1') from dual

statement (First time Get this  and copy it and run below... after running my query again there suppose to no Alter 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
2 comments
118 views