Oralce 表操作常用SQL

-- Create tablecreate table table_name(  id                   VARCHAR2(50),  create_time          TIMESTAMP(6),  column_name          NUMBER(10,2));--给表table_name 添加列column_name，并设置默认值为0ALTER TABLE userName.table_name ADD column_name VARCHAR2(50) DEFAULT '0';--修改列类型的长度alter table table_name modify column_name number(15,3);--删除列column_nameALTER TABLE userName.table_name drop column column_name--创建表分区partition by range (CREATE_TIME)(  partition PAR_2011_05_1 values less than (TIMESTAMP' 2011-05-11 00:00:00')    tablespace tablespace_name    pctfree 10    initrans 1    maxtrans 255    storage    (      initial 304M      next 1M      minextents 1      maxextents unlimited    ),  partition PAR_2011_05_2 values less than (TIMESTAMP' 2011-05-21 00:00:00')    tablespace tablespace_name    pctfree 10    initrans 1    maxtrans 255    storage    (      initial 3905M      next 1M      minextents 1      maxextents unlimited    ))--创建主分区和子分区partition by range (CREATE_TIME)subpartition by list (NUMBER_CODE)(  partition PAR_2013_07_05 values less than (TIMESTAMP' 2011-07-06 00:00:00')    tablespace tablespace_name    pctfree 10    initrans 1    maxtrans 255  (    subpartition PAR_2013_07_05_SUB_OTHER1 values ('10000') tablespace tablespace_name,    subpartition PAR_2013_07_05_SUB_OTHER2 values ('20000', '30000', '40000', '50000') tablespace tablespace_name,    subpartition PAR_2013_07_05_SUB_OTHER3 values (DEFAULT) tablespace tablespace_name  ),  partition PAR_MAX values less than (MAXVALUE)    tablespace USERS    pctfree 10    initrans 1    maxtrans 255  (    subpartition PAR_MAX_PART_OTHER1 values ('10000') tablespace USERS,    subpartition PAR_MAX_PART_OTHER2 values ('20000', '30000', '40000', '50000') tablespace USERS,    subpartition PAR_MAX_PART_OTHER3 values (DEFAULT) tablespace USERS  ));-- 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 10  initrans 2  maxtrans 255  storage  (    initial 64K    next 1M    minextents 1    maxextents unlimited  );  create index userName.IDX_NAME on userName.table_name (CREATE_TIME DESC, STATUS)  tablespace TS_NAME  pctfree 10  initrans 2  maxtrans 255  storage  (    initial 896K    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;--没有声明dbms_lock包，将DBMS_LOCK包授权给用户：usergrant execute on dbms_lock to user;--撤回授权revoke execute on dbms_lock from user;