MyException - 我的异常网
当前位置:我的异常网» SQL » Oralce 表操作惯用SQL

Oralce 表操作惯用SQL

www.myexceptions.net  网友分享于:2013-04-10  浏览:1次
Oralce 表操作常用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;

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有