在维护和管理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 )