25

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
  • 3,632
  • 24
  • 64
  • 113
  • 1
    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
    what does he do if already he is a dba admin? we should give him the answer without any other help. – java dev Jul 23 '22 at 20:41

3 Answers3

53

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
  • 297,002
  • 52
  • 306
  • 350
  • 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
3

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;
Julfiker
  • 268
  • 3
  • 9
2

If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:

  1. add a new datafile to the tablespace, for example:

alter tablespace xyz add datafile '/path/to/datafiles/xyz02.dbf' size 100M autoextend on next 100M maxsize unlimited;

Juan Aguilera Abarzua
  • 21
  • 1