ch16.htm

Learn SQL in an easy way...

CONNECT                        NO
RESOURCE                       NO
DBA                            NO
EXP_FULL_DATABASE              NO
IMP_FULL_DATABASE              NO
END_USER_ROLE                  NO

6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The view DBA_ROLES lists all the roles that have been created within the database.
It gives the role name and whether or not the role has a password.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *</B>
  2  <B>FROM SYS.DBA_ROLE_PRIVS</B>
  3  <B>WHERE GRANTEE = 'RJENNINGS';</B>

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
RJENNINGS                      CONNECT                        NO  YES
RJENNINGS                      DBA                            NO  YES
RJENNINGS                      RESOURCE                       NO  YES

3 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The DBA_ROLE_PRIVS view provides information about database roles that have been
granted to users. The first column is the grantee, or user. The second column displays
the granted role. Notice that every role granted to the user corresponds to a record
in the table. <TT>ADM</TT> identifies whether the role was granted with the Admin
option, meaning that the user is able to grant the matching role to other users.
The last column is <TT>DEFAULT</TT>, stating whether the matching role is a default
role for the user.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *</B>
  2  <B>FROM SYS.DBA_SYS_PRIVS</B>
  3 <B> WHERE GRANTEE = 'RJENNINGS';</B>

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RJENNINGS                      CREATE SESSION                           NO
RJENNINGS                      UNLIMITED TABLESPACE                     NO

2 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The DBA_SYS_PRIVS view lists all system-level privileges that have been granted
to the user. This view is similar to DBA_ROLE_PRIVS. You can include these system
privileges in a role by granting system privileges to a role, as you would to a user.
<H4><FONT COLOR="#000077">Database Objects</FONT></H4>
<P>Database objects are another major focus for a DBA. Several views within the data
dictionary provide information about objects, such as tables and indexes. These views
can contain general information or they can contain detailed information about the
objects that reside within the database.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *</B>
  2  <B>FROM SYS.DBA_CATALOG</B>
  3  <B>WHERE ROWNUM &lt; 5;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">OWNER                          TABLE_NAME                     TABLE_TYPE
------------------------------ ------------------------------ ----------
SYS                            CDEF$                          TABLE
SYS                            TAB$                           TABLE
SYS                            IND$                           TABLE
SYS                            CLU$                           TABLE

4 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The DBA_CATALOG is the same thing as the USER_CATALOG, only the owner of the table
is included. In contrast, the USER_CATALOG view deals solely with tables that belonged
to the current user. DBA_CATALOG is a view that the DBA can use to take a quick look
at all tables.</P>
<P>The following query shows you what type of objects exist in a particular database.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>You can use <TT>ROWNUM</TT> to narrow
	down the results of your query to a specified number of rows for testing purposes.
	Oracle calls <TT>ROWNUM</TT> a pseudocolumn. <TT>ROWNUM</TT>, like <TT>ROWID</TT>,
	can be used on any database table or view. 
<HR>
</P>

</BLOCKQUOTE>

<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT DISTINCT(OBJECT_TYPE)</B>
  2 <B> FROM SYS.DBA_OBJECTS;</B>

OBJECT_TYPE
------------
CLUSTER
DATABASE LINK
FUNCTION
INDEX
PACKAGE
PACKAGE BODY
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TRIGGER
VIEW

12 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The <TT>DISTINCT</TT> function in the preceding query lists all unique object
types that exist in the database. This query is a good way to find out what types
of objects the database designers and developers are using.</P>
<P>The DBA_TABLES view gives specific information about database tables, mostly concerning
storage.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(OWNER,1,8) OWNER,</B>
  2   <B>      SUBSTR(TABLE_NAME,1,25) TABLE_NAME,</B>
  3         <B>SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME</B>
  4  <B>FROM SYS.DBA_TABLES</B>
  5 <B> WHERE OWNER = 'JSMITH';
