ALTER TABLESPACE

How to Resolve ORA-27038: created file already exists

by Ed Chen
December 29, 2021May 18, 2022

ORA-27038 Tried to add a data file to a tablespace, but it failed with ORA-27038. SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example03.dbf' size 10m autoextend… Read More »

How to Resolve ORA-01537: cannot add file

by Ed Chen
December 27, 2021June 27, 2022

ORA-01537 Tried to add a data file to a permanent tablespace, but it failed with ORA-01537. SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example01.dbf' size 10m… Read More »

How to Resolve ORA-00905: missing keyword

by Ed Chen
December 24, 2021April 20, 2023
4 Comments

ORA-00905 ORA-00905 is a very broadly used error message, any expected keyword missing from its statement will result in ORA-00905. These are only cases we… Read More »

How to Resolve ORA-02142: missing or invalid ALTER TABLESPACE option

by Ed Chen
December 22, 2021April 7, 2022

ORA-02142 Tried to add a new data file to a tablespace, but it failed with ORA-02142. SQL> ALTER TABLESPACE EXAMPLE DATAFILE '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' SIZE 10M AUTOEXTEND… Read More »

How to Resolve ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range

by Ed Chen
November 25, 2020December 9, 2022

ORA-03206 Theoretically, we can add a data file with maximum size of 32 GB to a tablespace with 8 KB block size. Actually, we can't.… 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 Empty an Oracle Database

by Ed Chen
January 8, 2020March 20, 2023

Empty a Database In my case, a phased out database server is going to transfer to another project team, my job is to remove all… Read More »

How Max a Data File Can Be

by Ed Chen
October 2, 2019February 12, 2023

DataFile MaxSize According to Oracle Database Physical Database Limits, we can have approximate 222 database blocks for a data file in a smallfile tablespace (default)… 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 »

JavaScript is not enabled. Please enable it and reload the page.