Guida a Oracle Database 10g.

Esempi di codice:

*****************************************
Capitolo 1: Elementi di base dei database 
*****************************************

create table tablename (
   name        varchar2(30),
   city        varchar2(30),
   state       varchar2(2));

create table timestamp_test (ts timestamp);

insert into timestamp_test values (sysdate);

select * from timestamp_test;

create table date_test (d date);

insert into date_test values (sysdate);

select * from date_test;

create table part_master (
      id                   number(8) not null,
      manufacturer_code    number(4) not null,
      inception            date not null,
      description          varchar2(60) not null,
      unit_price           number(6,2) not null,
      in_stock             varchar2(1));

create or replace view emp_hq
as select * from employee_master
where loc_id = '2';

alter user hr quota 500m on hr_data;

alter user ap quota unlimited on ap_idx;

select count(*) 
from part_master
where in_stock is not null;


*******************************************
Capitolo 2: SQL (Structured Query Language)
*******************************************

create table b
(colb char(1));

drop table b;

create table state
(state_cd   char(2) not null,
state_name varchar2(30));

desc state;

insert into state values ('AZ','Arizona');

insert into state (state_cd, state_name) values ('NJ','New Jersey');

insert into state (state_cd, state_name) values ('CA','California');

insert into state (state_cd, state_name) values ('TX','Texas');

insert into state (state_cd, state_name) values ('FL','Florida');

insert into state (state_cd, state_name) values ('MN','Maine');

select *
from   state;

select state_name
from   state;

select *
from   a;

select *
from   a
/

desc customers;
desc products;
desc sales;
desc costs;
desc promotions

select prod_id, prod_name, prod_category, prod_list_price
from   products
where  prod_id = 117;

select cust_id, cust_state_province, cust_credit_limit
from   customers
where  cust_state_province = 'UT'
and    cust_credit_limit > 10000;

select prod_id, prod_category, prod_weight_class
from   products
where  prod_category = 'Hardware'
or     prod_weight_class = 4;

select prod_id, prod_category, prod_weight_class
from   products
where  prod_weight_class != 1;

select cust_id, cust_gender, cust_year_of_birth
from   customers
where  cust_state_province = 'CT'
and    cust_gender = 'M'
and    cust_year_of_birth between 1936 and 1939;

select cust_id, cust_state_province, cust_credit_limit 
from   customers
where  cust_credit_limit = 15000
and    cust_state_province in ('UT','CT');

select cust_last_name, cust_credit_limit
from   customers
where  cust_last_name like 'Q%';

select cust_last_name
from   customers
where  cust_last_name like '%inl%';

select cust_id, cust_credit_limit
from   customers
where  cust_id = 28983;

update customers
set    cust_credit_limit = 20000
where  cust_id = 28983;

select cust_id, cust_credit_limit
from   customers
where cust_id = 28983;

select count(*)
from   customers
where  cust_state_province = 'Delhi';

delete from customers
where cust_state_province = 'Delhi';


select count(*)
from   customers
where  cust_state_province = 'Delhi';


select count(*)
from   customers
where  cust_state_province = 'Delhi';

select cust_last_name, cust_city 
from   customers
where  cust_state_province = 'UT'
and    cust_credit_limit < 5000;

select cust_id, cust_state_province, cust_credit_limit
from   customers
where  cust_credit_limit = 15000
and    cust_state_province in ('UT','CT')
order by cust_state_province, cust_id;

select round(months_between('17-MAR-61','21-APR-62'))
from dual;

select prod_id, quantity_sold, cust_city, cust_state_province
from   sales, customers
where  sales.cust_id = customers.cust_id
and    prod_id = 117;

select prod_id, quantity_sold, cust_city, cust_state_province
from   sales s, customers c
where  s.cust_id = c.cust_id
and    prod_id = 117;

select s.prod_id, s.quantity_sold, c.cust_id,
c.cust_city, c.cust_state_province
from   sales s, customers c
where  s.cust_id = c.cust_id
and    s.prod_id = 117;

select c.country_id, p1.promo_name, p2.prod_category, s.quantity_sold,    
from   sales s,
       customers c,
       promotions p1,
       products p2
where  s.cust_id = c.cust_id
and    s.promo_id = p1.promo_id
and    s.prod_id = p2.prod_id
and    s.prod_id = 117;

select c.cust_id, c.cust_state_province, 
       s.quantity_sold, s.prod_category
from sales s join customers c
  on s.cust_id = c.cust_id
where prod_id = 117;

select cust_id, c.cust_state_province, 
       s.quantity_sold, s.prod_category
from sales s join customers c
  using (cust_id)
where prod_id = 117;

select c.cust_id, c.cust_state_province, 
       s.quantity_sold, p.prod_name
from   sales s 
  join customers c
    on s.cust_id = c.cust_id
  join products
    on s.prod_id = p.prod_id
where p.prod_id = 117
and c.country_id = 52790;

select cust_id, c.cust_state_province, 
       s.quantity_sold, p.prod_name
from   sales s 
  join customers c using (cust_id)
  join products p using (prod_id)
where p.prod_id = 117
and c.country_id = 52790;

select cust_id, c.cust_state_province, 
       s.quantity_sold, p.prod_name
from sales s 
   natural join customers c
   natural join products p
where prod_id = 117;

select c.cust_id, c.cust_last_name, s.prod_id, s.quantity_sold
from   customers c, sales s
where  c.cust_id = s.cust_id(+)
and    c.cust_id in (1,80);



select c.cust_id, c.cust_last_name, s.prod_id, s.quantity_sold
from sales s right join customers c
   on c.cust_id = s.cust_id
where c.cust_id in (1,80);


select id, desc1, desc2
from   temp2 right outer join temp1
        using (id);

select id, desc1, desc2
from   temp2 b natural left join temp1 a;

select a.id, a.desc1, b.id, b.desc2
from   temp1 a full join temp2 b
       on a.id = b.id;

select a.name, a.birth_year,
       a.father, b.birth_year    
from   family a, family b, family c
where  a.father = b.name;

select avg(amount_sold) 
from   sales s, products p
where  s.prod_id = p.prod_id
and    prod_category = 'Electronics';

select prod_subcategory, avg(amount_sold)
from   sales s, products p
where  s.prod_id = p.prod_id
and    prod_category = 'Electronics'
group by prod_subcategory;

select prod_subcategory, avg(amount_sold)
from   sales s, products p
where  s.prod_id = p.prod_id
and    prod_category = 'Electronics'
group by prod_subcategory
having avg(amount_sold) > 300;

select prod_subcategory, avg(amount_sold)
from   sales s, products p
where  s.prod_id = p.prod_id
and    prod_category = 'Electronics'
group by prod_subcategory;

select prod_subcategory, prod_name, avg(amount_sold)
from   sales s, products p
where  s.prod_id = p.prod_id
and    prod_category = 'Electronics'
group by prod_subcategory, prod_name;

select prod_subcategory, prod_name, avg(amount_sold)
from   sales natural join products
where  prod_category = 'Electronics'
and    prod_subcategory != 'Home Audio'
group by prod_subcategory, prod_name;

select prod_subcategory, prod_name, max(amount_sold), avg(amount_sold)
from   sales natural join products
where  prod_category = 'Electronics'
and    prod_subcategory != 'Home Audio'
group by prod_subcategory, prod_name;

select substr(prod_subcategory,1,18),
       substr(prod_name,1,25), 
       max(amount_sold), 
       avg(amount_sold)
from   sales natural join products
where  prod_category = 'Electronics'
and    prod_subcategory != 'Home Audio'
group by substr(prod_subcategory,1,18), 
         substr(prod_name,1,25);

select substr(prod_subcategory,1,18) Subcategory, 
       substr(prod_name,1,25) Product_Name, 
       max(amount_sold) Max_Amt_Sold, 
       round(avg(amount_sold),2) Avg_Amt_Sold
from   sales natural join products
where  prod_category = 'Electronics'
and    prod_subcategory != 'Home Audio'
group by substr(prod_subcategory,1,18), 
       substr(prod_name,1,25);

select substr(prod_subcategory,1,18) Subcategory, 
       substr(prod_name,1,25) Product_Name, 
       max(amount_sold) Max_Amt_Sold, 
       round(avg(amount_sold),2) Avg_Amt_Sold
from   sales natural join products
where  prod_category = 'Electronics'
and    prod_subcategory != 'Home Audio'
group by substr(prod_subcategory,1,18), 
         substr(prod_name,1,25)
