📄 ch16.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 16 -- Using Views to Retrieve Useful Information from the Data Dictionary</TITLE></HEAD><BODY TEXT="#000000" BGCOLOR="#FFFFFF"><CENTER><H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR><FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1></CENTER><CENTER><P><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"><IMGSRC="../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> <HR></CENTER><CENTER><H1><FONT COLOR="#000077">- Day 16 -<BR>Using Views to Retrieve Useful Information from the Data Dictionary</FONT></H1></CENTER><H2><FONT COLOR="#000077">Objectives</FONT></H2><P>Today we discuss the data dictionary, also known as the system catalog. By theend of the day, you should have a solid understanding of the following:<UL> <LI>The definition of the data dictionary <P> <LI>The type of information the data dictionary contains <P> <LI>Different types of tables within the data dictionary <P> <LI>Effective ways to retrieve useful information from the data dictionary</UL><H2><FONT COLOR="#000077">Introduction to the Data Dictionary</FONT></H2><P>Every relational database has some form of data dictionary, or system catalog.(We use both terms in today's presentation.) A <I>data dictionary</I> is a systemarea within a database environment that contains information about the ingredientsof a database. Data dictionaries include information such as database design, storedSQL code, user statistics, database processes, database growth, and database performancestatistics.</P><P>The data dictionary has tables that contain database design information, whichare populated upon the creation of the database and the execution of Data DefinitionLanguage (DDL) commands such as <TT>CREATE TABLE</TT>. This part of the system catalogstores information about a table's columns and attributes, table-sizing information,table privileges, and table growth. Other objects that are stored within the datadictionary include indexes, triggers, procedures, packages, and views.</P><P>User statistics tables report the status of items such as database connectivityinformation and privileges for individual users. These privileges are divided intotwo major components: system-level privileges and object-level privileges. The authorityto create another user is a system-level privilege, whereas the capability to accessa table is an object-level privilege. Roles are also used to enforce security withina database. This information is stored as well.</P><P>Day 16 extends what you learned yesterday (Day 15, "Streamlining SQL Statementsfor Improved Performance"). Data retrieved from the system catalog can be usedto monitor database performance and to modify database parameters that will improvedatabase and SQL statement performance.</P><P>The data dictionary is one of the most useful tools available with a database.It is a way of keeping a database organized, much like an inventory file in a retailstore. It is a mechanism that ensures the integrity of the database. For instance,when you create a table, how does the database server know whether a table with thesame name exists? When you create a query to select data from a table, how can itbe verified that you have been given the proper privileges to access the table? Thedata dictionary is the heart of a database, so you need to know how to use it.<H2><FONT COLOR="#000077">Users of the Data Dictionary</FONT></H2><P>End users, system engineers, and database administrators all use the data dictionary,whether they realize it or not. Their access can be either direct or indirect.</P><P>End users, often the customers for whom the database was created, access the systemcatalog indirectly. When a user attempts to log on to the database, the data dictionaryis referenced to verify that user's username, password, and privileges to connectto the database. The database is also referenced to see whether the user has theappropriate privileges to access certain data. The most common method for an enduser to access the data dictionary is through a front-end application. Many graphicaluser interface (GUI) tools, which allow a user to easily construct an SQL statement,have been developed. When logging on to the database, the front-end application mayimmediately perform a select against the data dictionary to define the tables towhich the user has access. The front-end application may then build a "local"system catalog for the individual user based on the data retrieved from the datadictionary. The customer can use the local catalog to select the specific tableshe or she wishes to query.</P><P>System engineers are database users who are responsible for tasks such as databasemodeling and design, application development, and application management. (Some companiesuse other titles, such as programmers, programmer analysts, and data modelers, torefer to their system engineers.) System engineers use the data dictionary directlyto manage the development process, as well as to maintain existing projects. Accessmay also be achieved through front-end applications, development tools, and computerassisted software engineering (CASE) tools. Common areas of the system catalog forthese users are queries against objects under groups of schemas, queries againstapplication roles and privileges, and queries to gather statistics on schema growth.System engineers may also use the data dictionary to reverse-engineer database objectsin a specified schema.</P><P>Database administrators (DBAs) are most definitely the largest percentage of directusers of the data dictionary. Unlike the other two groups of users, who occasionallyuse the system catalog directly, DBAs must explicitly include the use of the datadictionary as part of their daily routine. Access is usually through an SQL querybut can also be through administration tools such as Oracle's Server Manager. A DBAuses data dictionary information to manage users and resources and ultimately toachieve a well-tuned database.</P><P>As you can see, all database users need to use the data dictionary. Even moreimportant, a relational database cannot exist without some form of a data dictionary.<H2><FONT COLOR="#000077">Contents of the Data Dictionary</FONT></H2><P>This section examines the system catalogs of two RDBMS vendors, Oracle and Sybase.Although both implementations have unique specifications for their data dictionaries,they serve the same function. Don't concern yourself with the different names forthe system tables; simply understand the concept of a data dictionary and the datait contains.<H3><FONT COLOR="#000077">Oracle's Data Dictionary</FONT></H3><P>Because every table must have an owner, the owner of the system tables in an Oracledata dictionary is <TT>SYS</TT>. Oracle's data dictionary tables are divided intothree basic categories: user accessible views, DBA views, and dynamic performancetables, which also appear as views. Views that are accessible to a user allow theuser to query the data dictionary for information about the individual database account,such as privileges, or a catalog of tables created. The DBA views aid in the everydayduties of a database administrator, allowing the DBA to manage users and objectswithin the database. The dynamic performance tables in Oracle are also used by theDBA and provide a more in-depth look for monitoring performance of a database. Theseviews provide information such as statistics on processes, the dynamic usage of rollbacksegments, memory usage, and so on. The dynamic performance tables are all prefixed<TT>V$</TT>.<H3><FONT COLOR="#000077">Sybase's Data Dictionary</FONT></H3><P>As in Oracle, the owner of the tables in a Sybase data dictionary is <TT>SYS</TT>.The tables within the data dictionary are divided into two categories: system tablesand database tables.</P><P>The system tables are contained with the master database only. These tables defineobjects (such as tables and indexes) that are common through multiple databases.The second set of tables in a Sybase SQL Server data dictionary are the databasetables. These tables are related only to objects within each database.<H2><FONT COLOR="#000077">A Look Inside Oracle's Data Dictionary</FONT></H2><P>The examples in this section show you how to retrieve information from the datadictionary and are applicable to most relational database users, that is, systemengineer, end user, or DBA. Oracle's data dictionary has a vast array of system tablesand views for all types of database users, which is why we have chosen to exploreOracle's data dictionary in more depth.<H3><FONT COLOR="#000077">User Views</FONT></H3><P>User views are data dictionary views that are common to all database users. Theonly privilege a user needs to query against a user view is the <TT>CREATE SESSION</TT>system privilege, which should be common to all users.<H4><FONT COLOR="#000077">Who Are You?</FONT></H4><P>Before venturing into the seemingly endless knowledge contained within a database,you should know exactly who you are (in terms of the database) and what you can do.The following two examples show <TT>SELECT</TT> statements from two tables: one tofind out who you are and the other to see who else shares the database.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL><B> SELECT *</B> 2 <B>FROM USER_USERS;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">USERNAME USER_ID DEFAULT_TABLESPACE TEMPORARY TABLESPACE CREATED---------- ------ -------------------- -------------------- --------JSMITH 29 USERS TEMP 14-MAR-971 row selected.</FONT></PRE><H5>ANALYSIS:</H5><P>The USER_USERS view allows you to view how your Oracle ID was set up, when itwas set up, and it also shows other user-specific, vital statistics. The defaulttablespace and the temporary tablespace are also shown. The default tablespace, USERS,is the tablespace that objects will be created under as that user. The temporarytablespace is the designated tablespace to be used during large sorts and group functionsfor JSMITH.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2 <B>FROM ALL_USERS;</B>USERNAME USER_ID CREATED-------------- ------- ------------SYS 0 01-JAN-97SYSTEM 5 01-JAN-97SCOTT 8 01-JAN-97JSMITH 10 14-MAR-97TJONES 11 15-MAR-97VJOHNSON 12 15-MAR-97</FONT></PRE><P>As you can see in the results of the preceding query, you can view all users thatexist in the database by using the ALL_USERS view. However, the ALL_USERS view doesnot provide the same specific information as the previous view (USER_USERS) providedbecause there is no need for this information at the user level. More specific informationmay be required at the system level.<H4><FONT COLOR="#000077">What Are Your Privileges?</FONT></H4><P>Now that you know who you are, it would be nice to know what you can do. Severalviews are collectively able to give you that information. The USER_SYS_PRIVS viewand the USER_ROLE_PRIVS view will give you (the user) a good idea of what authorityyou have.</P><P>You can use the USER_SYS_PRIVS view to examine your system privileges. Remember,system privileges are privileges that allow you to do certain things within the databaseas a whole. These privileges are not specific to any one object or set of objects.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2 <B>FROM USER_SYS_PRIVS;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">USERNAME PRIVILEGE ADM-------- -------------------- ---JSMITH UNLIMITED TABLESPACE NOJSMITH CREATE SESSION NO2 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>JSMITH has been granted two system-level privileges, outside of any granted roles.Notice the second, <TT>CREATE SESSION</TT>. <TT>CREATE SESSION</TT> is also containedwithin an Oracle standard role, <TT>CONNECT</TT>, which is covered in the next example.</P><P>You can use the USER_ROLE_PRIVS view to view information about roles you havebeen granted within the database. Database roles are very similar to system-levelprivileges. A role is created much like a user and then granted privileges. Afterthe role has been granted privileges, the role can be granted to a user. Rememberthat object-level privileges may also be contained within a role.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2 <B>FROM USER_ROLE_PRIVS;</B>USERNAME GRANTED_ROLE ADM DEF OS_------------ ---------------- --- --- --JSMITH CONNECT NO YES NOJSMITH RESOURCE NO YES NO2 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>The USER_ROLE_PRIVS view enables you to see the roles that have been granted toyou. As mentioned earlier, <TT>CONNECT</TT> contains the system privilege <TT>CREATESESSION</TT>, as well as other privileges. <TT>RESOURCE</TT> has a few privilegesof its own. You can see that both roles have been granted as the user's default role;the user cannot grant these roles to other users, as noted by the Admin option (ADM);and the roles have not been granted by the operating system. (Refer to Day 12, "DatabaseSecurity.")<H4><FONT COLOR="#000077">What Do You Have Access To?</FONT></H4><P>Now you might ask, What do I have access to? I know who I am, I know my privileges,but where can I get my data? You can answer that question by looking at various availableuser views in the data dictionary. This section identifies a few helpful views.</P><P>Probably the most basic user view is USER_CATALOG, which is simply a catalog ofthe tables, views, synonyms, and sequences owned by the current user.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2 <B> FROM USER_CATALOG;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5><PRE><FONT COLOR="#0066FF">TABLE_NAME TABLE_TYPE---------------------------- ----------MAGAZINE_TBL TABLEMAG_COUNTER SEQUENCEMAG_VIEW VIEWSPORTS TABLE4 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>This example provides a quick list of tables and related objects that you own.You can also use a public synonym for USER_CATALOG for simplicity's sake: CAT. Thatis, try <TT>select * from cat;</TT>.</P><P>Another useful view is ALL_CATALOG, which enables you to see tables owned by otherindividuals.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2 <B>FROM ALL_CATALOG;</B></FONT></PRE><PRE><FONT COLOR="#0066FF">OWNER TABLE_NAME TABLE_TYPE-------------------- ------------------ ----------SYS DUAL TABLEPUBLIC DUAL SYNONYMJSMITH MAGAZINE_TBL TABLE JSMITH MAG_COUNTER SEQUENCEJSMITH MAG_VIEW VIEWJSMITH SPORTS TABLEVJOHNSON TEST1 TABLEVJOHNSON HOBBIES TABLEVJOHNSON CLASSES TABLEVJOHNSON STUDENTS VIEW10 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>More objects than appear in the preceding list will be accessible to you as auser. (The <TT>SYSTEM</TT> tables alone will add many tables.) We have simply shortenedthe list. The ALL_CATALOG view is the same as the USER_CATALOG view, but it showsyou all tables, views, sequences, and synonyms to which you have access (not justthe ones you own).</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT SUBSTR(OBJECT_TYPE,1,15) OBJECT_TYPE,</B> 2 <B> SUBSTR(OBJECT_NAME,1,30) OBJECT_NAME,</B>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -