wORACLE8 DBAnhubNx


Chapter1
p.10
create table EMPLOYEE
(EmpNo            NUMBER(10)    PRIMARY KEY,
 Name             VARCHAR2(40)  NOT NULL,
 DeptNo           NUMBER(2)     DEFAULT 10,
 Salary           NUMBER(7,2)   CHECK salary<1000000,
 Birth_Date       DATE,
 Soc_Sec_Num      CHAR(9)       UNIQUE,
 foreign key (DeptNo) references DEPT.DeptNo)
tablespace USERS;


p.11
create type NAME_TY as object
(First_Name     VARCHAR2(25),
Middle_Initial  CHAR(1),
Last_Name       VARCHAR2(30),
Suffix          VARCHAR2(5));


create table EMPLOYEE
(EmpNo            NUMBER(10)    PRIMARY KEY,
 Name             NAME_TY,
 DeptNo           NUMBER(2)     DEFAULT 10,
 Salary           NUMBER(7,2)   CHECK salary<1000000,
 Birth_Date       DATE,
 Soc_Sec_Num      CHAR(9)       UNIQUE,
 foreign key (DeptNo) references DEPT.DeptNo)
tablespace USERS;


p.12
create table NAME of NAME_TY;


p.15
where key_col_value = 1002


where key_col_value > 1000


p.16
!select Name
  from EMPLOYEE
 where EmpNo = 123;
 
 
select Name
  from EMPLOYEE
 where EmpNo > 123;


p.17
select MY_SUBSTR('text') from DUAL;


p.19
create public synonym EMPLOYEE for HR.EMPLOYEE;


p.21
create public database link MY_LINK
connect to HR identified by PUFFINSTUFF
using 'DB1';


select * from EMPLOYEE@MY_LINK;


create synonym EMPLOYEE for EMPLOYEE@MYLINK;



Chapter2
p.43
control_files              = (/db01/oracle/ORA1/ctrl1ora1.ctl,
                              /db02/oracle/ORA1/ctrl2ora1.ctl,
                              /db03/oracle/ORA1/ctrl3ora1.ctl)


p.44
> svrmgrl
SVRMGR> connect internal
SVRMGR> alter database
     2> add logfile group 4
     3> ('/db01/oracle/CC1/log_1c.dbf',
     4>  '/db02/oracle/CC1/log_2c.dbf') size 5M;


> svrmgrl
SVRMGR> connect internal
SVRMGR> alter database
     2> add logfile member '/db03/oracle/CC1/log_3c.dbf'
     3> to group 4;


p.50
select * from EMPLOYEE@HR_LINK
where  Office='ANNAPOLIS';


p.51
create view LOCAL_EMP
as select * from EMPLOYEE@HR_LINK
where Office='ANNAPOLIS';

grant select on LOCAL_EMP to PUBLIC;


p.52
HQ =(DESCRIPTION=
      (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=HQ)
            (PORT=1521))
      (CONNECT DATA=
            (SID=loc)))



Chapter3
p.61
alter user USER quota 0 on SYSTEM;


create user USERNAME identified by PASSWORD
default tablespace TABLESPACE_NAME;


alter user USERNAME default tablespace TABLESPACE_NAME;


p.63
alter index EMPLOYEE$DEPT_NO rebuild
tablespace INDEXES
storage (initial 2M next 2M pctincrease 0);


alter user SYSTEM quota 0 on SYSTEM;
alter user SYSTEM quota 50M on TOOLS;


p.64
create user USERNAME identified by PASSWORD
default tablespace SOME_TABLESPACE
temporary tablespace TEMP;


p.65
alter user USERNAME temporary tablespace TEMP;


create user USERNAME identified by PASSWORD
default tablespace USERS
temporary tablespace TEMP;


alter user USERNAME default tablespace USERS;


p.67
alter table EMPLOYEE_TYPE
  add constraint UNIQ_DESCR  unique(DESCRIPTION)
using index tablespace INDEXES_2;


p.68
set transaction use rollback segment SEGMENT_NAME


p.69
create user USERNAME identified by PASSWORD
default tablespace TABLESPACE_NAME
temporary tablespace temp_USER;


alter user USERNAME temporary tablespace TEMP_USER;



Chapter4
p.90-91
set pagesize 60 linesize 80 newpage 0 feedback off
ttitle skip center "Database File IO Weights" skip center -
"ordered by Drive" skip 2
column Total_IO format 999999999
column Weight format 999.99
column file_name format A40
break on Drive skip 2
compute sum of Weight on Drive

select
substr(DF.Name, 1,5) Drive,
DF.Name File_Name,
FS.Phyblkrd+FS.Phyblkwrt Total_IO,
100*(FS.Phyblkrd+FS.Phyblkwrt)/MaxIO Weight
from V$FILESTAT FS, V$DATAFILE DF,
  (select MAX(Phyblkrd+Phyblkwrt) MaxIO
    from V$FILESTAT)
where DF.File# = FS.File#
order by Weight desc

spool io_weights
/
spool off


p.91
                  Database File I/O Weights
                         Ordered by Drive

DRIVE    FILE_NAME                    TOTAL_IO      WEIGHT
-----    ---------------------------- --------     -------
/db01    /db01/oracle/DEMO/sys01.dbf     31279       40.65
         /db01/oracle/DEMO/tools.dbf      2112        2.74
*****                                              -------
sum                                                  43.39

/db02    /db02/oracle/DEMO/rbs01.dbf      3799        5.94
         /db02/oracle/DEMO/rbs02.dbf      2465        3.20
         /db02/oracle/DEMO/rbs03.dbf      1960        2.55
         /db02/oracle/DEMO/rbs04.dbf      1675        2.18
*****                                              -------
sum                                                  13.87

/db03    /db03/oracle/DEMO/ddata.dbf     76950      100.00
*****                                              -------
sum                                                 100.00

/db04    /db04/oracle/DEMO/demondx.dbf   36310       47.19
         /db04/oracle/DEMO/temp.dbf       4012        5.21
*****                                              -------
sum                                                  52.40


p.94
> tar /dev/rmt/1hc /db0[1-8]/oracle/CASE


p.100
alter tablespace TEMP temporary;


alter tablespace TEMP permanent;


p.102
alter tablespace DATA coalesce;


alter database
datafile '/db05/oracle/CC1/data01.dbf' resize 200M;


p.103
alter tablespace DATA
datafile '/db05/oracle/CC1/data01.dbf' resize 200M;


create tablespace DATA
datafile '/db05/oracle/CC1/data01.dbf' size 200M
autoextend ON
next 10M
maxsize 250M;


p.104
alter tablespace DATA
add datafile '/db05/oracle/CC1/data02.dbf'
size 50M
autoextend ON
maxsize unlimited;


alter database
datafile '/db05/oracle/CC1/data01.dbf'
autoextend ON
maxsize unlimited;


p.105
> svrmgrl
SVRMGR> connect internal;
SVRMGR> shutdown;
SVRMGR> exit;


> mv /db01/oracle/CC1/data01.dbf /db02/oracle/CC1


p.106
> svrmgrl
SVRMGR> connect internal;
SVRMGR> startup mount CC1;
SVRMGR> alter database rename file
     2> '/db01/oracle/CC1/data01.dbf' to
     3> '/db02/oracle/CC1/data01.dbf';


SVRMGR> alter database open;


p.107
> svrmgrl
SVRMGR> connect internal;
SVRMGR> alter tablespace DATA offline;
SVRMGR> exit;


> mv /db01/oracle/CC1/data01.dbf /db02/oracle/CC1


> svrmgrl
SVRMGR> connect internal;
SVRMGR> alter tablespace DATA rename datafile
     2> '/db01/oracle/CC1/data01.dbf' to
     3> '/db02/oracle/CC1/data01.dbf';


p.108
SVRMGR> alter tablespace DATA online;


> svrmgrl
SVRMGR> connect internal;
SVRMGR> shutdown;
SVRMGR> exit;


> mv /db05/oracle/CC1/redo01CC1.dbf /db02/oracle/CC1


p.109
> svrmgrl
SVRMGR> connect internal;
SVRMGR> startup mount CC1;
SVRMGR> alter database rename file 
     2> '/db05/oracle/CC1/redo01CC1.dbf' to
     3> '/db02/oracle/CC1/redo01CC1.dbf';


SVRMGR> alter database open;


p.110
> svrmgrl
SVRMGR> connect internal;
SVRMGR> shutdown;
SVRMGR> exit;


> mv /db05/oracle/CC1/ctrl1CC1.ctl /db02/oracle/CC1


control_files     =  (/db01/oracle/CC1/ctrl1CC1.ctl,
                      /db03/oracle/CC1/ctrl1CC1.ctl,
                      /db05/oracle/CC1/ctrl1CC1.ctl)


control_files     =  (/db01/oracle/CC1/ctrl1CC1.ctl,
                      /db03/oracle/CC1/ctrl1CC1.ctl,
                      /db02/oracle/CC1/ctrl1CC1.ctl)


p.111
> svrmgrl
SVRMGR> connect internal;
SVRMGR> startup;
SVRMGR> exit;


alter database datafile '/db05/oracle/CC1/data01.dbf' 
resize 80M;


p.112
alter database datafile '/db05/oracle/CC1/data01.dbf'
resize 80M;
*
ERROR at line 1:
ORA-03297: file contains 507 blocks of data beyond
requested RESIZE value


p.113
declare
        VAR1 number;
        VAR2 number;
        VAR3 number;
        VAR4 number;
        VAR5 number;
        VAR6 number;
        VAR7 number;
begin
dbms_space.unused_space('OPS$CC1','SPACES','TABLE',
                          VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7);
   dbms_output.put_line('OBJECT_NAME       = SPACES');
   dbms_output.put_line('---------------------------');
   dbms_output.put_line('TOTAL_BLOCKS      = '||VAR1);
   dbms_output.put_line('TOTAL_BYTES       = '||VAR2);
   dbms_output.put_line('UNUSED_BLOCKS     = '||VAR3);
   dbms_output.put_line('UNUSED_BYTES      = '||VAR4);
   dbms_output.put_line('LAST_USED_EXTENT_FILE_ID  = '||VAR5);
   dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||VAR6);
   dbms_output.put_line('LAST_USED_BLOCK   = '||VAR7);
end;
/


alter table SPACES deallocate unused keep 80K;


p.114
alter index IU_SPACES$DB_TS_CD rebuild
storage (initial 10M next 5M pctincrease 0)
tablespace INDX_1;



Chapter5
p.123
create role APPLICATION_USER;
grant CREATE SESSION to APPLICATION_USER;
grant APPLICATION_USER to username;


p.128
2048 - 90 = 1958oCg̋󂫗̈


1958*(pctfree/100) = 1958*0.1 = 196i؂グj


1958 - 196 = 1762oCggp\


select AVG(NVL(VSIZE(Column1),0))+
       AVG(NVL(VSIZE(Column2),0))+
       AVG(NVL(VSIZE(Column3),0))   Avg_Row_Length
from TABLENAME;


p.129
1s̎gpTCY = Avg_Row_Length
                      + 3
                      + (̌)
                      + (̌)


p.130
1s̎gpTCY = 24
                      + 3
                      + 3
                      + 0
                      = 30ioCg/sj


1ubN̍s = TRUNC(1762/30)
                      = 58is/ubNj


analyze table TABLENAME compute statistics;


p.131
select Num_Rows,              /*s*/
       Blocks,                /*gp̃ubN*/
       Num_Rows/Blocks        /*1ubN̍s*/
  from USER_TABLES
 where Table_Name='TABLENAME';


p.132
2048 - 161 = 1887oCg̋󂫗̈


1887*(pctfree/100) = 1887*0.1 = 189i؂グj


1887 - 189 = 1698oCggp\


p.133
select AVG(NVL(VSIZE(Column1),0))+
       AVG(NVL(VSIZE(Column2),0))   Avg_Row_Length
from TABLENAME;


