Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata