Lesson 5	Listing tables
Objective	Generate a list of the tables in your Oracle database.

Listing Oracle Tables

Each Oracle database contains a number of views called the data dictionary^[1].
You can query the views in the data dictionary to get information about the objects in the database and their attributes.
In Oracle 19c, you can query the data dictionary views to retrieve information about database objects such as tables, indexes, users, privileges, and more. The data dictionary consists of three main categories of views:

USER_ views – Shows information about objects owned by the current user.
ALL_ views – Shows information about objects accessible to the user.
DBA_ views – Shows information about all database objects (requires DBA privileges).

Querying Tables in the Data Dictionary
- Find all tables owned by the current user
```
SELECT table_name FROM USER_TABLES;
```
- Find all tables accessible to the user
```
SELECT table_name, owner FROM ALL_TABLES WHERE owner = 'SCOTT';
```
- Find all tables in the database (DBA only)
```
SELECT table_name, owner FROM DBA_TABLES;
```

Querying Columns of a Table

Get column details of a specific table

SELECT column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'EMPLOYEES';

Querying Indexes

Find all indexes on a table

SELECT index_name, table_name, uniqueness
FROM USER_INDEXES
WHERE table_name = 'EMPLOYEES';

Querying Constraints

Find all constraints on a table

SELECT constraint_name, constraint_type, status
FROM USER_CONSTRAINTS
WHERE table_name = 'EMPLOYEES';

Querying Database Users
- Find all database users (DBA Only)
```
SELECT username, account_status FROM DBA_USERS;
```

Querying Privileges

Find privileges granted to the current user
```
SELECT privilege FROM USER_SYS_PRIVS;
```

Find object privileges for a specific table

SELECT grantee, privilege
FROM ALL_TAB_PRIVS
WHERE table_name = 'EMPLOYEES';

Querying Database Sessions (DBA Only)

Find active database sessions

SELECT sid, serial#, username, machine, status
FROM V$SESSION
WHERE status = 'ACTIVE';

Conclusion:

Use USER_ views for objects owned by the user.
Use ALL_ views for objects the user can access.
Use DBA_ views for full database visibility (requires DBA privileges).

Types of data dictionary views
Each data dictionary view has three different versions:

A version preceded by `USER_`	Displays all of the relevant objects in a particular schema A structure in an Oracle database that contains database structures, typically owned by the schema owner.
A version preceded by `ALL_`	Displays all of the relevant objects in all the schemas available to a user
A version preceded by `DBA_`	This is like the `ALL_` version, but contains additional information of interest to database administrators

The DBA views can only be queried by users who have the DBA privilege.

Table Views in Oracle

To find out information about the tables in an Oracle database, you normally use the USER_TABLES view. This view includes 39 different columns, with information about a wide variety of characteristics of the table. Many of these attributes will only be meaningful when you learn all the intricacies of the Oracle database. The most commonly used columns are:

`TABLE_NAME`	The name of the table
`NUM_ROWS`	The number of rows in the table, as determined by the most recent `ANALYZE` command
`AVG_ROW_LEN`	The average length of a row in the table

You can use the Schema Manager to display the names and attributes of tables, but it is just as easy to use SQL statements to query the data dictionary views, as shown in the Slideshow that follows:

1) You bring up SQL Plus, the standard interactive tool for Oracle, here using the standard SCOTT/TIGER username and password set up

2) You enter this SQL statement to return a list of table names and the number of rows in the table.

3) The result is the list of table names, but it does not show the number of rows, as this table has not had the ANALYZE command run on it to gather statistics. — 3) The result is the list of table names, but it does not show the number of rows, as this table has not had the ANALYZE command run on it to gather statistics

Querying the Data Dictionary

The central set of tables and views that are used as a read-only reference about a particular database. A data dictionary stores such information as:

The logical and physical structure of the database
Valid users of the database
Information about integrity constraints
How much space is allocated for a schema object and how much of it is in use

A data dictionary is created when a database is created and is automatically updated when the structure of the database is updated.
The next lesson explains how to get information about the columns in a table from the data dictionary.

[1] Data Dictionary: A group of tables and views that define the structures in a database.