20

When i tried to create a table in my User_DB schema i am getting an error as ORA-01658: unable to create INITIAL extent for segment in tablespace TS_DATA. I run the following query to get all the TABLESPACE_NAME:

SELECT * FROM DBA_DATA_FILES;

But i really dont know which tablespace i am using and how to extend the tablespace to solve this issue.

Andrew
  • 2,942
  • 17
  • 49
  • 91
  • The tablespace you are using is TS_DATA, according to the error message; about extending it, you'd better contact your DBA – Aleksej May 06 '16 at 11:25
  • why do i need to conatact dba ? Is it not possible for me to resolve this issue. Because i have access to dba user – Andrew May 06 '16 at 11:28

1 Answers1

45

As the error message indicates, you're using the TS_DATA tablespace. You can extend it by either enlarging one of the existing data files:

ALTER DATABASE 
DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TS_DATA.DBF' 
RESIZE 3000M;

Or by adding a second datafile to the tablespace:

ALTER TABLESPACE ts_data 
ADD DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TS_DATA2.DBF' 
SIZE 1000M;

Or just allow the datafile to auto extend:

ALTER DATABASE 
DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TS_DATA2.DBF'
AUTOEXTEND ON
MAXSIZE UNLIMITED; -- Or some reasonable cap
Mureinik
  • 252,575
  • 45
  • 248
  • 283
  • Thanks after resizing the tablespace it worked for me ! – Akash5288 Apr 27 '18 at 07:02
  • also note that i got the message when exporting (expdp) which required more space. after altering as mentioned above, i added a specific amount that was not enough. so my expdp job suspended. when i changed again to unlimited (i a different window) it continued by itself after a minute and completed. – thedrs Jun 14 '20 at 08:46