Database Reference
In-Depth Information
the current user. USER_name views access information available to
and about the currently logged-in user; in our case, the user is the
same as our schema, MUSIC.
ALL_name.
These are accessible by any user and show information
about any object that the current user either owns or has privileges to
use. For example, ALL_TABLES lists all tables created by the user
plus those created by other users where the user has received permis-
sion to access the table.
Other Views.
These have names that do not follow the naming pat-
terns above. Some are for the DBA and others are for all users. Some
of these views are holdovers from previous releases of the database
and will gradually be removed in future releases. These views can also
cover obscure optional Oracle add-on packages or be newly devel-
oped and not yet completely incorporated.
Let's look at some queries. One of the most important metadata and
performance view queries is a query that lists all of the metadata and perfor-
mance views:
SELECT TABLE_NAME FROM DICTIONARY ORDER BY TABLE_NAME;
If I wanted to show all metadata views for the current logged-in user, I
could use a query such as this one:
SELECT TABLE_NAME FROM DICTIONARY
WHERE TABLE_NAME LIKE 'USER_%'
ORDER BY TABLE_NAME;
Now let's look at some specific examples. The following query lists all of
the tables owned by the currently connected user.
SELECT TABLE_NAME FROM USER_TABLES;
Next we can find out how many other tables the currently logged-in user
has permission to see. Note that this query uses the ALL_TABLES view to
find all accessible tables, not just tables owned by the current user.
SELECT OWNER, TABLE_NAME FROM ALL_TABLES
WHERE OWNER <> USER;
