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博客 ” ，链接：https://blog.itpub.net/7364032/viewspace-368774/ ，如需转载，请注明出处，否则将追究法律责任。
