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

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

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

Column	Datatype	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^Foot 1 QUERY HIGH^Footref 1 ARCHIVE LOW^Footref 1 ARCHIVE HIGH^Footref 1 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^Foot 2	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	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

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 )

ITPub博客

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

最新文章