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

Tables

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

• 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, etc.

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 that user_tables shows you only the tables you own. To see a list of your tables, simply select the table_name column and any other columns containing information of interest:

               SELECT table_name, tablespace_name
               FROM user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMPLOYEE_COPY                  USERS
PROJECT_HOURS                  USERS
BIN$3oJlQsAlRUfgMKjAAgAV7g==$0 USERS
 . . .

The recycle bin

Oops! What's that BIN$3oJlQsAlRUfgMKjAAgAV7g==$0 business all about? Did I give a table a mixed up name like that? No, I didn't. The BIN$3oJlQsAlRUfgMKjAAgAV7g==$0 table that you see represents a table I deleted. Oracle Database 10g introduced a recycle bin for deleted database objects, which somewhat complicates the task of querying the data dictionary views. Filter out any recycle bin objects by adding WHERE dropped = 'NO ...