having max(amount_sold) > 10
and    avg(amount_sold) > 10
order by substr(prod_subcategory,1,18), 
         substr(prod_name,1,25);

select prod_category, prod_subcategory, prod_name, prod_list_price
from   products
where  prod_list_price > 100
order by prod_category, prod_subcategory, prod_name;

select prod_category, prod_subcategory, sum(amount_sold)
from   products natural join sales
group by prod_category, prod_subcategory;

select c.cust_id, cust_last_name, count(*)
from   customers c, sales s
where  c.cust_id = s.cust_id
group by c.cust_id, cust_last_name
having count(*) = 1;

select prod_id, prod_name, prod_category
from   products
where  prod_list_price = (select max(prod_list_price)
                          from   products);

select substr(prod_category,1,22) Category, 
        substr(prod_name,1,39) Product, 
        prod_list_price List
from   products p
where  prod_list_price > (select avg(prod_list_price)
                          from   products
                          where  p.prod_category = prod_category)
order by substr(prod_category,1,22), prod_list_price desc;

select a.id, a.desc1, b.id, b.desc2
from   temp1 a, temp2  b
where  a.id = b.id(+);

select a.id, a.desc1, b.id, b.desc2
from   temp1 a, temp2  b
where  a.id(+) = b.id;

select a.id, a.desc1, b.id, b.desc2
from temp1 a, temp2 b
where a.id = b.id(+)
union 
select a.id, a.desc1, b.id, b.desc2
from temp1 a, temp2 b
where a.id(+) = b.id

create view utah_sales
as
select c.cust_id ID,
       substr(cust_last_name,1,20) Name,
       substr(cust_city,1,20) City,
       substr(cust_state_province,1,5) State,
       sum(amount_sold) Total
from   customers c, sales s
where  c.cust_id = s.cust_id
and    cust_state_province = 'UT'
group by c.cust_id, 
        substr(cust_last_name,1,20),
        substr(cust_city,1,20), 
        substr(cust_state_province,1,5);

desc utah_sales

select *                
from   utah_sales;

select name, total
from   utah_sales
where  total > 20000;


select cust_id_seq.nextval
from dual;

insert into promotions
  2    (promo_id,
  3     promo_name,
  4     promo_subcategory,
  5     promo_subcategory_id,
  6     promo_category,
  7     promo_category_id,
  8     promo_cost,
  9     promo_begin_date,
 10     promo_end_date,
 11     promo_total,
 12     promo_total_id)
 13  values
 14    (36,
 15     'Thanksgiving Sale',     
 16     'Newspaper',
 17     28,
 18     'ad news',
 19     4,
 20     250,
 21     '23-NOV-03',
 22     '27-NOV-03',
 23     'Promotion Total',
 24     5);
insert into promotions
*
ERROR at line 1:
ORA-00001: unique constraint (SH.PROMO_PK) violated


create table check_constraint_example
    (col1 char(1)
      constraint check_col1
         check (col1 in ('B','G','N')));

insert into check_constraint_example values ('B');

insert into check_constraint_example values ('C');

insert into check_constraint_example values ('C')
*
ERROR at line 1:
ORA-02290: check constraint (SH.CHECK_COL1) violated

select cust_last_name, cust_city, sum(amount_sold)
from   customers natural join sales
where  cust_state_province = 'UT'
group by cust_last_name, cust_city;

set pagesize 15
set linesize 70
ttitle 'Customer Sales Report | Utah Region'
btitle 'CONFIDENTIAL REPORT'
column cust_last_name format a12 wrap heading 'Last | Name'
column cust_city format a15 heading 'City'
column sum(amount_sold) format $999,999.99 wrap heading 'Total | Sales'

select cust_last_name, cust_city, sum(amount_sold)
from   customers natural join sales
where  cust_state_province = 'UT'
group by cust_last_name, cust_city;

select cust_last_name, cust_city, cust_state_province, amount_sold
from   customers c, sales s
where  c.cust_id = s.cust_id
and    c.cust_id = 100895;

select UPPER(prod_category), 
       prod_subcategory, 
       prod_name, 
       prod_list_price
from   products
where  prod_list_price > 100;

select UPPER(prod_category), 
          prod_subcategory, 
          prod_name, 
          round(prod_list_price,0)
from   products
where  prod_list_price > 100;

select c.cust_id, cust_last_name, count(*)
from   customers c, sales s
where  c.cust_id = s.cust_id
group by c.cust_id, cust_last_name
having count(*) > 200;

select prod_name
from   products
where  prod_list_price = (select min(prod_list_price)
                            from   products);

create view electronics_products
as
select prod_name
from   products
where  prod_category = 'Electronics';

*****************************************
Capitolo 3: L'amministratore del database 
*****************************************

Startup;

startup nomount;

startup mount;
alter database mount;

Startup {open} {resetlogs};
alter database open;

alter database open read only;

startup restrict; 

alter system quiesce restrict;
alter system unquiesce;

startup force;

shutdown normal;

shutdown immediate;

shutdown transactional;

shutdown abort;

select owner, object_name, object_type
from dba_objects where status ^= ‘VALID’;

alter database datafile ‘/u01/oradata/ora10g/example01.dbf’
autoextend on next 50M maxsize 5000M;

CREATE USER "NEWUSER" PROFILE "DEFAULT" IDENTIFIED BY "newpassword"
PASSWORD EXPIRE DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"
QUOTA UNLIMITED ON TEMP QUOTA UNLIMITED ON USERS
ACCOUNT UNLOCK;
GRANT "CONNECT" TO "NEWUSER";

ALTER USER "username"  IDENTIFIED BY "newpwd " QUOTA UNLIMITED
ON TOOLS ACCOUNT UNLOCK;

GRANT SELECT ON “TABLE_NAME” TO "NEWUSER" WITH GRANT OPTION;
GRANT INSERT ON “TABLE_NAME” TO "NEWUSER" WITH GRANT OPTION;
GRANT EXECUTE ON "PROCEDURE_NAME

REVOKE INSERT ON "TABLE_NAME" FROM "NEWUSER";
REVOKE EXECUTE ON "TABLE_NAME" FROM "NEWUSER;

CREATE ROLE "TESTROLE”;
GRANT CONNECT TO "TESTROLE"
GRANT EXECUTE ANY PROCEDURE TO "TESTROLE"
GRANT SELECT ON "table_name" TO "TESTROLE"

Grant “testrole” to “Testuser”;

DROP ROLE "TESTROLE";

CREATE PROFILE "NEWPOLICY"
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 120
PASSWORD_VERIFY_FUNCTION COMPLEX_PASSWORD

ALTER USER NEWUSER PROFILE NEWPOLICY;

CREATE TABLESPACE "NEW_TS" LOGGING
DATAFILE 'C:\ORACLE\ORA10\ORA10G\NEW_TS1.ora' SIZE 2M REUSE AUTOEXTEND ON
NEXT  1280K MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 96K SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER "NEW_USER"  PROFILE "DEFAULT" IDENTIFIED BY "new_password" PASSWORD EXPIRE DEFAULT TABLESPACE "NEW_TS" 
TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED     ON "TEMP”;

CREATE ROLE "NEW_ROLE"  NOT IDENTIFIED;

GRANT CREATE TABLE TO "NEW_ROLE";
GRANT SELECT ON  "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" TO "NEW_ROLE";
GRANT "OLAP_USER" TO "NEW_ROLE";

GRANT "NEW_ROLE" TO "NEW_USER";
ALTER USER "NEW_USER" DEFAULT ROLE  ALL;
ALTER USER "NEW_USER"  QUOTA UNLIMITED ON "NEW_TS";

CREATE TABLE "NEW_USER"."NEW_TABLE"
("COL01" NUMBER(15) NOT NULL,
 "COL02" VARCHAR2(30) NOT NULL,
CONSTRAINT "NEW_TABLE_PK" PRIMARY KEY("COL01"),
CONSTRAINT "NEW_TABLE_U1" UNIQUE("COL01"))
TABLESPACE "NEW_TS";

ALTER DATABASE DATAFILE '/u01/oradata/ORA10G/UNDOTBS01.DBF' RESIZE  150M;

*******************************
Capitolo 4: Connessioni di rete
*******************************

NLS_LANG = language_territory.charset

DB_NAME=customer
DB_DOMAIN=us.trubix.com

DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=5)'

DISPATCHERS='(ADDRESS=(PROTOCOL=TCP)(DISPATCHERS=5))'

DISPATCHERS="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
             (HOST=eclipse)(PORT=1521)(QUEUESIZE=20)))
             (DISPATCHERS=2)
             (SERVICE = customer.us.trubix.com)
             (SESSIONS=2000)
             (CONNECTIONS = 2000)
             (MULTIPLEX = ON)
             (POOL = ON)
             (TICK = 5)"

(DESCRIPTION =
  (ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
  (CONNECT_DATA=
      (SERVICE_NAME=customer.us.trubix.com)))

(DESCRIPTION =
  (ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
 (CONNECT_DATA=
      (SERVICE_NAME=customer.us.trubix.com)
       (SERVER=dedicated)))

-- Definizione manuale di un descrittore di connessione
CONNECT
username/password@(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp) (HOST=eclipse) (PORT=1521))  (CONNECT_DATA= (SERVICE_NAME=customer.us.trubix.com)))
-- Connessione tramite un descrittore predefinito
CONNECT username/password@cust

cmctl {comando} [parametro1 … parametroN] {-c nome_istanza} {-p password}

cmctl
CMCTL> startup –c cman0

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = eclipse)(PORT = 1521))
      )
    )
  )

lsnrctl> change_password
Old password: <enter>
New password: newpassword
Reenter new password: newpassword
lsnrctl> save_config

(DESCRIPTION =
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
   (CONNECT_DATA=
      (SERVICE_NAME=customer.us.trubix.com)))

(DESCRIPTION =
  (ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
 (CONNECT_DATA=
      (SERVICE_NAME=customer.us.trubix.com)))

CUST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = eclipse)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CUST)
    )
  )

CONNECT username/password@eclipse:1521/customer.us.trubix.com/cust

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = eclipse)(PORT = 1521))
      )
    )
  )

lsnrctl> change_password
Old password: <enter>
New password: newpassword
Reenter new password: newpassword
lsnrctl> save_config

(DESCRIPTION =
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
   (CONNECT_DATA=
      (SERVICE_NAME=customer.us.trubix.com)))

(DESCRIPTION =
  (ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
 (CONNECT_DATA=
      (SERVICE_NAME=customer.us.trubix.com)))

CUST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = eclipse)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CUST)
    )
  )

CONNECT username/password@eclipse:1521/customer.us.trubix.com/cust

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = eclipse)(PORT = 1521))
      )
    )
  )

$  $ORACLE_HOME/bin/netca

lsnrctl command [nome_listener]

$ lsnrctl
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
The command completed successfully

LSNRCTL> start

starting tnslsnr: please wait...
 TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 -
System parameter file is C:\oracle\ora10\network\admin\listener.ora
Log messages written to C:\oracle\ora10\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.2.0
Start Date                03-FEB-2004 21:26:56
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   C:\oracle\ora10\network\admin\listener.ora
Listener Log File         C:\oracle\ora10\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521)))
Services Summary...
Service "cust" has 1 instance(s).
  Instance "cust", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

LSNRCTL> status

LSNRCTL> services

LSNRCTL> set
password                           rawmode
displaymode                        trc_file
trc_directory                      trc_level
log_file                           log_directory
log_status                         current_listener
inbound_connect_timeout            startup_waittime
save_config_on_stop

DISPATCHERS="(PROTOCOL=tcp)(DISPATCHERS=6)(CONNECTIONS=1000)"
DISPATCHERS="(PROTOCOL=tcps)(DISPATCHERS=6)(CONNECTIONS=1000)"

DISPATCHERS="(PROTOCOL=tcp)(DISPATCHERS=6)(POOL=on) (TICK=1)(CONNECTIONS=1000)(SESSIONS=5000)"
DISPATCHERS="(PROTOCOL=tcps)(DISPATCHERS=6)(POOL=on) (TICK=1)(CONNECTIONS=1000)(SESSIONS=5000)"

eclipse     customer.us.trubix.com         

ping eclipse

ping 122.23.20.24

lsnrctl start listener_name

lsnrctl services listener_name

cust=
(DESCRIPTION =
  (ADDRESS=(PROTOCOL=tcp)(HOST=eclipse)(PORT=1521))
 (CONNECT_DATA=
      (SERVICE_NAME=customer.us.trubix.com)))

CONNECT username/password@cust

tnsping net_service_name  [count]

trcroute net_service_name

NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)

*******************************
Capitolo 5: Backup e ripristino
*******************************

alter database backup controlfile to trace;                -- backup di testo
alter database backup controlfile to ‘/directory/file’;    -- backup binario

alter tablespace <nome_tablespace> begin backup;
[comando di copia del sistema operativo: cp in unix o ocopy in Windows]
alter tablespace <nome_tablespace> end backup;


SQL> recover database until cancel;
SQL> recover database until change 1234567;
SQL> recover database until time ‘2004-04-15:14:33:00’;

select * from dba_tablespaces;  -- Informazioni sulle tablespace
select * from dba_data_files;   -- Informazioni sui file di dati
select * from v$datafile;       -- Ulteriori informazioni sui file di dati
select * from v$logfile;        -- Informazioni sui file log
select * from v$log;            -- Ulteriori informazioni sui file log
select * from v$controlfile;    -- Informazioni sui file di controllo
select * from v$parameter;      -- Parametri attivi
select * from v$nls_parameters; -- Lingua attiva
-- Richiede le informazioni sui log per gli ultimi 3 giorni
select * from v$log_history where first_time > sysdate - 3;

set echo on;
spool /u02/backup/ora10g/hotBackup1.lst;
alter system archive log current;
alter tablespace INDX begin backup;
! cp /u01/oradata/ora10g/indx01.dbf /u02/backup/ora10g
alter tablespace INDX end backup;
alter tablespace TABLESPACE_n begin backup;
! cp /u01/oradata/ora10g/tablespace_n01.dbf /u02/backup/ora10g
! cp /u01/oradata/ora10g/tablespace_n02.dbf /u02/backup/ora10g
alter tablespace TABLESPACE_n end backup;
… tablespace …
alter tablespace SYSTEM begin backup;
! cp /u01/oradata/ora10g/system01.dbf /u02/backup/ora10g
alter tablespace SYSTEM end backup;
-- Backup del file log
alter system archive log current;
-- Crea 3 copie del file di controllo di backup
alter database backup controlfile to /u02/backup/ora10g/CONTROL01.CTL' reuse;
alter database backup controlfile to /u02/backup/ora10g/CONTROL02.CTL' reuse;
alter database backup controlfile to /u02/backup/ora10g/CONTROL03.CTL' reuse;
-- Crea una versione di testo del file di controllo di backup
alter database backup controlfile to trace;
spool off;
exit;

# Pseudocodice for liberare spazio di archiviazione
# Controlla lo spazio usato e lo spazio libero: č uno script molto semplice
# df –k (in linux: la posizione dei campi varia a seconda della piattaforma)
# Filesystem  1k-blocks  Used  Available Use% Mounted on
# /dev/hda3    3763404 102676    3469556   3%          /
Log_arch_dest='/u01/oradata/db01/arch'
arch_dir_mountPoint=`df -k ${log_arch_dest}|grep -v blocks|awk '{print $6}'`
arch_dir_freeSpace=`df -k ${log_arch_dest}|grep -v blocks|awk '{print $4}'`
arch_dir_used=`df -k ${LOG_ARCH_DEST}|grep -v blocks|awk '{print $3}'`
if [${arch_dir_freeSpace} -le ${arch_dir_Used}]; then
  echo "Inserire qui la logica di archiviazione"
fi

create or replace directory DPUMP_DIR as ‘\u01\’;
grant read, write on dpump_dir to export_user;

expdp system/manager DUMPFILE=expdat.dmp FULL=y LOGFILE=export.log

expdp system/manager DUMPFILE=expdat.dmp SCHEMAS=sh LOGFILE=export.log

expdp system/manager DUMPFILE=expdat.dmp TABLES=sh.customers LOGFILE=export.log

expdp sh/sh parfile=exp.par

      File exp.par:
DIRECTORY=DPUMP_DIR
DUMPFILE=testsh.dmp
CONTENT=DATA_ONLY
EXCLUDE=TABLE:”in (‘PROMOTIONS’)”
QUERY=customers:”where cust_id=1”

expdp system/manager parfile=exp.par

      File exp.par:
DIRECTORY=DPUMP_DIR
DUMPFILE=testsh.dmp
SCHEMAS=SH
EXCLUDE=TABLE:”in (‘SALES’)”

