This discussion is archived
2 Replies Latest reply: Aug 17, 2012 9:44 AM by rp0428 RSS

Oracle Exchange partition feature not working as expected?

User252311 Newbie
Currently Being Moderated
I used the Exchange Partition feature to swap segments between 2 tables- one Partitioned, and one Non-Partitioned. The exchange went well. However, all the data in the partitioned table has gone to the partition which stores the maxbound values.



Any ideas if this is the default behavior or have i missed out something?







/** actual table names changed due to client confidentiality issues */



-- Drop the 2 intermediate tables if they already exist



drop table ordered_inv_bkp cascade constraints ;



drop table ordered_inv_t cascade constraints ;

/**

1st create a Non-Partitioned Table from ORDERED_INV and then add the primary key and unique index(s):

*/

create table ordered_inv_bkp as select * from ordered_inv ;



alter table ordered_inv_bkp add constraint ordinvb_pk primary key (ordinv_id) ;

--

create unique index ordinv_scinv_uix on ordered_inv_bkp(

SCP_ID ASC,

INV_ID ASC,

ANATLOC_ID ASC,

SOI_ID ASC,

CANCEL_TS ASC );





-- Next, we have to create a partitioned table ORDERED_INV_T with a similar

-- structure as ORDERED_INV.

-- This is a bit tricky, and involves a pl/sql code





declare

l_dt_start DATE;

l_ptn VARCHAR2(50);

cnt PLS_INTEGER;

l_cnt_initial PLS_INTEGER;

ts_name VARCHAR2(50);

l_sql VARCHAR2(10000);

ts_indx VARCHAR2(100);

l_num_errors NUMBER ;

l_num_errors_ok NUMBER ;

l_user_name VARCHAR2(50) ;

l_sysdate VARCHAR2(50);

l_cnt_script PLS_INTEGER ;



BEGIN

--

SELECT COUNT(*) INTO cnt FROM user_TABLES

WHERE TABLE_NAME='ORDERED_INV_T';



IF cnt=0 THEN

l_sql:= 'CREATE TABLE ORDERED_INV_T

PARTITION BY RANGE (ORDINV_TIME)

( PARTITION TP_ORDINV_MAX VALUES LESS THAN (MAXVALUE)

)

TABLESPACE TEST_TPT_DATA

ENABLE ROW MOVEMENT

AS SELECT * FROM ORDERED_INV WHERE 1=0 ';



EXECUTE IMMEDIATE l_sql;



-- Add section to set default values for the intermediate table OL_ORDERED_INV_T

FOR crec_cols IN (

SELECT u.column_name ,u.nullable, u.data_default,u.table_name

FROM USER_TAB_COLUMNS u WHERE

u.table_name ='ORDERED_INV' AND

u.data_default IS NOT NULL )

LOOP

l_sql:= 'ALTER TABLE ORDERED_INV_T MODIFY '||crec_cols.column_name||' DEFAULT '||crec_cols.data_default;

-- dbms_output.put_line('chk data default => ' || l_sql) ;

EXECUTE IMMEDIATE l_sql;

END LOOP;



END IF;



-- Split partition to create more partitions

--

select TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') into l_sysdate from dual;

DBMS_OUTPUT.PUT_LINE ('Finding oldest value at ' || l_sysdate) ;



EXECUTE IMMEDIATE 'SELECT NVL(TRUNC(MIN(OL_ORDINV_TIME),''MONTH''),TRUNC(SYSDATE,''MONTH''))

FROM ORDERED_INV' INTO l_dt_start;



select TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') into l_sysdate from dual;

DBMS_OUTPUT.PUT_LINE ('Started creating partitions at ' || l_sysdate) ;



LOOP

EXIT WHEN l_dt_start > ADD_MONTHS(TRUNC(SYSDATE,'MONTH'),1);

l_ptn:='tp_ordinv_'|| TO_CHAR(l_dt_start,'YYYYMM');



l_sql:= 'ALTER TABLE ORDERED_INV_T

split partition TP_ORDINV_MAX at (TO_DATE('''|| TO_CHAR(ADD_MONTHS(l_dt_start,12),'YYYYMM') ||''',''YYYYMM''))

into ( partition '||l_ptn||' , partition TP_ORDINV_MAX)';



execute immediate l_sql;



l_dt_start:=add_months(l_dt_start,12);



END LOOP;

END;

/

-- Also, add indexes to this table



alter table ORDERED_INV_T add constraint ordinvt_pk primary key (ordinv_id) ;

--

create unique index ordinvt_uix on ordered_inv_t(

SCP_ID ASC,

INV_ID ASC,

ANATLOC_ID ASC,

SOI_ID ASC,

CANCEL_TS ASC );





-- Next, use exchange partition for actual swipe

-- Between ordered_inv_t and ordered_inv_bkp

-- Analyze both tables : ordered_inv_t and ordered_inv_bkp



BEGIN

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HENRY220', TABNAME => 'ORDERED_INV_T');

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HENRY220', TABNAME =>'ORDERED_INV_BKP');

END;

/



SET TIMING ON;

ALTER TABLE ordered_inv_t

EXCHANGE PARTITION TP_ORDINV_MAX

WITH TABLE ordered_inv_bkp

WITHOUT VALIDATION

UPDATE GLOBAL INDEXES;





--

-- Check query :



select partition_name, num_rows, high_value from user_tab_partitions where table_name = 'ORDERED_INV_T' ;



These are the results:



TP_ORDINV_199801 0 TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_199901 0 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_200001 0 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_200101 0 TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_200201 0 TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_200301 0 TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_200401 0 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_200501 0 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_200601 0 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_200701 0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_200801 0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_200901 0 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_201001 0 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_201101 0 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_201201 0 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TP_ORDINV_MAX 24976 MAXVALUE



Pay attention to the last record
  • 1. Re: Oracle Exchange partition feature not working as expected?
    Solomon Yakobson Guru
    Currently Being Moderated
    872515 wrote:

    TP_ORDINV_MAX 24976 MAXVALUE
    Pay attention to the last record
    Are you referring to num_rows = 24976? Column num_rows is statistical column it is populated when you gather table stats. So all num_rows = 24976 indicates is you collected stats on a single partition table with num_rows = 24976. Then you exchanged partition. That doesn't update stats automatically. So you end up with stale stats. Now you need to collect stats after and you'll see correct values.

    SY.

    Edited by: Solomon Yakobson on Aug 17, 2012 7:41 AM
  • 2. Re: Oracle Exchange partition feature not working as expected?
    rp0428 Guru
    Currently Being Moderated
    >
    used the Exchange Partition feature to swap segments between 2 tables- one Partitioned, and one Non-Partitioned. The exchange went well. However, all the data in the partitioned table has gone to the partition which stores the maxbound values.
    >
    That isn't possible. The data in the partition before the exchange could only have gone to the non-partitioned table.

    Please edit you post and add \
     tags on the lines before and after your code to preserve formatting. See the FAQ for details.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points