Hi All,
I get the ORA-1652: unable to extend temp segment by 128 in tablespace TEMP error when I try to cerate index. However, the user that I try to create index is not in TEMP tablesace. It is using another tablespace. Moreover, I couldn't find the TEMP tablespace in DBA_DATAFILES view.
select file_id, tablespace_name, bytes from DBA_DATA_FILES
14 USERS 6050283520
16 ECOREDATA_1M 6442450944
18 ECOREDATA_1M 19327352832
19 ECOREDATA_1M 5368709120
1 SYSTEM 1142947840
2 SYSAUX 3206545408
3 UNDOTBS1 9229565952
4 ECOREDATA_1M 34309406720
5 ECOREDATA_1M 34309406720
6 ECOREDATA_1M 34321989632
7 ECOREINDEX_1M 1073741824
8 ECOREINDEX_1M 1073741824
10 USERS 6071255040
11 AUDITDATA 1073741824
12 ECORETEMP 1073741824
20 ECOREDATA_1M 6442450944
22 USERS 1073741824
23 SYSTEM 1073741824
select tablespace_name, bytes from dba_temp_files;
TEMP 10737418240
select sum(bytes)/1024/1024/1024 from DBA_DATA_FILES where tablespace_name = 'TEMP';
no rows return
select distinct tablespace_name from dba_segments where owner = 'MY_USER';
ECOREDATA_1M
Why I can't see TEMP tablespace in DBA_DATAFILES? What should I do in order to create an index without getting ORA-1652 error.
In addtion, I was tyring to create very big table in MY_USER user, after 16 hours I canceled it. Does it effect? If so, what should I do in order to delete unnecessary things in TEMP table_space?
Thanks