Oracle database methods for viewing information about partitioned tables

How does an Oracle database view information about a partitioned table? I wonder if you have summed up this knowledge. Here we create two table test, GPRS_CELLTOPVOL_WK to do the experiment, the script is as follows:

CREATE TABLE Gprs_celltopvol_wk

(

DATE_CD Number (8) is not NULL,

WK_CD Number (2) is not NULL,

CITY_ID Number (10),

Cell_en_nam VARCHAR2 () not NULL,

Cell_cn_nam VARCHAR2 () not NULL,

Cell_vol number,

cell_vol_pct number,

Avg_rat number,

CONSTRAINT pk_gprs_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_celltopvol_wk is ' GPRS traffic cell Week analysis '

/

COMMENT on COLUMN gprs_celltopvol_wk. DATE_CD is ' date encoding '

/

COMMENT on COLUMN gprs_celltopvol_wk. WK_CD is ' weekly code '

/

COMMENT on COLUMN gprs_celltopvol_wk. CITY_ID is ' City code '

/

COMMENT on COLUMN gprs_celltopvol_wk. Cell_en_nam is ' Community English name '

/

COMMENT on COLUMN gprs_celltopvol_wk. Cell_cn_nam is ' Community Chinese name '

/

COMMENT on COLUMN gprs_celltopvol_wk. Cell_vol is ' Community traffic '

/

COMMENT on COLUMN gprs_celltopvol_wk. cell_vol_pct is ' Community traffic ratio '

/

COMMENT on COLUMN gprs_celltopvol_wk. Avg_rat is ' average rate '

/

CREATE TABLE TEST

(

ID Number (10),

NAME VARCHAR2 (20),

SEX VARCHAR2 (2)

)

Tablespace SYSTEM

PCTFREE 10

Pctused 40

Initrans 1

Maxtrans 255

STORAGE

(

INITIAL 64K

Minextents 1

Maxextents Unlimited

);

If you're a developer or DBA, and you want to see whether the tables in the database or a table are partitioned, you can view them in the following ways.

Method 1:

SELECT * FROM all_tables WHERE table_name = ' TEST '

SELECT * FROM all_tables WHERE table_name = ' GPRS_CELLTOPVOL_WK ';

As shown in the screenshot you will find that the tablespcae_name of the Gprs_celltopvol_wk table is empty and the tablespcae_name of table test is system, so you can pass Tablespcae_ Whether name is null to determine whether a table is a partitioned table. Someone on the internet has given this method, but remember that it is not necessarily tablespcae_name null, the table is a partitioned table, there is no reciprocal relationship between the two. An example is the countries table of owner HR. In fact, you can tell whether the table is a partitioned table by the partitioned value of Yes or No in the All_tables field. As shown below

SELECT partitioned from All_tables WHERE Tablespace_name is NULL

Method 2 You can use Dba_part_tables, All_part_tables, user_part_tables to view the partition table information, the three specific differences are as follows:

Display information for all partitioned tables in the database: Dba_part_tables

Displays all partition table information accessible to the current user: All_part_tables

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

SELECT * FROM all_part_tables WHERE table_name = ' GPRS_CELLTOPVOL_WK '

You can view the basic information for a partitioned table from tables such as Dba_part_tables: Partitioned table types (viewed through Partitioning_type fields), sub-partition type (Subpartitioning_type), Number of partitions (partition_ COUNT) But if you want to view the detailed partition information for the table, you can view the details by dba_tab_partitions, All_tab_partitions, User_tab_partitions, and the three tables. The differences between the three are shown below

Show Oracle View partition table information displays detailed partition information for all partitioned tables in the database: Dba_tab_partitions

Displays detailed partition information for all partitioned tables accessible to the current user: All_tab_partitions

Displays detailed partition information for all partitioned tables for the current user: User_tab_partitions

If you want to view the partition column information for a partitioned table, you can view the information by Dba_part_key_columns, All_part_key_columns, user_part_key_columns three tables, as shown in the following illustration, The differences between the three are shown below

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

Displays the partition column information for all partitioned tables accessible to the current user: All_part_key_columns

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

If you want to see the sub partition information of the combined table and the Sub partition column information, you can see the specific situation from the following tables

Displays child partition information for all grouped partitioned tables for the current user: User_tab_subpartitions

Displays child partition information for all combined partitioned tables accessible to the current user: All_tab_subpartitions

Displays child partition information for all combined partitioned tables accessible to the current user: All_tab_subpartitions

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

Displays the child partition column information for all partitioned tables accessible to the current user: All_subpart_key_columns

Display sub-partition columns display sub-partition column information for all partitioned tables in the database: Dba_subpart_key_columns