DROP

In Oracle, the DROP statement is used to remove database objects like tables, views, indexes, sequences, or even entire schemas. When we talk about "dropping notes", it typically refers to removing comments (i.e., notes or descriptions) that were added to various schema objects (like tables, columns, views, etc.) using the COMMENT ON statement.

In this case, the DROP statement does not apply to comments directly. Instead, Oracle provides a method to "drop" comments (i.e., remove them) by setting the comment to NULL using the COMMENT ON statement itself.

Here’s a detailed guide on how to drop comments (notes) in Oracle.

How to Drop Comments (Notes) in Oracle

To remove a comment (drop a note) from an object (table, column, view, sequence, etc.), you use the COMMENT ON statement, setting the comment to NULL.

Syntax to Drop a Comment (Note)

COMMENT ON <object_type> <object_name> IS NULL;

·        <object_type>: The type of object you are removing the comment from (e.g., TABLE, COLUMN, INDEX).

·        <object_name>: The name of the object (e.g., employees, salary).

·        IS NULL: This will remove any existing comment associated with the object.

Examples of Dropping Comments (Notes)

1. Dropping a Comment from a Table

If you have added a comment to a table (e.g., the employees table), you can remove it with the following command:

COMMENT ON TABLE employees IS NULL;

This will remove the comment from the employees table.

2. Dropping a Comment from a Column

If a column (e.g., salary column in the employees table) has a comment, it can be dropped as follows:

COMMENT ON COLUMN employees.salary IS NULL;

This will remove the comment from the salary column.

3. Dropping a Comment from a View

If you have added a comment to a view (e.g., employee_view), you can remove the comment using:

COMMENT ON VIEW employee_view IS NULL;

This will remove the comment from the employee_view view.

4. Dropping a Comment from an Index

Similarly, if an index (e.g., idx_employee_name) has a comment, you can drop it with:

COMMENT ON INDEX idx_employee_name IS NULL;

This removes the comment from the idx_employee_name index.

5. Dropping a Comment from a Sequence

If a sequence (e.g., emp_id_seq) has a comment, you can remove it with:

COMMENT ON SEQUENCE emp_id_seq IS NULL;

This will remove the comment from the emp_id_seq sequence.

---

Viewing Comments Before Dropping Them

Before dropping a comment, you might want to view the current comments on database objects. You can do this by querying the data dictionary views.

1. View Comments on Tables

SELECT table_name, comments

FROM user_tab_comments

WHERE table_name = 'YOUR_TABLE_NAME';

2. View Comments on Columns

SELECT table_name, column_name, comments

FROM user_col_comments

WHERE table_name = 'YOUR_TABLE_NAME';

3. View Comments on Views

SELECT view_name, comments

FROM user_view_comments

WHERE view_name = 'YOUR_VIEW_NAME';

4. View Comments on Indexes

SELECT index_name, comments

FROM user_index_comments

WHERE index_name = 'YOUR_INDEX_NAME';

---

Dropping Database Objects with DROP Command

The DROP statement is used to remove database objects themselves, but not comments. Here are examples of how to drop different types of database objects:

1. Dropping a Table

DROP TABLE employees;

This removes the employees table and all associated data, indexes, and constraints.

2. Dropping a Column

You can drop a column from a table using the ALTER TABLE statement (not DROP directly):

ALTER TABLE employees DROP COLUMN salary;

This will remove the salary column from the employees table, along with its data.

3. Dropping an Index

DROP INDEX idx_employee_name;

This command drops the index idx_employee_name.

4. Dropping a Sequence

DROP SEQUENCE emp_id_seq;

This removes the sequence emp_id_seq.

---

Things to Keep in Mind

1.     Dropping a comment: You cannot undo the action of removing a comment once it's set to NULL. If you need the comment back, you’ll have to manually re-add it.

2.     Deleting an object: Dropping an object (like a table or column) will also remove any associated comments along with the object itself.

3.     Audit Trails: Comments are not versioned or tracked by Oracle by default. If you want to track changes to comments, you will need to implement auditing or manual logging solutions.

4.     Null Comments: Setting comments to NULL removes them, but the object still exists. The object simply no longer has a comment associated with it.