ch16.htm

Learn SQL in an easy way...

<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(OWNER,1,10) OWNER,</B>
  2       <B>  SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,</B>
  3      <B>   EXTENT_ID,</B>
  4       <B>  BYTES</B>
  5 <B> FROM SYS.DBA_EXTENTS</B>
  6  <B>WHERE OWNER = 'TWILLIAMS'</B>
  7   <B> AND SEGMENT_NAME = 'INVOICE_TBL'</B>
  8  <B>ORDER BY EXTENT_ID;</B>

OWNER      SEGMENT_NAME                    EXTENT_ID     BYTES
---------- ------------------------------ ---------- --------
TWILLIAMS  INVOICE_TBL                            0      16384
TWILLIAMS  INVOICE_TBL                            1      16384
TWILLIAMS  INVOICE_TBL                            2      16384
TWILLIAMS  INVOICE_TBL                            3      16384
TWILLIAMS  INVOICE_TBL                            4      16384
TWILLIAMS  INVOICE_TBL                            5      16384
TWILLIAMS  INVOICE_TBL                            6      16384
TWILLIAMS  INVOICE_TBL                            7      16384
TWILLIAMS  INVOICE_TBL                            8      16384
TWILLIAMS  INVOICE_TBL                            9      16384

10 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This example displays each extent of the table, the <TT>extent_id</TT>, and the
size of the extent in bytes. Each extent is only 16K, and because there are 10 extents,
you might want to rebuild the table and increase the size of the <TT>initial_extent</TT>
to optimize space usage. Rebuilding the table will allow all the table's data to
fit into a single extent, and therefore, not be fragmented.
<H4><FONT COLOR="#000077">Space Allocated</FONT></H4>
<P>Oracle allocates space to the database by using &quot;data files.&quot; Space
logically exists within a tablespace, but data files are the physical entities of
tablespaces. In other implementations, data is also ultimately contained in data
files, though these data files may be referenced by another name. The view called
DBA_DATA_FILES enables you to see what is actually allocated to a tablespace.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(TABLESPACE_NAME,1,25) TABLESPACE_NAME,</B>
  2        <B> SUBSTR(FILE_NAME,1,40) FILE_NAME,
</B>  3  <B>       BYTES</B>
  4  <B>FROM SYS.DBA_DATA_FILES;
</B>
TABLESPACE_NAME           FILE_NAME                                     BYTES
------------------------- ---------------------------------------- ----------
SYSTEM                    /disk01/system0.dbf                        41943040
RBS                       /disk02/rbs0.dbf                          524288000
TEMP                      /disk03/temp0.dbf                         524288000
TOOLS                     /disk04/tools0.dbf                         20971520
USERS                     /disk05/users0.dbf                         20971520
DATA_TS                   /disk06/data0.dbf                         524288000
INDEX_TS                  /disk07/index0.dbf                        524288000

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You are now able to see how much space has been allocated for each tablespace
that exists in the database. Notice the names of the data files correspond to the
tablespace to which they belong.
<H4><FONT COLOR="#000077">Space Available</FONT></H4>
<P>As the following example shows, the DBA_FREE_SPACE view tells you how much free
space is available in each tablespace.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT TABLESPACE_NAME, SUM(BYTES)</B>
  2  <B>FROM SYS.DBA_FREE_SPACE</B>
  3  <B>GROUP BY TABLESPACE_NAME;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">TABLESPACE_NAME                SUM(BYTES)
------------------------------ ----------
SYSTEM                           23543040
RBS                             524288000
TEMP                            524288000
TOOLS                            12871520
USERS                              971520
DATA_TS                            568000
INDEX_TS                          1288000

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The preceding example lists the total free space for each tablespace. You can
also view each segment of free space by simply selecting bytes from DBA_FREE_SPACE
instead of <TT>SUM(bytes)</TT>.
<H4><FONT COLOR="#000077">Rollback Segments</FONT></H4>
<P>As areas for rolling back transactions are a crucial part to database performance,
you need to know what rollback segments are available. DBA_ROLLBACK_SEGS provides
this information.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT OWNER,</B>
  2         <B>SEGMENT_NAME</B>
  3 <B> FROM SYS.DBA_ROLLBACK_SEGS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">OWNER  SEGMENT_NAME
------ ------------
SYS    SYSTEM
SYS    R0
SYS    R01
SYS    R02
SYS    R03
SYS    R04
SYS    R05

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This example performs a simple select to list all rollback segments by name. Much
more data is available for your evaluation as well.
<H3><FONT COLOR="#000077">Dynamic Performance Views</FONT></H3>
<P>Oracle DBAs frequently access dynamic performance views because they provide greater
detail about the internal performance measures than many of the other data dictionary
views. (The DBA views contain some of the same information.)</P>
<P>These views involve extensive details, which is implementation-specific. This
section simply provides an overview of the type of information a given data dictionary
contains.
<H4><FONT COLOR="#000077">Session Information</FONT></H4>
<P>A <TT>DESCRIBE</TT> command of the V$SESSION views follows. (<TT>DESCRIBE</TT>
is an SQL*Plus command and will be covered on Day 20.) You can see the detail that
is contained in the view.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>DESCRIBE V$SESSION</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> Name                            Null?    Type
 ------------------------------  -------  ----
 SADDR                                    RAW(4)
 SID                                      NUMBER
 SERIAL#                                  NUMBER
 AUDSID                                   NUMBER
 PADDR                                    RAW(4)
 USER#                                    NUMBER
 USERNAME                                 VARCHAR2(30)
 COMMAND                                  NUMBER
 TADDR                                    VARCHAR2(8)
 LOCKWAIT                                 VARCHAR2(8)
 STATUS                                   VARCHAR2(8)
 SERVER                                   VARCHAR2(9)
 SCHEMA#                                  NUMBER
 SCHEMANAME                               VARCHAR2(30)
 OSUSER                                   VARCHAR2(15)
 PROCESS                                  VARCHAR2(9)
 MACHINE                                  VARCHAR2(64)
 TERMINAL                                 VARCHAR2(10)
 PROGRAM                                  VARCHAR2(48)
 TYPE                                     VARCHAR2(10)
 SQL_ADDRESS                              RAW(4)
 SQL_HASH_VALUE                           NUMBER
 PREV_SQL_ADDR                            RAW(4)
 PREV_HASH_VALUE                          NUMBER
 MODULE                                   VARCHAR2(48)
 MODULE_HASH                              NUMBER
 ACTION                                   VARCHAR2(32)
 ACTION_HASH                              NUMBER
 CLIENT_INFO                              VARCHAR2(64)
 FIXED_TABLE_SEQUENCE                     NUMBER
 ROW_WAIT_OBJ#                            NUMBER
 ROW_WAIT_FILE#                           NUMBER
 ROW_WAIT_BLOCK#                          NUMBER
 ROW_WAIT_ROW#                            NUMBER
 LOGON_TIME                               DATE
 LAST_CALL_ET                             NUMBER
</FONT></PRE>
<P>To get information about current database sessions, you could write a <TT>SELECT</TT>
statement similar to the one that follows from V$SESSION.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT USERNAME, COMMAND, STATUS</B>
  2  <B>FROM V$SESSION</B>
  3 <B> WHERE USERNAME IS NOT NULL;</B>

USERNAME                          COMMAND STATUS
------------------------------ ---------- --------
TWILLIAMS                               3 ACTIVE
JSMITH                                  0 INACTIVE

2 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>TWILLIAMS is logged on to the database and performing a select from the database,
which is represented by command 3.</P>
<P>JSMITH is merely logged on to the database. His session is inactive, and he is
not performing any type of commands. Refer to your database documentation to find
out how the commands are identified in the data dictionary. Commands include <TT>SELECT</TT>,
<TT>INSERT</TT>, <TT>UPDATE</TT>, <TT>DELETE</TT>, <TT>CREATE TABLE</TT>, and <TT>DROP
TABLE</TT>.
<H4><FONT COLOR="#000077">Performance Statistics</FONT></H4>
<P>Data concerning performance statistics outside the realm of user sessions is also
available in the data dictionary. This type of data is much more implementation specific
than the other views discussed today.</P>
<P>Performance statistics include data such as read/write rates, successful hits
on tables, use of the system global area, use of memory cache, detailed rollback
segment information, detailed transaction log information, and table locks and waits.
The well of knowledge is almost bottomless.
<H3><FONT COLOR="#000077">The Plan Table</FONT></H3>
<P>The <TT>Plan</TT> table is the default table used with Oracle's SQL statement
tool, <TT>EXPLAIN PLAN</TT>. (See Day 15.) This table is created by an Oracle script
called <TT>UTLXPLAN.SQL</TT>, which is copied on to the server when the software
is installed. Data is generated by the <TT>EXPLAIN PLAN</TT> tool, which populates
the <TT>PLAN</TT> table with information about the object being accessed and the
steps in the execution plan of an SQL statement.
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>Although the details of the data dictionary vary from one implementation to another,
the content remains conceptually the same in all relational databases. You must follow
the syntax and rules of your database management system, but today's examples should
give you the confidence to query your data dictionary and to be creative when doing
so.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Exploring the data dictionary is
	an adventure, and you will need to explore in order to learn to use it effectively.
	
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q Why should I use the views and tables in the data dictionary?</B>
	<P><B>A</B> Using the views in the data dictionary is the most accurate way to discover
	the nature of your database. The tables can tell you what you have access to and
	what your privileges are. They can also help you monitor various other database events
	such as user processes and database performance.</P>
	<P><B>Q How is the data dictionary created?</B></P>
	<P><B>A</B> The data dictionary is created when the database is initialized. Oracle
	Corporation provides several scripts to run when creating each database. These scripts
	create all necessary tables and views for that particular database's system catalog.</P>
	<P><B>Q How is the data dictionary updated?</B></P>
	<P><B>A</B> The data dictionary is updated internally by the RDBMS during daily operations.
	When you change the structure of a table, the appropriate changes are made to the
	data dictionary internally. You should never attempt to update any tables in the
	data dictionary yourself. Doing so may cause a corrupt database.</P>
	<P><B>Q How can I find out who did what in a database?</B></P>
	<P><B>A</B> Normally, tables or views in a system catalog allow you to audit user
	activity.
</DL>

<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;
<H3><FONT COLOR="#000077">Quiz</FONT></H3>

<DL>
	<DD><B>1. </B>In Oracle, how can you find out what tables and views you own?
	<P><B>2.</B> What types of information are stored in the data dictionary?</P>
	<P><B>3.</B> How can you use performance statistics?</P>
	<P><B>4. </B>What are some database objects?
</DL>

<H3><FONT COLOR="#000077">Exercise</FONT></H3>
<P>Suppose you are managing a small to medium-size database. Your job responsibilities
include developing and managing the database. Another individual is inserting large
amounts of data into a table and receives an error indicating a lack of space. You
must determine the cause of the problem. Does the user's tablespace quota need to
be increased, or do you need to allocate more space to the tablespace? Prepare a
step-by-step list that explains how you will gather the necessary information from
the data dictionary. You do not need to list specific table or view names.</P>
<CENTER>
<P>
<HR>
<A HREF="../ch15/ch15.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch17/ch17.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A><BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>