ch16.htm

Learn SQL in an easy way...

  3       <B>  CREATED,</B>
  4       <B>  STATUS</B>
  5  <B>FROM USER_OBJECTS</B>
  6  <B>ORDER BY 1;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">OBJECT_TYPE      OBJECT_NAME            CREATED      STATUS
--------------   --------------------   ------------ ------
INDEX            MAGAZINE_INX           14-MAR-97    VALID
INDEX            SPORTS_INX             14-MAR-97    VALID
INDEX            HOBBY_INX              14-MAR-97    VALID
TABLE            MAGAZINE_TBL           01-MAR-97    VALID
TABLE            SPORTS                 14-MAR-97    VALID
TABLE            HOBBY_TBL              16-MAR-97    VALID

6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You can use the USER_OBJECTS view to select general information about a user's
owned objects, such as the name, type, date created, date modified, and the status
of the object. In the previous query, we are checking the data created and validation
of each owned object.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT TABLE_NAME, INITIAL_EXTENT, NEXT_EXTENT</B>
  2 <B> FROM USER_TABLES;</B>

TABLE_NAME                       INITIAL_EXTENT     NEXT EXTENT
----------------------------     --------------     -----------
MAGAZINE_TBL                            1048576          540672
SPORTS                                   114688          114688
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Much more data is available when selecting from the USER_TABLES view, depending
upon what you want to see. Most data consists of storage information.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice in the output that the values
	for initial and next extent are in bytes. In some implementations you can use column
	formatting to make your output more readable by adding commas. See Day 19, &quot;Transact-SQL:
	An Introduction,&quot; and Day 20, &quot;SQL*Plus.&quot; 
<HR>


</BLOCKQUOTE>

<P>The ALL_TABLES view is to USER_TABLES as the ALL_CATALOG view is to USER_CATALOG.
In other words, ALL_TABLES allows you to see all the tables to which you have access,
instead of just the tables you own. The ALL_TABLES view may include tables that exist
in another user's catalog.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(OWNER,1,15) OWNER,</B>
  2     <B>    SUBSTR(TABLE_NAME,1,25) TABLE_NAME</B>,
  3        <B> SUBSTR(TABLESPACE_NAME,1,13) TABLESPACE</B>
  4 <B> FROM ALL_TABLES;
</B>
OWNER                    TABLE_NAME                      TABLESPACE
--------------------     ----------------------------    ----------
SYS                      DUAL                            SYSTEM
JSMITH                   MAGAZINE_TBL                    USERS
SMITH                    SPORTS                          USERS
VJOHNSON                 TEST1                           USERS
VJOHNSON                 HOBBIES                         USERS
VJOHNSON                 CLASSES                         USERS
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Again, you have selected only the desired information. Many additional columns
in ALL_TABLES may also contain useful information.</P>
<P>As a database user, you can monitor the growth of tables and indexes in your catalog
by querying the USER_SEGMENTS view. As the name suggests, USER_SEGMENTS gives you
information about each segment, such as storage information and extents taken. A
segment may consist of a table, index, cluster rollback, temporary, or cache. The
following example shows how you might retrieve selected information from the USER_SEGMENTS
view.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,</B>
  2         <B>SUBSTR(SEGMENT_TYPE,1,8) SEG_TYPE,</B>
  3         <B>SUBSTR(TABLESPACE_NAME,1,25) TABLESPACE_NAME,</B>
  4        <B> BYTES, EXTENTS</B>
  5 <B> FROM USER_SEGMENTS</B>
  6  <B>ORDER BY EXTENTS DESC;</B>

