📄 ch16.htm
字号:
<PRE><FONT COLOR="#0066FF">SQL> <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 "data files." 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> <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> <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> <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> <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> <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&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, "Answers to Quizzes and Exercises."
<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.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch15.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch17.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch17.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A><BR>
<BR>
<BR>
<IMG SRC="corp.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>
<P>© <A HREF="copy.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>
</BODY>
</HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -