Oralce 表操作常用SQL
-- Create tablecreate 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;
