首先,我建了三个表空间:flp,p1,p2,对应到数据库文件,flp.dnf,p1.dnf,p2.dnf 然后我建了下面两个分区表
create table inter_table1
(id number, fid number, new_name varchar2(30), create_date date, comments varchar2(1000))
partition by range (create_date)
(partition p1 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),
partition p2 values less than (maxvalue)
); create table inter_table2
(id number, fid number, new_name varchar2(30), create_date date, comments varchar2(1000))
tablespace flp
partition by range (create_date)
(partition p3 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),
partition p4 values less than (maxvalue)
);
inter_table1 好理解,将数据放到两个物理分区上 但inter_table2,怎么理解呢,数据都是放到flp.dnf中,那么这是否意味着没分区?这样的分区表与不分区是不是差不多?分区表
create table inter_table1
(id number, fid number, new_name varchar2(30), create_date date, comments varchar2(1000))
partition by range (create_date)
(partition p1 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),
partition p2 values less than (maxvalue)
); create table inter_table2
(id number, fid number, new_name varchar2(30), create_date date, comments varchar2(1000))
tablespace flp
partition by range (create_date)
(partition p3 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),
partition p4 values less than (maxvalue)
);
inter_table1 好理解,将数据放到两个物理分区上 但inter_table2,怎么理解呢,数据都是放到flp.dnf中,那么这是否意味着没分区?这样的分区表与不分区是不是差不多?分区表
(id number, fid number, new_name varchar2(30), create_date date, comments varchar2(1000))
partition by range (create_date)
(partition p1 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),
partition p2 values less than (maxvalue)
); 你建立的分区表inter_table1没有指定存储的表空间,默认的分区表存储在你当前用户的表空间里。如果想要你的分区方便存储在不同的表空间里,想要建立表的时候为每个分区指定表空间,应该如下代码:
create table inter_table1
(id number, fid number, new_name varchar2(30), create_date date, comments varchar2(1000))
partition by range (create_date)
(partition p1 values less than (to_date('2005-1-1', 'yyyy-mm-dd')) tablespace p1,
partition p2 values less than (maxvalue) tablespace p2
);
(id number, fid number, new_name varchar2(30), create_date date, comments varchar2(1000))
partition by range (create_date)
(partition p1 values less than (to_date('2005-1-1', 'yyyy-mm-dd')) <span style="color: #FF0000;">tablespace p1</span>,
partition p2 values less than (maxvalue) <span style="color: #FF0000;">tablespace p2</span>
);
(id number, fid number, new_name varchar2(30), create_date date, comments varchar2(1000))
partition by range (create_date)
(partition p1 values less than (to_date('2005-1-1', 'yyyy-mm-dd')) tablespace p1,
partition p2 values less than (maxvalue) tablespace p2
);CSDN编辑器也够烂的分区表的分区指的是逻辑分区,和存储位置物理表空间没有什么关系,2个逻辑分区可以存储在一个表空间里,就想表和索引两种逻辑对象可以同时存储在一个表空间里一样