</B>
OWNER    TABLE_NAME                TABLESPACE_NAME
-------- ------------------------  --------------------
JSMITH   MAGAZINE_TBL              USERS
JSMITH   HOBBY_TBL                 USERS
JSMITH   ADDRESS_TBL               SYSTEM
JSMITH   CUSTOMER_TBL              USERS

4 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>All tables are in the <TT>USERS</TT> tablespace except for <TT>ADDRESS_TBL</TT>,
which is in the <TT>SYSTEM</TT> tablespace. Because the only table you should ever
store in the <TT>SYSTEM</TT> tablespace is the <TT>SYSTEM</TT> table, the DBA needs
to be aware of this situation. It's a good thing you ran this query!</P>
<P>JSMITH should immediately be asked to move his table into another eligible tablespace.</P>
<P>The DBA_SYNONYMS view provides a list of all synonyms that exist in the database.
DBA_SYNONYMS gives a list of synonyms for all database users, unlike USER_SYNONYMS,
which lists only the current user's private synonyms.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SYNONYM_NAME,</B>
  2         <B>SUBSTR(TABLE_OWNER,1,10) TAB_OWNER,</B>
  3        <B> SUBSTR(TABLE_NAME,1,30) TABLE_NAME</B>
  4  <B>FROM SYS.DBA_SYNONYMS</B>
  5  <B>WHERE OWNER = 'JSMITH';</B>

SYNONYM_NAME                   TAB_OWNER  TABLE_NAME
------------------------------ ---------- ----------
TRIVIA_SYN                     VJOHNSON   TRIVIA_TBL

1 row selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The preceding output shows that JSMITH has a synonym called <TT>TRIVIA_SYN</TT>
on a table called <TT>TRIVIA_TBL</TT> that is owned by <TT>VJOHNSON</TT>.</P>
<P>Now suppose that you want to get a list of all tables and their indexes that belong
to <TT>JSMITH</TT>. You would write a query similar to the following, using <TT>DBA_INDEXES</TT>.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(TABLE_OWNER,1,10) TBL_OWNER,</B>
  2        <B> SUBSTR(TABLE_NAME,1,30) TABLE_NAME,</B>
  3    <B>     SUBSTR(INDEX_NAME,1,30) INDEX_NAME</B>
  4  <B>FROM SYS.DBA_INDEXES</B>
  5 <B> WHERE OWNER = 'JSMITH'</B>
  6   <B> AND ROWNUM &lt; 5</B>
  7  <B>ORDER BY TABLE_NAME;</B>

TBL_OWNER  TABLE_NAME                     INDEX_NAME
---------- ------------------------------ ------------
JSMITH     ADDRESS_TBL                    ADDR_INX
JSMITH     CUSTOMER_TBL                   CUST_INX
JSMITH     HOBBY_TBL                      HOBBY_PK
JSMITH     MAGAZINE_TBL                   MAGAZINE_INX

4 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>A query such as the previous one is an easy method of listing all indexes that
belong to a schema and matching them up with their corresponding table.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(TABLE_NAME,1,15) TABLE_NAME,</B>
  2       <B>  SUBSTR(INDEX_NAME,1,30) INDEX_NAME,</B>
  3      <B>   SUBSTR(COLUMN_NAME,1,15) COLUMN_NAME,</B>
  4       <B>  COLUMN_POSITION</B>
  5  <B>FROM SYS.DBA_IND_COLUMNS</B>
  6  <B>WHERE TABLE_OWNER = 'JSMITH'</B>
  7   <B> AND ROWNUM &lt; 10</B>
  8  <B>ORDER BY 1,2,3;</B>

TABLE_NAME      INDEX_NAME                     COLUMN_NAME     COLUMN_POSITION
--------------  ------------------------------ --------------  ---------------
ADDRESS_TBL     ADDR_INX                       PERS_ID                       1
ADDRESS_TBL     ADDR_INX                       NAME                          2
ADDRESS_TBL     ADDR_INX                       CITY                          3
CUSTOMER_TBL    CUST_INX                       CUST_ID                       1
CUSTOMER_TBL    CUST_INX                       CUST_NAME                     2
CUSTOMER_TBL    CUST_INX                       CUST_ZIP                      3
HOBBY_TBL       HOBBY_PK                       SAKEY                         1
MAGAZINE_TBL    MAGAZINE_INX                   ISSUE_NUM                     1
MAGAZINE_TBL    MAGAZINE_INX                   EDITOR                        2

