------------------------------------------------------------------- - - Mike Corey and I are pleased to have extracted this code from - Oracle8: A Beginner's Guide. Please download and use as you see fit. - - Michael Abbey Mike Corey - Michael Abbey Systems International Inc. Database Technologies Inc. - Ottawa ON Canada Newton MA USA - - July, 1997 (c) Osborne McGraw-Hill and Oracle Press - ------------------------------------------------------------------- select rowid, substr(rowid,1,8) "BLOCK", substr(rowid,15,4) "FILE", substr(rowid,10,4) "ROW" from tablex; SQL> create table sales 2 (invoice_no number, 3 sale_year int not null, 4 sale_month int not null, 5 sale_day int not null) 6 partition by range (sale_year,sale_month,sale_day) 7 (partition p1 values less than (1994,04,01) tablespace p1, 8 partition p2 values less than (1994,07,01) tablespace p2); Table created. SQL> insert into sales values (100,1994,2,1); 1 row created. SQL> insert into sales values (200,1994,6,1); 1 row created. SQL> SQL> select rowid from sales; ROWID ------------------ AAAAfOAAFAAAAADAAA AAAAfPAAGAAAAADAAA 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; 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) 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; 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); Table created. SQL> create table state ( 2 state_cd varchar(2) not null, 3 state_name varchar2(30)); Table created. SQL> create table x ( 2 col varchar2(30)); Table created. SQL> create table y ( 2 col varchar2(30)); Table created. SQL> insert into customer values ('Porter','CA', 6989.99); 1 row created. SQL> insert into customer values ('Martin','CA',2345.45); 1 row created. SQL> insert into customer values ('Laursen','CA',34.34); 1 row created. SQL> insert into customer values ('Bambi','CA',1234.55); 1 row created. SQL> insert into customer values ('McGraw','NJ', 123.45); 1 row created. SQL> insert into state (state_name, state_cd) 2 values ('Massachusetts','MA'); 1 row created. SQL> insert into state (state_name, state_cd) 2 values ('California','CA'); 1 row created. SQL> insert into state (state_name, state_cd) 2 values ('NewJersey','NJ'); 1 row created. SQL> insert into X values ('1'); 1 row created. SQL> insert into X values ('2'); 1 row created. SQL> insert into X values ('3'); 1 row created. SQL> insert into Y values ('3'); 1 row created. SQL> insert into Y values ('4'); 1 row created. SQL> insert into Y values ('5'); 1 row created. create or replace procedure samp (parm1 in varchar2, parm2 in varchar2) as -- This is the DECLARE section; when coding a stored object -– the DECLARE is implied, and need not be mentioned accum1 number; accum2 number; h_date date := sysdate; --Notice variables can be --initialized here too. 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; / set serveroutput on size 100000 declare state_rec person%rowtype; -- state_rec has same makeup -- as a row from the STATE table type just_names is table of state.name%type index by binary_integer; -- the local just_names -- table contains i binary_integer := 0; -- "rows" with the same -- makeup as each row in STATE nametab just_names; begin for state_rec in -- notice how the rows are -- fetched" from STATE by this query -- embedded in the FOR loop (select name from state) loop i := i+1; nametab(i) := state_rec.name; dbms_output.put_line (nametab(i)); end loop; end; / create or replace procedure do_trav (class_in in varchar2) as cursor mycur is --This is the start of a PL/SQL block select count(*) --Local variables and explicit from person --cursors are defined here where class_code = class_in; cnt number; begin --This inner "begin" is start of the open mycur; --code executed when the PL/SQL is fetch mycur into cnt; --invoked. while mycur%found loop if cnt > 100 then --"If" procedural logic insert into trav_audit (class_in,cnt) values (classin,cnt); --An implicit cursor else update trav_audit --Inline update statement set cnt = cnt+1 where classin = class_in; end if; --"If" statement ends with "end-if" fetch mycur into cnt; end loop; end; --Each "begin" terminated by end; --matching "end" keyword / --"/" terminates the PL/SQL block create or replace procedure get_dob (ss_num varchar2, dob out date) as begin -- Start of program select birth_date -- Implicit cursor into dob -- DOB and BIRTH_DATE must from person -- be same data type where soc_sec_num = ss_num; exception when no_data_found then error_notify (ss_num); -- Call another procedure end; end; / create or replace function get_dob (ss_num varchar2) return date is birthd date; --Local date field to hold birth date begin begin select dob into birthd --"into" mandatory for implicit cursor from person where soc_sec_num = ss_num; exception when no_data_found begin error_notify (ss_num); -- Call another procedure birthd := trunc(sysdate); end; when others then null; end; return birthd; end; end; --Termination of outer block / --"/" terminates PL/SQL block create or replace procedure ac_switch (oac number, nac number) as l_pref_3 number(3); -- Implicit DECLARE l_change_sw number(3); l_change_it varchar2(1); cursor mycur is select distinct pref_3 from phone_nbr where are_code = oac; begin -- Main processing open mycur; -- Open before fetch fetch mycur into l_pref_3; while mycur%found loop -- Start of loop change_it := 'N'; begin -- "begin" of select select '' -- with exception block into l_change_sw from static_exc where area_code = oac and pref_3 = l_pref_3; exception when no_data_found then -- Check success of select l_change_it := 'Y'; end; -- Close of select/exception if l_change_it = 'Y' then update phone_nbr set area_code = nac where area_code = oac and pref_3 = l_pref_3; end if; fetch mycur into l_pref_3; end loop; -- End of loop end; -- Main processing end; -- Procedure / rem * ----------------------------------------------------------- rem * Script to set up tables used in Chapter 9 from rem * Oracle8: A Beginner's Guide 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); create or replace procedure overdue_notice (borrower_ident char) as l_book_name varchar2(40); l_book_ident number; l_due_date date -- Get any overdue book numbers for current person cursor mycur is select book_name, book_ident, due_date from overdue_notes where borrower_id = borrower_ident; begin open mycur; fetch mycur into l_book_name, l_book_ident, l_due_date; while mycur%found loop -- Create an overdue notice insert into overdue_notes values (book_ident, book_name, borrower_ident, book_title, was_due, sysdate, user); fetch mycur into l_book_name, l_book_ident, l_due_date; end loop; end; / 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. 25-SEP-1998 clear screen prompt ******************************************* prompt Enter "ALL" to see all your tables prompt Or Enter a Partial table name accept tname prompt "Enter ALL/Partial Tablename....:" select table_name from user_tables where table_name like '%&tname%' or upper('&tname') = 'ALL'; rem **************** rem * setup.sql rem **************** rem This script contains common setup information and rem formatting information I use for many sql scripts rem Michael Corey 05/23/98 Created rem Michael Abbey 10/23/98 Changed pagesize to 60, rem to add additional rem 5 lines for new printer. rem Standard set-up set echo off set pagesize 60 set linesize 80 rem ************************************* rem * common column format statements 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 * End of script rem ******************** 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> -- Oracle8 cannot resolve the equality condition since the ’’ SQL> -- represents a zero length character string BUT NOT a null value SQL> SQL> select 12 from dual where null = ''; no rows selected SQL> SQL> -- One would think that Oracle8 would allow equality comparison between SQL> -- two ’’ strings, but not so! Watch out! SQL> SQL> select 12 from dual where '' = ''; no rows selected SQL> SQL> -- Even though the comparison is coded correctly with the null keyword, SQL> -- the null on the left side of the = is meaningless as is the one on SQL> -- the right; 2 meaningless values can’t be compared against one another SQL> SQL> select 12 from dual where null = null; no rows selected SQL> SQL> -- This in the only way to do it properly to get the desired results SQL> SQL> select 12 from dual where null is null; 12 --------- 12 SQL> SQL> -- Let’s do the same sort of thing using the nvl function SQL> SQL> select 12 from dual where nvl(null,'X') = nvl(null,'X'); 12 --------- 12 SQL> 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> -- Tweak the SQL*Plus environment by suppressing headings, SQL> -- feedback about number of rows fetched, and echoing of commands SQL> -- to the screen. SQL> set pages 0 feed off echo off SQL> -- Capture output to ana_all.sql 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 alter table person add constraint person_pk primary key (id) using index storage (initial 1m next 1m pctincrease 0) tablespace prd_indexes; 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 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> -- You must have select privileges on the v$parameter SQL> -- v$logfile v$dbfile and v$controlfile data SQL> -- dictionary views belonging to SYS to run SQL> -- this program 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 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> -- We inform Oracle that we are backing up a tablespace SQL> -- before doing the copy. This is done one tablespace SQL> -- at a time. After the tablespace is put in backup SQL> -- mode, make a copy of its datafile(s). By forcing SQL> -- a checkpoint after each tablespace is backed up, SQL> -- we synch the registering of the backup internally SQL> -- to Oracle. 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. Oracle Server Manager Release 3.0.3.0.1 - Production Copyright (c) Oracle Corporation 1994, 1995. All rights reserved. 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 SVRMGR> connect internal SVRMGR> spool scratch.log SVRMGR> set echo on SVRMGR> startup nomount pfile=d:\orant\database\initprac.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; Statement processed. SVRMGR> create rollback segment temp 2 tablespace system 3 storage (initial 50k minextents 2); Statement processed. 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 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; / analyze table outlet compute statistics for columns province size 10; create table SALES_94 as select * from PURCHASE_ORDER where po_date between to_date(’21-JAN-1994’,’DD-MON-YYYY’) and to_date(’31-DEC-1994’,’DD-MON-YYYY’) and line_item <= 3; create table SALES_95 as select * from PURCHASE_ORDER where po_date between to_date(’01-JAN-1995’,’DD-MON-YYYY’) and to_date(’31-DEC-1995’,’DD-MON-YYYY’) and line_item <= 3; create table SALES_96 as select * from PURCHASE_ORDER where po_date between to_date(’01-JAN-1996’,’DD-MON-YYYY’) and to_date(’31-DEC-1996’,’DD-MON-YYYY’) and line_item <= 3; create table SALES_97 as select * from PURCHASE_ORDER where po_date between to_date(’01-JAN-1997’,’DD-MON-YYYY’) and to_date(’31-DEC-1997’,’DD-MON-YYYY’) and line_item <= 3; create table SALES_98 as select * from PURCHASE_ORDER where po_date between to_date(’01-JAN-1998’,’DD-MON-YYYY’) and to_date(’31-DEC-1998’,’DD-MON-YYYY’) and line_item <= 3; create table SALES_99 as select * from PURCHASE_ORDER where po_date between to_date(’01-JAN-1999’,’DD-MON-YYYY’) and to_date(’31-DEC-1999’,’DD-MON-YYYY’) 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;