生产环境的DB规范

数据库创建表操作规范注意事项

1、ucr_shop  所有表建这个用户。建表的表空间为TBS_DAT.主键，外键以及索引等使表空间为TBS_IDX。如下建表的例子

-- Create table

createtable UCR_SHOP.TEST_LINDW

(

  ORG_ID_TESTNUMBER(8)notnull

)

tablespace TBS_DAT

pctfree10

initrans1

maxtrans255

storage

  (

initial256K

next1M

minextents1

maxextentsunlimited

pctincrease0

  );

2)需要将新建的表相关权限授予uop_shop用户，参考如下语句：

grantinsert,delete,select,updateon  UCR_SHOP.tets_tablename to uop_shop;

3)创建表对应同义词，参考如下语句:

create public synonym test_tablename for UCR_SHOP.test_tablename;

注意：如果是建表，则第一步、第二步、第三步必须有，建议在建表完成之后就把授权、建同义词的语句放在后面，如以杰哥的建表SQL为例：

-- Create table

create table UCR_SHOP.ECPS_SHUA_COUPONS_GROUP

(

  COUPONS_GROUP_ID          VARCHAR2(32) not null,

  COUPONS_GROUP_NAME        VARCHAR2(256),

  COUPONS_GROUP_STYLE       VARCHAR2(16),

  COUPONS_GROUP_PROBABILITY INTEGER,

  COUPONS_GROUP_DESC        VARCHAR2(1024)

)

tablespace TBS_DAT

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 10M

    next 10M

    minextents 1

    maxextents unlimited

    pctincrease 0

  );

 

-- Add comments to the table

comment on table UCR_SHOP.ECPS_SHUA_COUPONS_GROUP

  is '卡劵大类';

-- Add comments to the columns

comment on column UCR_SHOP.ECPS_SHUA_COUPONS_GROUP.COUPONS_GROUP_ID

  is '卡券大类ID';

comment on column UCR_SHOP.ECPS_SHUA_COUPONS_GROUP.COUPONS_GROUP_NAME

  is '卡券大类名称';

comment on column UCR_SHOP.ECPS_SHUA_COUPONS_GROUP.COUPONS_GROUP_STYLE

  is '卡券展示';

comment on column UCR_SHOP.ECPS_SHUA_COUPONS_GROUP.COUPONS_GROUP_PROBABILITY

  is '中奖概率';

comment on column UCR_SHOP.ECPS_SHUA_COUPONS_GROUP.COUPONS_GROUP_DESC

  is '描述';

-- Create/Recreate primary, unique and foreign key constraints主键

alter table UCR_SHOP.ECPS_SHUA_COUPONS_GROUP

  add constraint PK_ECPS_SHUA_COUPONS_GROUP primary key (COUPONS_GROUP_ID)

  using index

  tablespace TBS_IDX

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 10M

    next 10M

    minextents 1

    maxextents unlimited

    pctincrease 0

  );

 

------权限授予uop_shop用户

grant insert,delete,select,update on UCR_SHOP.ECPS_SHUA_COUPONS_GROUP to uop_shop;

------创建表对应同义词

create public synonym ECPS_SHUA_COUPONS_GROUP for UCR_SHOP.ECPS_SHUA_COUPONS_GROUP;

4)创建序列,用UOP_SHOP用户，参考如下语句:

CREATE SEQUENCE UOP_SHOP.test_tablename

minvalue 1

maxvalue 999999999999999999

start with 1

increment by 1

cache 20;

5)创建索引,用UCR_SHOP用户，参考如下语句:

create INDEX_NAME index UCR_SHOP.test_tablename(PRIZE_VALID_END)

  tablespace TBS_IDX

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 10M

    next 10M

    minextents 1

    maxextents unlimited

    pctincrease 0

  );

6)insertinto、update用ucr_shop用户，参考如下语句:

insert into ucr_shop.test_tablename (PAYCHNNL, INNER_MER,   INTERFACETYPE, OFFLINEFLAG, REF_ID)

values ('WEIXINPAY', '888073157340006', 'app', '1', '4');

 

 

update ucr_shop.test_tablename set ROYALTY = '1' ,PAY_PARTNERID =  '888073157340006';

 

7)添加主键用UCR_SHOP用户，参考如下语句:

alter table UCR_SHOP.test_tablename

  add constraint TF_R_HOTSEARCH_ID primary key (HOTSEARCH_ID)

  using index

  tablespace TBS_IDX

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 10M

    next 10M

    minextents 1

    maxextents unlimited

    pctincrease 0

  );

8)提交的DB变更脚本名称一律采用“需求名+姓名.txt/sql”的形式发送，如：shua-优化周杰.sql

 数据库创建表操作规范注意事项

