CODEWITHSUNDEEP
sqloracle11gcommentsoracle-sqldeveloper
JavierCvilla
JavierCvilla

Reputation: 41

How to delete comments from a removed table in Oracle SQL?

We have a SQL Table that was created with

CREATE TABLE EXAM
date DATE NOT NULL;

Later we add a comment to the table and another comment to the column 'exam':

COMMENT ON TABLE EXAM IS 'Exams of the year';
COMMENT ON COLUMN EXAM.DATE IS 'Date of the exam';

The problem is that after remove the table with:

DROP TABLE EXAM CASCADE CONSTRAINT;

we consult the comments of the column user with:

SELECT * FROM USER_COL_COMMENTS;

and we found something like that:

BIN$mw7H0FUoSiiAatqV7Hehog==$0 DATE Date of the exam

and a lot of rows with a table_name like this. Each time I create a table and add a comment to it, and then drop it, I found a new group of rows with similar names (BIN$mw7H0FUoSiiAatqV7Hehog==$0) with the comments of removed tables.

So, I would to know if is possible to remove all this kind of comments.

Upvotes: 3

Views: 4998

Answers (1)

Justin Cave
Justin Cave

Reputation: 231761

By default, when you drop a table, it is placed in your recycle bin. That allows you to undrop the table if you discover that you dropped a table inadvertently.

You can prevent a table from going to the recycle bin by using the PURGE clause, i.e.

DROP TABLE exam PURGE

You can clear everything out of the current user's recycle bin

PURGE RECYCLEBIN

You can (assuming you have privileges) clear out everything from the recycle bins of every user

PURGE DBA_RECYCLEBIN

You can disable the recycle bin for the system or for the session

ALTER SYSTEM SET recyclebin = OFF scope = BOTH
ALTER SESSION SET recyclebin = OFF

Upvotes: 2

Related Questions