Database Tuning (MOSC)

MOSC Banner

Unable to redefine a large table with DBMS_REDEFINITION

in Database Tuning (MOSC) 3 commentsAnswered ✓

BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE (

  uname         => 'xxxx', 

  orig_table      => 'BIG_TABLE', -- range interval on date

  int_table       => 'BIG_TABLE_TMP', -- changing to range interval on new key

  options_flag     => DBMS_REDEFINITION.CONS_USE_ROWID,  -- has no pk

  orderby_cols     => 'PCI',

  continue_after_errors => FALSE,   

  enable_rollback    => TRUE);

END;

/  

Fails after running for several hours with :

ORA-1653: unable to extend table ENCTR.SYS_RMTAB$$_H137121 by 8192 in tablespace SYSTEM

On demand

MV owner.BIG_TABLE_TMP was not refreshed successfully.

Number of MV refresh failures: 1.

Encountered error ORA-12008.

  1. Why is this job writing to SYSTEM?
  2. SYSTEM is defined with autoextensible and there is free space on the mount so why is this small write failing?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center