> Quizzes > Quizzes 2015 - 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 8i is quite different than Oracle 12c!)
- it is not warranted to be error-free in all the Oracle environments

December - db parameters

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

In this moment in your db a session with SID = 1976 is running.
You think that this session is running with some Oracle initialization parameters value different than the default value.
How can you find out if this is true?
And, in that session, which are the Oracle parameters with value different than the default values?

November - Info Connection

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

Thru a simple query on DUAL, you have to find the name of the instance and if you are connected thru a user with DBA privileges.

Find out this query.

You could use the function SYS_CONTEXT :

 FROM dual;

October - Clean up a little part of the library cache

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

In a RAC, a query Q1 with sql_id=25ucwzczhs4la get always the same bad plan hash value.
You do some modification on the db and you want to be sure that the Oracle optimizer will do again the HARD PARSE of Q1.
Of course, you don't need to clean up (flush) the entire shared pool but you only want to purge the library cache part regarding that query.

How can you do it?
If the query has been launched only on the instance 1, can you do that purge being connected to the db thru the instance 2?

September - Execution Plan

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

A query Q1 has sql_id 0bffz5s6gflor and plan hash value 635035975.
This Q1 query is running in an Oracle RAC in this moment and you can't analyze it thru AWR/ADDM, you should need to do an AWR snapshot.
Instead of using the AWR snapshot, you have to find a query in order to keep the execution plan of this Q1.

E.g. the result of your query should be something like this:
--- -------------------------- ------ ----------- ---- ---- ----- ---- ------ ----- ------
  0 SELECT - Plan 635035975                                  5668
  1  PX COORDINATOR                                                                 SEL$1
  2   PX SEND QC (RANDOM)      :TQ107       20063  467       5668    3
  3    HASH GROUP BY                        20063  467   76  5668    3
  4     PX RECEIVE                          20063  467       5667    3
  5      PX SEND HASH          :TQ106       20063  467       5667    3
  6       BUFFER SORT                       20063  467       5668    3
  7        NESTED LOOPS                     20063  467       5667    3
  8         HASH JOIN                       20063  369       5667    3
 15      PARTITION LIST SINGLE               2857              12    1 3       3
 16       INDEX RANGE SCAN     FACT1         2857              12    1 KEY     KEY  SEL$1

August - Init parameters on RAC

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

You are working on a RAC db composed by 2 instances.
You want to check which Oracle initialization parameters are different between these instances. So you must write a query in order to get this information.

The query result must be something like this:
 NAME                  VALUE_I1                             VALUE_I2
 --------------------- -------------------------------- --------------------------------
 background_dump_dest  /opt/ora/diag/rdbms/db/db1/trace /opt/ora/diag/rdbms/db/db2/trace
 core_dump_dest        /opt/ora/diag/rdbms/db/db1/cdump /opt/ora/diag/rdbms/db/db2/cdump
 instance_name         db1                              db2
 instance_number       1                                2

Reading the GV$SYSTEM_PARAMETER views, you could use a query like this:

	i1.value value_i1,
	i2.value value_i2
 where i1.inst_id = (select min(inst_id) from GV$SYSTEM_PARAMETER)  and
       i2.inst_id = (select max(inst_id) from GV$SYSTEM_PARAMETER)  and
       i1.num = i2.num and
       i1.value != i2.value
 order by 1;

Usually in a RAC, two instance have at least 8-10 parameters with different values.

July - Tablespace size History

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

You are in Oracle 11.2 and you want to know some stats about the size of tablespace TB1.
You want these stats referred to the last 2 weeks and you want to see them aggregated by day.
For example you want a result like this:
 --------- ------- ---------- ------------- -----------
 09-JUL-15 TB1     3543          3700           3438
 10-JUL-15 TB1     3543          3700           3438
 11-JUL-15 TB1     3543          3700           3431
 12-JUL-15 TB1     3543          3700           3453
 13-JUL-15 TB1     3548          3860           3458
 14-JUL-15 TB1     3548          3860           3476
 15-JUL-15 TB1     3548          3860           3480
 16-JUL-15 TB1     3548          3860           3417
 17-JUL-15 TB1     3548          3860           3410
 18-JUL-15 TB1     3548          3860           3410
 19-JUL-15 TB1     3548          3860           3410
 20-JUL-15 TB1     3548          3860           3439
 21-JUL-15 TB1     3726          3860           3584
 22-JUL-15 TB1     3726          3860           3589
 23-JUL-15 TB1     3726          3860           3554
Find out the query in order to get these information.