impdp system/manager parfile=imp.par
      
      File imp.par:
DIRECTORY=DPUMP_DIR
DUMPFILE=testsh.dmp
REMAP_SCHEMA=SH:SHNEW
TABLES=SH.PRODUCTS
TABLE_EXISTS_ACTION=SKIP

exp system/manager full=y grants=y rows=y triggers=y buffer=10000000 direct=n consistent=Y constraints=Y compress=Y file=shexp.dmp log=shexp.log

exp system/manager OWNER=SH grants=y rows=y triggers=y buffer=10000000 direct=n consistent=Y constraints=Y compress=Y file=shexp.dmp log=shexp.log

exp SYSTEM/manager parfile=exp.par

       File exp.par:
TABLES=(SH.PRODUCTS) grants=y rows=y triggers=y direct=Y consistent=Y constraints=Y compress=Y file=shexp.dmp log=shexp.log

imp system/manager FROMUSER=SH TOUSER=SHNEW ignore=y compile=y destroy=n grants=y rows=y buffer=10000000 constraints=y file=shexp.dmp log=shimp.log

RMAN:
create tablespace rcatts datafile ‘/u01/oradata/rcatts.dbf’ size 10M;
create user rcat identified by rcat temporary tablespace temp default tablespace rcatts quota unlimited on rcatts;
grant connect, resource, recovery_catalog_owner to rcat;

$ rman catalog=rmancat/rmancat

RMAN> create catalog

$ rman
RMAN> connect catalog rmancat/rmancat@sid
RMAN> connect target sys/pwd@sid
RMAN> register database;

RMAN> create script b_whole_l0 {
allocate channel c1 type disk;
backup
      incremental level 0
      format /u01/backup/b_t%t_s%s_p%p’
      (database);
sql ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
backup (archivelog all delete input);          }


run { allocate channel c1 type disk;
set limit channel c1 readrate=50; …}

configure backup optimization {ON | OFF | CLEAR}

configure controlfile autobackup;

configure channel 1 device type disk format ‘/orarecover/backup/db01/tp_%U’;

configure retention policy to redundancy 4;

configure retention policy to recovery window of 15 days;

List backup by file

Report obsolete

RMAN > connect catalog rmancat/rmancat@test2
RMAN > connect target sys/lexus4me@TEST1
RMAN > backup database;

RMAN > run {
RMAN > allocate channel c1 type disk;
RMAN > allocate channel c2 type disk;
RMAN > allocate channel c3 type disk;
RMAN > backup (datafile 1,2,3 filesperset =3 channel c1)
RMAN > (archivelog all channel c3);}

RMAN > backup tablespace EXAMPLE include controlfile plus archivelog;

replace script BackupTEST1 {
configure backup optimization on;
configure channel device type disk;
sql 'alter system archive log current';
backup database incremental 2 cumulative database;
release channel d1;
}
run {execute script BackupTEST1;}

RMAN > run { allocate channel c1 type disk;
RMAN > copy datafile 1 to ‘/u01/back/system.dbf’;}

RMAN> connect catalog rmancat/rmancat@ora10g
RMAN> connect target sys/change_on_install@ora10g

MAN> replace script fullRestoreTEST1 {
allocate channel ch1 type disk;
# Stabilisce una nuova posizione per i log
set archivelog destination to '/TD70/sandbox/TEST1/arch';
startup nomount;
restore archivelog from logseq 2123 until logseq 2145;
restore controlfile;
alter database mount;
restore database;
recover database;
alter database open resetlogs;
release channel ch1;
}
host 'echo "start `date`"';
run {execute script fullRestoreTEST1;}
host 'echo "stop `date`"';
exit

$ export ORACLE_SID=oracat

$ sqlplus /nolog

connect sys/change_on_install as sysdba

create tablespace rcatts datafile '/u01/oradata/oracat/rcatts.dbf' size 50M;

create user rcat identified by rcat temporary tablespace temp default
tablespace rcatts quota unlimited on rcat;

grant connect, resource, recovery_catalog_owner to rcat;

exit;

rman catalog=rcat/rcat@oracat
RMAN> catalog rcat/rcat@oracat
RMAN> create catalog;
RMAN> exit;

RMAN> connect catalog rcat/rcat@oracat
RMAN> connect target sys/manager@ora10g
RMAN> register database;

RMAN> configure default device type to disk;
RMAN> configure controlfile autobackup on;
RMAN> backup database plus archivelog;
RMAN> exit;

RMAN> connect catalog rcat/rcat@oracat
RMAN> connect target sys/manager@ora10g
RMAN> startup nomount;
RMAN> restore archivelog all;
RMAN> restore controlfile;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

******************
Capitolo 6: PL/SQL
******************

[DECLARE]
	— Variabili
BEGIN
	— Programma
[EXCEPTION]
	— Gestione delle eccezioni
END;
/

nome_variabile   tipo_variabile   :=   valore;


nome_variabile   :=   valore;

-- Sezione di dichiarazione del programma
declare
	l_counter number := 0; -- inizializza il valore a 0
	l_today date := sysdate; -- assegna a una variabile la data corrente
	l_name varchar2(50); -- la variabile viene definita ma non contiene alcun valore
-- Sezione di esecuzione del programma
begin
	l_counter := l_counter + 1; -- aggiunge 1 al valore corrente di counter
	l_name := ‘YOGI THE CAT’; -- assegna un valore a una variabile
-- Sezione di gestione degli errori del programma
exception
	-- Gestione generica degli errori per trattare ogni tipo d’errore
	when others then
