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;