Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Sysadmin trying to learn Oracle...help!

Re: Sysadmin trying to learn Oracle...help!

From: GeoPappas <PappasG_at_gmail.com>
Date: 8 Jan 2006 05:49:28 -0800
Message-ID: <1136728168.203556.197770@g47g2000cwa.googlegroups.com>


<< For now I would be happy to see what sort of tables exist in this database, what they are called, i.e. but all of the examples I find assume I already know that. >>

You can see what tables are OWNED by the user that you logged in with as follows:

SELECT table_name FROM user_tables;

You can also see tables that are ACCESSIBLE by the user that you logged in with as follows:

SELECT owner, table_name FROM all_tables ORDER BY 1, 2;

If you logged in as a user with DBA privileges, then you will have access to ALL of the tables in the database as follows:

SELECT owner, table_name FROM dba_tables ORDER BY 1, 2;

Please note that this last SELECT statement will probably generate a LOT of output, so you probably should limit the amount of output by a WHERE clause. For example:

SELECT table_name FROM dba_tables WHERE rownum < 50;

The objects user_tables, all_tables, and dba_tables are all from the Oracle data dictionary. They are actually views, but that doesn't matter much for you at this point.

There are a LOT of other views in the Oracle data dictionary. Some of them are as follows:

user_tab_columns (lists the columns for tables)
user_views
user_synonyms
user_indexes

The user_ views give you access to objects that are OWNED by the user that you are logged in as. For example, if you are logged in as SCOTT, then you will only see SCOTT's objects via the user_ views.

The all_ views give you access to objects that are ACCESSIBLE by the user that you are logged in as.

The dba_ views are only accessible by users with DBA privileges.

As mentioned before, you can replace the user_ prefix with all_ or dba_, for example:

all_tab_columns
dba_indexes
...

With this info, you can then use DESC to get a listing of a specific table (which you can also get via the user_tab_columns view).

Hope this helps. Received on Sun Jan 08 2006 - 07:49:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US