> Old Quizzes > Quizzes 2005 - Oracle Best Pratices

In the page Prices and Donations, you can find how to have the solutions of the level 3 quizzes.

Nella pagina Prezzi e Contatti potete vedere come avere le soluzioni dei quiz level 3.

The quizzes often have several solutions. Here is one solution that often takes into account of the performance tuning.
If it is not specified, the commands must be executed connected as SYS.
Remember that the solution could change because of the Oracle version (eg. Oracle 8.0 is quite different than Oracle 11.2!).
This document is not warranted to be error-free.

December (2) - SQL and Features Oracle9i

Level (*) : 3
Topic (*) : Prog

We are in Oracle9i and we have a table like this:
create table telefoni
cod_cliente varchar2(8),
contatore number,
tel varchar2(12)

ALTER TABLE telefoni
ADD CONSTRAINT telefoni_pk
PRIMARY KEY (cod_cliente, contatore);
We insert some rows in this table:
insert into telefoni values ('AAAAA', 1, '01/11111');
insert into telefoni values ('AAAAA', 2, '01/22222');
insert into telefoni values ('AAAAA', 3, '01/33333');
insert into telefoni values ('BBBBB', 1, '01/44444');
insert into telefoni values ('CCCCC', 1, '01/55555');
insert into telefoni values ('CCCCC', 2, '01/66666');

We want a query that, given a cod_cliente, show in a row all his telephones (separated by commas).

For instance, if we want to know the telephones of the cod_cliente='AAAAA', then the query result must be this:

----------------- ----------------------
AAAAA 01/11111, 01/22222, 01/33333

For the cod_cliente BBBBB we want:

----------------- ----------------------
BBBBB 01/44444

December - SQL and Oracle9i Features

Level (*) : 3
Topic (*) : Prog

We are in Oracle8.0 ed we have a table like this:
create table a
( day varchar2(10), kg number
); insert into a values ('mon', 1000); insert into a values ('tue', 2000); insert into a values ('wed', 3000); insert into a values ('thu', 4000); insert into a values ('fri', 5000); insert into a values ('sat', 6000); insert into a values ('sun', 7000);
Then this table has always 7 rows.
We want a query that show the 7 rows in a unique row and in 7 columns, then:

Kg mon Kg tue Kg wed Kg thu Kg fri Kg sat Kg sun
------- ------- ------- ------- ------- ------- -------
1000 2000 3000 4000 5000 6000 7000

Now we are in Oracle9 (then you can use the scalar subqueries) and we have a clob column:
create table a
day varchar2(10),
descr clob

insert into a values ('mon', 'on monday ...');
insert into a values ('tue', 'on tuesday ...');
insert into a values ('wed', 'on wednesday ...');
insert into a values ('thu', 'on thursday ...');
insert into a values ('fri', 'on friday ...');
insert into a values ('sat', 'on saturday ...');
insert into a values ('sun', 'on sunday ...');
We want to have a result like this:

descr mon descr tue descr wed descr thu descr fri descr sat descr sun
---------- ---------- ---------- ---------- ---------- ---------- ----------
on monday ... on tuesday ... on wednesday ... on thursday ... on friday ... on saturday ... on sunday ...

In the solution of this second problem you don't have to use to_char. You could use the "to_char" just if you are sure that the column descr can be converted in char; then you could only if in the column there aren't string very long.

November - PL/SQL and Administration

Level (*) : 2
Topic (*) : DBA

We want to do a job that, every night at 21.15, kills all the Apache sessions connected to the database.
How you can do it?

Some difficulties of this exercise are:

- the job must start always at 21.15: if you don't pay attention, the job could start later (e.g., the first time it starts at 21.15, after one week it starts at 21.18, after 2 weeks it starts at 21.20, etc...)
- be sure that you kill also possible parallel processes started from the Apache sessions

We suppose that the Oracle user that do it has the right privileges (e.g., "alter system") and the Apache doesn't start automatically the process killed.

