如何使用 Oracle 廉价地验证另一模式中表中的列是否存在？

Question

环境是Oracle 9& 10. 我没有 DBA 级别的访问权限。

问题是验证另一个模式中的特定表中是否存在特定列。

有两种情况需要处理。

1. 同一实例中的另一个模式
2. 不同实例中的模式，使用 db_link

给定我的模式 FRED 和另一个模式 BARNEY，我尝试了类似的操作

SELECT 1
FROM BARNEY.USER_TAB_COLS
WHERE TABLE_NAME = 'SOME_TABLE' 
  AND COLUMN_NAME = 'SOME_SPECIFIC_COLUMN'

，结果是 [1]: (Error): ORA-00942: table or view不存在

经过一段时间的思考，我意识到 USER_TAB_COLS 并不是真正的表。这是一种观点。我一直从表中进行选择，但不是从视图中进行选择。

我对 db_link 尝试了同样的操作，并惊讶地看到数据返回。 db_link 中有一个嵌入的 schema_name/password，所以对我来说它的工作似乎是合理的，因为它有效地登录到其他模式，这应该使视图可访问。

在谷歌上到处搜索，并在 Oracle 文档山上耗尽了我的眼球， 我正在寻找有人指出我正确的方向，或者至少指出我所缺少的东西。

有哪些技术可用于从同一实例中的架构中获取与用户表相关的元数据，以验证特定列是否存在？

提前致谢。

邪恶的。

好的答案+1。 谢谢。

Answer 1

您可以使用以下查询：

SELECT 1
FROM ALL_TAB_COLS
WHERE TABLE_NAME = 'SOME_TABLE' 
  AND COLUMN_NAME = 'SOME_SPECIFIC_COLUMN'
  AND OWNER = 'BARNEY';

（User_Tables 和 User_Tab_Cols 只是 all_tables 和 all_tab_coumns 上的视图，并附有 whereowner =）

如果您被允许查看 Barney 的 some_table (即您已被授予至少 SELECT 权限），然后您就会知道该列是否存在。如果您没有该表的权限，您将无法获取其元信息。

Answer 2

与其他回复一样，通常我使用 ALL_TAB_COLUMNS 进行这样的查询。但这只会显示您有 SELECT 的表中的列。它是在该列上进行选择的——万一他们已经为该表实现了列级权限，您可能可以看到该表，但看不到感兴趣的特定列。对于我们大多数人来说，这是极其罕见的。

DBA_TAB_COLUMNS 将显示所有列，但您需要选择由 DBA 授予您的架构的列。 （实际上，您需要获得 ALL_TAB_COLUMNS 的授权才能使用它，但这在大多数商店中很常见）。也可以使用 DBMS_METADATA PL/SQL 内置包，具有类似的限制，但我认为您会发现它更复杂。

当然，您也可以尝试从 barney.some_table.some_column@my_dblink （或您感兴趣的任何部分）中选择一条记录。然后处理异常。丑陋，在大多数情况下我不会推荐它。

Answer 3

您可以使用 all_tab_columns 来实现此目的。

但请注意，您只能看到允许看到的内容。

Answer 4

相同的实例，不同的模式：

Select Count(*)
From   all_tab_cols
Where  owner       = 'BARNEY'               and
       table_name  = 'SOME_TABLE'           and
       column_name = 'SOME_SPECIFIC_COLUMN';

count(*) 的优点是始终返回值为 1 或 0 的单行，因此您不必处理 PL/SQL 中的 NO_DATA_FOUND 错误。

跨 DB Link，与您连接的架构相同：

Select Count(*)
From   user_tab_cols@MY_DB_LINK
Where  table_name  = 'SOME_TABLE'           and
       column_name = 'SOME_SPECIFIC_COLUMN';

跨 DB Link，与您连接的架构不同：

Select Count(*)
From   all_tab_cols@MY_DB_LINK
Where  owner       = 'BARNEY'               and
       table_name  = 'SOME_TABLE'           and
       column_name = 'SOME_SPECIFIC_COLUMN';