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!

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
62,168 views