How do I move records to respective partitions after I do an exchange partition, splitting the exchanged partition doesnt seem to be working
After exchanging partition with a table, the partitioned table does not have the records distributed across the appropriate partitions but it just resides in a same partition in which it was swapped--DROPPING EXISTING TABLE
DROP TABLE requests PURGE;
--CREATING NON-PARTITIONED TABLE
CREATE TABLE requests (
reqsource NUMBER,
reqtime TIMESTAMP(6),
req CLOB
)
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
--TABLESPACE "REQ_DATA"
LOB ( "REQ" ) STORE AS BASICFILE (
-- TABLESPACE "REQ_DATA"
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE LOGGING
STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
);
--DROPPING EXISTING TABLE
DROP TABLE requests_part;
--CREATING PARTITIONED TABLE
CREATE TABLE requests_part (
reqsource NUMBER,
reqtime TIMESTAMP(6),
req CLOB
)
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
--TABLESPACE "REQ_DATA"
LOB ( "REQ" ) STORE AS BASICFILE (
--TABLESPACE "REQ_DATA"
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE LOGGING
STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
)
-- MONTHLY PARTITIONED ON TIMESTAMP COLUMN
PARTITION BY RANGE (
"REQTIME"
) INTERVAL ( numtoyminterval(1, 'MONTH') ) ( PARTITION "OLD_DATA"
VALUES LESS THAN ( TIMESTAMP ' 2013-01-01 00:00:00' )
--TABLESPACE "REQ_DATA"
);
ALTER TABLE requests_part EXCHANGE PARTITION old_data WITH TABLE requests WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
SELECT
*
FROM
user_tab_partitions
WHERE
table_name = 'REQUESTS_PART';
<b>TABLE_NAME COMPOSITE PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION</b>
REQUESTS_PART NO OLD_DATA TIMESTAMP' 2013-01-01 00:00:00' 31 1
All the rows are existing only in the partition OLD_DATA, no new partitions are created. When I tried to split that partition I'm getting the below error
ALTER TABLE results_part
SPLIT PARTITION
OLD_DATA
FOR(
TIMESTAMP' 2013-02-01 00:00:00'
-- TO_DATE('01-MAY-2007','dd-MON-yyyy')
)
AT (
-- TO_DATE('15-MAY-2007','dd-MON-yyyy')
TIMESTAMP' 2013-01-01 00:00:00'
)
Error report -
ORA-14080: partition cannot be split along the specified high bound
14080. 00000 - "partition cannot be split along the specified high bound"
*Cause: User attempted to split a partition along a bound which
either collates higher than that of the partition to be split or
lower than that of a partition immediately preceding the one
to be split
*Action: Ensure that the bound along which a partition is to be split
collates lower than that of the partition to be split and
higher that that of a partition immediately preceding the one
to be split
How do i distribute the data in the exchanged partition across various partitions????
The issue is you're doing this:
without validation
So the database doesn't check that the rows you're swapping belong in the target partition; it trusts that you've verified this beforehand.
If you don't you can end up with rows in the wrong partition:
create table t (
c1 int, c2 int
);
create table tpart (
c1 int, c2 int
) partition by range ( c1 )
interval ( 10 ) (
partition p0 values less than ( 11 )
);
insert into t
with rws as (
select level x from dual
connect by level <= 20
)
select x, x
from rws;
commit;
alter table tpart
exchange partition p0
with table t;
ORA-14099: all rows in table do not qualify for specified partition
alter table tpart
exchange partition p0
with table t
without validation;
select count (*), max ( c1 )
from tpart
partition ( p0 );
COUNT(*) MAX(C1)
20 20 So we have rows with the value 20 in p0, despite it's upper bound being 11!
You can only exchange all the rows in a table with one partition at a time. If you have rows you want to load into many partitions, you could:
- Create one partition with a high enough upper bound to store all the values, then split this partition after the exchange
- Run the exchange process many times, inserting just the rows for each partition to the exchange table, running the exchange and repeating for the next.