In the page Prices and Donations, you can find how to have the solutions of the quizzes.			Nella pagina Prezzi e Contatti potete vedere come avere le soluzioni dei quiz.

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.

You have an Oracle db in an AIX machine.
To manage that db, you use 2 different OS users:
- osdblor: installation, patches, etc...
- oradblor: day to day administration

This is the result of their "id" shell command:

 osdblor:
 uid=20470(osdblor) gid=55(oinstall) groups=54(dba)
 oradblor:
 uid=20471(oradblor) gid=54(dba)

You see that you can do sqlplus "/as sysdba" with the first OS user but when you try it with the second user, you receive

 ORA-01031: insufficient privileges

What is the cause of that problem? What you can do in order to use the sqlplus "/as sysdba" with the oradblor OS user?

You are on Oracle 9i and you want to check 2 "special" tablespaces: the TEMPORARY and the UNDO tablespaces.
For the TEMPORARY tablespaces, you must find a query in order to find how much space is used in it.
Example: in this db, you have 2 temporary tablespaces then you must receive a result like this :

 TABLESPACE_NAME  MB Used  MB Size  % Used
 ---------------- -------- -------- ------
 TEMP                   16      200      8
 TEMP_NEW             3405     3405    100

For the UNDO tablespace, you must find a query that show some indicators in order to understand if its size is right or not.
For example, you must receive a result like this:

 Undo Stats
 ------------------------------------------------
 Start Statistics: 20081017 12:17
 End Statistics: 20081024 12:13
 Minimum Size suggested for the Undo tbs: 672 MB
 Max Length Query: 6109 seconds
 How many ORA-01555: 0
 How many Out-of-Space in the Undo tbs: 0

In this example, the db has an undo-size of 3 GB.
Be careful to draw a conclusion: you could think that the undo tablespace is too big because the size suggested is 672 MB.
But remind that every indicator here is refered only to the period considered in the system views: in this case, only the last week: from 20081017 12:17 to 20081024 12:13.

You have the db "dblor" in 10.2.0.4 and it is on AIX.
In that machine you have the OS user "ordblor" and you try to connect in Sql*Plus with this OS user:

 $ sqlplus
 Error 6 initializing SQL*Plus
 Message file sp1.msb not found
 SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
 Exit 1

Then you have a look at its .profile:

 export ORACLE_HOME=/oracle/dblor/dbms102040
 export PATH=/usr/bin:/etc:/usr/sbin:$HOME/bin:/usr/bin/X11:/sbin:.:$ORACLE_HOME/bin
 export TNS_ADMIN=/oracle/dblor/admin/tnsadmin
 export ORACLE_SID=dblor

and you check its UID:

 $ id
 uid=18216(ordblor) gid=54(dba) groups=1(staff)

How you can use Sql*Plus with this OS user?

Connected as the Oracle Software owner (also while the db processes are active):

 cd $ORACLE_HOME/install

 ./changePerm.sh

It runs for about 10 minutes.
It decreases the db access security then before that you launch it, you should check in the Oracle
documentation.

You have this query:

 select ...
 from view1 a,
      view2 b
 where a.col1 = b.col2;

The view1 is on table10.
The view2 is a join query between table20 and table21.

When you launch this query, the Oracle Optimizer choose a bad explain plan:
it does a Full Table Scan on table21: 9 millions rows.

You want that the Optimizer uses the index table21_idx5 (7000 distinct values) on the table21.
How you can do it?

You must do it:
- without changing Oracle parameters
- without using a hint inside the view2: usually it's not a good idea to put a hint inside a view

In the query, you can put a hint that use the index table21_idx5:

 select /*+ index( b.table21 table21_idx5 ) */
	...
 from view1 a, 
      view2 b
 where a.col1 = b.col2;

In that hint then you have:
- the type of hint: index
- the alias "b" where the Optimizer can find the table21
- the index name to use: table21_idx5

You have the table EMP. It contains the employees of your company.
You want to do an anonymous block and in it you want to do this:

 UPDATE emp
 SET sal = sal*1.1
 WHERE sal < 1000;

Moreover, execution this block, you want to see how many rows are affected by this DML.
How you can do it?

In order to do the update, Oracle uses an implicit cursor, than you can use a cursor attribute:
sql%rowcount:

 declare 
  a pls_integer := 0;
 begin 
  UPDATE emp
  SET sal = sal*1.1
  WHERE sal < 1000;
  a:=sql%rowcount; 
  dbms_output.put_line(a); 
 end;

In the schema LORIS, you have this table:

 create table emp1
 ( cod number,
   name varchar2(100),
   salary number );

In it, you have some rows:

 insert into emp1 values (10,'loris',1000);
 insert into emp1 values (20,'mirko',900);
 insert into emp1 values (30,'emanuele',800);
 insert into emp1 values (40,'pippo',600);
 insert into emp1 values (50,'pluto',600);

In your db, you have the user SCOTT and you want both:
- SCOTT could do update of the employees on EMP1 with cod>30
- SCOTT could do update only of the field SALARY

How you can do this?

In order to reach these goals, you could use a view: 
Connected as LORIS:

 CREATE OR REPLACE VIEW view_emp1 AS
 SELECT *
 FROM emp1
 WHERE cod>30;

 GRANT UPDATE ( salary ) 
 ON view_emp1
 TO scott;

So, the user SCOTT can do something like this:

 update loris.view_emp1 
 set salary=0;

 2 rows updated

And it can’t do operations like these:

 update loris.emp1
 set salary=0;

 ORA-00942: table or view does not exist

Or

 update loris.view_emp1 
 set cod=0;

 ORA-01031: insufficient privileges

