Find tables with SQL Plus?? 1

[cojiro]

It is my responsibility to produce reports from an Oracle database. This would be fine except that the data dictionary that was given to me to write my queries seems to be way off. I'm using SQL plus and I am not very familiar with oracle. Is there a way that I can find out what tables are available for me to use?

 

[sybaseguru]

For list of your own tables try

select table_name from user_tables;

Or all tables that you have access to try

select owner, table_name from all_tables;

 

[cojiro]

I didn't get any rows returned for the first one, however the second query worked perfectly. Thanks!

 

[carp]

Not getting any rows returned from user_tables indicates that you don't have any tables in your schema. Getting rows back from all_tables indicates that you have privileges on tables in other schemas.

 

[cojiro]

Ok Carp, I'm not very familiar with schemas. Can you tell me what your last message means and how I might be affected by this?

 

[sybaseguru]

Other schema tables means that these are tables created by other users in their schema which you as user logged in to Oracle have some rights on them (simplest form you can select from tables to create a report say). For example, Table scott.emp means "emp" table in scott's schema and scgott happens to be a user in Oracle. So when you login with a user say "cojiro" to oracle you have a default schema called cojiro. You may not have created any object in "cojiro" schema but you may have select permission on scott.emp table (i.e. you can select from emp table in scott's schema, a schema is synonym in oracle with a user.) In sql server or Sybase this is a user in a database with say "dbo" privilleges usually who creates objects like table.

I hope this makes sense

 

[cojiro]

Thanks sybaseguru, that was very helpful.

 

[carp]

Sorry about that, cojiro.

As sybaseguru explained, a schema is sort of like a box of everything you own - tables, views, indexes, synonyms, packages, procedures, sequences, etc, etc, etc.

There are also three basic levels of views in the data dictionary that people normally look in to see what's in all of the boxes. USER will show you everything in your box. ALL will show you anything that you have privileges on in any box. DBA will show you everything in every box - but (as DBA would imply) this is normally reserved for specific, high-powered users.

So if you want to see all of the tables in your box, you would look in USER_TABLES; all of your triggers would be in USER_TRIGGERS, and so on.

If you wanted to see all of the views that are available to you, you would look in ALL_VIEWS.

If you had proper authority and wanted to see every index in the database, you would look in DBA_INDEXES.

Hope this helps.