> Quizzes > Quizzes 2010 - Oracle Best Pratices

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.

December - Statspack

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

You want to delete all the data inside the tables in the STATSPACK schema that are older than 1 year.
What is a sample way to do it?

The stats$snapshot is the main table in the STATSPACK.
This table contains the snapshot ID for all of the subordinate tables and the SNAP_TIME indicates when the snapshot was taken.

The stats$snapshot table can be deleted in order to delete rows from all of the subordinate tables, so in this case you can launch:
 Delete stats$snapshot 
 where SNAP_TIME>sysdate-365;

November - Secret Code

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

You are in Oracle 10g and you have a PL/SQL procedure called pr_trunc.
This procedure can be used but you want that nobody (neither the schema owner) can see its source.
How can you do it?

October - Index Use

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

You have the index LASSI.idx_1.

You want to discover whether it is used, or not:
- how can you enable this check?
- which view you have to examine if you are connected as LASSI? and if you are connected as SYS?
- how can you stop this check?

September (2) - Package Compilation

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

You are trying to compile a package but you receive the error
  ORA-04021: timeout occurred while waiting to lock object 
You want to know who is using it. How can you do it?
You want a result like this:
  (103) - LASSI
  (211) - USER2
where 103 and 211 are the SIDs and LASSI and USER2 are the users that are using the package.

You have to create the procedure who_is_using in the SYS schema.
You find it in metalink.
Then you launch that procedure. Example where PKG_LASSI is the package that you want to compile:
 set serveroutput on

 exec who_is_using ('PKG_LASSI')
  (103) - LASSI
  (211) - USER2

September - Hidden Parameters

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

You have to write a query that find out your hidden parameters and some its characteristics.
Example of its result:
  NAME                                  VALUE        DEFLT     TYPE
  ------------------------------------- ------------ --------- ----------------
  _abort_recovery_on_join               FALSE        TRUE      boolean
  _active_standby_fast_reconfiguration  TRUE         TRUE      boolean
  _adaptive_direct_read                 TRUE         TRUE      boolean
  _adaptive_fetch_enabled               TRUE         TRUE      boolean
  _adjust_literal_replacement           FALSE        TRUE      boolean
  _affinity_on                          TRUE         TRUE      boolean
  _aiowait_timeouts                     100          TRUE      number
This is a good query :
 select a.ksppinm name,
	b.ksppstvl value,
	b.ksppstdf deflt,
	(a.ksppity, 1,
	'boolean', 2,
	'string', 3,
	'number', 4,
	'file', a.ksppity) type
	--,a.ksppdesc description
	sys.x$ksppi a,
	sys.x$ksppcv b
	a.indx = b.indx  AND
	a.ksppinm like '\_%' escape '\'
 order by 1;

August (2) - Move Files

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

You are in Unix and inside the directory /oracle/db_lor/dbms102/rdbms/audit
you have thousands of files having the name starting with "ora_1".

You want to move them inside the directory

You try it normally, but you receive one error:
  mv ora_1* /oracle/db_lor_audit_ora_1/
  bash: /bin/mv: Argument list too long
How can you do this?

August - SQL Query

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

You want to find the tablespaces that have the datafiles only in /oracled02.
Find out the query that do this.

Launch this:
 select distinct tablespace_name  
 from dba_data_files 
 where file_name  like '/oracled02%'
 select tablespace_name  
 from dba_data_files 
 where file_name not like  '/oracled02%';
As I wrote, after the “minus”, you don’t need the “distinct”.

July - Parameter Problem

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

You have created a single db instance in Oracle 10.2.
When you start the instance, you receive the error ORA-32004 :
 SQL> startup

 ORA-32004: obsolete and/or deprecated parameter(s) specified
 ORACLE instance started.

 Total System Global Area  981467136 bytes
 Fixed Size                  2088800 bytes
 Variable Size             436207776 bytes
 Database Buffers          536870912 bytes
 Redo Buffers                6299648 bytes

 Database mounted.
 Database opened.
How can you find which is the "wrong" parameter ?

You find that the "wrong" parameter is max_commit_propagation_delay.
You are using a modified SPFILE copied from a RAC db.
How can you adjust that parameter?

You will find that parameter inside the alert.log :
  pga_aggregate_target     = 536870912
  _optimizer_connect_by_cost_based= FALSE
  _right_outer_hash_enable = FALSE
 Deprecated system parameters with specified values:
 End of deprecated system parameter listing
 Wed Apr  7 16:55:36 2010
 Oracle instance running with ODM: Veritas 5.0 ODM Library, Version 1.1
 PMON started with pid=2, OS id=1298548
 PSP0 started with pid=3, OS id=1290286
You can change that parameter using this command:
 alter system reset max_commit_propagation_delay
After that, you have to do the bounce of the database.

June - Disable Db links

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

You are in Oracle 10g and you want to disable one database link without dropping it.
Actually, in Oracle the Database links cannot be altered.
But there is a workaround.
Which is it?

A simple workaround of this problem is: put comments inside the entry in tnsnames.ora.
For example:
the db link uses this TNS entry:
db_lor =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)))
      (SERVICE_NAME = db_lor ))