You can have this strange situation:

SQL> select* from v$version;

BANNER
-----------------------------------------------------------
Oracle9i Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE	9.2.0.1.0	Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> select trunc(avg(tempo_di_risp),2), trunc(starttime),
transactionname from cwf
where trunc(starttime) < trunc(sysdate)
group by trunc(starttime), transactionname;

It works a while and after that it shows same rows, you receive:

ORA-03113: end-of-file sul canale di comunicazione

After that, you can't access on the db and you must restart it!
After the db bounce, you discover:
- you can do "select* from cfw" without problems
- the table cfw doesn't have blocks corruption
- the describe of that table is:

Name		 Type	  Nullable Default
-------------------- ------------- -------- -------
ID		   NUMBER	Y
SOURCE_ID	    VARCHAR2(30)  Y
STARTTIME	    DATE	  Y
ELAPSEDTIME	  NUMBER	Y
TRANSACTIONNAME      VARCHAR2(255) Y
DISPONIBILITA	NUMBER	Y
TEMPO_DI_RISP	FLOAT	 Y

- than you try again the first query and you receive the same error after the same number of fetched rows.

What could be the problem?
And how you could avoid it?

You can have this strange situation:

SQL> select* from v$version; 

BANNER 
---------------------------------------------------------------- 
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE	9.2.0.7.0	Production

TNS for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

SQL> select*
from tab1;

UID	NAME
---- --------
10	Loris
11	Mirko

SQL> select uid
from tab1;

UID
----
78
78

Maybe an update is happened. Then you try again:

SQL> select*
from tab1;

UID	NAME
-------------
10	Loris
11	Mirko

Then it wasn't an update.
How is it possible that Oracle show different UID values according to the query?

You can have this strange situation:

SQL> select* from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create user loris identified by loris
default tablespace users temporary tablespace temp;

ORA-00600: internal error code, arguments: [kzdugn], [], [], [], [], [], [], []

The user monitor_db doesn't exists. And you receive the same error trying to create others users.
You try to restart the db but you still receive that error.
What you can do to create that user?

You have a table like this:

 create table x (first_name varchar2(50), last_name varchar2(50));

And you have 2 rows, such as below, inside that table:

 insert into x VALUES ('SCOTT','TIGER');
 insert into x VALUES ('HENRY','FLOWERS');

You must find a query that has this result:

 Scott Tiger's
 Henry Flowers'

Then in this query, these are your goals:
- the names and surnames must have the first letter in capital form and the others letters not in capital form
- you must use the possessive form: at the end of the surname you must put 's. But if at the end of the surname there is an S, you must put only the '


You must use: - initcap: because of the capital letter - decode: to check the last letter

 SELECT initcap(trim(first_name)||' '||initcap(trim(last_name)))|| 
 decode( 
 substr(lower(trim(last_name)),-1,1) 
 , 's', '''', '''s') 
 FROM X;

You can have this strange situation:

 SQL> select* from v$version;

 BANNER 
 ---------------------------------------------------------------- 
 Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production 
 PL/SQL Release 9.2.0.4.0 - Production 
 CORE 9.2.0.3.0 Production 

 TNS for Solaris: Version 9.2.0.4.0 - Production 
 NLSRTL Version 9.2.0.4.0 - Production 

 SQL> desc b 
 Object b does not exist. 

 SQL> create table b (bb number); 

 Table created 
 
 SQL> create table b (cc char); 

 Table created
 
 SQL> desc b
 Name Type Nullable Default Comments 
 ---- ------ -------- ------- -------- 
 BB NUMBER Y

On the second creation you don't receive any errors!
What could be the reason? And what could be a simple workaround to bypass that problem?

Of course, this an Oracle bug.
Maybe the problem is that you don't have the unique index on sys.tab$.
In this case you have to create it (look at the details in the Oracle documentation).

If you already have that index, try an Oracle restart: it could be the solution!

In your db, there is a running job and you want to stop it.
In order to do it, initially you put the job in BROKEN state.
After that, you search that job among the sessions but it is not easy to find it.

You must write a query that shows the running jobs and creates the script to kill these jobs.

E.g:
When you launch the query, you must have a result like this:

Job	Schema_user	What	This_date	Script
--------	---------	------------------	-----------------	------------------------
156	LASSI	pr_archive;	18/02/2008 18.29.53	alter system kill session '20, 59149';
1976	SCOTT	pkg_test.pr_rebuild;	18/02/2008 17.30.13	alter system kill session '87, 41857';

You can use this query reading 3 views:

select jobs.job,
       jobs.schema_user,
       jobs.what,
       jobs_run.this_date,
       'alter system kill session ''' || sess.sid || ', ' || sess.serial# || ''';' script
from dba_jobs   jobs,
     dba_jobs_running  jobs_run,
     v$session  sess
where jobs.job = jobs_run.job  and
      jobs_run.sid = sess.sid;

You have a Oracle db in the machine machine1 and you want to clone it to the machine2.

There are 2 cases:
- in the machine2, the db has the same Oracle version as in machine1 (example Oracle 10g in both cases)
- in the machine2, the db has not the same Oracle version as in machine1 (e.g. the machine1 has Oracle9 and the machine2 has Oracle10).

How can you have the same data between the 2 dbs?

Actually in the last case, you shouldn't speak of "clone db" because you have a real "clone db" only if the 2 dbs has the same Oracle version.

First case:

If target and source dbs have same version, you can use the DUPLICATE DATABASE RMAN command.

Second case:

If the db versions are different, the easiest way is thru export and import:
 1. machine2: Use the same parameters such as the source db
 2. machine1: export the schemas that you want (no system or similar)
 3. machine2: import the data

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

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