DBA_Oracle DBA partition concept Summary (concept), dba_oracledba

DBA_Oracle DBA partition concept Summary (concept), dba_oracledba

2014-06-20 BaoXinjian

I. Summary

Some maintenance operations on table partitions:

Note: select a partition based on the actual situation.

Table partitions have the following advantages:

1. Data Query: data is stored on multiple files, which reduces the I/O load and improves the query speed.

2. Data pruning: It is ideal for storing historical data.

3. Backup: divides data in large tables into multiple files to facilitate backup and recovery.

4. Parallelism: DML operations can be performed on tables at the same time to improve the parallel performance.

 

Ii. Partition operations

1. Add partitions

The following code adds a P3 partition to the SALES table

Alter table sales add partition P3 values less than (TO_DATE ('2017-06-01 ', 'yyyy-MM-DD '));

Note: The partition limit added above should be higher than the last partition limit.

The following code adds an P3SUB1 subpartition to the P3 partition of the SALES table

Alter table sales modify partition P3 add subpartition P3SUB1 VALUES ('complete ');

 

2. delete partitions

Run the following code to delete a partition in Table P3:

Alter table sales drop partition P3;

Run the following code to delete the P4SUB1 subpartition:

Alter table sales drop subpartition P4SUB1;

Note: If the deleted partition is the only partition in the table, the partition cannot be deleted. to delete the partition, you must delete the table.

 

3. Partition Truncation

A partition is used to delete data from a partition. Data in other partitions is not deleted. If a table has only one partition, it can be truncated. Run the following code to truncate a partition:

Alter table sales truncate partition P2;

Run the following code to truncate a subpartition:

Alter table sales truncate subpartition P2SUB2;

 

4. merge partitions

Merging partitions combines adjacent partitions into one partition. The result partitions use the boundary of High-score partitions. It is worth noting that partitions cannot be merged into partitions with low boundaries. Run the following code to merge the P1 P2 partitions:

Alter table sales merge partitions P1, P2 into partition P2;

 

5. Shard

A shard Splits a shard into two new shards. After the Shard is split, the original shard no longer exists. Note that HASH partitions cannot be split.

Alter table sales sb1_partition P2 AT (TO_DATE ('1970-02-01 ', 'yyyy-MM-DD '))

INTO (PARTITION p21. PARTITION P22 );

 

6. Join partition (coalesca)

A combined partition joins data in a hash partition to another partition. When the data in the hash partition is large, you can add a hash partition and then join the partition. It is worth noting that, join partitions can only be used in hash partitions. Run the following code to perform join partitioning:

Alter table sales coalesca partition;

 

7. rename table partitions

The following code changes p21.

Alter table sales rename partition p21-p2;

 

8. Cross-partition Query

Select sum (*) from (

(Select count (*) cn from t_table_SS PARTITION (P200709_1)

Union all

Select count (*) cn from t_table_SS PARTITION (P200709_2 ));

 

9. query the partitions in the table.

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME = 'tablename'

 

10. query index information

Select object_name, object_type, tablespace_name, sum (value)

From v $ segment_statistics

Where statistic_name IN ('physical reads', 'Physical write', 'logical reads') and object_type = 'index'

Group by object_name, object_type, tablespace_name

Order by 4 desc

 

Iii. Partition Query

1. display information of all database partition tables:

Select * from DBA_PART_TABLES

 

2. display information about all partition tables accessible to the current user:

Select * from ALL_PART_TABLES

 

3. display information about all the partition tables of the current user:

Select * from USER_PART_TABLES

 

4. display the table partition information to display the detailed partition information of all database partition tables:

Select * from DBA_TAB_PARTITIONS

 

5. display the detailed partition information of all partition tables accessible to the current user:

Select * from ALL_TAB_PARTITIONS

 

6. display the detailed partition information of all the partition tables of the current user:

Select * from USER_TAB_PARTITIONS

 

7. Show the sub-partition information to display the sub-partition information of all the combined partition tables of the database:

Select * from DBA_TAB_SUBPARTITIONS

 

8. display the sub-partitions of all the combined partition tables accessible to the current user:

Select * from ALL_TAB_SUBPARTITIONS

 

9. display the subpartitions of all the combined partition tables of the current user:

Select * from USER_TAB_SUBPARTITIONS

 

10. Display partition column information of all database partition tables:

Select * from DBA_PART_KEY_COLUMNS

 

11. display the partition column information of all partition tables accessible to the current user:

Select * from ALL_PART_KEY_COLUMNS

 

12. display the partition column information of all the partition tables of the current user:

Select * from USER_PART_KEY_COLUMNS

 

13. display the subpartition column information of all database partition tables:

Select * from DBA_SUBPART_KEY_COLUMNS

 

14. display the subpartition column information of all partition tables accessible to the current user:

Select * from ALL_SUBPART_KEY_COLUMNS

 

15. display information about the subpartition columns of all partition tables of the current user:

Select * from USER_SUBPART_KEY_COLUMNS

 

16. How to query all partition tables in the oracle database

Select * from user_tables a where a. partitioned = 'yes'

 

********************Author: Bao Xin********************

 

Reprinted: the DBA information is basically reprinted on the network. After the organization is completed, the DBA information is kept and cannot be found. I would like to pay tribute to the original author.