> Quizzes > Quizzes 2007 - 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 - Clone Db

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

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.
During this operation nobody can access to these dbs and you don't want to stop the db in the machine1.
Which is the fastest way to do it?

This method is usually faster (and better) than export-import: 

 1.	target machine: install Oracle (same version than the source db) with a database

 2.	source machine: hot backup of the whole db:
	alter tablespace TBS1 begin backup;
	copy (not “cut and paste”!) its datafiles in a different location. 
	Eg in sql*plus in Windows: 
	  host xcopy  R:\ORADATA\TBS1.DBF  X:\oracle_hotbackup  /Y;
	alter tablespace TBS1 end backup;
	etc… etc… for the others tablespaces (except the temporary tablespace)
	alter system switch logfile;

 3.	source machine: backup of the controlfile:
	alter database backup controlfile to trace;   (you can find it in the udump)

 4.	target machine: db shutdown and here you copy the datafiles and the controlfile
	in the same directories than in the source machine

 5.	startup the target db

November - Existing Files

Level (*) : 1
Topic (*) : Prog

We are in Oracle 9i on a Sun Solaris machine.
You have to do a PLSQL program that, when is launched, do this:

1. checks if exists the file /store2/errlog/pippo.txt
2. if it's existing, you have to write (by dbms_output) 'The file exists'
3. if it's not existing, you have to write (by dbms_output) 'The file doesn't exists'

You can check if the file exists by trying to open it:


  dir constant varchar2(50):= '/store2/errlog'; -- directory where you check
  name varchar2(30):= 'pippo.txt'; -- file_name 
  id_file utl_file.file_type; -- type 
  exists_file exception; -- for exception 
  pragma exception_init (exists_file, -29283); -- for exception 


  id_file:=utl_file.fopen(location => dir, filename => name, open_mode => 'r'); 
  dbms_output.put_line ('The file exists');
  utl_file.fclose (id_file);


  when exists_file then 
      dbms_output.put_line ('The file doesn''t exist');
      utl_file.fclose (id_file);  


October - Log Tables

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

In our database, as in many others, we write information about some important transactions in a log table.
This is our log table:

create table LOG_MSG
(nr_sequence number, procedure_name varchar2(70), message varchar2(200), dt_insert date);

alter table LOG_MSG
add constraint LOG_MSG_PK
primary key (nr_sequence);
In this table, our application write about 200.000 rows every day.
You want to keep this information for 1 month.
You must find a way to access to this information always fast (then do every night a delete of the old rows is not a good solution).

Your solution must be suitable for Oracle8i, 9i and 10g and must be suitable also if in your db is not enabled the PARTITIONING option.

September - Datafile Contents

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

You know that the tablespace EXAMPLE has many free space: it's big 100M and has 95M free space.
You use the query used in the quiz of August 2007 so you see that you can do the resize of his datafile until his size will be 80M.
Then you want to look at inside his datafile contents to discover if it's easy to save more space in it.

Then you have to do a query that show the tablespace contents like that (ordered by block_id desc):

Tablespace File_id Owner || Segment Partition_name Segment_type Block_id Kbytes
-------------- --------- ------------------ -------------- ------------------ --------- ---------
EXAMPLE 5 Free Space 10265 20288
EXAMPLE 5 Free Space 2157 64000

These are just the first rows of the result.
This result is useful because now you know that if you rebuild the indexes SH.FW_PSC_S_MV_IDX and LASSI.EXAMPLE_PK then you can resize the datafile until about 17M (of course, before that you rebuild these indexes you should check how much big they are).

In the query that you have to find, you have to use two substitution variables: one useful if you want to give the tablespace name, the other one if you want to give the datafile name (especially if the tablespace has more than one datafile).

August - Datafile resize and Dictionary Views

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

You are working in Oracle 9.2. In your machine there is just a little disk free space.
You know that the USERS tablespace has many unsed space then you decide to shrink his 2 data files.
You must find a SQL query that you can use in order to find how much big are these data files and how much big you can make them.

When you launch the SQL query, you must receive a result like this:

Tablespace File_name Size(KB) Highwater(KB) DDL Resize
--------- ---------------------- -------- ------------ ----------------------------
USERS D:\ORA92\ORCL\USERS1.DBF 80000 8792 alter database datafile 'D:\ORA92\ORCL\USERS1.DBF' resize 8792K;
USERS D:\ORA92\ORCL\USERS2.DBF 102400 96816 alter database datafile 'D:\ORA92\ORCL\USERS1.DBF' resize 96816K;

And you must find another SQL query for Oracle 8.0 and 8i.

