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.

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 his 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 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 analyze 2 distinctive 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?

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: usually the tuning of the SQL statements should be done before the tuning of the db
- without using a hint inside the view2: usually it's not a good idea to put a hint inside a view

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, this block must show how many rows are affected by this DML.
How you can do it?

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?

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 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?

You have a running job and you want to stop it.
In order to do it, initially you put in BROKEN the job.
After, you search that job among the sessions but you don't find it.

You must do 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 have an Oracle db in the machine machine1.
You want to clone it in the machine2 that has exactly the same features of the machine1.
But in the machine2 the db has not the same Oracle version as in machine1 (e.g. the machine1 has Oracle10 and the machine2 has Oracle9 or vice versa).

How you can have the same data between the 2 dbs? And if you want to clone the db in Oracle10 to another db in Oracle10 and both the dbs are in machine1, how you can do it?

Actually in the first 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.




(*)
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