Managing Tables   «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[1].
You can query the views in the data dictionary to get information about the objects in the database and their attributes.

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:

1) Running Programs 1 2) Running Programs 2 3) Running Programs 3

Program 1 Program 2 Program 3
  1. You bring up SQL Plus, the standard interactive tool for Oracle, here using the standard SCOTT/TIGER username and password set up
  2. You enter this SQL statement to return a list of table names and the number of rows in the table.
  3. The result is the list of table names, but it does not show the number of rows, as this table has not had the ANALYZE command run on it to gather statistics.

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

[1]Data Dictionary: A group of tables and views that define the structures in a database.