Oracle 11g Partitioned Table

View all user partition tables and partitioning policies (1, 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 subarea ', 1, ' sub-partition ') as has no sub-partition,
Q.subpartitioning_type as sub-partition type, q.column_name as sub-partition 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 for all partitioned tables in the database: Dba_part_tables

Displays all partition table information that the current user can access: all_part_tables

Displays information about all partitioned tables for the current user: User_part_tables

Display table partition information displays detailed partition information for all partitioned tables in the database: Dba_tab_partitions

Displays detailed partitioning information for all partitioned tables that the current user can access: all_tab_partitions

Displays detailed partitioning information for all partitioned tables for the current user: User_tab_partitions
SELECT * from User_tab_partitions;

Show sub-partition information displays sub-partition information for all combined partitioned tables in the database: Dba_tab_subpartitions

Displays sub-partition information for all combined partitioned tables that the current user can access: all_tab_subpartitions

Displays sub-partition information for all combined partitioned tables for the current user: User_tab_subpartitions

Display partition column displays partition column information for all partitioned tables in the database: Dba_part_key_columns

Displays the partition column information for all partitioned tables that the current user can access: all_part_key_columns

Displays partition column information for all partitioned tables for the current user: User_part_key_columns

Show sub-partition columns displays sub-partition column information for all partitioned tables in the database: Dba_subpart_key_columns

Displays sub-partition column information for all partitioned tables that the current user can access: all_subpart_key_columns

Displays sub-partition column information for all partitioned tables for the current user: User_subpart_key_columns

--------------------------------------------------------------------------------------------------

How to query out all the partitioned 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;

Delete partition table data for a partition is ALTER TABLE TABLE_NAME TRUNCATE PARTITION P5;
such as: ALTER TABLE table_name truncate PARTITON SYS_P5;

If I want to empty the data of each partition in the partition table, it can be deleted directly with TRUNCATE TABLE table_name;

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 them individually.

Oracle 11g Partitioned Table