wORACLE f[^x[X`[jOx


Chapter1
p.3
/ora7/sqlreport/README


/ora7/orainst/README.FIRST


/ora7/rdbms/doc/README.doc


/ora7/rdbms/doc/readmeunix.doc


p.5
set file/prot=o:rwed *.*;*


chown oracle *.*
chgrp dba *.*


p.6
search error .log/win=10 error


p.7
more error.log|grep error


p.8
/u01@(500MB)
/data@i1GBj
/indexes@(500MB)


p.11
/u01/prod/appl_catscan/catp1.dbf
/u01/prod/appl_nurse/nursep1.dbf
/u01/prod/app1_doctor/doctorp1.dbf
/u01/training/app1_catscan/catt1.dbf
/u01/training/app1_nurse/nurset1.dbf
/u01/test/app1_doctor/doctor1.dbf


p.12
/u01/oradbf/catp1.dbf
/u01/oradbf/nursep1.dbf
/u01/oradbf/doctorp1.dbf
/u01/oradbf/catt1.dbf
/u01/oradbf/nurset1.dbf
/u01/oradbf/doctor1.dbf


p.13
create database prod
datafile 'disk01:[oradbf]systemp.dbf' size 50m
maxdatafiles 255;


p.13-14
create database PROD
logfile group 1 ('dsk1:[oradbf]redo1a.dbf', 'dsk2:[oradbf]redo1b.dbf') size 10M,
logfile group 2 ('dsk1:[oradbf]redo2a.dbf', 'dsk2:[oradbf]redo2b.dbf') size 10M,
logfile group 3 ('dsk1:[oradbf]redo3a.dbf', 'dsk2:[oradbf]redo3b.dbf') size 10M
datafile 'disk01:[oradbf]systemp1.dbf' size 50M
maxdatafiles 255;


p.14
create tablespace rollback_segs
datafile '/u01/oradbf/roll.dbf'  size  100M
default storage (initial 25K  next 75K);


create tablespace users
datafile '/u01/oradbf/user.dbf' size 50M
default storage (initial 10k next 10k pctincrease 0);


p.16
/* ́Cif[^x[Xp̕\̈ XNvg쐬܂*/
spool script_name.lis
set echo on
create tablespace temp datafile '/u01/oradbf/temp01.dbf' size 50M
default storage (initial 25K next 75K pctincrease 50);
/*  */
create tablespace user datafile '/u02/oradbf/user01.dbf' size 100M
default storage (initial 10K next 10K pctincrease 0);
spool off



Chapter2
p.29-30
col "Percentage miss" format 990.00
col "Gets" form 999,999,990
col "Misses" form 999,999,990

select unique parameter "Cache entry",
       gets "Gets",             /*Read from memory */
       getmisses "Misses",      /*Read from disk */
       getmisses/(gets+getmisses)*100 "Percentage miss"
  from v$rowcache
 where gets+getmisses <> 0;

Parameter                               Gets       Misses     Miss rate %
-------------------------------------------------------------------------
dc_columns                        23,531,944      533,293            2.22
dc_constraint_defs                       121           63           34.24
dc_constraint_defs                   164,065       23,114           12.35
dc_constraints                         1,939          792           29.00
dc_database_links                     60,395          493            0.81
dc_files                              29,809           22            0.07
dc_free_extents                    1,640,343       82,436            4.79
dc_indexes                         1,428,118       18,191            1.26
dc_object_ids                            853          378           30.71
dc_objects                         3,465,615      304,367            8.07
dc_rollback_segments                 127,899            4            0.00
dc_segments                          631,038       30,399            4.60
dc_sequence_grants                    10,753        2,921           21.36
dc_sequences                         283,069          763            0.27
dc_synonyms                          919,519       10,257            1.10
dc_table_grants                   11,196,614      421,759            3.63
dc_tables                         13,037,675       24,177            0.19
dc_tablespace_quotas                   2,655           45            1.67
dc_tablespaces                        42,544           60            0.14
dc_tablespaces                       158,151          536            0.34
dc_used_extents                       84,900       42,729           33.48
dc_user_grants                       870,690        8,181            0.93
dc_usernames                       1,610,250        9,110            0.56
dc_users                            1,703,638        9,070           0.53


p.31
select 250*trunc(indx/250)+1||
      ' to '||250*(trunc(indx/250)+1) "Interval",
      sum(count) "Buffer Cache Hits"
from sys.x$kcbrbh
group by trunc(indx/250);


p.32
select 25*trunc(undx/25)+1||
      ' to '||25*(trunc(indx/25)+1) "Interval",
      sum(count) "Buffer Cache Hits"
from sys.x$kcbcbh
group by trunc(indx/25);


p.35
LSNRCTL for HPUX: Version 2.0.15.0.0 - Production on 08-AUG-95 12:37:24
Copyright (c) Oracle Corporation 1993.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=dev))
Services Summary...
tst                   has 3 service handlers
    DEDICATED SERVER established:501 refused:0
    DISPATCHER established:0 refused:0 current:0 max:56 state:ready
      D000 (machine: devel, pid: 23141)
      (ADDRESS=(PROTOCOL=tcp)(DEV=5)(HOST=130.1.0.2)(PORT=3329))
    DISPATCHER established:0 refused:0 current:0 max:56 state:ready
      D001 (machine: devel, pid: 23142)
      (ADDRESS=(PROTOCOL=ipc)(DEV=5)(KEY=#23142.1))
rel                 has 1 service handlers
    DEDICATED SERVER established:1074 refused:0
  usr                 has 1 service handlers
    DEDICATED SERVER established:439 refused:0
  devel               has 1 service handlers
    DEDICATED SERVER established:2552 refused:0
The command completed successfully


Chapter3
p.41
select  fname,  lname  from  people  where  ssn = '123-45-6789';


p.42
select  fname, lname, address  from people, address where ssn = '123-45-0269' 
and people.ssn = address.ssn and address_type = 'home'


p.45
rollback_segments = (rbs01, rbs02, rbs03, rbs04, rbs05, rbs06, rbs07, rbs08)


rollback_segments = (rbs01, rbs05, rbs02, rbs06 ...)


p.52
greatest(4, ceil(ROW_COUNT /
  ((round(((1958 - (initrans * 23)) *
  ((100-PCT_FREE)/100)) / ADJ_ROW_SIZE)))) * 2)
  
  
greatest(4, ( 1.01 ) * ((ROW_COUNT /
(( floor(((2048 - 113 - (initrans * 23)) * (1-(percent_free/100.))) /
((10+uniqueness)+number_col_index+(total_col_length))))))*2))


p.55
analyze table (owner.table_name) compute statistics;
Select empty_blocks
from sys.dba_tables
where owner||'.'||table_name = (owner.table_name);


select a.blocks "Total Blocks Allocated",
       ((a.blocks - b.empty_blocks)/a.blocks)*100. "%Blocks Used",
       (b.empty_blocks/a.blocks)*100. "%Blocks Empty"
from sys.dba_segments a, sys.dba_tables b
where a.owner||'.'||a.segment_name = b.owner||'.'||b.table_name and
      b.owner||'.'||b.table_name = (owner.table_name)
      
      
      
Chapter4
p.64
ps -ef | grep oracleprd | cut -c1-70


p.73
               faults       cpu
sr     in     sy    cs  us sy id
 0    109    546    80  22  8 70
 0     98    553    77  18  8 74
 0    122    547    73  43  8 49
 0    111    548    83  22  5 73
 0    119    546    70  31  9 60
 0    108    512    66  28 18 54
 0    117    532    69  34 23 44
 0    121    538    83  22 15 74
 0    109    530    82  32 18 50
 0     98    546    82   8  0 92
 0    122    527    70  54 11 35
 0    111    541    73  22  5 73
 0    119    508    80  19  3 79
 0     96    554    75  22 15 74
 0    108    509    74  41 12 47
 0    117    531    59  24 23 54
 0    121    534    80  22 15 74
 0    109    523    72  29 14 57
 0    111    509    70  31 12 57
 0     99    542    69  20  8 72
 
 
p.76
alter system enable restricted session;
 
 
alter system disable restricted session;
 
 
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
 
 
fitzpaj  5002    1  0 09:09:22 ?     212:51 oracleprd P4096,8,1
fitzpaj  4223 4222  0 14:01:51 ttyra   0:00 -ksh
fitzpaj  4267 4223  0 14:01:58 ttyra   0:00 iap30x -q -c wpbios:vt220 /usr/
fitzpaj  4268 4267  0 14:01:58 ?       0:00 oracleprd P:4096,8,1


p.77
ALTER SYSTEM KILL SESSION '41,20898';


p.78
select sid, serial#
from v$session
where username = 'OPS$FITZPAJ';


           SID           SERIAL#
  ------------    --------------
            41             20898
            44             11000


SQL>  sho user
user is "SYSTEM"
SQL>  alter system kill session '41,20898';
System altered
SQL>


p.80
CREATE PROFILE FINUSERS LIMIT
    CPU_PER_SESSION      20
    SESSION_PER_USER      1
    IDLE_TIME          30;


p.81
CREATE PROFILE HUMANRES LIMIT
    CPU_PER_SESSION       20
    IDLE_TIME          8;


Chapter5
p.87
SQL> select a.sid,b.name,a.value from v$sesstat a, v$statname b
  2 where a.statistic# in (15,16)
  3 and a.statistic# = b.statistic#
  4 order by 1,3;
       SID NAME                                VALUE
---------- ------------------------------ ----------
         1 session memory                       9116
         1 max session memory                   9116
         2 session memory                       8388
         2 max session memory                   8388
         3 session memory                       8772
         3 max session memory                   8772
         4 session memory                       8460
         4 max session memory                   8460
         5 session memory                      15396
         5 max session memory                  15396
         6 session memory                       9284
         6 max session memory                   9284
         7 session memory                       9284
         7 max session memory                   9284
         8 session memory                       9284
         8 max session memory                   9284
         9 session memory                     185676
         9 max session memory                 202660
        20 session memory                      26212
        20 max session memory                  26212
        38 session memory                      17372
        38 max session memory                  25556


p.94
ROLLBACK_SEGMENTS = (RS1, RS2, RBS_HUGE)


SELECT  NAME, ACCOUNT, BALANCE
FROM  ORCL
WHERE  NAME  = 'MONEY GRABBERS'
AND  ACCOUNT = '5002300';



p.95
UPDATE  ORCL
SET  BALANCE = BALANCE - 200
WHERE  NAME  =  'MONEY GRABBERS'
AND  ACCOUNT  =  '5002300';


COMMIT-WORK;


p.101
sqldba @utlbstat


sqldba @utlestat


p.102
LIBRARY      GETS       GETHITRATI PINS       PINHITRATI RELOADS    INVALIDATI
------------ ---------- ---------- ---------- ---------- ---------- ----------
BODY                  0          1          0          1          0          0
CLUSTER               0          1          0          1          0          0
INDEX                 0          1          0          1          0          0
OBJECT                0          1          0          1          0          0
PIPE                  0          1          0          1          0          0
SQL AREA          49538        .89     177179       .924       1628          4
TABLE/PROCED      16204        .85      49261       .926       1443          0
TRIGGER               0          1          0          1          0          0
8 rows selected.


p.103
LOGICAL READS = CONSISTENT GETS + DB BLOCK GETS
HIT RATIO = ( LOGICAL READS - PHYSICAL READS ) / LOGICAL READS


LOGICAL READS = CONSISTENT GETS + DB BLOCK GETS
(14,237,750)      =   (13,477,423 + 760,327)
HIT RATIO       = ( LOGICAL READS - PHYSICAL READS ) / LOGICAL READS
(90.903)          = (14,237,750 - 1,374,655) / 14,237,750


p.106
BUFFER BUSY WAITS RATIO   = BUFFER BUSY WAITS / LOGICAL READS
LOGICAL READS = CONSISTENT GETS + DB BLOCK GETS


LOGICAL READS = CONSISTENT GETS + DB BLOCK GETS
(14,237,750)      =   (13,477,423 + 760,327)
BUFFER BUSY WAITS RATIO   = BUFFER BUSY WAITS / LOGICAL READS
( .0000002)                     =  ( 3 / 14,237,750 )


p.107
SELECT class, SUM(COUNT) 'Total Waits'
from sys.v$waitstat
where class in ('undo header', 'undo block', 'data block')
group by class;


Class                       Total Waits
-------------------     ---------------
Data block                           10
Undo segment header               1,000
Undo block                        1,200


p.109
NON-INDEX LOOKUPS RATIO = Table Scans (Long Tables) / Table Scans
(Long Tables) + Table Scans(Short Tables)


Table Scans (Long Tables)              = 10,588
Table Scans(Short Tables)              = 15,788
NON-INDEX LOOKUPS RATIO    .401 = 10,588 / (10,588 + 15.788)


p.110
Average Write Queue Length
--------------------------------------
.41711170449488206497552292
1 row selected.


p.114
UNDO     TRANS     TRANS             UNDO       SEGMENT  
SEGMENT  TBL_GETS  TBL_WAITS BYTES_WRITTEN   SIZE_BYTES   XACTS  SRINKS  WRAPS
-------- --------- --------- ------------- ------------ ------- ------- ------
       0     639           0             0       356352       0       0      0    
       2     5190          0       4474650      9203712       0       0      1
       3     5040          0       4254436      6234112      -1       0      1
       4     7727          0       9434609      8384512       0       0     34
       5     4193          0       2633827      6541312       0       0     16
       6     4213          0       2613917      5517312       0       0      0 
       7     8113          0       9440878     10432512       1       0     53
7 rows selected.


Rollback wait ratio = TRANS_TBL_WAITS / TRANS_TBL_GETS * 100


p.115
NAME                                VALUE
--------------------------------    --------------------------------------
audit_trail                         NONE
control_files                       user$disk2:[oradata.prodc]ora_control1_
db_block_buffers                    3000
db_block_size                       2048
db_file_multiblock_read_count       20
distributed_transactions            16
dml_locks                           400
enqueue_resources                   420
gc_db_locks                         1000
gc_rollback_locks                   10
gc_rollback_segments                10
c_save_rollback_locks               10
gc_tablespaces                      10
global_names                        TRUE
ifile                               ora_system:initps.ora
log_buffer                          81920
log_checkpoint_interval             12000
log_simultaneous_copies             3
max_dump_file_size                  10240
max_enabled_roles                   22
mts_servers                         0
open_cursors                        255
optimizer_mode                      CHOOSE
processes                           50
rollback_segments                   roll_priv1, roll_priv2, roll_priv3
sequence_cache_entries              50
sequence_cache_hash_bucket          23
sessions                            60
shared_pool_size                    4000000
sort_area_retained_size             65536
temporary_table_locks               60
transactions                        66
transactions_per_rollback_segment   18
33 rows selected.


p.116
NAME            GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS COUNT    CUR_USAG
--------------- -------- -------- -------- -------- -------- -------- --------
dc_tablespaces      1025        0        0        0        0       21        7
dc_free_extents    19784     1419     1477        0     4098       74       27
dc_segments         7778      621        0        0     1004      290      286
dc_rollback_seg    11502        0        0        0        0       11       10
dc_used_extents     1578      818        0        0     1578       70       54
dc_tablespace_q       40        2        0        0        7        2        1
dc_users           13280       71        0        0        0       23       21
dc_user_grants     11350       67        0        0        0       49       18
dc_objects         18989     2068        0        0       19      598      597
dc_tables          74490      686        0        0       11      410      409
dc_columns        221240     7405    10173     1586       39     3948     3946
dc_table_grants    55388     5835        0        0        0     2482     2478
dc_indexes         11142      302     8630      548        0      232      227
dc_constraint_d      678      184      494       76       19       46       45
dc_constraint_d        0        0      235       30        0        1        0
dc_synonyms         4595      997        0        0        0      140      139
dc_usernames       14775       70        0        0        0       35       33
dc_object_ids          5        4        0        0        0        3        2
dc_constraints        38       19        0        0       38        4        3
dc_sequences         989       13        0        0       44       11       10
dc_sequence_gra      206       52        0        0        0       13       12
dc_tablespaces       818        1        0        0      818       10        4
22 rows selected.


p.117
STATS_GATHER_TIMES
--------------------
19-jul-95 07:01:12
21-jul-95 07:44:55
2 rows selected.


p.119
analyze table people list chained rows;


p.122
UPDATE MY_TABLE
SET COL1 = 'NEW VALUE';


p.124
Select * from v$log;
Group#  Thread#  Sequence#     Bytes  Members  ARC    Status  First_change
     1        1        273  10485760        2  YES  INACTIVE         50601
     2        1        274  10485760        2   No   Current         50601
     3        1        275  10485760        2  YES  INACTIVE         50601


Select * from V$logfile;
Group   Status    Member
1                 /u01/redo/redo_log1.log
1                 /u02/redo/redo_mirror1.log
2                 /u01/redo/redo_log2.log
2                 /u02/redo/redo_mirror2.log



Chapter6
p.128
db_files = 60


p.130
ORA-01547: failed to allocate extent of size 1024 in tablespace 'PRD_IDX'


p.131
01547, 00000, "failed to allocate extent of size %s in tablespace '%s'"
// *Cause: Tablespace indicated is out of space
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//         files to the tablespace indicated or create the object in other
//         tablespace if this happens during a CREATE statement


alter tablespace prd_idx
add datafile '/usr/ora/prd/indexes_prd_2.dbf' size 10M;
alter tablespace prd_idx

ERROR at line 1:
ORA- 01118: cannot add any more database files: limit of 40 exceeded


db_files = 40


db_files = 60


p.132-134
# Step 1
#
# Start the database with the nomount option.  This is the only
# mode the following create statement will run in since the database
# does not yet exist
connect internal
startup nomount pfile=?/dbs/initdevel.ora
# Step 2
#
# Create the database.  Notice how the "controlfile reuse" line
# tells Oracle to reuse the current control files if it finds them
# where they are specified in the initialization parameter file.
# Even though we ask for 2 redo log files, Orace7 will actually create
# 2 single-membered redo log groups.  The "maxdatafiles" and 
# "maxlogmembers" parameters are examples of database parameters
# that can only be set in when the database is created (or a new
# control file created as we discuss in Chapter 8.
# The default for this statement is "noarchivelog",
# we put it here just as a reminder.  You do not want the database
# to archive redo logs when you do the full database import.
create database devel
       controlfile reuse
       datafile '?/dbs/dbs1devel.dbf'          size 20M
       logfile '/sys1/ora/log1devel.dbf'       size 2M,
               '/sys2/ora/log2dvel.dbf'        size 2M
       maxdatafiles 40,
       maxlogmembers 6,
       noarchivelog;
# Step 3
#
# Open the database and create the first non-system rollback
# segment.  You need at least one non-system rollback segment before
# you can create a tablespace.  We always make this rollback segment
# small - based on these storage parameters, it will never grow over
# 500K (512,000 bytes).
alter database open;
create rollback segment temp
      tablespace system
      storage (initial 100K next 100K minextents 1 maxextents 5);
alter rollback segment temp online;
# Step 5
# 
# Set up the data dictionary which ends up in the SYS account.  Then
# run the script "catproc.sql" that installs the procedural option.
# Then set up the views required to run the export and import utilities
# that should end up as part of your system backups.
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catexp.sql
@?/rdbms/admin/catldr.sql
# Step 6
#
# As the SYSTEM user, set up private synonyms pointing at all the
# data dictionary views prefixed with the characters "dba_".  Then
# run the script to build the PRODUCT_USER_PROFILE table.  This 
# table can be used to restrict certain SQL or SQL*Plus commands
# for individual or classes of Oracle users.  Without at least
# building this table, any user other than SYSTEM that enters
# SQL*Plus is told "Warning! Product user profile information 
# not loaded".
connect system/manager
@?/rdbms/admin/catdbsyn
@?/sqlplus/admin/pupbld


p.134
create table my_table  (seq number,
                           my_key varchar2(10),
                           desc varchar2(40))
                           freelist 12;


p.135
SQL> select class,count from v$waitstat where class = 'free list'
CLASS                           COUNT
------------------------------- -----------
free list                       59 


SQL> select name,value from v$sysstat 
   2  where name in ('db_block_gets','consistent gets');
CLASS                         COUNT
----------------------- -----------
db block gets                 12850
consistent gets               10119    


FREELIST wait events = (free list count) / (db block gets +consistent gets) * 100
   = 59 / (12850 + 10119) * 100
   = 59 / 22969 = 0.26


p.136
create table my_table (seq number,
                        my_key varchar2(10),
                        desc varchar2(40))
          storage (initial 500K next 100K pctincrease 0 maxextents 999)
          pctfree 15 pctused 85 freelist 12;


p.136-137
SQLDBA> connect internal

Connected.

SQLDBA> show parameters db_block_size

db_block_size             integer    4096


p.137
alter table my_table storage (maxextents 240)


rem * What is the maximum number of extents that MY_TABLE can occupy?
SQL> select maxextents from sys.dba_tables
   2  where table_name = 'MY_TABLE' and owner = 'ITSME';
MAXEXTENTS
----------
      99

rem * How many extents does it have now?
SQL > select count(*) from sys.dba_extents
   2  where segment_name = 'MY_TABLE' and
   3  segment_owner = 'ITSME';
COUNT(*)
--------
     98          


p.139
create table my_table (seq number,
                     my_key varchar2(10),
                     desc varchar2(40))
      storage (initial 100k next 100k pctincrease 0);


p.141
alter table my_table storage (pctincrease 0);


next extent size  previous extent size * PCTINCREASE ^ 100


alter table my_table allocate extent size 1M;


p.142
SQL> analyze product_1 validate structure;

Index analyzed.

SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len
  2  from index_stats where name = 'PRODUCT_1';

   LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ----------- ----------- ---------------
     53075     1576044        5789          164308


p.143
create index my_index on my_table (seq, my_key)
      storage (initrans 1  maxtrans 4);


p.144
today := sysdate;
logged_in := user;


p.145
SQL> select count(*) from dual;
COUNT(*)
----------
         1
SQL> select * from dual;
DUMMY
-----------
X


SQL> delete from dual where rownum = 1;
1 row deleted.


p.146
00257, 00000, "archiver error. Connect internal only, until freed."
// *Cause:  The archiver process recieved an error while trying to archive
//        a log.  Unless the problem is resolved soon, the database will
//        stop executing transactions.
// *Action:  By far the most likely cause of the error is the archive
//        destination device is out of space.  Check archiver trace file
//        for detail description of the problem.


p.148
alter system enable restricted session;


alter system disable restricted session;


Chapter7
p.155
select pin,pos_id,last_name,first_name from people where pin = :blk1.pin;
select pin,pos_id,last_name,first_name from people where pin = :blk1.pin;
select pos_id,sal_cap from sal_limit where over_time = :blk1.ot_ind; 
select pos_id,sal_cap from sal_limit where over_time = :blk1.ov_ind;  


p.161
create table plant_detail (
          plant_id     number(2),
          city_id      number(2)  check (city_id < 90),
          location     varchar2(20));


p.163
select * from sal_limit where sal_cap between 100000 and 200000;


select * from sal_limit where sal_cap >= 100000 and sal_cap <= 200000;


select sdesc from work_city where city_id in (1,15,67);


select sdesc from work_city where city_id = 1 or city_id = 15 or city_id = 67;


/*+ first_rows */


--+ first_rows


p.164
select  /* +RULE  */  ...


select  /* qgwȂ  */  ...


select  /* +ALL_ROWS  */  ...


alter session set optimizer_goal = { ύX[h };


p.165
alter session set optimizer_goal = ALL_ROWS;
select  /*+ RULE : [x[X */  ...
select  /*  qg͋LqȂ  */  ...
select  /*+ FIRST_ROWS */  ...
alter session set optimizer_goal = RULE;
select  /* qgw肵ȂCȍ~̓[x[X  */  ...


p.166
EXPLAIN PLAN SET STATEMENT_ID


p.166-167
delete plan_table where statement_id = 'ZZ';
explain plan set statement_id = 'ZZ' for
 select t.tmdt_pin pin,b.last_name surname,b.first_name given,
   t.tmdt_product prod,
   sum(nvl(t.tmdt_orig_sat,0)+nvl(t.tmdt_orig_sun,0) +
   nvl(t.tmdt_orig_mon,0)+nvl(t.tmdt_orig_tue,0) +
   nvl(t.tmdt_orig_wed,0)+
   nvl(t.tmdt_orig_thu,0)+nvl(t.tmdt_orig_fri,0) +
   nvl(t.tmdt_transfer_sat,0)+nvl(t.tmdt_transfer_sun,0) +
   nvl(t.tmdt_transfer_mon,0)+nvl(t.tmdt_transfer_tue,0) +
   nvl(t.tmdt_transfer_wed,0)+
   nvl(t.tmdt_transfer_thu,0)+nvl(t.tmdt_transfer_fri,0) +
   nvl(t.tmdt_adjustment_sat,0)+nvl(t.tmdt_adjustment_sun,0) +
   nvl(t.tmdt_adjustment_mon,0)+nvl(t.tmdt_adjustment_tue,0) +
   nvl(t.tmdt_adjustment_wed,0)+
   nvl(t.tmdt_adjustment_thu,0)+nvl(t.tmdt_adjustment_fri,0)) acc
 from  time_detail t,person b
 where tmdt_pin = b.pin
 and tmdt_pin between 1720 and 1730
 and tmdt_weekending between '940401' and '950331'
 and tmdt_product IN ('TC210','TC680')
group by t.tmdt_pin,b.last_name,b.first_name,t.tmdt_product;


p.167
set echo off term off feed off ver off
spool xpl
select decode(id,0,'',
   lpad(' ',2*(level-1))||level||'.'||position)||' '||
   operation||' '||options||' '||object_name||' '||
   object_type||' '||
   decode(id,0,'Cost = '||position) Query_plan
  from plan_table
connect by prior id = parent_id
and statement_id = upper('&1')
start with id = 0 and statement_id = upper('&1');
spool off
set term on
prompt
prompt Output from EXPLAIN PLAN is in file called "xpl.lst"...
prompt


p.168
QUERY_PLAN
--------------------------------------------------------------------
SELECT STATEMENT    Cost = 902
  2.0 SORT GROUP BY
    3.1 NESTED LOOPS
      4.1 VIEW  TIME_DETAIL
        5.1 SORT GROUP BY
          6.1 NESTED LOOPS
            7.1 TABLE ACCESS BY ROWID TRS_TIMESHEET
              8.1 INDEX RANGE SCAN TRS_TIMESHEET_IDX1 UNIQUE
            7.2 TABLE ACCESS BY ROWID TRS_TS_STATUS
              8.1 INDEX RANGE SCAN TRS_TS_STATUS_IDX1 UNIQUE
      4.2 TABLE ACCESS BY ROWID PEOPLE_MASTER
      5.2 INDEX UNIQUE SCAN PEOPLE_MASTER_1 UNIQUE
                                 

p.171
alter session set sql_trace = true;


alter session set sql_trace = false;


timed_statistics = true


sql_trace = true


p.171-172
****************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
****************************************************************************

select userid
from assign,person where assign.pin = person.pin and assign.home_base_ind =
 'Y'

call        count       cpu    elapsed     disk    query current        rows
-------  -------  -------  --------- ------- ------- -------  ----------
Parse           1      0.01       0.01        0        4       0           0
Execute         1      0.00       0.00        0        0       0           0
Fetch         141      0.26       1.09      207    12812       3        2113

Misses in library cache during parse: 0
Parsing user id: 678  (JPROUDF)
Rows     Execution Plan
-------  ---------------------------------------------------
      0   SELECT STATEMENT
   2113    NESTED LOOPS
   2706      TABLE ACCESS (FULL) OF 'PEOPLE_MASTER'
   2255      TABLE ACCESS (BY ROWID) OF 'ASSIGN'
   4961        INDEX (RANGE SCAN) OF 'ASSIGN_1' (NON-UNIQUE)


p.172
tkprof mg[X t@Cn output=mo̓t@Cn
explain=m[U[^pX[hn sort=m\[g IvVn


p.172-173
prscnt	͉
prscpu	͂ɂCPU
prsela	͂ɂELAPS
prsdsk	͎̃fBXNǍݐ
prsqry	͎̃obt@
prscu	͎̃obt@Ǎݐ
prsmis	͎̃Cu LbVł̃~X
execnt	s
execpu	sŎgpꂽCPU
exeela	sŎgpꂽELAPS
exedsk	s̃fBXNǍݐ
exeqry	s̃obt@
execu	s̃obt@Ǎݐ
exerow	s̏s
exemis	s̃Cu LbVł̃~X
fchcnt	FETCH
fchcpu	FETCHɂCPU
fchela	FETCHɂELAPS
fchdsk	FETCH̃fBXNǍݐ
fchqry	FETCH̃obt@
fchcu	FETCH̃obt@Ǎݐ
fchrow	FETCH̏s
userid	J[\͂Ă郆[U[ID


p.174
select substr(location,1,10) from plant_detail;


p.175
select colc, substr(cold,1,30) from taba where cola >= 'ABC';


select colc substr(cold,1,30) from taba where colb >= 'ABC';


p.176-177
TABLE_NAME      COLUMN_NAME     STAT_NAME                         STAT_VALUE
--------------- --------------- ------------------------------ -------------
OCOMP           ORG_COMP_TYPE   Rows - Null                             0.00
OCOMP           ORG_COMP_TYPE   Rows - Total                          205.00
OCOMP           ORG_COMP_TYPE   Rows per key - avg                     51.25
OCOMP           ORG_COMP_TYPE   Rows per key - dev                     77.94
OCOMP           ORG_COMP_TYPE   Rows per key - max                    166.00
OCOMP           ORG_COMP_TYPE   Rows per key - min                      1.00
OCOMP           ORG_COMP_TYPE   Total Distinct Keys                     4.00
OCOMP           ORG_COMP_TYPE   db_gets_per_key_hit                    70.07
OCOMP           ORG_COMP_TYPE   db_gets_per_key_miss                  140.14

9 rows selected.

TABLE_NAME         COLUMN_NAME    BADNESS KEYS_COUNT ROW_PERCENT KEY_PERCENT
--------------- -------------- ---------- ---------- ----------- -----------
OCOMP           ORG_COMP_TYPE         166          1       80.98       25.00
OCOMP           ORG_COMP_TYPE          34          1       16.59       25.00
OCOMP           ORG_COMP_TYPE           4          1        1.95       25.00
OCOMP           ORG_COMP_TYPE           1          1        0.49       25.00

4 rows selected.


p.178
TAB_NAME                        COL_NAME
------------------------------- -------------------------------------------
STATUS_HISTORY                  PIN

1 row selected.

TABLE_NAME      COLUMN_NAME     STAT_NAME                         STAT_VALUE
--------------- --------------- ------------------------------ -------------
STATUS_HISTORY  PIN             Rows - Null                             0.00
STATUS_HISTORY  PIN             Rows - Total                        2,288.00
STATUS_HISTORY  PIN             Rows per key - avg                      1.87
STATUS_HISTORY  PIN             Rows per key - dev                      1.21
STATUS_HISTORY  PIN             Rows per key - max                      9.00
STATUS_HISTORY  PIN             Rows per key - min                      1.00
STATUS_HISTORY  PIN             Total Distinct Keys                 1,222.00
STATUS_HISTORY  PIN             db_gets_per_key_hit                     1.59
STATUS_HISTORY  PIN             db_gets_per_key_miss                    2.65

9 rows selected.

TABLE_NAME      COLUMN_NAME       BADNESS  KEYS_COUNT ROW_PERCENT KEY_PERCENT
--------------- --------------- ---------- ---------- ----------- ----------
STATUS_HISTORY  PIN                      9          2        0.79       0.16
STATUS_HISTORY  PIN                      8          1        0.35       0.08
STATUS_HISTORY  PIN                      7          9        2.75       0.74
STATUS_HISTORY  PIN                      6         10        2.62       0.82
STATUS_HISTORY  PIN                      5         30        6.56       2.46
STATUS_HISTORY  PIN                      4         68       11.89       5.57
STATUS_HISTORY  PIN                      3        125       16.39      10.23
STATUS_HISTORY  PIN                      2        365       31.91      29.87
STATUS_HISTORY  PIN                      1        612       26.75      50.08

9 rows selected.



Chapter8
p.188
alter database archivelog;


alter database open;


/data/oracle_prd/arc_logs/arch


_%s.prd


/data/oracle_prd/arc_logs/arch_1287.prd     iOԍ1287̏ꍇj


alter tablespace personnel begin backup;


p.189
alter tablespace personnel end backup;


p.191
show parameters dump_dest


p.193
select tablespace_name,sum(bytes) from sys.dba_free_space
    group by tablespace_name;
select tablespace_name,max(bytes) from sys,dba_free_space
    group by tablespace_name;


TABLESPACE_NAME                SUM(BYTES)
------------------------------ ----------
AUDIT_APPS                        2678784
COMMON                           18423808
DESIDERATA                       10932224
FINANCIAL                        40550400
ALL_INDEXES                       4861952

TABLESPACE_NAME                MAX(BYTES)
------------------------------ ----------
AUDIT_APPS                        1331200
COMMON                           13897728
DESIDERATA                        5734400
FINANCIAL                        40550400
ALL_INDEXES                       4505600


p.194-195
rem *  Create rows in FSPACE for today

insert into fspace
select a.tablespace_name,
       sum(a.bytes),             /*  Allocated from DBA_DATA_FILES  */
       round(sum(b.bytes)),      /*  Free bytes from DBA_FREE_SPACE */
       '','',sysdate
  from sys.dba_data_files a,sys.dba_free_space b
 where a.tablespace_name = b.tablespace_name
 group by a.tablespace_name,'','',sysdate;

rem *  Yesterday's free space is in the rows from yesterday FREE_TODAY
rem *  column.  The FREE_TODAY column values from yesterday are moved into the
rem *  FREE_YESTERDAY columns for today's rows.

update fspace a
set free_yesterday =
    (select free_today
       from fspace b
      where a.tablespace_name = b.tablespace_name
        and to_char(b.system_date) = to_char(sysdate - 1))
where to_char(system_date) = to_char(sysdate);

rem *  The PERCENT_CHANGED is set to represent the following:
rem *  % change = free_today - free_yesterday / free_yesterday expressed
rem *  as a percentage.  The calculation has to use a DECODE in case the amount
rem *  of free space has not changed.  This avoids dividing by 0.

update fspace
   set percent_changed = round(decode(free_today-free_yesterday,
                         0,0,  /* If no change, set PERCENT_CHANGED to zero  */
                         100*(free_today-free_yesterday)/
                         (free_yesterday)),2)
 where to_char(system_date) = to_char(sysdate);

rem *  Print changed free space report for today.

col tablespace_name heading 'Tablespace'
col allocated heading 'Allocated' 999,999,990
col free_today heading 'Free today' form 999,999,990
col free_yesterday heading 'Yesterday' form 999,999,990
col percent_changed heading 'Pct Ch' form 90.00

select tablespace_name, allocated, free_today, free_yesterday, percent_changed
  from fspace
 where to_char(system_date) = to_char(sysdate);
 
 
p.196
Tablespace              Allocated        Today    Yesterday  Pct Ch
-------------------- ------------ ------------ ------------ -------
AUDIT_APPS             73,400,320    2,678,784    2,678,784    0.00
COMMON                209,715,200   18,423,808   18,423,808    0.00
DESIDERATA            209,715,200   10,932,224    7,245,824   50.88
FINANCIAL             104,857,600   40,550,400   40,796,160   -6.25
ALL_INDEXES            15,728,640    4,861,952    4,861,952    0.00


select username from sys.dba_users where temporary_tablespace = 'SYSTEM';


p.198
truncate table plant_detail;


truncate cluster plant_cluster;


p.199
set transaction use rollback segment


p.200
procedure get_items is
cursor my_cursor is
  select count(*)
    from plant_detail
   where city_id = 12;
  temp_buffer number;
begin
  open my_cursor;
  fetch my_cursor into temp_buffer;
  close my_cursor;
end;


p.200-201
procedure get_items is
the_cnt number;
begin
  select count(*)
    into the_cnt
    from plant_detail
   where city_id = 12;
end;


p.204
alter database backup controlfile to 'location' reuse;


alter database backup controlfile to trace;



Chapter9
p.209
SQL> select owner "Owner", segment_name "Segment Name",
  2     segment_type "Type", tablespace_name "Tablespace",
  3     extents "#Ext", max_extents "Max"
  4     from sys.dba_segments
  5     where extents > 5
  6     and owner not in ('SYS','SYSTEM')
  7     order by owner,segment_name;
Owner              Segment Name        Type      Tablespace         #Ext   Max
-----------------  ------------------  --------  -----------------  ----  ----
USER1              ACC_TABLE           TABLE     TBSP_TESTONE          7    99
USER1              TBL_SECONDS         TABLE     TBSP_TESTONE          7    99
USER1              ACTORS              TABLE     TBSP_TESTONE         16   120
USER1              XFERS               TABLE     TBSP_TESTONE          6   120
USER2              HISTORY_FILE        TABLE     TBSP_TESTTWO          8   120
USER2              HISTORY_INDEX       INDEX     TBSP_TESTTWO          9   120
USER2              TEMP2               INDEX     TBSP_TESTTWO          8   120
USER3              FORM                TABLE     TBSP_TEST3           13   120
USER3              MENU                TABLE     TBSP_TEST3           21   120
USER3              REPORT              TABLE     TBSP_TEST3            8   120


SQL> select segment_name, extents
  2  from sys.dba_segments
  3  where segment_name = 'MY_TABLE' and owner = 'USER1';
SEGMENT_NAME       EXTENTS
------------------ -------
MY_TABLE               101


p.210
SEGMENT_NAME       EXTENTS
------------------ -------
MY_TABLE                 1


imp userid=user1/password indexfile=my_table.sql
 
 
imp userid=user1/password ignore=y tables=my_tabledata     


p.211-212
ttitle center 'Report of Next Extent Within ' &1 ' of maxextents' skip -
       center 'Date: ' datevar skip -
       left '(T/I - Table or Index)' skip 2
column bsize new_value max_ext
column today new_value datevar format a1 noprint
select sysdate today, decode(value,2048,121,240) bsize 
/* You get 121 max extents if block   */
/* size is 2048 and 240 if it is 4096 */
  from v$parameter                                     
 where name = 'db_block_size';
select a.owner, table_name "object", 
       a.tablespace_name "tablespace",
       'T' "T/I", 
       a.max_extents max_extents,
       b.extents current_extent
 from sys.dba_tables a, sys.dba_segments b
where table_name = segment_name and
      (a.max_extents < extents + &1 or &max_ext < extents + &1)
union
select a.owner, index_name, a.tablespace_name,
       'I' indicator, 
       a.max_extents max_extents,
       b.extents current_extent
  from sys.dba_indexes a, sys.dba_segments b
 where index_name = segment_name and
       (a.max_extents < extents + &1 or &max_ext < extents + &1);


p.212
sqlplus @withinmax 10


                  Report of Next Extent Within 10 of maxextents                 
                                 DATE: 05-JAN-96                                
(T/I - TABLE OR INDEX)                                                          
                                                                                
OWNER    OBJECT              TABLESPACE         T/I MAX_EXTENTS CURRENT_EXTENT
-------- ------------------- ------------------ --- ----------- --------------
USER1    IND_BILLING         TBSP_IDX_ACTION    I            99             92
USER1    IND_SERVICE         TBSP_IDX_ACTION    I            20             12


SQL> select * from sys.dba_free_space 
  2 where tablespace_name = 'USERS7'
  3 order by block_id;

TABLESPACE_NAME     FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------------- ---------- ---------- ---------- ----------
USERS7                   16        817      49152         12
USERS7                   16        829     356352         87
USERS7                   16       5359    1024000        250
USERS7                   16       5609     409600        100
USERS7                   16       5709     565248        138
USERS7                   16       8463   13897728       3393

6 rows selected.


p.213
TABLESPACE_NAME     FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------------- ---------- ---------- ---------- ----------
USERS7                   16        817     405504         99
USERS7                   16       5359    1998848        488
USERS7                   16       8463   13897728       3393

3 rows selected.


p.214-215
SQL> select b.file_id "File #" 
  2         b.tablespace_name "Tablespace name", 
  3         b.bytes "# bytes",
  4         (b.bytes - sum(nvl(a.bytes,0))) "# used",
  5         sum(nvl(a.bytes,0)) "# free", 
  6         (sum(nvl(a.bytes,0))/(b.bytes))*100 "%free"
  7    from sys.dba_free_space a, sys.dba_data_files b 
  8   where a.file_id(+) = b.file_id
  9   group by b.tablespace_name, b.file_id, b.bytes 
  10  order by b.tablespace_name
File#  Tablespace Name                # Bytes       # Used       # Free  %Free
-----  -------------------------  -----------  -----------  -----------  -----
    1  SYSTEM                       104857600     26503168     78354432   74.7
    3  TBSP_INDEX001                 83886080     78610432      5275648    6.3
    9  TBSP_INDEX001                 10485760      6907904      3577856   34.1
    5  TBSP_PROD001                   3145728         2048      3143680   99.9
    6  TBSP_PROD002                   5242880      3381248      1861632   35.5
    7  TBSP_PROD003                  52428800     50563792      1835008    3.5
    8  TBSP_PROD004                   5242880      2021376      3221504   61.4
    2  TBSP_ROLLBACK                157286400     47310848    109975552   69.9
    4  TBSP_USER_TEMP                31457280         2048     31455232  100.0
   10  TBSP_WORK                     31457280     11300864     20156416   64.1
   
   
p.216
rem     Table sizing table.
create table table_sizing (
    table_pk         number primary key,
    table_name       varchar2(30),
    owner            varchar2(20),
    adj_row_size     number(4),
    row_count        number,
    tspace           varchar2(30),
    pct_free         number(3),
    initrans         number(2),
    free_lists       number(2));
rem     Index sizing table.
create table index_sizing (
    table_pk             number references (table_sizing),
    index_pk             number primary key,
    index_name           varchar2(30),
    uniqueness           number(1),
    number_col_index     number(2),
    total_col_length     number(3),
    percent_free         number(2),
    initrans             number(2),
    tspace               varchar2(30));
       

create sequence table_pk;
create sequence index_pk;


p.217
table_sizing.table_pk = index_sizing.table_pk


p.218-219
Trigger Name: key-commit                       Style: V3   Hide: No
       Text: DECLARE
         I_NAME CHAR(30);
         I_SIZE integer;
         ITOTAL INTEGER;
         BEGIN
         if :load_percent is null then
            message('Load Percent is required.');
            bell;
         else
            if round(((1958 - (:tables.initrans * 23)) *
                  ((100-:PCT_FREE)/100))/:adj_row_size) = 0 then
            :TSIZE_K := greatest(4, ceil((:ROW_COUNT * (:LOAD_PERCENT/100)) /
                        (((((1958 - (:tables.initrans * 23)) *
                        ((100-:PCT_FREE)/100)) /
                        :ADJ_ROW_SIZE)))) * 2);
          else
            :TSIZE_K := greatest(4, ceil((:ROW_COUNT * (:LOAD_PERCENT/100)) /
                        ((round(((1958 - (:tables.initrans * 23)) *
                        ((100-:PCT_FREE)/100)) /
                        :ADJ_ROW_SIZE)))) * 2);
          end if;
         ITOTAL := 0;
         GO_BLOCK('INDEXES');
         FIRST_RECORD;
         GO_BLOCK('SIZE_INDEX');
         FIRST_RECORD;
         LOOP
            GO_BLOCK('INDEXES');
            IF :INDEX_NAME IS NULL THEN
               EXIT;
            END IF;
            I_NAME := :INDEX_NAME;
            I_SIZE := greatest(4, ( 1.01 ) *
                      (( (:row_count * (:load_percent/100.)) /
                      (( floor(((2048 - 113 - (:indexes.initrans * 23)) *
                      (1-(:percent_free/100.))) / ((10+:uniqueness)+
                      :number_col_index+(:total_col_length))))))*2));
            ITOTAL := ITOTAL + I_SIZE;
            NEXT_RECORD;
            go_block('size_index');
            :sindex_name := I_name;
            :isize_k := i_size;
            NEXT_RECORD;
         END LOOP;
         GO_BLOCK('Indexes');
         FIRST_RECORD;
         GO_BLOCK('SIZE_INDEX');
         FIRST_RECORD;
         :ITOT_K := ITOTAL;
         end if;
         end;


p.219
if  :load_percent  is  null  ....


p.220
if  round(((...) = 0 ...


:tsize_k := ...


Itotal := 0 ...


Loop...


I_NAME := ... ITOTAL := ITOTAL + I_SIZE


NEXT_RECORD;  ...NEXT_RECORD;


END  LOOP;


:TOT_K  := ITOTAL;  ... end


p.221-222
rem      Table sizing report.
rem
set pagesize 60
set newpage 0
column initial format 999,999,999
column next format 99,999,999
break on report
compute sum of "INITIAL" on report
compute sum of "NEXT"  on report
column pct new_value percent noprint;
ttitle center -
    'Table Storage Calculations for Initial and Next Extents (in kilobytes)' -
    skip center 'Percent of Total ' percent skip 2
select &3 pct from dual;
spool &4
select owner,table_name ,
       decode (round(((1958 - (initrans * 23)) *
                     ((100-pct_free)/100))/adj_row_size),0,
         greatest(4, ceil((row_count*(&3/100)) /
         (((((1958 - (initrans * 23)) *
         ((100-pct_free)/100)) /
         adj_row_size)))) * 2),
         greatest(4, ceil((row_count*(&3/100)) /
         ((round(((1958 - (initrans * 23)) *
         ((100-pct_free)/100)) /
          adj_row_size)))) * 2)) "INITIAL",
 decode (round(((1958 - (initrans * 23)) *
                     ((100-pct_free)/100))/adj_row_size),0,
         greatest(4, (ceil((row_count*(&3/100)) /
         (((((1958 - (initrans * 23)) *
         ((100-pct_free)/100)) /
         adj_row_size)))) * 2) * .1),
         greatest(4, (ceil((row_count*(&3/100)) /
         ((round(((1958 - (initrans * 23)) *
         ((100-pct_free)/100)) /
          adj_row_size)))) * 2) * .1)) NEXT
  from table_sizing
 where table_name like upper('&1') 
   and owner like upper('&2')
 order by 1,2;
rem
rem      Index sizing report.
rem
set pagesize 60
set newpage 0
ttitle center 'Index Storage Calculations (in kilobytes)' -
       skip center 'Percent of Total ' percent skip2 2
column index_size format 999,999
column table_name format a20
column index_name format a20
break on report
compute sum of index_size on report
select owner, table_name, index_name,
       greatest(4, ( 1.01 ) * (( (row_count*(&3/100)) / (( floor(((2048 -113 -
               (a.initrans * 23)) *
               (1-(percent_free/100))) / 
                ((10+uniqueness)+number_col_index+(total_col_length))))))*2))
               index_size

  from index_sizing a, table_sizing b
 where index_name is not null 
   and a.table_pk = b.table_pk 
   and table_name like upper('&1') 
   and owner like upper('&2')
 order by owner, table_name,index_name;
spool off
exit;


p.222-223
sqlplus  /  @sizing  "%"  "USER1"  "100"  "sizing.rpt"


                          Percent of Total        100
                                                                                                                            
OWNER                TABLE_NAME                          INITIAL        NEXT
-------------------- ------------------------------ ------------ -----------
USER1                DATA_TABLE_1                          2,858         286                                                                                                                 
                                                    ------------ ----------- 
sum                                                        2,858         286 
                       Index Storage Calculations (in kilobytes)
                                  Percent of Total      100

OWNER                 TABLE_NAME            INDEX_NAME            INDEX_SIZE
--------------------- --------------------- --------------------- ---------- 
USER1                 DATA_TABLE_1          INDX1                       367
                                                                  ----------
sum                            
   
 
 
p.223
select username, id, promo_date, name
  from sys_users;


select cumulative_logons, screens_accessed, username
  from sys_activities;


p.224
Username   ID  Due promo   Name                 Logons    Screens
-----------------------------------------------------------------
ABBFLAB   213  12-DEC-95   Boris Abbflantro        289         29
BESDESN   198  11-MAR-97   Nancy Besdesmith        812        102
NADROJN    23  09-FEB-96   Norman Nadrojian         34       2345
DEFWAYF   721  11-MAR-95   Francis Defwayno        321       1189


Parent query
select username "Username", created "Created",
       substr(granted_role,1,15) "Roles",
       default_tablespace "Default TS", 
       temporary_tablespace "Temporary TS"
  from sys.dba_users, sys.dba_role_privs
 where username = grantee (+)
 order by username
Child query
select unique '#' "#", user_name
from menu_v_user


                                     USER LIST
PAGE: 1                                                        DATE: 24-AUG-97

Username        Created   Roles            Default TS       Temporary TS     #
--------------  --------  ---------------  ---------------  ---------------  -
ADAMS           03/18/94  CONNECT          TBSP_WORKDEV     TBSP_TEMPORARY
FORMS30         03/18/94  CONNECT          TBSP_WORKDEV     TBSP_TEMPORARY
FORMS30         03/18/94  RESOURCE         TBSP_WORKDEV     TBSP_TEMPORARY
OPS$USER1       03/18/94  CONNECT          SYSTEM           SYSTEM           #
OPS$USER1       03/18/94  DBA              SYSTEM           SYSTEM           #
OPS$USER1       03/18/94  RESOURCE         SYSTEM           SYSTEM           #
OPS$USER002     03/18/94  CONNECT          TBSP_WORKDEV     TBSP_TEMPORARY   #
OPS$APPLIC1     03/18/94  CONNECT          TBSP_DATA001     TBSP_TEMPORARY
OPS$APPLIC1     03/18/94  RESOURCE         TBSP_DATA001     TBSP_TEMPORARY
SYS             03/17/94  CONNECT          SYSTEM           SYSTEM           #
SYS             03/17/94  DBA              SYSTEM           SYSTEM           #
SYS             03/17/94  EXP_FULL_DATABA  SYSTEM           SYSTEM           #
SYS             03/17/94  IMP_FULL_DATABA  SYSTEM           SYSTEM           #
SYS             03/17/94  RESOURCE         SYSTEM           SYSTEM           #


p.225
set echo off feed off pages 0
spool veedollar_dba.sql
select 'grant select on '||view_name||' to public;'
  from user_views
 where view_name like 'V_$%'
    or view_name like 'DBA_%;
set echo on feed on
@veedollar_dba


p.226     
04031, 00000, "ĹloCg蓖Ăł܂ilClClj"
// *  Iy[eBO VXeɊ蓖ĂĂ̋LKvłD
//          SGAvCx[g gʂĂD
// *u  K̓pbP[WŒ肷邽߂ɁCDBMS_SHARED_POOLpbP[Wgp邩C
//          ܂͋L̎gpʂȂCp[^SHARED_POOL_SIZE
//          ̐ݒl傫肵āC
//          gp\ȋL̗eʂ𑝂₷D


p.226-227
set echo off ver off feed off pages 0

rem * OI邽߂̃[U[ID̓͂D
rem * œ͂[U[̏Ă郁ʂTvʂƂĈD
rem * ܂CvZɎgp邽߂̓ڑ[U[̓͂D

accept username prompt 'User to use??'
accept numusers prompt '# of users ??'

rem *  v$sessionv$processADDRPADDRŌCZbVIDoD
rem * ܂Cv$sessionUSERNAMEƁCɓ͂ꂽ[U[̃}b`OsD

set term off
col a new_value snum
select sid a
from v$process p, v$session s
 where p.addr = s.paddr
   and s.username = 'OPS$'||upper('&username');

rem *  ܂łŁC[U[̃ZbVID擾邱ƂłD
rem *  ŁCv$sesstat炻̃[U[Ă郁ʂ߂D
rem *  Ŏw肵ĂSTATISTIC# = 16́Cv$sesstat̃[U[Ƃ
rem *  MAX SESSION MEMORYɑΉvԍłD

col b new_value pumem
select value b
from v$sesstat
where statistic# = 16
  and sid = &snum;

rem *  ݎgp̋Lv[̍vgpʂ߂D
rem *  iႦ΁CLv[ɂSQL̃TCYȂǁj

col c new_value spl
select sum(sharable_mem) c
from v$sqlarea;

rem *  ̌vZgpCœKȋLv[̃TCY𓱂oD
rem *  œKTCY = 1.3 * (per_user_memory * number_users +
       size_of_sql_in_pool)

col d new_value size1
col e new_value size2
select (&pumem*&numusers+&spl) d,
      (&pumem*&numusers+&spl) + 3/10 * (&pumem*&numusers+&spl) e
from dual;

col pmem form 99,999,990
col nu   like pmem
col sss  like pmem
col tmu  like pmem
col s1   like pmem
col s2   like pmem
set term on
prompt
prompt
prompt
prompt ===================================================
select 'Per user memory requirement:  ', &pumem pmem
  from dual;
select 'Number of users            :  ', &numusers nu
  from dual;
prompt ===================================================
select 'Total memory for users     :  ', &numusers*&pumem tmu
  from dual;
select 'Size of stuff in shared SQL:  ', &spl sss
  from dual;
prompt ===================================================
select 'Base shared pool size      :  ', &size1 s1
  from dual;
select 'Pool size with 30% free    :  ', &size2 s2
  from dual;
prompt ===================================================


p.228
SQL> @pool
User to use?? ops$jonespg
# of users ?? 30
===================================================
Per user memory requirement:       198,116
Number of users            :            30
===================================================
Total memory for users     :     5,943,480
Size of stuff in shared SQL:    10,360,432
===================================================
Base shared pool size      :    16,303,912
Pool size with 30% free    :    21,195,086
===================================================


p.229
select decode(state,0,'FREE',
                    1,'Read and Modified',
                    2,'Read and Non-Modified',
                    4,'Current Block Read','Other'),count(*)
  from x$bh
 group by decode(state,0,'FREE',
                       1,'Read and Modified',
                       2,'Read and Non-Modified',
                       4,'Current Block Read','Other');
                        
 
alter database backup controlfile to trace; 


p.230
# The following commands will create a new control file and use it
# to open the database.
# No data other than log history will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE PRD NORESETLOGS ARCHIVELOG
    MAXLOGFILES 20
    MAXLOGMEMBERS 4
    MAXDATAFILES 30
    MAXINSTANCES 1
    MAXLOGHISTORY 100
LOGFILE
  GROUP 1 (
    '/disk2/prd_log/log1prd_g1.dbf',
    '/data/log_shadow/log2prd_g1.dbf',
    '/disk1/log_shadow/log3prd_g1.dbf'
  ) SIZE 2M,
  GROUP 2 (
    '/oracle/dbs/log1prd_g2.dbf',
    '/disk3/log_shadow/log2prd_g2.dbf',
    '/disk1/log_shadow/log3prd_g2.dbf'
  ) SIZE 2M,

DATAFILE
  '/disk1/oracle_prd/dbs1prd.dbf' SIZE 30M,
  '/oracle/dbs/finance.dbf' SIZE 120M,
  '/disk1/oracle_prd/audit.dbf' SIZE 20M,
  '/disk2/oracle_prd/dba_stats.dbf' SIZE 18M;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;


p.231
create table as select


Name               Null?    Type
------------------ -------- ----
FY_CODE            NOT NULL VARCHAR2(5)
JOB_NUM            NOT NULL VARCHAR2(6)
PROJ_NUM           NOT NULL NUMBER(2)
SDESC_E                     VARCHAR2(30)
SDESC_F                     VARCHAR2(30)
LDESC_E                     VARCHAR2(60)
LDESC_F                     VARCHAR2(60)
BUDGET_HOURS                NUMBER(7,2)
STATUS                      VARCHAR2(2)


p.232
rem * Create a temp table with the new column name.
create table jobs_temp (fy_code, job_num, proj_num, sdesc_e, sdesc_f,
                        ldesc_e, ldesc_f, bud_hours, status)
as select * from jobs;
rem * swap names of tables.
rename jobs to jobs_old;
rename jobs_temp to jobs;


exp userid=user/password tables=jobs rows=n compress=n file=jobs


imp userid=/ indexfile=jobs.sql file=jobs


set echo off feed off pages 0
spool regrant_jobs.sql
select 'grant ' || privilege || ' on ' || table_name || ' to ' ||
        grantee || decode (grantable,'YES',' with grant option;',';')
   from user_tab_privs_made
  where table_name = 'JOBS';
spool off


p.233
set pages 0 feed off echo off
spool jobs_idrop.sql
select 'drop index ' || index_name || ';'
  from user_indexes
 where table_name = 'JOBS';
spool off


rename jobs to jobs_old;


CREATE TABLE "OPS$SLOANKJ"."JOBS" ("FY_CODE" VARCHAR2(5) NOT NULL,
"JOB_NUM" VARCHAR2(6) NOT NULL, "PROJ_NUM" NUMBER(2, 0) NOT NULL,
"SDESC_E" VARCHAR2(30), "SDESC_F" VARCHAR2(30), "LDESC_E"
VARCHAR2(60), "LDESC_F" VARCHAR2(60), "BUD_HOURS" NUMBER(7, 2),
"STATUS" VARCHAR2(2)) PCTFREE 40 PCTUSED 60 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 5242880 NEXT 1064960 MINEXTENTS 1 MAXEXTENTS 240
PCTINCREASE 20 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" ;

CREATE INDEX "OPS$SLOANKJ"."JOBS_1" ON "JOBS" ("FY_CODE" , "JOB_NUM",
"PROJ_NUM" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 835584
NEXT 81920 MINEXTENTS 1 MAXEXTENTS 240 PCTINCREASE 20 FREELISTS 1)
TABLESPACE "INDEXES" ;
CREATE INDEX "OPS$SLOANKJ"."JOBS_3" ON "JOBS" ("PROJ_NUM" ) PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 565248 NEXT 40960 MINEXTENTS 1
MAXEXTENTS 240 PCTINCREASE 20 FREELISTS 1) TABLESPACE "INDEXES" ;
CREATE INDEX "OPS$SLOANKJ"."JOBS_2" ON "JOBS" ("JOB_NUM" ) PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 589824 NEXT 61440 MINEXTENTS 1
MAXEXTENTS 240 PCTINCREASE 20 FREELISTS 1) TABLESPACE "INDEXES" ;


p.234
insert into jobs select * from jobs_old;


select owner, table_name, column_name
  from sys.dba_tab_columns
 where column_name = 'BUDGET_HOURS';
 
 
drop user ops$francisl;


set echo off pages 0 feed off
spool nukeuser.sql
select 'drop ' || object_type || ' ' || owner || '.' || object_name || ';'
  from sys.dba_objects
 where object_type in ('TABLE','VIEW','SEQUENCE','SYNONYM') and
       owner = 'OPS$FRANCISL';
spool off


p.235
spool ts_quotas.sql
select 'alter user quota ' || max_bytes || ' on ' || tablespace_name || ';'
  from sys.dba_ts_quotas
 where nvl(max_bytes,0) > 0;
spool off


set pages 0 feed off echo off
spool yr_trans.parfile
prompt userid=system/manager
prompt file=yr_trans
prompt buffer=10240000
prompt indexes=y
prompt grants=y
prompt owner=(
select unique owner || ','
  from sys.dba_tables
 where tablespace_name = 'YR_TRANS'
    and  owner <>
   (select max(owner)
     from sys.dba_tables 
   where tablespace_name = 'YR_TRANS');
select max(owner) || ')'
  from sys.dba_tables 
where tablespace_name = 'YR_TRANS';
spool off


p.236
select 'create rollback segment ' || segment_name || chr(10),
       '       tablespace ' || tablespace_name || chr(10),
       '       storage ( initial     ' || initial_extent || chr(10),
       '                 next        ' || next_extent || chr(10),
       '                 minextents  ' || min_extents || chr(10),
       '                 maxextents  ' || max_extents || chr(10) ||
       '                 optimal     ' || optsize || ');'
  from sys.dba_rollback_segs a,v$rollstat b,v$rollname c
 where segment_name <> 'SYSTEM'
   and b.usn = c.usn
   and a.segment_name = c.name;
 
 
AppendixA    
p.238
 02030, 00000, "can only select from fixed tables/views"
// *Cause:  An attempt is being made to perform an operation other than
//     a retrieval from a fixed table/view.
// *Action:  You may only select rows from fixed tables/views.


 Name                            Null?    Type
 ------------------------------- -------- ----
 FILE_NAME                                VARCHAR2(257)
 FILE_ID                                  NUMBER
 TABLESPACE_NAME                          VARCHAR2(30)
 BYTES                                    NUMBER
 BLOCKS                                   NUMBER
 STATUS                                   VARCHAR2(9)


 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                                    VARCHAR2(30)
 SEGMENT_NAME                             VARCHAR2(81)
 SEGMENT_TYPE                             VARCHAR2(17)
 TABLESPACE_NAME                          VARCHAR2(30)
 EXTENT_ID                       NOT NULL NUMBER
 FILE_ID                         NOT NULL NUMBER
 BLOCK_ID                        NOT NULL NUMBER
 BYTES                                    NUMBER
 BLOCKS                          NOT NULL NUMBER
 
 
p.239
 Name                            Null?    Type
 ------------------------------- -------- ----
 TABLESPACE_NAME                 NOT NULL VARCHAR2(30)
 FILE_ID                         NOT NULL NUMBER
 BLOCK_ID                        NOT NULL NUMBER
 BYTES                                    NUMBER
 BLOCKS                          NOT NULL NUMBER
 
 
 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                           NOT NULL VARCHAR2(30)
 INDEX_NAME                      NOT NULL VARCHAR2(30)
 TABLE_OWNER                     NOT NULL VARCHAR2(30)
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 TABLE_TYPE                               VARCHAR2(11)
 UNIQUENESS                               VARCHAR2(9)
 TABLESPACE_NAME                 NOT NULL VARCHAR2(30)
 INI_TRANS                       NOT NULL NUMBER
 MAX_TRANS                       NOT NULL NUMBER
 INITIAL_EXTENT                           NUMBER
 NEXT_EXTENT                              NUMBER
 MIN_EXTENTS                     NOT NULL NUMBER
 MAX_EXTENTS                     NOT NULL NUMBER
 PCT_INCREASE                    NOT NULL NUMBER
 PCT_FREE                        NOT NULL NUMBER
 BLEVEL                                   NUMBER
 LEAF_BLOCKS                              NUMBER
 DISTINCT_KEYS                            NUMBER
 AVG_LEAF_BLOCKS_PER_KEY                  NUMBER
 AVG_DATA_BLOCKS_PER_KEY                  NUMBER
 CLUSTERING_FACTOR                        NUMBER
 STATUS                                   VARCHAR2(11)
 
 
p.240
 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                                    VARCHAR2(30)
 OBJECT_NAME                              VARCHAR2(128)
 OBJECT_ID                                NUMBER
 OBJECT_TYPE                              VARCHAR2(13)
 CREATED                                  DATE
 LAST_DDL_TIME                            DATE
 TIMESTAMP                                VARCHAR2(75)
 STATUS                                   VARCHAR2(7)


 Name                            Null?    Type
 ------------------------------- -------- ----
 GRANTEE                                  VARCHAR2(30)
 GRANTED_ROLE                    NOT NULL VARCHAR2(30)
 ADMIN_OPTION                             VARCHAR2(3)
 DEFAULT_ROLE                             VARCHAR2(3)


 Name                            Null?    Type
 ------------------------------- -------- ----
 SEGMENT_NAME                    NOT NULL VARCHAR2(30)
 OWNER                                    VARCHAR2(6)
 TABLESPACE_NAME                 NOT NULL VARCHAR2(30)
 SEGMENT_ID                      NOT NULL NUMBER
 FILE_ID                         NOT NULL NUMBER
 BLOCK_ID                        NOT NULL NUMBER
 INITIAL_EXTENT                           NUMBER
 NEXT_EXTENT                              NUMBER
 MIN_EXTENTS                              NUMBER
 MAX_EXTENTS                              NUMBER
 PCT_INCREASE                             NUMBER
 STATUS                                   VARCHAR2(16)
 INSTANCE_NUM                             VARCHAR2(40) 


p.241
 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                                    VARCHAR2(30)
 SEGMENT_NAME                             VARCHAR2(81)
 SEGMENT_TYPE                             VARCHAR2(17)
 TABLESPACE_NAME                          VARCHAR2(30)
 HEADER_FILE                              NUMBER
 HEADER_BLOCK                             NUMBER
 BYTES                                    NUMBER
 BLOCKS                                   NUMBER
 EXTENTS                                  NUMBER
 INITIAL_EXTENT                           NUMBER
 NEXT_EXTENT                              NUMBER
 MIN_EXTENTS                              NUMBER
 MAX_EXTENTS                              NUMBER
 PCT_INCREASE                             NUMBER
 FREELISTS                                NUMBER
 FREELIST_GROUPS                          NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                           NOT NULL VARCHAR2(30)
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 COLUMN_NAME                     NOT NULL VARCHAR2(30)
 DATA_TYPE                                VARCHAR2(9)
 DATA_LENGTH                     NOT NULL NUMBER
 DATA_PRECISION                           NUMBER
 DATA_SCALE                               NUMBER
 NULLABLE                                 VARCHAR2(1)
 COLUMN_ID                       NOT NULL NUMBER
 DEFAULT_LENGTH                           NUMBER
 DATA_DEFAULT                             LONG
 NUM_DISTINCT                             NUMBER
 LOW_VALUE                                RAW(32)
 HIGH_VALUE                               RAW(32)
 DENSITY                                  NUMBER


p.242
 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                           NOT NULL VARCHAR2(30)
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                 NOT NULL VARCHAR2(30)
 CLUSTER_NAME                             VARCHAR2(30)
 PCT_FREE                        NOT NULL NUMBER
 PCT_USED                        NOT NULL NUMBER
 INI_TRANS                       NOT NULL NUMBER
 MAX_TRANS                       NOT NULL NUMBER
 INITIAL_EXTENT                           NUMBER
 NEXT_EXTENT                              NUMBER
 MIN_EXTENTS                              NUMBER
 MAX_EXTENTS                              NUMBER
 PCT_INCREASE                             NUMBER
 BACKED_UP                                VARCHAR2(1)
 NUM_ROWS                                 NUMBER
 BLOCKS                                   NUMBER
 EMPTY_BLOCKS                             NUMBER
 AVG_SPACE                                NUMBER
 CHAIN_CNT                                NUMBER
 AVG_ROW_LEN                              NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 TABLESPACE_NAME                 NOT NULL VARCHAR2(30)
 INITIAL_EXTENT                           NUMBER
 NEXT_EXTENT                              NUMBER
 MIN_EXTENTS                     NOT NULL NUMBER
 MAX_EXTENTS                     NOT NULL NUMBER
 PCT_INCREASE                    NOT NULL NUMBER
 STATUS                                   VARCHAR2(9)
 
 
p.243
 Name                            Null?    Type
 ------------------------------- -------- ----
 TABLESPACE_NAME                 NOT NULL VARCHAR2(30)
 USERNAME                        NOT NULL VARCHAR2(30)
 BYTES                                    NUMBER
 MAX_BYTES                                NUMBER
 BLOCKS                          NOT NULL NUMBER
 MAX_BLOCKS                               NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 USERNAME                        NOT NULL VARCHAR2(30)
 USER_ID                         NOT NULL NUMBER
 PASSWORD                                 VARCHAR2(30)
 DEFAULT_TABLESPACE              NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE            NOT NULL VARCHAR2(30)
 CREATED                         NOT NULL DATE
 PROFILE                         NOT NULL VARCHAR2(30)


p.243-244
 Name                            Null?    Type
 ------------------------------- -------- ----
 HEIGHT                                   NUMBER
 BLOCKS                          NOT NULL NUMBER
 NAME                            NOT NULL VARCHAR2(30)
 LF_ROWS                                  NUMBER
 LF_BLKS                                  NUMBER
 LF_ROWS_LEN                              NUMBER
 LF_BLK_LEN                               NUMBER
 BR_ROWS                                  NUMBER
 BR_BLKS                                  NUMBER
 BR_ROWS_LEN                              NUMBER
 BR_BLK_LEN                               NUMBER
 DEL_LF_ROWS                              NUMBER
 DEL_LF_ROWS_LEN                          NUMBER
 DISTINCT_KEYS                            NUMBER
 MOST_REPEATED_KEY                        NUMBER
 BTREE_SPACE                              NUMBER
 USED_SPACE                               NUMBER
 PCT_USED                                 NUMBER
 ROWS_PER_KEY                             NUMBER
 BLKS_GETS_PER_ACCESS                     NUMBER
              

p.244
 Name                            Null?    Type
 ------------------------------- -------- ----
 STATEMENT_ID                             VARCHAR2(30)
 TIMESTAMP                                DATE
 REMARKS                                  VARCHAR2(80)
 OPERATION                                VARCHAR2(30)
 OPTIONS                                  VARCHAR2(30)
 OBJECT_NODE                              VARCHAR2(30)
 OBJECT_OWNER                             VARCHAR2(30)
 OBJECT_NAME                              VARCHAR2(30)
 OBJECT_INSTANCE                          NUMBER(38)
 OBJECT_TYPE                              VARCHAR2(30)
 SEARCH_COLUMNS                           NUMBER(38)
 ID                                       NUMBER(38)
 PARENT_ID                                NUMBER(38)
 POSITION                                 NUMBER(38)
 OTHER                                    LONG


 Name                            Null?    Type
 ------------------------------- -------- ----
 PRODUCT                         NOT NULL VARCHAR2(30)
 USERID                                    VARCHAR2(30)
 ATTRIBUTE                                 VARCHAR2(240)
 SCOPE                                     VARCHAR2(240)
 NUMBERIC_VALUE                            NUMBER(15,2)
 CHAR_VALUE                                VARCHAR2(240)
 DATE_VALUE                                DATE
 LONG_VALUE                                LONG


p.245
 Name                            Null?    Type
 ------------------------------- -------- ----
 TS#                             NOT NULL NUMBER
 NAME                            NOT NULL VARCHAR2(30)
 OWNER#                          NOT NULL NUMBER
 ONLINE$                         NOT NULL NUMBER
 UNDOFILE#                                NUMBER
 UNDOBLOCK#                               NUMBER
 BLOCKSIZE                       NOT NULL NUMBER
 INC#                            NOT NULL NUMBER
 SCNWRP                                   NUMBER
 SCNBAS                                   NUMBER
 DFLMINEXT                       NOT NULL NUMBER
 DFLMAXEXT                       NOT NULL NUMBER
 DFLINIT                         NOT NULL NUMBER
 DFLINCR                         NOT NULL NUMBER
 DFLEXTPCT                       NOT NULL NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 FILE#                                    NUMBER
 STATUS                                   VARCHAR2(7)
 CHECKPOINT_CHANGE#                       NUMBER
 BYTES                                    NUMBER
 NAME                                     VARCHAR2(257)


p.245-246
 Name                            Null?    Type
 ------------------------------- -------- ----
 ADDR                                     RAW(4)
 LATCH#                                   NUMBER
 LEVEL#                                   NUMBER
 NAME                                     VARCHAR2(50)
 GETS                                     NUMBER
 MISSES                                   NUMBER
 SLEEPS                                   NUMBER
 IMMEDIATE_GETS                           NUMBER
 IMMEDIATE_MISSES                         NUMBER
 WAITERS_WOKEN                            NUMBER
 WAITS_HOLDING_LATCH                      NUMBER
 SPIN_GETS                                NUMBER
 SLEEP1                                   NUMBER
 SLEEP2                                   NUMBER
 SLEEP3                                   NUMBER
 SLEEP4                                   NUMBER
 SLEEP5                                   NUMBER
 SLEEP6                                   NUMBER
 SLEEP7                                   NUMBER
 SLEEP8                                   NUMBER
 SLEEP9                                   NUMBER
 SLEEP10                                  NUMBER
 SLEEP11                                  NUMBER
 SLEEP12                                  NUMBER
 SLEEP13                                  NUMBER


p.246
 Name                            Null?    Type
 ------------------------------- -------- ----
 LATCH#                                   NUMBER
 NAME                                     VARCHAR2(64)


 Name                            Null?    Type
 ------------------------------- -------- ----
 NAMESPACE                                VARCHAR2(15)
 GETS                                     NUMBER
 GETHITS                                  NUMBER
 GETHITRATIO                              NUMBER
 PINS                                     NUMBER
 PINHITS                                  NUMBER
 PINHITRATIO                              NUMBER
 RELOADS                                  NUMBER
 INVALIDATIONS                            NUMBER


p.247
 Name                            Null?    Type
 ------------------------------- -------- ----
 ADDR                                     RAW(4)
 KADDR                                    RAW(4)
 SID                                      NUMBER
 TYPE                                     VARCHAR2(2)
 ID1                                      NUMBER
 ID2                                      NUMBER
 LMODE                                    NUMBER
 REQUEST                                  NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 GROUP#                                   NUMBER
 THREAD#                                  NUMBER
 SEQUENCE#                                NUMBER
 BYTES                                    NUMBER
 MEMBERS                                  NUMBER
 ARCHIVED                                 VARCHAR2(3)
 STATUS                                   VARCHAR2(8)
 FIRST_CHANGE#                            NUMBER
 FIRST_TIME                               VARCHAR2(20)


 Name                            Null?    Type
 ------------------------------- -------- ----
 GROUP#                                   NUMBER
 STATUS                                   VARCHAR2(7)
 MEMBER                                   VARCHAR2(257)


 Name                            Null?    Type
 ------------------------------- -------- ----
 NUM                                      NUMBER
 NAME                                     VARCHAR2(64)
 TYPE                                     NUMBER
 VALUE                                    VARCHAR2(512)
 ISDEFAULT                                VARCHAR2(9)


p.248
 Name                            Null?    Type
 ------------------------------- -------- ----
 USN                                      NUMBER
 EXTENTS                                  NUMBER
 RSSIZE                                   NUMBER
 WRITES                                   NUMBER
 XACTS                                    NUMBER
 GETS                                     NUMBER
 WAITS                                    NUMBER
 OPTSIZE                                  NUMBER
 HWMSIZE                                  NUMBER
 SHRINKS                                  NUMBER
 WRAPS                                    NUMBER
 EXTENDS                                  NUMBER
 AVESHRINK                                NUMBER
 AVEACTIVE                                NUMBER
 STATUS                                   VARCHAR2(15)


 Name                            Null?    Type
 ------------------------------- -------- ----
 CACHE#                                   NUMBER
 TYPE                                     VARCHAR2(11)
 SUBORDINATE#                             NUMBER
 PARAMETER                                VARCHAR2(32)
 COUNT                                    NUMBER
 USAGE                                    NUMBER
 FIXED                                    NUMBER
 GETS                                     NUMBER
 GETMISSES                                NUMBER
 SCANS                                    NUMBER
 SCANMISSES                               NUMBER
 SCANCOMPLETES                            NUMBER
 MODIFICATIONS                            NUMBER
 FLUSHES                                  NUMBER


p.249
 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


p.249-250
 Name                            Null?    Type
 ------------------------------- -------- ----
 SID                                      NUMBER
 STATISTIC#                               NUMBER
 VALUE                                    NUMBER
 SQL_TEXT                                 VARCHAR2(1000)
 SHARABLE_MEM                             NUMBER
 PERSISTENT_MEM                           NUMBER
 RUNTIME_MEM                              NUMBER
 SORTS                                    NUMBER
 VERSION_COUNT                            NUMBER
 LOADED_VERSIONS                          NUMBER
 OPEN_VERSIONS                            NUMBER
 USERS_OPENING                            NUMBER
 EXECUTIONS                               NUMBER
 USERS_EXECUTING                          NUMBER
 LOADS                                    NUMBER
 FIRST_LOAD_TIME                          VARCHAR2(19)
 INVALIDATIONS                            NUMBER
 PARSE_CALLS                              NUMBER


p.250
 Name                            Null?    Type
 ------------------------------- -------- ----
 SQL_TEXT                                 VARCHAR2(1000)
 SHARABLE_MEM                             NUMBER
 PERSISTENT_MEM                           NUMBER
 RUNTIME_MEM                              NUMBER
 SORTS                                    NUMBER
 VERSION_COUNT                            NUMBER
 LOADED_VERSIONS                          NUMBER
 OPEN_VERSIONS                            NUMBER
 USERS_OPENING                            NUMBER
 EXECUTIONS                               NUMBER
 USERS_EXECUTING                          NUMBER
 LOADS                                    NUMBER
 FIRST_LOAD_TIME                          VARCHAR2(19)
 INVALIDATIONS                            NUMBER
 PARSE_CALLS                              NUMBER
 DISK_READS                               NUMBER
 BUFFER_GETS                              NUMBER
 COMMAND_TYPE                             NUMBER
 PARSING_USER_ID                          NUMBER
 PARSING_SCHEMA_ID                        NUMBER
 KEPT_VERSIONS                            NUMBER
 ADDRESS                                  RAW(4)
 HASH_VALUE                               NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 STATISTIC#                               NUMBER
 NAME                                     VARCHAR2(64)
 CLASS                                    NUMBER


p.251
 Name                            Null?    Type
 ------------------------------- -------- ----
 STATISTIC#                               NUMBER
 NAME                                     VARCHAR2(64)
 CLASS                                    NUMBER
 VALUE                                    NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 EVENT                                    VARCHAR2(64)
 TOTAL_WAITS                              NUMBER
 TOTAL_TIMEOUTS                           NUMBER
 TIME_WAITED                              NUMBER
 AVERAGE_WAIT                             NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 CLASS                                    VARCHAR2(18)
 COUNT                                    NUMBER
 TIME                                     NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 ADDR                                     RAW(4)
 INDX                                     NUMBER
 COUNT                                    NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 ADDR                                     RAW(4)
 INDX                                     NUMBER
 COUNT                                    NUMBER


p.252
 Name                            Null?    Type
 ------------------------------- -------- ----
 STATISTIC#                               NUMBER
 NAME                                     VARCHAR2(64)
 CLASS                                    NUMBER
 VALUE                                    NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 STATISTIC#                               NUMBER
 NAME                                     VARCHAR2(64)
 CLASS                                    NUMBER
 VALUE                                    NUMBER


p.252-253
 Name                            Null?    Type
 ------------------------------- -------- ----
 ADDR                                     RAW(4)
 LATCH#                                   NUMBER
 LEVEL#                                   NUMBER
 NAME                                     VARCHAR2(50)
 GETS                                     NUMBER
 MISSES                                   NUMBER
 SLEEPS                                   NUMBER
 IMMEDIATE_GETS                           NUMBER
 IMMEDIATE_MISSES                         NUMBER
 WAITERS_WOKEN                            NUMBER
 WAITS_HOLDING_LATCH                      NUMBER
 SPIN_GETS                                NUMBER
 SLEEP1                                   NUMBER
 SLEEP2                                   NUMBER
 SLEEP3                                   NUMBER
 SLEEP4                                   NUMBER
 SLEEP5                                   NUMBER
 SLEEP6                                   NUMBER
 SLEEP7                                   NUMBER
 SLEEP8                                   NUMBER
 SLEEP9                                   NUMBER
 SLEEP10                                  NUMBER
 SLEEP11                                  NUMBER
 SLEEP12                                  NUMBER
 SLEEP13                                  NUMBER


p.253
 Name                            Null?    Type
 ------------------------------- -------- ----
 ADDR                                     RAW(4)
 LATCH#                                   NUMBER
 LEVEL#                                   NUMBER
 NAME                                     VARCHAR2(50)
 GETS                                     NUMBER
 MISSES                                   NUMBER
 SLEEPS                                   NUMBER
 IMMEDIATE_GETS                           NUMBER
 IMMEDIATE_MISSES                         NUMBER
 WAITERS_WOKEN                            NUMBER
 WAITS_HOLDING_LATCH                      NUMBER
 SPIN_GETS                                NUMBER
 SLEEP1                                   NUMBER
 SLEEP2                                   NUMBER
 SLEEP3                                   NUMBER
 SLEEP4                                   NUMBER
 SLEEP5                                   NUMBER
 SLEEP6                                   NUMBER
 SLEEP7                                   NUMBER
 SLEEP8                                   NUMBER
 SLEEP9                                   NUMBER
 SLEEP10                                  NUMBER
 SLEEP11                                  NUMBER
 SLEEP12                                  NUMBER
 SLEEP13                                  NUMBER


p.254
 Name                            Null?    Type
 ------------------------------- -------- ----
 USN                                      NUMBER
 EXTENTS                                  NUMBER
 RSSIZE                                   NUMBER
 WRITES                                   NUMBER
 XACTS                                    NUMBER
 GETS                                     NUMBER
 WAITS                                    NUMBER
 OPTSIZE                                  NUMBER
 HWMSIZE                                  NUMBER
 SHRINKS                                  NUMBER
 WRAPS                                    NUMBER
 EXTENDS                                  NUMBER
 AVESHRINK                                NUMBER
 AVEACTIVE                                NUMBER
 STATUS                                   VARCHAR2(15)


 Name                            Null?    Type
 ------------------------------- -------- ----
 USN                                      NUMBER
 EXTENTS                                  NUMBER
 RSSIZE                                   NUMBER
 WRITES                                   NUMBER
 XACTS                                    NUMBER
 GETS                                     NUMBER
 WAITS                                    NUMBER
 OPTSIZE                                  NUMBER
 HWMSIZE                                  NUMBER
 SHRINKS                                  NUMBER
 WRAPS                                    NUMBER
 EXTENDS                                  NUMBER
 AVESHRINK                                NUMBER
 AVEACTIVE                                NUMBER
 STATUS                                   VARCHAR2(15)


p.255
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAMESPACE                                VARCHAR2(15)
 GETS                                     NUMBER
 GETHITS                                  NUMBER
 GETHITRATIO                              NUMBER
 PINS                                     NUMBER
 PINHITS                                  NUMBER
 PINHITRATIO                              NUMBER
 RELOADS                                  NUMBER
 INVALIDATIONS                            NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 NAMESPACE                                VARCHAR2(15)
 GETS                                     NUMBER
 GETHITS                                  NUMBER
 GETHITRATIO                              NUMBER
 PINS                                     NUMBER
 PINHITS                                  NUMBER
 PINHITRATIO                              NUMBER
 RELOADS                                  NUMBER
 INVALIDATIONS                            NUMBER


p.255-256 
 Name                            Null?    Type
 ------------------------------- -------- ----
 CACHE#                                   NUMBER
 TYPE                                     VARCHAR2(11)
 SUBORDINATE#                             NUMBER
 PARAMETER                                VARCHAR2(32)
 COUNT                                    NUMBER
 USAGE                                    NUMBER
 FIXED                                    NUMBER
 GETS                                     NUMBER
 GETMISSES                                NUMBER
 SCANS                                    NUMBER
 SCANMISSES                               NUMBER
 SCANCOMPLETES                            NUMBER
 MODIFICATIONS                            NUMBER
 FLUSHES                                  NUMBER
 
 
p.256
 Name                            Null?    Type
 ------------------------------- -------- ----
 CACHE#                                   NUMBER
 TYPE                                     VARCHAR2(11)
 SUBORDINATE#                             NUMBER
 PARAMETER                                VARCHAR2(32)
 COUNT                                    NUMBER
 USAGE                                    NUMBER
 FIXED                                    NUMBER
 GETS                                     NUMBER
 GETMISSES                                NUMBER
 SCANS                                    NUMBER
 SCANMISSES                               NUMBER
 SCANCOMPLETES                            NUMBER
 MODIFICATIONS                            NUMBER
 FLUSHES                                  NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 EVENT                                    VARCHAR2(64)
 TOTAL_WAITS                              NUMBER
 TOTAL_TIMEOUTS                           NUMBER
 TIME_WAITED                              NUMBER
 AVERAGE_WAIT                             NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 EVENT                                    VARCHAR2(64)
 TOTAL_WAITS                              NUMBER
 TOTAL_TIMEOUTS                           NUMBER
 TIME_WAITED                              NUMBER
 AVERAGE_WAIT                             NUMBER
 
 
p.257
 Name                            Null?    Type
 ------------------------------- -------- ----
 STATS_GATHER_TIMES                       VARCHAR2(100)


 Name                            Null?    Type
 ------------------------------- -------- ----
 TS                              NOT NULL VARCHAR2(30)
 NAME                                     VARCHAR2(257)
 PYR                                      NUMBER
 PYW                                      NUMBER
 PRT                                      NUMBER
 PWT                                      NUMBER
 PBR                                      NUMBER
 PBW                                      NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 TS                              NOT NULL VARCHAR2(30)
 NAME                                     VARCHAR2(257)
 PYR                                      NUMBER
 PYW                                      NUMBER
 PRT                                      NUMBER
 PWT                                      NUMBER
 PBR                                      NUMBER
 PBW                                      NUMBER


 Name                            Null?    Type
 ------------------------------- -------- ----
 TS                              NOT NULL VARCHAR2(30)
 NAME                                     VARCHAR2(257)
 PYR                                      NUMBER
 PYW                                      NUMBER
 PRT                                      NUMBER
 PWT                                      NUMBER
 PBR                                      NUMBER
 PBW                                      NUMBER




