`
dannyhz
  • 浏览: 408375 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
文章分类
社区版块
存档分类
最新评论

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

oracle 
阅读更多
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
分享到：
| jvm 内存模型
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    nacos 适配 oracle 11g版本 ，附带oracle 脚本，亲测可用

    Nacos是一款由阿里巴巴开源的分布式服务治理和配置中心，...请记住，对于不同版本的Oracle数据库，可能需要更新JDBC驱动版本以及适应性调整。同时，关注Nacos的官方文档和社区更新，以获取最新的适配指南和兼容性信息。

    Oracle轻松取得建表和索引的DDL语句

    Oracle数据库管理系统为用户提供了方便的方式来获取创建表和索引的DDL（Data Definition Language）语句。在Oracle 9i及更高版本中，可以利用DBMS_METADATA包来直接从数据字典中提取这些语句，这大大简化了过去通过...

    toad for oracle

    Toad for Oracle是一款针对Oracle数据库的专业软件工具，由Quest软件公司开发。该软件旨在加速数据库设计、开发、测试和管理过程，提升开发人员和数据库管理员的工作效率和系统性能。 首先，Toad for Oracle包含了...

    Oracle9i取得建表和索引的DDL语句

    Oracle数据库管理系统是世界上最广泛使用的数据库系统之一，尤其在企业级应用中占据主导地位。在Oracle 9i版本之前，获取数据库对象（如表和索引）的创建（DDL）语句是一项复杂的工作，通常需要通过exp导出或者手动...

    Toad使用方法（TOAD of ORACEL）

    Toad for Oracle是一款强大的Oracle数据库管理工具，它提供了丰富的功能，便于DBA和开发人员进行数据库管理和开发工作。以下是对标题和描述中所提及的知识点的详细解释： 1. **Schema Browser**： - Schema ...

    Oracle 9i轻松取得建表和索引的DDL语句

    Oracle 9i数据库管理系统在获取建表和索引的DDL（Data Definition Language）语句方面提供了极大的便利。在Oracle 9i之前，如果想要获取这些语句，通常需要借助于导出（Export）工具，但这种方法生成的输出由于格式...

    2009达内SQL学习笔记

    保存在oracle数据库中的所有操作细节： spool oracleday01.txt ：开始记录 spool off ：开始保存细节 四、SELECT语句：选择操作、投影操作。 select：从一个或多个表中检索一个或多个数据列。包含信息：想选择...

Magicbox
Global site tag (gtag.js) - Google Analytics