-- Stampa di un messaggio d’errore
raise_application_error (-20100, ‘error#’ || sqlcode || ‘ desc: ‘ sqlerrm)
end;
nome_variabile	varchar2(max_length);

l_name		varchar2(30);

l_name	varchar2(30)	:= 'ABRAMSON';

variabile number(lunghezza, posizioni_decimali);

l_average_amount	number(12,2);

variabile 	date;

Declare
	l_start_date	date;
Begin
	l_start_date := '29-SEP-05';   -- imposta la variabile 
                                     -- al 29 settembre 2005.
	l_start_date := to_date('29-SEP-2083 ', 'DD-MON-YYYY');   -- imposta la variabile al 29 settembre 2083
	l_start_date := to_date('09-JUN-91:13:01 ', 'DD-MON-YY:HH24:MI');  
                 -- imposta la variabile al 9 giugno 1991, ore 13:01
End;

L_record_goals := 91;
L_season_goals := 77;
-- la seguente espressione sarŕ vera (true)
L_record_goals > l_season_goals
-- mentre la prossima č falsa (false)
l_record_goals <= l_season_goals

l_Cognos_developer := 'Falcon';
l_Oracle_dba := 'Ruxpinnah';
-- la seguente espressione sarŕ vera (true)
l_Cognos_developer != l_oracle_dba

v_product_id 	products.prod_id%TYPE

Declare
      l_start_date   date;
Begin
         l_start_date := '29-SEP-2005'; 
         dbms_output.putline (l_start_date); -- mostra la data
End;
/

Output:
29-SEP-05
PL/SQL procedure successfully completed.

  l_start_date := to_date('14-JAN-2063', 'DD-MON-YYYY');
  dbms_output.put_line (l_start_date);
  l_start_date := to_date('09-JUN-91:13:01', 'DD-MON-YY:HH24:MI');
  dbms_output.put_line (l_start_date);

select prod_name
into   v_prod_name
from products;

declare
  v_prod_name varchar2(80);
  begin
       select prod_name
       into   v_prod_name
       from products
       where rownum = 1;
     dbms_output.put_line(v_prod_name);
end;
/

select prod_name, prod_list_price, prod_min_price 
from products
where rownum < 10;

declare
    v_prod_name varchar2(80);
    cursor get_data is
     select prod_name
     from products;
  begin
     open get_data;
     fetch get_data into v_prod_name;
     dbms_output.put_line(v_prod_name);
     close get_data;
end;
/

 set serveroutput on
  declare
    v_prod_name varchar2(80);
    cursor get_data is
     select prod_name
     from products;
  begin
     for i in get_data
     LOOP
             dbms_output.put_line(i.prod_name);
     END LOOP;
end;
/

BEGIN	
   -- Programma
EXCEPTION
-- gestione delle eccezioni
END;
/

raise_application_error (-20123, 'Questo č un errore');

run
  declare
         l_emp_count number;
         i           number;  -- contatore
         l_row       employee%rowtype;
    begin
             select *
             into l_row
             from   employee
             order by emp_name;
  EXCEPTION
   WHEN no_data_found then
    raise_application_error (-20052,'La tabella non contiene dati');
   WHEN others then
     raise_application_error (-20999,'Si č verificato un errore, meglio controllare');
* end; 
*
ERROR at line 1:
ORA-20052: La tabella non contiene dati
ORA-06512: at line 12

Declare
	L_counter 	number := 0;
	L_name	employee.employee_name%type;
	Cursor get_employee_name is
	Select employee_name
	From employee;
excep_old_friend	Exception;
never_met_them		Exception;
Begin
	Open	get_employee_name;
	Fetch	get_employee_name into l_name;
	If l_name = 'CARL DUDLEY' then
		Raise excep_old_friend;
Else
	Rasie excep_never_met_them;
	End if;
	Close get_employee_name;
Exception
	When excep_old_friend then
		Dbms_output.put_line('Conosco questa persona'); 	
When    excep_old_friend then
		Dbms_output.put_line('Non conosco questa persona');
End;

excep_old_friend	Exception;
never_met_them		Exception;

		Raise excep_old_friend;

Exception
	When excep_old_friend then
		Dbms_output.put_line('Conosco questa persona');

Declare
	L_counter 	number := 0;
	L_name	employee.employee_name%type;
	Cursor get_employee_name is
	Select employee_name
	From employee;
excep_old_friend	Exception;
never_met_them		Exception;
Begin
	Open	get_employee_name;
	Fetch	get_employee_name into l_name;
	If l_name = 'JACQUI DEBIQUE' then
		Raise excep_old_friend;
Else
	Rasie excep_never_met_them;
End if;
	Close get_employee_name;
Exception
	When excep_old_friend then
		Dbms_output.put_line('Conosco questa persona'); 	
When excep_never_met_them then
		Dbms_output.put_line('Non conosco questa persona');
    When others then
	          Dbms_output.put_line('Errore Oracle: ' || sqlcode);
              Dbms_output.put_line('Il messaggio di errore Oracle č: '|| sqlerrm);
End; 
/

IF l_date > '11-APR-63' then
	l_salary :=  l_salary * 1.15; -- Aumentare lo stipendio del 15%
END IF;

IF not(l_date <= '11-APR-63') then
	l_salary :=  l_salary * 1.15; -- Aumentare lo stipendio del 15%
END IF;

IF l_date > '11-APR-63' then
IF l_last_name = 'PAKMAN' then
			l_salary :=  l_salary * 1.15; -- Aumentare lo stipendio del 15%
END IF;
END IF;

IF l_date > '11-APR-63' then
		l_salary :=  l_salary * 1.15; -- Aumentare lo stipendio del 15%
ELSE
l_salary := l_salary * 1.05;  -- Aumentare lo stipendio del 5%
END IF;

IF l_date > '11-APR-63' then
If l_last_name = 'PAKMAN' then
			l_salary :=  l_salary * 1.15; -- Aumentare lo stipendio del 15%
ELSE
			l_salary :=  l_salary * 1.10; -- Aumentare lo stipendio del 10%
END IF;
ELSE
l_salary := l_salary * 1.05;  -- Aumentare lo stipendio del 5%
END IF;

IF l_city = 'OTTAWA' then
	L_team_name := 'SENATORS';
ELSIF l_city = 'BOSTON' then
	L_team_name := 'BRUINS';
ELSIF l_city = 'NEW YORK' then
	L_team_name := 'RANGERS'
ELSIF l_city = 'TORONTO' then
	L_team_name := 'MAPLE LEAFS';
END IF;

CASE variable
	WHEN espressione1 then valore1
	WHEN espression2 then valore2
	WHEN espression3 then valore3
	WHEN espression4 then valore4
	ELSE valore5
END;

 run
  declare
     val     varchar2(100);
     city    varchar2(20) := 'TORONTO';
  begin
     val := CASE city
                  WHEN 'TORONTO' then 'RAPTORS'
                  WHEN 'LOS ANGELES' then 'LAKERS'
                  WHEN 'BOSTON' then 'CELTICS'
                  WHEN 'CHICAGO' then 'BULLS'
                  ELSE 'NO TEAM'
            END;

     dbms_output.put_line(val); -- output sullo schermo
* end;
RAPTORS

PL/SQL procedure successfully completed.

/* Questo č un commento */
 -- Anche questo č un commento 

LOOP
	Istruzioni eseguibili;
END LOOP;

LOOP
	IF l_bank_balance >= 0 then EXIT;
	ELSE
		L_decision := 'ACCOUNT OVERDRAWN';
	END IF;
END LOOP;

WHILE l_sales_total < 100000 LOOP
	Select sales_amount into l_sale_amount from daily_sales;
	l_sales_total := l_sales_total + l_sale_amount;
END LOOP;

FOR contatore IN 1 .. 10
LOOP
	Istruzioni;
END LOOP;

    declare
       l_counter number;
    begin
       FOR l_counter IN REVERSE 1..5
       LOOP
               dbms_output.put_line(l_counter);
       END LOOP;
   end;
/

 run
  declare
     l_emp_count number;
     i           number;  -- Variabile contatore
  begin
   -- Seleziona il numero di dipendenti e lo inserisce nella variabile
     select count(*) into l_emp_count from employee;

     FOR i IN 1 .. l_emp_count  LOOP
             dbms_output.put_line('Dipendente ' || i);
     END LOOP;
* end;

  declare
    cursor get_data is
     select prod_name, prod_list_price
     from products;
  begin
     for i in get_data
     LOOP
        if i.prod_list_price > 50 then
             dbms_output.put_line(i.prod_name || ' Prezzo: ' || i.prod_list_price);
        end if;
     END LOOP;
* end;

     else
        dbms_output.put_line(i.prod_name || ' Prodotto a meno di 50$');

  create or replace procedure print_products
  as
  declare
    cursor get_data is
     select prod_name, prod_list_price
     from products;
  begin
     for i in get_data
     LOOP
        if i.prod_list_price > 50 then
             dbms_output.put_line(i.prod_name || ' Prezzo: ' || i.prod_MIN_price);
        else
             dbms_output.put_line(i.prod_name || ' Prodotto a meno di 50$');
        end if;
     END LOOP;
* end;
/
Warning: Procedure created with compilation errors.


show errors

Errors for PROCEDURE PRINT_PRODUCTS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1      PLS-00103: Encountered the symbol "DECLARE" when expecting one of
         the following:
         begin function package pragma procedure subtype type use
         <an identifier> <a double-quoted delimited-identifier> form
         current cursor external language
         The symbol "begin" was substituted for "DECLARE" to continue.

16/4     PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         begin case declare end exception exit for goto if loop mod
         null pragma raise return select update while with
         <an identifier> <a double-quoted delimited-identifier>
         <a bind variable> << close current delete fetch lock insert
         open rollback savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe
         <an alternatively-quoted SQL string>


create or replace procedure print_products
  as
    cursor get_data is
     select prod_name, prod_list_price
     from products;
  begin
      for i in get_data
     LOOP
     if i.prod_list_price > 50 then
      dbms_output.put_line(i.prod_name ||' Prezzo: '|| 
                                    i.prod_LIST_price);
     else
       dbms_output.put_line(i.prod_name || ' Prodotto a meno di 50$');
      end if;
     END LOOP;
 end;
/

execute print_products

set serveroutput on size 100000

describe print_products

exec print_products ('A');

    create or replace function GetProductTaxIn
    (in_product_id number)
    return number
    is
        v_price number;
        cursor get_price_tax  is
        select nvl(round(prod_list_price * 1.15,2),0)
        from   products
        where  prod_id = in_product_id;
   begin
          open get_price_tax;
          fetch get_price_tax into v_price;
          return v_price;
   exception
       when others then v_price := 0;
       return v_price;
  end;
/

select prod_id, prod_list_price, GetProductTaxIn(Prod_id)
from products

select prod_id, prod_list_price, GetProductTaxIn(Prod_id)
  from products
 where GetProductTaxIn(Prod_id)>= 500

create or replace procedure call_print_prods
as
begin
  for l_alpha IN 65 .. 90
  LOOP
   print_products(chr(l_alpha));
  END LOOP;
end;
/

****************
Capitolo 7: Java
****************

$  javac  HelloWorld.java

$  java   HelloWorld

CONNECT / AS SYSDBA
SELECT count(*)  FROM dba_objects WHERE object_type = 'JAVA CLASS';

SELECT dbms_java.longname(object_name), status
FROM   dba_objects
WHERE  object_type = 'JAVA CLASS'
AND    status = 'INVALID';

SET linesize 140
COLUMN longname FORMAT A80
SELECT * FROM javasnm;


Oracle.connect("jdbc:oracle:thin:@hostname:portnumber:sid","username","password");
Oracle.connect("jdbc:oracle:oci:@hostname:portnumber:sid","username","password");

System.getProperty("oracle.jserver.version");

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;

Class.forName("oracle.jdbc.OracleDriver");
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Connection conn = DriverManager.getConnection(
    "jdbc:oracle:thin:@hostname:portnumber:sid",
    "username", "password");

Statement  s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT empno, ename FROM scott.emp");
int  empno = 0;
String name = null;

while (rs.next()) {
   empno = rs.getInt("empno");
   name   = rs.getString("ename");
   System.out.println("Il codice del dipendente č " + empno);
   System.out.println("Il nome del dipendente č   " +  name);
}

rs.close();
s.close();
conn.close();

String name;
int  empid = 7788;
…
  #sql {SELECT ename INTO :name FROM emp  WHERE empno=:empid};
  #sql {INSERT INTO emp (empno, ename, sal, deptno)
      VALUES (8888, 'TRUJILLO', 2400, 10);
  #sql {CREATE TABLE mytemp (id NUMBER, name VARCHAR2(20)) };
  #sql { UPDATE emp SET salary = salary * 1.10  WHERE id = :empid };
  #sql { DELETE FROM customer  WHERE id = :custid };
…

C:\> loadjava –user scott/tiger@orcl MyFirstProgram.class

C:\> dropjava –user scott/tiger@orcl MyFirstProgram.class

$  javac  HelloWorld.java

$ loadjava –r –user scott/tiger@orcl

$ ojvmjava –user scott –password tiger – database orcl

$  java   HelloWorld
MyFirstProgram  yea

public class MyFirstProgram


    public static  String returnString()
    {
         return  "Il mio primo programma  Evviva!";
    }
}

