------------------------------------------------------------
-
- Michael Corey and I are pleased to provide some of the scripts from
- our recently released book entitled ORACLE: A BEGINNER'S GUIDE. Please
- download and enjoy. For context on what this stuff will do for you
- as you learn more about Oracle, please refer to the listed chapter in
- the book. We thank you for your support! 
-
- Michael Abbey - Ottawa Canada - July 1995
-
- The material in this file is provided as is, with no warranty implied
- or expressed.
-
- (c) Osborne McGraw Hill 1995
- ------------------------------------------------------------

---------
Chapter 5
---------
=====================================================================
create table customer(
      last_name  varchar2(30) not null,
      state_cd   varchar(2),
      sales      number)
  tablespace custspace
  storage(initial 25k next 25k minextents 1);
create table state (
      state_cd   varchar(2) not null,
      state_name varchar2(30));
create table x (
      col        varchar2(30));
create table y (
      col        varchar2(30));
insert into customer values ('Porter','CA', 6989.99);
insert into customer values ('Martin','CA',2345.45);
insert into customer values ('Laursen','CA',34.34);
insert into customer values ('Bambi','CA',1234.55);
insert into customer values ('McGraw','NJ', 123.45);
insert into state (state_name, state_cd)
     values ('Massachusetts','MA');
insert into state (state_name, state_cd)
     values ('California','CA');
insert into state (state_name, state_cd)
     values ('NewJersey','NJ',);
insert into X values ('1');
insert into X values ('2');
insert into X values ('3');
insert into Y values ('3');
isert into Y values ('4');
insert into Y values ('5');
=====================================================================
---------
Chapter 8
---------
=====================================================================
rem * -----------------------------------------------------------
rem *  Script to set up tables used in Chapter 8 from
rem *  Oracle: A Beginner's Guide ISBN 0-07-882122-3
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 
         (100210,'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');
insert into bonus values (123,null,'AE',2000);
insert into bonus values (124,null,'AF',2200);
insert into bonus values (125,null,'AH',1200);
insert into bonus values (126,null,'AH',1200);
insert into bonus values (127,null,'AF',1200);
insert into bonus values (128,null,'AT',1500);
insert into bonus values (129,null,'AT',1100);
insert into bonus values (130,null,'AU',1400);
insert into bonus values (131,null,'AE',200);
insert into bonus values (132,null,'AF',220);
insert into bonus values (133,null,'AG',120);
insert into bonus values (134,null,'AG',200);
insert into bonus values (135,null,'AG',200);
insert into bonus values (136,null,'AU',1400);
insert into bonus values (137,null,'AH',100);
insert into bonus values (138,null,'AU',1400);
insert into factory values ('AE','Northeast','ON');
insert into factory values ('AF','Northwest','MN');
insert into factory values ('AH','Southeast','ON');
insert into factory values ('AT','Central','MN');
insert 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);
=====================================================================
----------
Chapter 12
----------
=====================================================================
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
=====================================================================
----------
Chapter 15
----------
=====================================================================
rem You must have select privileges on the v$parameter
rem v$logfile v$dbfile and v$controlfile data
rem dictionary views belonging to SYS to run
rem this program

set pages 0 feed off echo off
col a new_value b
col c new_value d
select value a,sysdate c
  from v$parameter
 where name = 'db_name';
spool cold.backup
prompt
prompt Cold backup for "&b" database on &d ...
prompt
prompt # Redo logs
prompt
select 'cp '||member||' .'
  from v$logfile;
prompt
prompt # Datafiles
prompt
select 'cp '||name||' .'
  from v$dbfile;
 prompt
prompt # Control files
prompt
select 'cp '||name||' .'
  from v$controlfile;
spool off
exit
=====================================================================
rem   We inform Oracle that we are backing up a tablespace
rem   before doing the copy. This is done one tablespace
rem   at a time. After the tablespace is put in backup
rem   mode, make a copy of its datafile(s). By forcing
rem   a checkpoint after each tablespace is backed up,
rem   we synch the registering of the backup internally
rem   to Oracle.

alter tablespace tools begin backup;
!cp /sys/tools/d1/tools.dbf /sys/backups/repos/tools.dbf
alter tablespace tools end backup;
alter system checkpoint;

alter tablespace temp begin backup;
! cp /usr/oradata/d3/temp.dbf /sys/backups/repos/temp.dbf
alter tablespace temp end backup;
alter system checkpoint;

alter tablespace rollback_segs begin backup;
! cp /usr/sys/rollback/rbs1.dbf /sys/backups/repos/rbs1.dbf
! cp /usr/sys/rollback/rbs2.dbf /sys/backups/repos/rbs2.dbf
alter tablespace rollback_segs end backup;
alter system checkpoint;

alter tablespace users begin backup;
!cp /usr/oradata/d1/users1.dbf /sys/backups/repos/users1.dbf
!cp /usr/oradata/d1/users2.dbf /sys/backups/repos/users2.dbf
alter tablespace users end backup;
alter system checkpoint;

alter tablespace system begin backup;
!cp /usr/oradata/d0/dbs1.dbf /sys/backups/repos/dbs1.dbf
alter tablespace system end backup;
alter system checkpoint;

alter database backup controlfile to
     '/sys/backup/repos/control_bkp' reuse;
=====================================================================
connect internal
spool scratch.log
set echo on
startup nomount pfile=?/dbs/initprac.ora
create database prac
       datafile '?/dbs1prac.dbf'                   size 10m
       logfile  '?/log1prac.dbf','?/log2prac.dbf' size 300k
maxlogfiles 20
       maxlogmembers   4
       maxdatafiles   30
       maxinstances    1
       maxloghistory 100;
create rollback segment temp
       tablespace system
       storage (initial 50k minextents 2);
shutdown
startup pfile=?/dbs/initprac.ora
alter tablespace system default storage (pctincrease 0);
set echo off
set termout off
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catexp.sql
@?/rdbms/admin/catldr.sql
@?/rdbms/admin/catproc.sql
connect system/manager
@?/rdbms/admin/catdbsyn.sql
connect internal
shutdown
=====================================================================
set serveroutput on size 10000
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) := '/data/oracle/bkp/prd/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 '||
            '@/data/oracle/bkp/prd/start.sql '||
                                ts_name);
      end if;
      dbms_output.put_line ('cp '||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 '||
            '@/data/oracle/bkp/prd/end.sql '||
                                prev_ts_name);
      end if;
    end loop;
    dbms_output.put_line ('sqlplus @/data/oracle/bkp/prd/end.sql '||
                           prev_ts_name);
  end;
end;
/
=====================================================================