1s̎gpTCY = Avg_Row_Length
                      + (̌)
                      + (̌)
                      + 8i=wb_[p̃oCgj


1s̎gpTCY = 16
                      + 2
                      + 0
                      + 8
                      = 26ioCg/Ggj


26 + 1 = 27ioCg/sj


1ubÑGǧ = TRUNC(1698/27)
                                   = 62iGg/ubNj


p.135
2048 - 110 = 1938oCg̋󂫗̈


1938*(pctfree/100) = 1938*0.1 = 194i؂グj


1938 - 194 = 1744oCggp\


gp\ȋ󂫗̈  = 1744oCg
                    - 4oCg
                    - 4*(\̌)
                    = 1744
                    - 4
                    - 8
                    = 1732oCg


p.136
select AVG(NVL(VSIZE(Column1),0))+
       AVG(NVL(VSIZE(Column2),0))   Avg_Row_Length_1
from TABLE1;

select AVG(NVL(VSIZE(Column1),0))   Avg_Row_Length_2
from TABLE2;


s̕ϒ = 23oCg


swb_[̃TCY = 4oCg
                   + (̌)
                   + (̌)


1s̎gpTCY = (s̕ϒ) + (swb_[̃TCY)
        = 23 + 4 + (̌) + (̌)
        = 23 + 4 + 3 + 0
        = 30oCg


p.137
select
   COUNT(DISTINCT(column name))/    /* \̃R[h*/
   COUNT(*)  rows_per_key           /* NX^L[̒ľ*/
from tablename;


select
   AVG(NVL(VSIZE(cluster key column),0)) Avg_Key_Length
 from TABLE1;


size=
((TABLE1̃NX^L[1̍s)*(TABLE1̍s̕ϒ))+
((TABLE2̃NX^L[1̍s)*(TABLE2̍s̕ϒ))+
(NX^L[̃wb_[)+
(NX^L[̗)+
(NX^L[̕ϒ)+
2*((TABLE1̃NX^L[1̍s)+(TABLE2̃NX^L[1̍s)+(NX^̑̕\ɂNX^L[1̍s))


SIZE     = (30 rows per key in Table1*20 bytes per row)+
    (1 row per key in Table2*3 bytes per row)+
    19  bytes for the cluster key header+
    10  bytes for the column length of the cluster key+
    5   bytes for the average length of the cluster key+
    2*(30 rows+1 row)
   = (30*20)+(1*3)+19+10+5+(2*31)
   = 600+3+19+10+5+62
   = 699 bytes


p.138
1ubÑNX^L[̌ = (󂫕Kv̈)/(size + 42)
                                    = 1732/(700+42)
                                    = 1732/742
                                    = 2i؂̂āj


create type ADDRESS_TY as object
(Street   VARCHAR2(50),
City      VARCHAR2(25),
State     CHAR(2),
Zip       NUMBER);


create table CUSTOMER
(Name     VARCHAR2(25),
Address   ADDRESS_TY);


p.139
insert into CUSTOMER values
(1,ADDRESS_TY('My Street', 'Some City', 'ST', 10001));


p.140
1s̎gpTCY = Avg_Row_Length
                      + (̌)
                      + (̌)
                      + 2i=wb_[̃oCgj




Chapter6
p.158-159
drop table dbs;

rem /* ̕\ɂ́ACX^XɊւi[*/

create table DBS
(Db_Nm        VARCHAR2(8),     /*CX^X̖O*/
 Host_Nm      VARCHAR2(8),     /*zXgiT[o[j̖O*/
 Description  VARCHAR2(80))    /*CX^XɊւ*/
tablespace CC;

drop table FILES;

rem /*̕\ɂ́Af[^t@CɊւi[*/

create table FILES
(Db_Nm        VARCHAR2(8),     /*CX^X̖O*/
TS            VARCHAR2(30),    /*\̖̈O*/
Check_Date    DATE,            /*͓t*/
File_Nm       VARCHAR2(80),    /*t@C*/
Blocks        NUMBER,          /*t@C̃TCY*/
primary key(Db_Nm, TS, Check_Date,File_Nm))
tablespace CC;

drop view FILES_TS_VIEW;

rem /*̃r[ł́At@C̃TCY\̈ʂɃO[v*/

create view FILES_TS_VIEW as
select
   Db_Nm,                      /*CX^X̖O*/
   TS,                         /*\̖̈O*/
   Check_Date,                 /*͓t */
   SUM(Blocks) Sum_File_Blocks /*\̈̊ăubN*/
from FILES
group by
   Db_Nm,
   TS,
   Check_Date;

drop table SPACES;

rem /*̕\ɂ́A󂫗̈Ɋւi[*/

create table SPACES
(Db_Nm        VARCHAR2(8),   /*CX^X̖O*/
TS            VARCHAR2(30),  /*\̖̈O*/
Check_Date    DATE,          /*͓t */
Count_Free_Blocks NUMBER,    /*󂫃GNXeǧ*/
Sum_Free_Blocks   NUMBER,    /*󂫗̈iOracleubNj*/
Max_Free_Blocks   NUMBER,    /*ő̋󂫃GNXeg */ 
primary key (Db_Nm, Ts, Check_Date))
tablespace CC;

drop table EXTENTS;

rem /*̕\ɂ́AGNXegɊւi[ */

create table EXTENTS
(Db_Nm   VARCHAR2(8),      /*CX^X̖O*/
TS       VARCHAR2(30),     /*\̖̈O*/
Seg_Owner    VARCHAR2(30), /*ZOg̏L*/
Seg_Name VARCHAR2(32),     /*ZOg̖O*/
Seg_Type VARCHAR2(17),     /*ZOg̃^Cv*/
Extents  NUMBER,           /*čς݃GNXeǧ*/
Blocks   NUMBER,           /*čς݃ubŇ*/
Check_Date    DATE,        /*͓t */
primary key (Db_Nm, TS, Seg_Owner, Seg_Name, Check_Date))
tablespace CC;


p.160
create database link CASE
connect to system identified by manager
using 'case';


p.162
# t@C:  ins_cc1
#
# ̃XNvǵA1sB̃XNvgsƁÃXNvg
# Ăяoinserts.sqlt@CɋLqĂf[^x[Ẍ̗̎gp󋵂
# f[^x[XCC1ɃR[hƂđ}BVf[^x[Xǉۂ́A
# CC1ŐVN쐬ÃGgXNvginserts.sql
# ǉKvB
# 
ORACLE_SID=cc1; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
. oraenv
cd /orasw/dba/CC1
svrmgrl <<EOF
connect internal
startup;
!sqlplus / @inserts
shutdown
EOF


p.162-163
rem
rem  t@C:  inserts.sql
rem  i[ꏊ:  /orasw/dba/CC1
rem  Ăяo:  VFXNvgins_cc1
rem  VXeɐVf[^x[Xǉ邽тɁA
rem  YVGgɋLq邱ƁB
rem
set verify off
@ins_all CASE
@ins_all CC1
analyze table FILES compute statistics;
analyze table SPACES compute statistics;
analyze table EXTENTS compute statistics;
analyze table RSEGS compute statistics;
@space_watcher
@extent_watcher


p.164-165
rem
rem  t@C:  ins_all.sql
rem  i[ꏊ:  /orasw/dba/CC1
rem  CC1̒̊Ďp̕\ɑ΂insertׂĎsB
rem  ̃XNvǵACX^XƂinserts.sql
rem  ĂяoB
rem  ANZX̃CX^X̌Ƀf[^x[XN
rem  w肷̂]܂B
rem
insert into FILES
   (Db_Nm,
   TS,
   Check_Date,
   File_Nm,
   Blocks)
select
   UPPER('&&1'),     /*f[^x[XNACX^X*/
   Tablespace_Name,  /*\̈於*/
   TRUNC(SysDate),   /*⍇̎st*/
   File_Name,        /*f[^x[Xt@C̃tl[*/
   Blocks            /*t@C̃f[^x[XubŇ*/
from sys.DBA_DATA_FILES@&&1
/
commit;
rem
insert into SPACES
   (Db_Nm,
   Check_Date,
   TS,
   Count_Free_Blocks,
   Sum_Free_Blocks,
   Max_Free_Blocks)
select
   UPPER('&&1'),     /*f[^x[XNACX^X*/
   TRUNC(SysDate),   /*⍇̎st*/
   Tablespace_Name,  /*\̈於*/
   COUNT(Blocks),    /*󂫗̈̃Gg */
   SUM(Blocks),      /*\̈̑󂫗̈*/
   MAX(Blocks)       /*\̈̒ōő̋󂫃GNXeg*/
from sys.DBA_FREE_SPACE@&&1
group by Tablespace_Name
/
commit;
rem
insert into EXTENTS
   (Db_Nm,
   TS,
   Seg_Owner,
   Seg_Name,
   Seg_Type,
   Extents,
   Blocks,
   Check_Date)
select
   UPPER('&&1'),    /*f[^x[XNACX^X*/
   Tablespace_Name, /*\̈於*/
   Owner,           /*ZOg̏L*/
   Segment_Name,    /*ZOg̖O*/
   Segment_Type,    /*ZOg̃^CviTABLEAINDEXȂǁj*/
   Extents,         /*ZOg̃GNXeg*/
   Blocks,         /*ZOg̃f[^x[XubŇ*/
   TRUNC(SysDate)   /*⍇̎st*/
from sys.DBA_SEGMENTS@&&1
where Extents>9          /*gꂽZOg܂*/
or Segment_Type = 'ROLLBACK'   /*[obNZOgL^*/
/
commit;
rem
undefine 1


p.166-167
rem
rem  t@C:  space_watcher.sql
rem  i[ꏊ:  /orasw/dba/CC1
rem  Ăяo:  inserts.sql
rem
rem  ...ȓV̊ϑ҂̂Ƃ
rem  ނ̋ɐVfꂽƂiL[cj
rem
column Db_Nm format A8
column TS format A20
column Week4 format 999 heading "1Wk|Ago"
column Week3 format 999 heading "2Wks|Ago"
column Week2 format 999 heading "3Wks|Ago"
column Week1 format 999 heading "4Wks|Ago"
column Today format 999
column Change format 999

set pagesize 60
break on Db_Nm skip 2
ttitle center 'Tablespaces whose PercentFree values have -
decreased 5 pct this month' skip 2

select
   SPACES.Db_Nm,
   SPACES.TS,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDateSysDate-28),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Week1,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-21),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Week2,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-14),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Week3,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-7),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Week4,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Today,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) -
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-28),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Change
from SPACES, FILES_TS_VIEW FTV
where SPACES.Db_Nm = FTV.Db_Nm          /*DB*/
and SPACES.TS = FTV.TS                  /*TS*/
and SPACES.Check_Date = ftv.Check_Date  /*`FbNt*/
and exists                              /*݂Ă邱*/
   (select 'x' from spaces x
   where x.db_nm = SPACES.db_nm
   and x.ts = SPACES.ts
   and x.Check_Date = TRUNC(SysDate))
group by
   SPACES.Db_Nm,
   SPACES.Ts
having               /*percentfree5%ȉɌ?*/
( ABS( MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate),
         ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) -
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-28),
         ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)))
>5    )
or                  /*percentfree10%?*/
( MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) <10)
order by SPACES.Db_Nm, 
   DECODE(MAX(DECODE(SPACES.Check_Date,TRUNC(SysDate),
     ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) -
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-28),
     ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)),0,9999,
   MAX(DECODE(SPACES.Check_Date,TRUNC(SysDate),
     ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) -
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-28),
     ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0))),
   MAX(DECODE(SPACES.Check_Date,TRUNC(SysDate),
     ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0))

spool space_watcher.lst
/
spool off


p.168-169
rem
rem  t@C:  ext_watcher.sql
rem  i[ꏊ:  /orasw/dba/CC1
rem  Ăяo:  Called from inserts.sql
rem
rem  ...ȓV̊ϑ҂̂Ƃ
rem  ނ̋ɐVfꂽƂiL[cj
rem
column Db_Nm format A8
column TS format A18
column Seg_Owner format a14
column Seg_Name format a32
column Seg_Type format a8
column Blocks format 99999999
column Week4 format 999 heading "1Wk|Ago"
column Week3 format 999 heading "2Wks|Ago"
column Week2 format 999 heading "3Wks|Ago"
column Week1 format 999 heading "4Wks|Ago"
column Today format 999
column Change format 999

set pagesize 60 linesize 132
break on Db_Nm skip 2 on TS skip 1 on Seg_Owner
ttitle center 'Segments whose extent count is over 10' -
skip 2

select
   EXTENTS.Db_Nm,
   EXTENTS.TS,
   EXTENTS.Seg_Owner,
   EXTENTS.Seg_Name,
   EXTENTS.Seg_Type,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Blocks,0)) Blocks,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extents,0)) Week1,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-21),
         Extents,0)) Week2,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-14),
         Extents,0)) Week3,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-7),
         Extents,0)) Week4,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Extents,0)) Today,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Extents,0)) -
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extents,0)) Change
from EXTENTS
where exists  /*̃ZOǵA{o?*/
   (select 'x' from EXTENTS x
   where x.Db_Nm = EXTENTS.Db_Nm
   and x.TX = EXTENTS.TX
   and x.Seg_Owner = EXTENTS.Seg_Owner
   and x.Seg_Name = EXTENTS.Seg_Name
   and x.Seg_Type = EXTENTS.Seg_Type
   and x.Check_Date = TRUNC(SysDate))
group by
   EXTENTS.Db_Nm,
   EXTENTS.TX,
   EXTENTS.Seg_Owner,
   EXTENTS.Seg_Name,
   EXTENTS.Seg_Type
order by EXTENTS.Db_Nm, EXTENTS.TS, 
   DECODE(MAX(DECODE(EXTENTS.Check_Date,TRUNC(SysDate),
         Extents,0)) -
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extents,0)),0,-9999,
   MAX(DECODE(EXTENTS.Check_Date,TRUNC(SysDate),
         Extents,0)) -
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extents,0))) desc,
   MAX(DECODE(EXTENTS.Check_Date,TRUNC(SysDate),
         Extents,0)) desc

spool extent_watcher.lst
/
spool off


