Beware 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, engaged in the day to day can happen to us and we can cause errors that can make us waste a lot of time until we solve it. 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.

Beware when deleting a tablespace in Oracle

Default Tablespaces

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

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

  • It can be assigned in the command to create a user:
CREATE USER admindb
IDENTIFIED BY admindb
DEFAULT TABLESPACE tbs_datos_admin
TEMPORARY TABLESPACE TEMP
PROFILE default;
  • If it is not assigned in the creation command, it is assigned the default tablespace of the database, 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 tablespace by default

When you want to delete a tablespace in Oracle, the engine does not check if there are users who have it assigned as a tablespace by default, so it can be deleted without any problem, taking into account the usual restrictions of 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

Deleted with DROP TABLESPACE statement

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 database

If the one you are trying to delete is the default tablespace of the database, it does check it and does not let us delete it, returning 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

Creating objects in deleted tablespace

Once the tablespace has been deleted, and verified that it is still the user’s default tablespace, we must 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 it does not exist, because 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 you found it useful.

Greetings.

Database computer

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 only one email per month.

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

Comments are closed.