Adding new datafile to tablespace but no datafile being created



  • I recently faced the following error:

    ORA-01688: unable to extend table RATEL.MV_IN_GPRS_SPLIT partition SYS_P54817 by 128 in tablespace RATEL" while loading data into the database.

    To resolve it I am trying to add a new datafile on Oracle 11g using the following query:

    ALTER TABLESPACE RATEL 
        ADD DATAFILE 'e:\app\oracle\oradata\rvas\RATEL141.dbf' 
        SIZE 20000M 
        AUTOEXTEND ON 
        MAXSIZE 32000M;
    

    However, the query is executing endlessly and no datafile has been created.

    How can I verify if the datafile has been created or not? What would cause a datafile to not be created?



  • No solution, but possible things to look at.

    ALERT_.LOG (ALERT.LOG) File Specifically

    Have a look at the alert.log file for your instance. It should contain lines that look a bit like this after you sent the command:

    2022-01-07T08:37:09.455525+01:00
    ALTER TABLESPACE RATEL 
      ADD DATAFILE 'e:\app\oracle\oradata\rvas\RATEL141.dbf'
      SIZE 20000M
      AUTOEXTEND ON
      MAXSIZE 32000M
    2022-01-07T08:37:48.127440+01:00
    

    This will be followed by an entry for completion:

    Completed: ALTER TABLESPACE RATEL 
      ADD DATAFILE 'e:\app\oracle\oradata\rvas\RATEL141.dbf'
      SIZE 20000M
      AUTOEXTEND ON
      MAXSIZE 32000M
    

    If you don't see the above lines, then the command might not have been sent and/or is waiting for some kind of acknowledgement.

    ALERT_.LOG (ALERT.LOG) In General

    After receiving the error that the tablespace had run full, are there any other entries in the alert.log file that tell you what might be failing?

    Disk Capacity

    Does the disk where you are creating the additional datafile have enough capacity for the new file?

    If not, chose a different location/drive with enough capacity.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2