Oracle 11中无法查询指定表列数据类型的技术求助

First, let's break down the core issues causing this problem:

1. user_tab_columns only includes tables you own #

The user_tab_columns view displays columns for tables, views, and clusters owned by the current user. Since hr.t2 belongs to the HR schema, if you're not logged in as the HR user, this view won't contain any entries for it. That's why you can't find it there, even though desc hr.t2 works— the desc command checks for any table you have permission to access, not just your own.

2. You're using the wrong format for table names in the query #

Neither user_tab_columns nor all_tab_columns stores table names with their schema prefix (like hr.t2). Instead:

• The OWNER column holds the schema name (e.g., HR)
• The TABLE_NAME column holds just the table name (e.g., T2)

Your query select unique table_name from user_tab_columns where table_name='hr.t2'; is looking for a table literally named hr.t2 (which doesn't exist), so it returns no results.

Fixes to get the column data type #

Option 1: Use all_tab_columns (if you have access to hr.t2) #

Since you can run desc hr.t2, you have at least read permission on the table. Use all_tab_columns and specify both the schema (OWNER) and table name:

SELECT t.data_type 
FROM all_tab_columns t 
WHERE t.OWNER = 'HR' 
  AND t.TABLE_NAME = 'T2' 
  AND t.COLUMN_NAME = '<YOUR_COLUMN_NAME>';

Make sure to replace <YOUR_COLUMN_NAME> with the actual column name (in uppercase, since Oracle stores metadata in uppercase by default).

Option 2: Verify the table exists first #

If you want to confirm hr.t2 is visible to your user, run this query against all_tables:

SELECT * FROM all_tables WHERE owner='HR' AND table_name='T2';

If this returns a row, you have access to the table, and the all_tab_columns query above will work.

Option 3: If you're logged in as HR #

If you're using the HR user account, you can stick with user_tab_columns—just remove the schema prefix from the table name:

SELECT t.data_type 
FROM user_tab_columns t 
WHERE t.TABLE_NAME = 'T2' 
  AND t.COLUMN_NAME = '<YOUR_COLUMN_NAME>';

内容的提问来源于stack exchange，提问作者George Hernando