Managing DB Objects   «Prev  Next»
Lesson 5Listing tables
ObjectiveGenerate a list of the tables in your Oracle database.

Listing Oracle Tables

Each Oracle database contains a number of views called the data dictionary.
You can query the views in the data dictionary to get information about the objects in the database and their attributes.
Data Dictionary: A group of tables and views that define the structures in a database.
Types of data dictionary views
Each data dictionary view has three different versions:
A version preceded by USER_ Displays all of the relevant objects in a particular schema
A structure in an Oracle database that contains database structures, typically owned by the schema owner.
A version preceded by ALL_ Displays all of the relevant objects in all the schemas available to a user
A version preceded by DBA_ This is like the ALL_ version, but contains additional information of interest to database administrators
The DBA views can only be queried by users who have the DBA privilege.

Table views

To find out information about the tables in an Oracle database, you normally use the USER_TABLES view. This view includes 39 different columns, with information about a wide variety of characteristics of the table. Many of these attributes will only be meaningful when you learn all the intricacies of the Oracle database. The most commonly used columns are:

TABLE_NAME The name of the table
NUM_ROWS The number of rows in the table, as determined by the most recent ANALYZE command
AVG_ROW_LEN The average length of a row in the table

You can use the Schema Manager to display the names and attributes of tables, but it is just as easy to use SQL statements to query the data dictionary views, as shown in the Slideshow that follows:

Querying Data Dictionary
The next lesson explains how to get information about the columns in a table from the data dictionary.

Learn more about database design by visiting one of the pages below