Temporary Tablespace

How to Resolve ORA-02231: missing or invalid option to ALTER DATABASE

by Ed Chen
December 22, 2022December 28, 2022

ORA-02231 ORA-02231 means that a keyword is missing from the position where it should be in ALTER DATABASE statement. Most likely, it's a syntax error.… Read More »

ORA-01187: cannot read from file because it failed verification tests

How to Resolve ORA-01187: cannot read from file

by Ed Chen
September 12, 2022March 20, 2023

ORA-01187 Found ORA-01187 and ORA-01110 after activating a standby database. ORA-01187: cannot read from file because it failed verification tests ORA-01110: data file 2003: '+DATA/ORCLCDB/ORCLPDB/temp01.dbf'… Read More »

Alter User Default Temporary Tablespace Examples

by Ed Chen
August 29, 2022September 12, 2022

Default Temporary Tablespace Normally, all users in the database use the same default temporary tablespace except that we specifically set another one for them. In… Read More »

Create Temporary Tablespace Examples

by Ed Chen
August 23, 2022February 12, 2023

Normally, we don't create additional temporary tablespace, because there's already a default one. For some reason, we need to create one for assigning the temporary… Read More »

Alter Temporary Tablespace Correctly

by Ed Chen
February 15, 2020February 12, 2023

Some statements about temporary tablespaces are NOT valid to perform. Create a Temporary Tablespace You can't create a temporary tablespace like a normal, permanent tablespace.… Read More »

How to Resolve ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

by Ed Chen
February 12, 2020October 10, 2021

ORA-03217 ORA-03217 means that the statement looks syntactically correct, but the action you performed is invalid for a temporary tablespace. Let's check the content of… Read More »

How to Alter Temporary Tablespace Offline

by Ed Chen
May 20, 2017March 20, 2023

Offline a Temporary Tablespace Theoretically, you can't take the whole temporary tablespaces offline. SQL> alter tablespace temp offline; alter tablespace temp offline * ERROR at… Read More »

Don't Forget to Add Tempfiles Back After Restoring 9i Database

by Ed Chen
August 28, 2016December 11, 2022

If your database is 10g onward, you don't have to worry about it, the control file will recreate the tempfiles after database restore, but for… Read More »

How to Resolve ORA-25153: Temporary Tablespace is Empty

by Ed Chen
March 14, 2015March 20, 2023
2 Comments

ORA-25153 After activating a physical standby database, I found some SQL statements returned ORA-25153: Description ORA-25153: Temporary Tablespace is Empty Cause An attempt was made… Read More »

How to Resolve ORA-01652: unable to extend temp segment by 8192 in tablespace

by Ed Chen
May 31, 2014November 13, 2021

ORA-01652 or ORA-1652 1. Permanent Tablespace SQL> INSERT INTO ERP35.PAY_HIST SELECT * FROM ERP35.PAY_TRANS; INSERT INTO ERP35.PAY_HIST SELECT * FROM ERP35.PAY_TRANS; * ERROR at line… Read More »