How to get table comments through SQL in Oracle?

Question

I tried:

select * from user_tab_comments; 

and it returns me 3 columns "TABLE_NAME", "TABLE_TYPE" and "COMMENTS", but the column "TABLE_NAME" looks like "encrypted", I need clear table names:

 TABLE_NAME TABLE_TYPE COMMENTS BIN$IN1vjtqhTEKcWfn9PshHYg==$0 TABLE Résultat d'intégration d'une photo numérisée BIN$PUwG3lb3QoazOc4QaC1sjw==$0 TABLE Motif de fin d'agrément de maître de stage 

When I use "select * from user_tables"; TABLE_NAME is not encrypted.

Answer 1

Since 10g Oracle does not immediately delete tables when we execute the DROP TABLE statement. Instead, he renames them as follows: BIN$IN1vjtqhTEKcWfn9PshHYg==$0 and puts them in the trash. This allows us to restore tables that we did not want to delete. Find out more

The tables in the basket are still tables, so they appear in ALL_TABLES and similar views. Therefore, if you want to see only comments related only to live (not deleted) tables, you need to filter by the table name:

 select * from all_tab_comments where substr(table_name,1,4) != 'BIN$' / 

---

"I cannot believe that there is no flag column, so you can do this, and is_recycled = 0 or something like that."

You are right, that would be incredible. So, I checked the documentation, it turned out that Oracle 10g added a column named DROPPED to the USER_ / ALL_ / DBA_TABLES views.

 select tc.* from all_tab_comments tc join all_tables t on tc.owner = t.owner and tc.table_name = t.table_name where t.dropped = 'NO' / 

Check out the documentation . Obviously, having to join the ALL_TABLES view requires more input than filtering by name, so depending on our needs, it might be easier to keep the original WHERE clause.

Answer 2

SELECT t.table_name, t.comments FROM USER_TAB_COMMENTS t WHERE TABLE_NAME = 'SS_DEPT';