Oracle is one of the most popular Relational Database Management Systems (RDBMS) that offers a variety of features to manage the data, including the ability to manage the sequences. Sequences are objects that generate unique numbers in a sequential order, often used as primary key values. While working in the Oracle database, deleting a sequence may be necessary for database maintenance or schema changes.
This article will cover how to delete sequence using:
How to Delete Sequences in Oracle?
To delete a sequence in the Oracle database, first, log in to the Oracle database using the SQLPLUS utility:
In the above command “C##MD” is the username and “md1234” is the password of the database that is being used for this post.
Output
The output depicts a success message that the database has been connected successfully.
Before deleting the sequence in the Oracle database, let’s list all sequences available in the database.
Check the Sequence Name in Oracle
To check the list of available sequences in the Oracle database run the query given below:
In the above command “ALL_SEQUENCES” is the name of the table that contains the details of sequences.
Output
The output retrieved the list of sequences available in the Oracle database.
How to Delete Sequence Using the DROP Command in Oracle?
To delete a sequence in the Oracle database, the “DROP” command can be used with the “SEQUENCE” clause followed by the sequence name. Type the given below command to drop the “LINUXHINT_SEQ” sequence:
Output
The output returned a “Sequence dropped” message after deleting the sequence successfully.
How to Delete Sequence Using a PL/SQL Block in Oracle?
The sequence can be dropped by using the PL/SQL block. Here is an example of a PL/SQL block to delete a sequence:
SEQUENCE_NAME VARCHAR2(100) := 'LINUXHINT_SEQUENCE';
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || SEQUENCE_NAME;
END;
In the above code block, DECLARE is used to declare the sequence name and in the BEGIN block the DROP command is used to delete the “LINUXHIT_SEQUENCE”.
Output
The output depicts that the sequence has been dropped.
How to Delete Sequence Using SQL Developer in Oracle?
To drop the sequence using SQL developer, login to the Oracle database, expand the database to view all entities, and find the “Sequences” from the list:
Expand the sequence, select the sequence name by right-clicking on it, and then click on “Drop…” to delete the sequence from the specific database:
Confirm the “Name” and click on the “Apply” button:
The confirmation prompt showed that the sequence has been dropped, to continue working on the database click on the “OK” button:
This is how to delete the sequence in Oracle using different methods.
Conclusion
The sequence can be deleted in Oracle using the “DROP” command, “PL/SQL” code block, or the “SQL developer” tool. In the DROP command, the sequence name can be specified by using the SEQUENCE clause, and in PL/SQL code block, the sequence name can be declared using the SEQUENCE_NAME column. The SQL developer tool shows the list of sequences from which you can delete the sequence. By following the steps explained in this article, you can safely delete sequences in Oracle.