SEGMENT_NAME          SEG_TYPE        TABLESPACE_NAME         BYTES           EXTENTS
--------------------  ------------    --------------------    ------------    -------
MAGAZINE_TBL          TABLE           USERS                        4292608          7
SPORTS_INX            INDEX           USERS                         573440          4
SPORTS                TABLE           USERS                         344064          2
MAGAZINE_INX          INDEX           USERS                        1589248          1
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">4 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The output in the preceding query was sorted by extents in descending order; the
segments with the most growth (extents taken) appear first in the results.</P>
<P>Now that you know which tables you have access to, you will want to find out what
you can do to each table. Are you limited to query only, or can you update a table?
The ALL_TAB_PRIVS view lists all privileges that you have as a database user on each
table available to you.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(TABLE_SCHEMA,1,10) OWNER,</B>
  2        <B> SUBSTR(TABLE_NAME,1,25) TABLE_NAME,</B>
  3        <B> PRIVILEGE</B>
  4  <B>FROM ALL_TAB_PRIVS;</B>

OWNER         TABLE_NAME            PRIVILEGE
------------  --------------------  ---------
SYS           DUAL                  SELECT
JSMITH        MAGAZINE_TBL          SELECT
JSMITH        MAGAZINE_TBL          INSERT
JSMITH        MAGAZINE_TBL          UPDATE
JSMITH        MAGAZINE_TBL          DELETE
JSMITH        SPORTS                SELECT
JSMITH        SPORTS                INSERT
JSMITH        SPORTS                UPDATE
JSMITH        SPORTS                DELETE
VJOHNSON      TEST1                 SELECT
VJOHNSON      TEST1                 INSERT
VJOHNSON      TEST1                 UPDATE
VJOHNSON      TEST1                 DELETE
VJOHNSON      HOBBIES               SELECT
VJOHNSON      CLASSES               SELECT
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>As you can see, you can manipulate the data in some tables, whereas you have read-only
access (<TT>SELECT</TT> only) to others.</P>
<P>When you create objects, you usually need to know where to place them in the database
unless you allow your target destination to take the default. An Oracle database
is broken up into tablespaces, each of which are capable of storing objects. Each
tablespace is allocated a certain amount of disk space, according to what is available
on the system. Disk space is usually acquired through the system administrator (SA).</P>
<P>The following query is from a view called USER_TABLESPACES, which will list the
tablespaces that you have access to, the default initial and next sizes of objects
created within them, and their status.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME,</B>
  2         <B>INITIAL_EXTENT,</B>
  3        <B> NEXT_EXTENT,</B>
  4         <B>PCT_INCREASE,</B>
  5        <B> STATUS</B>
  6  <B>FROM USER_TABLESPACES;</B>

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE STATUS
------------------------------ -------------- ----------- ------------ ------
SYSTEM                                  32768       16384           1  ONLINE
RBS                                   2097152     2097152           1  ONLINE
TEMP                                   114688      114688           1  ONLINE
TOOLS                                   32768       16384           1  ONLINE
USERS                                   32768       16384           1  ONLINE
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">5 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This type of query is very useful when you are creating objects, such as tables
and indexes, which will require storage. When a table or index is created, if the
initial and next storage parameters are not specified in the DDL, the table or index
will take the tablespace's default values. The same concept applies to <TT>PCT</TT>
<TT>INCREASE</TT>, which is an Oracle parameter specifying the percentage of allocated
space an object should take when it grows. If a value for <TT>PCT INCREASE</TT> is
not specified when the table or index is created, the database server will allocate
the default value that is specified for the corresponding tablespace. Seeing the
default values enables you to determine whether you need to use a storage clause
in the <TT>CREATE</TT> statement.</P>
<P>Sometimes, however, you need to know more than which tablespaces you may access,
that is, build tables under. For example, you might need to know what your limits
are within the tablespaces so that you can better manage the creation and sizing
of your objects. The USER_TS_QUOTAS view provides the necessary information. The
next query displays a user's space limits for creating objects in the database.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> SELECT SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME,</B>
  2      <B>   BYTES, MAX_BYTES</B>
  3  <B>FROM USER_TS_QUOTAS;</B>

TABLESPACE_NAME                     BYTES  MAX_BYTES
------------------------------ ---------- ----------
SYSTEM                                  0          0
TOOLS                             5242880      16384
USERS                              573440         -1

