如何在Oracle中通过SQL获取表注释？

I've tried :

我试过了：

select * from user_tab_comments;

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

它返回3列“TABLE_NAME”，“TABLE_TYPE”和“COMMENTS”，但“TABLE_NAME”列类似于“加密”，我需要清楚的表名：

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 no "encrypted".

当我使用“select * from user_tables;”时TABLE_NAME没有“加密”。

1 个解决方案

#1

Since 10g Oracle doesn't immediately drop tables when we issue a DROP TABLE statement. Instead it renames them like this BIN$IN1vjtqhTEKcWfn9PshHYg==$0 and puts them in the recycle bin. This allows us to recover tables we didn't mean to drop. Find out more.

由于10g Oracle在发出DROP TABLE语句时不会立即删除表。相反，它将它们重命名为BIN $ IN1vjtqhTEKcWfn9PshHYg == $ 0并将它们放入回收站。这允许我们恢复我们并不意味着丢弃的表。了解更多。

Tables in the recycle bin are still tables, so they show up in ALL_TABLES and similar views. So if you only want to see comments relating only to live (non-dropped) tables you need to filter by table name:

回收站中的表仍然是表，因此它们显示在ALL_TABLES和类似视图中。因此，如果您只想查看仅与实时（非删除）表相关的注释，则需要按表名过滤：

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

"I can't believe there isn't a flag column so you could do and is_recycled = 0 or something. "

“我不敢相信没有一个标志列，所以你可以做，并且is_recycled = 0或其他东西。”

You're right, it would be incredible. So I checked the documentation it turns out Oracle 10g added a column called DROPPED to the USER_/ALL_/DBA_TABLES views.

你是对的，这将是不可思议的。所以我检查了文档，事实证明Oracle 10g在USER_ / ALL_ / DBA_TABLES视图中添加了一个名为DROPPED的列。

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 the need to join to the ALL_TABLES view requires more typing than filtering on the name, so depending on our need it might just be easier to keep the original WHERE clause.

查看文档。显然，需要加入ALL_TABLES视图需要更多的输入而不是对名称进行过滤，因此根据我们的需要，保留原始WHERE子句可能更容易。

#1