> Quizzes > Quizzes 2011 - 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.

If it is not specified, the commands must be executed connected as SYS.
About the solutions:
- could be more than one. Here is one solution that often takes into account of the performance tuning
- they could change because of the Oracle version (eg. Oracle 8.0 is quite different than Oracle 11g!)
- it is not warranted to be error-free in all the Oracle environments

December - Db Analysis

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

You have to write a PL/SQL program that do a short "db analysis".
When you launch it, you must receive a result like this:
   Analysis Date: 07/12/2011 16:33:42
Oracle Database 11g Enterprise Edition Release - 64bit Production
 Dbid: 324368697       Name: DBLOR
 Created: 18-AGO-11    Log_mode: ARCHIVELOG

 Instance Number: 1     Name: DBLOR
 Parallel: NO           Host name: mi088lor
 Archiver: STARTED      Startup Time: 05/10/2011 16:08
SGA Statistics:
 Buffer Cache Hit Ratio:   97.384 %
 Library Cache Miss Ratio:   0.912 %
 Dictionary Cache Miss Ratio:   0.984 %

November - Contention and "Metadata query"

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

One your colleague, says that the EMP table and its indexes have contention.
Of course this is an "application issue" but, as a DBA, you could reduce this problem (for the "future rows") by changing the INITRANS setting.
So you have to find a "metadata query" that:
- return the commands you could launch in order to apply this changing
- return the current INITRANS

Result Example:
  COMMAND                                 INI_TRANS
  -------------------------------------- ----------
  alter index SCOTT.EMP_IDX2 initrans 6; 	2
  alter index SCOTT.EMP_IDX1 initrans 6; 	2
  alter table SCOTT.EMP initrans 5;		1
You have to find it without knowing the indexes names.

October - Initialization Parameters

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

You are in Oracle 10.2. and you want to avoid errors like :
 ORA-00018: maximum number of sessions exceed
 ORA-00020: maximum number of processes (%s) exceeded
To avoid them, you want to set correctly the parameters "processes" and "sessions".
To do this analyze, you have to find a query that has result like this:
 -------------- ------------------- --------------- -----------
 processes                      130             133         150
 sessions                       135             159         170

You should use the view v$resource_limit : it displays information regarding some of the system resources:
    resource_name in ('sessions', 'processes');

September - DDL generation problem

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

You are in Oracle 10.2.
You have an existing DBMS_SCHEDULER job called FIND_JOB inside the schema SCOTT.
You are using SQL*Plus and you want to generate the DDL of this job.
How can you do it?

You can use the function dbms_metadata.get_ddl.
Its output is a CLOB so in order to see the result in SQL*Plus, you have to launch:
  set long 2000
After this, you can use this query (the type of this object is PROCOBJ):
  SELECT dbms_metadata.get_ddl('PROCOBJ', 'FIND_JOB', 'SCOTT') 
  FROM dual;

August - Export Problem

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

You are in Oracle
You use the "original" export (no datapump) in order to export the schema called LASSI and you receive this error:
  EXP-11 Table Does Not Exist or Table is Ignored
Which query can you launch before the export in order to see if you could receive this error?
How can you avoid it?
Which metalink note is useful to read?

July - Invalid Views

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

You have to find a query that shows the invalid views in your db.
This query must be executed in SQL*Plus and you want a result like this:
 ----------------------- ---------------------------------------
 LASSI.VW_S_ORDER_ITEM   select row_id,
                         from pippo.S_ORDER_ITEM_X

                         T1.RULE_ID     RULE_ID,
                         T1.NODE_ID     NODE_ID,
                         T1.GROUP_CD    GROUP_CD,
                         T2.NAME        RULE_NAME
                         FROM S_EXTDATA_N T1, S_EXTDATA_R T2
                         WHERE T1.RULE_ID = T2.ROW_ID

You have to set up the SQL*Plus environment. Example:
  set lines 155 pages 66
  col invalid_object for a33
  set long 2000
And, after this, you can launch the query:
  select o.owner || '.' || o.object_name invalid_object, v.text
  from dba_objects o,
       dba_views v
  where status != 'VALID'
        AND object_type = 'VIEW'
        AND o.owner = v.owner
        AND o.object_name = v.view_name;

June - Table Access

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

In the database, you have many "physical reads" on a small table called TAB_1.
Then you can try to improve the queries that read it and you can do others 2 things in order to improve the access to it.
Find these 2 other solutions.

May - SQL and Files

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

