📄 ch16.htm
字号:
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> <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, "Transact-SQL:
An Introduction," and Day 20, "SQL*Plus."
<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> <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> <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> <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> <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><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> <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> <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 "see"
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> <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> <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
------------------------------ --------
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -