-- 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;---查看索引的创建语句
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30018455/viewspace-1665008/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30018455/viewspace-1665008/