You have to find a query that shows the datafiles and tempfiles created in the last X days, where X is a dynamic variable defined like this: &days.
For example, I want to know the files created in the last 90 days then my result is:
 TYPE   TABLESPACE_NAME    FILE_NAME                                   MB   CREATION
 ------ ------------------ ------------------------------------- -------- ----------
 DATAF  TS_TSOLC_TAB       /oracle/dblor/data0/TS_TAB_d02.dbf       10240  22-FEB-11
 DATAF  TS_TSOLC_TAB       /oracle/dblor/data0/TS_TAB_d03.dbf       10240  22-APR-11
 DATAF  USERS              /oracle/dblor/data0/users02.dbf          16384  14-FEB-11
 TEMPF. TS_ACN_UNB_TEMP    /oracle/dblor/temp0/TS_ACN_TEMP.dbf       5120  14-FEB-11

April - Archiving

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

You have to find a query that shows the amount of redo logs generated every 10 minutes in the last 2 hours.
Then you want a result like this:
  DATE                   N.Archive     Tot.MB
  ------------------- ------------ ----------
  From 2011-01-25 17.20          3        708
  From 2011-01-25 17.30          3        702
  From 2011-01-25 17.40          1        234
  From 2011-01-25 17.50          2        469
  From 2011-01-25 18.00          1        234
  From 2011-01-25 18.10          2        471
  From 2011-01-25 18.20          2        469
  From 2011-01-25 18.30          2        468
  From 2011-01-25 18.40          1        234
  From 2011-01-25 18.50          2        468
  From 2011-01-25 19.00          2        480
  From 2011-01-25 19.10          1        144
  From 2011-01-25 19.20          1          0

You can see the view v$archived_log:
 select 'From '|| substr(to_char(al.completion_time,'YYYY-MM-DD hh24.mi'),1,15) || 0 date,
      count(al.recid) "N.Archive",
      round(sum ((al.blocks+1)*al.block_size)/1048576) "Tot.MB" 
 from v$archived_log al 
 where al.completion_time >=sysdate-1/12
 group by substr(to_char(al.completion_time,'YYYY-MM-DD hh24.mi'),1,15) 
 order by 1;

March - Auditing

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

You have the auditing activated in Oracle9i.
You upgrade the db to the Oracle version.
After that, you see that Oracle writes in the sys.aud$ but the result of one query is strange:
 SELECT count(1)
 FROM sys.aud$

You used this query in Oracle9i and now you expected to receive a result different than 0.
What is the reason?
Which metalink notes can you refer to?

In the Oracle db version 10gR1 and above, the TIMESTAMP# column is obsoleted in favour of the new NTIMESTAMP# column.

Since datatype of TIMESTAMP# (date) is coarse and can not capture fractional seconds and timezone, NTIMESTAMP# column is introduced of datatype timestamp(6).
The old column is deprecated.

Metalink ID 427296.1

February - Alert.log

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

Your Oracle server is crashed.
After it works again, you check the db.
It seems to work fine but some people say that they can't connect to the db and in the alert.log you found this:
 Tue Jan 11 12:10:28 2011
 Errors in file /oracle/db_lor/admin/bdump/db_lor_qmn0_5525654.trc:
 ORA-00600: internal error code, arguments: [4194], [73], [77], [], [], [], [], []
 Doing block recovery for fno: 2 blk: 43
   block recovery range from rba 17687.5432.0 to scn 1972.2718780554 
Which metalink notes can you refer to?
How can you fix the problem?

January (2) - Listener Problem

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

You want to see the status of a listener and you receive this error:

  ordblor # lsnrctl status LS_DBLOR_1521

  LSNRCTL for IBM/AIX RISC System/6000: Version - Production on 9-JAN-11 11:17

  Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mi011)(PORT=1521)))
  TNS-01169: The listener has not recognized the password

Why have you received this error?
How can you avoid it?

You have received this error because you have implemented the password in the listener.
The password in the listener was useful especially in Oracle 9i for security reasons.

To avoid the error here described, you have to look at inside the file listener.ora .
At the end of this file, you find something this:
  PASSWORDS_ls_dblor_1521 = B5C1F55A82F9E241
Then you have to launch these commands:
  1. lsnrctl
  2. set current_listener LS_DBLOR_1521
  3. set password B5C1F55A82F9E241
  4. status

January - Oracle 11g Installation

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

Using OUI, you are installing Oracle server on AIX machines (AIX 5.3 and AIX 6.1).
At Step 10 ("Perform Prerequisite Checks"), you receive errors like these:
 1. free space: server88:/tmp : Expected Value 1GB,  Actual Value 100MB
 2. Checking for rsct.basic.rte(0.0); Not found
 3. Checking for rsct.compat.clients.rte(0.0); Not found
 4. APARs IZ42940, IZ49516, IZ52331 are missing
 5. APARs IZ41855, IZ51456, IZ52319 are missing
Can you ignore them? In which metalink notes can you find these problems?

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 connected with Oracle Corporation or the official Oracle website.
Some words used here are registered trade marks of their owners.