ManualiOracle.it
  
 
> Quizzes > Quizzes 2014 - 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 Objects

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



You have to find a query useful to find the objects created in the last 3 hours.
Result example:
 OWNER   OBJECT_NAME  SUBOBJECT_NAME  OBJECT_TYPE  CREATED
 ------- ------------ --------------- ------------ --------------
 LASSI   DIM_CIN      VIEW                         20141216 14:36
 LASSI   FACT_IDX     INDEX                        20141216 14:09
 LASSI2  FACT         INDEX                        20141216 14:08
Solution

You should use the DBA_OBJECTS view:
 SELECT owner, object_name, subobject_name, object_type, created 
 FROM  dba_objects 
 WHERE created>sysdate-3/24;




November - Invalid indexes

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



You have to find 3 queries that write the SQL commands to rebuild the invalid indexes contained in LASSI schema.
Query 1: it is about the not-partitioned indexes. Result example:
 COMMAND
 ------------------------------------
 alter index LASSI.DIMIC_PK rebuild;
 alter index LASSI.LOGMSG_IDX rebuild;
Query 2: it is about the partitioned indexes. Result example:
 COMMAND
 -------------------------------------------------------
 alter index LASSI.FSE_IDX rebuild partition P_20141029;
 alter index LASSI.FTR_IDX rebuild partition P_20141030;
Query 3: it is about the subpartitioned indexes. Result example:
 COMMAND
 -----------------------------------------------------------
 alter index LASSI.FS_IDX rebuild subpartition P_20141111_1;
 alter index LASSI.FT_IDX rebuild subpartition P_20141111_2;




October - Oracle dictionary

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



You have to find 2 queries that, using a TABLE_NAME, find out which indexes it has and in which columns they are.
For example: in the db, you have 2 tables called FMAP: one in the schema LA and one in the schema XXX. The result of the queries must be something like this:
 TABLENAME INDEXNAME   INDEX_TYPE  UNIQUENES  BLEVEL DistinctKeys LAST_ANAL PARTITIONED
 --------- ----------- ----------- --------- ------- ------------ --------- -----------
 LA.FMAP   LA.FMAP_IDX  NORMAL     NONUNIQUE       2     12589039 14-OTT-14  NO
 XXX.FMAP  XXX.FMAP_PK  NORMAL     UNIQUE          2     65640917 13-OTT-14  NO

 TABLENAME INDEXNAME    Column_name   C_Position
 --------- ------------ ------------- ----------
 LA.FMAP   LA.FMAP_IDX  AOD                   1
 LA.FMAP   LA.FMAP_IDX   LE                   2
 LA.FMAP   LA.FMAP_IDX    RIC                 3
 XXX.FMAP  XXX.FMAP_PK  AOD                   1
 XXX.FMAP  XXX.FMAP_PK   LE                   2
 XXX.FMAP  XXX.FMAP_PK    VAR_SCEN            3
 XXX.FMAP  XXX.FMAP_PK     NAME               4




September - Materialized views

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



You work in an Oracle 11.2 database and looking at an AWR report, you see that some of the "Top SQL Statements" are DELETE commands on materialized views.
After an investigation, you get that this happens during their refresh.
So how can you transform these DELETEs in TRUNCATE commands? And tell one MOS (metalink) note that explains it.




August - Crontab

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



You see an Oracle db in a Linux machine. In the crontab, you have this row:
 00 1 * * * /app/ora11/data/STAT/analyze.ksh > /app/ora11/data/STAT/stat.nohup 2>&1
So, when this script is launched?



July - Global Indexes

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



You have to find a query useful to find the global indexes (and their size) contained in the LASSI schema inside your database.




June - Partitioned Table

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



You are in Oracle 11.2 and you want to create a table internal-partitioned.
The table, called T1, is like this:
 AOD integer,
 cod varchar2(10),
 cvalue number
In the AOD column there are integer values instead of date values.
For example in this column, you have values such as 20131201, 20140101, 20140102, 20140201, etc.

You want the partitioning key is the column AOD and you want a partition every year from 2013.
So you'll have the value 20131201 in the p2013 partition, the values 20140101, 20140102 and 20140201 in p2014 partition, etc...
Write the T1 table (internal-partitioned by AOD column) creation script.



