wORACLE f[^EFAnEWOx


Chapter2
p.35
select rtrim ('teplow                    ',' ') from .....


p.36
update paymast set gender = decode (gender,'1','f','m');


update pay_mast
set gender = (select new_code 
                from code_matching
               where gender = old_code);


if p-gender = '1' then
     move "f" to dss-gender
else
     move "m" to dss-gender.


if (gender == '1')
  dss_g = 'm';
else
  dss_g = 'f';


p.44
"How many widgets were sold last year?"iNCEBWFbg͂ꂽj


"How many widgets were sold last year in the third quarter?"iN̑3l
ɁCEBWFbg͂ꂽj


"How many widgets were sold last year in the third quarter in Europe and 
Africa, in countries where the retail outlets are within one kilometer of 
a major city center whose population is over 1,000,000?"i[bpуAt
Jɂ鍑ŁCN̑3lɁCEBWFbg͂ꂽDClS𒴂ss
̒S珬X1kmȓɂ鍑ΏۂƂDj


p.45
select count(widget),loc_code
  from widget_mast a,city b,con c,stor d
 where c.con_code in ('AF','EU') 
   and a.sale_date between '01-JUL-1998' and '30-SEP-1998'
   and b.pop > 1000000
   and d.coord - b.coord <= 1
   and b.loc_code = d.loc_code
 group by d.loc_code; 


"How would hiring three additional sales personnel in the northeast 
region affect profit over the complete fiscal year?"iknŔ̔3
₷ƁCNx̗vɂǂ̂悤ȉe邩Dj



Chapter4

p.87
Oracle Data WarehousingvWFNg|Ƃ̏ڍ׍\iAEgCtH[j

1. vWFNgǗ
    1.1   Ǘ
          1.1.1  X̊Ǘ
          1.1.2  X̂
          1.1.3  ̉
    1.2   ~[eBO
          1.2.1  NCAgƂ̃~[eBO
          1.2.2  X^btł̃~[eBO
          1.2.3  LbNIt~[eBO

2. eNjJGfB^
    2.1   eNjJGfB^̑I
          2.1.1  KvȃXLZbg̋K
          2.1.2  ҂̍i荞
          2.1.3  Y҂̕]
          2.1.4  ҂̌
    2.2   eNjJGfB^ɑ΂⏞
          2.2.1  _񏑂ւ̃TC
          2.2.2  ؎̑t
    2.3   eNjJGfB^Ƃ̂
          2.3.1  葱̍쐬
          2.3.2  _񎖍Ɋւ铯

3. ̎M
    3.1   ̒҂̑I


p.88
Oracle Data WarehousingvWFNg | Ƃ̏ڍ׍\iڍ׃tH[j

1. vWFNgǗ

̃ANeBreB́Cʏ퐬ʕƂ͌ȂȂDCvWFNg̗\ZŜ25%߂
߁CɊ܂߂̂ƂD

1.1 Ǘ
            vWFNgɊ֘AG܂߂D
   1.1.1    X̊Ǘ
            ^CJ[h󋵃|[g̎WCxɁCvWFNg̃RXgCl̍팸ɑ
            ΏCыɊւ鎖D
   1.1.2    X̂
            pIɎ{є̃fBXJbV܂Ƃ߂ĈDNCA
            gƂ̂̓vWFNgɂƂďdvłC̍ۂɏdvłD



Chapter5
p.99
        1         2         3         4         5         6
123456789012345678901234567890123456789012345678901234567890123
0-07-881181-3Tuning Oracle           Corey Abbey Dechichio 1995
84-481-0337-3Puesta a punto de OracleCorey Abbey Dechichio 1995


p.101-102
/oracle/app/oracle/product/7.3.2/bin> sqlldr
SQL*Loader: Release 7.3.2.1.0 - Production on Tue Feb 30 14:46:50 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 
Usage: SQLLOAD keyword=value [,keyword=value,...] 
Valid Keywords: 
    userid -- ORACLE username/password
   control -- Control file name
       log -- Log file name
       bad -- Bad file name
      data -- Data file name
   discard -- Discard file name
discardmax -- Number of discards to allow          (Default all) 
      skip -- Number of logical records to skip    (Default 0) 
      load -- Number of logical records to load    (Default all) 
    errors -- Number of errors to allow            (Default 50) 
      rows -- Number of rows in conventional path bind array or between
              direct path data saves
              (Default: Conventional path 64, Direct path all) 
  bindsize -- Size of conventional path bind array in bytes(Default 65536) 
    silent -- Suppress messages during run 
              (header,feedback,errors,discards) 
    direct -- use direct path                      (Default FALSE) 
   parfile -- parameter file: name of file that contains parameter
              specifications
  parallel -- do parallel load                     (Default FALSE) 
      file -- File to allocate extents from
PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords.  An example of the former case is 'sqlload
scott/tiger foo'; an example of the latter is 'sqlload control=foo
userid=scott/tiger'.  One may specify parameters by position before
but not after parameters specified by keywords.  For example, 
'sqlload scott/tiger control=foo logfile=log' is allowed, but
'sqlload scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct. 
/oracle/app/oracle/product/7.3.2/bin>


p.102
sqlldr userid=/ control=regional bad=regbad.dat discard=regdsc.dat


sqlldr userid=/ control=regional


infile 'regional.dat' badfile regbad.dat discardfile regdsc.dat


p.103
sqlldr userid=/ control=accload log=accload


p.104
when (3) = 'A'


load data


options


infile


infile = 'ACCOUNTS'


p.105
load data
infile 'mytown.dat' discardfile mtdisc.rec
                    badfile mtbad.rec


p.106
into account_trans
   when day between '01' and '31'
. . . . 
into account_nbr
   when account_type between 'AA' and 'ZZ'
. . . .


p.107
-- ̂悤Ƀ_bVi-j2ċLqƁCSQL*Loader̐
-- t@CɃRgLqł܂D󔒍śCǂɂłLqł܂D

load data
infile 'account.dat'
into table account_trans append
(account_nbr       position(01:10) character,  -- i
 day               position(11:12) character,  -- ii
 month             position(13:14) character, 
 transaction_code  position(15:16) character, 
 debit_amount      position(17:26) character, 
 credit_amount     position(27:36) character)  -- iii


p.108
load data
infile 'account.dat'
into table account append

(account_nbr    position(1:10)  char, 
 account_type   position(11:12) char, 
 owner          position(13:42) char, 
 last_activity  position(43:48) date 'YYMMDD', 
 status         position(49:50) char)


p.109
sqlldr userid=system/manager control=account.ctl log=account.out
SQL*Loader: Release 7.3.2.1.0 - Production on Wed Jun 12 14:02:14 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 

Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 704
Commit point reached - logical record count 768
Commit point reached - logical record count 832
Commit point reached - logical record count 896
Commit point reached - logical record count 960


p.109-110
SQL*Loader: Release 7.3.2.1.0 - Production on Wed Jun 12 14:28:59 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 
Control File:   ././account.ctl
Data File:      ././account.dat
  Bad File:     ././account.bad
  Discard File:  none specified

(Allow all discards) 
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional
Table ACCOUNT, loaded from every logical record. 
Insert option in effect for this table: APPEND
   Column Name                  Position  Len   Term  Encl Datatype
----------------------------- ----------- ----- ---- ---- ---------------
ACCOUNT_NBR                          1:10    10            CHARACTER
ACCOUNT_TYPE                         1:12     2            CHARACTER
OWNER                               13:42    30            CHARACTER
LAST_ACTIVITY                       43:48     6            DATE YYMMDD
STATUS                              49:50     2            CHARACTER

Table ACCOUNT: 
  960 Rows successfully loaded. 
  0 Rows not loaded due to data errors. 
  0 Rows not loaded because all WHEN clauses were failed. 
  0 Rows not loaded because all fields were null. 

Space allocated for bind array:                   4608 bytes(64 rows) 
Space allocated for memory besides bind array:    56709 bytes
Total logical records skipped:          0
Total logical records read:           960
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Wed Jun 12 14:28:59 1997
Run ended on Wed Jun 12 14:29:00 1997
Elapsed time was:     00:00:01.12
CPU time was:         00:00:00.17


p.110-111
Record 64: Rejected - Error on table ACCOUNT, column LAST_ACTIVITY. 
ORA-01839: date not valid for month specified
 Record 203: Rejected - Error on table ACCOUNT, column ACCOUNT_NBR. 
ORA-01722: invalid number

Table ACCOUNT: 
  958 Rows successfully loaded. 
  2 Rows not loaded due to data errors. 
  0 Rows not loaded because all WHEN clauses were failed. 
  0 Rows not loaded because all fields were null. 
Space allocated for bind array:                   4608 bytes(64 rows) 
Space allocated for memory besides bind array:    56709 bytes
Total logical records skipped:          0
Total logical records read:           960
Total logical records rejected:         2
Total logical records discarded:        0


p.111
0067897782RRDatabase Technologies        961529TT
889GHG8777GGIan Abramson Systems         960529TT


p.112
load data
infile 'account.dat'  

-- 1990ÑgUNVR[hłāCAJEgANeBu
-- i܂CXe[^X00jȃR[h[hD

into table account_trans replace
when year = '1990'
  (account_nbr         position(01:10) char, 
   day                 position(11:12) char, 
   month               position(13:15) char, 
   year                position(16:19) char, 
   transaction_code    position(20:21) char, 
   debit_amount        position(22:31) char, 
   credit_amount       position(32:41) char) 

into table account replace
when status = '00'
  (account_nbr    position(01:10)  char, 
   account_type   position(11:12) char, 
   owner          position(13:42) char, 
   last_activity  position(43:48) date 'YYMMDD', 
   status         position(49:50) char) 


if i16`19̈ʒu"1990"ƂeLXgi[Ăj then
 if iR[h̎c̃f[^[ᔽłȂj then
      iACCOUNT_TRANSɍs[hj
 else
 account.badɍs
 end if
else
 if i49`50̈ʒu"00"ƂeLXgi[Ăj then
 if iR[h̎c̃f[^[ᔽłȂj then
 ACCOUNTɍs[h
 else
 account.badɍs
 end if
end if


p.113
sqlldr userid=system/manager control=accmult.ctl discard=accmult.dsc
SQL*Loader: Release 7.3.2.1.0 - Production on Wed Jun 12 14:02:14 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 
Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576


p.113-115
SQL*Loader: Release 7.3.2.1.0 - Production on Wed Jun 12 20:54:44 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 

Control File:   ././accmult.ctl
Data File:      ././account.dat
  Bad File:     ././account.bad
  Discard File: accmult.dsc
 (Allow all discards) 

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table ACCOUNT_TRANS, loaded when YEAR = 0X31393930(character '1990') 
Insert option in effect for this table: REPLACE
  Column Name                  Position   Len  Term Encl Datatype
----------------------------- ----------- ----- ---- ----  -----------
ACCOUNT_NBR                          1:10    10            CHARACTER
DAY                                 11:12     2            CHARACTER
MONTH                               13:15     3            CHARACTER
YEAR                                16:19     4            CHARACTER
TRANSACTION_CODE                    20:21     2            CHARACTER
DEBIT_AMOUNT                        22:31    10            CHARACTER
CREDIT_AMOUNT                       32:41    10            CHARACTER

Table ACCOUNT, loaded when STATUS = 0X3030(character '00') 
Insert option in effect for this table: REPLACE
   Column Name                   Position  Len  Term Encl Datatype
----------------------------- ----------- ----- ---- ---- -----------
ACCOUNT_NBR                          1:10    10           CHARACTER
ACCOUNT_TYPE                        11:12     2           CHARACTER
OWNER                               13:42    30           CHARACTER
LAST_ACTIVITY                       43:48     6           DATE YYMMDD
STATUS                              49:50     2           CHARACTER

Record 43: Discarded - failed all WHEN clauses. 
Record 213: Discarded - failed all WHEN clauses. 
Record 462: Discarded - failed all WHEN clauses. 
Record 2: Rejected - Error on table ACCOUNT_TRANS. 
ORA-01843: not a valid month. 

Table ACCOUNT_TRANS: 
  254 Rows successfully loaded. 
  0 Rows not loaded due to data errors. 
  2 Rows not loaded because all WHEN clauses were failed. 
  0 Rows not loaded because all fields were null. 

Table ACCOUNT: 
  318 Rows successfully loaded. 
  1 Rows not loaded due to data errors. 
  1 Rows not loaded because all WHEN clauses were failed. 
  0 Rows not loaded because all fields were null. 

Total logical records skipped:          0
Total logical records read:           576
Total logical records rejected:         1
Total logical records discarded:        3

Run began on Wed Jun 12 20:54:44 1997
Run ended on Wed Jun 12 20:54:44 1997
Elapsed time was:     00:00:00.58
CPU time was:         00:00:00.21

Space allocated for bind array:                   9728 bytes(64 rows) 
Space allocated for memory besides bind array:    91245 bytes


p.115
0067897782RRDatabase Technologies         96043100

0032236177THRiver Styx                    90021205
006789778212JUN1988CR          0000034569
004329744404DEC1991DR0000234888


123,"OK","Michael Abbey Systems International Inc.",1828,9383,"R",908
456,"GT","IOUG Americas",342,2211,"Q",82
789,"OK","The Beatles",77,2128,"R",112


p.115-116
load data
infile 'customer.dat'
into table customer append
(cust_id   char terminated by ',', 
 status    char enclosed by '"', 
 cname     char enclosed by '"', 
 acc_num   char terminated by ','
 tax_class char terminated by ',', 
 dsc_class char enclosed by '"', 
 source    char terminated by whitespace)


p.116
sqlldr loader oracle text sql


insert into account_summ values(129,'12-DEC-98',1929100,249,'VRP'); 


p.117
sqlldr userid=/ control=acct1.ctl direct=true parallel=true
sqlldr userid=/ control=acct2.ctl direct=true parallel=true
sqlldr userid=/ control=acct3.ctl direct=true parallel=true


p.119
exp {parameter1=value1,parameter2=value2,...parametern=valuen}


p.119-120
/oracle> exp help=y

Export: Release 7.3.2.1.0 - Production on Fri Jun 14 15:27:14 1996
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password: 

    Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords: 

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) 
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR) 

Keyword  Description (Default)        Keyword      Description (Default) 
-------------------------------------------------------------------------
USERID   username/password            FULL         export entire file (N) 
BUFFER   size of data buffer          OWNER        list of owner usernames
FILE     output file (EXPDAT.DMP)     TABLES       list of table names
COMPRESS import into one extent (Y)   RECORDLENGTH length of IO record
GRANTS   export grants (Y)            INCTYPE      incremental export type
INDEXES  export indexes (Y)           RECORD       track incr. export (Y) 
ROWS     export data rows (Y)         PARFILE      parameter filename
CONSTRAINTS export constraints (Y)    CONSISTENT   cross-table consistency
LOG      log file of screen output    STATISTICS   analyze objects 
DIRECT   direct path (N)                           (ESTIMATE)


p.120
exp


p.121-122
Export: Release 7.3.2.1.0 - Production on Sun Jun 15 16:14:53 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 

Username: account
Password: 
pX[h́CXbVi/jŋ؂usernameƓsɓ͂ł܂D

Connected to: Oracle7 Server Release 7.3.2.1.0 - Production Release
PL/SQL Release 2.3.2.0.0 - Production

Enter array fetch buffer size: 4096 >
ɂ́CoCgPʂŐl͂܂DOraclef[^oăGNX|[gt@Cɏ
ނ߂ɁCɓ͂oCg̃obt@蓖Ă܂D

Export file: expdat.dmp > account
ɂ́CGNX|[gt@C̖O͂܂D͂Ƀ^[L[ƁC
uexpdat.dmpvƂt@CftHglƂĎgp܂DCł́Cuaccountv
Ƃt@C͂Ă܂Dœ͂t@Čɂ́Cu.dmpvƂgqI
ɕt܂D

(2)U(sers), or (3)T(ables): (2)U > u
export̏[hq˂Ă܂D̃[hɂẮC̏͂Ōquexport
[hvŐ܂D
Export grants (yes/no): yes >

Export table data (yes/no): yes >
uyesvƉƁCexport͎w肳Ă\f[^𒊏o܂DunovƉƁCf[^
ł͂Ȃf[^̒`GNX|[gt@Cɏ܂܂D

Export table data (yes/no): yes >
uyesvƉƁCexport͎w肳Ă\f[^̂𒊏o܂DunovƉƁCf[^
̂ł͂Ȃf[^̒`GNX|[gt@Cɏ܂܂D

Compress extents (yes/no): yes >
̎ɂẮC̃XǧiuCompress extentsp[^vjŐ܂D

Export done in US7ASCII character set
About to export ACCOUNT's objects ... 
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ACCOUNT's tables via Conventional Path ... 
. . exporting table                        ACCOUNT      1331 rows exported
. . exporting table                  ACCOUNT_TRANS     67331 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting referential integrity constraints
. exporting triggers
. exporting posttables actions
Export terminated successfully without warnings. 

/oracle>


p.125
exp userid=account/account file=accreg grants=n buffer=1048576
tables=(account.account,account.account_trans,region.state,region.geo)


Connected to: Oracle7 Server Release 7.3.2.1.0 - Production Release
PL/SQL Release 2.3.2.0.0 - Production
Export done in US7ASCII character set
Note: grants on tables/views/sequences/roles will not be exported
̌x́Ct^GNX|[gȂ悤Ɏw肵Ă̂ŏo͂܂D͗v
ŁC肠܂D

About to export specified tables via Conventional Path ... 
. . exporting table                        ACCOUNT       1331 rows exported
. . exporting table                  ACCOUNT_TRANS      67331 rows exported
Current user changed to REGION
́CGNX|[g[`C̃[U[Ă\́CGNX|[gɕύXꂽ
ƂĂ܂D

. . exporting table                          STATE         50 rows exported
. . exporting table                            GEO       8712 rows exported
Export terminated successfully without warnings.


p.126
exp parfile=acc.parfile


userid=account/account
file=account
buffer=8192


Connected to: Oracle7 Server Release 7.3.2.1.0 - Production Release
PL/SQL Release 2.3.2.0.0 - Production
Export done in US7ASCII character set

About to export specified users ... 
About to export ACCOUNT's objects ... 
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ACCOUNT's tables via Conventional Path ... 
. . exporting table                        ACCOUNT       1331 rows exported
. . exporting table                  ACCOUNT_TRANS      67331 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting referential integrity constraints
. exporting triggers
. exporting posttables actions
Export terminated successfully without warnings.


p.127
imp {parameter1=value1,parameter2=value2,...parametern=valuen}


p.127-128
/oracle > imp help=y
Import: Release 7.3.2.1.0 - Production on Sun Jun 17 21:40:14 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 

You can let Import prompt you for parameters by entering the IMP
command followed by your username/password: 

    Example: IMP SCOTT/TIGER

Or, you can control how Import runs by entering the IMP command followed
by various arguments. To specify parameters, you use keywords: 

Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) 
     Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N

Keyword  Description (Default)       Keyword      Description (Default) 
-------------------------------------------------------------------------
USERID   username/password           FULL         import entire file (N) 
BUFFER   size of data buffer         FROMUSER     list of owner usernames
FILE     output file (EXPDAT.DMP)    TOUSER       list of usernames
SHOW     just list file contents (N) TABLES       list of table names
IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
GRANTS   import grants (Y)           INCTYPE      incremental import type
INDEXES  import indexes (Y)          COMMIT       commit array insert (N) 
ROWS     import data rows (Y)        PARFILE      parameter filename
LOG      log file of screen output
DESTROY  overwrite tablespace data file (N) 
INDEXFILE write table/index info to specified file
CHARSET  character set of export file (NLS_LANG) 
Import terminated successfully without warnings. 
/oracle>


p.129
Import: Release 7.3.2.1.0 - Production on Sun Jun 17 22:07:59 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 
Connected to: Oracle7 Server Release 7.3.2.1.0 - Production Release
PL/SQL Release 2.3.2.0.0 - Production
Export file created by EXPORT:V07.03.02 via conventional path
. importing ACCOUNT's objects into ACCOUNT
IMP-00015: following statement failed because the object already exists: 
 "CREATE TABLE "ACCOUNT" ("ACCOUNT_NBR" NUMBER(10, 0), "ACCOUNT_TYPE" 
  VARCHAR2(2), """OWNER" VARCHAR2(30), "LAST_ACTIVITY" DATE, "STATUS"
  VARCHAR2(2))  PCTFREE ""10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 10240 NEXT 10240 MINE"
  "XTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 
  1) TABLESPACE "ACCOUNT""
Import terminated successfully with warnings.


imp userid=account/account full=y file=account ignore=y


Import: Release 7.3.2.1.0 - Production on Sun Jun 17 22:26:36 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 
Connected to: Oracle7 Server Release 7.3.2.1.0 - Production Release
PL/SQL Release 2.3.2.0.0 - Production

Export file created by EXPORT:V07.03.02 via conventional path
. importing ACCOUNT's objects into ACCOUNT
. . importing table                      "ACCOUNT"      67331 rows imported
Import terminated successfully without warnings.


p.130
imp userid=account/account full=y file=account ignore=y commit=y


Export file created by EXPORT:V07.03.02 via conventional path
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES 
           arguments
IMP-00021: operating system error - error code (dec 2, hex 0x2) 
IMP-00000: Import terminated unsuccessfully


Export file created by EXPORT:V07.03.02 via conventional path
IMP-00024: Full Import mode, cannot specify FROMUSER or TABLES parameter
IMP-00021: operating system error - error code (dec 2, hex 0x2) 
IMP-00000: Import terminated unsuccessfully


p.131
imp


Import: Release 7.3.2.1.0 - Production on Sun Jun 16 22:41:03 1996
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 
Username: account
Password: 
pX[h́CXbVi/jŋ؂΁CusernameƓsɓ͂ł܂D

Connected to: Oracle7 Server Release 7.3.2.1.0 - Production Release
PL/SQL Release 2.3.2.0.0 - Production
Import file: ./expdat.dmp > account
ł́CC|[gt@C̖O͂܂D͂Ƀ^[L[ƁC
uexpdat.dmpvƂt@CftHglƂĎgp܂DCł́Cuaccountv
Ƃt@C͂Ă܂Dœ͂t@Čɂ́Cu.dmpvƂgqI
ɕt܂D

Enter insert buffer size (minimum is 4096) 30720>
ɂ́CoCgPʂŐl͂܂DOraclef[^oăGNX|[gt@Cɏ
ނ߂ɁCɓ͂oCg̃obt@蓖Ă܂D

Export file created by EXPORT:V07.03.02 via conventional path
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no > yes
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no > yes
. importing ACCOUNT's objects into ACCOUNT
. . importing table                  "ACCOUNT_TRANS"    67331 rows imported
Import terminated successfully without warnings.


p.132
exp parfile=arb.parfile


userid=account/account 
file=arb_data 
fromuser=(account,region,branch) 
touser=dw_holder


p.133
Export file created by EXPORT:V07.03.02 via conventional path
. importing ACCOUNT's objects into DW_HOLDER
. . importing table                      "ACCOUNT"       2189 rows imported
. . importing table                "ACCOUNT_TRANS"      67331 rows imported
. importing REGION's objects into DW_HOLDER
. . importing table                         "CITY"      11219 rows imported
. . importing table                         "CNTY"        129 rows imported
. importing BRANCH's objects into DW_HOLDER
. . importing table                         "COMM"       3440 rows imported
. . importing table                         "SELF"       2129 rows imported
. . importing table                         "STOR"       7562 rows imported
. . importing table                         "BUSI"       3481 rows imported
Import terminated successfully without warnings.


imp userid=account/account file=account tables=account_trans ignore=y


p.134
Import: Release 7.3.2.1.0 - Production on Mon Jun 18 13:55:25 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. 
Connected to: Oracle7 Server Release 7.3.2.1.0 - Production Release
PL/SQL Release 2.3.2.0.0 - Production
Export file created by EXPORT:V07.03.02 via conventional path
. importing ACCOUNT's objects into DW_HOLDER
. . importing table                          "ACCOUNT_TRANS"
IMP-00003: ORACLE error 1653 encountered
ORA-01653: unable to extend table DW_HOLDER.ACCOUNT_TRANS by 8208 in 
           tablespace TOOLS
Import terminated successfully with warnings.


Export file created by EXPORT:V07.03.02 via conventional path
. importing ACCOUNT's objects into DW_HOLDER
. . importing table                "ACCOUNT_TRANS"      21893 rows imported
Import terminated successfully without warnings.


p.137
select to_date (ext_date_field,'YYYYMMDD'). . .


              
Chapter6
p.141
select person.hire_year, department.dname,directorate.dir_name,
       person.full_name
  from person,department,directorate
 where person.dept_id = department.dept_id
   and department.dir_id = directorate.dir_id;


p.143
NAME                                TYPE    VALUE
----------------------------------- ------- ----------------------
hash_area_size                      integer 0
hash_join_enabled                   boolean TRUE
hash_multiblock_io_count            integer 8
sequence_cache_hash_buckets         integer 10


p.144
create bitmap index person_region on person (region);


p.145
create bitmap index person_region on
person (region) tablespace indexes_prd
pctfree 20 pctused 80 initrans 4 maxtrans 16
storage (initial 1m next 1m pctincrease 0 initrans 4);


p.148
select count(*)
  from person
 where pensioned = 'Y'
   and region = 'NORTH';


p.152
from order,customer,item,line_item,location


p.153
create index purchase_pn_dos on purchase (part_number,date_of_sale);


p.154
select person.full_name,department.dept_name,department.location
  from person,department
 where person.full_name = 'Darlene Lloyd';


p.155
Darlene Lloyd        Denim apparel        North


p.156
alter tablespace {ts_name} read only;


alter tablespace {ts_name} read write;


p.160
parallel_default_max_instances      integer 0
parallel_max_servers                integer 16
parallel_min_percent                integer 0
parallel_min_servers                integer 8
parallel_server_idle_time           integer 5
recovery_parallelism                integer 0


p.162
create or replace view acc_trans_v as
select * from acc_trans_92
union all
select * from acc_trans_93
union all
select * from acc_trans_94
union all
select * from acc_trans_95;


p.163
select count(*),transit_nbr
  from account_trans_v
 where trans_date between '01-JAN-1992' and '31-DEC-1993';


p.164
select count(*),transit_nbr
  from acc_trans_v
 where fiscal_year in (1992,1993) 
   and account_type = 'SRI'
 group by transit_nbr;


p.167
SQL> create table sales_summ
  2    as select * from sales
  3    group by greg; 
Table created. 
 real: 189.50
SQL> drop table sales_summ; 
Table dropped. 
 real : 1.10
 SQL> create table sales_summ
   2    unrecoverable as
   3    select * from sales 
   4    group by greg; 
Table created. 
 real: 36.30


p.168
SQL> create index sales_summ unrecoverable  
  2 on sales_summ (sales_id,cust_id,greg) ;
Index created.


SQL> create bitmap index sales_summ_greg unrecoverable 
  2 on sales_summ (sales_id,cust_id,greg) ;
Index created.


exp userid=darch/pword tables=(fin,inv,geo,trn) direct=true


p.169
analyze table {table_name} compute statistics;


p.170
analyze table {table_name} compute statistics for table for all
indexed columns size {num_buckets};


SQL> analyze table person compute statistics for table for all
  2  indexed columns size 6;
Table analyzed.


SQL> select endpoint_number 
  2  from user_histograms 
  3 where table_name = 'PERSON'
              4
             37
            130
           2048
           7168
           7616
6 rows selected.


p.171
SQL> col a new_value b
SQL> select count(distinct hire_year) a from person; 
         7
SQL> analyze table person compute statistics for table for all
  2  indexed columns size &b; 
old   2: indexed columns size &b
new   2: indexed columns size          7
Table analyzed.


              
Chapter7
p.185
GAR & LsOVbv̐` ----> RVԂ̍w


ĩS ----> ^Ԃ̔̔



Chapter8
p.197
salesperson     sales_amt     cost_of_sale     region


p.204
create table dw_finsumm
(dwf_id number constraint dwf_pk primary key, ...


alter table dw_finsumm add constraint dwf_pk primary key (dwf_id);


p.205
alter table dw_finsumm add constraint dw_acc_fk 
      foreign key (acc) references account;


Chapter11
p.242
create role dss_finance;


create user {user_name} identified by {password};


grant {role_name} to sswanson;


create user showard identified by drawoh;
create user warnock identified by kconraw; 
create user babramson identified by nosmarba; 
create role finance_mgr; 
create role finance_sen; 
create role finance_user; 
grant finance_mgr to showard; 
grant finance_sen to fwarnock; 
grant finance_user to babramson;


grant select on fin_plan to finance_mgr; 
grant select on fin_hist to finance_user,finance_mgr,finance_sen;


p.243
revoke finance_mgr from showard; 


drop user {username};


p.244
create or replace view dept_south as
select *
  from dept_rllup
 where reg_cd = 'S';


-- eñ[쐬
create role southern; 
create role northern; 
create role eastern; 
create role western; 
-- n悲ƂDEPT_RLLUP̃r[쐬
create or replace view dept_north as
       select * from dept_rllup where reg_cd = 'N'; 
create or replace view dept_west as 
       select * from dept_rllup where reg_cd = 'W'; 
create or replace view dept_east as
       select * from dept_rllup where reg_cd = 'E'; 
create or replace view dept_south as
       select * from dept_rllup where reg_cd = 'S'; 
-- eñr[ɑ΂selecťCY郍[ɕt^
grant select on dept_south to southern; 
grant select on dept_north to northern; 
grant select on dept_east to eastern; 
grant select on dept_west to western;


Chapter13
p.282
Error #400: Non-fatal (0300): Can't start Oracle Express ...
Error #415: Non-fatal (0300): An incomplete installation of ...
Error #417: Non-fatal (0300): The SNAPI DLL C:\PCXWIN\XWSNLR16.DLL can't ...
... 


ConnectToExpress=0



AppendixA
p.311
Norman Nadrojian     55 Potomac Blvd.
Nancy Besdesmith     RR #12
Francis Defwayno     811 Pleasance
Boris Abbeflantro    56-B Eglinton


p.315
Chris Lentz          189 Pleasant Park
Darlene Lloyd        56 Cleary
Fred Shoemaker       89 Clarence
Ian Abramson         489 Borne Shell


AppendixC
p.339
if <estimatepercentIvVƂɎw肳Ă> then
   <w肳Ăs̃p[Ze[Wgpēvestimates>
elsif <estimaterowIvVƂɎw肳Ă> then
 if <w肳Ăs>=YIuWFNg̍s̔̒l> then
      <YIuWFNgɑ΂ēvcomputes>
 else
      <w肳Ăsgpēvestimates>
 end if
elsif <estimateIvVȂŎw肳Ă> then
   <1064sTvƂĎgpvestimates>
end if


p.340
ORA-00054 resource busy and acquire with NOWAIT specified 
Cause:  The NOWAIT keyword forced a return to the command prompt because 
        resource was unavailable for a LOCK TABLE or SELECT FOR UPDATE
        command. 
Action: Try the command after a few minutes or enter the command without
        the NOWAIT keyword.


p.341
set pagesize 0 echo off verify off feedback off 
set linesize 200 trimspool off  --<= 7.2 for trimspool
spool all_ana.sql
select 'analyze table '||owner||'.'||table_name||
       ' estimate statistics sample 20 percent;'    
  from sys.dba_tables
 where owner not in ('SYS','SYSTEM'); 
select 'analyze index '||owner||'.'||index_name||
       'compute statistics;'
  from sys.dba_indexes
 where owner not in ('SYS','SYSTEM'); 
spool off
set feed on echo on


p.342
execute dbms_utility.analyze_schema ('IEDB','ESTIMATE',null,20);


execute dbms_ddl.analyze_object ('INDEX','PLANNER_EX','COMPUTE');


p.345
            \̍s          1
I  =  ------------   *  ---------
          ӂȒľ    \̍s


select sum(aggr_day),region, ...
  from day_summary,region
 where trans_date between '01-JAN-1997' and '31-JAN-1997' ...;


p.346
select sum(aggr_day),region, ...
  from day_summary,region
 where to_char(trans_date,'Dy') in ('Mon','Tue') ...;
select sum(aggr_day),region, ...
  from day_summary,region
 where months_between(trans_date,sysdate) > 6 ...;


select ...
  from day_summary ...
 where to_char(trans_date) ...


select ...
  from day_summary ...
 where trans_date between (...


p.349
set pages 0
select value from v$parameter where name = 'db_block_buffers'; 
-- FREEɊւĒlԂȂꍇ́Cf[^LbV̂ȂɃAChԂ
-- obt@݂ĂȂƂӖĂD܂CFREEɊւĕԂꂽ
-- lCۂɂ0ł邱ƂĂD
select decode(state,0,'FREE', 
                    'Other'),count(*)
  from x$bh
 group by decode(state,0,'FREE', 
                       'Other');


-- 1
8000
Other               8000
-- 2
9000
FREE                 600
Other               7400



