oracle 数据库的建表以及跨schema 依赖关系

GRANT ALL ON ZX_2.TRANSACTION TO ZX_1
GRANT ALL ON ZX_1.DATA_SOURCE TO ZX_2


--------------------------------------------------------
-- DDL for Table USE_CASE
--------------------------------------------------------
CREATE TABLE "ZX_1"."DATA_SOURCE"
( "SOURCE_ID" NUMBER,
"SOURCE_NM" VARCHAR2(16 BYTE)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index DATA_SOURCE_PK
--------------------------------------------------------

CREATE UNIQUE INDEX "ZX_1"."DATA_SOURCE_PK" ON "ZX_1"."DATA_SOURCE" ("SOURCE_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ;
--------------------------------------------------------
-- Constraints for Table DATA_SOURCE
--------------------------------------------------------

ALTER TABLE "ZX_1"."DATA_SOURCE" ADD CONSTRAINT "DATA_SOURCE_PK" PRIMARY KEY ("SOURCE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE;
ALTER TABLE "ZX_1"."DATA_SOURCE" MODIFY ("SOURCE_ID" NOT NULL ENABLE);


--------------------------------------------------------
-- DDL for Table USE_CASE
--------------------------------------------------------


CREATE TABLE "ZX_1"."USE_CASE"
( "USE_CASE_ID" NUMBER,
"NAME" VARCHAR2(16 BYTE)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index USE_CASE_PK
--------------------------------------------------------

CREATE UNIQUE INDEX "ZX_1"."USE_CASE_PK" ON "ZX_1"."USE_CASE" ("USE_CASE_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ;
--------------------------------------------------------
-- Constraints for Table USE_CASE
--------------------------------------------------------

ALTER TABLE "ZX_1"."USE_CASE" ADD CONSTRAINT "USE_CASE_PK" PRIMARY KEY ("USE_CASE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE;
ALTER TABLE "ZX_1"."USE_CASE" MODIFY ("USE_CASE_ID" NOT NULL ENABLE);


--------------------------------------------------------
-- DDL for Table USE_CASE_SCENARIO
--------------------------------------------------------

CREATE TABLE "ZX_1"."USE_CASE_SCENARIO"
( "SCENARIO_ID" NUMBER,
"USE_CASE_ID" NUMBER,
"NAME" VARCHAR2(20 BYTE)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index USE_CASE_SCENARIO_PK
--------------------------------------------------------

CREATE UNIQUE INDEX "ZX_1"."USE_CASE_SCENARIO_PK" ON "ZX_1"."USE_CASE_SCENARIO" ("SCENARIO_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ;
--------------------------------------------------------
-- Constraints for Table USE_CASE_SCENARIO
--------------------------------------------------------

ALTER TABLE "ZX_1"."USE_CASE_SCENARIO" ADD CONSTRAINT "USE_CASE_SCENARIO_PK" PRIMARY KEY ("SCENARIO_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE;
ALTER TABLE "ZX_1"."USE_CASE_SCENARIO" MODIFY ("SCENARIO_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Ref Constraints for Table USE_CASE_SCENARIO
--------------------------------------------------------

ALTER TABLE "ZX_1"."USE_CASE_SCENARIO" ADD CONSTRAINT "USE_CASE_SCENARIO_FK1" FOREIGN KEY ("USE_CASE_ID")
REFERENCES "ZX_1"."USE_CASE" ("USE_CASE_ID") ENABLE;


--------------------------------------------------------
-- DDL for Table TRANSACTION
--------------------------------------------------------

CREATE TABLE "ZX_2"."TRANSACTION"
( "GC_GUID" RAW(20),
"SOURCE_ID" NUMBER(10,0),
"TRANS_AMT" NUMBER(23,6)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index TRANSACTION_PK
--------------------------------------------------------

CREATE UNIQUE INDEX "ZX_2"."TRANSACTION_PK" ON "ZX_2"."TRANSACTION" ("GC_GUID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ;
--------------------------------------------------------
-- Constraints for Table TRANSACTION
--------------------------------------------------------

ALTER TABLE "ZX_2"."TRANSACTION" ADD CONSTRAINT "TRANSACTION_PK" PRIMARY KEY ("GC_GUID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE;
ALTER TABLE "ZX_2"."TRANSACTION" MODIFY ("GC_GUID" NOT NULL ENABLE);
--------------------------------------------------------
-- Ref Constraints for Table TRANSACTION
--------------------------------------------------------

ALTER TABLE "ZX_2"."TRANSACTION" ADD CONSTRAINT "TRANSACTION_FK1" FOREIGN KEY ("SOURCE_ID")
REFERENCES "ZX_1"."DATA_SOURCE" ("SOURCE_ID") ENABLE;


--------------------------------------------------------
-- DDL for Table EXECUTION_TASK
--------------------------------------------------------

CREATE TABLE "ZX_1"."EXECUTION_TASK"
( "TASK_ID" NUMBER,
"UC_TXN_ID" NUMBER,
"SCENARIO_ID" NUMBER,
"SCENARIO_STATUS" VARCHAR2(32 BYTE)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index EXECUTION_TASK_PK
--------------------------------------------------------

CREATE UNIQUE INDEX "ZX_1"."EXECUTION_TASK_PK" ON "ZX_1"."EXECUTION_TASK" ("TASK_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ;
--------------------------------------------------------
-- Constraints for Table EXECUTION_TASK
--------------------------------------------------------

ALTER TABLE "ZX_1"."EXECUTION_TASK" ADD CONSTRAINT "EXECUTION_TASK_PK" PRIMARY KEY ("TASK_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE;
ALTER TABLE "ZX_1"."EXECUTION_TASK" MODIFY ("TASK_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Ref Constraints for Table EXECUTION_TASK
--------------------------------------------------------

ALTER TABLE "ZX_1"."EXECUTION_TASK" ADD CONSTRAINT "EXECUTION_TASK_FK1" FOREIGN KEY ("UC_TXN_ID")
REFERENCES "ZX_1"."USE_CASE_TRANSACTION" ("UC_TXN_ID") ENABLE;


--------------------------------------------------------
-- DDL for Table USE_CASE_TRANSACTION
--------------------------------------------------------

CREATE TABLE "ZX_1"."USE_CASE_TRANSACTION"
( "UC_TXN_ID" NUMBER,
"SCENARIO_ID" NUMBER,
"GC_GUID" RAW(20),
"SOURCE_ID" NUMBER(10,0)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index USE_CASE_TRANSACTION_PK
--------------------------------------------------------

CREATE UNIQUE INDEX "ZX_1"."USE_CASE_TRANSACTION_PK" ON "ZX_1"."USE_CASE_TRANSACTION" ("UC_TXN_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ;
--------------------------------------------------------
-- Constraints for Table USE_CASE_TRANSACTION
--------------------------------------------------------

ALTER TABLE "ZX_1"."USE_CASE_TRANSACTION" ADD CONSTRAINT "USE_CASE_TRANSACTION_PK" PRIMARY KEY ("UC_TXN_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE;
ALTER TABLE "ZX_1"."USE_CASE_TRANSACTION" MODIFY ("UC_TXN_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Ref Constraints for Table USE_CASE_TRANSACTION
--------------------------------------------------------

ALTER TABLE "ZX_1"."USE_CASE_TRANSACTION" ADD CONSTRAINT "USE_CASE_TRANSACTION_FK1" FOREIGN KEY ("SCENARIO_ID")
REFERENCES "ZX_1"."USE_CASE_SCENARIO" ("SCENARIO_ID") ENABLE;
ALTER TABLE "ZX_1"."USE_CASE_TRANSACTION" ADD CONSTRAINT "USE_CASE_TRANSACTION_TRAN_FK1" FOREIGN KEY ("GC_GUID")
REFERENCES "ZX_2"."TRANSACTION" ("GC_GUID") ENABLE;

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


GRANT ALL ON DANNY_2.TRANSACTION TO DANNY_1


ALTER TABLE DANNY_1.USE_CASE_TRANSACTION
ADD CONSTRAINT USE_CASE_TRANSACTION_TRAN_FK1 FOREIGN KEY
(
GC_GUID
)
REFERENCES DANNY_2.TRANSACTION
(
GC_GUID
)
ENABLE

ALTER TABLE DANNY_1.USE_CASE_SCENARIO
ADD CONSTRAINT USE_CASE_SCENARIO_FK1 FOREIGN KEY
(
USE_CASE_ID
)
REFERENCES DANNY_1.USE_CASE
(
USE_CASE_ID
)
ENABLE


ALTER TABLE DANNY_1.EXECUTION_TASK
ADD CONSTRAINT EXECUTION_TASK_FK1 FOREIGN KEY
(
UC_TXN_ID
)
REFERENCES DANNY_1.USE_CASE_TRANSACTION
(
UC_TXN_ID
)
ENABLE

ALTER TABLE DANNY_1.USE_CASE_TRANSACTION
ADD CONSTRAINT USE_CASE_TRANSACTION_FK1 FOREIGN KEY
(
SCENARIO_ID
)
REFERENCES DANNY_1.USE_CASE_SCENARIO
(
SCENARIO_ID
)
ENABLE

GRANT ALL ON DANNY_1.DATA_SOURCE TO DANNY_2

ALTER TABLE DANNY_2.TRANSACTION
ADD CONSTRAINT TRANSACTION_FK1 FOREIGN KEY
(
SOURCE_ID
)
REFERENCES DANNY_1.DATA_SOURCE
(
SOURCE_ID
)
ENABLE