Oracle SQL*Plus: The Definitive Guide

Tables

When it comes to looking at a table and its column definitions, there are two data dictionary views you need to be concerned with. These views are:

ALL_TABLES

ALL_TAB_COLUMNS

The ALL_TABLES view contains one row for each table. You can use this view to get a quick list of tables you own or to which you have been granted some type of access. ALL_TABLES has a one-to-many relationship to ALL_TAB_COLUMNS, which contains one record for each column in a table. ALL_TAB_COLUMNS is the source for information on column names, datatypes, default values, and so forth.

Listing Tables You Own

To get a quick list of tables you own, it’s easier to use the USER_TABLES view than ALL_TABLES. Remember, USER_TABLES shows you only the tables you own. To see a list of your tables, simply select the table_name column.

SQL> SELECT table_name FROM user_tables;

TABLE_NAME
------------------------------
DATA_TYPE_TEST
DT_TEST
EMPLOYEE
PROJECT
PROJECT_HOURS

5 rows selected.

To see tables owned by other users, you need to query the ALL_TABLES view. Just be sure to qualify your query by specifying the owner’s username in the WHERE clause. Here’s an example that lists all tables owned by the user SYSTEM:

SELECT table_name
  FROM all_tables
 WHERE owner = 'SYSTEM';

You can make things easy on yourself by writing a script to list tables. The following listing shows one approach you can take. It prompts you for a username, then lists all tables owned by that user. The table listing is in alphabetical order.

--DESCRIPTION ...