Be careful when deleting a tablespace in Oracle

Hello everyone, today we are going to tell you a curiosity that has happened to us in a client and that, although it may seem logical, engrossed in our day-to-day lives, it can happen to us and we can cause errors that can make us waste a lot of time until we do it. we solve. Although in concept it is not very complicated for anyone who is used to working with databases. It happens when we want to delete a tablespace in Oracle.

Default tablespaces

Every user in a database Oracle It has been assigned from the moment of its creation a tablespace where objects, such as tables and indexes, will be created by default. If no specific tablespace is specified when these objects are created.

This default tablespace is assigned to the user in two ways:

• It can be assigned in the user creation command:

CREATE USER admindb
IDENTIFIED BY admindb
DEFAULT TABLESPACE tbs_datos_admin
TEMPORARY TABLESPACE TEMP
PROFILE default;

• If it is not assigned in the create command, it is assigned the default DB tablespace, which can be found in the catalog view database_properties :

SQL> select * from database_properties
 2   where property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace

Delete a default tablespace

When you want to delete a tablespace in Oracle, the engine does not check if there are users who have it assigned as tablespace by default, so it can be deleted without any problem, taking into account the usual restrictions on deleting tablespaces.

SQL> select username, account_status, default_tablespace, temporary_tablespace
 2   from dba_users
 3   where username='ADMINDB';

USERNAME                       ACCOUNT_STATUS                   DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ -------------------------------- ------------------------------ ------------------------------
ADMINDB                        OPEN                             TBS_DATOS_ADMIN                TEMP

It is erased with the sentence DROP TABLESPACE

SQL> DROP TABLESPACE tbs_datos_admin INCLUDING CONTENTS AND DATAFILES;

Tablespace borrado.

Once deleted, the user still has it as the default tablespace.

SQL> select username, account_status, default_tablespace, temporary_tablespace
 2   from dba_users
 3   where username='ADMINDB';

USERNAME                       ACCOUNT_STATUS                   DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ -------------------------------- ------------------------------ ------------------------------
ADMINDB                        OPEN                             TBS_DATOS_ADMIN                TEMP

Delete a default tablespace from the DB

If the one to be deleted is the default tablespace of the DB, yes it checks it and does not let us delete it , returning us an error.

SQL> DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

ERROR en lÝnea 1:
ORA-12919: No se puede borrar el tablespace permanente por defecto

Create objects in deleted tablespace

Once the tablespace has been deleted, and verified that it is still the user’s default tablespace, be careful because if you want to create an object and the storage clause is not specified, when you go to create it in the default tablespace, and this not exist, as it returns an error not allowing us to create the object.

SQL> connect admindb/admindb
Conectado.
SQL> CREATE TABLE tpru1 (
  2  cprue1 number,
  3  cprue2 varchar2(100)
  4  );
CREATE TABLE tpru1 (
*
ERROR en lÝnea 1:
ORA-00959: el tablespace 'TBS_DATOS_ADMIN' no existe

We hope it has been useful to you.

Greetings.

Database Team

If you don’t want to miss tricks like this and stay up to date with this world, subscribe to our newsletter . We will keep you informed with a single email per month.

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn

If you have questions about its administration or related to Oracle or SQL Server, do not hesitate to contact us that we will be happy to help !!