WHERE TO_NUMBER(SUBSTR()) 我的是可以的

HUNTER@devjob>CREATE TABLE T_PART (ID NUMBER, CREATE_DATE DATE)
  2  PARTITION BY RANGE (CREATE_DATE)
  3  (
  4  PARTITION P2004 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
  5  PARTITION P2005 VALUES LESS THAN (TO_DATE('2006-1-1', 'YYYY-MM-DD')),
  6  PARTITION P2006 VALUES LESS THAN (TO_DATE('2007-1-1', 'YYYY-MM-DD')),
  7  PARTITION P2007 VALUES LESS THAN (TO_DATE('2008-1-1', 'YYYY-MM-DD'))
  8  );

Table created.

HUNTER@devjob>select table_name,partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
T_PART                         P2004
T_PART                         P2005
T_PART                         P2006
T_PART                         P2007

HUNTER@devjob>SELECT TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) FROM USER_TAB_PARTITIONS;

TO_NUMBER(SUBSTR(PARTITION_NAME,2))
-----------------------------------
                               2004
                               2005
                               2006
                               2007

HUNTER@devjob>SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS  WHERE TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) < 2006;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
T_PART                         P2005
T_PART                         P2004

HUNTER@devjob>SELECT * FROM
  2  (
  3  SELECT TABLE_NAME, PARTITION_NAME, TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) PART
  4  FROM USER_TAB_PARTITIONS
  5  )
  6  WHERE PART < 2006;

TABLE_NAME                     PARTITION_NAME                       PART
------------------------------ ------------------------------ ----------
T_PART                         P2005                                2005
T_PART                         P2004                                2004

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

转载于:http://blog.itpub.net/7364032/viewspace-368774/