1

Okay this is my case. I need to query the Column name of a table from another Oracle Database User. It is with the same schema and same database connections with my username.

This is my query:

Select COLUMN_NAME 
from user_tab_columns 
where table_name='CY_3TRAVELTRAX' AND User = 'PUBLISH_PNL_AFPI_2013';

But I always got a zero result where in reality that User has many table and columns. Any help? Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JKStack
  • 210
  • 2
  • 9
  • 1
    The `user_*` views only contain data for the current user. – Mat May 02 '13 at 09:17
  • 1
    I suggest you remember to search before asking. The first hit on google when you type your title as-is is the above question. – Mat May 02 '13 at 09:27

1 Answers1

3

For most user_ data dictionary views there are also all_ (and dba_) views.

An excerpt from the documentation (at http://docs.oracle.com/cd/E11882_01/server.112/e17110/statviews_1001.htm#i1572007 ) describes it best:

Many data dictionary tables have three corresponding views:

  • An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.

  • A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.

  • A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.

So you should use:

Select COLUMN_NAME
from all_tab_columns
where table_name='CY_3TRAVELTRAX'
AND owner = 'PUBLISH_PNL_AFPI_2013';

NB: The column to use is called OWNER and not USER! The latter is a function that returns the current user (and every DBA gets bitten by this at least once in their career).

Colin 't Hart
  • 7,372
  • 3
  • 28
  • 51