How to delete users and tablespaces in Oracle under Linux

The example in this article describes how to delete users and table spaces in Oracle under Linux. Share it with everyone for your reference, the details are as follows:

1. Delete a user

SQL> conn /as sysdba
Connected.
SQL> drop user userName cascade;

The user has been deleted

If the user cannot be deleted and an error is reported:

ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

View the user’s progress and kill the user process , and then delete the user.

SQL> select sid,serial# from v$session where username='userName';
SID  SERIAL#
---------- ----------
150    9019
SQL> alter system kill session '150,9019';
System altered.

2. Delete the table space

Prerequisite: Before deleting the table space, make sure that the table space is not used by other users before deleting it.

drop tablespace nsName including contents and datafiles cascade constraints ;

including contents Delete the contents in the table space. If there is content in the table space before deleting the table space, but this parameter is not added, the table space cannot be deleted, so it is customary to add this parameter.
including datafiles Delete the data files in the table space.
cascade constraints Also delete foreign key references to tables in tablespace.

I hope this article will be helpful to everyone in Oracle database programming.

For more related articles on how to delete users and table spaces in Oracle under Linux, please pay attention to the PHP Chinese website!