資料庫維護常用操作命令1-表操作

 

-- Create table （分割槽表）

create table T_ORD_ORDER

(

  order_id                  VARCHAR2(100) not null,

  order_no                  VARCHAR2(100)

)

partition by range (MESSAGE_TIME)

(

partition PAR_2014_11_08 values less than (TIMESTAMP' 2014-11-09 00:00:00')

    tablespace TS_YTORD

    pctfree 10

    initrans 1

    maxtrans 255

    storage

    (

      initial 64K

      next 1M

      minextents 1

      maxextents unlimited

    ),

 ----最大分割槽 可以進行拆分

  partition P_MAX values less than (MAXVALUE)

    tablespace TS_YTORD

    pctfree 10

    initrans 1

    maxtrans 255

    storage

    (

      initial 64K

      next 1M

      minextents 1

      maxextents unlimited

    )

);

-----所在表空間

tablespace TS_YTORD

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 64K

    next 8K

    minextents 1

    maxextents unlimited

  );

-- Add comments to the columns

comment on column T_ORD_ORDER.waybill_status

  is '3  攬收成功';

-- Create/Recreate index

create index YTEXP.MOBILEINDEX on T_ORD_ORDER (SENDER_MOBILE)

  tablespace TS_YTEXP

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    next 1M

    minextents 1

    maxextents unlimited

  );

-- Create/Recreate primary, unique and foreign key constraints

alter table T_ORD_ORDER

  add constraint PK_T_ORD_ORDER primary key (ORDER_ID)

  using index

  tablespace TS_YTORD

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    next 1M

    minextents 1

    maxextents unlimited

  );

alter table T_ORD_ORDER

  add constraint FK_ORDER_CHANNEL_ORDER foreign key (ORDER_CHANNEL_CODE)

  references T_ORD_ORDER_CHANNEL (ORDER_CHANNEL_CODE)

  disable;

-- Grant/Revoke object privileges

grant select on T_ORD_ORDER to CACHEUSER;

grant select, insert, update on T_ORD_ORDER to YTCLT;

grant select, insert, update, delete, references, alter, index on T_ORD_ORDER to YTEXP;

grant select on T_ORD_ORDER to YTHET;

grant select on T_ORD_ORDER to YTOCC;

grant select, insert, update, delete on T_ORD_ORDER to YTPDA;

grant select, insert, update, delete on T_ORD_ORDER to YTRPT;

------------------------------action of partition

alter table p_range_test add partition t_p4 values less than(40);---增加表分割槽

select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='P_RANGE_TEST' order by partition_position;
--檢視分割槽資訊

create table p_range_test (id number,name varchar2(100))

partition by range(id)(

partition t_p1   values less than (10),

partition t_p2   values less than (20),

partition t_p3   values less than (30),

partition t_pmax values less than (maxvalue));---建立帶有最大分割槽的分割槽表

alter table p_range_test split partition t_pmax at (50) into (partition t_p5 , partition t_pmax);---拆分最大分割槽

alter table p_range_test RENAME PARTITION sys_p41 to t_p4;-----修改表分割槽的名稱

tablespace TBS_KR_LIST_M partition by range (STATIS_MONTH) ---建立包含子分割槽的表 

SUBPARTITION BY list (STATIS_DATE)   (   

  PARTITION MMS_RPT_BADS_200712 VALUES less than (200801) tablespace TBS_KR_LIST_M  

 (   

    subpartition mms_rtp_bads_20071212 values (to_date('2007-12-12','yyyy-mm-dd')) tablespace TBS_KR_LIST_M  

  )  

);

alter table p_range_test RENAME PARTITION sys_p41 to t_p4;-----修改表分割槽的名稱

------------------------------action of index

 create index idx_1 on t(a) ;---建立索引

 

 select index_name from all_indexes where table_name = 'PICTURE';---檢視現有索引資訊

 

SELECT dbms_metadata.get_ddl('INDEX','IDX0')FROM DUAL;---檢視索引的建立語句