O'Reilly logo

Oracle SQL*Plus: The Definitive Guide by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required