ORA-01658: unable to create INITIAL extent for segment in tablespace TS_DATA

Question

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.

Answer 1

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

Answer 2

To check existing table spaces data file and size by following sql

select a.file_id,b.file_name,b.autoextensible,b.bytes/1024/1024,sum(a.bytes)/1024/1024
from dba_extents a , dba_data_files b
where a.file_id=b.file_id
group by a.file_id,b.file_name,autoextensible,b.bytes/1024/1024

Then run following sql, it would to make auto extend data file size.

ALTER DATABASE 
DATAFILE '/u01/app/oracle/oradata/XE/TS_DATA.dbf'
AUTOEXTEND ON
MAXSIZE UNLIMITED;