wORACLE8 rMi[YKChx


Chapter1
p.32
[J[:  Y
f    X^C      iNX
Quest     ~jo      D
Maxima    Z_        G
Ultima    X|[c      F

[J[:  GM
f    X^C      iNX
Safari    ~jo      D
Impala    Z_        G
TransAm   X|[c      F


̔       4          m}
10           1              500
10           2              400
20           1              600
20           2              900
20           3              350
30           2              900
30           3              235


             1              2              3
10           500            400 
20           600            900            350
30                          900            235


p.33
...
Dear Ms. Stroud:
This is to inform you that ...... no later than the end of May, 1998.
...
Dear Mr. Flaherty:
This is to inform you that ...... no later than the middle of July, 1999.
...



Chapter2
p.55
create table ...
( col1   number,
  col2   number,
  ...        )
partition by range ( col1, col2 )
 ( partition p1 values less than ( ...,... ) tablespace p1,
   partition p2 values less than ( ...,... ) tablespace p2);


create index customer_idx
      on customer   (customer_last_name, customer_first_name)
  partition by range (customer_last_name)
             partition values less than ('N') tablespace ts1,
             partition values less than (maxvalue) tablespace ts2;


p.57
alter table sales drop partition prt4;
alter table sales add partition prt4 values
      less than ('970523') tablespace ts4;
alter table sales drop partition prt4;
alter table sales modify partition prt4 unusable local indexes;
alter table sales modify partition prt4 rebuild unusable local indexes;
alter table sales rename partition prt4 to sale_prt4;
alter table sales truncate partition prt4 drop storage;
alter index cust_idx rebuild partition prt4 nologging;
alter index cust_idx modify partition prt4 unusable;
export scott/tiger file=exp.dmp
      tables=(scott.sales:prt4, scott.sales:prt2)


p.59-60
create table sales nologging parallel (degree 4) as select * from sales_ne;
create index sales_idx on sales (sale_dt) nologging parallel (degree 3);
update /*+ parallel (sales, 4) */ sales set c1=c1+1;
insert /*+ parallel (sales, 2) */ into sales ...
select /*+ parallel (sales, 4) */ * from sales;


p.61
create type room_capacity_type (
     auditorium_setting        integer,
     table_setting             integer,
     standing_room_setting     integer);


SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL>
SQL> create table conference_facility (
  2   room_name                 varchar2 (20),
  3   room_settings             room_capacity_type);
\쐬܂B
SQL> -- ** now let's inset into the table **
SQL> insert into conference_facility values (
  2   'GREAT HALL', room_capacity_type (500, 200, 1000));
1 R[h쐬܂B


p.62
create type price as varray (100) of number;


create table car (car_name varchar2 (25), car_val price);


p.62-63
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> create type address_type (
  2   street                     varchar2 (40),
  3   city                       varchar2 (30),
  4   state                      varchar2 (2),
  5   zip_cd                     number (5));
\쐬܂B
SQL> create type employee_type (
  2   name                        varchar2 (30),
  3   hire_date                 date,
  4   address                              address_type,
  5   member procedure           give_raise
  6   member function                   get_salary return number;
\쐬܂B
SQL>


p.63
declare
  p1        person_type;
  addr      address;
  begin
    create procedure get_emp_id
       (employee            emp_type) as ...
    create function get_emp (name varchar2)
      return emp_type as ...


p.63-64
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> create type good_bad_type
  2   (good_guy                    integer,
  3    bad_guy                     integer,
  4    map member function good_or_bad return real,
  5    member procedure            normalize,
  6    member function             neutral (x good_bad_type)
  7    return good_bad_type);
Type created.
SQL>



Chapter3
p.72
create table customer
  (first_name         varchar2(15),
   last_name          varchar2(15),
   phone_area_code    number,
   phone_number       number)
tablespace users;



Chapter4
p.95-96
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects 
and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> create table customer 
  2  (last_name  varchar2(30), 
  3   state_cd   char(2), 
  4   sales      number);
\쐬܂B
SQL>


p.98
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> create or replace view cust as 
  2  select last_name lname, state_cd
  3  from customer;
r[쐬܂B
SQL>


p.99
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects 
and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> create table sample_3
  2  (a   char2(30),
  3   b   char2(30),
  4   c   char2(30)
....
\쐬܂B
SQL>


p.101
select cola from ops$coreymj.sample_3;


p.102
select cola from ops$coreymj.sample_3;
select cola from toast;


p.103
select * from sample_b;


p.104
12003 R[h폜܂B


p.105
grant insert on tableA to nurse; 
grant insert on tableB to nurse;
grant insert,delete on tableC to nurse;
grant update on tableD to nurse;
grant delete on tableE to nurse;
grant select on tableF to nurse;


grant nurse to ops$abbey;
grant nurse to ops$teplow;
grant nurse to ops$lane;


p.107
grant execute on my_package to public; -- all users can execute the code
grant execute on my_func to nurse_role; -- all members of the role can
      run the code
grant execute on my_procedure to tom_scholz; -- user tom_scholz may run 
      procedure


p.108
ALL_CATALOG         All tables, views, synonyms, sequences accessible to 
                    the user
ALL_INDEXES         Descriptions of indexes on tables accessible to the 
                    user
ALL_IND_COLUMNS     COLUMNs comprising INDEXes on accessible TABLES
ALL_OBJECTS         Objects accessible to the user
ALL_SEQUENCES       Description of SEQUENCEs accessible to the user
ALL_SYNONYMS        All synonyms accessible to the user
ALL_TABLES          Description of tables accessible to the user
USER_CATALOG        Tables, Views, Synonyms and Sequences owned by the
                    user
USER_CLUSTERS       Descriptions of user's own clusters
USER_CLU_COLUMNS    Mapping of table columns to cluster columns
USER_INDEXES        Descriptions of the user's own indexes
USER_IND_COLUMNS    COLUMNs comprising user's INDEXes or on user's TABLES
USER_OBJECTS        Objects owned by the user
USER_SEQUENCES      Description of the user's own SEQUENCEs
USER_SYNONYMS       The user's private synonyms
USER_TABLES         Description of the user's own tables
USER_VIEWS          Description of the user's own views


p.109
SQL> desc user_dependencies
Name                     Null?        Type
--------------------     --------     ----
NAME                     NOT NULL     VARCHAR2(30)
TYPE                                  VARCHAR2(12)
REFERENCED_OWNER                      VARCHAR2(30)
REFERENCED_NAME                       VARCHAR2(64)
REFERENCED_TYPE                       VARCHAR2(12)
REFERENCED_LINK_NAME                  VARCHAR2(128)
SCHEMAID                              NUMBER


SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects 
and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> select name, referenced_owner, referenced_type
  2   from user_dependencies
  3   where referenced_name = 'ASSIGNMENT';


NAME                   REFERENCED_OWNER   REFERENCED_TYPE
--------------------   ----------------   -------------------
DEPARTMENT             DEPT               TABLE
GET_END_DATE           COMMON             PROCEDURE
DURATION               COMMON             TABLE
SQL>



Chapter5
Ȃ


Chapter6
p.146-147
SQL> create table customer(
   2      last_name  varchar2(30) not null,
   3      state_cd   varchar(2)
   4      sales      number)
   5  tablespace custspace 
   6  storage(initial 25k next 25k minextents 1);
\쐬܂B
SQL> create table state (
   2      state_cd   varchar(2) not null, 
   3      state_name varchar2(30));
\쐬܂B
SQL> create table x (
   2      col        varchar2(30));
\쐬܂B
SQL> create table y (
   2      col        varchar2(30));
\쐬܂B


p.149
 Name                            Null?    Type
 ------------------------------- -------- ----
 last_name                       not null varchar2(50)
 state_cd                        not null char(2)
 sales                                    number


p.150
1 R[h쐬܂B


SQL> insert into customer values ('Porter','CA', 6989.99);
1 R[h쐬܂B
SQL> insert into customer values ('Martin','CA',2345.45);
1 R[h쐬܂B
SQL> insert into customer values ('Laursen','CA',34.34);
1 R[h쐬܂B
SQL> insert into customer values ('Bambi','CA',1234.55);
1 R[h쐬܂B
SQL> insert into customer values ('McGraw','NJ', 123.45);
1 R[h쐬܂B


SQL> insert into state (state_name, state_cd)
   2 values('Massachusetts','MA');
1 R[h쐬܂B
SQL> insert into state (state_name, state_cd)
   2 values('California','CA');
1 R[h쐬܂B
SQL> insert into state (state_name, state_cd)
   2 values('NewJersey','NJ',);
1 R[h쐬܂B


p.150-151
SQL> insert into X values ('1');
1 R[h쐬܂B
SQL> insert into X values ('2');
1 R[h쐬܂B
SQL> insert into X values ('3');
1 R[h쐬܂B
SQL> insert into Y values ('3');
1 R[h쐬܂B
SQL> insert into Y values ('4');
1 R[h쐬܂B
SQL> insert into Y values ('5');
1 R[h쐬܂B


p.151
SQL> select * from user_tables;
PERSON                                   USER_DATA
       10         40        1            255         10240
      10240           1          121             50         1
              1 YES N 1           1          N ENABLED
                      NO         N           NO


p.152
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
CUSTOMER
STATE
X
Y
4 R[hI܂B


LAST_NAME                                          ST      SALES
-------------------------------------------------- -- ----------
Teplow                                             MA   23445.67


p.153
LAST_NAME                      ST      SALES
------------------------------ -- ----------
Porter                         CA    6989.99
Abbey                          CA    6969.96


LAST_NAME                      ST      SALES
------------------------------ -- ----------
Porter                         CA    6989.99
Martin                         CA    2345.45
Laursen                        CA      34.34
Bambi                          CA    1234.55
Teplow                         MA   23445.67
Abbey                          CA    6969.96


p.154
LAST_NAME                                          ST      SALES
-------------------------------------------------- -- ----------
Porter                                             CA    6989.99
McGraw                                             NJ     123.45
Martin                                             CA    2345.45
Laursen                                            CA      34.34
Bambi                                              CA    1234.55
Abbey                                              CA    6969.96


p.155
LAST_NAME                                          ST      SALES
-------------------------------------------------- -- ----------
Porter                                             CA    6989.99
McGraw                                             NJ     123.45
Martin                                             CA    2345.45
Bambi                                              CA    1234.55
Laursen                                            CA      34.34
Abbey                                              CA    6969.96


LAST_NAME                                          ST      SALES
-------------------------------------------------- -- ----------
Porter                                             CA    6989.99
McGraw                                             NJ     123.45
Martin                                             CA    2345.45
Laursen                                            CA      34.34
Bambi                                              CA    1234.55
Abbey                                              CA    6969.96


LAST_NAME                                          ST      SALES
-------------------------------------------------- -- ----------
McGraw                                             NJ     123.45
Martin                                             CA    2345.45


LAST_NAME                                          ST      SALES
-------------------------------------------------- -- ----------
Martin                                             CA    2345.45


p.157
LAST_NAME                                          ST      SALES
-------------------------------------------------- -- ----------
Teplow                                             MA   23445.67
Porter                                             CA    6989.99
McGraw                                             NJ     123.45
Martin                                             CA    2345.45
Laursen                                            CA      34.34
Bambi                                              CA    1234.55
Abbey                                              CA    6969.96
7 R[hI܂B


p.159
SQL> help functions
SQL*Plus Help Files are included in Oracle Documentation.
You have to read SQL*Plus Help Files from Oracle Documentation.
SQL>


ERROR:
ORA-01722: łB


p.160
SQL> create table customer 
  2 (last_name   varchar2(30) not null,
  3  state_cd    varchar2(2), 
  4  sales       number,
  5  sale_date   date);
\쐬܂B


p.166
col
---
1
2
3


col
----
3
4
5


p.167
col col
--- ---
3   3


p.167-168
feedback ON for 6 or more rows
heading ON
linesize 80
numwidth 10
trimsp OFF
autotrace off
spool ON
user is "SYSTEM"
space 1
worksize DEFAULT
lines will be wrapped
pagesize 14
showmode OFF
pause is OFF
ttitle OFF and is the 1st few ...
btitle OFF and is the 1st few ...
define "&" (hex 26)
escape OFF
concat "." (hex 2e)
sqlprompt "SQL> "
underline "-" (hex 2d)
null ""
verify ON
message ON
sqlcode 0
tab ON
scan ON
dclsep OFF
termout ON
echo OFF
sqlcase MIXED
headsep "|" (hex 7c)
maxdata 32767
time OFF
cmdsep OFF
xisql OFF
sqlterminator ";" (hex 3b)
sqlprefix "#" (hex 23)
release 39512
sqlnumber ON
autocommit OFF
newpage 1
long 80
document ON
trimout ON
timing OFF
qbidebug OFF
numformat ""
synonym OFF
suffix "SQL"
flush ON
sqlcontinue "SQL>"
pno 1
lno 15
buffer SQL
embedded OFF
arraysize 15
crt ""
copycommit 0
compatibility version NATIVE
recsep WRAP
recsepchar " " (hex 20)
blockterminator "." (hex 2e)
copytypecheck is ON
longchunksize 80
serveroutput OFF
flagger OFF


Chapter7
p.184
declare
  employee varchar2(30);
  loop number;


p.186
variable_name varchar2(max_length);


vc_field varchar2(10);


vc_field varchar2(10) := 'STARTVALUE';


num_field number(precision,scale)


num_field (12,2)


date_field date;


p.189
if var1 > 10 then
   var2 := var1 + 20;
end if;


p.190
if not(var1 <= 10) then
   var2 := var1 + 20;
end if;


if var1 > 10 then
   if var2 < var 1 then
     var2 := var1 + 20;
   end if;
end if;


if var1 > 10 then
   var2 := var1 + 20;
else
   var2 := var1 * var1;
end if;


p.190-191
if var1 <= 10 then
   var2 := var1 * var1;
else
   var2 := var1 + 20;
end if;


p.191
if var1 > 10 then
   var2 := var1 + 20;
else
   if var1 between 7 and 8 then
      var2 = 2* var1;
   else
      var2 := var1 * var1;
   end if;
end if;


if var1 > 10 then
   var2 := var1 + 20;
elsif var1 between 7 and 8 then
   var2 = 2* var1;
else
   var2 := var1 * var1;
end if;


p.192
if var1 > 10 then
   var2 := var1 + 20;
elsif var1 between 7 and 8 then
   var2 = 2* var1;
end if;


/*R[fBO 1 - 킩ɂ        */
if var1 < 5 then var2 := 'Y'; elsif
var1 = 5 then
var2 := 'N';
else var2 := null; end if;
/*                                   */
/*R[fBO 2 - 킩₷           */
if var1 < 5 then
   var2 :=  'Y';   --́̕Cŏ̃eXgtrue
                   --ꍇCs
elsif var1 = 5 then
   var2 := 'N';    --́̕C2Ԗڂ̃eXgtrue
                   --ꍇCs
else
   var2 := null;   --́̕C̃eXgfalse
                   --ꍇCs
end if;


p.192-193
create or replace procedure licence_transaction (the_act in varchar2) as
begin
  if the_act = 'DLT' then
     12a;
  elsif the_act = 'DT' then
     12b;
  else
     12g;
  end if;
end;
/


p.193-194
cnt := 1;              --[v͂܂OɃ[vp
                       --     JE^
loop                   --Part 1: [v̊Jn
  cnt := cnt + 1;      --Part 2: [vpJE^1Z
  if cnt > 100 then    --     Iɍv邩ǂ
                       --     cnteXg
     exit;             --     Iɍv̂
                       --     [v𔲂
  end if;              --     end if͒OifɑΉ
  ...
  ...
end loop;              --Part 3: [v̏I
...
...


p.194
cnt := 1;              --[v͂܂OɃ[vp
                       --     JE^
loop                   --Part 1: [v̊Jn
  cnt := cnt + 1;      --Part 2: [vpJE^1Z
  exit when cnt > 100  --     cntIɍvĂ邩
  ...                  --     eXg
  ...
  ...
end loop;              --Part 3: [v̏I
...


cnt := 1;              --[v͂܂OɃ[vp
                       --     JE^
while cnt <= 100 loop  --Part 1: whilegă[v
  ...                  --     JnOɖI
  ...                  --     eXg
  ...                  --Part 2: [ṽR[hs
  ...
  cnt := cnt + 1;      --     Iɍv܂Ń[vp
  ...                  --     JE^Z
end loop;              --Part 3: [v̏I
...


p.195
for cnt in 1 .. 3 loop
  insert into tab1 values ('Still in loop',cnt);
end loop;


if cnt >= 90 then
   null;
else
   insert into tab1 values (cnt,'Still less than 90',cnt);
end if;


p.195-196
create or replace procedure samp (parm1 in varchar2,
                                  parm2 in varchar2) as
--DECLARWłDXgAhIuWFNgR[fBOꍇCDECLARE
--ÖقȂ̂ŁCLqKv͂܂D
    accum1 number;
    accum2 number;
    h_date date := sysdate;  --ϐ͂ŏ
                             --܂Ƃɒ
    status_flag varchar2(1);
    mess_text varchar2(80);
    temp_buffer varchar2(1);
    cursor my_cursor is
      select ' '
        from person
       where last_name = parm1
         and sal_stat = parm2;
  begin
     ...
     ...
  end;
/


p.197
...
...
declare
  fname         varchar2(10),
  lname         varchar2(30),
  ssec_num      varchar2(8),
  cursor mycur is
    select first_name, last_name, ssn
      from person
     where pin = pin_in;
begin;
  open mycur;
  fetch mycur into fname, lname, ssec_num;
  while mycur%found loop
    if ssec_num is null then
       insert into e_msg values (pin_in, 'No SSNUM');
    else
       insert into e_tab values (pin_in,sysdate);
    end if;
    fetch mycur into fname, lname, ssec_num;
  end loop;
  close mycur;
end;
...
...
/


p.199
...
...
if mycur%isopen = 'TRUE' then
   null;
else
   open mycur;
end if;


p.200
...
begin
  if counter >= 20 then
     select last_name 
       into lname from person 
      where pin = pin_in;
     ...
  else
     ...
  end if;
end;
/


p.201-202
set serveroutput on size 100000
declare
  state_rec person%rowtype; -- state_rećC 
                            -- STATE\̍sƓ\łD
 type just_names is table of state.name%type
      index by binary_integer; -- [Jjust_names\ɂ
                               -- STATE̊esƓ\
  i binary_integer := 0;       -- "s"܂܂Ă܂D
  nametab just_names;
  begin
    for state_rec in -- FOR[vɖߍ܂Ă邱̖⍇ɂC
                     -- STATEǂ̂悤ɍstFb`̂ 
                     -- ɒӂĂD
       (select name from state) loop
      i := i+i;
      nametab(i) := state_rec.name;
      dbms_output_line (nametab(i));
    end loop;
  end;
/


p.202
Alaska
Alabama
Arkansas


begin
  declare tfield varchar2(20);
  begin
      select desc
        into tfield            
        from prod              /*  PRODPLANNINGL */
       where pnum = 'FR4512';  /*  Qƕ\łD                */
  end;
end;
/


p.203
begin
  declare tfield varchar2(20);
  begin
      select desc
        into tfield            
        from prod              -- PRODPLANNINGL
       where pnum = 'FR4512';  -- Qƕ\łD
  end;
end;
/


create or replace procedure do_trav
                  (class_in in varchar2) as
  cursor mycur is        --PL/SQLubNn܂
    select count(*)      --[JϐƖJ[\
      from person        --Œ`
     where class_code = class_in;
    cnt number;
  begin                   --begińCPL/SQLĂяoC
    open mycur;           --lXgꂽubN̐擪
    fetch mycur into cnt; --\Ă
    while mycur%found loop
      if cnt > 100 then                --ifg
         insert into trav_audit (class_in,cnt)
                values (classin,cnt);  --ÖٓIJ[\
      else
         update trav_audit             --ꗥupdates
                set cnt = cnt+1 where classin = class_in;
      end if;              --If̏Iend-ifŕ\
      fetch mycur into cnt;
    end loop;      
  end;                   --ubNibeginŎn܂jI
end;                     --ɂendg
/                        --"/"PL/SQLubNI


p.204
create or replace procedure temp (count in number) as
  begin
    declare cursor mycur is
      select count(*) from emp;
    begin;
      open mycur;
      fetch mycur;
    end;
 end;
/
x: vV[W쐬܂ARpCEG[܂B
SQL> sho errors
PROCEDURE TEMPŃG[܂B
LINE/COL ERROR
-------- ----------------------------------------------------------------
1/17     PLS-00103: V{ɑ܂B "COUNT"
         ̂w肵Ă:
         <ʎq> <dpt>
         u "COUNT"  "<ʎq>".
5/8      PLS-00103: V{ɑ܂B ";"
         ̂w肵Ă:
         begin declare exit for goto if loop mod null pragma raise
         return select update while <ʎq> Ȃ
         exit ";" 𑱂Oɑ}܂B
7/16     PLS-00103: V{ɑ܂B ";"
         ̂w肵Ă:
         . into
         s: 7, : 16̃p[XĊJ܂B


p.205
create or replace procedure get_dob(ss_num in varchar2,
                                    dob out date) as
begin                 --vO̊Jn
  select birth_date   --J[\
    into dob          --DOBBIRTH_DATE
    from person       --f[^^łKv
      where soc_sec_num = ss_num; 
@@@exeption when no_data_found then
@@@@error_notify (ss_num);  @@@  --ʂ̃vV[WĂяo
  end;
end;
/


p.206
create or replace procedure get_dob (ss_num in varchar2) return date is
  birthd date;       -- [JtɒaZbg
  begin
    select dob
      into birthd
      from person
     where soc_sec_num = ss_num;        -- ÖكJ[\ɂ"into"K{ł
    exception
      when no_data_found
          begin
            error_notify (ss_num);      -- ʂ̃vV[WĂяo
          @birthid := trunc(sysdate);
          end;
        when others then null;
    end;
    return birthd;
  end;
end;                   -- ÕubN̏I
/                      -- "/"PL/SQLubNI


p.206-207
create or replace procedure
          ac_switch (oac number,
                     nac number) as
  1_pref_3          number(3);       -- ÖٓIDECLARE
  1_change_sw       number(3);
  1_change_it       varchar2(1);
  cursor mycur is
    select distinct pref_3
      from phone_nbr
    where are_code = oac;
  begin                             -- ȏ
    open mycur;                     -- tFb`OɃI[v
    fetch mycur into 1_pref_3;
   while mycur%found loop           -- [v̊Jn
     change_it := 'N';
     begin                          -- I̊Jn
       select ''                    -- OubN
         into 1_change_sw
         from static_exc
        where area_code = oac
          and pref_3 = 1_pref_3;
        exeption
          when no_data_found then   -- selectǂ肷
               1_change_it := 'Y';
      end;                          -- select/exception̏I
      if 1_changew_it = 'Y' then
         update phone_nbr
            set area_code = nac
          where area_code = oac
            and pref_3 = 1_pref_3;
      end if;
      fetch mycur into 1_pref_3;
    end loop;                  -- [v̏I
  end;                         -- ȏ̏I
end;                           -- vV[W̏I
/



Chapter8
Ȃ



Chapter9
p.243-245
rem * -----------------------------------------
rem * Oracle8rMi[YKChChapter 9Ŏgp\
rem * 쐬XNvgiISBN 0-07-882122-3j
rem * -----------------------------------------
set echo on
drop table person;
drop table clssn;
drop table bonus;
drop table factory;
drop table commission;
create table person (
    pin            number(6)
    last_name      varchar2(20),
    first_name     varchar2(20),
    hire_date      date,
    salary         number(8,2)
    clssn          varchar2(5));
create table clssn (
    clssn          varchar2(5),
    descr          varchar2(20));
create table bonus (
    emp_id         number(4),
    emp_class      varchar2(2),
    fac_id         varchar2(3),
    bonus_amt      number);
create table factory (
    fac_id         varchar2(3),
    descr          varchar2(20),
    prov           varchar2(2));
create table commission (
    sales_id       number(3),
    qtr            varchar2(1),
    comm_amt       number(8,2));
insert into person values
    (100110, 'SAUNDERS', 'HELEN', '12-DEC-87', 77000, '1');
insert into person values
    (100120, 'FONG', 'LYDIA', '11-MAY-88', 55000, '3');
insert into person values
    (100130, 'WILLIAMS', 'FRANK', '09-DEC-82', 43000, '4');
insert into person values
    (100140, 'COHEN', 'NANCY', '14-AUG-93', 44000, '4');
insert into person values
    (100150, 'STEWART', 'BORIS', '11-NOV-91', 48000, '4');
insert into person values
    (100160, 'REDMOND', 'KENNETH', '01-FEB-92', 32000, '5');
insert into person values
    (100170, 'SMYTHE', 'ROLLY', '11-JUL-83', 33000, '5');
insert into person values
    (100180, 'FRANKS', 'HENRY', '31-JUL-83', 55000, '3');
insert into person values
    (100190, 'GREENBERG', 'JOE', '30-MAR-86', 21000, '6');
insert into person values
    (100200, 'LEVIS', 'SANDRA', '06-DEC-89', 18000, '7');
insert into person values
    (100210, 'APPOLLO', 'BILL', '12-APR-89', 44000, '4');
insert into person values
    (100220, 'JENKINS', 'SALLY', '12-DEC-87', 44000, '4');
insert into clssn values ('1' 'Manager');
insert into clssn values ('2' 'Chief');
insert into clssn values ('3' 'Leader');
insert into clssn values ('4' 'Analyst');
insert into clssn values ('5' 'Clerk');
insert into clssn values ('6' 'Trainee');
insert into clssn values ('7' 'Part time');
inset into bonus values (123, null, 'AE', 2000);
inset into bonus values (124, null, 'AF', 2200);
inset into bonus values (125, null, 'AH', 1200);
inset into bonus values (126, null, 'AH', 1200);
inset into bonus values (127, null, 'AF', 1200);
inset into bonus values (128, null, 'AT', 1500);
inset into bonus values (129, null, 'AT', 1100);
inset into bonus values (130, null, 'AU', 1400);
inset into bonus values (131, null, 'AE', 200);
inset into bonus values (132, null, 'AF', 220);
inset into bonus values (133, null, 'AG', 120);
inset into bonus values (134, null, 'AG', 200);
inset into bonus values (135, null, 'AG', 200);
inset into bonus values (136, null, 'AU', 1400);
inset into bonus values (137, null, 'AH', 100);
inset into bonus values (138, null, 'AU', 1400);
inset into factory values ('AE', 'Northeast', 'ON');
inset into factory values ('AF', 'Northwest', 'MN');
inset into factory values ('AH', 'Southeast', 'ON');
inset into factory values ('AT', 'Central', 'MN');
inset into factory values ('AU', 'South', 'CA');
insert into commission values (10, 1, 140);
insert into commission values (10, 2, 10);
insert into commission values (10, 3, null);
insert into commission values (10, 4, 810);
insert into commission values (20, 1, 1200);
insert into commission values (20, 2, 200);
insert into commission values (20, 3, 500);
insert into commission values (20, 4, 100);
insert into commission values (30, 1, 40);
insert into commission values (30, 2, 19);
insert into commission values (30, 3, 340);
insert into commission values (30, 4, null);


p.249
select oname, location, province,
       desc_e, desc_f         /* ̕\擾*/
  from offices, locations     /* stFb`ꍇ */
 where offices. location =    /* ̍ŝ̒l */
        locations. location;  /* ǂ̕\łłKv܂D */


p.258
Pin       Full name
101210    SALLY JENKINS


p.265
PIN       Full name
101210    Sally Jenkins


p.266
Pin        Full name          Classification
101210     Sally Jenkins      Analyst


p.272
Factory      Emp ID       Bonus
AE           123          2000
             131           200
AF           124          2200
             127          1200
             132           220
AG           133           120
             134           200


Factory      Emp ID       Bonus
AE           123          4000
             124          1000
                           250
AF           126           400


p.276
          1           2           3           4
10              200         300        400        70
20              150          40        600
30                          500        890        50


          1           2           3
10              200         300        400
20              150          40        600
30                          500        890



Chapter10
p.283-284
SQL*Loader: Release 8.0.3.0.1 - Production on Wed Apr 23 19:6:11 1999
Copyright (c) Oracle Corporation 1994, 1996.  All rights reserved.

gp@: SQLLOAD keyword=value [,keyword=value,...]
L[[h:
    userid -- [U[܂̓pX[h           
   control -- t@C̃t@C                  
       log -- Ot@C̃t@C                      
       bad -- sǃt@C̃t@C                      
      data -- f[^t@C̃t@C                     
   discard -- pt@C̃t@C                  
discardmax -- pt@C̐              (ftHg:all)
      skip -- XLbv_R[h̐     (ftHg:0)
      load -- [h_R[h̐     @(ftHg:all)
    errors -- eG[                (ftHg:50)
      rows -- ]^pX̃oChz܂̓_CNgEpX̕ۑꂽf[^̍s
              (ftHg: ]^pX 64, _CNgEpX all)
  bindsize -- ]^pX̃oChz̃TCYioCgj 
             (ftHg:65536)
    silent -- s̃bZ[W̗}~  
             (oCtB[hobNCG[CpCp[eBV)
    direct -- _CNgEpX̎gp                   (ftHg:FALSE)
   parfile -- p[^t@C: p[^w肳Ăt@C̃t@C
  parallel -- p[h̎s                     (ftHg:FALSE)
      file -- GNXeg蓖Ăt@C      
skip_unusable_indexes --  gps\ȍ܂̓p[eBV֎~^܂D 
                        (ftHg:FALSE)
skip_index_maintenance -- ێɁC gps\ɂƂă}[N܂D
                         (ftHg:FALSE)
commit_discontinued -- [hfꂽꍇC[hsR~bg
                      (ftHg:FALSE)
: R}hs̈͒`ꂽŎw肷邱ƂL[[h
Ďw肷邱Ƃ\łB                                    
  O҂'sqlload scott/tiger foo' ɁA                            
  ҂'sqlload control=foo userid=scott/tiger'ɑΉ܂B    
Œӂׂ_                                              
'sqlload scott/tiger control=foo logfile=log'͐ł
'sqlload scott/tiger control=foo log'                     
̂悤ɃL[[hw̌ɒ`ɂw͑܂B    


p.285
sqlload frieda
control = person
pX[h:
SQL*Loader: Release 8.0.3.1 - Production on Sat Mar 11 13:21:54 2000
Copyright (c) Oracle Corporation 1994, 1996.  All rights reserved.


p.288
control = bruce
pX[h:
SQL*Loader: Release 8.0.3.1 - Production on Thu Dec  29 18:08:43 2001
Copyright (c) Oracle Corporation 1994, 1996.  All rights reserved.


p.290
SQL*Loader: Release 8.0.3.1 - Production on Thu Dec 21 18:28:36 1999
Copyright (c) Oracle Corporation 1994, 1997.  All rights reserved.
SQL*Loader-350: 2sɍ\G[܂B
t@CɉpȊO̕sKiȕ܂B
Infile $HOME\person.dat


SQL*Loader: Release 8.0.3.1 - Production on Thu Dec 19 22:17:50 1999
Copyright (c) Oracle Corporation 1994, 1996.  All rights reserved.
SQL*Loader-601: INSERTIvVgꍇA\͋łȂ΂Ȃ܂B\:PERSONŃG[܂B


p.291
 (first_name position (01:14) char,
  surname    position (15:28) char,
  clssn      position (29:36) char,
  hire_date  position (40:46) date 'YYMMDD')


BORIS         ABBEFLANTRO  AU2     830101
NANCY         BESDESMITH   MX      840926
FRANCIS       DEFWAYNO     DX      860422
NORMAN        NADROJIAN    CR5     860422


(first_name position (01:14) char,
 surname    position (15:28) char,
 clssn      position (29:36) char,
 hire_date  position (37:42) date 'YYMMDD')


p.292
BORIS         ABBEFLANTRO   AU2     831501
NORMAN        NADROJIAN     MX      860422
NANCY         BESDESMITH    DX      840926
FRANCIS       DEFWAYNO      CR5     870229
R[h:1͋ۂ܂B- \:STUFF,:CREATEDŃG[܂B
ORA-01843:w肵łB

R[h:4͋ۂ܂B- \:STUFF,:CREATEDŃG[܂B
ORA-01847:w肵ɑ΂ētłB


p.292-293
 1 SQL*Loader: Release 8.0.3.1 - Production on Fri Mar 12 10:44:14 1998
 2    Copyright (c) Oracle Corporation 1994, 1997.  All rights reserved.
 3    t@C:     person.ctl
 4    f[^Et@C: person.dat
 5    sǃt@C:   person.bad
 6    pt@C:   wȂ
 7    (R[hۂ͂܂ł܂B)
 8    [h:         ALL
 9    XLbv:     0
10    eG[:     50
11    Bind array:     64 rows, maximum of 65536 bytes
12    p:   wȂ
13    gppX:      ]^
14    \:PERSONNEL̓[h܂B (ׂĂ̘_R[h)
15    ̕\ɊւẮuINSERTvIvVɂď܂B
16                            u         ͂  f[^^
17    -------------------------- ---------- ----- ---- ---- ---------
18    FIRST_NAME                      1:14    14            CHARACTER            
19    SURNAME                        15:28    14            CHARACTER            
20    CLSSN                          29:36     8            CHARACTER            
21    HIRE_DATE                      37:42     6            DATE YYMMDD          
22    \uPERSONNELv:
23    2609 s[hł܂B
24    0 s̓f[^EG[ɂċۂ܂B
25    0 sWHEÑG[ɂċۂ܂B
26    0 s͊e񂪋̂ߖ܂B
27    Space allocated for bind array:                3584oCg(64 s)
28    ̑Ɋ蓖Ăꂽ̈:                           52603oCg
29    ǂݔ΂ꂽS_R[h:             0
30    ǂݍ܂ꂽS_R[h:               2609
31    ۂꂽS_R[h:                0
32    pꂽS_R[h:                0
33    sJn:  Fri Mar 12 10:44:14 1996
34    sI:  Fri Mar 12 10:44:16 1996
35    s:             00:00:02.12
36    CPU:           00:00:00.54


p.294
R[h:222͋ۂ܂B- \:PERSONNELŃG[܂B
ORA-00001: Ӑ:PERSON.U_FIRST_LASTɔĂ܂B


R[h:87͋ۂ܂B- \:PERSONNELŃG[܂B
ORA-01400: s}ŕK{(NOT NULL)ɒlw肳ĂȂNULL}Ă܂B


R[h:1189͋ۂ܂B- \:PERSONNELC:HIRE_DATEŃG[܂B
ORA-01843: w肵łB


\uPERSONNELv
2903 s[hł܂B
3 s̓f[^EG[ɂċۂ܂B
0 sWHEÑG[ɂċۂ܂B
0 s͊e񂪋̂ߖ܂B


p.295
FWUFFEROO     DES           FE8     851429


p.296
load data
infile 'person.dat'
into table personnel
when clssn <> 'CR4'
(first_name position (01:14) char,
 surname    position (15:28) char,
 clssn      position (29:36) char,
 hire_date  position (37:42) date 'YYMMDD')



Chapter11
p.307
book_number                          varchar2(20)
publisher                            varchar2(40)
year_printed                         varchar2(4)


p.309
select col1, col2, col3, col4
  from PORTLAND             /* ̏ꍇOR */
 where col5 > col6;


select col1, col2, col3, col4
  from PORTLAND             /* ̏ꍇME */
 where col5 > col6;


p.310
select col1, col2, col3, col4
  from SPRINGFIELD            /* ̏ꍇMA */
 where col5 > col6;
select col1, col2, col3, col4
  from SPRINGFIELD            /* ̏ꍇ */
 where col5 > col6;
select col1, col2, col3, col4
  from SPRINGFIELD            /* ̏ꍇMO */
 where col5 > col6;


p.311
select col1, col2, sum(col3)  /*p[g1 */
  from fiscal_year            /*p[g2 */
 where col1 > col2            /*p[g3 */
   and col1 > 10              /*p[g3 */
 group by col1, col2          /*p[g4 */
 order by col1;               /*p[g5 */


p.312
select col1, col2, sum(col3)
  from fiscal_year
 where col1 > col2
   and col1 > 10
 group by col1, col2
 order by col1;


select|col1, col2, sum(col3)
  from|fiscal_year
 where|col1 > col2
   and|col1 > 10
 group|by col1, col2
 order|by col1;


p.313
SELECT col1, col2
  FROM fiscal_year;


select COL1, COL2
  from FISCAL_YEAR;


select col1, col2, sum(col3)

select col1,col2,sum(col3)


select col1, col2
      ,col3


p.314
select col1, col2,
       col3


select col1, col2, sum(col3), col4, col5, sum(col6),
       col7, col8, col9, col10, col11, col 12, col13


 where
   and
   and
   and
   and


p.315
select col1, col2, sum(col3)


where x = y
  and d = c
  and j = p


where x=y
  and d=c
  and j=p


p.316-317
rem *************** Example #1 ******************

rem * 񃊃XgdecodéC8ɖ߂ĊJn܂D́Cdecode
rem * ̃JbR̃R[h2sɕȂ悤ɂ邽߂łD
rem * eJ}̂Ƃɂ͋󔒂1Cdecode̎n܂̃JbȒOɂ͋󔒂
rem * ܂D
select fy_code, prd.prod_num, prd.sdesc_e, proj_num, prj.sdesc_e,
       comm_amt, comm_amt*1.10,
       decode(fiscal, 'Y', 'multi', 'single'), bud_class
  from prod prd, project prj, bud_mast bud, commitment
 where prd.prod_num = prj.prod_num
   and bud.rpt_cat = prd.rpt_cat
 order by fy_code, prd.prod_num;

rem *************** Example #2 ******************

select max('&1'), max('&2'), max(sysdate), max('DETAIL'), max(a.sr),
       b.sdesc_e,
       /*  JbȒOɂ͋󔒂܂D         */
       /*  eJ}̑Oɂ͋󔒂͂܂񂪁C      */
       /*  ̂Ƃɋ󔒂1܂D           */
       nvl(round(sum(nvl(a.comm_amt, 0)), 0), 0),
       nvl(round(sum(nvl(a.precomm_amt, 0)), 0), 0),
       nvl(round(sum(nvl(a.budget_orig, 0)), 0), 0),
       nvl(round(sum(nvl(a.budget_rev,0)), 0), 0),
       nvl(round(sum(nvl(a.exp_accr, 0)), 0),0),
       nvl(round(sum(nvl(a.exp_accr_comm, 0)), 0), 0),
       nvl(round(sum(nvl(a.free_bal, 0)), 0), 0),
       nvl(round(sum(nvl(a.adj_bal, 0)), 0), 0)
  from fms_financials a, fms_control_obj b
 where b.cobj_type = 'SR'        /* es̍ŏ̃[h́C    */
   and b.cobj_code = a.sr        /* 8ŏI܂D       */
   and b.fy_code = '&2'
   and a.repname = '&1'          /* whereandśC   */
   and a.fy_code = '&2'          /* ׂđĂ܂D   */
   and a.qualifier = 'SRDETAIL'
 group by a.sr,b.sdesc_e;


p.317
create or replace procedure overdue_notice (borrower_ident char) as
  1_book_name     varchar2(40);
  1_book_ident    number;
  1_due_date      date
  -- ݂o߂{̐𒲂ׂB
  cursor mycur is
    select book_name, book_ident, due_date
      from overdue_notes
     where borrower_id = book_ident;
  begin
    open mycur;
    fetch mycur into 1_book_name, 1_book_ident, 1_due_date;
    while mycur%found loop
      -- ʒm쐬
       insert into overdue_notes
@@@@@@ values (book_ident, book_name, borrower_ident,
                   book_title, was_due, sysdate, user);
       fetch mycur into 1_book_name, 1_book_ident, 1_due_date;
    end loop;
  end;
/


p.318
create or replace


-- PL/SQLɑ
if sysdate - :due_back >= 6 then
   overdue_notice (boor_ident);
end if;


p.319
-- SQLɑ
execute overdue_notice ('A45R')


p.319-320
SQL> create or replace function is_yorn (in_char varchar2) return boolean is
  2    if in_char = 'Y' then
  3       return true;
  4    else
  5       return false;
  6    end;
  7  /
Function created.
SQL> select last_name, first_name, cdesc_e, c_inc_date
  2    from person a, cstat b
  3   where a.c_code = b.c_code
  4     and is_yorn(a.status) = 'Y'
  5     and a.pin = 100782;
LAST_NAME          FIRST_NAME          CDESC_E          INC_DATE
------------------ ------------------- ---------------- -----------
Phipps             Susan               Indet.           05-JUL-1998




Chapter12
p.328
sF1ŃG[܂B
ORA-00979: GROUP BY̎܂B


p.329
{main query text} where {condition}
          ({sub query text});


select last_name,sales
  from customer
 where state_cd =
         (select max(state_cd)
            from state);


p.330
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
rem * XvbhV[gf[^vO쐬܂D
SQL> set heading off
SQL> set pagesize 0
SQL> set feedback off
SQL> set echo off
SQL> spool out.dat 
SQL> /* o̓f[^t@CɒPp1zu邽߂ɂ́C */
SQL> /* 4̒PpgpKv܂D            */
SQL> select ''''||last_name||''''||','||''''||
  2  state_cd||''''||','||''''||sales
  3  from customer; 
SQL> spool off


p.330-331
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> set heading off
SQL> set pagesize 0
SQL> set feedback off
SQL> trimspool on
SQL> set echo off
SQL> spool out.dat
SQL> /* 2̒Pp̊Ԃɓdp1zuďo̓f[^t@C */
SQL> /* dp1zuƂ@ɒڂĂD             */
SQL> select '"'||last_name||'","'||state_cd||'",'||sales
  2  from customer;
SQL> spool off


p.331
'Teplow','MA',23445.67
'Abbey','CA',6969.96
'Porter','CA',6989.99
'Martin','CA',2345.45
'Laursen','CA',34.34
'Bambi','CA',1234.55
'McGraw','NJ',123.45


"Teplow","MA",23445.67
"Abbey","CA",6969.96
"Porter","CA",6989.99
"Martin","CA",2345.45
"Laursen","CA",34.34
"Bambi","CA",1234.55
"McGraw","NJ",123.45


p.332
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> set heading off
SQL> set pagesize 0
SQL> set feedback off
SQL> set echo off
SQL> spool out.sql
SQL> select 'set pagesize 55' from dual;
SQL> select 'grant select on '||table_name||'  to public;' 
  2  from user_tables;
SQL> spool off


p.333
set pagesize 55
grant select on CUSTOMER to public;
grant select on STATE to public;


decode(column_name,comparison,action,comparison, action,. . . else action)


column region format a20 heading 'Region'
column sales  format 999,999,999,999,999.99
compute sum of sales on report
compute sum of sales on region
break on report on region
select decode(state_cd, 'MA', 'East',
                        'NJ', 'East',
                        'CA', 'West',
                        'Middle of River'),sales
from customer
order by 1;


p.334
if state_cd = 'MA' then
   display 'East'
elsif state_cd = 'NJ' then
   display 'East'
elsif state_cd = 'CA' then
   display 'West'
else
   display 'Middle of River';


define rpt_cd = "MA"
select sales from customer where state_cd = '&rpt_cd';


p.335
DEFINE RPT_CD          = "MA" (CHAR)
DEFINE SALES_AMT       = "18000" (CHAR)
DEFINE CUST_START      = "A" (CHAR)


DEFINE SALES_AMT       = "18000" (CHAR)


V{Fsales_amt͒`Ă܂B


p.336
rptcolɑ΂Ēl: sales
   1: select state_cd, avg(&&rptcol), max(&&rptcol) . . .
V   1: select state_cd, avg(sales), max(sales) from . . .
ST AVG(SALES) MAX(SALES)
-- ---------- ----------
CA   3514.858    6989.99
MA   23445.67   23445.67
NJ     123.45     123.45


clear screen
prompt *******************************************
prompt ׂĂ̕\\ɂ"ALL"Ɠ͂ĂB
prompt ܂͕\̖Öꕔ͂ĂB
accept tname prompt "ALL܂͕\̖Öꕔ:"
select table_name
  from user_tables
 where table_name like '%&tname%'
    or upper('&tname') = 'ALL';


p.336-337
*******************************************
ׂĂ̕\\ɂ"ALL"Ɠ͂ĂB
܂͕\̖Öꕔ͂ĂB
ALL܂͕\̖Öꕔ:ALL
   3:  where table_name like '%&tname%'
V   3:  where table_name like '%ALL%'
   4:         or upper('&tname') = 'ALL')
V   4:         or upper('ALL') = 'ALL')
TABLE_NAME 
------------------------------
CUSTOMER
STATE


p.337
select state_cd, sales from customer
 where sales > &1 
   and state_cd = '&2';


sqlplus username/password @test 1000 CA


   1: select state_cd, sales from customer where sales > &1
V   1: select state_cd, sales from customer where sales > 1000
   2: and state_cd = '&2'
V   2: and state_cd = 'CA'

ST      SALES
-- ----------
CA    6969.96
CA    6989.99
CA    2345.45
CA    1234.55


p.338
start test.sql 1000 CA


p.339
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> -- Oɐݒ肵XgA܂B
SQL> -- WƂ͈قȂAt@C̖ɂ
SQL> -- ".cmd" ƂeLXgtKv܂B
@D:\ORANT\DATABASE\resenv.cmd


p.340
set linesize 62
repheader left 'Michael Abbey Systems International Inc.' - skip center -
'An Oracle Systems Consulting Company' -
right 'Page: ' format 999 sql.pno - 
skip center bold 'Customer Report'


Michael Abbey Systems International Inc.
              An Oracle Systems Consulting Company  Page:   1
                 Customer Report


p.341-342
SQL> column sales   format 999,999,999.99
SQL> select sales from customer;
Michael Abbey Systems International Inc.
     An Oracle Systems Consulting Company            Page:    1
                       Customer Report
          SALES
---------------
      23,445.67
       6,969.96
       6,989.99
       2,345.45
          34.34
       1,234.55
         123.45
7 R[hI܂B


p.342
SQL> column sales       format $099999
SQL> select sales from customer;
Michael Abbey Systems International Inc.
     An Oracle Systems Consulting Company            Page:    1
                       Customer Report
SALES
--------
 $023446
 $006970
 $006990
 $002345
 $000034
 $001235
 $000123
7 R[hI܂B
SQL>


p.344
column state_name justify right
          STATE_NAME
--------------------
Massachusetts
California
NewJersey
col state_name justify center

     STATE_NAME
--------------------
Massachusetts
California
NewJersey


p.344-345
column state_cd new_value rpt_cd
repheader left 'STATE CD: ' rpt_cd skip 1
break on state_cd skip page
STATE CD: CA
ST LAST_NAM SALES
-- -------- ------------
CA Abbey           6,970
   Porter          6,990
   Martin          2,345
   Bambi           1,235
   Laursen            34

STATE CD: MA
ST LAST_NAM SALES
-- -------- ------------
MA Teplow         23,446

STATE CD: NJ
ST LAST_NAM SALES
-- -------- ------------
NJ McGraw            123

7 R[hI܂B 


p.345
column today new_value today_date
select to_char(sysdate,'HH24:MM:SS DD-Mon-YYYY') today
  from dual;
repheader center 'Michael Abbey Systems International Inc.' - 
       skip left today_date -
       right 'Page: ' format 999 sql.pno skip
select sales from customer;


Michael Abbey Systems International Inc.
14:03:22 26-Mar-1999                                 Page:    1

     SALES
----------
  23445.67
   6969.96
   6989.99
   2345.45
     34.34
   1234.55
    123.45


p.346
select * from x;

COL
---
1
2
3
4
5
6
6 R[hI܂B
select * from y;
COL
---
5
6
7
3 R[hI܂B


p.346-347
COL
---
1
2
3
4
5
6
7
7 R[hI܂B


p.347
COL
---
5
6


COL
---
1
2
3
4


select * from x union select * from z
       *
sF1ŃG[܂B
ORA-01790: ɑΉ鎮Ɠf[^^Ȃ΂Ȃ܂B



p.348
rem ****************
rem * setup.sql
rem ****************
rem ̃XNvgɂ́CʓIȃZbgAbv
rem sqlXNvgɎgptH[}bgɊւ񂪊܂܂Ă܂D
rem Michael Corey    05/23/98   쐬
rem Michael Abbey    10/23/98   y[WTCY60ɕύX
rem                             Vv^p
rem                             5sǉ 
rem WZbgAbv
set echo off
set pagesize 60
set linesize 80
rem *************************************
rem * ʓIȗ`̃tH[}bg
rem *************************************
column bytes heading 'Bytes' format 999,999,999.99
column kbytes heading 'K Bytes' format 999,999,999.99
column less1 heading 'Under 1|Minute' format 999,990
column sales heading 'Sales|Ytd' format 999,999,999,999,999.99
state_cd heading 'St|Cd' format a2 
state_name heading 'State|Name' format a20 truncate
column sum(sales) heading 'Sales|Ytd' format 999,999,999,999,999.99
column sum(bytes) heading 'Bytes'      format 999,999,999.99
column table_name heading 'Table|Name' format a20 wrap
rem ********************
rem * XNvgI
rem ********************


p.349
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> rem *****************************************
SQL> rem *    customer.sql
SQL> rem *****************************************
SQL> rem * ̃|[g͌ڋq̃Xg쐬܂
SQL> rem * Mike Corey  10/23/98
SQL> rem * Zg|WgWZbgAbvĂяo
SQL> @D:\ORANT\DATABASE\setup.sql
SQL> rem * |[g^Cgƃy[Wtb^[ݒ
SQL> repheader center 'Database Technologies Inc.' skip -
     center 'Customer Report'
SQL> repfooter center '*** customer.sql ***'


SQL> repfooter skip 2 center '**** my_report.sql ****'


p.350
1   select state_cd, last_name, sales
2   from customer
3*  order by 1


1*  select state_cd, last_name, sales


1*  select customer.state_cd, last_name, sales


1   select customer. state_cd, last_name, sales
2   from customer
3*  order by 1


p.351
1   select customer. state_cd, last_name, sales
2*  from customer


ST LAST_NAME
-- ------------------------------
MA Teplow
CA Abbey
CA Porter
CA Martin
CA Laursen
CA Bambi
NJ McGraw


p.352
select count(*)
  from mytab
 where cola is null;
select count(*)
  from mytab
where cola is not null;


COUNT(*)  COMM_AMT
--------  --------
      12      1200
       7      1700
       8



p.353
SQL> select count(*),nvl(comm_amt,0) from comm group by nvl(comm_amt,0);
 COUNT(*) NVL(COMM_AMT,0)
--------- ---------------
        8               0
       12            1200
        7            1700


SQL> select count(*) from comm where nvl(comm_amt,0) < 1700;
 COUNT(*)
---------
       20


p.353-354
SQL*PlusFRelease 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright(c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
connected to:
Oracle8 Server Release 8.0.3.0.1 - Production 
With the distributed, heterogeneous, replication, objects
and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL>
SQL> -- ' 'NLLlł͂Ȃ[\߁COracle8͓ł܂D
SQL>
SQL> select 12 from dual where null = '';
no rows selected
SQL>
SQL> -- Oracle8ł2''̊Ԃœr͎sł܂IӂĂI
SQL>
SQL> select 12 from dual where ''= '';
no rows selected
SQL> -- rNULLL[[hgēK؂ɃR[fBOĂ܂C
SQL> -- =L̍ENULL͂ǂӖłDӖ2̒lr邱Ƃ͂ł܂D
SQL> select 12 from dual where null = null ;
no rows selected
SQL>
SQL> -- ړǏʂ𓾂邽߂ɂ͂ꂪœKȕ@ł
SQL>
SQL> select 12 from dual where null is null;
       12
---------
SQL>
SQL> -- łnvl֐gpēƂĂ݂܂傤
SQL>
SQL> select 12 from dual where nvl(null,'X') = nvl(null,'X');
       12
---------
       12
SQL>



Chapter13
p.359
USER TABLES
num_rous       number
blocks         number
chain_cnt      number
avg_row_len    number
degree         varchar2(10)  -- p̒x
sample_size    number        -- v̎W̃TṽTCY
last_analyzed  date
partitioned    varchar2(3)   -- p[eBVƍœKقȂꍇ܂

USER INDEXES
distinct_keys             number
avg_leaf_blocks_per_key   number  -- ̍sƎ̍śCCBO
avg_data_blocks_per_key   number  -- ڂ̕z̃ACfA񋟂܂
status                    varchar2(8)
num_rows                  number
sample_size               number  -- ̓vWꍇɎw
last_analyzed             date
degree                    varchar2(40)
partitioned               varchar2(3)

USER TAB COLUMNS
num_distinct    number
low_value       raw(32)  -- ڒC2Ԗڂɏl
high_value      raw(32)  -- ڒC2Ԗڂɑ傫l
num_nulls       number   -- Oracle8NULL̓vW (Oracle 7.3l)
last_analyzed   date
sample_size     number   -- vZł͂Ȃ


p.360
ORA-00054  \[XrW[ŁCNOWAIT w肳Ă܂D
:       LOCK TABLER}h܂SELECT FOR UPDATER}hɂ̓\[Xgpł
            Ȃ߁CNOWAITL[[hɂăR}hvvgɖ߂܂D
:     ΂炭ĂxR}hĂD܂́CNOWAITL[[hg
           킸ɃR}h͂ĂD


analyze table lumberjack estimate statistcs sample 20 percent;
analyze table mountie estimate statistics sample 20 percent;
analyze table suspenders estimate statistics sample 20 percent;


analyze index lumberjack_pk compute statistics;
analyze index mountie_pk compute statistics;
analyze index suspenders_pk compute statistics;


p.360-361
SQL*Plus: Release 4.0.3.0.0 - Production on Mon May 12 20:43:31 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL>
SQL> -- otFb`sɊւtB[hobN̐C 
SQL> -- ʂւ̃R}h̃GR[ɂāC
SQL> -- SQL*Plus̊𒲐܂D
SQL> set pages 0 feed off echo off
SQL> -- ana_all.sqlւ̏o͂Lv`
SQL> spool ana_all.sql
SQL> prompt set echo on feed on
SQL> spool ana_all
SQL> select 'analyze table '||owner||'.'||table||
  2    ' estimate statistics sample 20%;'
  3    from all_tables
  4    where owner = upper('&1');
SQL> 
SQL> select 'analyze index '||owner||'.'||
  2    index_name||' compute statistics;'
  3    from all_indexes
  4    where owner = upper('&1');
SQL> spool off


p.361
execute dbms_utility.analyze_schema ('MALLIA','ESTIMATE',null,20); 


p.362
execute dbms_ddl.analyze_object ('INDEX','SHAUNA_2','COMPUTE');


p.363
SQL*Plus: Release 4.0.3.0.0 - Production on Mon May 12 20:43:31 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> alter session set optimizer_goal = ALL_ROWS;
Session altered.
... ...
... ... Whole bunch of SQL statements
... ...
SQL> alter session set optimizer goal = CHOOSE;
Session altered.
SQL>


select /*+ choose */ name, address . . . .


select --+ choose
  name, address . . . .


p.364
analyze table bianca estimate statistics sample 20 percent;
analyze table bianca estimate statistics sample 20 percent
/


analyze index flounder_pk compute statistics
analyze index bowie_david compute statistics


p.365
select *
  from fin_mast
 where rowid = '008A4.0002.009D';


Oracle error occurred while executing KEY-COMMIT trigger:
update tabA set name=:nam,address=:addr ........ where rowid=:rowid


p.366
select *
  from street_master
 where street_name = 'ROBSON'
   and house_number = '2802';


select *
  from street_master
 where street_name = 'ROBSON'
   and house_number >= '2802';


select *
  from street_master
 where street_name = 'ROBSON';


p.367
select max_out
  from fin_mast
 where fin_id = '1234M';


select max_out
  from fin_mast
 where fin_id = '1234M'
    or fin_rel is not null;


p.369
select a.class_group, descr, sum(mon_amt + tue_amt + wed_amt + thu_amt + 
                                 fri_amt + sat_amt + sun_amt)
  from timesheets a, classes b
 where a.class_group like 'CS%'
   and a.class_group = b.class_group
 group by a.class_group, descr;


p.373
select count(*) into :people.class_count from classif where class_1 = 
:people.classif;
select count(*) into :people.class_count from classif where class_1 = 
:people.classif;
select last_name||' '||first_name into :person.full_name from person;
select last_name||' '||first_name into :person.fnam from person;


p.375
alter table person add constraint person_pk primary key (id)
  using index storage (initial 1m next 1m pctincrease 0)
  tablespace prd_indexes;


select last_name,first_name,salary
  from person
where id = 289;


p.376
rem *  The sql_text column is 1,000 characters wide so
rem *  set it to 80 for this display
col sql_text format a80
select sql_text from v$sqlarea where lower(sql_text)
         like lower('&text'||'%');


SQL>  select sql_text from v$sqlarea where lower(sql_text) like 
   2>        lower('&text'||'%');
Enter value for text: select surname


 2: select sql_text from v$sqlarea where lower(sql_text) like
lower('&text'||'%')
V 2: select sql_text from v$sqlarea where lower(sql_text) like
lower('last_name'||'%')
SQL_TEXT
--------------------------------------------------------------------
select surname||' '||first_name into :person.fname from person
select surname||' '||first_name into :person.fnam from person
SQL>


p.377
select last_name, first_name, descr
  from person a, classn b
 where pin = 123897
   and a.clssn = b.clssn;


explain plan set statement_id = 'ST_ID' for
select last_name, first_name, descr
  from person a, classn b
 where pin = 123897
   and a.clssn = b.clssn;


p.378
rem *  File name: expl.sql
spool expl
select decode(id,0,'',
       lpad(' ',2*(level-1))||level||'.'||position)||' '||
       operation||' '||options||' '||object_name||' '||
       object_type||' '||
       decode(id,0,'Cost = '||position) Query_plan
  from plan_table
connect by prior id = parent_id
    and statement_id = upper('&1')
  start with id = 0 and statement_id = upper('&1');
spool off


p.378
SELECT STATEMENT    Cost = 13
  2.0 NESTED LOOPS
    3.1 TABLE ACCESS FULL PERSON
    3.2 TABLE ACCESS BY ROWID CLASSN
      4.1 INDEX RANGE SCAN CLASSN_1 NON-UNIQUE


p.379
SQL> set autotrace
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]]
[STAT[ISTICS]]


SQL> autotrace on
Unable to verify PLAN_TABLE format or existence
Error enabling EXPLAIN report


p.379-380
SQL*Plus: Release 4.0.3.0.0 - Production on Mon May 12 20:43:31 1998
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> select last_name,first_name,desce
   2  from person,dept
   3  where person.dept = dept.dept_no
   4  and pin = (select min(pin) from person);

LAST_NAME            FIRST_NAME           DESCE
-------------------- -------------------- --------------------
Abbey                Michael              Eastern Region
Execution Plan
--------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1   0    NESTED LOOPS
   2   1      TABLE ACCESS (BY ROWID) OF 'PERSON'
   3   2        INDEX (UNIQUE SCAN) OF 'PERSON_PK' (UNIQUE)
   4   3          SORT (AGGREGATE)
   5   4            INDEX (FULL SCAN) OF 'PERSON_PK' (UNIQUE)
   6   1      TABLE ACCESS (BY ROWID) OF 'DEPT'
   7   6        INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
Statistics
-------------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        197  bytes sent via SQL*Net to client
        286  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>


p.381-382
****************************************************************************
select ename,loc,hiredate
from emp a, dept b where a.deptno = b.deptno 

call      count       cpu    elapsed    disk    query  current       rows
------  -------  --------  --------- -------  -------  -------   --------
Parse         1      0.00       0.06       0        0        0          0
Execute       1      0.00       0.00       0        0        0          0
Fetch         1      0.00       0.06       0       57        2         14
Rows     Execution Plan
-------  ------------------------------------------------------
      0  SELECT STATEMENT
     14    NESTED LOOPS
     14      TABLE ACCESS (FULL) OF 'EMP'
     14      TABLE ACCESS (BY ROWID) OF 'DEPT'
     28        INDEX (RANGE SCAN) OF 'DEPT_1' (NON-UNIQUE)
****************************************************************************


p.382-383
D:\ORANT\RDBMS80\TRACE>dir
 Volume in drive D is DRIVE DEE
 Volume Serial Number is 2627-1D06
 Directory of D:\ORANT\RDBMS80\TRACE
04/20/97  09:21a        <DIR>          .
04/20/97  09:21a        <DIR>          . .
04/25/97  11:47a                26,092 ORA00076.TRC
04/20/97  11:35p                 1,559 ORA00206.TRC
05/12/97  08:25p                76,319 orclALRT.LOG
05/12/97  08:25p                 5,746 orclCKPT.TRC
05/12/97  08:25p                 5,746 orclDBWR.TRC
05/12/97  08:25p                 5,746 orclLGWR.TRC
05/11/97  02:47p                 3,211 orclPMON.TRC
05/12/97  08:25p                 5,746 orclRECO.TRC
05/12/97  08:25p                 5,746 orclSMON.TRC
05/12/97  08:25p                 5,239 orclSNP0.TRC
              12 File(s)       141,150 bytes
                         1,523,089,408 bytes free
D:\ORANT\RDBMS80\TRACE>


alter session set sql_trace = true;
!


jrstocks  9824     1 0 15:44:14 tty0p5  0:00 -ksh
jrstocks 11276  2805 0 17:16:21 ttyp7   0:00 sqlplus
jrstocks 11277 11276 0 17:16:21 ?       0:00 oracledev (DESCRIPTION=(LOC)



Chapter14
p.402
Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation. All rights reserved.

Personal Oracle8 Release 8.0.3.0.0 - Production
With the partitioning option
PL/SQL Release 8.0.3.0.0 - Production

SVRMGR>


Connected to an idle instance.
SVRMGR>


ڑ܂B
SVRMGR>


Connected.
SVRMGR>


p.403
startup nomount
alter database mount;
alter database open;


connect internal
startup
exit


p.404
ORA-01033: ORACLE̋N܂͒~słB


SVRMGR> shutdown
_


p.405
ORA-01013: [U[ɂČsIy[V̎v܂B


p.407
Server ManagerI܂B



Chapter15
p.420
D:\ORANT\BIN> exp80
Export: Release 8.0.3.0.1 - Production on Tue Nov  19 11:32:25 1998
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.
[U[: fin_man/drowssap
ڑ: Oracle8 Server Release 8.0.3.0.1 - Production Release
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.1 - Production
zutFb`Eobt@ETCY: 4096 > 102400
GNX|[gt@C: EXPDAT.DMP >
[hIFi1jEiSf[^x[Xj, i2jUi[U[j, i3jTi\j:i2jU > 3
\f[^GNX|[g܂H(yes/no): yes >
GNXegk܂H(yes/no): yes >
Export done in WE8ISO8859P1 character set and WE8ISO8859P1
       NCHAR character set
w肳ꂽ\GNX|[gĂ܂ ...
GNX|[g\: (RETURNŏI) >e_master
.\GNX|[g               E_MASTER          122sGNX|[g܂B
GNX|[g\: (RETURNŏI) >
GNX|[g͌xȂŐ܂B


p.421
D:\ORANT\BIN> imp80
Import: Release 8.0.3.0.1 - Production on Fri Apr 15 11:53:59 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
[U[: fin_man/drowssap
ڑ: Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Producion
C|[gt@C: EXPDAT.DMP >
}obt@ETCY͂Ă(ŏl:4096)B30720>
GNX|[gEt@CEXPORT:V08.00.03쐬܂B
C|[gEt@C̓êݕ\܂H (yes/no): no >
IuWFNgɂ쐬G[͖܂H (yes/no): yes >
GRANT()C|[g܂H(yes/no): yes >
\f[^C|[g܂H(yes/no): yes >
SGNX|[gEt@CC|[g܂H(yes/no): yes >
. FIN_MAÑIuWFNgFIN_MANɃC|[gĂ܂...
.. \C|[g "E_MASTER"        122sC|[g܂B
C|[g͌xȂŐ܂B


p.423
SGNX|[gEt@CC|[g܂H(yes/no): yes >


D:/ORANT/BIN> imp80
Import: Release 8.0.3.0.1 - Production on Fri Apr 15 11:53:59 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
[U[: other_user/opass
ڑ: Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Producion
C|[gt@C: EXPDAT.DMP >
}obt@ETCY͂Ă(ŏl:4096)B30720>
GNX|[gEt@CEXPORT:V08.00.03쐬܂B
C|[gEt@C̓êݕ\܂H (yes/no): no >
IuWFNgɂ쐬G[͖܂H (yes/no): no > yes
GRANT()C|[g܂H(yes/no): yes > yes
\f[^C|[g܂H(yes/no): yes > yes
SGNX|[gEt@CC|[g܂H(yes/no): no > no
[U[: finman/drowssap
\͂ĂB(iEXgׂ͂Ă̕\Ӗ܂B)
\܂ . ͂ĂB: PER-MAST
\܂ . ͂ĂB: .
.  PER_MAST̃IuWFNgFINMANɃC|[gĂ܂...
.. \C|[g "PER_MAST"         2134sC|[g܂B
C|[g͌xȂŐ܂B


p.424
D:\ORANT\BIN> imp80
[U[: fin_mast/drowssap
ڑ: Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Producion
C|[gt@C: EXPDAT.DMP >
}obt@ETCY͂Ă(ŏl:4096)B30720>
GNX|[gEt@CEXPORT:V08.00.03쐬܂B
C|[gEt@C̓êݕ\܂H (yes/no): no >
IuWFNgɂ쐬G[͖܂H (yes/no): no >
GRANT()C|[g܂H(yes/no): yes >
\f[^C|[g܂H(yes/no): yes >
SGNX|[gEt@CC|[g܂H(yes/no): no > yes
PER_MAST̃IuWFNgFIN_MASTɃC|[gĂ܂...
IMP-00015: IuWFNgɑ݂Ă邽ߎ͎̕s܂B
"CREATE TABLE "DEPT" ...iȉj
xăC|[g͏I܂B


p.425
exp80 keyword1=value1 keyword2=value2 keyword3=value3


p.425-426
D:\ORANT\BIN> exp80 help=y
Export: Release 8.0.3.0.1 - Production on Fri Apr 5 12:58:52 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
EXP͂ƃGNX|[gp[^͂v܂B
R}hɑă[U[/pX[hw肵ĂB
      ͗: EXP SCOTT/TIGER
܂AEXPR}hɈ͂邱ƂɂāAGNX|[g̎s
䂷邱Ƃł܂Bp[^̎wɂ̓L[[hgp܂B
      `:   EXP KEYWORD=value or KEYWORD=(value1,value2....valueN)
      ͗: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
L[[h    e(ftHgl)        L[[h     e(ftHgl)
-------------------------------------------------------------------------
USERID        [U[/pX[h     FULL           SGNX|[g[h
BUFFER        f[^Eobt@TCY    OWNER          L郆[U[EXg
FILE          o̓t@C(EXPDAT.DMP)  TABLES         \Xg
COMPRESS      GNXegk(Y)       RECORDLENGTH   t@C̃R[h
GRANTS        EXPORT(Y)           INCTYPE        EXPORT̎
INDEXES       EXPORT(Y)           RECORD         EXPORTDB\ɋL^(Y)
ROWS          \f[^sEXPORT(Y)     PARFILE        p[^Et@C
CONSTRAINTS   \ɑ΂鐧EXPORT(Y) CONSISTENT     \݂̈ѐ(Y)
LOG           ʏo͂̃OEt@C  STATISTICS     IuWFNg̕
DIRECT        direct path (N)


p.427
imp80 keyword1=value1 keyword2=value2 keyword3=value3


p.428
D:\ORANT\BIN> imp80 help=y
Import : Release 8.0.3.0.1 - Production on Fri Apr 5 13:15:10 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
IMP͂ƃGNX|[gp[^͂v܂B
R}hɑă[U[/pX[hw肵ĂB
      ͗:  IMP SCOTT/TIGER
܂AIMPR}hɈ͂邱ƂɂāAC|[g̎s
䂷邱Ƃł܂Bp[^̎wɂ̓L[[hgp܂B
      `:    IMP KEYWORD=value or KEYWORD=(value1,value2....valueN)
      ͗:  IMP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
L[[h    e(ftHgl)         L[[h      e(ftHgl)
-------------------------------------------------------------------------
USERID        [U[/pX[h       FULL           SC|[g[h(N)
BUFFER        f[^Eobt@TCY      FROMUSER       L郆[U[EXg
FILE          o̓t@C(EXPDAT.DMP)    TOUSER         IMP̃[U[EXg
SHOW          EXPt@Ce\(N)      TABLES         \Xg
IGNORE        쐬G[(N)         RECORDLENGTH   t@C̃R[h
GRANTS        IMPORT(Y)             INCTYPE        EXPORT̎
INDEXES       IMPORT(Y)             COMMIT         z}ɃR~bg(N)
ROWS          \f[^sIMPORT(Y)       PARFILE        p[^Et@C
LOG           ʏo͂̃OEt@C
DESTROY       \̈f[^Et@C̏㏑(N)
INDEXFILE     w肳ꂽt@C֕\/̏
CHARSET       GNX|[gEt@C(NLS_LANG)̕Zbg
C|[g͌xȂŏI܂B


p.430
imp80 parfile=my.parfile


exp80 parfile=my.parfile


userid=fin_man/drowssap tables=(fin_mast,assignment)
buffer=102400 compress=y grants=y


userid=fin_man/drowssap fromuser=fin_man touser=per_man
buffer=102400 grants=y


userid=fin_man/drowssap tables=(fin_mast,assignment)
buffer=102400 grants=y
compress=y


compress=y
userid=fin_man/drowssap
tables=(fin_mast,assignment)
buffer=102400
grants=y


p.431
exp80 fin_man/drowssap buffer=102400 compress=n


exp80 fin_man/drowssap buffer=102400 rows=n


p.432
exp80 userid=fin_man/drowssap tables=(permast,finmast,stockmast) file=fm.dmp


exp80 userid=fin_man/drowssap owner=(fin_man,per_man,acc_man)


p.433
exp80 userid=system/manager full=y grants=y indexes=y


imp80 userid=fin_man/drowssap tables=(permast,finmast,stockmast)


imp80 userid=fin_man/drowssap fromuser=(per_man,acc_man) 
touser=(per_man,acc_man) file=two_users


p.434
imp80 userid=system/manager full=y file=full_tst


p.436
exp80 system/manager file=export.dmp
tables=(scott.b:px, scott.b:py, mary.c, d:qb)


p.437
D:\ORANT\BIN> imp80 userid=/ full=y
Import: Release 8.0.3.0.1 - Production on Mon Nov 23 11:39:01 1998
Copyright (c) Oracle Corporation 1979, 1994, 1996.  All rights reserved.
IMP-00003: ORACLEG[:1034܂B
ORA-01034: ORACLE not available
ORA-07318: smsget: open error when opening sgadef.dbf file.
HP-UXG[: 2: t@C܂̓fBNg܂B
IMP-00021: operating system error - error code (dec 13, hex 0xD)
IMP-00000: G[߃C|[gI܂B


p.438
D:\ORANT\BIN> imp80 userid=userx/drowssap full=y
Import: Release 8.0.3.0.1 - Production on Mon Nov 23 11:39:01 1998
Copyright (c) Oracle Corporation 1979, 1994, 1996.  All rights reserved.
ڑ: Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
GNX|[gEt@CEXPORT:V08.00.03쐬܂B
IMP-00013: DBAʂDBAGNX|[gt@CC|[gł܂B
IMP-00021: operating system error - error code (dec 13, hex 0xD)
IMP-00000: G[߃C|[gI܂B


D:\ORANT\BIN> imp80 userid=/ full=y rows=n
exp userid=/ full=y rows=n
Import: Release 8.0.3.0.1 - Production on Mon Nov 23 11:39:01 1998
Copyright (c) Oracle Corporation 1979, 1994, 1996.  All rights reserved.
ڑ: Oracle8 Server Release 8.0.3.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
EXP-00023: Sf[^x[XEGNX|[g̎sDBAłȂ΂Ȃ܂B
EXP-00222:
System error message
(2)U(sers), or (3)T(ables): (2)U >
EXP-00030: Unexpected End-Of-File encountered while reading input
EXP-00222: 
System error message
EXP-00000: G[߃GNX|[gI܂B


p.439
exp80 userid=fin_man/drowssap owner=(userA,userB) tables=(table1A,table1B,table2B)


EXP-00026: p[^(TABLES,OWNER,FULL)͂Pwł܂B
EXP-00222: 
System error message 2
EXP-00000: G[߃GNX|[gI܂B


D:\ORANT\BIN> exp80 userid=/ rows=n file=partial
Import: Release 8.0.3.0.1 - Production on Mon Nov 23 11:39:01 1998
Copyright (c) Oracle Corporation 1979, 1994, 1996.  All rights reserved.
Connected to: Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
EXP-00035: Cannot specify Rows=N and Compress=Y
EXP-00222: System error message
EXP-00000: Export terminated unsuccessfully


p.439-440
D:\ORANT\BIN> exp80 userid=/ file=prod
Import: Release 8.0.3.0.1 - Production on Mon Nov 23 11:39:01 1998
Copyright (c) Oracle Corporation 1979, 1994, 1996.  All rights reserved.
Connected to: Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
Export file created by EXPORT:V08.00.03
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLE arguments
IMP-00021: operating system error - error code (dec 2, hex 0x2)
IMP-00000: Import terminated unsuccessfully


p.440
exp userid=/ tables=(table_q,table_r)
ksh: syntax error: '(' unexpected


exp userid=/ tables='(table_q,table_r)'


exp userid=/ tables=\(table_q,table_r\)


p.441
exp userid=ops$francesl/drowssap
Export: Release 8.0.3.0.0 - Production on Mon Nov 20 12:07:28 1999
Copyright (c) Oracle Corporation 1997.  All rights reserved.
Connected to: Oracle7 Server Release 8.0.3.0.0 - Production Release
PL/SQL Release 3.0.3.0 - Production
EXP-00004: [U܂̓pX[hłB
EXP-00222:
System error message 
Username:


exp userid=ops\$francesl/drowssap


exp userid='ops$francesl/drowssap'


userid=/ file=recs tables=(person,acc_rec,fin_mast) buffer=10240


userid=/ ignore=y file=frank buffer=102400 fromuser=userA touser=userB


p.442
userid=/
tables=(userA.table_1,userA.table_2,userA.table_3,userB.table_1,userB.table_2)


userid=userc/drowssap fromuser=(userA,userB) touser=(userC,userC)




Chaptyer16
p.446
ORA-01034: ORACLEgpł܂D
ORA-09243: smsget: SGAւ̘AɃG[܂D


p.451
EM Wizard Backup Tc1 Script Ver. 1.1
$SMP_USER/$SMP_PASSWORD@$SMP_SERVICE
"UP"
{\"ONLINE\"}
{{ {ROLLBACK_DATA} {USER_DATA} {TEMPORARY_DATA} }}
{{ {1} {1} {1} }}
{{ {D:\ORANT\DATABASE\RBS1ORCL.ORA} {D:\ORANT\DATABASE\USR1ORCL.ORA}
{D:\ORANT\DATABASE\TMP1ORCL.ORA} }}
{{{pkzip ora8nt %s}}}
CopyFilesUsingCommand


p.452
if database is in archivelog mode then
    if backup is turned on for selected tablespace
       loop until tablespace is taken out of backup mode
            write information destined for that tablespace to
                  the online redo logs
       end loop
       take tablespace out of backup mode
       dump appropriate information from redo logs to tablespace
            now out of backup mode
   else
      write information directly to appropriate tablespace data
            files as systems operate
   end if
else
   allow online tablespace backup with no intervention (thereby
         the DBA ends up writing an unusable backup)
end if


p.453
Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation. All rights reserved.

Personal Oracle8 Release 8.0.3.0.0 - Production
With the partitioning option
PL/SQL Release 8.0.3.0.0 - Production
SVRMGR> connect internal
pX[h:
ڑ܂B
SVRMGR> shutdown
f[^x[XN[Y܂B
f[^x[XfBX}Eg܂B
OracleCX^X~܂B
SVRMGR> startup mount
OracleCX^XN܂B
SVXeEO[ö       8030448oCg
          Fixed Size         44584oCg
       Variable Size       7510728oCg
    Database Buffers        409600oCg
        Redo Buffers         65536oCg
f[^x[X}Eg܂B
SVRMGR> alter database archivelog;
܂B
SVRMGR> alter database open;
܂B


p.454
SVRMGR> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\ORANT\RDBMS80\
Oldest online log sequence     36
Next log sequence to archive   37
Current log sequence           37
SVRMGR>


p.455
userid=system/manager full=y file=full_sysbuffer=102400 log=full_sys grants=y indexes=y


p.459
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle8 Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> -- ̃vOsɂ́Cf[^fBNVir[
SQL> -- v$parameterCv$logfileCv$dbfileCv$controlfile
SQL> -- IKv܂D
SQL> set pages 0 feed off echo off
SQL> col a new_value b
SQL> col c new_value d
SQL> select value a,sysdate c
  2    from v$parameter
  3    where name = 'db_name';
SQL> prompt g:
SQL> prompt cd \oradb\backups
SQL> spool cold.backup
SQL> prompt
SQL> prompt Cold backup for "&b" database on &d ...
SQL> prompt
SQL> prompt rem Redo logs
SQL> prompt
SQL> select 'copy '||member
  2    from v$logfile;
SQL> prompt
SQL> prompt rem Datafiles
SQL> prompt
SQL> select 'copy '||name
  2    from v$dbfile;
SQL> prompt
SQL> prompt rem Control files
SQL> prompt
SQL> select 'copy '||name
  2    from v$controlfile;
SQL> spool off
SQL> exit


p.460
Cold backup for "prd" database on 12-JUN-99...
Rem Redo logs
copy d:\orant\database\log1orcl.dbf
copy d:\orant\database\log2orcl,dbf
Rem Datafiles
copy d:\orant\database\dbslorcl.dbf
copy d:\orant\database\temporcl.dbf
copy d:\orant\database\usersorcl.dbf
copy d:\orant\database\toolsorcl.dbf
Rem Control files
copy d:\orant\database\ctl1orcl.dbf
copy d:\orant\database\ctl2orcl.dbf
ICizbgjobNAbv


p.461-462
SQL*Plus: Release 4.0.3.0.0 - Production on Sun May 11 12:20:12 1999
Copyright (c) Oracle Corporation 1979, 1994, 1996. All rights reserved.
Connected to:
Oracle Server Release 8.0.3.0.1 - Production
With the distributed, heterogeneous, replication, objects and parallel query options
PL/SQL Release 3.0.3.0.1 - Production
SQL> -- ̃vÓCRs[Oɕ\̈̃obNAbvs܂D
SQL> -- ̑́Cx1̕\̈Ŏs܂D
SQL> -- \̈obNAbv[hɂC̃f[^t@CRs[܂D
SQL> -- e\̈obNAbv邽тɃ`FbN|Cg𔭐ƁC
SQL> -- Oracleւ̓obNAbv̓o^𓯊ł܂D
SQL>
SQL> alter tablespace tools begin backup;
Tablespace altered.
SQL> $ copy d:\orant\database\d1\tools.dbf d:\backups\d1\tools1.dbf
SQL> alter tablespace tools end backup;
tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> alter tablespace temp begin backup;
Tablespace altered.
SQL> $ copy d:\orant\database\d1\temp.dbf d:\backups\d1\temp.dbf
SQL> alter tablespace temp end backup;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> alter tablespace rollback_segs begin backup;
Tablespace altered.
SQL> $ copy d:\orant\database\d1\rbs1.dbf d:\backups\d1\rbs1.dbf
SQL> $ copy d:\orant\database\d1\rbs2.dbf d:\backups\d1\rbs2.dbf
SQL> alter tablespace rollback_segs end backup;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> $ copy d:\orant\database\d1\users1.dbf d:\backups\d1\users1.dbf
SQL> $ copy d:\orant\database\d1\users2.dbf d:\backups\d1\users2.dbf
SQL> alter tablespace users end backup;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> alter tablespace system begin backup;
Tablespace altered.
SQL> $ d:\orant\database\dbs1.dbf d:\backups\d1\dbs1.dbf
SQL> alter tablespace system end backup;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database backup controlfile to
  2       'd:\backups\d1\control_bkp' reuse;
Database altered.


p.462-463
Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation. All rights reserved.

Personal Oracle8 Release 8.0.3.0.0 - Production
With the partitioning option
PL/SQL Release 8.0.3.0.0 - Production
SVRMGR> connect internal
SVRMGR> spool scratch.log
SVRMGR> set echo on
SVRMGR> startup nomount pfile=d:\orant\database\initrac.ora
SVRMGR> create database prac
  2     datafile 'd:\orant\database\dbs1prac.dbf'            size 10m
  3     logfile  'd:\orant\database\log1prac.dbf'
  4              'd:\orant\database\log2prac.dbf'            size 300k
  5              maxlogfiles     20
  6              maxlogmembers    4
  7              maxdatafiles    30
  8              maxinstances     1
  9              maxloghistory  100;
܂B
SVRMGR> create rollback segment temp
  2        tablespace system
  3     storage (initial 50k minextents 10 maxextents 10);
܂B
SVRMGR> shutdown
SVRMGR> startup pfile=d:\orant\database\initprac.ora
 SVRMGR> alter tablespace system default storage (pctincrease 0);
SVRMGR> set echo off
SVRMGR> set termout off
SVRMGR> @d:\orant\rdbms80\admin\catalog.sql
SVRMGR> @d:\orant\rdbms80\admin\catexp.sql
SVRMGR> @d:\orant\rdbms80\admin\catldr.sql
SVRMGR> @d:\orant\rdbms80\admin\catproc.sql
SVRMGR> connect system/manager
SVRMGR> @d:\orant\rdbms80\admin\catdbsyn.sql
SVRMGR> connect internal
SVRMGR> shutdown


p.466
copy d:\sys\backups\rbook.dbf f:\ora8db\rbook.dbf
copy d:\sys\backups\users.dbf f:\ora8db\users.dbf
copy d:\sys\backups\tools.dbf f:\ora8db\tools.dbf
copy d:\sys\backups\temp.dbf f:\ora8db\temp.dbf
copy d:\sys\backups\dbs1book.dbf f:\ora8db\dbs1book.dbf


Log applied.
Media recovery complete.
SVRMGR>


p.467
ORA-00279: Change 10029 generated at 12/21/99 14:43:13 needed...
ORA-00289: Suggestion : d:\orant\arc\arch_399.arc
ORA-00280: Change 10029 for thread 1 is in sequence #399
ORA-00278: Logfile 'd:\orant\arc\arch_398.arc' no longer...
Log applied.
Media recovery complete.
SVRMGR>


ORA-00308: cannot open archived log 'd:\orant\arc\arch_387.arc
ORA-07360: sfifi: stat error, unable to obtain information...
HP-UX Error: 2: No such file or directory
SVRMGR>


arch_382.arc  arch_386.arc  arch_391.arc  arch_395.arc  arch_399.arc
arch_383.arc  arch_388.arc  arch_392.arc  arch_396.arc  arch_400.arc
arch_384.arc  arch_389.arc  arch_393.arc  arch_397.arc  arch_401.arc
arch_385.arc  arch_390.arc  arch_394.arc  arch_398.arc


p.468
RECID                                  NUMBER
STAMP                                  NUMBER
THREAD#                                NUMBER
SEQUENCE#                              NUMBER
FIRST_CHANGE#                          NUMBER
FIRST_TIME#                            DATE
NEXT_CHANGE#                           NUMBER


     FIRST_CHANGE#-1
----------------------
                 9999


p.469
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}


ORA-00289: Suggestion : d:\orant\arc\arch_387.arc
ORA-00278: Logfile 'd:\orant\arc\arch_386.arc' no...
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.


p.470
TIME
------------------
12/21/98 14:42:04


ORA-00280: Change 9999 for thread 1 is in sequence #387
ORA-00278: Logfile 'd:\orant\arc\arch_386.arc' no...
Log applied.
Media recovery complete.


p.471
ORA-00283: Recovery session canceled due to errors
ORA-01190: control file or data file 1 is...RESETLOGS
ORA-01110: data file 1: 'd:\orant\arc\dbs1book.dbf'
SVRMGR>


p.471-472
set serveroutput on size 100000
set echo off feed off pages 0
spool hot.backup
select 'File created '||to_char(sysdate, 'dd-Mon-yy hh24:mm:ss')
  from dual;
prompt
begin
  declare
    target_dir varchar2(100) := 'g:\oracle\backups';
    source_file varchar2(100);
    ts_name varchar2(100);
    prev_ts_name varchar2(100);
    cursor mycur is
      select file_name,lower(tablespace_name)
        from sys.dba_data_files
       where instr(file_name, 'temp') = 0
       order by 2;
  begin
    prev_ts_name := 'X';
    open mycur;
    fetch mycur into source_file,ts_name;
    while mycur%found loop
      if ts_name <> prev_ts_name then
         dbms_output.put_line ('#######################');
         dbms_output.put_line ('# Tablespace '||ts_name||'. . .');
         dbms_output.put_line ('#######################');
         dbms_output.put_line ('sqlplus '||
         '@d:\orant\sysman\start.sql '||
                                ts_name);
      end if;
      dbms_output.put_line ('copy '||source_file||' '||target_dir);
      prev_ts_name := ts_name;
      fetch mycur into source_file,ts_name;
      if ts_name <> prev_ts_name then
         dbms_output.put_line ('sqlplus '||
         '@d:\orant\sysman\end.sql '||
                                prev_ts_name);
      end if;
    end loop;
    dbms_output.put_line ('sqlplus @d:\orant\sysman\end.sql '||
                           prev_ts_name);
  end;
end;
/


p.472-473
File created 19-May-99 11:05:20
#######################
# Tablespace rollback_data. . .
#######################
sqlplus @d:\orant\sysman\start.sql roolback_data
copy D:\ORANT\DATABASE\RBS1ORCL.ORA g:\oracle\backups
sqlplus @d:\orant\sysman\end.sql rollback_data
#######################
# Tablespace system. . .
#######################
sqlplus @d:\orant\sysman\start.sql system
copy D:\ORANT\DATABASE\SYS1ORCL.ORA g:\oracle\backups
sqlplus @d:\orant\sysman\end.sql system
#######################
# Tablespace temporary_data. . .
#######################
sqlplus @d:\orant\sysman\start.sql temporary_data
copy D:\ORANT\DATABASE\TMP1ORCL.ORA g:\oracle\backups
sqlplus @d:\orant\sysman\end.sql temporary_data
#######################
# Tablespace user_data. . .
#######################
sqlplus @d:\orant\sysman\start.sql user_data
copy D:\ORANT\DATABASE\USR1ORCL.ORA g:\oracle\backups
sqlplus @d:\orant\sysman\end.sql user_data


p.473
alter tablespace &1 begin backup;
exit


alter tablespace &1 end backup;
exit


SQL> alter tablespace &1 begin backup;
old   1: alter tablespace &1 begin backup;
new   1: alter tablespace system begin backup;
SQL>



Chapter17
p.479
Connected.
SVRMGR>


p.480
NAME                        TYPE               VALUE
------------------------   ----------------   ----------------
audit_trail                 string             NONE
background_dump_dest        string             %RDBMS80%\trace
checkpoint_process          boolean            FALSE
cleanup_roolback_entries    integer            20
commit_point_strength       integer            1


NAME                        TYPE               VALUE
------------------------   ----------------   ----------------
db_block_buffers            integer            400
log_archive_buffer_size     integer            127
log_archive_buffers         integer            4
log_buffer                  integer            65596


p.487
db_block_buffers = 800
db_name = 'prod'
checkpoint_process = true


p.488
SVXeEO[ö         4817701oCg
Fixed Size                           28376oCg
Variable Size                      3904532oCg
Database Buffers                    819200oCg
Redo Buffers                         65596oCg


p.489
SVRMGR> show sga
SVXeEO[ö              4767460oCg
          Fixed Size                      36432oCg
       Variable Size                    3846292oCg
    Database Buffers                     819200oCg
        Redo Buffers                      65536oCg
SVRMGR> spool off



Chapter18
p.496
Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation. All rights reserved.

Personal Oracle8 Release 8.0.3.0.0 - Production
With the partitioning option
PL/SQL Release 8.0.3.0.0 - Production
SVRMGR> connect internal
pX[h:
ڑ܂B
SVRMGR> startup
OracleCX^XN܂B
SVXeEO[ö          8030448oCg
Fixed Size                      44584oCg
Variable Size                 7510728oCg
Database Buffers               409600oCg
Redo Buffers                    65536oCg
f[^x[X}Eg܂B
f[^x[XI[v܂B
SVRMGR>


p.498
SQL*Plus: Release 8.0.3.0.0 - Production on Tue Dec 18 22:14:09 1999
Copyright (c) Oracle Corporation 1997.  All rights reserved.
pX[h͂ĂB
ORA-01035: ORACLERESTRICTED SESSION[U[̂ݎgp\łB
[U[͂ĂB


p.505
grant dbtech to ops$masi;
grant dbtech to ops$puff;
-- [dbtechɌt^̂ŁC
-- ops$masiops$puff́C[dbtech 
-- O[vɊÂČF܂D
grant select, insert, update, delete on spoon to dbtech;


p.518
dml_locks                           integer 1200
gc_db_locks                         integer 75
gc_files_to_locks                   string
gc_rollback_locks                   integer 20
gc_save_rollback_locks              integer 20
row_locking                         string  default
temporary_table_locks               integer 80


pfile_keyword = keyword_value


p.519
rollback_segments = (rollback_disk1,rollback_disk2, rollback_disk3, \
                     rollback_disk4)
                     
                     
p.520
SHARED_POOL_SIZE
ftHgl:3.5 MoCg
l͈̔:300 KoCgOSɈˑ܂
Lv[̃TCYioCgPʁjBLv[́ALJ[\уXgAhEvV[W܂݂
B}`[U[EVXeł͒l傫΁AptH[}XP܂Bl
΁A܂܂B
ڍ:  21́u蓖Ă̒v


p.527
svrmgr30 < bcft.sql


oracle
alter database backup controlfile to trace;
exit


p.527-528
# ȉ̃R}h͐Vt@C쐬C
# f[^x[X̃I[vɎgp܂D
# Recovery ManagerɂĎgpf[^͑܂DItC
# f[^t@C̉񕜂ɂ́Cʂ̃OKvɂȂꍇ܂D̕@́C
# ݂̃o[ŴׂẴICOgpłꍇɂ̂ݎgp܂D
create controlfile reuse database "Oracle" noresetlogs noarchivelog
    maxlogfiles 32
    maxlogmembers 2
    maxdatafiles 32
    maxinstances 16
    maxloghistory 1630
logfile
  group 1 'd:\orant\database\log2orcl.ora'  size 200k,
  group 2 'd:\orant\database\log1orcl.ora'  size 200k
datafile
  'd:\orant\database\sys1orcl.ora',
  'd:\orant\database\usr1orcl.ora',
  'd:\orant\database\rbs1orcl.ora',
  'd:\orant\database\tmp1orcl.ora'
;
# f[^t@ĈꂩobNAbv񕜂̂łꍇC
# ܂́CO̒~ɍsȂꍇ̓f[^t@C񕜂Kv܂D
recover database
# Ńf[^x[Xʏ̕@ŃI[vł܂D
alter database open;


p.529-531
00000-00100: Oracle Server
00101-00149: }`XbhET[o[
00150-00159: Oracle*XA
00160-00199: U
00200-00249: t@C
00250-00299: A[JCuщ
00300-00369: RedoOEt@C
00390-00399: RedoOEt@C
00600-00639: Oracle ̗O
00640-00699: SQL*Connect
00700-00709: fBNViELbV
00816-00816: bZ[W
00900-00999: SQL
01000-01099: [U[EvOEC^tF[X
01100-01250: Oraclet@C
01400-01489: SQL̎s
01490-01499: ANALYZECSQĹCsɊւ邻̑̃bZ[W
01500-01699: OracleR}h
01700-01799: SQL
01800-01899: t֐
01900-02039: SQL
02040-02099: U
02140-02299: SQL
02351-02375: _CNgpX[hSQL*Loader
02376-02399: Oracle\[X
02401-02419: EXPLAIN PLANR}h
02420-02429: XL[}
02430-02449: ̗LƖ
02476-02479: Parallel Direct Loader
02480-02489: g[X@\
02490-02499: f[^t@C̃TCYύX
02700-02874: UNIX ł̕^XN
02875-02899: IBM RS/6000
03000-03099: Ȃ@\
03100-03199: ^XÑC^tF[X
03200-03289: ̈̊蓖ĂƊǗ
03290-03295: TRUNCATER}h
03296-02099: f[^t@C̃TCYύX
04000-04019: ȃR}hEp[^
04020-04029: Library Object Lock
04030-04039: VXeE
04040-04059: XgAhEvV[W
04060-04069: XgAhEvV[W̎s
04070-04099: gK[
06000-06429: SQL*Net
06500-06580: PL/SQL
06600-06699: SQL*Net
07200-07499: UNIX
07500-07999: VAX/VMS
08000-08174: f[^EANZX
08401-08499: PL/SQL [eBeBEpbP[W
08600-09099: SQL*Connect
09100-09199: Oracle Gateway
09200-09499: DOSCOS/2CNovell
09700-09999: UNIX
10000-10999: bZ[W
12000-12099: SQL*Net
12500-12699: SQL*Net
12700-12799: eΉ
12800-12849: pENG[^̍쐬
13000-13199: UIvV
14000-14119: p[eBVIuWFNg- 
14400-14499: p[eBVIuWFNg- s
14500-14999: p[eBVIuWFNg- ANALYZE
19500-19998: T[o[Ǘ
19999-21099: XgAhEvV[W
21100-21299: bZ[W
22800-22849: IuWFNgSQL
22850-22879: IuWFNgSQL DDL
22880-22899: IuWFNgSQL REF/DEREFΉ
22900-22919: SQLlXg\ƎW
23300-24299: DBMS PL/SQLpbP[W
25000-25099: gK[
25100-25199: 
27000-27299: Unix
27500-27650: IPC
28000-28499: ZLeB֘A
28500-28549: َIvV
28750-29249: ZLeBET[rX
29250-29399: DBMS_SQL
29400-29499: Oraclef[^EJ[gbW


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


ORA-00600: G[R[h, : [12387], [34503],[],[],[],[]


p.533
SQL> select * from dict where table_name like '%OBJECTS';
TABLE_NAME                     COMMENTS
------------------------------ ----------------------------------    
ALL_OBJECTS                    Objects accessible to the user
DBA_OBJECTS                    All objects in the database
USER_OBJECTS                   Objects owned by the user

SQL>  select * from dict where table_name like '%_QUOTAS';
TABLE_NAME                     COMMENTS
------------------------------ ----------------------------------    
DBA_TS_QUOTAS                  Tablespace quotas for all users
USER_TS_QUOTAS                 Tablespace quotas for the user

SQL> select * from dict where table_name like '%_SYNONYMS';
TABLE_NAME                     COMMENTS
------------------------------ ----------------------------------    
ALL_SYNONYMS                   All synonyms accessible to the user
DBA_SYNONYMS                   All synonyms in the database
USER_SYNONYMS                  The user's private synonyms

SQL>


p.533-534
TABLE_NAME              COMMENTS                                         
----------------------- ----------------------------------------------    
DBA_DATA_FILES          Information about database files
DBA_DB_LINKS            All database links in the database
DBA_EXTENTS             Extents comprising all segments in the database
DBA_FREE_SPACE          Free extents in all tablespaces
DBA_INDEXES             Description for all indexes in the database      
DBA_IND_COLUMNS         COLUMNs comprising INDEXes on all TABLEs and 
                        CLUSTERs
DBA_OBJECTS             All objects in the database                      
DBA_ROLLBACK_SEGS       Description of rollback segments                 
DBA_SEGMENTS            Storage allocated for all database segments      
DBA_SEQUENCES           Description of all SEQUENCEs in the database     
DBA_SYNONYMS            All synonyms in the database                     
DBA_TABLES              Description of all tables in the database        
DBA_TABLESPACES         Description of all tablespaces                   
DBA_TAB_COLUMNS         Columns of all tables, views, and clusters 
                        database
DBA_TAB_GRANTS          All grants on objects in the database            
DBA_TAB_PRIVS           All grants on objects in the database            
DBA_TS_QUOTAS           Tablespace quotas for all users                  
DBA_USERS               Information about all users of the database      
DBA_VIEWS               Text of all views in the database  



Chapter19
p.541
|    |    |    |    |    |    |    |    |    |    |
+----+----+----+----+----+----+----+----+----+----+
1   10   20   30   40   50   60   70   80   90   100


p.542
          |
          |         |         |
     |    |         |         |    |         |    |
|    |    |    |    |    |    |    |    |    |    |
+----+----+----+----+----+----+----+----+----+----+
1   10   20   30   40   50   60   70   80   90   100


analyze table outlet
   compute statistics for columns province size 10;
   
   
p.544
Bill Greer           CEO           Bunque Systems International
Boris Abbeflantro    President     BJA Holdings
Norman Nadrojian     Senior VP     Communications Directorate Ltd.
Nancy Besdesmith     Trainer       Silver Institute
Francis Defwayno     Conductor     Chapel Philharmonic



p.545
sqlldr userid=tom/scholz control=parr1 parallel=ture &
sqlldr userid=tom/scholz control=parr2 parallel=ture &
sqlldr userid=tom/scholz control=parr3 parallel=ture &


create table blah_blah (...) parallel 2;
create index blah_blah_1 on blah_blah (...) parallel 2;


p.548
Column name
po_num
po_date
delivery_date


p.548-549
create table SALES_94 as
select * from PURCHASE_ORDER where po_date between
         to_date('21-JAN-1994','DD-MON-YYY')
         and to_date('31-DEC-1994','DD-MON-YYY')
         and line_item <= 3;
create table SALES_95 as
select * from PURCHASE_ORDER where po_date between
         to_date('01-JAN-1995','DD-MON-YYY')
         and to_date('31-DEC-1995','DD-MON-YYY')
         and line_item <= 3;
create table SALES_96 as
select * from PURCHASE_ORDER where po_date between
         to_date('01-JAN-1996','DD-MON-YYY')
         and to_date('31-DEC-1996','DD-MON-YYY')
         and line_item <= 3;
create table SALES_97 as
select * from PURCHASE_ORDER where po_date between
         to_date('01-JAN-1997','DD-MON-YYY')
         and to_date('31-DEC-1997','DD-MON-YYY')
         and line_item <= 3;
create table SALES_98 as
select * from PURCHASE_ORDER where po_date between
         to_date('01-JAN-1998','DD-MON-YYY')
         and to_date('31-DEC-1998','DD-MON-YYY')
         and line_item <= 3;
create table SALES_99 as
select * from PURCHASE_ORDER where po_date between
         to_date('01-JAN-1999','DD-MON-YYY')
         and to_date('31-DEC-1999','DD-MON-YYY')
         and line_item <= 3;
create view SALES as
select * from sales_94
union all
select * from sales_95
union all
select * from sales_96
union all
select * from sales_97
union all
select * from sales_98
union all
select * from sales_99







                     






































