ORACLE8i- P5

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:40

0
37
lượt xem
3
download

ORACLE8i- P5

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

ORACLE8i- P5: We want information… information...” Possibly you recognize these words as the primary interest of a somewhat clandestine group, and as told by a character called Number 2 to Patrick McGoohan’s character Number 6 (in the old TV show The Prisoner). Indeed, in this day, information is king, and the speedy, accurate, and reliable retrieval of this information is paramount.

Chủ đề:
Lưu

Nội dung Text: ORACLE8i- P5

  1. 150 CHAPTER 4 • THE DATA DICTIONARY -- Now, grant select on the dba_data_files view to scott. SQL> grant select on dba_data_files to scott; Grant succeeded. SQL> connect scott/tiger Connected. SQL> alter procedure get_all_bytes compile; Procedure altered. SQL> set serveroutput on SQL> exec get_all_bytes Total bytes: 141557760 PL/SQL procedure successfully completed. -- Now, create a procedure that runs with the rights of the owner -- Create the procedure as SYS because it owns the tables. -- We could also create an account and grant each table to it directly. SQL> connect sys/change_on_install Connected. CREATE OR REPLACE PROCEDURE get_all_bytes AUTHID DEFINER AS total_bytes NUMBER; BEGIN SELECT SUM(bytes) INTO total_bytes FROM dba_data_files; DBMS_OUTPUT.PUT_LINE(‘Total bytes: ‘||total_bytes); END; / Procedure created. SQL> grant execute on get_all_bytes to scott; Grant succeeded. SQL> connect scott/tiger Connected. SQL> set serveroutput on SQL> exec sys.get_all_bytes Total bytes: 249561088 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  2. USING THE ORACLE DATA DICTIONARY 151 Using the Oracle Data Dictionary PA R T I Now that you know what the data dictionary is and how it came to be, we urge you not to stop here! Don’t fall into the trap of being a DBA who doesn’t know how to use your data dictionary to effectively manage your database. Unfortunately, many DBAs Oracle Essentials are not comfortable with the data dictionary’s tables and views and don’t use them regularly. This may be because they learned to administer the database itself through a GUI tool such as Oracle’s Enterprise Manager, which reduces the need to refer to these tables. You’ll be a better DBA, however, when you know how to navigate a pro- ductive course through the data dictionary and its contents. Appendix H is a quick reference to all the data dictionary and dynamic perfor- mance views. NOTE Beyond its maintenance as a result of Oracle’s DDL operations, the data dictio- nary is also used during recursive SQL operations. When you execute a SQL statement, Oracle goes recursively through the data dictionary to make sure that your SQL statement is valid. Among the items checked are the presence of the object in the SQL statement (for example, do the tables being inserted into really exist?), and whether the columns in that object in fact exist. Often, when you see ORA-type errors, it’s because something in the recursive table lookups didn’t work out. You may have entered an invalid column or object name, and the recursive lookups failed to find it. In this section, we introduce you to some of the common data dictionary views; you’ll encounter these and others throughout this book. Then we end the chapter with some typical uses for data dictionary information; you can tailor these for use in your day-to-day administrative tasks. Using the X$ Tables You won’t often need to look at the X$ tables. They are only minimally documented by Oracle, and they change from version to version of Oracle. The X$ tables may differ slightly across various Oracle releases. For a complete list of the X$ tables, you can traverse the V$FIXED_TABLE view (which also contains the V$, GV$ and other data dictionary items). This section covers a few of the more commonly used X$ tables and a description of their purpose, in addition to occasions when you can find them useful. You’ll see them used in other chapters of this book, as well. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  3. 152 CHAPTER 4 • THE DATA DICTIONARY • X$BH allows you to track the usage of the database buffer cache. • X$KCBFWAIT allows you to track buffer busy waits to specific database datafiles. • X$KSMLRU lists the ten largest allocations in the shared pool. You’ll see an example shortly. • X$KSPPI, together with X$KSPPCV, helps you find hidden Oracle parameters. Made for the pirate in all of us! Arrrrrghhhhh…. • X$KSPPCV, along with X$KSPPI, helps you to find hidden Oracle parameters. There’s an example coming up. X$BH The X$BH table offers a great deal of potentially interesting information for the DBA. This view allows you to track the usage of the database buffer cache. For each buffer in the cache, you can see the file and block address that the buffer represents. N OTE With the new touch-point algorithm in Oracle8i (which replaces the old LRU algorithm), we can tell how often a particular block has been “touched,” or used, by the database. Thus, we now have a mechanism to determine which blocks in the database are truly hot. Some of the more useful columns in the X$BH table include the following: ADDR Buffer address. STATE Current state of the buffer: 0 - Free (never been used) 1 - Either available or being used, depending on the status of the IRBA_SEQ column (if IRBA_SEQ=0, it is available; otherwise it is being used) 3 - In use IRBA_SEQ If > 0, this is the type of operation being conducted on the block. If 0, the block is available for use. TS# Tablespace number to which the block is assigned. You can reference the TS$ data dictionary view (using columns TS# and NAME) to get the name of the associated tablespace. FILE# The absolute file number to which the block is assigned. You can ref- erence the DBA_DATA_FILES column FILE_ID to get the name of the datafile or the tablespace name associated with the datafile. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  4. USING THE ORACLE DATA DICTIONARY 153 DBABLK Block’s assigned number within the assigned datafile. PA R T TCH This is the “touch” counter, which is incremented each time the I buffer is touched. Gives an indication of how “hot” (popular) the block is. Listing 4.2 is the code for a report that tells us which database blocks are the hot Oracle Essentials blocks. It provides the name of the segment and the datafile in which it belongs. Be aware that this query can take some time to complete, depending on the size of your database buffer cache and the number of overall segments, as well as the number of datafiles in your database. Listing 4.2: Listing the Hot Blocks in the Database Buffer Cache COLUMN segment_name FORMAT a30 COLUMN file_name FORMAT a60 SET LINES 132 SELECT * FROM (SELECT a.tch, a.file#, a.dbablk, b.segment_name, c.file_name FROM x$bh a, dba_extents b, dba_data_files c WHERE a.file#=b.file_id AND a.dbablk >= b.block_id AND a.dbablk < b.block_id+b.blocks AND a.file#=c.file_id ORDER BY 1 DESC) WHERE rownum < 20; X$KCBFWAIT The X$KCBFWAIT table provides with a list of files that are experiencing a great num- ber of buffer busy waits. Using the following query, you can determine which datafiles are experiencing the worst wait problems: SELECT count, file#, name FROM x$kcbfwait, v$datafile WHERE indx + 1 = file# ORDER BY count desc; You’ll find complete coverage of wait monitoring in Chapter 17. X$KSMLRU The X$KSMLRU table can be used to monitor the latest large object (LOB) allocations in the shared pool. In earlier versions of Oracle8i, the contents of this table were erased after a SELECT statement was issued against it. This is not the case with later versions— however, the table may change quickly, particularly in a dynamic environment. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  5. 154 CHAPTER 4 • THE DATA DICTIONARY X$KSMLRU returns the 10 latest allocations, so for use in ongoing analysis, it’s a good idea to create a second table and dump the contents of X$KSMLRU into it from time to time. Let’s say you want to figure out which large PL/SQL objects you should pin in the shared pool. (“Pinning” an object means forcing Oracle to keep it. Pinning objects in the shared pool is discussed in later chapters.) You can use a query like that shown in Listing 4.3. This query returns the six largest users of the shared pool. You will proba- bly want to change the number of rows returned as needed. In this example, we can see that a stored procedure gets loaded quite a bit, as well as some large SQL state- ments. We might well want to find those SQL statements and create them as PL/SQL statements instead. Listing 4.3: Using X$KSMLRU to Determine Objects to Pin in the Shared Pool SELECT * FROM (SELECT ksmlrhon, ksmlrsiz FROM x$ksmlru ORDER BY ksmlrsiz) WHERE rownum < 6; KSMLRHON KSMLRSIZ -------------------------------- --------- SP_1438_SEL_S_INVOICE 1060 SEG$ 2232 PKG_3541_CALL_STATISTICS 4120 BEGIN pkg_4488_sel_m_eqp_src... 4132 SELECT * FROM “CSTTDD” 4292 select a.* from csttdd a, cs... 4324 X$KSPPI and X$KSPPCV There is a class of parameters that is not available to you in V$PARAMETER or any V$ view: the hidden or undocumented parameters. But you can see them in the X$KSPPI and X$KSPPCV tables, which contain all the various parameters and their current and default settings in Oracle. These tables are made for the pirate in all of us! Oracle has more hidden and undocumented parameters than it has documented ones. Many of these parameters are dangerous and should never be tinkered with unless you have guidance from Oracle Support, and not until they’ve been tested in a non- production environment first. Nevertheless, some of the hidden parameters can be very useful from time to time. Often when we install a new Oracle version, we produce Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  6. USING THE ORACLE DATA DICTIONARY 155 a list of hidden parameters just to see what’s out there. The SQL in Listing 4.4 gives PA R T you this list for Oracle8i. I Note that the SQL in this listing may change somewhat with new Oracle software versions (because Oracle never guarantees that database views won’t change). Because the X$ tables might change in 9i, this code may well not work. As a result, you may have to do a little experimentation to see what might have changed (or you can buy Oracle Essentials the 9i version of this book when it comes out!). Listing 4.4: Querying for Hidden Parameters SELECT a.ksppinm “Name”, b.ksppstvl “Current Value”, b.ksppstdf “Original Default?” FROM x$ksppi a, x$ksppcv b WHERE a.indx=b.indx AND substr(ksppinm,1,1)=‘_’ ORDER BY 1; -- and a partial result Name Current Value Origional Default? ------------------------- ------------------------------ ------------------ _trace_flushing FALSE TRUE _trace_multi_block_reads FALSE TRUE _trace_write_batch_size 32 TRUE _tts_allow_nchar_mismatch FALSE TRUE _unnest_subquery FALSE TRUE _use_ism TRUE TRUE _use_nosegment_indexes FALSE TRUE _use_vector_post TRUE TRUE _wait_for_sync TRUE TRUE _walk_insert_threshold 0 TRUE _write_clones 3 TRUE _yield_check_interval 100 TRUE Using the DBA_, ALL_, and USER_ Views The DBA_, ALL_, and USER_ views are used primarily for database administration pur- poses. This is in contrast to the dynamic performance (V$) views that contribute pri- marily to database tuning (although they are handy for backup and recovery, as well). As stated earlier in this chapter, a user’s privileges determines which views he or she can query. In the ALL_ view, the grants to specific objects determine what objects Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  7. 156 CHAPTER 4 • THE DATA DICTIONARY the user can see. All users who have the ability to log in to the database can query the USER_ and ALL_ views, giving users a way to tell what objects they own and have access to. Note that there are some DBA views that do not exist as ALL_ or USER_ views. This is generally for security purposes. Also, you will find that columns don’t appear in all of these views. This is sometimes for security purposes, and sometimes because those columns are simply not needed for a particular view. For example, with the USER_ views, there is really no need for the username column NOW. The DBA_ Views These views are used by the DBA to get an overall look at the entire database. For example: • Documenting the database. This involves locating all objects in the database, all datafiles, rollback segments and so on. We will cover this in more detail shortly. • Performing security audits of the database. • Finding out where and when certain problems, such as space problems, are likely to occur. • Determining how much free space remains in the database. • Determining what users are set up on the system, and what default and tempo- rary tablespaces are assigned to those users. WARN I NG There are no restrictions on the DBA_ views; their information is avail- able to all DBAs—that’s exactly why you need to be careful about giving DBA privileges. Access to DBA_ views is granted through use of the DBA role (see later in this section for instructions on granting users access to specific DBA_ views, if that is required). Never grant the DBA role just to accommodate temporary access to a DBA_ view. Substantial risk is associated with the DBA role, as discussed in Chapter 21. Following is an example of using the DBA_USERS dictionary view to produce a report of all users. We want to make sure that no one is using the SYSTEM tablespace for the TEMPORARY tablespace (as is the default setting in Oracle). Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  8. USING THE ORACLE DATA DICTIONARY 157 SQL> SELECT username, temporary_tablespace FROM dba_users; PA R T I USERNAME TEMPORARY_TABLESPACE ---------- -------------------- SYS SYSTEM SYSTEM SYSTEM Oracle Essentials OUTLN SYSTEM DBSNMP SYSTEM SID SYSTEM TEST TEMP SCOTT2 TEMP SCOTT TEMP As you can see from the report above, we probably need to modify temporary tablespace settings for several users, in order to avoid the problems with sort segments in the SYSTEM tablespace (this problem is discussed in Chapter 6). The DBA_ reports offer all sorts of information like this. In the later section “Documenting the Data- base,” you’ll see many examples of queries and the resulting output that will help you document your database configuration. NOTE A new role is available in 8i that allows you to give users access to the data dic- tionary views but not DBA privileges (as with the DBA role). The SELECT_CATALOG_ROLE role allows the user assigned to this role to select from all the data dictionary views. The USER_ Views Here is the output from a query to the USER_TABLES view for the SCOTT schema: SQL> SELECT table_name, tablespace_name, pct_free, pct_used, pct_increase FROM user_tables; TABLE_NAME TABLESPACE_NAME PCT_FREE PCT_USED PCT_INCREASE ------------------------ --------------- ---------- ---------- ------------ BONUS USERS 10 40 50 CHILD USERS 10 40 50 DEPT USERS 10 40 50 DUMMY USERS 10 40 50 EMPLOYEE USERS 10 40 50 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  9. 158 CHAPTER 4 • THE DATA DICTIONARY HOLD_EVENTS USERS 10 40 50 NEWTABLE USERS 10 40 50 PARENT USERS 10 40 50 PAY_TABLE USERS 10 40 50 SALGRADE USERS 10 40 50 TEMP_EMP USERS 10 40 50 TEMP_EMPLOYEE USERS 10 40 50 TESTME USERS 10 40 50 TEST_ME USERS 10 40 50 TEST_RGF USERS 10 40 50 In this query, we are looking for the list of tables owned by the user SCOTT, and SCOTT only. He may have access to other tables, but they will not be listed in this view. Because these objects are only objects that belong to the SCOTT schema, there is no need for an OWNER column. You will find that there is no OWNER column in most of the USER_ views. Following is a second example of querying a USER_ view: SQL> SELECT username, account_status, expiry_date, default_tablespace FROM user_users; USERNAME ACCOUNT_STATUS EXPIRY_DA DEFAULT_TABLESPACE ---------- ---------------- --------- ------------------------------ SCOTT OPEN 31-MAR-01 USERS Here we find only our own account information. We can see that our account pass- word expires on March 31. We can also see in the ACCOUNT_STATUS column whether our password has expired and whether the system is allowing us to use grace logins. You’ll see many other examples of USER_ views throughout this book. The ALL_ Views Since users can access any Oracle object, provided the correct grants are given, you need a way to query the data dictionary metadata on those objects. Enter the ALL_ views, which provide a complete picture of objects you have access to, and other important database-level information. Because of security concerns, use of the ALL_ views is more restricted. For example, the ALL_USERS view only contains three columns, as opposed to ten columns for USER_USERS and twelve columns for DBA_USERS. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  10. USING THE ORACLE DATA DICTIONARY 159 The next example is a query against the ALL_TABLES view. Contrast this against PA R T the earlier query on USER_TABLES. Note the inclusion of the OWNER column; this I view can now report on multiple schemas if you have access to objects in multiple schemas. You can see some tables in the SCOTT2 schema in our example, which implies that we have some form of access to these tables. Of course, there are other views that will tell us what accesses we have, but we’ll save that for Chapter 21. Oracle Essentials SQL> SELECT owner, table_name, tablespace_name, pct_free, pct_used, pct_increase from all_tables WHERE OWNER NOT IN (‘SYS’,’SYSTEM’,’OUTLN’); OWNER TABLE_NAME TABLESPACE_NAME PCT_FREE PCT_USED PCT_INCREASE ------- --------------- ------------------ -------- ---------- ------------ SCOTT DEPT USERS 10 40 50 SCOTT BONUS USERS 10 40 50 SCOTT SALGRADE USERS 10 40 50 SCOTT DUMMY USERS 10 40 50 SCOTT EMPLOYEE USERS 10 40 50 SCOTT TEST_RGF USERS 10 40 50 SCOTT TESTME USERS 10 40 50 SCOTT NEWTABLE USERS 10 40 50 SCOTT TEST_ME USERS 10 40 50 SCOTT PAY_TABLE USERS 10 40 50 SCOTT2 EMP LOCAL_UNIFORM 10 40 0 SCOTT2 DEPT LOCAL_UNIFORM 10 40 0 SCOTT2 BONUS LOCAL_UNIFORM 10 40 0 SCOTT2 SALGRADE LOCAL_UNIFORM 10 40 0 SCOTT2 DUMMY LOCAL_UNIFORM 10 40 0 SCOTT TEMP_EMPLOYEE USERS 10 40 50 SCOTT TEMP_EMP USERS 10 40 50 SCOTT PARENT USERS 10 40 50 SCOTT CHILD USERS 10 40 50 SCOTT HOLD_EVENTS USERS 10 40 50 Using the V$ Dynamic Performance Views The V$ dynamic performance views are used primarily for the following purposes: • For database performance monitoring and tuning, as discussed in Chapters 15 through 17. • To facilitate recovery solutions for the database, as discussed in Chapters 10 through 13. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  11. 160 CHAPTER 4 • THE DATA DICTIONARY • For database documentation. In particular, the V$ views provide information on the redo logs and the control files. The rest of this chapter demonstrates this use in documenting particular database structures. NOTE Despite their name, the dynamic performance views are not truly “dynamic,” in the sense that they are updated on a real-time basis. They should not be expected to provide real-time information. Documenting the Database This section presents a series of queries on data dictionary views that you can run to help you document your Oracle database. These reports will come in handy when you need to recover parts of your database, and when you run into odd problems with things such as user accesses. In addition, studying and understanding the reports will help you see how to better use the data dictionary. We have included SQL*Plus formatting commands in the headers of these reports, so they should look as good on your screen as they do ours! Many of them utilize 132- character lines, so if you’re going to print the output, you will want to do so in land- scape mode. Also, notice that we’ve excluded the SYS and SYSTEM users in many of these reports. When you want to reinstate this data, simply remove the query lines that cause these users to be excluded. All sorts of SQL code is used in these examples—outer joins, unions, and more. We hope the examples will get you started creating your own set of useful scripts for querying your data dictionary. The idea here is to get your feet wet, so that as you come across more detailed queries in other chapters of this book and in your everyday Oracle environment, you will develop a feeling of comfort with the data dictionary. When you can navigate it with ease, you will be a better DBA. Database Tablespaces and Associated Datafiles The query in Listing 4.5 will report on your database’s tablespaces and the datafiles associated with those tablespaces. The output also includes the size of each datafile, and if you run this query in SQL*Plus, it will break on each tablespace, giving you the total size of each. In this listing we use several columns of the DBA_DATA_FILES data dictionary view, including TABLESPACE_NAME and FILE_NAME (to give us the name of the datafile associated with the tablespace, because each tablespace can have more Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  12. DOCUMENTING THE DATABASE 161 than one datafile). Also, we include the BYTES column. The listing also shows the PA R T SQL*Plus report with sample output. I Listing 4.5: Report on Tablespaces and Datafiles TTITLE CENTER “Database Tablespace and Datafile Report” COLUMN no_prt NOPRINT Oracle Essentials BREAK ON no_prt SKIP 2 COMPUTE SUM OF bytes ON no_prt COMPUTE SUM OF bytes ON report COLUMN bytes FORMAT 9,999,999,999 SELECT tablespace_name no_prt, tablespace_name, file_name, bytes FROM dba_data_files ORDER BY 1,2; Database Tablespace and Datafile Report TABLESPACE_NAME FILE_NAME BYTES ---------------- ----------------------------------------------------- --------------------- LOCAL_UNIFORM D:\ORACLE\ORADATA\ORA816\ORA816_LOCAL_UNIFORM_01.DBF 20,971,520 -------------- 20,971,520 RBS D:\ORACLE\ORADATA\ORA816\ORA816_RBS_01.DBF 20,971,520 -------------- 20,971,520 SYSTEM D:\ORACLE\ORADATA\ORA816\SYSTEM01.DBF 57,671,680 -------------- 57,671,680 TEMP D:\ORACLE\ORADATA\ORA816\ORA816_TEMP_01.DBF 10,485,760 -------------- 10,485,760 USERS D:\ORACLE\ORADATA\ORA816\ORA816_USERS_01.DBF 31,457,280 USERS D:\ORACLE\ORADATA\ORA816\USERS02.DBF 5,242,880 -------------- 36,700,160 What can you deduce from this report? First of all, there is a problem with consis- tency in the naming convention for datafiles. This is always a serious management issue. (If you don’t agree, you might want to reconsider your choice of career…or, at Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  13. 162 CHAPTER 4 • THE DATA DICTIONARY least, reread “Naming Conventions” in Chapter 3.). Whether you’re naming file sys- tems or datafiles or the objects within your database, be consistent! Online Redo Logs You can use the V$LOG and V$LOGFILE parameters to document the location and sizes of the online redo logs. Listing 4.6 shows a report that uses the data dictionary to document the location, number, and size of these logs on a Unix system. It is sorted by each redo log group, and then by the group member. Incidentally, this list- ing is a good example of spreading redo log group members among disks. The first members of the groups are sitting on disk /ora010, and the second member of each redo log group is sitting on disk /ora110. Listing 4.6: Documenting Your Redo Logs COLUMN member FORMAT a60 SET LINES 132 TTITLE CENTER “Redo Log Report” BREAK ON REPORT COMPUTE SUM OF bytes ON report COLUMN bytes FORMAT 9,999,999,999 SELECT a.group#, b.member, a.bytes FROM v$log a, v$logfile b WHERE a.group#=b.group# ORDER by 1,2; GROUP# MEMBER BYTES --------- -------------------------------------------------- -------------- 1 /ora101/oracle/DBDB/redo/dbdb_redo01a.log 104,857,600 1 /ora201/oracle/DBDB/redo/dbdb_redo01b.log 104,857,600 2 /ora101/oracle/DBDB/redo/dbdb_redo02a.log 104,857,600 2 /ora201/oracle/DBDB/redo/dbdb_redo02b.log 104,857,600 3 /ora101/oracle/DBDB/redo/dbdb_redo03a.log 104,857,600 3 /ora201/oracle/DBDB/redo/dbdb_redo03b.log 104,857,600 4 /ora101/oracle/DBDB/redo/dbdb_redo04a.log 104,857,600 4 /ora201/oracle/DBDB/redo/dbdb_redo04b.log 104,857,600 5 /ora101/oracle/DBDB/redo/dbdb_redo05a.log 104,857,600 5 /ora201/oracle/DBDB/redo/dbdb_redo05b.log 104,857,600 6 /ora101/oracle/DBDB/redo/dbdb_redo06a.log 104,857,600 6 /ora201/oracle/DBDB/redo/dbdb_redo06b.log 104,857,600 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  14. DOCUMENTING THE DATABASE 163 7 /ora101/oracle/DBDB/redo/dbdb_redo07a.log 104,857,600 PA R T 7 /ora201/oracle/DBDB/redo/dbdb_redo07b.log 104,857,600 I 8 /or1a01/oracle/DBDB/redo/dbdb_redo08a.log 104,857,600 8 /or2a01/oracle/DBDB/redo/dbdb_redo08b.log 104,857,600 9 /or1a01/oracle/DBDB/redo/dbdb_redo09a.log 104,857,600 9 /or2a01/oracle/DBDB/redo/dbdb_redo09b.log 104,857,600 Oracle Essentials 10 /or1a01/oracle/DBDB/redo/dbdb_redo10a.log 104,857,600 10 /or2a01/oracle/DBDB/redo/dbdb_redo10b.log 104,857,600 -------------- sum 2,097,152,000 Control Files You can use the V$PARAMETER and V$CONTROLFILE_RECORD_SECTION views to get the location and size of the control files. For example, should you need to re- create the init.ora parameter file, it helps to know where the control files used to be. Also, such a report helps you analyze the distribution of these files to ensure they are protected against accidental loss. Listing 4.7 is a nice little script that reports the loca- tion and size of each control file. Listing 4.7: Documenting the Control Files COLUMN NAME FORMAT a60 SET LINES 132 TTITLE CENTER “Control File Report” BREAK ON REPORT COMPUTE SUM OF bytes ON REPORT COLUMN bytes FORMAT 9,999,999,999 SELECT c.name, b.db_block_value * (1 + 2 * sum(ceil(record_size * records_total / b.db_block_value))) bytes FROM sys.v_$controlfile_record_section a, (SELECT value db_block_value FROM v$parameter WHERE name=‘db_block_size’) b, v$controlfile c GROUP by c.name, b.db_block_value; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  15. 164 CHAPTER 4 • THE DATA DICTIONARY Control File Report NAME BYTES ----------------------------------------------------- -------------- /ora101/oracle/DBDB/crtl/control01.ctl 9,814,016 /ora201/oracle/DBDB/crtl/control02.ctl 9,814,016 /ora301/oracle/DBDB/crtl/control03.ctl 9,814,016 -------------- sum 29,442,048 NOTE The number of bytes reported in this output is a close approximation. This script works fine on Solaris 2.6 with Oracle 8.1.6.3, but was about 8 blocks short on an NT sys- tem running 8.1.6. On other NT systems, however, it worked just fine (go figure!). Current Parameters in Use There are many times when the DBA needs to know the current setting of a database parameter. You’ll also have frequent questions about the names and values of hidden parameters. In Chapter 3 we discussed the possibility of changing parameters dynami- cally. This report will provide the information you need in order to know if a parame- ter can be changed on-the-fly. For session parameters, only two possibilities are reported: The parameter can either change (YES) or it cannot (NO). For system-level parameters, there are three possibilities: The parameter can change as soon as you issue the command (IMMEDI- ATE), or it will change as soon as your session exits (DEFERRED), or it cannot be changed at all (NO). The report also tells you whether the parameter is currently mod- ified or is taking its default setting. The report shown in Listing 4.8 has parameters sorted by name, with hidden para- meters at the top. There’s also a description of the parameter, which we have set up to display on a second line for each parameter in the report. NOTE If you dig a little, you’ll find that this report has many elements in common with the code for the V$PARAMETER view. This is one example of how you can study Oracle’s handling of the data dictionary views and modify them to provide the data you really want. You might even consider turning this report into a view, named perhaps MYV$_ALL_ PARAMETERS. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  16. DOCUMENTING THE DATABASE 165 Listing 4.8: Report of Current Database Parameters PA R T SET LINES 132 I SET PAGES 66 TTITLE CENTER “Parameter Setting Report” COLUMN “Parameter Name” FORMAT a25 WRAP Oracle Essentials COLUMN “Value” FORMAT a20 WRAP COLUMN “Default?” FORMAT a5 COLUMN “Session Modifiable” FORMAT a4 COLUMN “System Modifiable” FORMAT a12 COLUMN “Currently Modified” FORMAT a4 COLUMN “Oracle adjusted” FORMAT a4 COLUMN “Parameter Description” FORMAT a80 SELECT ksppinm “Parameter Name”, ksppstvl “Value”, ksppstdf “Default?”, DECODE(BITAND(ksppiflg/256,1),1,’YES’,’NO’) “Session Modifiable”, DECODE(BITAND(ksppiflg/65536,3),1,’IMMEDIATE’,2,’DEFERRED’, 3,’IMMEDIATE’,’NO’) “System Modifiable “, DECODE(BITAND(ksppstvf,7),1,’YES’,4,’SYSTEM_MOD’,’NO’) “Currently Modified”, DECODE(BITAND(ksppstvf,2),2,’YES’,’NO’) “Oracle Adjusted”, SUBSTR(ksppdesc,1,80) “Parameter Description” FROM x$ksppi x, x$ksppcv y WHERE (x.indx = y.indx) ORDER BY ksppinm; Parameter Setting Report Parameter Name Value Defau Sess System Mo Curr Orac ------------------------- -------------------- ----- ---- --------- ---- ---- Parameter Description ---------------------------------------------------------------- _use_vector_post TRUE TRUE NO NO NO NO use vector post _wait_for_sync TRUE TRUE NO NO NO NO wait for sync on commit MUST BE ALWAYS TRUE _walk_insert_threshold 0 TRUE NO NO NO NO maximum number of unusable blocks to walk across freelist Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  17. 166 CHAPTER 4 • THE DATA DICTIONARY _write_clones 3 TRUE NO IMMEDIATE NO NO write clones flag _yield_check_interval 100 TRUE YES IMMEDIATE NO NO interval to check whether actses should yield active_instance_count TRUE NO NO NO NO number of active instances in the parallel server always_anti_join NESTED_LOOPS TRUE NO NO NO NO always use this anti-join when possible always_semi_join standard TRUE NO NO NO NO always use this semi-join when possible aq_tm_processes 0 TRUE NO IMMEDIATE NO NO number of AQ Time Managers to start audit_trail NONE TRUE NO NO NO NO enable system auditing background_dump_dest D:\Oracle\admin\ora8 FALSE NO IMMEDIATE NO NO 16\bdump Detached process dump directory backup_tape_io_slaves FALSE TRUE NO DEFERRED NO NO BACKUP Tape I/O slaves bitmap_merge_area_size 1048576 TRUE NO NO NO NO maximum memory allow for BITMAP MERGE blank_trimming FALSE TRUE NO NO NO NO blank trimming semantics parameter buffer_pool_keep TRUE NO NO NO NO Number of database blocks/latches in keep buffer pool buffer_pool_recycle TRUE NO NO NO NO Number of database blocks/latches in recycle buffer pool commit_point_strength 1 TRUE NO NO NO NO Bias this node has toward not preparing in a two-phase commit compatible 8.1.6 FALSE NO NO NO NO Database will be completely compatible with this software versio control_file_record_keep_ 7 TRUE NO IMMEDIATE NO NO time control file record keep time in days PL/SQL Objects in the Database The report in Listing 4.9 documents the names and types of PL/SQL objects in the database, sorted by schema and object name. It can serve as a good cover page for the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  18. DOCUMENTING THE DATABASE 167 report described in the next section, which extracts the syntax of the PL/SQL code in PA R T the database. I Listing 4.9: PL/SQL Code in the Database COLUMN object_name FORMAT a50 SET LINES 132 Oracle Essentials SET PAGES 66 TTITLE CENTER “Stored PL/SQL Object Listing” SELECT owner, object_name, object_type FROM dba_objects WHERE object_type IN (‘FUNCTI/ON’,’PROCEDURE’,’PACKAGE’, ‘PACKAGE BODY’,’TRIGGER’) ORDER BY owner, object_type, object_name; Stored PL/SQL Object Listing OWNER OBJECT_NAME OBJECT_TYPE ---------------------- ---------------------------------------- ----------- SCOTT GET_ALL_BYTES PROCEDURE SCOTT MY_PROCEDURE PROCEDURE SCOTT EMPLOYEE_TRIGGER TRIGGER PL/SQL Object Text In the RevealNet Knowledge Base for Oracle Administration, you’ll find a report on the text of all PL/SQL objects within the database, titled PL/SQL Source Report. With just a tiny bit of modification to the code, you could have it extract just a specific piece of PL/SQL, or just the PL/SQL for a specific user. Users’ Setups in the Database The report produced by the code in Listing 4.10 documents the users in the database, sorted by username. (The output has been slightly modified to fit on this page.) NOTE Don’t forget about the importance of keeping your SYSTEM tablespace unfrag- mented and unfilled. Controlling the default tablespace settings for users is particularly important in preventing this. The report shown in Listing 4.10 can be used to alert you about users with incorrect temporary tablespace or default tablespace settings. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  19. 168 CHAPTER 4 • THE DATA DICTIONARY Listing 4.10: Database User Report SET LINES 132 SET PAGES 66 SELECT a.username, a.default_tablespace, a.temporary_tablespace, NVL(b.num_objects,0) “Number of Objects” FROM dba_users a, (SELECT owner, COUNT(*) num_objects FROM dba_objects GROUP BY owner) b WHERE b.owner (+) = a.username ORDER BY 1; USERNAME DEFAULT TEMPORARY Number of Objects ---------- ---------- ---------- ----------------- DBSNMP SYSTEM SYSTEM 4 OUTLN SYSTEM SYSTEM 5 PERFSTAT PERFSTAT TEMP 59 REPO REPOSITORY TEMP 99 REPOSITORY REPOSITORY TEMP 0 SCOTT USERS TEMPTEMP 15 SYS SYSTEM TEMP 2160 SYSTEM TOOLS TEMP 59 List of Current Roles Listing 4.11 shows the SQL to produce a report that lists the current roles in your database, and the grants that those roles have. This report is specific to roles and excludes other types of grantees. Listing 4.11: Role Grants Report SET PAGES 66 SET LINES 80 BREAK ON role SKIP 1 TTITLE CENTER “Role Grants Report” SELECT a.role, b.privilege FROM dba_roles a, dba_tab_privs b WHERE a.role=b.grantee UNION SELECT a.role, b.privilege FROM dba_roles a, dba_sys_privs b WHERE a.role=b.grantee ORDER BY 1, 2; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  20. DOCUMENTING THE DATABASE 169 List of Current Grants PA R T The report produced from Listing 4.12 is a listing of all the grants that all users have, I sorted by grantee, object, and type; it also includes direct grants to objects and system grants. In addition, you can see whether the users have the ability to grant the role they own to other users. This report uses a join and unions between several data dic- Oracle Essentials tionary tables, including DBA_ROLE_PRIVS, DBA_TAB_PRIVS, and DBA_SYS_PRIVS. Listing 4.12: Current Grants to Users SET PAGES 66 SET LINES 132 BREAK ON grantee SKIP 1 SELECT a.grantee, ‘Object Grant’ AS grant_type, b.privilege, b.grantable AS “Grant Option” FROM dba_role_privs a, dba_tab_privs b WHERE a.grantee=b.grantee UNION SELECT a.grantee, ‘System Grant’ AS grant_type, b.privilege, b.admin_option AS “Grant Option” FROM dba_role_privs a, dba_sys_privs b WHERE a.grantee=b.grantee UNION SELECT a.grantee, ‘Role’ AS grant_type, a.granted_role, a.admin_option AS “Grant Option” FROM dba_role_privs a ORDER BY 1, 2 Current Index Key Structures Often, the DBA will want to produce a list of indexes and the columns they are built on. This is very handy when tuning SQL statements. Listing 4.13 is a SQL statement that will produce a report on indexes and their key structures. It presents the user- name, the table on which the index is built, the index type, and the key order of the columns. This report uses the DBA_IND_COLUMNS data dictionary view. Listing 4.13: Index Key Report SET LINES 132 COLUMN table_owner FORMAT a20 COLUMN index_name FORMAT a25 COLUMN column_name FORMAT a25 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
Đồng bộ tài khoản