You could use a query like this (in bold font what you should change according to your
tablespace and period to analyze) :

 select trunc(begin_interval_time) day,tsname, 
    round(max(tablespace_size*8192)/1024/1024/1024) size_gb, 
    round(max(tablespace_maxsize*8192)/1024/1024/1024) maxsize_gb,
    round(max(tablespace_usedsize*8192)/1024/1024/1024) usedsize_gb
 from (
         s.snap_id, s.dbid, s.instance_number, s.startup_time, s.begin_interval_time, 
         s.end_interval_time, tablespace_size, tablespace_maxsize, tablespace_usedsize,
         tsname, contents, status, segment_space_management, extent_management, is_backup
    from  dba_hist_tbspc_space_usage u,
          dba_hist_snapshot s ,
          dba_hist_tablespace_stat t
    where u.snap_id=s.snap_id and u.dbid=s.dbid and u.snap_id=t.snap_id and t.dbid=s.dbid
      and u.tablespace_id=t.ts# and s.instance_number=t.instance_number
      and tsname = 'TB1'
      and begin_interval_time > sysdate-14 
    order by s.snap_id desc
 group by trunc(begin_interval_time), tsname 
 order by trunc(begin_interval_time), tsname desc;

June - SQL*Plus Settings

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

In your pc, you have an Oracle client installed and in your desktop you create 2 files called dblor.cmd and dblassi.cmd .
These files are useful to launch the SQL*Plus connections to the DBs called DBLOR and DBLASSI.

When you launch these files, in SQL*Plus you don't want a prompt like this:
  SQL > 
But you want always prompted the time and the db where you are connected. Example:
 18:03:23 dblor >
And you want other settings like:
 set lin 180 pages 120
 alter session set nls_language='AMERICAN';
In order to do automatically these settings, you have to create or modify another file.

Explain these solutions:
- in the case that you are "well granted" on the PC
- in the case where you don't have good privileges on the PC

May - Dates in SQL*Plus

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

You are connected on the db thru SQL*Plus, you want to know what's the time (and the date), so you launch :
 SQL> select sysdate from dual;

But you see only the date and not the time.

To solve easily this issue, you have 2 solutions:
1. modification of the query
2. modification of the session
So, you please describe better both solutions.

1. You could modify the query using the function TO_CHAR

    SQL> SELECT to_char(sysdate,'yyyymmdd hh24.mi') date_time 
         FROM dual;

    20150602 21.37

2. You could modify the NLS_DATE_FORMAT on the session where you are connected using the
   ALTER SESSION command:

    SQL> alter session set nls_date_format = 'yyyymmdd hh24.mi';
    Session modified.

    SQL> SELECT sysdate 
         FROM dual;

    20150602 21.37

April - Bind variables

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

You are in an Oracle 11g RAC and you have a query with bind variables running with SQL_ID 0kkhhb2w93cx1.
You want to know some information about its session and the values of its bind variables.

For example, you want to know something like :
 ------- ---- ----------- ------------- ---------------- ----- -------------- -----
       2  285  2170058777 0kkhhb2w93cx1 0000000692F75980 :1    23/04 14.00    1
       2  285  2170058777 0kkhhb2w93cx1 0000000692F75980 :2    23/04 14.00    2
       2  285  2170058777 0kkhhb2w93cx1 0000000692F75980 :3    23/04 14.00    44506
Find the query in order to get these info.

March - Subquery Behaviour

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

You create a simple table called A:
  create table a (aa char);
  insert into a values ('Z');
You try this query:

  SQL > select aa from dual;
  select aa from dual
  ERROR at line 1:
  ORA-00904: "AA": invalid identifier
Of course, you receive an error because the column AA is not in the table DUAL.
But if you put the above query in a subquery, you don't receive any error :
  SQL > select * from a where aa in (select aa from dual);

How is it possible? Why Oracle doesn't give you an error?
How you should write the query in order to receive an error (during the SQL parsing) when a column in the subquery doesn't exists?

February - Your environment

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

You are connected on the db through a not "DBA" user (SYS, SYSTEM or someone else "strong").
You have to find 2 queries to know:
- the instance_name
- the db global name

 Instance_name : you could use the sys_context function :

	select sys_context ('USERENV', 'INSTANCE_NAME') 
	from dual;

 Database Global name : you could use the view global_name :

	select *
	from global_name;

January - EXCHANGE error

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

You want to exchange the table T2 with the subpartition SP_20141218_MI (inside the table T1) but you get an error:
 Report errors:
 Error SQL: ORA-14278: column type or size mismatch in EXCHANGE SUBPARTITION
 14278. 00000 -  "column type or size mismatch in EXCHANGE SUBPARTITION"
 *Cause:    The corresponding columns in the tables specified in the
            ALTER TABLE EXCHANGE SUBPARTITION are of different type or size
 *Action:   Ensure that the two tables have the same number of columns
            with the same type and size.
So you check all the columns in both tables but you don't find any mismatch.
There is no difference also between their indexes.
Then what could be the reason? And what you can do to avoid this error?

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.