Scenario:
A tablespace has been created in the database through a script using Mixed case.Now we want to drop the tablespace.
SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS -------------------------- --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE PVPRSpon_CDM_INDEX ONLINE
SQL> drop tablespace PVPRSpon_CDM_INDEX; drop tablespace PVPRSpon_CDM_INDEX * ERROR at line 1: ORA-00959: tablespace 'PVPRSPON_CDM_INDEX' does not exist
We are unable to drop the tablespace even if it is available. The reason is while creating the tablespace the name was specified in mixed case within double quotes as follows
SQL> create tablespace "PVPRSpon_CDM_INDEX" ....
SQL> SELECT tablespace_name FROM dba_tablespaces WHERE LOWER(tablespace_name) = 'PVPRSPON_CDM_INDEX'; no rows selected
SQL> SELECT tablespace_name FROM dba_tablespaces WHERE UPPER(tablespace_name) = 'PVPRSPON_CDM_INDEX'; TABLESPACE_NAME ------------------------------ PVPRSpon_CDM_INDEX
Solution:
To drop the tablespace mention the tablespace name within double quotes as shown below.
SQL> drop tablespace "PVPRSpon_CDM_INDEX" INCLUDING CONTENTS; Tablespace dropped.
Now the tablespace stands dropped.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez
Anuradha’s LinkedIn: https://www.linkedin.com/in/dbaanuradhamudgal/
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