July - Create table and Dictionary views

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

You have the table TAB_A and this table contains columns only with these datatypes: char, varchar2, date and number. These columns don't have any default value.

You must do a SQL query that you can launch in order to obtain the TAB_ORDERED such as TAB_A but with the column created in alphabetic order.
It's not important to take into account the tablespace_name, the constraints and the storage clause.

 SQL> desc tab_a
 Name    Type	 Nullable
 ------- ------------ --------
 FILE#   NUMBER       Y     
 FLAG    CHAR(1)
 SUM     NUMBER(10,2) Y
And when you launch your SQL query, you must obtain a result like this:
 ,SUM NUMBER(10,2)

 from dual 
   union all 
 select decode(rownum,1,null,',') || x.script 
 from ( 
  select a.column_name || ' ' || a.data_type || 
   decode(a.data_type,'VARCHAR2', '('||to_char(a.data_length)||')', 
  'NUMBER', decode(data_precision,null,null,'('||data_precision||','||a.data_scale||')'),
  'DATE', null, 
  'CHAR', '('||a.data_length||')') 
  || decode (a.nullable,'N', ' NOT NULL', NULL) script 
  from user_tab_columns a 
  where a.table_name = 'TAB_A' 
  order by column_name) x 
   union all 
 select ');' 
 from dual;

June (2) - SQL and System Views

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

You have many concurrencies in the tables and indexes of the schema MONITOR_DB.
Then you decide to modify their initrans like that:
- the table A will have initrans 8 and its indexes with initrans 9
- the table XXX will have initrans 6 and its indexes with initrans 7
- the other tables will have initrans 4 and their indexes with initrans 5

You must do ONE single SQL query that compose this.

 select 'alter table ' || owner || '.' || table_name || ' initrans ' ||
        decode(table_name, 'A',8, 'XXX', 6, 4) || ';'
 from dba_tables
 where owner = 'MONITOR_DB'
   union all
 select 'alter index ' || owner || '.' || index_name || ' initrans ' ||
        decode(table_name, 'A',9, 'XXX', 7, 5) || ';'
 from dba_indexes
 where owner = 'MONITOR_DB';

June - Import, Export and Estimated Time

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

You are working in Oracle 9.2 and you want to do a long export of a single schema.
You must do a SQL query useful in order to estimate when this export will be finished.

The SQL query will be launched during the export and it must to take into account how big are the tables and their lobs.
For instance, when you launch the query, you must receive a result like that:
 EXPORT (tables and lobs):
 Start: 200706191500
 Now  : 200706191558
 Owner: MPH
 You are doing the table TR3L (Rate of this table already done: 90%)
 3872 MB Done
 1655 MB Not Done Yet
 The export will finish on 20070619 1623
When you execute this query you have to write:
- the time when you have started the import. In the example above: 200706191500
- the schema name that you want to export. In the example above: MPH
- the table_name that is exported in the moment that you launch the query. In the example above: TR3L
- the rate of the current table that has been already exported (estimation). In the example above: I have estimated 90%. If you don't know it, write 50.

May - Backup and Estimated Time

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

You are working in Oracle 9.2. and you are doing an hot backup.
You do the hot backup (very day) launching a script that do the backup of every tablespace except the temp tablespace.
You want to do a SQL query useful to estimate when the backup that now is happening will finish.

For example, you want a result like this:

 Start Backup: 26/05/2007 10.21.19 Now: 26/05/2007 10.24.52
 124 M backupped today.
 989 M missing.
 Estimated finish time: 26/05/2007 10.53.11
Remember that you work in Oracle 9.2 than you can use the "WITH clause" in order to have a query more efficient.

April - Index Rebuild

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

