Tablespace

How to List Tables or Indexes in a Tablespace

by Ed Chen
June 3, 2022June 3, 2022

We have talked about the differences between schema objects and segments in another post. From tablespace's point of view, program units and informative schema objects… Read More »

Which Tablespace Contains the Table or Index?

by Ed Chen
June 1, 2022June 3, 2022

Table Resides in What Tablespace? To better manage overall tablespace usage, we'd like to know which tablespace specific table or index is in before moving… Read More »

How to Check Tablespace Creation Date

by Ed Chen
May 30, 2022June 10, 2022

Tablespace Create Time There's no direct way to know the creation date of a tablespace, because either DBA_TABLESPACES or V$TABLESPACE doesn't have such information. But… Read More »

How to Resolve ORA-00959: tablespace does not exist

by Ed Chen
May 25, 2022April 12, 2022

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

How to Resolve ORA-02140: invalid tablespace name

by Ed Chen
May 23, 2022June 27, 2022

ORA-02140 There're 2 types of ORA-02140 when we tried to add a data file to a tablespace. 1. Use a Keyword as Tablespace Name We… Read More »

How to Resolve ORA-02216: tablespace name expected

by Ed Chen
May 20, 2022May 23, 2022

ORA-02216 ORA-02216 means that SQL parser finds no identifier at the position where expects a tablespace name. Basically, it's a syntax error. To correct it,… Read More »

How to Resolve ORA-02180: invalid option for CREATE TABLESPACE

by Ed Chen
May 19, 2022May 22, 2022

ORA-02180 There're 2 basic patterns of error ORA-02180 in this post: Missing Tablespace Name Missing Keyword A. Missing Tablespace Name If we didn't specifiy the… Read More »

How to Resolve ORA-22868: table with LOBs contains segments in different tablespaces

by Ed Chen
January 26, 2022January 28, 2022

ORA-22868 Saw error ORA-22868 when I tried to drop a tablespace cascadingly. SQL> drop tablespace ERP_TBS01 including contents and datafiles cascade constraints; drop tablespace ERP_TBS01… Read More »

How to Resolve ORA-23515: materialized views and/or their indices exist in the tablespace

by Ed Chen
January 24, 2022March 8, 2022

ORA-23515 Tried to drop a tablespace from the database in order to reclaim some space, but it failed with ORA-23515. SQL> drop tablespace example including… Read More »

How to Resolve ORA-29857: domain indexes and/or secondary objects exist in the tablespace

by Ed Chen
January 21, 2022January 22, 2022

ORA-29857 Tried to drop a tablespace from the database in order to reclaim some space, but it failed with ORA-29857. SQL> drop tablespace example including… Read More »