Table Space Properties in a partitioned table in Oracle

The partitioned table in Oracle is a good feature in Oracle that divides large tables into smaller tables, increasing the efficiency of SQL execution for that large table, while each partition is transparent to the application.

Each partition in the partitioned table has a separate storage feature, including Tablespace, Pct_free, and so on. What is the relationship between the partitioned table spaces in the partitioned table? What table space will the new partition be created in? Which table space does the corresponding local partition index use? Here's an example to explain the above questions.

To create a test partition table:

[Email protected]>create table t (ID number,name varchar2 ()) 2 tablespace users 3 partition by range (ID) 4 (5 Partition P1 values less than (Ten) Tablespace example, 6 partition P2 values less than tablespace system, 7 par Tition P3 values less than (30) 8);

A range partition table T is created above, table T is specified with the table space for users, partition P1 table space is example, partition P2 tablespace is system, Partition P3 table space is not specified.

The following table spaces are viewed from the user_tables, user_tab_partitions view respectively

[Email protected]>col tablespace_name for a30[email protected]>col partition_name for A30[email protected]> Select tablespace_name,partitioned from User_tables where table_name= ' T '; Tablespace_name PARTITION---------------------------------------yes[email protected]>select Partition_na Me,tablespace_name from user_tab_partitions where table_name= ' T ';       Partition_name tablespace_name------------------------------------------------------------P1 EXAMPLEP2 SYSTEMP3 USERS

As can be seen from the above query, the partition table T in the User_tables view does not record the table space name information, partition P1 and P2 corresponding partition with the table statement specified in the same, partition P3 the corresponding partition and table t specified table space is the same as users. Does the table t have table space properties? We use DBMS_METADATA.GET_DDL to view the statement for table T:

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/96/EF/wKioL1km3uaiZzdhAAC7zPV8vnE762.png "title=" 1.png "alt=" Wkiol1km3uaizzdhaac7zpv8vne762.png "/>

As you can see, the table T actually has a table space attribute, which is the users table space specified when the table is being constructed. The partition P3 inherits this attribute. So why is partition P3 inherited this property, we query the following view:

[Email protected]>col table_name for a30[email protected]>select Table_name,def_tablespace_name from User_part_ Tables table_name def_tablespace_name---------------------------------------------------------------T USERS

The official document's explanation of column def_tablespace_name is the Default tablespace to being used when adding a partition. as you can see from the above query, the partition of table T uses the users table space if the tablespace is not explicitly specified. The truth is, add a table space to table T below:

[Email protected]>alter table T add partition P4 values less than (40); Table altered. [Email protected]>select partition_name,tablespace_name from user_tab_partitions where table_name= ' T ';       Partition_name tablespace_name------------------------------------------------------------P1 EXAMPLEP2 SYSTEMP3 USERSP4 USERS

As can be seen from the above, the newly added partition P4 corresponding tablespace is the users, confirming the previous view.

If the current tablespace is already unable to expand and you want to create a new partition into another table space and do not specify tablespace information when adding a table space, can you do that? The answer is definitely yes.

[Email protected]>alter table t modify default attributes tablespace  example; Table altered. [email protected]>select table_name,def_tablespace_name from user_part_tables; Table_name       def_tablespace_name------------------------------ --- ---------------------------------------------------------------------------------------t        EXAMPLE[email protected]>alter table t add partition  p5 values less than  (50); Table altered. [email protected]>select partition_name,tablespace_name from user_tab_partitions  Where table_name= ' T '; Partition_name       tablespace_name------------------------------ --- ---------------------------p1       examplep2        systemp3       usersp4       usersp5        example

As you can see from the above, the newly added partition P5 is created in the example table space after modifying the table space properties of table T.

The table space corresponding to the local partition index is shown below. Create a partitioned index on the table first.

[Email protected]>create index idx_t on t (ID) Local;index created.

Here's a look at the properties of the table space for the local partition index:

[Email protected]>select tablespace_name,partitioned from user_indexes where index_name= ' idx_t '; Tablespace_name PARTITION---------------------------------------yes[email protected]>select Partition_na Me,partition_position,tablespace_name from user_ind_partitions where index_name= ' idx_t '; Partition_name partition_position tablespace_name--------------------------------------------------------------- ---------------P11 EXAMPLEP22 SYSTEMP33 USERSP44 USERSP55 EXAMPLE

As you can see from the above query, there is no tablespace information on the local partition index, and the corresponding tablespace name for each index partition is the same as the table space where the corresponding partition resides. We also use the Dbms_metadata package to view the index's build statement:

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/96/EF/wKiom1km5cmT08hBAADhEurAxtg660.png "title=" 1.png "alt=" Wkiom1km5cmt08hbaadheuraxtg660.png "/> from can see that the index idx_t does not have a tablespace attribute. Let's check User_part_index to see if it's true:

[Email protected]>col index_name for a30[email protected]>col def_tablespace_name for A30[email protected]> Select Index_name,def_tablespace_name from user_part_indexes where index_name= ' idx_t '; index_name def_tablespace_name ------------------------------------------------------------idx_t

You can see from the above query that the index idx_t also does not have the default tablespace storage option, and you see in the official documentation:

[Email protected]>drop index idx_t;index dropped. [Email protected]>create index idx_t on t (ID)  local tablespace  Sysaux;index created. [email protected]>select tablespace_name,partitioned from user_indexes where  Index_name= ' idx_t '; Tablespace_name        partition------------------------------ -- -------       YES       [email  Protected]>select partition_name,partition_position,tablespace_name from user_ind_partitions  where index_name= ' idx_t '; Partition_name       partition_position tablespace_ NAME------------------------------ ------------------ ------------------------------p11  Sysauxp22 sysauxp33 sysauxp44 sysauxp55 sysaux[email protected]>select index_ NAme,def_tablespace_name from user_part_indexes where index_name= ' IDX_T ';INDEX_NAME        def_tablespace_name------------------------------ ------------------- -----------Idx_t       sysaux

From the above query, you can see that all partitioned indexes have a tablespace of Sysaux.

Create a new partition to see if the corresponding partition index is still in the Sysaux table space:

[Email protected]>alter table T add partition P6 values less than (60); Table altered. [Email protected]>select partition_name,partition_position,tablespace_name from user_ind_partitions where index_ Name= ' idx_t '; Partition_name partition_position tablespace_name--------------------------------------------------------------- ---------------P11 SYSAUXP22 SYSAUXP33 SYSAUXP44 SYSAUXP55 SYSAUXP66 sysaux

As you can see from the above, the table space where the new partition index resides is still sysaux.

Here's how to modify the corresponding tablespace created by the new partition index:

[Email protected]>alter index idx_t modify default attributes tablespace  users;index altered. [email protected]>select index_name,def_tablespace_name from user_part_indexes  Where index_name= ' idx_t '; index_name       def_tablespace_ NAME------------------------------ ------------------------------idx_t        users[email protected]>alter table t add partition p7 values  less than  (70); Table altered. [Email protected]>select partition_name,partition_position,tablespace_name from user_ Ind_partitions where index_name= ' idx_t '; Partition_name       partition_position tablespace_ NAME------------------------------ ------------------ ------------------------------p11  Sysauxp22 sysauxp33 sysauxp44 sysauxp55 sysauxp66 sysauxp77 users 

As can be seen from the above results, the table space of the partition index corresponding to the new partition is changed to the newly specified users. The modification was successful.

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1929585

Table Space Properties in a partitioned table in Oracle