I suggest to create a table where you can trace what happen:

 create table kill_apache
 cod_session  varchar2(20),
 spid      number(10),
 osuser    varchar2(40),
 time	  date,
 result     varchar2(500)    -- here you put ‘ok’ or the error description

Now you can create a procedure like this:

create or replace procedure pr_kill_apache is

  v_err varchar2(500);
  cursor cur is 
    select se.sid || ', ' || se.serial#  sessione,
    from v$session se,
         v$process pr
    where se.paddr = pr.addr  and
          se.module = 'Apache.exe';   -- use "module" instead of "program" to be sure that you take
					-- also the parallel child processes

  -- INSERT A ROW IN kill_apache
  insert into kill_apache 
  values ( 'PROCEDURE LAUNCHED', null, null, 
           sysdate, null);

  for rec in cur loop
      execute immediate 'alter system kill session ''' || rec.sessione || '''';
      insert into kill_apache 
      values ( rec.sessione, rec.spid, rec.osuser,
               sysdate, 'OK');
      when others then
        v_err := sqlerrm;
        insert into kill_apache 
        values ( rec.sessione, rec.spid, rec.osuser,
               sysdate, v_err);

  end loop;


Create the job:

  job_number number := null;
  next_date => to_date('26-10-2005 21.15.00','dd-mm-yyyy'), -- next_date must be adjusted
  interval=>'trunc(sysdate)+1+21/24+15/(24*60)');	-- in this way it starts always at 21.15

October - SQL

Level (*) : 3
Topic (*) : Prog

We are in a typical datawarehouse. In the normalized part of the db, we have a fact table like this:
 ( date_ym varchar2(6), -- format ‘yyyymm’
 cod_product varchar2(5),
 cod_province number(3),
 kg_sold number(4),
 takings_euro number(6)

ALTER TABLE sales_ym
ADD CONSTRAINT sales_ym_pk PRIMARY KEY (date_ym, cod_product, cod_province);
This table has sales data of the company in last 2 years. It has about 50.000.000 rows (24 months for 20.000 distinct products for 100 provinces).

In the denormalized area of the db, often there are queries that compare the data of the corrent month and the same month of the previous month (e.g. the sale of august 2005 compared with the sale of august 2004).

In order to do QUICKLY this comparison, you must load this table:
( month varchar2(2), -- format ‘mm’
  cod_product varchar2(5),
  cod_province number(3),
  kg_sold_cy number, -- sold of the Current Year-month
  takings_euro_cy number,
  kg_sold_py number(4), -- sold of Previous Year-month
  takings_euro_py number(6)

ALTER TABLE sales_m 
ADD CONSTRAINT sales_m_pk PRIMARY KEY (month, cod_product, cod_province);
You have to create a SQL INSERT that insert data from sales_ym to sales_m.


We insert some rows in sales_ym:
insert into sales_ym values ('200507', 'A1111', 1, 111, 11000);
insert into sales_ym values ('200507', 'A2222', 1, 222, 22000);
insert into sales_ym values ('200407', 'A1111', 1, 333, 33000);
insert into sales_ym values ('200406', 'A1111', 1, 444, 44000);
insert into sales_ym values ('200406', 'A1111', 2, 555, 55000);
insert into sales_ym values ('200411', 'A1111', 2, 666, 66000);

After the INSERT, in the sales_m you must have data like this:

06 A1111 1 0 0 444 44000
06 A1111 2 0 0 555 55000
07 A1111 1 111 11000 333 33000
07 A2222 1 222 22000 0 0
11 A1111 2 0 0 666 66000

!!! Attention: to solve this problem, you must find a query that read JUST ONE TIME the table sales_ym.

September - SQL, SYSTEM VIEW and UNIX

Level (*) : 1
Topic (*) : DBA

We have the client-machine OMILA76 connected to the db.

On that machine there are many sessions and we want to kill the unique session that do a query on the table PIPPO.

Then, how we can find that session? and how we can kill it from SQL*Plus?

Sometimes, Oracle is not able to kill the session then, after 60 seconds, its result is the message "ORA-00031 session marked for kill".

In this case, you have to find the spid of the process, so you can kill it directly from the O.S. (we are in Unix).

Then, how you can find the spid? and how you can kill it from O.S?

You find the session that you want to kill through the following query (the v$session shows the 
sessions, the v$sql shows the sql that the sessions are executing):

 select distinct logon_time,  a.sid,  serial#,  a.action,  status,  
	b.sql_text,  machine,  program,  osuser,  username
 from v$session a, v$sql b
 where sql_hash_value = hash_value (+) –with the  outer-join, you can find also the no-log processes 
   and machine like 'OMILA76'
   and b.sql_text like '%PIPPO%';

Suppose that the result is SID = 50 and SERIAL# = 12345.

So, from SQL*Plus you do this command:
	alter system kill session ’50, 12345’;

If Oracle is not able to stop the session, you find the process on the server in this way:

 select p.spid 
 from v$process p, v$session s
 where s.paddr = p.addr  and  sid = 50;

Suppose that the result is 22005.
Then, you connect the user oracle in the Unix machine where you have the db, and kill the process:
	kill -9 22005


Level (*) : 3
Topic (*) : DBA/Prog

You must do a PL/SQL block that, given the name and the owner of a Primary Key (or Unique), do the scripts useful to create all the foreign keys that reference it.

- this PLSQL block must be suitable also for the Primary keys do on more than one column
- in the scripts you must write also if these foreign keys have the "on delete cascade" option or if they are "deferrable"


We have the table LASSI.WB_RT_AUD and on that table there is a Primary key called PK_RTBB.

You must do a PLSQL block that, through the dbms_output, create scripts like these:
add constraint FK_RTM_RTBB foreign key (RTBB_IID)
references LASSI.WB_RT_AUD (RTA_IID)

alter table LASSI.WA_RT_ERR
add constraint FK_RTE_RTBB foreign key (RTBB_IID)
references LASSI.WB_RT_AUD (RTA_IID)

alter table LASSI.WA_RT_ERR_SOU
add constraint FK_RTS_RTBB foreign key (RTBB_IID)
references LASSI.WB_RT_AUD (RTA_IID) on delete cascade

July - SQL

Level (*) : 3
Topic (*) : Prog

We are in Oracle 8i and we have the table A like this:
 (aa NUMBER, 
 aaa CHAR(1));
This table has these rows:
insert into a values (10,'a');
insert into a values (10,'b');
insert into a values (20,'c'); 
insert into a values (30,'e'); 
insert into a values (30,'d'); 
insert into a values (40,'f'); 
insert into a values (40,'g'); 
insert into a values (50,'i'); 
insert into a values (50,'h'); 
insert into a values (60,'z');
insert into a values (60,'y');
We know that for every distinct value in aa, we have maximum 2 rows.

Now we suppose the result of SELECT* FROM A;
is exactly this:

10 a
10 b
20 c
30 e
30 d
40 f
40 g
50 i
50 h
60 z
60 y

(I remind you that not always the rows are shown in the same order than you have used to insert them)

I want a query that group by aa giving this result on 2 columns:

10 a, b
20 c
30 e, d
40 f, g
50 h, i
60 y, z

That is, for every value of the column aa, group by the values of aaa in ascendent alphabetic order and separating them with a comma.

And I want another query that group like this:

10 a, b
20 c
30 e, d
40 f, g
50 i, h
60 z, y

That is, I want to have the second column in the same order than you read the table; that is with the first "i" before than "h" and the "z" before than "y".

Obviously, in the queries you can't write something about the real data in the table; that is it must be a solution always suitable.

(you can do them also without using the analytic functions).

1: easy solution
2: medium solution
3: hard (or long) solution

quiz usually suitable for DBAs
Progr: quiz usually suitable for PL/SQL or SQL Programmers
DBA/Progr: quiz suitable for DBAs and for Programmers


This website is not authorized by Oracle Corporation and it is not connected to the official Oracle website.
Some words used here are registered trade marks of their owners.