数据库创建表操作规范注意事项
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