Oralce表操作常用SQL
Oralce表操作常用SQL
[sql]
-- Create table
create table table_name
(
id VARCHAR2(50),
create_time TIMESTAMP(6),
column_name NUMBER(10,2)
);
--修改列类型的长度
alter table table_name modify column_name number(15,3);
--创建表分区
partition by range (CREATE_TIME)
(
partition PAR_2013_04_01 values less than (TIMESTAMP' 2013-04-01 00:00:00')
tablespace tablespace_name
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition PAR_2013_05_01 values less than (TIMESTAMP' 2013-05-01 00:00:00')
tablespace tablespace_name
pctfree 40
initrans 1
maxtrans 255
);
-- Add comments to the columns 给列添加注释
comment on column table_name.create_time
is '创建时间';
comment on column table_name.column_name
is '列名';
--创建索引
create index IDX_name on table_name (CREATE_TIME)
local;
create INDEX IDX_NAME on table_name (CREATE_TIME, NVL(COLUMN_NAME, 0))
tablespace TS_NAME
pctfree 0
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table table_name
add constraint PK_NAME primary key (ID, CREATE_TIME)
using index
local;
create unique INDEX PK_table_name on table_name (ID, CREATE_TIME)
local;
-- Grant/Revoke object privileges 给用户赋权限
grant select, insert, update, delete on table_name to userName;
