0

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?

Improve this question
5
  • Are you on XE?? Commented Jan 17, 2022 at 10:42
  • Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production Commented Jan 17, 2022 at 10:48
  • 1
    How long is "endlessly"? The above command needs to allocate a 20 GB file which could take minutes if your infrastructure is slow. Commented Jan 17, 2022 at 11:47
  • Its been over 4hours now. Commented Jan 17, 2022 at 12:11
  • You are aware that 11.2.0.1 is buggy as anything. It could be a bug that was fixed in 11.2.0.2. Check MOS. Commented Jan 17, 2022 at 12:32

1 Answer 1

Reset to default
0

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.