CREATE TABLE SEGMENT_SIZES(
SEGMENT_SIZES_ID NUMBER,
STIMESTAMP DATE,
OWNER VARCHAR2(30 BYTE),
SEGMENT_NAME VARCHAR2(30 BYTE),
PARTITION_NAME VARCHAR2(30 BYTE),
SEGMENT_TYPE VARCHAR2(20 BYTE),
BYTES NUMBER,
CONSTRAINT SEGMENT_SIZES_PK PRIMARY KEY (SEGMENT_SIZES_ID)
)
PARTITION BY RANGE (STIMESTAMP )
-- Use 11gR1 Interval Partitioning
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION P062013 VALUES LESS THAN (TO_DATE('01.07.2013','DD.MM.YYYY'))
)
TABLESPACE TOOLS
ENABLE ROW MOVEMENT
COMPRESS FOR ALL OPERATIONS;
It was populated with data, which created the needed partititions automatically.The primary key defined on the table will of course create a global index spanning all partitions.
Consequently, during partition maintenance operations, you will end up with an UNUSUABLE primary key index if you drop a partition, as follows:
ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42;
However, if you you add the "UPDATE INDEXES" clause, oracle will update the global index; the index will remain USABLE:
ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42 UPDATE INDEXES;
Keep in mind that you cannot drop the highest range partition of an interval-partitioned table!
Source: Oracle Documentation
No comments:
Post a Comment