9 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Now you have selected each column that is indexed in each table and ordered the
results by the order the column appears in the index. You have learned about tables,
but what holds tables? Tablespaces are on a higher level than objects such as tables,
indexes, and so on. Tablespaces are Oracle's mechanism for allocating space to the
database. To allocate space, you must know what tablespaces are currently available.
You can perform a select from DBA_TABLESPACES to see a list of all tablespaces and
their status, as shown in the next example.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT TABLESPACE_NAME, STATUS</B>
  2  <B>FROM SYS.DBA_TABLESPACES</B>

TABLESPACE_NAME                STATUS
------------------------------ ------
SYSTEM                         ONLINE
RBS                            ONLINE
TEMP                           ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
DATA_TS                        ONLINE
INDEX_TS                       ONLINE

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The preceding output tells you that all tablespaces are online, which means that
they are available for use. If a tablespace is offline, then the database objects
within it (that is, the tables) are not accessible.</P>
<P>What is JSMITH's quota on all tablespaces to which he has access? In other words,
how much room is available for JSMITH's database objects?</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT TABLESPACE_NAME,</B>
  2     <B>    BYTES,</B>
  3       <B>  MAX_BYTES</B>
  4 <B> FROM SYS.DBA_TS_QUOTAS</B>
  5  <B>WHERE USERNAME = 'JSMITH'</B>

TABLESPACE_NAME                     BYTES  MAX_BYTES
------------------------------  ---------- ----------
DATA_TS                         134111232         -1
INDEX_TS                        474390528         -1

2 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>JSMITH has an unlimited quota on both tablespaces to which he has access. In this
case the total number of bytes available in the tablespace is available on a first-come
first-served basis. For instance, if JSMITH uses all the free space in <TT>DATA_TS</TT>,
then no one else can create objects here.
<H4><FONT COLOR="#000077">Database Growth</FONT></H4>
<P>This section looks at two views that aid in the measurement of database growth:
DBA_SEGMENTS and DBA_EXTENTS. DBA_SEGMENTS provides information about each segment,
or object in the database such as storage allocation, space used, and extents. Each
time a table or index grows and must grab more space as identified by the <TT>NEXT_EXTENT</TT>,
the table takes another extent. A table usually becomes fragmented when it grows
this way. <TT>DBA_EXTENTS</TT> provides information about each extent of a segment.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,</B>
  2        <B> SUBSTR(SEGMENT_TYPE,1,12) SEGMENT_TYPE,</B>
  3        <B> BYTES,</B>
  4       <B>  EXTENTS,</B>
  5  <B>FROM SYS.DBA_SEGMENTS</B>
  6  <B>WHERE OWNER = 'TWILLIAMS'</B>
  7   <B> AND ROWNUM &lt; 5;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SEGMENT_NAME                   SEGMENT_TYPE      BYTES    EXTENTS
------------------------------ ------------ ---------- ----------
INVOICE_TBL                    TABLE            163840         10
COMPLAINT_TBL                  TABLE           4763783          3
HISTORY_TBL                    TABLE         547474996         27
HISTORY_INX                    INDEX         787244534         31

4 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>By looking at the output from DBA_SEGMENTS, you can easily identify which tables
are experiencing the most growth by referring to the number of extents. Both <TT>HISTORY_TBL</TT>
and <TT>HISTORY_INX</TT> have grown much more than the other two tables.</P>
<P>Next you can take a look at each extent of one of the tables. You can start with
<TT>INVOICE_TBL</TT>.</P>
<H5>INPUT/OUTPUT:</H5>