How to view partition table information in ORACLE Database

How does an ORACLE database view partition table information? I wonder if you have summarized this knowledge. Next, we will first create two tables TEST and maid to perform the experiment. The script is as follows:

Create table maid
(
DATE_CD NUMBER (8) not null,
WK_CD NUMBER (2) not null,
CITY_ID NUMBER (10 ),
CELL_EN_NAM VARCHAR2 (64) not null,
CELL_CN_NAM VARCHAR2 (64) not null,
CELL_VOL NUMBER,
CELL_VOL_PCT NUMBER,
AVG_RAT NUMBER,
CONSTRAINT pk_upls_celltopvol_wk primary key (DATE_CD, WK_CD, CITY_ID, CELL_EN_NAM, CELL_CN_NAM)
) Partition by range (DATE_CD)
(
PARTITION TEST_RANGE_1 values less than (201104) tablespace users,
PARTITION TEST_RANGE_2 values less than (201105) tablespace users,
PARTITION TEST_RANGE_3 values less than (201106) TABLESPACE USERS
);
/


Comment on table gprs s_celltopvol_wk IS 'gprs traffic cell weekly analytics'
/

Comment on column upls_celltopvol_wk.date_cd IS 'date encoding'
/
Comment on column upls_celltopvol_wk.wk_cd IS 'Weekly encoding'
/
Comment on column upls_celltopvol_wk.city_id IS 'prefecture-city Code'
/
Comment on column upls_celltopvol_wk.cell_en_nam IS 'residential name'
/
Comment on column gps_celltopvol_wk.cell_cn_nam IS 'Community Chinese name'
/
Comment on column upls_celltopvol_wk.cell_vol IS 'residential traffic'
/
Comment on column upls_celltopvol_wk.cell_vol_pct IS 'area traffic proportion'
/
Comment on column upls_celltopvol_wk.avg_rat IS 'average speed'

/

CREATE TABLE TEST
(
Id number (10 ),
NAME VARCHAR2 (20 ),
SEX VARCHAR2 (2)
)
TABLESPACE SYSTEM
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 64 K
MINEXTENTS 1
MAXEXTENTS UNLIMITED

);

If you are a developer or DBA, you can use the following methods to check whether the tables or tables in the database are partitioned.

Method 1:

SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'test'

SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'gprs _ CELLTOPVOL_WK ';

You will find that the TABLESPCAE_NAME of the table 'table _ celltopvol_wk 'is empty, and the TABLESPCAE_NAME of the table 'test' is SYSTEM. Therefore, you can judge whether a table is a partition table by checking whether TABLESPCAE_NAME is empty. Some people provide this method on the Internet, but remember that if TABLESPCAE_NAME is not necessarily NULL, the table is a partition table and there is no mutual push relationship between the two. The COUNTRIES table whose OWNER is HR is an example. In fact, you can identify whether the table is a PARTITIONED table through the value of PARTITIONED in the ALL_TABLES field is YES or NO. As shown below:

Select partitioned from ALL_TABLES WHERE TABLESPACE_NAME IS NULL

 

 

Method 2: You can view the partition table information through DBA_PART_TABLES, ALL_PART_TABLES, and USER_PART_TABLES. The specific differences between the three methods are as follows:

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

SELECT * FROM ALL_PART_TABLES WHERE TABLE_NAME = 'gprs _ CELLTOPVOL_WK'

From DBA_PART_TABLES and other tables, you can view the basic information of the partition table, such as the partition table type (viewed through the PARTITIONING_TYPE field), subpartition type (SUBPARTITIONING_TYPE), and partition count (PARTITION_COUNT) however, if you want to view the detailed partition information of the table, you can view the detailed information through the DBA_TAB_PARTITIONS, ALL_TAB_PARTITIONS, and USER_TAB_PARTITIONS tables. The differences between the three are as follows:

Show Oracle view partition table information show 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

 

If you want to view the partition column information of a partition table, you can view the related information in three tables: DBA_PART_KEY_COLUMNS, ALL_PART_KEY_COLUMNS, and USER_PART_KEY_COLUMNS, as shown in the following figure.

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

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

Display partition column information of all database partition tables: DBA_PART_KEY_COLUMNS

 

If you want to view the sub-partition information and sub-partition column information of the combined table, you can view the specific information from the following tables.

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

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

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

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

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

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