p.171-172
rem
rem space_summary.sql
rem  p[^1: f[^x[XN
rem  p[^2: `FbNt
rem  p[^3: ubNTCY̊iOracle:OSj
rem
rem  SQL*Plus̖{|[ǧĂяo@:
rem  @space_summary link_name Check_Date block_ratio
rem
rem  :
rem  @space_summary CASE 07-NOV-98 4
rem
rem  {|[ǵAf[^x[XƂɏT1ĂяoƁB
rem
set pagesize 60 linesize 132 verify off feedback off 
set newpage 0
column TS heading 'Tablespace' format A18
column File_Nm heading 'File nm' format A40
column Blocks heading 'Orablocks'
column Percentfree format 999.99
column Diskblocks format 99999999
column Cfb format 9999999 heading 'NumFrExts'
column Mfb format 9999999 heading 'MaxFrExt'
column Sfb format 9999999 heading 'SumFrBl'
column Dfrb format 9999999 heading 'DiskFrBl'
column Sum_File_Blocks heading 'DiskBlocks'
column Maxfrpct heading 'MaxFrPct' format 9999999

break on TS
ttitle center 'Oracle Tablespaces in ' &&1 skip center -
'Check Date = ' &&2 skip 2 center
spool &&1._space_summary.lst

select
   Ts,                    /*\̈於*/
   File_Nm,               /*t@C*/
   Blocks,                /*t@COracleubN*/
   Blocks*&&3 Diskblocks  /*t@COS̃ubN*/
from FILES
where Check_Date = '&&2'
and Db_Nm = UPPER('&&1')
order by TS, File_Nm
/

ttitle center 'Oracle Free Space Statistics for ' &&1 -
skip center '(Extent Sizes in Oracle blocks)' skip center -
  'Check Date = ' &&2 skip 2

select
   SPACES.TS,                    /*\̈於*/
   SPACES.Count_Free_Blocks Cfb, /*󂫃GNXeǧ*/
   SPACES.Max_Free_Blocks Mfb,   /*ő̋󂫃GNXeg*/
   SPACES.Sum_Free_Blocks Sfb,   /*󂫗̈̍v*/
   ROUND(100*Sum_Free_Blocks/Sum_File_Blocks,2)
        Percentfree,             /*\̈percent freel*/
   ROUND(100*Max_Free_Blocks/Sum_Free_Blocks,2)
    Maxfrpct,                /*iőGNXeg:vj*/
   SPACES.Sum_Free_Blocks*&&3 Dfrb, /*fBXN̋󂫃ubN*/
   Sum_File_Blocks*&&3 Sum_File_Blocks /*fBXN̊čς݃ubN*/
from SPACES, FILES_TS_VIEW FTV
where SPACES.Db_Nm = FTV.Db_Nm
and SPACES.TS = FTV.TS
and SPACES.Check_Date = FTV.Check_Date
and SPACES.Db_Nm = UPPER('&&1')
and SPACES.Check_Date = '&&2'
/
spool off
undefine 1
undefine 2
undefine 3


p.174
delete from FILES
 where Check_Date < SysDate-60;

commit;

delete from SPACES
 where Check_Date < SysDate-60;

commit;

delete from EXTENTS
 where Check_Date < SysDate-60;

commit;


p.176
create database link CASE_STAT
connect to sys identified by only_for_a_minute
using 'case';


p.176-179
rem
rem  $ORACLE_HOME/rdbms/admin/utlbstat.sqlɕύX{́B
rem  tablespace储уf[^x[XNǉĂ_ɒӁB
rem
rem *************************************************************
rem                 ŏɂׂĂ̕\쐬
rem *************************************************************

drop table stats$begin_stats;
create table stats$begin_stats
TABLESPACE CC
as select * from v$sysstat@CASE_STAT where 0 = 1;
drop table stats$end_stats;
create table stats$end_stats
TABLESPACE CC
as select * from stats$begin_stats;

drop table stats$begin_latch;
create table stats$begin_latch
TABLESPACE CC
as select * from v$latch@CASE_STAT where 0 = 1;

drop table stats$end_latch;
create table stats$end_latch
TABLESPACE CC
as select * from stats$begin_latch;

drop table stats$begin_roll;
create table stats$begin_roll
TABLESPACE CC
as select * from v$rollstat@CASE_STAT where 0 = 1;

drop table stats$end_roll;
create table stats$end_roll
TABLESPACE CC
as select * from stats$begin_roll;

drop table stats$begin_lib;
create table stats$begin_lib
TABLESPACE CC
as select * from v$librarycache@CASE_STAT where 0 = 1;

drop table stats$end_lib;
create table stats$end_lib
TABLESPACE CC
as select * from stats$begin_lib;

drop table stats$begin_dc;
create table stats$begin_dc
TABLESPACE CC
as select * from v$rowcache@CASE_STAT where 0 = 1;

drop table stats$end_dc;
create table stats$end_dc
TABLESPACE CC
as select * from stats$begin_dc;

drop table stats$begin_event;
create table stats$begin_event
TABLESPACE CC
as select * from v$system_event@CASE_STAT where 0 = 1;

drop table stats$end_event;
create table stats$end_event
TABLESPACE CC
as select * from stats$begin_event;

drop table stats$begin_bck_event;
create table stats$begin_bck_event
  (event varchar2(200),
   total_waits number,
   time_waited number)
TABLESPACE CC;
drop table stats$end_bck_event;
create table stats$end_bck_event
as select * from stats$begin_bck_event;

drop table stats$dates;
create table stats$dates (stats_gather_times varchar2(100))
TABLESPACE CC;

drop view stats$file_view;
create view stats$file_view
as                 /*:  FROMύX邱*/
  select ts.name    ts,
         i.name     name,
         x.phyrds pyr,
         x.phywrts pyw,
         x.readtim prt,
         x.writetim pwt,
         x.phyblkrd pbr,
         x.phyblkwrt pbw,
         ROUND(i.bytes/1048576) megabytes_size
  from   v$filestat@CASE_STAT x,
         ts$@CASE_STAT ts,
         v$datafile@CASE_STAT i,
         file$@@CASE_STAT f
 where i.file#=f.file#
   and ts.ts#=f.ts#
   and x.file#=f.file#;

drop table stats$begin_file;
create table stats$begin_file   /*ł́ANsv*/
TABLESPACE CC
as select * from stats$file_view where 0 = 1;

drop table stats$end_file;
create table stats$end_file
TABLESPACE CC
as select * from stats$begin_file;

drop table stats$begin_waitstat;
create table stats$begin_waitstat 
TABLESPACE CC
as select * from v$waitstat@CASE_STAT where 1=0;
drop table stats$end_waitstat;
create table stats$end_waitstat 
TABLESPACE CC
as select * from stats$begin_waitstat;


p.179
ROUND(i.bytes/1000000) megabytes_size


ROUND(i.bytes/1048576) megabytes_size


insert into stats$end_latch select * from v$latch@CASE_STAT;
insert into stats$end_stats select * from v$sysstat@CASE_STAT;
insert into stats$end_lib select * from v$librarycache@CASE_STAT;
update stats$dates set end_time = SysDate;
insert into stats$end_event select * from v$system_event@CASE_STAT;
insert into stats$end_bck_event
  select event, sum(total_waits), sum(time_waited)
    from v$session@CASE_STAT s, v$session_event@CASE_STAT e
    where type = 'BACKGROUND' and s.sid = e.sid
    group by event;
insert into stats$end_waitstat select * from v$waitstat@CASE_STAT;
insert into stats$end_roll select * from v$rollstat@CASE_STAT;
insert into stats$end_file select * from stats$file_view; /*no link*/
insert into stats$end_dc select * from v$rowcache@CASE_STAT;


p.180-182
create table stats$stats
TABLESPACE CC
as select  e.value-b.value change , n.name
   from v$statname n ,  stats$begin_stats b , stats$end_stats e
   where n.statistic# = b.statistic# and n.statistic# = e.statistic#;

create table stats$latches
TABLESPACE CC
as select e.gets-b.gets gets,
   e.misses-b.misses misses,
   e.sleeps-b.sleeps sleeps,
   e.immediate_gets-b.immediate_gets immed_gets,
   e.immediate_misses-b.immediate_misses immed_miss,
   n.name
   from v$latchname n ,  stats$begin_latch b , stats$end_latch e
   where n.latch# = b.latch# and n.latch# = e.latch#;

create table stats$event
TABLESPACE CC
as select  e.total_waits-b.total_waits event_count,
          e.time_waited-b.time_waited time_waited,
          e.event
    from  stats$begin_event b , stats$end_event e
    where b.event = e.event
  union
  select  e.total_waits event_count,
          e.time_waited time_waited,
          e.event
    from  stats$end_event e
    where e.event not in (select b.event from stats$begin_event b);

create table stats$bck_event tablespace CC_ as
  select  e.total_waits-b.total_waits event_count,
          e.time_waited-b.time_waited time_waited,
          e.event
    from  stats$begin_bck_event b , stats$end_bck_event e
    where b.event = e.event
  union all
  select  e.total_waits event_count,
          e.time_waited time_waited,
          e.event
    from  stats$end_bck_event e
    where e.event not in (select b.event from stats$begin_bck_event b);

update stats$event e
  set (event_count, time_waited) = 
    (select e.event_count - b.event_count,
            e.time_waited - b.time_waited
      from stats$bck_event b
         where e.event = b.event)
   where e.event in (select b.event from stats$bck_event b);
create table stats$waitstat as
select  e.class,
        e.count - b.count count,
        e.time - b.time time
  from stats$begin_waitstat b, stats$end_waitstat e
   where e.class = b.class;

create table stats$roll
TABLESPACE CC
as select  e.usn undo_segment,
        e.gets-b.gets trans_tbl_gets,
   e.waits-b.waits trans_tbl_waits,
   e.writes-b.writes undo_bytes_written,
   e.rssize segment_size_bytes,
        e.xacts-b.xacts xacts,
   e.shrinks-b.shrinks shrinks,
        e.wraps-b.wraps wraps
   from stats$begin_roll b, stats$end_roll e
        where e.usn = b.usn;

create table stats$files
TABLESPACE CC
as select b.ts table_space,
       b.name file_name,
       e.pyr-b.pyr phys_reads,
       e.pbr-b.pbr phys_blks_rd,
       e.prt-b.prt phys_rd_time,
       e.pyw-b.pyw phys_writes,
       e.pbw-b.pbw phys_blks_wr,
       e.pwt-b.pwt phys_wrt_tim,
       e.megabytes_size
  from stats$begin_file b, stats$end_file e
       where b.name=e.name;

create table stats$dc
TABLESPACE CC
as select b.parameter name,
       e.gets-b.gets get_reqs,
       e.getmisses-b.getmisses get_miss,
       e.scans-b.scans scan_reqs,
       e.scanmisses-b.scanmisses scan_miss,
       e.modifications-b.modifications mod_reqs,
       e.count count,
       e.usage cur_usage
  from stats$begin_dc b, stats$end_dc e
       where b.cache#=e.cache#
        and  nvl(b.subordinate#,-1) = nvl(e.subordinate#,-1);

create table stats$lib
TABLESPACE CC
as select e.namespace,
       e.gets-b.gets gets,
       e.gethits-b.gethits gethits,
       e.pins-b.pins pins,
       e.pinhits-b.pinhits pinhits,
       e.reloads - b.reloads reloads,
       e.invalidations - b.invalidations invalidations
  from stats$begin_lib b, stats$end_lib e
       where b.namespace = e.namespace;



Chapter7
p.186
select Name from V$ROLLNAME
where USN = &UNDO_SEGMENT;


p.188
clear columns
clear breaks
column Drive format A5
column File_Name format A30
column Blocks_Read format 99999999
column Blocks_Written format 99999999
column Total_IOs format 99999999
set linesize 80 pagesize 60 newpage 0 feedback off
ttitle skip center "Database File I/O Information" skip 2
break on report
compute sum of Blocks_Read on report
compute sum of Blocks_Written on report
compute sum of Total_IOs on report

select substr(DF.Name,1,5) Drive,
       SUM(FS.Phyblkrd+FS.Phyblkwrt) Total_IOs,
       SUM(FS.Phyblkrd) Blocks_Read,
       SUM(FS.Phyblkwrt) Blocks_Written
  from V$FILESTAT FS, V$DATAFILE DF
 where DF.File#=FS.File#
 group by substr(DF.Name,1,5)
 order by Total_IOs desc;


p.189
DRIVE TOTAL_IOS BLOCKS_READ BLOCKS_WRITTEN
----- --------- ----------- --------------
/db03     57217       56820            397
/db01     39940       27712           6228
/db04     15759       14728           1031
/db02      1898          10           1888
      --------- ----------- --------------
sum      108814       99270           9544


p.189
clear breaks
clear computes
break on Drive skip 1 on report
compute sum of Blocks_Read on Drive
compute sum of Blocks_Written on Drive
compute sum of Total_IOs on Drive
compute sum of Blocks_Read on Report
compute sum of Blocks_Written on Report
compute sum of Total_IOs on Report
ttitle skip center "Database File I/O by Drive" skip 2

select substr(DF.Name,1,5) Drive,
       DF.Name File_Name,
       FS.Phyblkrd+FS.Phyblkwrt Total_IOs,
       FS.Phyblkrd Blocks_Read,
       FS.Phyblkwrt Blocks_Written
  from V$FILESTAT FS, V$DATAFILE DF
 where DF.File#=FS.File#
 order by Drive, File_Name desc;


p.189-190
DRIVE FILE_NAME                      TOTAL_IOS BLOCKS_READ BLOCKS_WRITTEN
----- ------------------------------ --------- ----------- --------------
/db01 /db01/oracle/CC1/sys.dbf           29551       27708           1843
      /db01/oracle/CC1/temp.dbf           4389           4           4385
*****                                --------- ----------- --------------
sum                                      33940       27712           6228

/db02 /db02/oracle/CC1/rbs01.dbf          1134           3           1131
      /db02/oracle/CC1/rbs02.dbf           349                        349
      /db02/oracle/CC1/rbs03.dbf           415           7            408
*****                                --------- ----------- --------------
sum                                       1898          10           1888

/db03 /db03/oracle/CC1/cc.dbf            57217       56820            397
*****                                --------- ----------- --------------
sum                                      57217       56820            397

/db04 /db04/oracle/CC1/ccindx.dbf        15759       14728           1031
      /db04/oracle/CC1/tests01.dbf                                       
*****                                --------- ----------- --------------
sum                                      15759       14728           1031

*****                                --------- ----------- --------------
sum                                     108814       99270           9544


p.191-192
rem
rem  t@C:  over_extended.sql
rem  p[^:  f[^x[XNiCX^Xj, t@N^
rem
rem  "t@N^"̒ĺA1傫ƁB
rem  :  GNXeǧ̍ő20%ȓɂ邩ǂ
rem  ׂꍇ́A1.2Ƃt@N^gpB
rem
rem  Ăяo̗:
rem  @over_extended CASE 1.2
rem

select
   Owner,                  /*ZOg̏L*/
   Segment_Name,           /*ZOg̖O*/
   Segment_Type,           /*ZOg̃^Cv*/
   Extents,                /*擾ς݂̃GNXeg*/
   Blocks                  /*擾ς݂̃ubN*/
from DBA_SEGMENTS@&&1 s
where                      /*NX^ZOg̏ꍇ*/
(S.Segment_Type = 'CLUSTER' and exists
(select 'x' from DBA_CLUSTERS@&&1 c
where C.Owner = S.Owner
and C.Cluster_Name = S.Segment_Name
and C.Max_Extents <= S.Extents*&&2))
or                         /*\ZOg̏ꍇ*/
(s.segment_type = 'TABLE' and exists
(select 'x' from DBA_TABLES@&&1 t
where T.Owner = S.Owner
and T.Table_Name = S.Segment_Name
and T.Max_Extents <= S.Extents*&&2))
or                         /*ZOg̏ꍇ*/
(S.Segment_Type = 'INDEX' and exists
(select 'x' from DBA_INDEXES@&&1 i
where I.Owner = S.Owner
and I.Index_Name = S.Segment_Name
and I.Max_Extents <= S.Extents*&&2))
or                         /*[obNZOg̏ꍇ*/
(S.Segment_Type = 'ROLLBACK' and exists
(select 'x' from DBA_ROLLBACK_SEGS@&&1 r
where R.Owner = S.Owner
and R.Segment_Name = S.Segment_Name
and R.Max_Extents <= S.Extents*&&2))
order by 1,2

spool &&1._over_extended.lst
/
spool off
undefine 1
undefine 2



CHapter7
p.200
alter rollback segment SEGMENT_NAME offline;


drop rollback segment SEGMENT_NAME;


create rollback segment SEGMENT_NAME
tablespace RBS;


p.201
alter rollback segment SEGMENT_NAME online;


rollback_segments    = (r0,r1,r2)


p.201-202
commit;

set transaction use rollback segment ROLL_BATCH
insert into TABLE_NAME
select * from DATA_LOAD_TABLE;

commit;

REM*  LcommitR}hɂA[obNZOg̊
REM*  w͉B[obNZOg̊ĎẃADDL
REM*  R}hȂǂɂÖٓIȃR~bgłB

insert into TABLE_NAME select * from SOME_OTHER_TABLE;


p.204
ORA-1555:  snapshot too old (rollback segment too small)


p.208
select * from DBA_SEGMENTS
where Segment_Type = 'ROLLBACK';


select
     N.Name,             /* [obNZOg̖O */
     S.OptSize           /* [obNZOgOPTIMALTCY */
from V$ROLLNAME N, V$ROLLSTAT S
where N.USN=S.USN;


p.209
alter rollback segment R1 shrink to 15M;

alter rollback segment R1 shrink;


p.212
DROP TABLE stats$begin_roll;
CREATE TABLE stats$begin_roll
AS SELECT * FROM v$rollstat WHERE 0 = 1;

DROP TABLE stats$end_roll;
CREATE TABLE stats$end_roll
AS SELECT * FROM stats$begin_roll;

INSERT INTO stats$begin_roll SELECT * FROM v$rollstat;


INSERT INTO stats$end_roll SELECT * FROM v$rollstat;

CREATE TABLE stats$roll
AS SELECT  e.usn undo_segment,
        e.gets-b.gets trans_tbl_gets,
   e.waits-b.waits trans_tbl_waits,
   e.writes-b.writes undo_bytes_written,
   e.rssize segment_size_bytes,
        e.xacts-b.xacts xacts,
   e.shrinks-b.shrinks shrinks,
        e.wraps-b.wraps wraps
   FROM stats$begin_roll b, stats$end_roll e
        WHERE e.usn = b.usn;


e.xacts-b.xacts xacts,


p.213
e.xacts        xacts,


p.214
select
   N.Name,                      /* [obNZOg̖O */
   S.RsSize                     /* [obNZOg̃TCY */
from V$ROLLNAME N, V$ROLLSTAT S
where N.USN=S.USN;


p.215
REM  [obNZOg̃[U[
REM
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
select R.Name rr,
       nvl(S.Username,'no transaction') us,
       S.Osuser os,
       S.Terminal te
  from V$LOCK L, V$SESSION S, V$ROLLNAME R
 where L.Sid = S.Sid(+)
   and trunc(L.Id1/65536) = R.USN
   and L.Type = 'TX'
   and L.Lmode = 6
order by R.Name
/


RB Segment         Username        OS User    Terminal
------------------ --------------- ---------- ----------
R01                APPL1_BAT       georgehj   ttypc
R02                APPL1_BAT       detmerst   ttypb


p.216
select
   N.Name,                      /* [obNZOg̖O */
   S.Writes                     /* _ŏݍς݂̃oCg */
from V$ROLLNAME N, V$ROLLSTAT S
where N.USN=S.USN;


set transaction use rollback segment SEGMENT_NAME


p.219
ŏTCYiMinimum Total SizeFMTSj=Sum(Gg̑TCY)*100/
                                  (100-((󂫗̈%)+(IIU%)+(wb_[%)))
                                       =Sum(Gg̑TCY)*100/100-(20+15+5))
                                       =Sum(Gg̑TCY)*100/60
                                       =Sum(Gg̑TCY)*1.67
                                       =2710KB*1.67
                                       =4525KB


p.220
[obNZOg̍ŏiMinimum Num of Rollback SegsFMNRSj=ŏTCY/
                                                                       ŏ\TCY
                                                                      =4525KB/1170KB
                                                                      =3.87i؂グ4j


[obNZOg̍ő=ɎsgUNV̌
                                =35


MTS = 1510K * 1.67  = 2522 K


p.221
ŏGNXegTCYiMinimum Extent SizeFMESj=70KB


p.224
1ZOg̃GNXeg̍ŏ=(PGNXeg̃gUNV)+(((gUNVɂ郉bv̉)+1)*(gUNV̕ό))


1ZOg̃GNXeg̍ŏ =(33/6)+(5+1)*1
                                          =5.6+6
                                          =11.6i؂グ12j


p.225
[obNZOg̃I[o[wbh=([obNZOg̃wb_[̗̈)+(uANeBuŎgpv̗̈)+(󂫗̈)


IIÜ̗ =(uANeBuŎgpṽp[Ze[W)*
               (f[^GNXeǧ)
          =.15*12
          =1.80i؂グ2GNXegj


p.226
󂫗̈̃GNXeg=]ɕKvȃGNXeg̍ő
                        =3


optimal	=((1ZOg̃f[^GNXeg̍ŏ)
              +([obNZOg̃wb_[̃GNXeg)
              +(uANeBuŎgpṽGNXeg)
              +(󂫗̈̃GNXeg))
           *(GNXegTCY)
        =(12+1+2+3)*125K/GNXeg
        =18*125K/GNXeg
        =2250KB


p.227
alter tablespace RBS
default storage
(initial 125K next 125K minextents 18 maxextents 249)


create rollback segment R4 tablespace RBS
   storage (optimal 2250K);
alter rollback segment R4 online;


p.228
set transaction use rollback segment SEGMENT_NAME



Chapter8
p.236
create procedure MY_RAISE (My_Emp_No IN NUMBER, Raise IN NUMBER)
as begin
      update EMPLOYEE
         set Salary = Salary+Raise
       where Empno = My_Emp_No;
end;
/


alter procedure MY_RAISE compile;


p.237
create index CITY_ST_ZIP_NDX
on EMPLOYEE(City, State, Zip)
tablespace INDEXES;


select * from EMPLOYEE
 where State='NJ';


p.238
select * from EMPLOYEE
where Empno between 1 AND 100;


p.239
explain plan
set Statement_Id = 'TEST'
for
select * from EMPLOYEE
where City > 'Y%';


select
  LPAD(' ',2*Level)||Operation||' '||Options||' '||Object_Name
   Q_Plan
  from PLAN_TABLE
 where Statement_Id = 'TEST'
connect by prior ID = Parent_ID and Statement_ID = 'TEST'
 start with ID=0;


Q_PLAN
-------------------------------------------------------
SELECT STATEMENT
  TABLE ACCESS BY ROWID EMPLOYEE
    INDEX RANGE SCAN CITY_ST_ZIP_NDX


p.241
qbg=iLogical Readsi_Ǎ݂̉񐔁j-Physical ReadsiǍ݂̉񐔁jj/
          Logical Readsi_Ǎ݂̉񐔁j


p.243
analyze table COMPANY compute statistics;

analyze table COMPANY compute statistics for table 
    for all indexed columns;


execute DBMS_UTILITY.ANALYZE_SCHEMA('APPOWNER','COMPUTE');


p.245
select
      Tablespace_Name,   /*\̖̈O*/
      Owner,             /*ZOg̏L*/
      Segment_Name,      /*ZOg*/
      Segment_Type,      /*ZOg̃^CviTABLEINDEXȂǁj*/
      Extents,           /*ZOg̃GNXeǧ*/
      Blocks,            /*ZOgDBubŇ*/
      Bytes              /*ZOg̃oCg*/
from DBA_SEGMENTS
/


p.246
select
      Tablespace_Name,   /*\̖̈O*/
      Owner,             /*ZOg̏L*/
      Segment_Name,      /*ZOg*/
      Segment_Type,      /*ZOg̃^CviTABLEINDEXȂǁj*/
      Extent_ID,         /*ZOg̃GNXegԍ*/
      Block_ID,          /*YGNXeg̊JnubNԍ*/
      Bytes,             /*GNXeg̃TCYioCgj*/
      Blocks             /*GNXeg̃TCYiOracleubNj*/
 from DBA_EXTENTS
where Segment_Name = 'segment_name'
order by Extent_ID;


p.247
exp system/manager file=exp.dmp compress=Y grants=Y indexes=Y
     tables=(HR.T1,HR.T2)


imp system/manager file=exp.dmp commit=Y buffer=64000 full=Y


p.249
                     @őGNXeg               1
 FSFI  =  100* sqrt(-----------------) * -------------------
                     SGNXeg̍v     (GNXeǧ)^1/4


rem
rem  t@C: fsfi.sql
rem  i[ꏊ: /orasw/dba/contig
rem
rem  ̃XNvgł́A1̃f[^x[Xׂ̂Ă
rem  \̈ɂ󂫗̈̒fЉvBāA
rem  Tv̎wWɏ]č̓_Arp̒l߂B
rem
set newpage 0 pagesize 60
column fsfi format 999.99
select
      Tablespace_Name,
      SQRT(MAX(Blocks)/SUM(Blocks))*
      (100/SQRT(SQRT(COUNT(Blocks)))) Fsfi
from DBA_FREE_SPACE
group by
      Tablespace_Name
order by 1

spool fsfi.lis
/
spool off


p.250-251
rem
rem   t@C: mapper.sql
rem   i[ꏊ: /orasw/dba/contig
rem   p[^: ̑ΏۂƂȂ\̖̈O
rem
rem   N@ij:
rem   @mapper DEMODATA
rem
rem   ̃XNvǵA1̕\̗̈̒̈̎gp
rem   i󂫁^gpj𐶐BZOgы󂫗̈
rem   fЉOtBJɏo͂B
rem
set pagesize 60 linesize 132 verify off
column file_id heading "File|Id"

select
      'free space' Owner,    /*󂫗̈́uLҁv*/
      '   ' Object,          /*󔒂̃IuWFNg*/
      File_ID,               /*GNXegwb_[̃t@CID*/
      Block_ID,              /*GNXegwb_[̃ubNID*/
      Blocks                 /*GNXeg̒iubNj*/
 from DBA_FREE_SPACE
where Tablespace_Name = UPPER('&&1')
union
select
      SUBSTR(Owner,1,20),         /*L҂̖Oiŏ20j*/
      SUBSTR(Segment_Name,1,32),  /*ZOg*/
      File_ID,                    /*GNXegwb_[̃t@CID*/
      Block_ID,                   /*ubNwb_[̃ubNID*/
      Blocks                    /*GNXeg̒iubNj*/
 from DBA_EXTENTS
where Tablespace_Name = UPPER('&&1')
order by 3,4

spool &&1._map.lst
/
spool off
undefine 1


p.252
alter tablespace DEMONDX 
default storage (pctincrease 1);


alter tablespace DEMONDX coalesce;


p.253
analyze table TABLE_NAME list chained rows into CHAINED_ROWS;


select
       Owner_Name,      /*f[^ZOg̏L*/
       Table_Name,      /*Aŝ\̖O*/
       Cluster_Name,    /*NX^̖OiNX^Ăꍇj*/
       Head_RowID       /*s擪ɂrowid*/
from CHAINED_ROWS;


p.255
REENABLE DISABLED_CONSTRAINTS


sqlload USERID=ME/PASS CONTROL=PART1.CTL DIRECT=TRUE PARALLEL=TRUE
sqlload USERID=ME/PASS CONTROL=PART2.CTL DIRECT=TRUE PARALLEL=TRUE
sqlload USERID=ME/PASS CONTROL=PART3.CTL DIRECT=TRUE PARALLEL=TRUE


p.258
truncate table EMPLOYEE drop storage;


truncate cluster EMP_DEPT reuse storage;


alter table EMPLOYEE
truncate partition PART3
drop storage;


p.262
set copycommit 1
set arraysize 1000
copy from HR/PUFFINSTUFF@loc -
create EMPLOYEE -
using -
select * from EMPLOYEE -
where State = 'NM'


p.264
create database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'loc';


p.265
select * from EMPLOYEE@HR_LINK;


p.266
select DEPT, MAX(Salary)
  from EMPLOYEE@HR_LINK
 group by DEPT;


create snapshot LOCAL_EMP
pctfree 5
tablespace data_2
storage (initial 100K next 100K pctincrease 0)
refresh fast
      start with SysDate
      next SysDate+7
as select * from EMPLOYEE@HR_LINK;


create snapshot log on EMPLOYEE
tablespace DATA
storage (initial 10K next 10K pctincrease 0);


p.268
create procedure MY_RAISE (My_Emp_No IN NUMBER, Raise IN NUMBER)
as begin
      update EMPLOYEE@HR_LINK
      set Salary = Salary+Raise
      where Empno = My_Emp_No;
end;
/


p.269
execute MY_RAISE@HR_LINK(1234,2000);


p.270
create synonym MY_RAISE for MY_RAISE@HR_LINK;


execute MY_RAISE(1234,2000);



Chapter9
p.273
grant select on EMPLOYEE to PUBLIC;


p.274
create user THUMPER
identified by RABBIT
default tablespace USERS
temporary tablespace TEMP;


p.275
alter user THUMPER
quota 100M on USERS;


p.276
drop user THUMPER cascade;


p.280
create role ACCOUNT_CREATOR;
grant CREATE SESSION, CREATE USER, ALTER USER 
   to ACCOUNT_CREATOR;


p.281
alter user THUMPER default role NONE;


alter user THUMPER default role CONNECT;


alter user THUMPER default role all except ACCOUNT_CREATOR;


p.283
alter profile DEFAULT limit
idle_time 60;


create profile LIMITED_PROFILE limit
FAILED_LOGIN_ATTEMPTS 5;

create user JANE identified by EYRE
profile LIMITED_PROFILE;

grant CREATE SESSION to JANE;


connect jane/eyre
ERROR: ORA-28000: the account is locked


p.284
alter user JANE account unlock;


alter user JANE account lock;


alter profile LIMITED_PROFILE limit
PASSWORD_LIFE_TIME 30;


alter user jane password expire;

User altered.


p.285
connect jane/eyre
ERROR: ORA-28001: the account has expired

Changing password for jane
Old password:
New password:
Retype new password:
Password changed
Connected.
SQL>


alter profile LIMITED_PROFILE limit
PASSWORD_REUSE_MAX 3
PASSWORD_REUSE_TIME UNLIMITED;


p.286
alter user JANE identified by austen;


alter user JANE identified by eyre;


alter user jane identified by austen;
alter user jane identified by austen
*
ERROR at line 1:
ORA-28007: the password cannot be reused


p.287-290
Rem utlpwdmg.sql
Rem
Rem  Copyright (c) Oracle Corporation 1996. All Rights Reserved.
Rem
Rem    NAME
Rem      utlpwdmg.sql - script for Default Password Resource Limits
Rem
Rem    DESCRIPTION
Rem      This is a script for enabling the password management features
Rem      by setting the default password resource limits.
Rem
Rem    NOTES
Rem      This file contains a function for minimum checking of password
Rem      complexity. This is more of a sample function that the customer
Rem      can use to develop the function for actual complexity checks
Rem      that the customer wants to make on the new password.
Rem
Rem    asurpur     12/12/96 - Changing the name of
Rem    password_verify_function
-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/<password> as sysdba before running the script

CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS 
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);
BEGIN 
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&()''*+,-/:;<=>?_';

   -- Check if the password is same as the username
   IF password = username THEN
     raise_application_error(-20001, 'Password same as user');
   END IF;

   -- Check for the minimum length of the password
   IF length(password) < 4 THEN
      raise_application_error(-20002, 'Password length less than 4');
   END IF;
   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF password IN ('welcome', 'password', 'oracle', 'computer', 'abcd') THEN
      raise_application_error(-20002, 'Password too simple');
   END IF;
  
   -- Check if the password contains at least one letter, one digit and one
   -- punctuation mark.
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP 
       FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one digit,
one character and one punctuation');
   END IF;
   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO findpunct;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one \
              digit, one character and one punctuation');
   END IF;
   -- 3. Check for the punctuation
   <<findpunct>>
   ispunct:=FALSE;
   FOR i IN 1..length(punctarray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(punctarray,i,1) THEN
            ispunct:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ispunct = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one \
              digit, one character and one punctuation');
   END IF;

   <<endsearch>>
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password = '' THEN
      raise_application_error(-20004, 'Old password is null');
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
   differ := length(old_password) - length(password);

   IF abs(differ) < 3 THEN
      IF length(password) < length(old_password) THEN
         m := length(password);
      ELSE
         m := length(old_password);
      END IF;
      differ := abs(differ);
      FOR i IN 1..m LOOP
          IF substr(password,i,1) != substr(old_password,i,1) THEN
             differ := differ + 1;
          END IF;
      END LOOP;
      IF differ < 3 THEN
          raise_application_error(-20004, 'Password should differ by at \
            least 3 characters');
      END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/
-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;


p.291
alter profile DEFAULT limit
PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;


p.292
> sqlplus /


p.293
create user OPS$FARMER
identified by SOME_PASSWORD
default tablespace USERS
temporary tablespace TEMP;


> sqlplus ops$farmer/some_password


create user OPS$FARMER
identified externally
default tablespace USERS
temporary tablespace TEMP;


p.294
create user THUMPER
identified by RABBIT;


alter user THUMPER identified by NEWPASSWORD;


password


password JANE


alter user USERNAME identified by NEWPASSWORD;


p.295
create role ACCOUNT_CREATOR identified by HELPDESK_ONLY;


alter role ACCOUNT_CREATOR not identified;


alter role MANAGER identified externally;


ora_local_manager_d:NONE:1:dora


p.296
ora_local_manager_d:NONE:1:dora,judy


grant select, update (Employee_Name, Address)
on EMPLOYEE to MCGREGOR
with grant option;

connect MCGREGOR/FARMER
grant select on THUMPER.EMPLOYEE to JFISHER;


p.297
create role APPLICATION_USER;
grant CREATE SESSION to APPLICATION_USER;

create role DATA_ENTRY_CLERK;
grant select, insert on THUMPER.EMPLOYEE to DATA_ENTRY_CLERK;
grant select, insert on THUMPER.TIME_CARDS to DATA_ENTRY_CLERK;
grant select, insert on THUMPER.DEPARTMENT to DATA_ENTRY_CLERK;


p.298
grant APPLICATION_USER to DATA_ENTRY_CLERK;


grant DATA_ENTRY_CLERK to MCGREGOR;


grant DATA_ENTRY_CLERK to BPOTTER with admin option;


set role DATA_ENTRY_CLERK;


p.299
set role NONE;


revoke delete on EMPLOYEE from PETER;
revoke all on EMPLOYEE from MCGREGOR;


revoke ACCOUNT_CREATOR from HELPDESK;


drop user USERNAME cascade;


select
      Role,             /*[̖O*/
      Privilege,        /*VXe*/
      Admin_Option      /*admin option̕t^*/
 from ROLE_SYS_PRIVS;


p.300
select
      Grantee,           /*t^*/
      Owner,             /*IuWFNg̏L*/
      Table_Name,        /*IuWFNg̖O*/
      Grantor,           /*t^̃[U[*/
      Privilege,         /*t^*/
      Grantable          /*ADMIN OPTION̕t^*/
 from DBA_TAB_PRIVS;


select
      DBA_ROLE_PRIVS.Grantee,        /*t^*/
      ROLE_TAB_PRIVS.Owner,          /*IuWFNg̏L*/
      ROLE_TAB_PRIVS.Table_Name,     /*IuWFNg̖O*/
      ROLE_TAB_PRIVS.Privilege,      /*t^*/
      ROLE_TAB_PRIVS.Grantable       /*admin option̕t^*/
 from DBA_ROLE_PRIVS, ROLE_TAB_PRIVS
where DBA_ROLE_PRIVS.Granted_Role = ROLE_TAB_PRIVS.Role
  and DBA_ROLE_PRIVS.Grantee = 'some username';


p.302
SQL> host
invalid command: host
SQL> connect system/manager
invalid command: connect


p.304
select
      Username,         /*[U[*/
      Password          /*Í̃pX[h*/
from DBA_USERS
where Username in ('MCGREGOR','THUMPER','OPS$FARMER');

USERNAME         PASSWORD
---------------- ----------------
MCGREGOR         1A2DD3CCEE354DFA
THUMPER          F3DE41CBB3AB4452
OPS$FARMER       4FF2FF1CBDE11332


create user MCGREGOR identified by VALUES '1A2DD3CCEE354DFA';


p.305
alter user OPS$FARMER identified by VALUES 'no way';

select
      Username,         /*[U[*/
      Password          /*Í̃pX[h*/
from DBA_USERS
where Username in ('MCGREGOR','THUMPER','OPS$FARMER');

USERNAME         PASSWORD
---------------- ----------------
MCGREGOR         1A2DD3CCEE354DFA
THUMPER          F3DE41CBB3AB4452
OPS$FARMER       no way


p.306
REM*  become_another_user.sql
REM*
REM*  ̃XNvgł́Aʂ̃[U[ɈꎞIɂȂ肷܂
REM*  ߂̃R}h𐶐B
REM*
REM*  ̃XNvǵADBÃAJEgs邱ƁB
REM*
REM*  ׂ͂: ړĨAJEg̃[U[
REM*
REM*
REM*  Xebv1`3: DBA_USERSɑ΂Ė⍇sB
REM*  pX[hslɖ߂̂ɕKvALTER USER
REM*  R}h𐶐B
REM*
set pagesize 0 feedback off verify off echo off termout off
REM*
REM*  R}hi[邽߂̃t@Creset.sql
REM*  쐬B
REM*
spool reset.sql
REM*
REM*  DBA_USERSAÍꂽpX[hIB
REM*
SELECT 'alter user &&1 identified by values '||''''||
password||''''||';'
FROM dba_users WHERE username = upper('&&1');

prompt 'host rm -f reset.sql'
prompt 'exit'
spool off
exit


p.307
alter user MCGREGOR identified by values '1A2DD3CCEE354DFA';
host rm -f reset.sql
exit


alter user MCGREGOR identified by MY_TURN;
connect MCGREGOR/MY_TURN


sqlplus system/manager @reset


p.308
audit session;


audit session whenever successful;
audit session whenever not successful;


p.309
select
  OS_Username,           /*gpIy[eBOVXẽ[U[*/
  Username,              /*gpAJEgOracle[U[*/
  Terminal,              /*gp[ID*/
  DECODE(Returncode,'0','Connected',
              '1005','FailedNull',
              '1017','Failed',Returncode),      /*G[̃`FbN*/
  TO_CHAR(Timestamp,'DD-MON-YY HH24:MI:SS'),    /*OC*/
  TO_CHAR(Logoff_Time,'DD-MON-YY HH24:MI:SS')   /*OIt*/
from DBA_AUDIT_SESSION;


noaudit session;


p.310
audit role;


noaudit role;


select
      Action,       /*ANVR[h*/
      Name          /*ANV̖OiALTER USERȂǁj*/
from AUDIT_ACTIONS;


p.311-312
select
  OS_Username,           /*gpIy[eBOVXẽ[U[*/
  Username,              /*gpAJEgOracle[U[*/
  Terminal,              /*gp[ID*/
  Owner,                /*e󂯂IuWFNg̏L*/
  Obj_Name,             /*e󂯂IuWFNg*/
  Action_Name,          /*ANV̐lR[h*/
  DECODE(Returncode,'0','Success',Returncode),   /*G[̃`FbN*/
  TO_CHAR(Timestamp,'DD-MON-YY HH24:MI:SS')      /*^CX^v*/
from DBA_AUDIT_OBJECT;


p.312
audit update table by MCGREGOR;


audit insert on THUMPER.EMPLOYEE;
audit all on THUMPER.TIME_CARDS;
audit delete on THUMPER.DEPARTMENT by session;


p.313
audit all on SYS.AUD$ by access;



Chapter10
p.320
exp help=Y


p.323
exp system/manager file=expdat.dmp compress=Y owner=(HR,THUMPER)


p.326-327
rem
rem   user_tablespace_maps.sql
rem
rem  ̃XNvǵA[U[IuWFNgƕ\̈̊֌Wo͂B
rem
set pagesize 60
break on Owner on Tablespace
column Objects format A20
select
      Owner,
      Tablespace_Name,
      COUNT(*)||' tables' Objects
 from DBA_TABLES
group by
      Owner,
      Tablespace_Name
union
select
      Owner,
      Tablespace_Name,
      COUNT(*)||' indexes' Objects
from DBA_INDEXES
group by
      Owner,
      Tablespace_Name

spool user_locs.lst
/
spool off


p.327
OWNER         TABLESPACE_NAME   OBJECTS
------------- ----------------- ---------------
FLOWER        USERS             3 tables
                                2 indexes
HR            HR_TABLES         27 tables
              HR_INDEXES        35 indexes
THUMPER       USERS             5 tables


p.327-328
rem
rem   user_tablespace_maps.sql
rem
rem  ̃XNvǵA[U[IuWFNgƕ\̈̊֌Wo͂B
rem
set pagesize 60
break on Tablespace on Owner
column Objects format A20
select
      Tablespace_Name,
      Owner,
      COUNT(*)||' tables' Objects
from DBA_TABLES
group by
      Tablespace_Name,
      Owner
union
select
      Tablespace_Name,
      Owner,
      COUNT(*)||' indexes' Objects
from DBA_INDEXES
group by
      Tablespace_Name,
      Owner

spool ts_locs.lst
/
spool off


p.328
TABLESPACE_NAME   OWNER       OBJECTS
----------------- ----------- -------------
HR_INDEXES        HR          35 indexes
HR_TABLES         HR          27 tables
USERS             FLOWER      3 tables
                              2 indexes
                  THUMPER     5 tables


p.329
exp system/manager file=hr.dmp owner=HR indexes=Y compress=Y


svrmgrl
SVRMGR> connect internal
SVRMGR> drop tablespace HR_INDEXES including contents;
SVRMGR> drop tablespace HR_TABLES including contents;
SVRMGR> create tablespace HR_TABLES...
SVRMGR> create tablespace HR_INDEXES...
SVRMGR> exit

imp system/manager file=hr.dmp full=Y buffer=64000 commit=Y


p.330
rem
rem  third_party_grants.sql
rem
rem  ̖⍇ł́A\̏L҈ȊÕ[U[t^
rem  B̂悤Ȍ́A[U[[h̃GNX|[g
rem  ɁAGNX|[gȂB
rem
break on Grantor skip 1 on Owner on Table_Name
select
      Grantor,          /*̕t^̃AJEg*/
      Owner,            /*\LĂAJEg*/
      Table_Name,       /*\̖O*/
      Grantee,          /*̕t^̃AJEg*/
      Privilege,        /*t^ꂽ*/
      Grantable         /*admin option̕t^*/
from DBA_TAB_PRIVS
where Grantor ! = Owner
order by Grantor, Owner, Table_Name, Grantee, Privilege

spool third_parts_privs.lst
/
spool off


p.330-331
rem
rem  third_party_indexes.sql
rem
rem  ̖⍇ł́A\̏L҈ȊÕ[U[쐬
rem  B
rem
select
      Owner,                /*̏L*/
      Index_Name,           /*̖O*/
      Table_Owner,          /*\̏L*/
      Table_Name            /*tĂ\̖O*/
from DBA_INDEXES
where Owner != Table_Owner

spool third_party_indexes.lst
/
spool off


p.331
exp system/manager FILE=expdat.dmp TABLES=(Thumper.SALES)


exp system/manager FILE=expdat.dmp TABLES=(Thumper.SALES:Part1)


p.335
imp system/manager file=expdat.dmp
imp system/manager file=expdat.dmp buffer=64000 commit=Y


p.336
exp system/manager file=thumper.dat owner=thumper grants=N
  indexes=Y compress=Y rows=Y

imp system/manager file=thumper.dat FROMUSER=thumper TOUSER=flower
      rows=Y indexes=Y


imp system/manager file=expdat.dmp full=Y commit=Y buffer=64000


p.337
imp system/manager file=expdat.dmp ignore=N rows=N commit=Y buffer=64000


p.339
/db01
          /oracle
                    /CASE
                           control1.dbf
                           sys01.dbf
                           tools.dbf
                    /CC1
                           control1.dbf
                           sys01.dbf
                           tools.dbf
                    /DEMO
                           control1.dbf
                           sys01.dbf


> tar -cvf /dev/rmt/0hc /db0[1-9]/oracle/CC1


> tar -rvf /dev/rmt/0hc /orasw/app/oracle/CC1/pfile/initcc1.ora


p.340
ORACLE_SID=cc1; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
. oraenv
svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate;
SVRMGR> exit
utarv̂悤ȃobNAbvp̃R}hɓ
svrmgrl
SVRMGR> connect internal
SVRMGR> startup


set def DB01:[ORASW.DB_instance_name]
@ORAUSER_DB_instance_name


$Backup/ignore=(nobackup,interl)/log file tape1:oracle_backup.bck/sav


p.341
svrmgrl
SVRMGR>connect internal
SVRMGR>startup mount cc1;
SVRMGR>alter database archivelog;
SVRMGR>archive log start;
SVRMGR>alter database open;


archive log list


p.342
svrmgrl
SVRMGR>connect internal
SVRMGR>startup mount cc1;
SVRMGR>alter database noarchivelog;
SVRMGR>alter database open;


log_archive_dest          = /db01/oracle/arch/CC1/arch
log_archive_start         = TRUE


arch_170.dbf
arch_171.dbf
arch_172.dbf


p.343
select Name,
       Value
  from V$PARAMETER
 where Name like 'log_archive%';


p.344-347
#
# zbgobNAbvp̃TṽXNvgiUNIXf[^x[X̏ꍇj
#
# ϐ̐ݒ:
ORACLE_SID=cc1; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
. oraenv
#
#   Xebv1.  \̈悲ƂɃf[^t@C̃obNAbv
#   ̎悷B\̈́A1begin backupԂɂB
#   āAf[^t@C̃obNAbv̎悵A
#   Y\̈ʏ̏Ԃɖ߂B
#
# UNIX̏ꍇ̒ӎ:  R}ht@C
#  Server Managerɂ邽߂ɁA
#  CWP[^iEOFarch1jgpB
#
svrmgrl <<EOFarch1
connect internal
REM
REM   SYSTEM\̈̃obNAbv̎
REM
alter tablespace SYSTEM begin backup;
!tar -cvf /dev/rmt/0hc /db01/oracle/CC1/sys01.dbf
alter tablespace SYSTEM end backup;
REM
REM  ŁASYSTEM\̈e[vfoCX/dev/rmt/0hc
REM   tar̃Z[uZbgɏݍݏIBȍ~tarR}h
REM   ł́u-rvfvw肵ÃZ[uZbg̖Ƀf[^
REM   ǉB
REM
REM   RBS\̈̃obNAbv̎
REM
alter tablespace RBS begin backup;
!tar -rvf /dev/rmt/0hc /db02/oracle/CC1/rbs01.dbf
alter tablespace RBS end backup;
REM
REM   DATA\̈̃obNAbv̎
REM   ̕\̈ɂ́Adata01.dbfdata02.dbfƂ
REM   2̃t@Ci[Ă̂ƂB̂߁A
REM   ChJ[h*gpăt@Cw肷B
REM
alter tablespace DATA begin backup;
!tar -rvf /dev/rmt/0hc /db03/oracle/CC1/data0*.dbf
alter tablespace DATA end backup;
REM
REM   INDEXES\̈̃obNAbv̎
REM
alter tablespace INDEXES begin backup;
!tar -rvf /dev/rmt/0hc /db04/oracle/CC1/indexes01.dbf
alter tablespace INDEXES end backup;
REM
REM   TEMP\̈̃obNAbv̎
REM
alter tablespace TEMP begin backup;
!tar -rvf /dev/rmt/0hc /db05/oracle/CC1/temp01.dbf
alter tablespace TEMP end backup;
REM
REM   c̕\̈́A܂łƓp^[ɏ]
REM   obNAbv̎悷B
REM
REM
REM       Xebv2.  A[JCuRedoOt@C̃obNAbv
REM
REM  ܂AA[JCu~BɂA̎s
REM  ʂ̃A[JCuRedoOt@Ci[̃fBNg
REM  ܂Ȃ悤ɂB
REM
archive log stop
REM
REM   ݒς݂̃CWP[^gpAServer ManagerI
exit
EOFarch1
#
#  i[fBNg̒ɂt@CL^B
#     ̏́Ai[fBNg̃fBNgXg
#  ɓlϐɐݒ肵AsB
#  ̗̏ꍇAlog_archive_dest́A
#  /db01/oracle/arch/CC1B
#
#
FILES=`ls /db01/oracle/arch/CC1/arch*.dbf`; export FILES
#
#  Server Managerɖ߂AA[JCuĊJB
#  CWP[^iEOFarch2jݒ肷B
#
svrmgrl <<EOFarch2
connect internal
archive log start;
exit
EOFarch2
#
#  utarvR}hgăA[JCuRedoÕobNAbv
#  e[vfoCXɊi[AurmvR}hgp
#  i[̃foCXYt@C폜B
#
tar -rvf /dev/rmt/0hc $FILES
rm -f $FILES
#
#      Xebv3.  t@C̃obNAbvfBXNɊi[B
#
svrmgrl <<EOFarch3
connect internal
alter database backup controlfile to
   'db01/oracle/CC1/CC1controlfile.bck';
exit
EOFarch3
#
#  t@C̃obNAbve[vɊi[B
#
tar -rvf /dev/rmt/0hc /db01/oracle/CC1/CC1controlfile.bck
#
#  zbgobNAbvpXNvg̏I


p.349
set pagesize 0

select 
    'alter tablespace '||Tablespace_Name||' begin backup;'
  from DBA_TABLESPACES
 where Status <> 'INVALID'

spool alter_begin.sql
/
spool off


alter tablespace SYSTEM begin backup;
alter tablespace RBS begin backup;
alter tablespace TEMP begin backup;
alter tablespace DATA begin backup;
alter tablespace INDEXES begin backup;


tar -cvf /dev/rmt/0hc /db0[1-9]/oracle/CC1


p.350
set pagesize 0

select 
    'alter tablespace '||Tablespace_Name||' end backup;'
  from DBA_TABLESPACES
 where Status <> 'INVALID'

spool alter_end.sql
/
spool off


p.351
#     Xebv1: A[JCu~BɂA
#     ̎sɕʂ̃A[JCuRedoOt@C
#     i[̃fBNgɏ܂Ȃ悤ɂB
#
svrmgrl <<EOFarch1
connect internal
archive log stop;
REM
REM   ݒς݂̃CWP[^gpAServer ManagerI
exit
EOFarch1
#
#     Xebv2: i[fBNg̒ɂt@C
#  L^B
#     ̏́Ai[fBNg̃fBNgXg
#  ɓlϐɐݒ肵AsB
#  ̗̏ꍇAlog_archive_dest́A
#  /db01/oracle/arch/CC1B
#
FILES=`ls /db01/oracle/arch/CC1/arch*.dbf`; export FILES
#
#     Xebv3: Server Managerɖ߂AA[JCu
#  ĊJBCWP[^iEOFarch2jݒ肷B
#
svrmgrl <<EOFarch2
connect internal
archive log start;
exit
EOFarch2
#
#     Xebv4. utarvR}hgăA[JCuRedoO
#  obNAbve[vfoCXɊi[B
#
tar -rvf /dev/rmt/0hc $FILES
#
#     Xebv5. i[̃fBNgYt@C폜B
#
rm -f $FILES
#
#     A[JCuRedoOt@CobNAbvpXNvg̏I


p.355
REM
REM   RBS\̈̃obNAbv̎|e[vɒڏ
REM
alter tablespace RBS begin backup;
!tar -rvf /dev/rmt/0hc /db02/oracle/CC1/rbs01.dbf
alter tablespace RBS end backup;
REM


REM
REM   RBS\̈̃obNAbv̎|ʂ̃fBXNɏށiUNIXj
REM   @
alter tablespace RBS begin backup;
!cp /db02/oracle/CC1/rbs01.dbf /db10/oracle/CC1/backups
alter tablespace RBS end backup;
REM


REM
REM   RBS\̈̃obNAbv̎|ʂ̃fBXNɏށiVMSj
REM   
alter tablespace RBS begin backup;
!backup/ignore=(no backup,interl) DB01:[ORACLE.CC1]RBS01.DBF DB10:[ORACLE.CC1.BACKUPS]
alter tablespace RBS end backup;
REM


p.356
#
# A[JCuRedoOʂ̃foCXɈړ邽߂̃vV[W
#
svrmgrl <<EOFarch2
connect internal
archive log stop;
!mv /db01/oracle/arch/CC1 /db10/oracle/arch/CC1
archive log start;
exit
EOFarch2
#
# A[JCuRedoOfBNg̈ړ̊


p.358
alter database backup controlfile to trace;



Chapter11
p.371
alter procedure APPOWNER.ADD_CLIENT compile;
execute DBMS_SHARED_POOL.KEEP('APPOWNER.ADD_CLIENT','P');


p.372
select Owner,
       Name,
       Type,
 Source_Size+Code_Size+Parsed_Size+Error_Size  Total_Bytes
  from DBA_OBJECT_SIZE
 where Type in ('PACKAGE BODY','PROCEDURE')
 order by 4 desc;


p.377
    imp system/manager file=export.dmp rows=N
    imp system/manager file=export.dmp full=y buffer=64000
        commit=Y ignore=Y


p.378
create tablespace TEMP_GL
datafile '/db01/oracle/FIN/temp_gl.dbf' size 100m
default storage
(initial 5m next 5m pctincrease 0);

alter user GL temporary tablespace TEMP_GL;


select
   Extents,  /*ZOg̃GNXeǧ*/
   Bytes,    /*ꎞZOg̃oCg*/
   Blocks    /*ꎞZOg̃TCYiOracleubNj*/
from DBA_SEGMENTS
where Segment_Type = 'TEMPORARY'
  and Tablespace_Name = 'TEMP_GL';


p.379
svrmgrl
SVRMGR> connect internal;
SVRMGR> grant select on V$PROCESS to applsys with grant option;
SVRMGR> grant select on V$SESSION to applsys with grant option;
SVRMGR> grant select on V$PROCESS to public;
SVRMGR> grant select on V$SESSION to public;


p.384
_optimizer_undo_changes    = TRUE


p.385
db_block_size    = 4096


p.388
create table EXTRACT_TABLE
as select * from REP_TABLE;


p.390
db_block_size    = 4096


p.391
select Name
  from PROSPECT
 where Name like 'B%'
   and contains (Resume, 'digging') > 0;


p.392
ctxsrv -user ctxsys/ctxsys_pass -personality QDML


column Ser_Name format A32

select Ser_Name,
       Ser_Status,
       Ser_Started_At
from CTX_ALL_SERVERS;


SER_NAME                         SER_STAT SER_START
-------------------------------- -------- ---------
DRSRV_42736                      IDLE     03-AUG-97


execute CTX_ADM.SHUTDOWN;


execute CTX_ADM.SHUTDOWN('DRSRV_42736');


p.393
text_enable = TRUE


p.394
and O$.Name <> 'table name'


p.396
select Owner,         /*̏L*/
       Index_Name,    /*̖O*/
       Status         /*DIRECT LOADVALID*/
  from DBA_INDEXES;



Chapter12
p.401
create tablespace CODES_TABLES
datafile '/u01/oracle/VLDB/codes_tables.dbf'
default storage
  (initial 1M next 1M pctincrease 0 pctfree 2);


p.408
create table EMPLOYEE (
EmpNo          NUMBER(10) primary key,
Name           VARCHAR2(40),
DeptNo         NUMBER(2),
Salary         NUMBER(7,2),
Birth_Date     DATE,
Soc_Sec_Num    VARCHAR2(9),
State_Code     CHAR(2),
constraint FK_DeptNO foreign key (DeptNo)
   references DEPT(DeptNo),
constraint FK_StateCode foreign key (State_Code)
   references State(State_Code),
);


create table EMPLOYEE (
EmpNo          NUMBER(10) primary key,
Name           VARCHAR2(40),
DeptNo         NUMBER(2),
Salary         NUMBER(7,2),
Birth_Date     DATE,
Soc_Sec_Num    VARCHAR2(9),
 constraint FK_DeptNO foreign key (DeptNo)
   references DEPT(DeptNo)
)
partition by range (DeptNo)
 (partition PART1   values less than (11)
   tablespace PART1_TS,
  partition PART2   values less than (21)
   tablespace PART2_TS,
  partition PART3   values less than (31)
   tablespace PART3_TS,
  partition PART4   values less than (MAXVALUE)
   tablespace PART4_TS)
;


p.409
partition by range (DeptNo)


partition PART4   values less than (MAXVALUE)


p.410
select *
  from EMPLOYEE (PART2)
 where DeptNo between 11 and 20;


p.410-411
create index EMPLOYEE_DEPTNO
  on EMPLOYEE(DeptNo)
   local
   (partition PART1
     tablespace PART1_NDX_TS,
    partition PART2
     tablespace PART2_NDX_TS,
    partition PART3
     tablespace PART3_NDX_TS,
    partition PART4
     tablespace PART4_NDX_TS)


p.411
create index EMPLOYEE_DEPTNO
on EMPLOYEE(DeptNo)
 global partition by range (DeptNo)
 (partition PART1   values less than (11)
   tablespace PART1_NDX_TS,
  partition PART2   values less than (21)
   tablespace PART2_NDX_TS,
  partition PART3   values less than (31)
   tablespace PART3_NDX_TS,
  partition PART4   values less than (MAXVALUE)
   tablespace PART4_NDX_TS)
;


p.412-412
alter table [user.]TABLE
  | modify partition PARTITION_NAME
             { storage
             | [logging | nologging] } ...
  | move partition PARTITION_NAME 
             { physical_attributes_clause
             | [logging | nologging]
             | tablespace tablespace
             | parallel parallel_clause} ...
  | add partition [NEW_PARTITION_NAME]
        values less than (value_list)
             { physical_attributes_clause
             | [logging | nologging]
             | tablespace tablespace } ...
  | drop partition PARTITION_NAME
  | truncate partition PARTITION_NAME
             [drop storage | reuse storage]
  | split partition PARTITION_NAME_OLD
              at (value_list)
    [into ( partition [SPLIT_PARTITION_1]
              [storage
              |  [logging | nologging]
              |  tablespace tablespace ] ...
          , partition [SPLIT_PARTITION 2]
              [storage
              |  [logging | nologging]
              |  tablespace tablespace ] ...) ]
              [ parallel parallel_clause ] ...
  | exchange partition PARTITION_NAME
       with table NON_PARTITIONED_TABLE_NAME
        [{including | excluding} indexes]
        [{with | without} validation ]
  | modify partition unusable local indexes
  | modify partition rebuild unusable local indexes }


p.412
partition by range (DeptNo)
 (partition PART1   values less than (11)
   tablespace PART1_TS,


alter table EMPLOYEE
   modify partition PART1
   storage (next 1M pctincrease 0);


alter table EMPLOYEE
  truncate partition PART3
  drop storage;


p.414
alter index [user.]INDEX
 | modify partition PARTITION_NAME
             [ storage
             | {logging | nologging}
             | unusable ]
| rename partition PARTITION_NAME
             to NEW_PARTITION_NAME
| drop partition PARTITION_NAME
| split partition PARTITION_NAME_OLD
             at (value_list)
   [ into ( partition [SPLIT_PARTITION_1]
             [ storage
             | tablespace tablespace
             | {logging | nologging} ... ]
            partition [SPLIT_PARTITION_2]
             [ storage
             | tablespace tablespace
             | {logging | nologging} ... ] ) ]
   [ parallel parallel_clause | noparallel ]
| rebuild partition PARTITION_NAME 
             [ storage
             | tablespace tablespace
             | {parallel parallel_clause | noparallel}
             | {logging | nologging} ... ]
| unusable


alter index EMPLOYEE_DEPTNO
rebuild partition PART4
storage (initial 2M next 2M pctincrease 0);


p.415
create index STATE_CODE_DESCRIPTION
on STATE(State_Code, Description);

create index STATE_DESCRIPTION_CODE
on STATE(Description, State_Code);


create table STATE (
State_Code      CHAR(2) primary key,
Description     VARCHAR2(25)
)
organization index;


p.416
State_Code bitmaps:
   DE:  < 1 1 1 1 1 0 0 0 0 0 >
   NH:  < 0 0 0 0 0 1 1 1 1 1 >


p.417
create bitmap index EMPLOYEE$STATE_CODE$BMAP
    on EMPLOYEE(State_Code);


p.419
set transaction use rollback segment SEGMENT_NAME;


p.422
insert /*+ APPEND */ into SALES_PERIOD_CUST_AGG
select Period_ID, Customer_ID, SUM(Sales)
  from SALES
 group by Period_ID, Customer_ID;


p.423
delete from SALES where Customer_ID=12;


execute DELETE_COMMIT('delete from SALES where Customer_ID=12',1000);


execute DELETE_COMMIT('delete from SALES where State_Code = ''NH''',500)


p.423-425
create or replace procedure DELETE_COMMIT
( p_statement in varchar2,
  p_commit_batch_size   in number default 10000)
is
        cid                             integer;
        changed_statement               varchar2(2000);
        finished                        boolean;
        nofrows                         integer;
        lrowid                          rowid;
        rowcnt                          integer;
        errpsn                          integer;
        sqlfcd                          integer;
        errc                            integer;
        errm                            varchar2(2000);
begin
        /* ۂ̕ where ܂ޏꍇ́A
           AND gp rewnum < n ǉB
           łȂꍇ́AWHERE rownum < n gp */
        if ( upper(p_statement) like '% WHERE %') then
                changed_statement := p_statement||' AND rownum < '
               ||to_char(p_commit_batch_size + 1);
        else
changed_statement := p_statement||' WHERE rownum < '
||to_char(p_commit_batch_size + 1);
        end if;
        begin
  cid := dbms_sql.open_cursor; -- ^XN̂߂ɃJ[\JB
                dbms_sql.parse(cid,changed_statement, dbms_sql.native);
                        -- J[\͂B
  rowcnt := dbms_sql.last_row_count;
                      -- Ń|[g邽߂Ɋi[B
        exception
           when others then
                     errpsn := dbms_sql.last_error_position;
                        -- ύXꂽ SQL ̃G[̈ʒu^A
                        -- ꍇ́A폜B
     sqlfcd := dbms_sql.last_sql_function_code;
                        -- t@NVR[hɂĂ OCI ̃}jAQƁB
                     lrowid := dbms_sql.last_row_id;
                        -- G[|[gpɁA炷ׂĂ̒li[B
                        -- ̒ĺAdbms_output 𐬌邽߂
                        -- PƂŃvV[WsꍇɖɗA
                        -- tH[܂̓tgGhc[Ăяoꂽ
                        -- ꍇ͎gpłȂB
                     errc := SQLCODE;
                     errm := SQLERRM;
                     dbms_output.put_line('Error '||to_char(errc)||
                           ' Posn '||to_char(errpsn)||
               ' SQL fCode '||to_char(sqlfcd)||
        ' rowid '||rowidtochar(lrowid));
                    raise_application_error(-20000,errm);
                        -- ɂAꍇ́A
                        -- ƂtgGhc[łĂA
                        -- ȂƂG[bZ[Wmɕ\B
        end;
        finished := FALSE;
        while not (finished)
        loop -- vZXI܂ŁA
             -- J[\̎s𑱂B
                 begin
 nofrows := dbms_sql.execute(cid);
                        rowcnt := dbms_sql.last_row_count;
                exception
                        when others then
                                errpsn := bms_sql.last_error_position;
                         sqlfcd := dbms_sql.last_sql_function_code;
                    lrowid := dbms_sql.last_row_id;
    errc := SQLCODE;
                                errm := SQLERRM;
                      dbms_output.put_line('Error '||to_char(errc)||
                             ' Posn '||to_char(errpsn)||
                 ' SQL fCode '||to_char(sqlfcd)||
          ' rowid '||rowidtochar(lrowid));
                                  raise_application_error(-20000,errm);
                end;
                if nofrows = 0 then
                        finished := TRUE;
                else
                 finished := FALSE;
                end if;
                commit;
        end loop;
        begin
                dbms_sql.close_cursor(cid);
                        -- ȂI邽߂ɃJ[\B
        exception
                when others then
                        errpsn := dbms_sql.last_error_position;
                        sqlfcd := dbms_sql.last_sql_function_code;
                        lrowid := dbms_sql.last_row_id;
     errc := SQLCODE;
                        errm := SQLERRM;
       dbms_output.put_line('Error '||to_char(errc)||
      ' Posn '||to_char(errpsn)||
                                ' SQL fCode '||to_char(sqlfcd)||
                                ' rowid '||rowidtochar(lrowid));
                        raise_application_error(-20000,errm);
        end;
end;
/


p.431
where Customer_ID between 123 and 241



Chapter13
p.349
(DESCRIPTION=
      (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=HQ)
            (PORT=1521))
      (CONNECT DATA=
            (SID=LOC)))
            
            
p.440
LOC =(DESCRIPTION=
      (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=HQ)
            (PORT=1521))
      (CONNECT DATA=
            (SID=LOC)))


> sqlplus hr/puffinstuff@LOC;


p.442
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS=
          (PROTOCOL=IPC)
          (KEY= loc.world)
        )
   ) 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = loc)
      (ORACLE_HOME = /orasw/app/oracle/product/8.0.3.1)
    )
  )


p.448
LOC =(DESCRIPTION=
      (ADDRESS=
            (COMMUNITY=TCP.HQ.COMPANY)
            (PROTOCOL=TCP)
            (HOST=HQ)
            (PORT=1521))
      (CONNECT DATA=
            (SID=LOC)))


p.449
cmctl start cman


p.451
create database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'LOC';


create public database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'LOC';


p.452
create view LOCAL_EMPLOYEE_VIEW
as
select * from EMPLOYEE@HR_LINK
where Office='ANNAPOLIS';


create public synonym EMPLOYEE for EMPLOYEE@HR_LINK;


p.453
create database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'LOC';

create table EMPLOYEE
as
select * from EMPLOYEE@HR_LINK;


copy from
remote_username/remote_password@service_name
to
username/password@service_name
[append|create|insert|replace]
TABLE_NAME
using subquery;


p.454
set copycommit 1
set arraysize 1000
copy from HR/PUFFINSTUFF@loc -
create EMPLOYEE -
using -
select * from EMPLOYEE


p.455
LOC =(DESCRIPTION=
      (SDU=2048)
      (TDU=2048)
      (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=HQ)
            (PORT=1521))
      (CONNECT DATA=
            (SID=LOC)))
ɊŶ́Â悤listener.orat@C̒łw肷Kv܂B

LISTENER =
  (ADDRESS_LIST =
        (ADDRESS=
          (PROTOCOL=IPC)
          (KEY= loc.world)
        )
   ) 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SDU=2048)
      (TDU=2048)
      (SID_NAME = LOC)
      (ORACLE_HOME = /orasw/app/oracle/product/8.0.3.1)
    )
  )



Chapter14
p.458
127.0.0.1 nmhost
127.0.0.2 txhost
127.0.0.3 azhost  arizona


p.460
loc:/orasw/app/oracle/product/8.0.3.1:Y
cc1:/orasw/app/oracle/product/8.0.3.1:N
old:/orasw/app/oracle/product/8.0.3.0:Y


HQ =(DESCRIPTION=
      (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=HQ)
            (PORT=1521))
      (CONNECT DATA=
            (SID=loc)))


p.461
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS=
          (PROTOCOL=IPC)
          (KEY= loc.world)
        )
   ) 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = loc)
      (ORACLE_HOME = /orasw/app/oracle/product/8.0.3.1)
    )
  )


p.462
> lsnrctl start


> lsnrctl start my_lsnr


> lsnrctl status


p.462-463
LSNRCTL for SUNOS: Version 2.3.3.0.0 -
Copyright (c) Oracle Corporation 1994.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=loc.world))

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for SUNOS: Version 2.3.3.0.0 - Production
Start Date                18-AUG-97 20:01:18
Uptime                    22 days 17 hr. 48 min. 26 sec
Trace Level             off
Security               OFF
SNMP                      OFF
Listener Parameter File   /etc/listener.ora
Listener Log File          /orasw/app/oracle/product/7.3.3.2/network
                          /log/listener
.log
Services Summary...
  loc           has 1 service handler(s)
The command completed successfully.


p.463
> ps -ef | grep tnslsnr


oracle  4249     1  0  Aug 18  ?      1:14 /orasw/app/oracle/product/7.3.3.
                                      2/bin/tnslsnr LISTENER -inhe
oracle  2469  2419  1 13:56:23 ttypc  0:00 grep tns


p.465
> lsnrctl
LSNRCTL> set password lsnr_password
LSNRCTL> stop


> lsnrctl status


> lsnrctl status hq


> lsnrctl version


p.466
> lsnrctl
LSNRCTL> set password lsnr_password
LSNRCTL> services


> lsnrctl
LSNRCTL> set password lsnr_password
LSNRCTL> reload


> lsnrctl
LSNRCTL> set password lsnr_password
LSNRCTL> save_config


> lsnrctl
LSNRCTL> set password lsnr_password
LSNRCTL> trace user


> lsnrctl
LSNRCTL> set password lsnr_password
LSNRCTL> start


p.467
> telnet host_name


> ping host_name


> lsnrctl status service_name



Chapter15
p.471
create public database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';


p.472
select * from EMPLOYEE@HR_LINK
 where Office='ANNAPOLIS';


p.477
create public database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';


hq =(DESCRIPTION=
      (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=HQ)
            (PORT=1521))
      (CONNECT DATA=
            (SID=LOC))))


create public database link HR_LINK
using 'hq';


select * from EMPLOYEE@HR_LINK;


p.478
create public database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';


create public database link HR_LINK
using 'hq';

create view REMOTE_EMP
as select * from EMPLOYEE@HR_LINK
where Ename=User;


p.480
drop database link HR_LINK;


create public database link HR_LINK
connect to HR identified by NEWPASSWORD
using 'hq';


create database link SCOTT.HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';


p.481
create public database link TRIGGER_LINK
using 'remote1';


p.482
create trigger COPY_DATA
after insert on EMPLOYEE
for each row
begin
     insert into EMPLOYEE@TRIGGER_LINK
     values
     (:new.Empno, :new.Ename, :new.Deptno,
     :new.Salary, :new.Birth_Date, :new.Soc_Sec_Num);
end;
/


select Trigger_Type,
       Triggering_Event,
       Table_Name
  from DBA_TRIGGERS
 where Trigger_Name = 'COPY_DATA';


   TYPE             TRIGGERING_EVENT       TABLE_NAME
   ---------------- ---------------------- ------------
   AFTER EACH ROW   INSERT                 EMPLOYEE


select Trigger_Body
  from DBA_TRIGGERS
 where Trigger_Name = 'COPY_DATA';


p.482-483
   TRIGGER_BODY
   -------------------------------------------------------
   begin
        insert into EMPLOYEE@TRIGGER_LINK
        values
        (:new.Empno, :new.Ename, :new.Deptno,
        :new.Salary, :new.Birth_Date, :new.Soc_Sec_Num);
   end;


p.483
create database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';


p.484
create snapshot EMP_DEPT_COUNT
pctfree 5
tablespace SNAP
storage (initial 100K next 100K pctincrease 0)
refresh complete
      start with SysDate
      next SysDate+7
as select Deptno, COUNT(*) Dept_Count
     from EMPLOYEE@HR_LINK
    group by Deptno;


drop snapshot EMP_DEPT_COUNT;


p.485
alter snapshot EMP_DEPT_COUNT pctfree 5;


select
 Name,           /*XibvVbgŎgpr[̖O*/
 Last_Refresh,   /*ŌɎstbṼ^CX^v*/
 Type,           /*tbVɎgp郊tbṼ^Cv*/
 Query           /*XibvVbg̍쐬Ɏgp⍇*/
from DBA_SNAPSHOTS;


p.487
DBMS_REFRESH.MAKE
( name      IN VARCHAR2,
  list      IN VARCHAR2,
  next_date IN DATE,
  interval  IN VARCHAR2,
  implicit_destroy     IN BOOLEAN DEFAULT FALSE,
  lax                  IN BOOLEAN DEFAULT FALSE,
  job                  IN BINARY_INTEGER DEFAULT 0,
  rollback_seg         IN VARCHAR2 DEFAULT NULL,
  push_deferred_rpc    IN BOOLEAN DEFAULT TRUE,
  refresh_after_errors IN BOOLEAN DEFAULT FALSE );


execute DBMS_REFRESH.MAKE
(name => 'emp_group',
 list => 'local_emp, local_dept',
 next_date => SysDate,
 interval => 'SysDate+7')


p.488
DBMS_REFRESH.ADD
( name      IN VARCHAR2,
  list      IN VARCHAR2,
  lax       IN BOOLEAN  DEFAULT FALSE );


DBMS_REFRESH.SUBTRACT
( name      IN VARCHAR2,
  list      IN VARCHAR2,
  lax       IN BOOLEAN  DEFAULT FALSE );


DBMS_REFRESH.CHANGE
( name                 IN VARCHAR2,
  next_date            IN DATE DEFAULT NULL,
  interval             IN VARCHAR2 DEFAULT NULL,
  implicit_destroy     IN BOOLEAN  DEFAULT NULL,
  rollback_seg         IN VARCHAR2 DEFAULT NULL,
  push_deferred_rpc    IN BOOLEAN DEFAULT NULL,
  refresh_after_errors IN BOOLEAN DEFAULT NULL);


p.489
execute DBMS_REFRESH.CHANGE
(name => 'emp_group',
 next_date => null,
 interval => 'SysDate+3');


execute DBMS_REFRESH.DESTROY(name => 'emp_group');


p.490
refresh complete
      start with SysDate
      next SysDate+7


p.491
execute DBMS_SNAPSHOT.REFRESH('emp_dept_count','?');


execute DBMS_SNAPSHOT.REFRESH_ALL;


p.492
execute DBMS_REFRESH.REFRESH('emp_group');


create snapshot log on EMPLOYEE
tablespace DATA_2
storage(initial 100K next 50K pctincrease 0)
pctfree 5 pctused 90;


p.493
alter snapshot log EMPLOYEE
pctfree 10;


drop snapshot log on EMPLOYEE;


p.495
create database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';

create snapshot EMP_DEPT_COUNT
refresh complete
      start with SYSDATE
      next SYSDATE+7
as select Deptno, COUNT(*) Dept_Count
     from EMPLOYEE@HR_LINK
    group by Deptno;


p.496
execute DBMS_SNAPSHOT.PURGE_LOG
(master => 'EMPLOYEE',
    num => 1,
   flag => 'DELETE');


truncate table EMPLOYEE preserve snapshot log;


p.497
truncate table EMPLOYEE purge snapshot log;


p.498
COMMIT_POINT_STRENGTH=100


p.500
DB_NAME = loc
DB_DOMAIN = hq.mycorp.com


GLOBAL_NAMES = true


CREATE PUBLIC DATABASE LINK loc.hq.mycorp.com
USING 'connect string';


p.506
PROCEDURE SUBMIT
  ( job       OUT BINARY_INTEGER,
    what      IN  VARCHAR2,
    next_date IN  DATE DEFAULT sysdate,
    interval  IN  VARCHAR2 DEFAULT 'null',
    no_parse  IN  BOOLEAN DEFAULT FALSE);


variable jobno number;
begin
  DBMS_JOB.SUBMIT(:jobno,'myproc',SysDate,'SysDate+1');
  commit;
end;
/

print jobno

JOBNO
-----------
       8791


p.507
PROCEDURE BROKEN
( job       IN  BINARY_INTEGER,
  broken    IN  BOOLEAN,
  next_date IN  DATE DEFAULT SYSDATE );


PROCEDURE CHANGE
( job       IN  BINARY_INTEGER,
  what      IN  VARCHAR2,
  next_date IN  DATE,
  interval  IN  VARCHAR2);



Chapter16
p.513
listener  1521


p.514
loc:/orasw/app/oracle/product/8.0.3.1:Y
cc1:/orasw/app/oracle/product/8.0.3.1:N
old:/orasw/app/oracle/product/8.0.3.0:Y


> lsnrctl start\


p.515
> lsnrctl status


127.0.0.1 nmhost
127.0.0.2 txhost
127.0.0.3 azhost arizona
127.0.0.4 hq


p.516
listener  1521



AppendixA
p.527
> ORACLE_SID=olddb; export ORACLE_SID
> . oraenv
> exp system/manager file=olddb.dmp rows=Y grants=Y indexes=Y


p.528
> ORACLE_SID=newdb; export ORACLE_SID
> imp system/manager file=olddb.dmp full=Y buffer=1000000
                     commit=Y ignore=Y


> imp system/manager file=olddb.dmp full=Y buffer=1000000 commit=Y
                                    ignore=Y rows=Y indexes=N
> imp system/manager file=olddb.dmp full=Y ignore=Y rows=N indexes=Y


> svrmgrl
SVRMGR> connect internal;
SVRMGR> @catalog


p.529
> svrmgrl
SVRMGR> connect internal
SVRMGR> startup nomount;
SVRMGR> alter database convert;
SVRMGR> alter database open resetlogs;


> svrmgrl
SVRMGR> connect internal
SVRMGR> @cat8000.sql


p.530
select RowID
  from NUMBERTEST
 where Rownum=1;

ROWID
------------------
AAAArfABQAAAALBAAA


p.531
select DBMS_ROWID.ROWID_OBJECT(RowID)
  from NUMBERTEST
 where RowNum=1;

DBMS_ROWID.ROWID_OBJECT(ROWID)
------------------------------
                          2783


select Object_Name
  from DBA_OBJECTS
 where Object_ID = 2783;

OBJECT_NAME
----------------------------------------
NUMBERTEST


p.531
select DBMS_ROWID.ROWID_TO_RESTRICTED(RowID,0)
  from NUMBERTEST;

DBMS_ROWID.ROWID_T
------------------
000002C1.0000.0050
000002C1.0001.0050
000002C1.0002.0050
000002C1.0003.0050
000002C1.0004.0050
000002C1.0005.0050
000002C1.0006.0050
000002C1.0007.0050


p.532
select DBMS_ROWID.ROWID_RELATIVE_FNO(RowID)
  from NUMBERTEST
 where RowNum=1;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
                                  80


p.533
select File_Name, File_ID
  from DBA_DATA_FILES
 where Relative_FNO = 80;

FILE_NAME                                   FILE_ID
---------------------------------------- ----------
/db02/oracle/CC1/users01.dbf                      5


select DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(RowID,'TALBOT','NUMBERTEST')
  from NUMBERTEST
 where RowNum=1;
 
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'TALBOT','NUMBERTEST')
-------------------------------------------------------------
                                                            5


p.534
select DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)
  from NUMBERTEST
 where RowNum=1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                 705


select Relative_FNO, Block_ID
  from DBA_EXTENTS
 where Segment_Name = 'NUMBERTEST';

RELATIVE_FNO   BLOCK_ID
------------ ----------
          80        704


select DBMS_ROWID.ROWID_RELATIVE_FNO(RowID),
       DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)
  from NUMBERTEST
 where RowNum=1;


p.535
select DBMS_ROWID.ROWID_ROW_NUMBER(RowID)
  from NUMBERTEST;

DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
----------------------------------
                                 0
                                 1
                                 2
                                 3
                                 4
                                 5
                                 6
                                 7


p.537
create type ADDRESS_TY as object
(Street  VARCHAR2(50),
 City    VARCHAR2(25),
 State   CHAR(2),
 Zip     NUMBER);


create type PERSON_TY as object
(Name      VARCHAR2(25),
 Address   ADDRESS_TY);


create table CUSTOMER
(Customer_ID  NUMBER,
 Person       PERSON_TY);


p.538
insert into CUSTOMER values
(444, 
 PERSON_TY('JANET NORWOOD',
    ADDRESS_TY('100 RIVER RD', 'RIDGE', 'MA', 10002)));


create type PERSON_TY as object
(Name     VARCHAR2(25),
 Address  ADDRESS_TY);


Warning: Type created with compilation errors.


create or replace type PERSON_TY as object
(Name     VARCHAR2(25),
 Address  Dora.ADDRESS_TY);

Warning: Type created with compilation errors.


p.539
show errors
Errors for TYPE PERSON_TY:

LINE/COL ERROR
-------- ---------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
3/11     PLS-00201: identifier 'DORA.ADDRESS_TY' must be declared


grant EXECUTE on ADDRESS_TY to George;


create or replace type PERSON_TY as object
(Name     VARCHAR2(25),
 Address  Dora.ADDRESS_TY);


create table CUSTOMER
(Customer_ID  NUMBER,
 Person       PERSON_TY);


insert into CUSTOMER values
(1,PERSON_TY('SomeName',
   ADDRESS_TY('StreetValue','CityValue','ST',11111)));


p.540
insert into CUSTOMER values
(1,PERSON_TY('SomeName',
   Dora.ADDRESS_TY('StreetValue','CityValue','ST',11111)));


create synonym ADDRESS_TY for Dora.ADDRESS_TY;


create type PERSON2_TY
(Name     VARCHAR2(25),
 Address  ADDRESS_TY);

create type PERSON2_TY
*
ERROR at line 1:
ORA-22863: synonym for datatype DORA.ADDRESS_TY not allowed


p.541
create table CUSTOMER
(Customer_ID    NUMBER,
 Person         PERSON_TY);


select Customer_ID, Person.Name
  from CUSTOMER;


p.542
select Person.Name,
       Person.Address.City
  from CUSTOMER
 where Person.Address.City like 'F%';


create index I_CUSTOMER$CITY
on CUSTOMER(Person.Address.City);


p.543
create table CUSTOMER
(Customer_ID NUMBER   primary key,
 Name        VARCHAR2(25),
 Street      VARCHAR2(50),
 City        VARCHAR2(25),
 State       CHAR(2),
 Zip         NUMBER);


p.543
create view CUSTOMER_OV (Customer_ID, Person) as
select Customer_ID,
       PERSON_TY(Name,
       ADDRESS_TY(Street, City, State, Zip))
  from CUSTOMER;


p.544
create or replace type CUSTOMER_TY as object
(Customer_ID NUMBER,
 Name        VARCHAR2(25),
 Street      VARCHAR2(50),
 City        VARCHAR2(25),
 State       CHAR(2),
 Zip         NUMBER);


create view CUSTOMER_OV of CUSTOMER_TY
with object OID (Customer_ID) as
select Customer_ID, Name, Street, City, State, Zip
  from CUSTOMER;


p.545
create table CUSTOMER_CALL
(Customer_ID   NUMBER,
 Call_Number   NUMBER,
 Call_Date     DATE,
 constraint CUSTOMER_CALL_PK
     primary key (Customer_ID, Call_Number),
 constraint CUSTOMER_CALL_FK foreign key (Customer_ID)
    references CUSTOMER(Customer_ID));


create view CUSTOMER_CALL_OV as
select MAKE_REF(CUSTOMER_OV, Customer_ID) Customer_ID,
       Call_Number,
       Call_Date
  from CUSTOMER_CALL;


select DEREF(CCOV.Customer_ID)
  from CUSTOMER_CALL_OV CCOV
 where Call_Date = TRUNC(SysDate);



AppendixB
p.550
> cd $ORACLE_HOME/otrace/admin
> ls -alt *.dat
-rw-rw-rw-   1 oracle   dba     11948576 Sep 16 10:40 process.dat
-rw-rw-rw-   1 oracle   dba      1023292 Sep 16 10:40 regid.dat


> cd $ORACLE_HOME/otrace/admin
> rm -f process.dat regid.dat
> otrccref


p.551
EPC_DISABLED=TRUE; export EPC_DISABLED


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = loc)
      (ORACLE_HOME = /orasw/app/oracle/product/8.0.3.1)
    )
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = loc)
      (ORACLE_HOME = /orasw/app/oracle/product/8.0.3.1)
      (ENVS='EPC_DISABLED=TRUE')
    )
  )


p.552
 alter tablespace REPOS_TS
default storage (initial 20K next 20K pctincrease 0 
                 maxextents 250);


p.553
orapwd file=$ORACLE_HOME/dbs/orapwCC1 password=wwywwyl


REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE


p.554
dbsnmp.world = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hq)(PORT=1528)))


cc1.world = (ORACLE_DATABASE,hq, LISTENER_hq.world)
dbsnmp.world = (ORACLE_AGENT,hq)
listener.world = (ORACLE_LISTENER, hq)


snmp.visibleservices = (cc1.world)
snmp.ORACLE_HOME.cc1.world=/orasw/app.oracle/product/8.0.3.0
snmp.INDEX.cc1.world = 1
snmp.CONTACT.cc1.world = "Your Name Here"
snmp.SID.cc1.world = cc1
snmp.CONNECT.cc1.world.name = dbsnmp
snmp.CONNECT.cc1.world = dbsnmp
nmi.trace_level = off
nmi.trace_mask = (106)
dbsnmp.address = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hq)(PORT=1528)))
dbsnmp.spawnaddress=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hq)
(PORT=1529)))


p.555
dbsnmp.address = (DESCRIPTION=(ADDRESS=
(COMMUNITY=unix.world)(PROTOCOL=tcp)(HOST=hq)(PORT=1528)))
dbsnmp.spawnaddress=(DESCRIPTION=(ADDRESS=
(COMMUNITY=unix.world)(PROTOCOL=tcp)(HOST=hq)(PORT=1529)))



AppendixC
p.586
ALTER SYSTEM
  DISABLE RESTRICTED SESSION;


ALTER SYSTEM
  FLUSH SHARED_POOL;


ALTER SYSTEM
  CHECKPOINT;


p.587
ALTER SYSTEM
  CHECK DATAFILES GLOBAL;


ALTER SYSTEM
  SET RESOURCE_LIMIT = TRUE;


p.590
ALTER SYSTEN
  SWITCH LOGFILE;


ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;


ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;


p.604
ALTER TABLE sales
  ADD PARTITION jan97 VALUES LESS THAN( '970201' )
  TABLESPACE tsx;


p.605
ALTER TABLE sales DROP PARTITION dec95;


ALTER TABLE sales
  EXCHANGE PARTITION feb97 WITH TABLE sales_feb97
   WITHOUT VALIDATION;


p.606
ALTER TABLE sales MODIFY PARTITION nov96
  UNUSABLE LOCAL INDEXES;


ALTER TABLE sales MODIFY PARTITIION jan97
  REBUILD UNUSABLE LOCAL INDEXES;


ALTER TABLE branch MODIFY PARTITION branch_ny
  STORAGE(MAXEXTENTS 75) LOGGING;


p.607
ALTER TABLE parts
  MOVE PARTITION depot2 TABLESPACE ts094 NOLOGGING;


ALTER TABLE emp RENAME TO employee;


ALTER TABLE employee RENAME PARTITION emp3 TO employee3;


p.608
ALTER TABLE parts
  SPLIT PARTITION depot4 AT ( '40-001' )
  INTO ( PARTITION depot4 TABLESPACE ts009 (MINEXTENTS 2),
         PARTITION depot9 TABLESPACE ts010 )
  PARALLEL ( DEGREE 10 );


ALTER TABLE deliveries
  TRUNCATE PARTITION sys_p017 DROP STORAGE;


p.622
ANALYZE TABLE emp
   COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;


ANALYZE TABLE emp PARTITION (p1) COMPUTE STATISTICS;


p.652
CREATE INDEX emp_idx1 ON emp (ename, job);
CREATE INDEX emp_idx2 ON emp (job, ename);


p.665
SELECT *
    FROM dept



p.666
SELECT *
    FROM blake.dept


SELECT *
    FROM dept
    
    
p.678
CREATE TYPE dept_t AS OBJECT
( dname VARCHAR2(100),
address VARCHAR2(200) );


CREATE TABLE dept OF dept_t;


