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

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

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

-- Create table

createtable UCR_SHOP.TEST_LINDW

(

  ORG_ID_TEST NUMBER ( 8 ) notnull

)

tablespace TBS_DAT

pctfree 10

initrans 1

maxtrans 255

storage

  (

initial 256 K

next 1 M

minextents 1

maxextentsunlimited

pctincrease 0

  );

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