May - Sql_id and explain access plan

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



You are working in an Oracle RAC db.
You have the query identified by sql_id = 3s0kd398u2pxx.
Using the AWR tables, you have to find a query in order to know if in the last 2 days this sql_id has changed explain access plan.
For example, you want a result like this:
 SNAP_ID NODE BEGIN_INTERVAL END_INTERVAL   PlanHashVal EXECUTS  AvgBufGets Avg_DiskRds
 ------- ---- -------------- -------------- ----------- ------- ----------- -----------
 Sql_id       3s0kd398u2pxj
 845        2 20140522 12.00 20140522 12.26  3847028022       1     811,955           0
 851        2 20140522 16.04 20140522 17.00  2247069225       1     515,732      21,028
 852        1 20140522 17.00 20140522 18.00  2247069225       1     525,600           2
 852        2 20140522 17.00 20140522 18.00   617702193       1   1,257,942           0
 852        2 20140522 17.00 20140522 18.00  2247069225       1     523,476           0
 872        1 20140523 13.00 20140523 14.00   530248343       0  93,634,900     195,400




April - Instances in a RAC

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



You are working in an Oracle RAC with 2 instances.
You want to know some simple info about them.
So you have to find out the query in order to know this info:
 INSTANCE_NAME  HOST_NAME   STARTUP_TIME
 -------------- ----------- ------------------
 expo_i1        lorsvil61   17-APR-14 18:07:45 
 expo_i2        lorsvil62   30-MAR-14 19:53:48
Solution

You should use the global view GV$INSTANCE:
 SELECT instance_name, host_name, startup_time
 FROM gv$instance;



March - Partitioned Tables

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



In your db, you have many partitioned tables with RANGE partitions: one partition a day.
Example:
 TABLE_NAME     PARTITION_NAME
 -------------- ----------------
 U1.TAB_LOR     TAB_LOR_20131201
 U1.TAB_LOR     TAB_LOR_20131202
 ...
 U1.TAB_LOR     TAB_LOR_20140331
 U1.TAB_LOR     TAB_LOR_20140401
 ...
 U1.TAB_LOR     TAB_LOR_PMAX
 U2.TA_PIPPO    PART_20131201
 U2.TA_PIPPO    PART_20131202
 ...
 U2.TA_PIPPO    PART_20140331
 U2.TA_PIPPO    PART_PMAX
The rules about the partition names are:
- at the end of the name is the date (partition key)
- every table have a partition, with high_value MAXVALUE, named %PMAX.

You have to write a query to check if all the tables have all the partitions from this month until Aug-2013.
You must check it for all the partitioned (and/or subpartitioned) tables on U1 and U2 schemas.
For example, you result must be something like this:
 TABLENAME    201308    09    10    11    12   201401    02    03    04
 ---------- -------- ----- ----- ----- ----- -------- ----- ----- -----
 U1.TAB_LOR       31    30    31    30    31       31    28    31    19
 U1.TAB_TEST      31    30    31    30    31       31    28    31    30
 U2.TA_PIPPO       0     0    12    30    31       31    28    31     0
 U2.EXPO           0    30    31    30    31       31    28    31    30




February - O.S. files visible from db

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



From the database, you want to see which O.S. files are in a directory, their size and the time of the last update of them.
You have decided to do it using Java.
You want to write these information inside a table like this:
 FILENAME               SIZE    FILE_TIME
 ---------------------- ------- ------------------
 LASSI_VAR_ERR.csv	39	20-FEB-14 15:47:03
 MANUALIORACLE_26.log	8136	04-FEB-14 10:37:28
 LORIS_26932.log	20523	06-FEB-14 16:38:14
Explain all the steps you have to do in order to obtain it. And write the Oracle grants you need.




January - Space Allocation

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



Ten minutes ago you have created the empty table TAB1.
You check inside the dictionary views, so you find the TAB1 inside the DBA_TABLES and the DBA_OBJECTS.
But you don't find it inside the DBA_SEGMENTS.

Questions:
- which Oracle feature do it?
- from which Oracle version can it happen?
- which Oracle parameter you should modify in order to change this behaviour (as the older Oracle version)?
- if a single row is inserted into TAB1 and after you do a "truncate" of it, do you find TAB1 in the DBA_SEGMENTS?




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

   

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.