Oracle中分区表中表空间属性
Oracle中的分区表是Oracle中的⼀个很好的特性，可以把⼤表划分成多个⼩表，从⽽提⾼对于该⼤表的SQL执⾏效率，⽽各个分区对应⽤⼜是透明的。
分区表中的每个分区有独⽴的存储特性，包括表空间、PCT_FREE等。那分区表中的各分区表空间之间有什么关系？新建的分区会创建在哪个表空间中呢？对应的local分区索引⼜会使⽤哪个表空间呢？下⾯使⽤⼀个例⼦来解释上⾯的这些问题。
创建测试分区表：
1 2 3 4 5 6 7 8zx@TEST>create table t (id number,name varchar2(10))
爱是什么 作文2 tablespace users
3 partition by range(id)
4 (
5 partition p1 values less than (10) tablespace example,
6 partition p2 values less than (20) tablespace system,
7 partition p3 values less than (30)
8 );
上⾯创建了⼀个range分区表T，对表T指定了表空间为users，分区p1表空间为example，分区p2表空间为system，分区p3表空间没有指定。
下⾯分别从user_tables、user_tab_partitions视图中查看对应的表空间
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15zx@TEST>col tablespace_name for a30
zx@TEST>col partition_name for a30
zx@TEST>select tablespace_name,partitioned from user_tables where table_name='T';
TABLESPACE_NAME PARTITION
------------------------------ ---------
YES
zx@TEST>select partition_name,tablespace_name from user_tab_partitions where table_name='T';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1 EXAMPLE
P2 SYSTEM
P3 USERS
从上⾯的查询可以看出，分区表T在user_tables视图中没有记录表空间名的信息，分区P1和P2对应的分区与建表语句中指定的⼀致，分区P3对应的分区与表T指定的表空间⼀致为USERS。难道表T就没有表空间属性么？我们使⽤_ddl查看表T的语句：
从上图中可以看出表T其实也是有表空间属性的，就是在建表时指定的USERS表空间。⽽分区P3继承了这⼀属性。那为什么说是分区P3继承了这⼀属性呢，我们查询下⾯的视图：
1 2 3 4 5 6zx@TEST>col table_name for a30
zx@TEST>select table_name,def_tablespace_name from user_part_tables;
TABLE_NAME DEF_TABLESPACE_NAME
------------------------------ ---------------------------------
T USERS
官⽅⽂档对列def_tablespace_name的解释是Default tablespace to be used when adding a partition。从上⾯的查询可以知道，表T的分区如果没有明确指定表空间时都会使⽤USERS表空间。事实是这样么，下⾯给表T添加⼀个表空间：
1 2 3 4 5 6 7
8 9 10 11 12zx@TEST>alter table t add partition p4 values less than (40);
Table altered.
zx@TEST>select partition_name,tablespace_name from user_tab_partitions where table_name='T'; PARTITION_NAME TABLESPACE_NAME
-
----------------------------- ------------------------------
P1 EXAMPLE
P2 SYSTEM
P3 USERS
P4 USERS
从上⾯可以看到，新添加的分区P4对应的表空间是USERS，证实了前⾯的观点。
如果当前的表空间已经⽆法扩展，想把新加的分区创建到其他表空间中，⽽在加表空间时不指定表空间信息，可以实现么？答案是肯定可以。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23zx@TEST>alter table t modify default attributes tablespace example;
Table altered.
zx@TEST>select table_name,def_tablespace_name from user_part_tables;
TABLE_NAME DEF_TABLESPACE_NAME
------------------------------ ------------------------------------------------------------------------------------------
T EXAMPLE
zx@TEST>alter table t add partition p5 values less than (50);
Table altered.
zx@TEST>select partition_name,tablespace_name from user_tab_partitions where table_name='T'; PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1 EXAMPLE
P2 SYSTEM
P3 USERS
P4 USERS
P5 EXAMPLE
从上⾯可以看到在修改了表T的表空间属性后，新加的分区P5创建在EXAMPLE表空间中。下⾯再来看local分区索引对应的表空间。先在表上创建⼀个分区索引。美娇娘
1 2 3zx@TEST>create index idx_t on t(id) local; Index created.
下⾯看看local分区索引对应的表空间的属性：葡萄酒的酿造方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15zx@TEST>select tablespace_name,partitioned from user_indexes where index_name='IDX_T';三国演义人物特点
TABLESPACE_NAME PARTITION
------------------------------ ---------
YES
zx@TEST>select partition_name,partition_position,tablespace_name from user_ind_partitions where index_name='IDX_T';
PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
P1 1 EXAMPLE
P2 2 SYSTEM
P3 3 USERS
P4 4 USERS
P5 5 EXAMPLE
从上⾯的查询可以看出，local分区索引上没有表空间信息，⽽每个索引分区对应的表空间名与相应的分区所在的表空间⼀致。我们同样使⽤dbms_metadata包来查看索引的建表语句：
从上图可以看到索引IDX_T确实没有表空间属性。我们再来查看user_part_index来验证⼀下是否是真的呢：
1 2 3 4 5 6 7zx@TEST>col index_name for a30
zx@TEST>col def_tablespace_name for a30
zx@TEST>select index_name,def_tablespace_name from user_part_indexes where index_name='IDX_T';
INDEX_NAME DEF_TABLESPACE_NAME
------------------------------ ------------------------------
IDX_T
从上⾯的查询中可以看到索引IDX_T也没有默认的表空间存储选项，⽽在官⽅⽂档中看到：New partitions or subpartitions added to the local index will be created in the same tablespace(s) as the corresponding partitions or subpartitions of the underlying table。说明local分区索引默认与相关联的表分区在同⼀个表空间，上⾯的查询也可以验证这⼀结论。那可以把local分区索引所在的表空间与表分区所在的表空间分开来么？答案是肯定可以的。在创建本地索引进指定表空间参数即可：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29zx@TEST>drop index idx_t;
Index dropped.
zx@TEST>create index idx_t on t(id) local tablespace sysaux;
Index created.
zx@TEST>select tablespace_name,partitioned from user_indexes where index_name='IDX_T';
TABLESPACE_NAME PARTITION
------------------------------ ---------
YES
zx@TEST>select partition_name,partition_position,tablespace_name from user_ind_partitions where index_name='IDX_T'; PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
P1 1 SYSAUX
P2 2 SYSAUX
P3 3 SYSAUX
P4 4 SYSAUX
P5 5 SYSAUX
zx@TEST>select index_name,def_tablespace_name from user_part_indexes where index_name='IDX_T';
INDEX_NAME DEF_TABLESPACE_NAME
------------------------------ ------------------------------
IDX_T SYSAUX
从上⾯的查询中可以看到所有的分区索引的表空间都为SYSAUX。创建⼀个新的分区，看对应的分区索引是否还是在SYSAUX表空间：
1 2 3 4 5 6 7 8 9 10 11 12 13 14zx@TEST>alter table t add partition p6 values less than (60);
Table altered.
zx@TEST>select partition_name,partition_position,tablespace_name from user_ind_partitions where index_name='IDX_T'; PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------ ------------------------------阿宝的老婆
P1 1 SYSAUX
P2 2 SYSAUX
P3 3 SYSAUX
P4 4 SYSAUX
P5 5 SYSAUX
P6 6 SYSAUX
从上⾯可以看出新的分区索引所在的表空间仍是SYSAUX。下⾯来看如何修改新分区索引创建的对应的表空间：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18zx@TEST>alter index idx_t modify default attributes tablespace users;
Index altered.
zx@TEST>select index_name,def_tablespace_name from user_part_indexes where index_name='IDX_T';
INDEX_NAME DEF_TABLESPACE_NAME
------------------------------ ------------------------------
IDX_T USERS
zx@TEST>alter table t add partition p7 values less than (70);
医院安全管理制度Table altered.
zx@TEST>select partition_name,partition_position,tablespace_name from user_ind_partitions where index_name='IDX_T'; PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
19 20 21 22 23 24 25P1 1 SYSAUX P2 2 SYSAUX P3 3 SYSAUX P4 4 SYSAUX P5 5 SYSAUX P6 6 SYSAUX P7 7 USERS
从上⾯的结果可以看出，新加分区对应的分区索引的表空间变为了新指定的USERS。修改成功。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论