TNPMW Primary Key in Partition IOT table not unique

Resolving The Problem

In order to fix this issue, it is recommended to modify primary key for WML_LOADED_TABLE_INTERVAL table to add another field known as frequency_id as a part of the composite key for PK_WML_LOADED_TABLE_INTERVAL constraint.

Note : Please consult your admin or DBA or support team if needed before doing these changes.It involves redefining constraint for a table and feasibility depends on size of the table.


As the table already contain data and it is on Oracle IOT partition table type it is recommended to use DBMS_REDEFINITON package to minimize system downtime.

CAUTION - Please complete reading the whole technote before attempting this task and do try it on a test server prior to production server !

Below are the steps to perform the solution:

A) Initialization
i. shutdown all loaders

ii. connect to sqlplus

sqlplus /nolog conn sys/<SYS Password>@<DB_Name> as sysdba

Note :

When you login to ORACLE sqlplus - ensure that you're looking at the VTDB oracle instance.

To ensure this :

At Unix Prompt - prior to sqlplus : export ORACLE_SID=vtdb
At SQL Prompt -
i) verify that this returns vtdb : select * from v$instance;
ii) verify that this returns SYS : show user

Then only proceed with the next steps on the Technote.


iii. grant execute pl/sql package to virtuo

SQL>GRANT execute ON dbms_redefinition TO virtuo;

iv. determines if table can be redefined online

SQL>exec DBMS_REDEFINITION.CAN_REDEF_TABLE('virtuo','WML_LOADED_TABLE_INTERVAL',DBMS_REDEFINITION.CONS_USE_PK);

Note: Only proceed to step B) & C) if above step does not produce any error, the last step should see "PL/SQL procedure successfully completed."

sample output


B) Preparation
i. connect to sqlplus

sqlplus virtuo@<DB_Name>

ii. create interim table and indexes

SQL>@create_interim_table.sql SQL>@create_interim_idx.sql

sample output



Note: To create files create_interim_table.sql and create_interim_idx.sql refer Appendix A

Tip: If got error “ORA-01536: space quota exceeded for tablespace” when running “@create_interim_table.sql” do below

sqlplus /nolog SQL>conn sys/<SYS_Password>@<DB_Name> as sysdba SQL>alter user virtuo quota unlimited on traffic_jumbo_ovfw;

C) Performing the IOT table reorganization
i. connect to sqlplus

sqlplus /nolog conn sys/<SYS Password>@<DB_Name> as sysdba

Note :

When you login to ORACLE sqlplus - ensure that you're looking at the VTDB oracle instance.

To ensure this :

At Unix Prompt - prior to sqlplus : export ORACLE_SID=vtdb
At SQL Prompt -
i) verify that this returns vtdb : select * from v$instance;
ii) verify that this returns SYS : show user

Then only proceed with the next steps on the Technote.


ii. start redefinition

SQL>set timing on SQL>set serveroutput on SQL>EXEC DBMS_REDEFINITION.START_REDEF_TABLE('virtuo', 'WML_LOADED_TABLE_INTERVAL', 'WM2_LOADED_TABLE_INTERVAL', - 'VENDOR_ID VENDOR_ID, TABLE_NAME TABLE_NAME, FREQUENCY_ID FREQUENCY_ID, START_TIME START_TIME, END_TIME END_TIME', - DBMS_REDEFINITION.CONS_USE_PK);

Note: Dash (-) in the command means line continuation. Be careful during cut and paste the command (refer sample output)

iii. synchronization

SQL>EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('virtuo', 'WML_LOADED_TABLE_INTERVAL', 'WM2_LOADED_TABLE_INTERVAL');

iv. end redefinition

SQL>EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('virtuo', 'WML_LOADED_TABLE_INTERVAL', 'WM2_LOADED_TABLE_INTERVAL');

sample output


D) Post action (for performance)

i. Recollect statistic for WML_LOADED_TABLE_INTERVAL table (was reset during redefinition)

sqlplus /nolog SQL>conn sys/<SYS_Password>@<DB_Name> as sysdba SQL> exec dbms_stats.gather_table_stats('VIRTUO', 'WML_LOADED_TABLE_INTERVAL', - estimate_percent => 20, cascade => true, method_opt => 'for all columns size 1');

sample output:


Note: Run below query (virtuo user) to check last_analyzed column is not null

SQL> select table_name,sample_size,last_analyzed from user_tables where table_name = 'WML_LOADED_TABLE_INTERVAL';

Appendix A

Below steps will auto generate file to create interim table and needs to be executed at the database server where the issue happen (usually it was done by L3 by requesting table partition list from L2/customer). a) Install the 2 PL/SQL scripts which are attached below (must be in order) sqlplus virtuo@<DB_Name> SQL>@GEN_INTERIM_TABLE.sql SQL>@GEN_INTERIM_IDX.sql Sample output: b) Run the installed pl/sql script sqlplus virtuo@<DB_Name> SQL> set lines 180 SQL> set feedback off SQL> set head off SQL> set pages 0 SQL> set serveroutput on buffer 2560000 SQL> set trimspool on SQL> spool create_interim_table.sql SQL> exec GEN_INTERIM_TABLE; …. SQL> spool off SQL> spool create_interim_idx.sql SQL> exec GEN_INTERIM_IDX; …. SQL> spool off SQL> exit c) Edit the spool output files create_interim_table.sql and create_interim_idx.sql e.g vi create_interim_table.sql remove following 2 lines SQL> exec GEN_INTERIM_TABLE; SQL> spool off vi create_interim_idx.sql remove following 2 lines SQL> exec GEN_INTERIM_IDX; SQL> spool off Note : Now the file create_interim_table.sql and create_interim_idx.sql is ready to be executed in step B)