oracle查看list表分区情况,查看分区表的基本情况及其创建语句

在维护和管理ORACLE数据库时，当遇到需要查看库中有多少分区表，分区的类型、分区键，以及创建该分区表的相关语句时，可以使用以下的方法。

有关分区表，ORACLE提供了两个字典视图。

一个是DBA_PART_TABLES(其中DBA可以用ALL或USER来替换，分别表示当前用户可访问的所有分区表和当前用户所属的分区表)，其显示了对象级的分区信息。其表结构及相关说明如下：

Column

Datatype

NULL

Description

OWNER

VARCHAR2(30)

Owner of the partitioned table

TABLE_NAME

VARCHAR2(30)

Name of the partitioned table

PARTITIONING_TYPE

VARCHAR2(9)

Type of the partitioning method:

UNKNOWN - Not specified

See Also: the *_TABLES view

RANGE

HASH

SYSTEM

LIST

REFERENCE

SUBPARTITIONING_TYPE

VARCHAR2(9)

Type of the composite partitioning method:

NONE - Not subpartitioned

See Also: the *_TABLES view

RANGE

HASH

SYSTEM

LIST

REFERENCE

PARTITION_COUNT

NUMBER

Number of partitions in the table. For interval partitioned tables, the value of this column is always 1048575.

DEF_SUBPARTITION_COUNT

NUMBER

For a composite-partitioned table, the default number of subpartitions, if specified

PARTITIONING_KEY_COUNT

NUMBER

Number of columns in the partitioning key

SUBPARTITIONING_KEY_COUNT

NUMBER

For a composite-partitioned table, the number of columns in the subpartitioning key

STATUS

VARCHAR2(8)

If a previous DROP TABLE operation failed, indicates whether the table is unusable (UNUSABLE) or valid (VALID)

DEF_TABLESPACE_NAME

VARCHAR2(30)

Default tablespace to be used when adding a partition

DEF_PCT_FREE

NUMBER

Default value of PCTFREE to be used when adding a partition

DEF_PCT_USED

NUMBER

Default value of PCTUSED to be used when adding a partition

DEF_INI_TRANS

NUMBER

Default value of INITRANS to be used when adding a partition

DEF_MAX_TRANS

NUMBER

Default value of MAXTRANS to be used when adding a partition

DEF_INITIAL_EXTENT

VARCHAR2(40)

Default value of INITIAL (in Oracle blocks) to be used when adding a partition, or DEFAULT if no INITIAL value was specified

DEF_NEXT_EXTENT

VARCHAR2(40)

Default value of NEXT (in Oracle blocks) to be used when adding a partition, or DEFAULT if no NEXT value was specified

DEF_MIN_EXTENTS

VARCHAR2(40)

Default value of MINEXTENTS to be used when adding a partition, orDEFAULT if no MINEXTENTS value was specified

DEF_MAX_EXTENTS

VARCHAR2(40)

Default value of MAXEXTENTS to be used when adding a partition, orDEFAULT if no MAXEXTENTS value was specified

DEF_MAX_SIZE

VARCHAR2(40)

Default value of MAXSIZE to be used when adding a partition, orDEFAULT if noMAXSIZE value was specified

DEF_PCT_INCREASE

VARCHAR2(40)

Default value of PCTINCREASE to be used when adding a partition, or DEFAULT if no PCTINCREASE value was specified

DEF_FREELISTS

NUMBER

Default value of FREELISTS to be used when adding a partition

DEF_FREELIST_GROUPS

NUMBER

Default value of FREELIST GROUPS to be used when adding a partition

DEF_LOGGING

VARCHAR2(7)

Default LOGGING attribute to be used when adding a partition:

NONE - Not specified

See Also: the *_TABLES view

YES

NO

DEF_COMPRESSION

VARCHAR2(8)

Default compression to be used when adding a partition:

NONE - Not specified

See Also: the *_TABLES view

ENABLED

DISABLED

DEF_COMPRESS_FOR

VARCHAR2(12)

Default compression for what kind of operations to be used when adding a partition:

BASIC

OLTP

QUERY LOW

QUERY HIGH

ARCHIVE LOW

ARCHIVE HIGH

UNKNOWN

NULL

DEF_BUFFER_POOL

VARCHAR2(7)

Default buffer pool to be used when adding a partition:

DEFAULT

KEEP

RECYCLE

NULL

DEF_FLASH_CACHE

VARCHAR2(7)

Default Database Smart Flash Cache hint to be used when adding a partition:

DEFAULT

KEEP

NONE

Solaris and Oracle Linux functionality only.

DEF_CELL_FLASH_CACHE

VARCHAR2(7)

Default cell flash cache hint to be used when adding a partition:

DEFAULT

KEEP

NONE

See Also: Oracle Exadata Storage Server Software documentation for more information

REF_PTN_CONSTRAINT_NAME

VARCHAR2(30)

Name of the partitioning referential constraint for reference-partitioned tables

INTERVAL

VARCHAR2(1000)

String of the interval value

IS_NESTED

VARCHAR2(3)

Indicates whether the partitioned table is a nested table (YES) or not (NO)

See Also: the *_NESTED_TABLES view for the parent table name/column

DEF_SEGMENT_CREATION

VARCHAR2(4)

Indicates whether a table partition segment has been created (YES) or not (NO). N/A indicates that this table is subpartitioned and no segment exists at the partition level

另一个是DBA_PART_KEY_COLUMNS(其中DBA可以用ALL或USER来替换，分别表示当前用户可访问的和当前用户所属的)，其显示了分区键字段的相关信息。其表结构及相关说明如下：

Column

Datatype

NULL

Description

OWNER