1、ucr_shop  所有表建这个用户。建表的表空间为TBS_DAT.主键，外键以及索引等使表空间为TBS_IDX。如下建表的例子

-- Create table

createtable UCR_SHOP.TEST_LINDW

(

  ORG_ID_TESTNUMBER(8)notnull

)

tablespace TBS_DAT

pctfree10

initrans1

maxtrans255

storage

  (

initial256K

next1M

minextents1

maxextentsunlimited

pctincrease0

  );

2)需要将新建的表相关权限授予uop_shop用户，参考如下语句：

grantinsert,delete,select,updateon  UCR_SHOP.tets_tablename to uop_shop;

3)创建表对应同义词，参考如下语句:

create public synonym test_tablename for UCR_SHOP.test_tablename;

注意：如果是建表，则第一步、第二步、第三步必须有，建议在建表完成之后就把授权、建同义词的语句放在后面，如以杰哥的建表SQL为例：

-- Create table

create table UCR_SHOP.ECPS_SHUA_COUPONS_GROUP

(

  COUPONS_GROUP_ID          VARCHAR2(32) not null,

  COUPONS_GROUP_NAME        VARCHAR2(256),

  COUPONS_GROUP_STYLE       VARCHAR2(16),

  COUPONS_GROUP_PROBABILITY INTEGER,

  COUPONS_GROUP_DESC        VARCHAR2(1024)

)

tablespace TBS_DAT

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 10M

    next 10M

    minextents 1

    maxextents unlimited

    pctincrease 0

  );

 

-- Add comments to the table

comment on table UCR_SHOP.ECPS_SHUA_COUPONS_GROUP

  is '卡劵大类';

-- Add comments to the columns

comment on column UCR_SHOP.ECPS_SHUA_COUPONS_GROUP.COUPONS_GROUP_ID

  is '卡券大类ID';

comment on column UCR_SHOP.ECPS_SHUA_COUPONS_GROUP.COUPONS_GROUP_NAME

  is '卡券大类名称';

comment on column UCR_SHOP.ECPS_SHUA_COUPONS_GROUP.COUPONS_GROUP_STYLE

  is '卡券展示';

comment on column UCR_SHOP.ECPS_SHUA_COUPONS_GROUP.COUPONS_GROUP_PROBABILITY

  is '中奖概率';

comment on column UCR_SHOP.ECPS_SHUA_COUPONS_GROUP.COUPONS_GROUP_DESC

  is '描述';

-- Create/Recreate primary, unique and foreign key constraints主键

alter table UCR_SHOP.ECPS_SHUA_COUPONS_GROUP

  add constraint PK_ECPS_SHUA_COUPONS_GROUP primary key (COUPONS_GROUP_ID)

  using index

  tablespace TBS_IDX

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 10M

    next 10M

    minextents 1

    maxextents unlimited

    pctincrease 0

  );

 

------权限授予uop_shop用户

grant insert,delete,select,update on UCR_SHOP.ECPS_SHUA_COUPONS_GROUP to uop_shop;

------创建表对应同义词

create public synonym ECPS_SHUA_COUPONS_GROUP for UCR_SHOP.ECPS_SHUA_COUPONS_GROUP;

4)创建序列,用UOP_SHOP用户，参考如下语句:

CREATE SEQUENCE UOP_SHOP.test_tablename

minvalue 1

maxvalue 999999999999999999

start with 1

increment by 1

cache 20;

5)创建索引,用UCR_SHOP用户，参考如下语句:

create INDEX_NAME index UCR_SHOP.test_tablename(PRIZE_VALID_END)

  tablespace TBS_IDX

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 10M

    next 10M

    minextents 1

    maxextents unlimited

    pctincrease 0

  );

6)insertinto、update用ucr_shop用户，参考如下语句:

insert into ucr_shop.test_tablename (PAYCHNNL, INNER_MER,   INTERFACETYPE, OFFLINEFLAG, REF_ID)

values ('WEIXINPAY', '888073157340006', 'app', '1', '4');

 

 

update ucr_shop.test_tablename set ROYALTY = '1' ,PAY_PARTNERID =  '888073157340006';

 

7)添加主键用UCR_SHOP用户，参考如下语句:

alter table UCR_SHOP.test_tablename

  add constraint TF_R_HOTSEARCH_ID primary key (HOTSEARCH_ID)

  using index

  tablespace TBS_IDX

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 10M

    next 10M

    minextents 1

    maxextents unlimited

    pctincrease 0

  );

8)提交的DB变更脚本名称一律采用“需求名+姓名.txt/sql”的形式发送，如：shua-优化周杰.sql

 

转载于:https://www.cnblogs.com/kekesang/p/7827351.html