O'Reilly logo

Oracle SQL*Plus: The Definitive Guide, 2nd Edition 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, 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 ...

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