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