C:\> SET PATH=%PATH%;C:\oracle\ora10\jdk\bin
C:\> java –version
C:\>

C:\> javac MyFirstProgram.java

C:\> loadjava –user scott/tiger MyFirstProgram.class
C:\> dropjava –user scott/tiger MyFirstProgram.class

connect scott/tiger
CREATE OR REPLACE FUNCTION displaystring RETURN varchar2 AS
      LANGUAGE  JAVA NAME
      'MyFirstProgram.returnString () return java.lang.String';

VARIABLE myvalue VARCHAR2(50)
EXECUTE :myvalue := displaystring;
SET SERVEROUTPUT ON
PRINT myvalue

SELECT object_name, object_type, status
FROM user_objects
WHERE object_type = 'JAVA%' OR object_name = 'DISPLAYSTRING';

CREATE JAVA CLASS
USING BFILE (java_dir, 'MyFirstProgram.class');

CREATE JAVA SOURCE NAMED "MyFirstProgram" AS
public class MyFirstProgram
{
    public static  String returnString()
    {
         return  "Il mio primo programma  Evviva!";
    }
};
/

CREATE JAVA RESOURCE NAMED  "myData"
USING BFILE (java_dir, 'MyData.dat');
}

*****************************
Capitolo 8: Il linguaggio XML
*****************************

<h1>Questo č un titolo di primo livello</h1>
<b>A volte č necessario il grassetto</b>
<font color=green size=+2>Carattere grande e verde</font>

<piano_terra>Soggiorno . . .
. . . con caminetto . . .
. . .
</piano_terra>

<?xml version="1.0"?>
<book isbn=”0078822424” >
  <publisher>”OsborneMcGraw-Hill”</publisher>
  <title>Oracle Data Warehousing: The Practical Guide to Building a Data Warehouse</title>
  <author>Michael Abbey</author>
  <author>Michael Corey</author>
  <book_image href="0078822424.gif"/>
  <print>paperback</print>
  <instock>yes</instock>
  <price>55.95</price>
</book>

select xmlelement( "Product",
       xmlattributes(p.prod_id,p.prod_name as "Name"),
       xmlforest(p.prod_category as "Category",
       p.prod_list_price as "List_price"))
  from products p
 where p.prod_category_id =  201;

<Product PROD_ID="16" name="Y Box">
 <category>Electronics</category>
 <list_price>299.99</list_price>
</Product>
<Product PROD_ID="20" name="Home Theatre Package with DVD-Audio/Video Play">
 <category>Electronics</category>
 <list_price>599.99</list_price>
</Product>

<Product PROD_ID="29" name="8.3 Minitower Speaker">
 <category>Electronics</category>
 <list_price>499.99</list_price>
</Product>
. . .
. . .

select xmlelement( "Prodcategory",
          xmlattributes(p.prod_category as "Category"),
          xmlagg(xmlelement("Product",
          xmlforest(p.prod_name as "Name"))))
  from products p
 group by p.prod_category

<ProdCategory category="Electronics">
 <product>
  <name>Y Box</name>
 </product>
 <product>
  <name>Home Theatre Package with DVD-Audio/Video Play</name>
 </product>
...
</ProdCategory>
<ProdCategory category="Hardware">
 <product>
   <name>Envoy 256MB - 40GB</name>
 </product>
 <product>
  <name>Envoy Ambassador</name>
</product>

select xmlelement( "Country",
       xmlattributes(c.country_region as "Region"),
       xmlagg(xmlelement("Country",
               xmlforest(c.country_name as "Name"))))
  from countries c
  where c.country_region_id = 52801
  group by c.country_region

SQL> create table purchase_order
  2  (
  3   po_no number(9),
  4   po_file xmltype
  5   )

<PurchaseOrder
   xmlns=" http://xteoma.com/xsd/"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:SchemaLocation="http://xteoma.com/xsd/purchaseorder.xsd">
....
</PurchaseOrder>

begin
  dbms_xmlschema.registerSchema (
  'http://xteoma.com/xsd/purchaseorder.xsd',
  xdbURIType('/xsd/purchaseorder.xsd').getClob(),
  True,True,False,True);
end;
/