You want to do a SQL query that creates the commands useful to do the rebuild of any index (not SYS or SYSTEM's indexes) not partitioned that have more than 100 extents.
In that rebuild you want to put the storage and the tablespace that the index had before the rebuild.

For example, if you launch the SQL the result is this:

alter index MONITOR_DB.XXX2_IDX rebuild storage (initial 786432) tablespace USERS;
alter index MONITOR_DB.XXX_IDX rebuild storage (initial 7340032) tablespace PROVA_STRIPE;
alter index MONITOR_DB.A_IDX rebuild storage (initial 23068672) tablespace USERS;


 select 'alter index ' || owner || '.' || segment_name || ' rebuild storage (initial ' ||
	 bytes || ') tablespace ' || tablespace_name || ';'
 from dba_segments a
 where segment_type = 'INDEX' and 
       owner not in ('SYS','SYSTEM')  and
       extents < 100;

March - Use Indexes

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

We are thinking to create an b-tree index in the ID_MOV column on the PIPPO table.
We have to calculate if that index could be selective, or not.

We don't want to do any statistics and we don't want to look at any dictionary views.
Then we decide to do a query and in that query we also want to calculate how many rows are in the PIPPO table.
Then you have to find the right query and you have to say one important init parameter that the optimizer use to decide if it is better to do a Full Table Scan or to use an index.

This is the right query:

	SELECT count(1), count(distinct ID_MOV ),
		100 / count(distinct ID_MOV
		) || ' %' pct_selettivo
	FROM pippo;

 - first column: the rows in the table
 - second column: the distinct value in the column ID_MOV
 - last column: the average percentage (of the total rows) selected with a single
   ID_MOV value

Then you also have be careful at the distribution of the value in the ID_MOV.

Here we suppose that the distribution is uniform.
However if the last column of the result is less than 2-5% it could be good to add an

But this decision also depends on:
 - how big is every table row: if the row is big, the Full Table Scan (FTS) is more
   probably heavy
 - the init parameter db_file_multiblock_read_count: if it is big, the FTS is more
   probably good in the small table: in the big table is very difficult that the FTS is

February - Administration

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

After one db creation, it's a good idea to create a "superuser" on the db.
This superuser is useful because you can use it in order to do normal administrative taskes (instead of using SYS or SYSTEM) and because in his schema you can put customized procedures or tables that are important to automatic checkes or taskes on the db.

Find the syntax in order to create that user, his grants and a dedicated tablespace for it.
Do it for Oracle 8.0, Oracle 8i, Oracle 9i and Oracle 10g.

Dedicated tablespace:

 oracle 8.0:
   create tablespace monitor
   datafile ‘D:\ORACLE\DATABASE\MONIT_01.ORA'     -- for example
   size 50M       
   default storage (initial 64k next 128k pctincrease 0);

 oracle 8i:
   create tablespace monitor
   datafile 'D:\ORACLE\DATABASE\MONIT_01.ORA'    -- for example
   size 50M        
   extent management local;

 oracle 9i/10g:
   create tablespace monitor
   datafile 'D:\ORACLE\DATABASE\MONIT_01.ORA'     -- for example
   size 50M        
   extent management local
   segment space management auto;

User script:

 oracle 8.0/8i/9i/10g:
   create user monitor_db
   identified by 
   default tablespace monitor
   temporary tablespace temp;

   grant dba to monitor_db;
   grant select any table to monitor_db;
   grant analyze any to monitor_db;
   grant execute on dbms_system to monitor_db;  -- this is in order to do the sql traces

 oracle 9i/10g:
   grant select any dictionary to monitor_db;

January (2) - Trace and TKPROF

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

In the schema SCOTT there is the procedure pr_pippo. This procedure do many operations and we would tune it in order to have it faster.
Then we decide to do a trace of that procedure and after use the tkprof.
How you can do this steps?


In order to do a trace of a procedure you must have the execute privilege on that
Then as SYS you have to do:

  grant execute on sys.dbms_system to scott;

In order to have also the CPU statistics and the elapsed_time you must have
the init parameter timed_statistics=true.

After this, you can do the trace of the procedure:

   v_sid integer;
   v_serial integer;


   select sid, serial#  
   into v_sid, v_serial
   from v$session
   where audsid = userenv ('sessionid'); 

   sys.dbms_system.set_sql_trace_in_session (v_sid, v_serial, true);

   sys.dbms_system.set_sql_trace_in_session (v_sid, v_serial, false);


So Oracle create the trace file (e.g., called ora00696.trc) on the user_dump_dest.
In the end, from the command line you launch the tkprof in order to understand easily
the trace file. 
Here an example:

  tkprof ora00696.trc sys=no sort=execu output=pr_pippo_execu.log

The result of this example is:
 - you don’t have the recursive SQL (sys=no)
 - ordered by the number of data blocks read from SGA to execute the statement
 - saved in the file pr_pippo_execu.log

January - DOS commands & DB Administration

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

We have an Oracle db on a Windows machine not in cluster.
We want to create a batch file that every month do these:

- rename the alert log and zip it (i.e. from alert_orcl10.log to
- compress the trace files contained in bdump to a unique zip
- compress the trace files contained in udump to a unique zip
- rename the listener.log and zip it (without stopping the listener service)

Warning: ALL these operations can happen while the users are working on the db.
This exercise is usefull also because you can see some IMPORTANT activities in the db maintainance (in Windows and in Unix)

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.