CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Puneet Kushwah
Puneet Kushwah

Reputation: 1600

What is the maximum size of temp tablespace in oracle database?

Trying to execute the next command

SELECT * FROM dba_temp_free_space

I am getting an error:

Error: ORA-01652: unable to extent temp segment by 128 in tablespace temp

and there are 32 GB of space is free, but when I ran a procedure I've got an error. When I check autoextending of the temp tablespace, it says YES..

What should I do to get maximum size of temp tablespace?

Upvotes: 1

Views: 8580

Answers (1)

kevinskio
kevinskio

Reputation: 4551

I think you are focusing on a solution, not the problem. A 32 GB tablespace should be adequate for most operations in a small OLTP database. You have not told us much about what is running when you get the error so I can only supply some diagnostics and test queries. The maximum size of any tablespace could be as much as 128 Terabyte or as small as 32 Terabyte with 8k block size.

Try this query while your problem object is running:

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

If you cannot change the problem object you can create a temporary tablespace group and add multiple temporary tablespaces to it.

Be sure to check what temporary tablespace the user who is running the object is using with a command like this:

ALTER USER scott TEMPORARY TABLESPACE temp;

Upvotes: 3

Related Questions