Chapter 7
Getting Familiar with Oracle8 Structure

In This Chapter

• Admiring data dictionary views from afar
• Getting to know Oracle8
• Using data-dictionary views for your own advancement
• Using Schema Manager to list the views
• Using the SQL Worksheet to peek inside data dictionary views

What are users in the Oracle8 database, and what have they created? How does Oracle8 know which tables a user can see and which a user cannot touch? Where is the off switch?

You can find the answers to all these questions -- except the last -- in the data-dictionary views. Data-dictionary views are actually views based on underlying tables called the system tables. System tables gather information about tables, rows, view, columns, security, users, and even about the files allocated to store the Oracle8 database itself. System tables keep track of all the users, as well as what each user can and cannot do. This chapter tells you how to access that information by looking at the data-dictionary views.

Oracle8 depends on the data-dictionary views and the underlying system tables, so don't mess with them. Look at the data-dictionary views all you want, but do not alter these views or the system tables in any way. Unless the database administrator (DBA) gives you special permission, you are not able to make changes in either the data-dictionary views or the system tables. If, however, you have your own desktop version of Oracle8, you're the DBA. You can, if a demon possesses you, add rows or alter the structure of these views or tables, but for now, you just have to exorcise a little restraint.

Data-Dictionary Views You Can Really Use

The following little table of views shows you the best-kept secrets in the Oracle8 database, including what resides in data-dictionary views. The views make it easy for you to interpret the internal workings of Oracle8.

Oracle8 created the PUBLIC user to handle situations in which it allows all users to see your table. PUBLIC simplifies your life by allowing you to grant privileges to PUBLIC rather than to many individuals. Oracle8 automatically gives anything granted to PUBLIC to any newly created users. This arrangement may not seem to be fair to those who have more seniority. Tough.

See Chapter 10 for details on granting privileges for tables.

Much of the information that you see displayed in graphical form in the Enterprise Manager tools, you can find in the data-dictionary views. You can use these views to write SQL queries to gather information about your database. You may want to create a report on the status of your database by using other report-writing tools. The data-dictionary views give you a great source of information on which to base reports.

Listing Data-Dictionary Views with Schema Manager

You can list all the views that the Oracle8 database contains by using Schema Manager. To begin, start the Schema Manager by following these steps:

1. Go to the Enterprise Manager Toolbar.

   If you have not already done so, customize your Toolbar so that it is always handy, as described in Chapter 4 (in "Managing the Enterprise Manager"). Start the Toolbar, if you haven't already.

2. Click the Schema Manager icon in the Enterprise Manager Toolbar.

   Schema Manager asks you to log in.

3. Log in as a DBA.

   Log in with a valid DBA Oracle8 username and password. One option is to log in with the userid and password that is loaded into all Oracle8 databases: SYSTEM (password MANAGER). Leave the Service box empty if you are running your own personal Oracle8. Otherwise, fill in the name of the Oracle8 database (instance) on your network. Leave the Connect As box defaulted to Normal.

4. Double-click the folder labeled Views in the left frame.

   Schema Manager brings up a listing of all the views available, including the data-dictionary views.

5. Sort the views by name.

   Click the View column heading near the top of the right frame.

   Oracle8 sorts the views in alphabetical order. Figure 7-1 shows the Schema Manager window with the view listings.

6. Close the Schema Manager.

   Click the X box in the top-right corner of the window.

You have many views available to you. The next section shows you how to look at the contents of a few of the most useful data-dictionary views. Refer to Table 7-1 earlier in this chapter to help you decide which data-dictionary views you want to see.

Looking at Data-Dictionary Views with SQL Worksheet

This section deals with two useful queries that you use to take a quick look around in the data-dictionary views. The first query is how you list all the data-dictionary views that are available for you to explore. The second query is how you list all the Oracle usernames that are defined to the Oracle8 database you are using.

List useful data-dictionary views

