性能调优之分区篇（二）

可通过查询USER_TAB_PARTITIONS视图来查询分区名单。[@more@]

 

SQL> desc user_tab_partitions;

 Name                                      Null?    Type

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

 TABLE_NAME                                         VARCHAR2(30)

 COMPOSITE                                          VARCHAR2(3)

 PARTITION_NAME                                     VARCHAR2(30)

 SUBPARTITION_COUNT                                 NUMBER

 HIGH_VALUE                                         LONG

 HIGH_VALUE_LENGTH                                  NUMBER

 PARTITION_POSITION                                 NUMBER

 TABLESPACE_NAME                                    VARCHAR2(30)

 PCT_FREE                                           NUMBER

 PCT_USED                                           NUMBER

 INI_TRANS                                          NUMBER

 MAX_TRANS                                          NUMBER

 INITIAL_EXTENT                                     NUMBER

 NEXT_EXTENT                                        NUMBER

 MIN_EXTENT                                         NUMBER

 MAX_EXTENT                                         NUMBER

 PCT_INCREASE                                       NUMBER

 FREELISTS                                          NUMBER

 FREELIST_GROUPS                                    NUMBER

 LOGGING                                            VARCHAR2(7)

 COMPRESSION                                        VARCHAR2(8)

 NUM_ROWS                                           NUMBER

 BLOCKS                                             NUMBER

 EMPTY_BLOCKS                                       NUMBER

 AVG_SPACE                                          NUMBER

 CHAIN_CNT                                          NUMBER

 AVG_ROW_LEN                                        NUMBER

 SAMPLE_SIZE                                        NUMBER

 LAST_ANALYZED                                      DATE

 BUFFER_POOL                                        VARCHAR2(7)

 GLOBAL_STATS                                       VARCHAR2(3)

 USER_STATS                                         VARCHAR2(3)

 

SQL> select table_name,partition_name,high_value,partition_position from user_tab_partitions

  2  where table_name='emp';

 

no rows selected

 

SQL> select table_name,partition_name,high_value,partition_position from user_tab_partitions

  2  where upper(table_name)='EMP';

 

TABLE_NAME                     PARTITION_NAME                                  

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

HIGH_VALUE                                                                     

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

PARTITION_POSITION                                                             

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

EMP                            P1                                               

1001                                                                           

                 1                                                             

                                                                                

EMP                            P2                                              

2001                                                                           

                 2                                                             

 

TABLE_NAME                     PARTITION_NAME                                  

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

HIGH_VALUE                                                                     

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

PARTITION_POSITION                                                             

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

                                                                                

EMP                            P3                                              

3001                                                                           

                 3                                                              

                                                                               

EMP                            P4                                              

10000                                                                           

 

TABLE_NAME                     PARTITION_NAME                                  

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

HIGH_VALUE                                                                      

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

PARTITION_POSITION                                                             

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

                 4                                                             

                                                                               

 

SQL> set linesize 100

SQL> select table_name,partition_name,high_value,partition_position from user_tab_partitions

  2  where upper(table_name)='EMP';

 

TABLE_NAME                     PARTITION_NAME                                                      

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

HIGH_VALUE                                                                       PARTITION_POSITION

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

EMP                            P1                                                                   

1001                                                                                              1

                                                                                                   

EMP                            P2                                                                  

2001                                                                                              2

                                                                                                    

EMP                            P3                                                                  

3001                                                                                              3

                                                                                                    

 

TABLE_NAME                     PARTITION_NAME                                                      

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

HIGH_VALUE                                                                       PARTITION_POSITION

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

EMP                            P4                                                                  

10000                                                                                             4

                                                                                                   

利用alter table 命令来增加，删除，移动，修改，更名，拆分和截断分区

下面是截断分区。

SQL> alter table emp

  2  truncate partition p2

  3  drop storage;

 

Table truncated.

 

SQL> select table_name,partition_name,high_value,partition_position from user_tab_partitions

  2  where upper(table_name)='EMP';

 

TABLE_NAME                     PARTITION_NAME                                                      

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

HIGH_VALUE                                                                       PARTITION_POSITION

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

EMP                            P1                                                                   

1001                                                                                              1

                                                                                                   

EMP                            P2                                                                  

2001                                                                                              2

                                                                &nbsp

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41224/viewspace-780374/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/41224/viewspace-780374/