insert into PURCHASE_ORDER
  values (67, XMLTYPE('
  <PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:noNamespaceSchemaLocation="http://xteoma.com/xsd/purchaseorder.xsd">
    <Reference>MALLIA-20040109123337403PDT</Reference>
     <Requestor>Sean Mallia</Requestor>
     <User>SHAUNA</User>
     <ShippingInstructions>
        <name>Paddy Mallia</name>
            <address>249 Kracqueric Ave.
 Ottawa
 ON
 K1A 1A1
 Canada</address>
             <telephone>613 555-2620</telephone>
    </ShippingInstructions>
     <SpecialInstructions>Air Mail</SpecialInstructions>
     <LineItems>
             <LineItem ItemNumber="1">
                     <Description>XML Done Easy</Description>
              <Part Id="37429155721" UnitPrice="29.95" Quantity="3"/>
            </LineItem>
           <LineItem ItemNumber="2">
              <Description>YATFG - A detailed explanation</Description>
             <Part Id="37429158920" UnitPrice="39.95" Quantity="3"/>
           </LineItem>
     </LineItems>
 </PurchaseOrder>'))
/

create table xcustomer of xmltype;

insert into XCUSTOMER values(XMLTYPE('
  <customer>
   <name>Chris Smith</name>
    <address>116 Main Street
   Big City
  U.S.A.</address>
    <telephone>123 555-1234</telephone>
  </customer>'))
/

select * from xcustomer;

select any_path
  from resource_view
  where under_path(res,'/home/xteoma/xmldata/)=1
  and extractValue(res,'/Resource/ContentType')='text/xml';

select xdburitype('/home/xteoma/xmldata/Products.xml')
 from dual;

update purchase_order
      set po_file = updateXML (PurchaseOrder,
        '/ PurchaseOrder/Requestor/text()', ‘Speedo’)
where po_no = 67

update xcustomer c 
set value(c) = updateXML(value(c), '/customer/name/text()','Pat Jones')
/

select extractvalue(p.PO_FILE,'PurchaseOrder/ShippingInstructions/address')
from PURCHASE_ORDER p
where existsnode(p.PO_FILE,'/PurchaseOrder/User = "SHAUNA"') = 1

select extractvalue(value(c),'/customer/telephone')
  from xcustomer c
  where existsnode(value(c),'/customer/name = "Chris Smith"') = 1

update xcustomer c
set value(c) = updateXML(value(c), '/customer/telephone/text()','888 555-1234')
where existsnode(value(c),'/customer/name = "Chris Smith"') = 1
/

select extract(value(c), 'customer')
 from XCUSTOMER c
where existsnode(value(c),'/customer/name = " Chris Smith "') = 1
 /


SQL> create or replace view po_master_view
  2    (reference,requestor,userid,costcentre,
  3     shipto,shiptoaddress,instructions)
  4  as select extractvalue(value(x), '/PurchaseOrder/Reference'),
  5       extractvalue(value(x), '/PurchaseOrder/Requestor'),
  6       extractvalue(value(x), '/PurchaseOrder/User'),
  7       extractvalue(value(x), '/PurchaseOrder/ShippingInstructions/name'),
  8       extractvalue(value(x), '/PurchaseOrder/ShippingInstructions/address'),
  9          extractvalue(value(x), '/PurchaseOrder/SpecialInstructions')
 10  from PURCHASE_ORDER x
 11  /

select * from PO_MASTER_VIEW where userid = 'MALLIA';

************************************************************
Capitolo 9: Funzionalitŕ per i database di grandi dimensioni
************************************************************

select sum(amount_sold)
from sales
where time_id between to_date('2006-02-01', 'YYYY-MM-DD') and
to_date('2006-02-28', 'YYYY-MM-DD');

create table sales (
  prod_id        number        not null,
  cust_id        number        not null,
  time_id        date          not null,
  channel_id     number        not null,
  promo_id       number        not null,
  quantity_sold  number (10,2) not null,
  amount_sold    number (10,2) not null)
tablespace example pctfree 5 initrans 1 maxtrans 255
storage ( initial 65536 minextents 1 maxextents 2147483645);

create table sales (
  prod_id        number        not null,
  cust_id        number        not null,
  time_id        date          not null,
  channel_id     number        not null,
  promo_id       number        not null,
  quantity_sold  number (10,2) not null,
  amount_sold    number (10,2) not null)
  storage (initial 65536  minextents 1 maxextents 2147483645)
 partition by range (time_id)
 (partition sales_200501 values less than
                       (to_date('2005-02-01','YYYY-MM-DD'))
                       tablespace sales_ts_200501,
   partition sales_200502 values less than
                       (to_date('2005-03-01','YYYY-MM-DD'))
                       tablespace sales_ts_200502,
   partition sales_200503 values less than
                       (to_date('2005-04-01','YYYY-MM-DD'))
                       tablespace sales_ts_200503,
...
  partition sales_200711 values less than
                      (to_date('2007-12-01','YYYY-MM-DD'))
                      tablespace sales_ts_200711,
  partition sales_200712 values less than
                      (to_date('2008-01-01','YYYY-MM-DD'))
                      tablespace sales_ts_200712,
 partition sales_max values less than (maxvalue)
                      tablespace sales_ts_max);

create table ins_coverage (
  plan_id         number        not null,
  cust_id         number        not null,
  time_id         date          not null,
  dist_channel_id number        not null,
  cov_type_cd     varchar2(50)  not null,
  cov_amt         number (10,2) not null,
  prem_amt        number (10,2) not null)
  storage (initial 65536  minextents 1 maxextents 2147483645)
  partition by list (cov_type_cd)
  (partition cov_life values ('TERM 65', 'UL', 'ADB')
               tablespace cov_life_ts,
   partition cov_gib values ('COLA', 'GIB')
               tablespace cov_gib_ts,
   partition cov_dis values ('WP', 'DIS')
               tablespace cov_dis_ts,
   partition cov_inv values ('MF')
               tablespace cov_inv_ts
   partition cov_other values(default));

create table sub_activations (
   sub_id          number        not null,
   dist_channel_id number        not null,
   act_date        date          not null,
   deact_date      date          not null,
   sales_rep_id    number        not null)
   storage (initial 65536  minextents 1 maxextents 2147483645)
   partition by hash (sub_id)
   partitions 4
 store in (subact_ts1, subact_ts2, subact_ts3, subact_ts4);

create table phone_usage
 (sub_id              number,
  call_date           date,
  call_type_id        number,
  called_location     varchar2(50),
  service_carrier_id  number)
 storage (initial 65536  minextents 1 maxextents 2147483645)
 partition by range (call_date)
 subpartition by hash(sub_id)
 subpartition template(
 subpartition sub1 tablespace ph_usg_ts1,
 subpartition sub2 tablespace ph_usg_ts2,
 subpartition sub3 tablespace ph_usg_ts3,
 subpartition sub4 tablespace ph_usg_ts4)
 (partition phoneusg_200601 values less than
                      (to_date('2006-02-01','YYYY-MM-DD')),
  partition phoneusg_200602 values less than
                      (to_date('2006-03-01','YYYY-MM-DD')),
  partition phoneusg_200603 values less than
                      (to_date('2006-04-01','YYYY-MM-DD')),
  partition phoneusg_200604 values less than
                      (to_date('2006-05-01','YYYY-MM-DD')),
  partition phoneusg_200605 values less than
                      (to_date('2006-06-01','YYYY-MM-DD')),
  partition phoneusg_200606 values less than
                      (to_date('2006-07-01','YYYY-MM-DD')),
  partition phoneusg_max values less than (maxvalue));

create index sales_idx_l1 on sales (time_id)
 local
 (partition sales_idx_200501  tablespace sales_ts_idx_200501,
 partition sales_idx_200502  tablespace sales_ts_idx_200502,
 partition sales_idx_200503  tablespace sales_ts_idx_200503,
...
 partition sales_idx_200711  tablespace sales_ts_idx_200711,
 partition sales_idx_200712  tablespace sales_ts_idx_200712,
 partition sales_idx_max  tablespace sales_ts_idx_max);

alter table sales
 split partition sales_max at (to_date('2008-02-01','YYYY-MM-DD'))
 into (partition sales_200801 tablespace sales_ts_200801,
       partition sales_max tablespace sales_ts_max);

alter index sales_idx_l1
 rename partition sales_200801 to sales_idx_200801;

alter index sales_idx_l1
 rebuild partition sales_idx_200801 tablespace sales_ts_idx_200801;

alter index sales_idx_l1
 rebuild partition sales_idx_max tablespace sales_ts_idx_max;

create index phone_usg_idx_g1 on phone_usage (call_type_id)
  global
  partition by range (call_type_id)
  (partition ph_usg_idx_g1 values less than (2)
                    tablespace ph_usg_ts_idx_1,
  partition ph_usg_idx_g2 values less than (3)
                    tablespace ph_usg_ts_idx_2,
  partition ph_usg_idx_g3 values less than (4)
                    tablespace ph_usg_ts_idx_3,
  partition ph_usg_idx_g4 values less than (5)
                    tablespace ph_usg_ts_idx_4,
  partition ph_usg_idx_gmax values less than (maxvalue)
                    tablespace ph_usg_ts_idx_max);

create index phone_usg_idx_g1 on phone_usage (call_type_id)
 global
 partition by range (call_type_id)
 (partition ph_usg_idx_g1 values less than (2)
                    tablespace ph_usg_ts_idx_1,
  partition ph_usg_idx_g2 values less than (3)
                    tablespace ph_usg_ts_idx_2,
  partition ph_usg_idx_g3 values less than (4)
                    tablespace ph_usg_ts_idx_3,
  partition ph_usg_idx_g4 values less than (5)
                    tablespace ph_usg_ts_idx_4,
  partition ph_usg_idx_gmax values less than (maxvalue)
                    tablespace ph_usg_ts_idx_max);

select count(*)
from phone_usage
where call_id = 3;

create tablespace inv_ts_2007q1 
   datafile 'inv_ts_2007q1_1.dat' size 10m;

create tablespace inv_ts_2007q2 
   datafile 'inv_ts_2007q2_1.dat' size 10m;

create tablespace inv_idx_ts_2007q1
   datafile 'inv_idx_ts_2007q1_f1.dat' size 10m;

create tablespace inv_idx_ts_2007q2
   datafile 'inv_idx_ts_2007q2_f1.dat' size 10m;

create table invoice (
  invoice_id   number,
  customer_id  number,
  order_date   date,
  ship_date    date)
partition by range (order_date)
(partition INV_2007Q1 values less than 
                         (to_date(2007-04-01','YYYY-MM-DD'))
                       tablespace inv_ts_2007Q1,
 partition INV_2007Q2 values less than
                         (to_date('2007-07-01','YYYY-MM-DD'))
                        tablespace inv_ts_2007Q2,
 partition inv_max values less than (maxvalue)
                        tablespace inv_ts_max)
enable row movement;

create index inv_order_dt_idx on invoice(order_date)
local
(partition inv_idx_2007q1  tablespace inv_idx_ts_2007q1, 
 partition inv_idx_2007q2  tablespace inv_idx_ts_2007q2,
 partition inv_idx_max  tablespace inv_idx_ts_max);

create table commission (
  sales_rep_id     number,
  prod_id          number,
  comm_date        date,
  comm_amt         number(10,2))
 tablespace comm_ts pctfree 5 initrans 1 maxtrans 255
 compress;

alter table sales
move compress;

create index comm_sr_prod_idx 
  on commission (sales_rep_id, prod_id)
  compress 1;

alter session enable parallel dml;


create table commission (
  sales_rep_id     number,
  prod_id          number,
  comm_date        date,
  comm_amt         number(10,2))
 tablespace comm_ts pctfree 5 initrans 1 maxtrans 255
 parallel;

select /*+ parallel (commission, 4) */
   prod_id, sum(comm_amt), count(*)
from commission
group by prod_id;

select prod_id, to_char(comm_date, 'YYYY-MM'), count(*), sum(comm_amt)
from commission
group by prod_id, to_char(comm_date, 'YYYY-MM');

select prod_id, to_char(comm_date, 'YYYY-MM'), count(*), sum(comm_amt)
from comm_prod_mv
group by prod_id, to_char(comm_date, 'YYYY-MM');

create materialized view comm_prod_mv
    tablespace comm_prod_mv_ts
    storage (initial 50k next 50k)
    refresh complete  next sysdate + 7
    enable query rewrite
 as select sales_rep_id, prod_id, comm_date, count(*), sum(comm_amt)
    from commission
    group by sales_rep_id, prod_id, comm_date;

create tablespace ts1
   datafile +diskgrp1 /alias1;

select c.cust_gender gender,
       b.channel_class channel_class, 
       to_char(a.time_id, 'yyyy-mm') month,
       count(*) unit_count, 
       sum(a.amount_sold) amount_sold
from sales a, channels b, customers c
where a.channel_id = b.channel_id
and   a.cust_id = c.cust_id
and   to_char(a.time_id, 'yyyy-mm') between '2001-01' and '2001-02'
group by rollup(c.cust_gender,
                b.channel_class, 
                to_char(a.time_id, 'yyyy-mm'));

select c.cust_gender gender,
       b.channel_class channel_class,
       to_char(a.time_id, 'yyyy-mm') month,
       count(*) unit_count,
       sum(a.amount_sold) amount_sold
from sales a, channels b, customers c
where a.channel_id = b.channel_id
and   a.cust_id = c.cust_id
and   to_char(a.time_id, 'yyyy-mm') between '2001-01' and '2001-02'
group by cube(c.cust_gender,
                b.channel_class,
                to_char(a.time_id, 'yyyy-mm'));

select prod_id,
       sum(quantity_sold),
       rank () over (order by sum(quantity_sold) desc) as rank,
       dense_rank () over (order by sum(quantity_sold) desc) as dense_rank
from sales
where to_char(time_id, 'yyyy-mm') = '2001-06'
group by prod_id;

select * from 
  (select prod_id, 
         sum(quantity_sold),
         rank () over (order by sum(quantity_sold) desc) as rank,
         dense_rank () over (order by sum(quantity_sold) desc) as dense_rank
  from sales 
  where to_char(time_id, 'yyyy-mm') = '2001-06'
  group by prod_id)
where rank < 11;

select prod_id,
    sum(quantity_sold),
    cume_dist () over (order by sum(quantity_sold) asc) as cume_dist
from sales
where to_char(time_id, 'yyyy-mm') = '2001-06'
group by prod_id
order by sum(quantity_sold) desc;

select prod_id,
       sum(quantity_sold),
       rank () over (order by sum(quantity_sold) desc) as rank,
       percent_rank () 
               over (order by sum(quantity_sold) asc) as percent_rank
from sales 
where to_char(time_id, 'yyyy-mm') = '2001-06'
group by prod_id
order by sum(quantity_sold) desc;

select b.prod_subcategory, 
       sum(a.quantity_sold),
       ntile(4) over (ORDER BY SUM(a.quantity_sold) desc) as quartile
from sales a, products b
where a.prod_id = b.prod_id
and to_char(a.time_id, 'yyyy-mm') = '2001-06'
group by b.prod_subcategory;

select b.prod_subcategory, 
       sum(a.quantity_sold),
       ntile(4) over (ORDER BY SUM(a.quantity_sold) desc) as quartile,
       row_number () over (order by sum(quantity_sold) desc) as rownumber       
from sales a, products b
where a.prod_id = b.prod_id
and to_char(a.time_id, 'yyyy-mm') = '2001-06'
group by b.prod_subcategory;

select b.prod_category, 
       to_char(a.time_id, 'yyyy-mm'),
       sum(a.quantity_sold),
       sum(sum(a.quantity_sold)) over (partition by b.prod_category 
                                order by to_char(a.time_id, 'yyyy-mm')
                                rows unbounded preceding) as cume_sum
from sales a, products b
where a.prod_id = b.prod_id
and b.prod_category_id between 202 and 204
and to_char(a.time_id, 'yyyy-mm') between '2001-01' and '2001-06'
group by b.prod_category, to_char(a.time_id, 'yyyy-mm')
order by b.prod_category, to_char(a.time_id, 'yyyy-mm');

select c.channel_desc, p.prod_category, t.calendar_year year,
       sum(s.quantity_sold) quantity_sold
from sales s, products p, channels c, times t
where s.prod_id = p.prod_id
and   s.channel_id = c.channel_id
and   s.time_id = t.time_id
and   c.channel_desc = 'Direct Sales'
group by c.channel_desc, p.prod_category, t.calendar_year 
order by c.channel_desc, p.prod_category, t.calendar_year;

select channel_desc, prod_category, year, quantity_sold
 from
 (select c.channel_desc, p.prod_category, t.calendar_year year,
         sum(s.quantity_sold) quantity_sold
  from sales s, products p, channels c, times t
  where s.prod_id = p.prod_id
  and   s.channel_id = c.channel_id
  and   s.time_id = t.time_id
  group by c.channel_desc, p.prod_category, t.calendar_year) sales
 where channel_desc = 'Direct Sales'
 model
    partition by (channel_desc) 
    dimension by (prod_category, year)
    measures (quantity_sold)
    rules (quantity_sold['Hardware', 2002] 
               = quantity_sold['Hardware', 2001] * 1.10)
 order by channel_desc, prod_category, year;

create or replace view sales_trends
as
select p.prod_category, c.channel_desc, t.calendar_year year,
       sum(s.quantity_sold) quantity_sold,
       round((sum(s.quantity_sold) - 
              lag(sum(s.quantity_sold),1) 
                   over (partition by p.prod_category, c.channel_desc
                     order by t.calendar_year)) /
              lag(sum(s.quantity_sold),1) 
                   over (partition by p.prod_category, c.channel_desc 
                     order by t.calendar_year) * 
              100 ,2) as percent_chng
from sales s, products p, channels c, times t
where s.prod_id = p.prod_id
and   s.channel_id = c.channel_id
and   s.time_id = t.time_id
group by p.prod_category, c.channel_desc, t.calendar_year;

select prod_category, channel_desc, year, quantity_sold, percent_chng
from sales_trends
where prod_category = 'Electronics'
order by prod_category, channel_desc, year;

select prod_category, channel_desc, year, quantity_sold, percent_chng
from sales_trends
where prod_category = 'Electronics'         
model
   partition by (prod_category, channel_desc) 
   dimension by (year)
   measures (quantity_sold, percent_chng)
   rules (
     percent_chng[for year from 2002 to 2006 increment 1] = 
        round(avg(percent_chng)[year between currentv()-3 and 
                                           currentv()-1], 2),
     quantity_sold[for year from 2002 to 2006 increment 1] = 
        round(quantity_sold[currentv()-1] * 
          (1 + (round(avg(percent_chng)[year between currentv()-3 and 
                                           currentv()-1] ,2) / 100))))
order by prod_category, channel_desc, year;