It should became like this:
# db_lor =
#       (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)))
#       (SERVICE_NAME = db_lor ))
#   )
Of course, you can do this only if this connection is used only by that database link!

May - Number of Sessions

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

You want to do a stored procedure in order to check every 30 seconds how many sessions (active and inactive) are on your db:
When you launch this check, the procedure must do 10 snapshots and you must see graphically the result.
Example of the result:

 Time                Active Ses    Inact Ses        Active | Inactive
 20091119 16.33.01       13           229             **** | ***************
 20091119 16.33.31       14           227             **** | ***************
 20091119 16.34.01       16           225             **** | ***************
 20091119 16.34.31       16           226             **** | ***************
 20091119 16.35.01       13           229             **** | ***************
 20091119 16.35.31       13           229             **** | ***************
 20091119 16.36.01       16           223             **** | ***************
 20091119 16.36.31       22           210            ***** | **************
 20091119 16.37.03       22           220            ***** | ***************
 20091119 16.37.33       15           245             **** | ****************

You have to use the view v$session and the functions LPAD, RPAD and dbms_lock.sleep:
set serveroutput on size 1000000;


 v_sessions	varchar2(2000) := null; -- USEFUL FOR THE CURSOR RESULT
 v_snapshots	pls_integer := 10;	-- NUMBER OF SNAPSHOTS
 v_wait		pls_integer := 30;	-- NUMBER OF SECONDS BETWEEN THE SNAPSHOT


 dbms_output.put_line (' Time                Active Ses    Inact Ses        Active | Inactive');

 for i in 1..v_snapshots loop

	select  to_char(sysdate,'yyyymmdd') || 
 		LPAD(sum(decode(status,'ACTIVE',count(1))),9,' ')  ||
 	 	LPAD(sum(decode(status,'INACTIVE',count(1))),14,' ') || '  ' ||
		LPAD(LPAD('*',round(sqrt(sum(decode(status,'ACTIVE',count(1))))) ,'*'),15,' ')
		|| ' | ' || 
	into v_sessions
	from v$session
	group by to_char(sysdate,'yyyymmdd'),

	dbms_output.put_line (v_sessions);

	if i != v_snapshots then 
		dbms_lock.sleep(seconds => v_wait);
	end if;

 end loop;


April - Tablespace Problem

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

Your db is in Oracle in a Sun Microsystems machine.
The datafiles are in the File System (FS) /oracle/dblor/data.
The tempfile is in the FS /oracle/dblor/temp.

The FS /oracle/dblor/temp is totally full then you ask the UNIX group to extend it.
But they say that in order to do this the FS can't be totally full.

In it there is only one file and it's the file of the temporary tablespace TEMP.
Its size is 300 MB and it is in AUTOEXTEND till 400 MB.

Which is the 2 ways to check if the TEMP is offline?
How you can bring it again online?

After that you bring it online, you could make that file a little bit smaller (295M) but you want to be sure that, till the FS will not be larger, new sessions will not use it.
What are the last steps in order to solve your problem?
And why could be useful the view database_properties?

March - Tablespaces Changed

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

You want to know the names of the tablespaces where at least one datafile has been added today.
You don't have access to the file system then you have to find them using a SQL query.
Which is this query?

You could launch this join:
 select  distinct
 from  v$datafile dbf,
       v$tablespace tbs
 where trunc(dbf.CREATION_TIME)=trunc(sysdate) and
       dbf.ts# = tbs.ts#;

February - DB Link

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

In the machine SERVER_1, you have the database DB1 and in the TNSNAMES.ORA you have this entry: =
     (SERVICE_NAME = DB2000)
Then you create a database link:
  USING '';
From the SERVER_1, you try this database link and it works:
  select * from dual@DB2000;

Then, you try the same query from a client connected to the DB1 and you receive this error:
  "ORA-12154: TNS:could not resolve the connect identifier specified"
How you could create the database link in order to avoid this error?

In the database link creation, you could use the TNSNAMES.ORA entry:
     (SERVICE_NAME = DB2000)

January - SQL tuning

Level (*) : 2
Topic (*) : Prog

You want to do a SQL tuning of this:
  FROM tab_1 , tab_2
  WHERE tab_1.IDDOC = tab_2.IDDOC 
	AND tab_2.DIR = :b1;
Then you decide to use an Oracle event:
Which undocumented Oracle event is useful in this situation?
And which level you have to use in this event if you want to have also the wait event (as the example below)?
 Elapsed times include waiting on following events:
   Event waited on                 Times   Max. Wait  Total Waited
   -----------------------------  Waited  ----------  ------------
   SQL*Net message to client           3        0.00          0.00
   SQL*Net message from client         3       16.10         16.14
   db file sequential read            32        0.00          0.02
And how you can stop the Oracle event?

You have to use the event 10046 and, after, use the TKPROF to interpret the trace file.
In order to activate this event :
  alter session set events '10046 trace name context forever, level num'; 
Where num is either 1,4,8 or 12, with:
- 1 being the same as a normal set sql_trace=true,
- 4 including values of bind variables,
- 8 including wait events,
- 12 including both bind variables and wait events.
Then in this example, you have to use the level 12:
  alter session set events '10046 trace name context forever, level 12'; 
In order to stop it:
  alter session set events '10046 trace name context off';

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.