The following steps show how to query a data-dictionary view and show part of the results:

1. Go to your Enterprise Manager Toolbar.

   If you have not already done so, customize your Toolbar so that it is always handy, as described in Chapter 4 (in "Managing the Enterprise Manager"). Start the Toolbar, if you haven't already.

2. Click the SQL Worksheet icon.

   The icon looks like a paper and pencil. Schema Manager asks you to log in.

3. Log in as a DBA.

   Log in with a valid DBA Oracle8 username and password. One option is to log in with the userid and password that is loaded into all Oracle8 databases: SYSTEM (password MANAGER). Leave the Service box empty if you are running your own personal Oracle8. Otherwise, fill in the name of the Oracle8 database (instance) on your network. Leave the Connect As box defaulted to Normal.

4. Type the following SQL command and then click the Run button:

   desc ALL_SYNONYMS;

   The results (see Figure 7-2) scroll into the top window. You have just listed all the columns contained in the ALL_SYNONYMS view.

5. Type the following SQL command and then click the Run button:

   select TABLE_NAME from ALL_SYNONYMS 
   
   
        where TABLE_NAME like 'USER%' 
   
   
        or TABLE_NAME like 'ALL%' 
   
   
   order by TABLE_NAME;
   

   The query's WHERE clause (everything between where and order by in the preceding command) narrows down the results by restricting the query to those data-dictionary views that begin with USER or ALL. These two groups of data-dictionary views (about 125) are very useful. Another 250 not-so-useful views are stored in the ALL_SYNONYMS data-dictionary view.

   Oracle8 lists the results of your query. The following shows a partial listing that you receive when running the query:

   TABLE_NAME
------------------------------
ALL_CATALOG
ALL_CLUSTERS
ALL_CLUSTER_HASH_EXPRESSIONS
ALL_COL_COMMENTS
ALL_COL_GRANTS_MADE
ALL_COL_GRANTS_RECD
ALL_COL_PRIVS
ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD
ALL_CONSTRAINTS
ALL_CONS_COLUMNS
ALL_DB_LINKS
ALL_DEF_AUDIT_OPTS
ALL_DEPENDENCIES
ALL_ERRORS
ALL_INDEXES
ALL_IND_COLUMNS
ALL_OBJECTS
ALL_REFRESH
ALL_REFRESH_CHILDREN
ALL_SEQUENCES

These views are just a few of the data-dictionary views that are available. The DBA restricts you to seeing only what you have privileges to see. So unless you're the DBA you can probably see just a small slice of the whole database pie.

Chapters 10 and 11 go on and on about the subject of privileges, grants, and how they fit together. Chapter 19 also has a section on security, which could also have practical uses at airport terminals.

List all Oracle users

Follow these steps to find out what users are hanging out in your database:

1. Go to your Enterprise Manager Toolbar.

   If you have not already done so, customize your Toolbar so that it is always handy, as described in Chapter 4 (in "Managing the Enterprise Manager"). Start the Toolbar, if you haven't already.

2. Click the SQL Worksheet icon.

   The icon looks like a paper and pencil. Schema Manager asks you to log in.

3. Log in as a DBA or other user.

   Log in with a valid Oracle8 username and password. One option is to log in with the userid and password that is loaded into all Oracle8 databases: SYSTEM (password MANAGER). Leave the Service box empty if you are running your own personal Oracle8. Otherwise, fill in the name of the Oracle8 database (instance) on your network. Leave the Connect As box defaulted to Normal.

4. Type the following SQL command and then click the Run button:

   select * from all_users;

   Your results will vary, of course, because your database probably does not have an identical list of users.

5. Close the SQL Worksheet.

   Click the X box in the top-right corner of the window.

The data-dictionary views give you a great deal of great information about your database. Use these views well. Use them often. Enjoy their companionship. Make them some little tinfoil hats, too, so that they can feel like part of the group. Well, at least keep in mind that you could use them later without too much trouble.