Query Oracle partition table information

View all user partition tables and partition policies (including level 1 and level 2 partition tables): SELECTp. table_nameAS table name, decode (p. partitioning_key_count, 1,

View all user partition tables and partition policies (including level 1 and level 2 partition tables): SELECT p. table_name AS table name, decode (p. partitioning_key_count, 1,

View all user partition tables and partition policies (level 1 and level 2 partition tables are included ):

SELECT p. table_name AS table name, decode (p. partitioning_key_count, 1, 'Primary partition') AS partition type,
P. partitioning_type AS partition type, p. column_name AS partition key,
Decode (nvl (q. subpartitioning_key_count, 0), 0, 'no subpartition', 1, 'subpartition') AS with or without subpartitions,
Q. subpartitioning_type AS subpartition type, q. column_name AS subpartition key
FROM (SELECT a. table_name, a. partitioning_type, B. column_name, a. partitioning_key_count
FROM user_part_tables a, user_part_key_columns B
WHERE a. table_name = B. NAME
AND B. object_type = 'table') p,
(SELECT a. table_name, a. subpartitioning_type, B. column_name, a. subpartitioning_key_count
FROM user_part_tables a, user_subpart_key_columns B
WHERE a. table_name = B. NAME
AND a. subpartitioning_key_count <> 0
AND B. object_type = 'table') q
WHERE p. table_name = q. table_name (+)
Order by 5, 4, 1;

Display information of all database partition tables: DBA_PART_TABLES

Display All the partition tables accessible to the current user: ALL_PART_TABLES

Display information of all the partition tables of the current user: USER_PART_TABLES

Displays the table partition information. displays the detailed partition information of all database partition tables: DBA_TAB_PARTITIONS

Displays the detailed partition information of all partition tables accessible to the current user: ALL_TAB_PARTITIONS

Displays detailed partition information for all partition tables of the current user: USER_TAB_PARTITIONS

Show the sub-partition information show the sub-partition information of all the combined partition tables of the database: DBA_TAB_SUBPARTITIONS

Display the subpartitions of all the combined partition tables accessible to the current user: ALL_TAB_SUBPARTITIONS

Displays the subpartition information of all the combined partition tables of the current user: USER_TAB_SUBPARTITIONS

Display partition column information of all database partition tables: DBA_PART_KEY_COLUMNS

Display the partition column information of all partition tables accessible to the current user: ALL_PART_KEY_COLUMNS

Display the partition column information of all partition tables of the current user: USER_PART_KEY_COLUMNS

Display the subpartition column information of all database partition tables: DBA_SUBPART_KEY_COLUMNS

Display the information of the subpartition columns of all partition tables accessible to the current user: ALL_SUBPART_KEY_COLUMNS

Displays the information of subpartition columns in all partition tables of the current user: USER_SUBPART_KEY_COLUMNS

Bytes --------------------------------------------------------------------------------------------------

How to query all partition tables in the Oracle database

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

The data for deleting a table is truncate table table_name;

To delete a partition table, the data in a partition is alter table table_name truncate partition p5;

If you want to clear the data of each partition in the partition table, you can use truncate table table_name to delete it directly;

You can also use:

Alter table table_name truncate partition p1;

Alter table table_name truncate partition p2;

Alter table table_name truncate partition p3;

Alter table table_name truncate partition p4;

Alter table table_name truncate partition p5;

Alter table table_name truncate partition p6;

Delete one by one.

,