间隔分区表的查询
select * from interval_sales partition for(to_date('1-1-2009','DD-MM-YYYY'));
如果知道PARTITION_NAME,那么可以直接用下面方法进行查询分区
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TRANSACTIONS SYS_P315 TED
TRANSACTIONS SYS_P316 TED
TRANSACTIONS SYS_P317 TED
TRANSACTIONS P_BEFORE_2007 TED
查询指定分区
SQL> select * from transactions partition sys_p315; /* 注意,这里的关键词为partition而不是partition for */
如果不知道partition_name,可使用下面方法
ALTER TABLE sales DROP PARTITION FOR(TO_DATE('01-SEP-2007','dd-MON-yyyy'));
如果知道系统自动产生的partition_name,可以用下面方法来删除
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TRANSACTIONS SYS_P315 TED
TRANSACTIONS SYS_P316 TED
TRANSACTIONS SYS_P317 TED
TRANSACTIONS P_BEFORE_2007 TED
SQL> alter table transactions drop partition sys_p315; /* 注意,这里的关键词为partition而不是partition for */
SQL> alter table transactions drop partition sys_p316;