标准建表语句(建表、注释、索引、授权、同义词)

-- Create table
create table DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION
(
  CREATED_BY                VARCHAR2(100) not null,--创建人
  CREATED_DATE              DATE not null,--创建时间
  UPDATED_BY                VARCHAR2(100) not null,--修改人
  UPDATED_DATE              DATE not null,--修改时间
  ID_SYSTEMNAME_DOCUMENT_RELATION  VARCHAR2(32) default RAWTOHEX(SYS_GUID()) not null,--主键
  REPORT_NO                 VARCHAR2(20),--报案号
  POLICY_NO                 VARCHAR2(20),--保单号
  CASE_NO                   VARCHAR2(20),--赔案号
  CASE_TIMES                NUMBER(2),--赔案次数
  RELATION_ID               VARCHAR2(32),--关联主键
  DOC_TYPE                  VARCHAR2(50),--任务类型
  DOCUMENT_ID               VARCHAR2(20)--关联附件号
)
MONITORING;

-- Add comments to the table 
comment on table DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION is '附件关联表';
-- Add comments to the columns 
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.CREATED_BY
  is '创建人员';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.CREATED_DATE
  is '创建日期';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.UPDATED_BY
  is '修改人员';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.UPDATED_DATE
  is '修改时间';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.ID_SYSTEMNAME_DOCUMENT_RELATION
  is '附件关联表pk，根据公司新的数据库规范自动生成';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.REPORT_NO
  is '报案号';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.POLICY_NO
  is '保单号';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.CASE_NO
  is '赔案号';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.CASE_TIMES
  is '赔案次数';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.RELATION_ID
  is '关联主键';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.DOC_TYPE
  is '任务类型（1、重大赔案）';
comment on column DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION.DOCUMENT_ID
  is '关联附件号';
  
-- Create/Recreate indexes 
create unique index DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION_ID on DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION (ID_SYSTEMNAME_DOCUMENT_RELATION) initrans 16;

create index DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION_RNO on DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION (REPORT_NO) initrans 16;

create index DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION_PNO on DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION (POLICY_NO) initrans 16;

create index DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION_CASE_NO on DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION (CASE_NO,CASE_TIMES) initrans 16;

create index DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION_UPD on DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION (UPDATED_DATE) initrans 16;

-- Create/Recreate primary, unique and foreign key constraints 
alter table DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION
  add constraint PK_SYSTEMNAME_DOCUMENT_RELATION primary key (ID_SYSTEMNAME_DOCUMENT_RELATION) USING INDEX DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION_ID;

-- Grant/Revoke object privileges 
grant select, insert, update, delete on DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION to APPLICATIONNAMEOPR;
grant select, insert, update, delete on DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION to R_APPLICATIONNAMEDATA_DML;
grant select on DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION to R_APPLICATIONNAMEDATA_QRY,R_APPLICATIONNAMEDATA_DEV_QRY;

---synonym
create public synonym SYSTEMNAME_DOCUMENT_RELATION for DATAOWNBER.SYSTEMNAME_DOCUMENT_RELATION;

先建索引，后建主键