Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to calculate TEMP tablespace size?

3018678Sep 15 2015 — edited Sep 16 2015

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 14 2015
Added on Sep 15 2015
30 comments
61,681 views