VARCHAR2(30)

Owner of the partitioned table or index

NAME

VARCHAR2(30)

Name of the partitioned table or index

OBJECT_TYPE

CHAR(5)

Object type:

TABLE

INDEX

COLUMN_NAME

VARCHAR2(4000)

Name of the column

COLUMN_POSITION

NUMBER

Position of the column within the partitioning key

借助于以上两个视图，当我们需要查看库中有多少分区表，分区的类型、分区键等信息时，可以通过以下语句进行：

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jan 2 15:27:08 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select owner,table_name,partitioning_type,partition_count,partitioning_key_count,status from dba_part_tables;

OWNER       TABLE_NAME      PARTITI PARTITION_COUNT PARTITIONING_KEY_COUNT STATUS

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

SYSTEM       LOGMNR_DICTSTATE$      RANGE    1   1 VALID

SYSTEM       LOGMNR_DICTIONARY$      RANGE    1   1 VALID

SYSTEM       LOGMNR_OBJ$      RANGE    1   1 VALID

SYSTEM       LOGMNR_USER$      RANGE    1   1 VALID

SYSTEM       LOGMNRC_GTLO      RANGE    1   1 VALID

SYSTEM       LOGMNRC_GTCS      RANGE    1   1 VALID

SYSTEM       LOGMNRC_GSII      RANGE    1   1 VALID

SYSTEM       LOGMNR_TAB$      RANGE    1   1 VALID

SYSTEM       LOGMNR_COL$      RANGE    1   1 VALID

SYSTEM       LOGMNR_ATTRCOL$      RANGE    1   1 VALID

SYSTEM       LOGMNR_TS$      RANGE    1   1 VALID

OWNER       TABLE_NAME      PARTITI PARTITION_COUNT PARTITIONING_KEY_COUNT STATUS

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

SYSTEM       LOGMNR_IND$      RANGE    1   1 VALID

SYSTEM       LOGMNR_TABPART$      RANGE    1   1 VALID

SYSTEM       LOGMNR_TABSUBPART$      RANGE    1   1 VALID

SYSTEM       LOGMNR_TABCOMPART$      RANGE    1   1 VALID

SYSTEM       LOGMNR_TYPE$      RANGE    1   1 VALID

SYSTEM       LOGMNR_COLTYPE$      RANGE    1   1 VALID

SYSTEM       LOGMNR_ATTRIBUTE$      RANGE    1   1 VALID

SYSTEM       LOGMNR_LOB$      RANGE    1   1 VALID

SYSTEM       LOGMNR_CDEF$      RANGE    1   1 VALID

SYSTEM       LOGMNR_CCOL$      RANGE    1   1 VALID

SYSTEM       LOGMNR_ICOL$      RANGE    1   1 VALID

OWNER       TABLE_NAME      PARTITI PARTITION_COUNT PARTITIONING_KEY_COUNT STATUS

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

SYSTEM       LOGMNR_LOBFRAG$      RANGE    1   1 VALID

SYSTEM       LOGMNR_INDPART$      RANGE    1   1 VALID

SYSTEM       LOGMNR_INDSUBPART$      RANGE    1   1 VALID

SYSTEM       LOGMNR_INDCOMPART$      RANGE    1   1 VALID

SYS       STREAMS$_APPLY_SPILL_MSGS_PART LIST    1   1 VALID

SYSTEM       LOGSTDBY$APPLY_PROGRESS      RANGE    1   1 VALID

SYS       WRH$_FILESTATXS      RANGE    3   2 VALID

SYS       WRH$_SQLSTAT      RANGE    3   2 VALID

SYS       WRH$_SYSTEM_EVENT      RANGE    3   2 VALID

SYS       WRH$_WAITSTAT      RANGE    3   2 VALID

SYS       WRH$_LATCH      RANGE    3   2 VALID

以返回结果的第一行为例，分区表名为“LOGMNR_DICTSTATE$ ”，所有者为“SYSTEM”，分区方法为“RANGE”，即范围分区，表分区的数量为1个，分区键的数量为1个，当前分区表状态是有效的。

再通过执行以下语句，还可以看到指定分区表上所使用的分区键是什么。例如：

SQL> select owner,name,object_type,column_name,column_position from dba_part_key_columns where name='LOGMNR_USER$';

OWNER       NAME      OBJEC COLUMN_NAME  COLUMN_POSITION

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

SYSTEM       LOGMNR_USER$      TABLE LOGMNR_UID       1

可以看到，对于我们所查看到分区表“LOGMNR_USER$”，其分区键为“LOGMNR_UID”。

更进一步，如果我们还想了解这个分区表的创建语句，可以使用系统自带的功能包dbms_metadata.get_ddl('TABLE','表名','用户名')来查询。例如，我们想看一下前面提到 的“LOGMNR_USER$”这个分区表是如何创建的，则可以用以下方法：

SQL> set pages 0

SQL> set long 100000

SQL> select dbms_metadata.get_ddl('TABLE','LOGMNR_USER$','SYSTEM') from dual;

CREATE TABLE "SYSTEM"."LOGMNR_USER$"

("USER#" NUMBER(22,0),

"NAME" VARCHAR2(30) NOT NULL ENABLE,

"LOGMNR_UID" NUMBER(22,0),

CONSTRAINT "LOGMNR_USER$_PK" PRIMARY KEY ("LOGMNR_UID", "USER#") DISABLE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

STORAGE(

BUFFER_POOL DEFAULT)

TABLESPACE "SYSAUX"

PARTITION BY RANGE ("LOGMNR_UID")

(PARTITION "P_LESSTHAN100" VALUES LESS THAN (100)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "SYSAUX" NOCOMPRESS )