3 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The preceding output is typical of output from an Oracle data dictionary. <TT>BYTES</TT>
identifies the total number of bytes in that tablespace that are associated with
the user. <TT>MAX BYTES</TT> identifies the maximum bytes allotted to the user, or
the user's quota, on the tablespace. The first two values in this column are self-explanatory.
The <TT>-1</TT> in the third row means quota unlimited--that is, no limits are placed
on the user for that tablespace.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The <TT>SUBSTR</TT> function appears
	in many of the preceding queries of data dictionary views. You can use many of the
	functions that you learned about earlier to improve the readablility of the data
	you retrieve. The use of consistent naming standards in your database may allow you
	to limit the size of data in your output, as we have done in these examples. 
<HR>


</BLOCKQUOTE>

<P>These examples all show how an ordinary database user can extract information
from the data dictionary. These views are just a few of the many that exist in Oracle's
data dictionary. It is important to check your database implementation to see what
is available to you in your data dictionary. Remember, you should use the data dictionary
to manage your database activities. Though system catalogs differ by implementation,
you need only to understand the concept and know how to retrieve data that is necessary
to supplement your job.
<H3><FONT COLOR="#000077">System DBA Views</FONT></H3>
<P>The DBA views that reside within an Oracle data dictionary are usually the primary,
or most common, views that a DBA would access. These views are invaluable to the
productivity of any DBA. Taking these tables away from a DBA would be like depriving
a carpenter of a hammer.</P>
<P>As you may expect, you must have the <TT>SELECT_ANY_TABLE</TT> system privilege,
which is contained in the DBA role, to access the DBA tables. For example, suppose
you are JSMITH, who does not have the required privilege to select from the DBA tables.</P>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *</B>
  2  <B>FROM USER_ROLE_PRIVS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">USERNAME               GRANTED_ROLE            ADM    DEF    OS_
------------------     --------------------    ---    ---    --
JSMITH                 CONNECT                 NO     YES    NO
JSMITH                 RESOURCE                NO     YES    NO
</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *</B>
  2  <B>FROM SYS.DBA_ROLES;</B>
FROM SYS.DBA_ROLES;
     *

ERROR at line 2:
ORA-00942: table or view does not exist
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>When you try to access a table to which you do not have the appropriate privileges,
an error is returned stating that the table does not exist. This message can be a
little misleading. Virtually, the table does not exist because the user cannot &quot;see&quot;
the table. A solution to the problem above would be to grant the role DBA to JSMITH.
This role would have to be granted by a DBA, of course.
<H4><FONT COLOR="#000077">Database User Information</FONT></H4>
<P>The USER_USERS and ALL_USERS views give you minimum information about the users.
The DBA view called DBA_USERS (owned by <TT>SYS</TT>) gives you the information on
all users if you have the DBA role or <TT>SELECT_ANY_TABLE</TT> privilege, as shown
in the next example.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *</B>
  2  <B>FROM SYS.DBA_USERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">USERNAME                          USER_ID PASSWORD
--------------------------------  ------ -----------------------------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ --------
PROFILE
------------------------------
SYS                                     0 4012DA490794C16B
SYSTEM                         TEMP                           06-JUN-96
DEFAULT

JSMITH                                  5 A4A94B17405C10B7
USERS                          TEMP                           06-JUN-96
DEFAULT

2 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>When you select all from the DBA_USERS view, you are able to see the vital information
on each user. Notice that the password is encrypted. DBA_USERS is the primary view
used by a DBA to manage users.
<H4><FONT COLOR="#000077">Database Security</FONT></H4>
<P>Three basic data dictionary views deal with security, although these views can
be tied to-gether with other related views for more complete information. These three
views deal with database roles, roles granted to users, and system privileges granted
to users. The three views introduced in this section are DBA_ROLES, DBA_ROLE_PRIVS,
and DBA_SYS_PRIVS. The following sample queries show how to obtain information pertinent
to database security.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *</B>
  2  <B>FROM SYS.DBA_ROLES;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ROLE                           PASSWORD
------------------------------ --------