ManualiOracle.it
  
 
> Quizzes > Quizzes 2016 - 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 - Space Used

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


You have to find a query in order to discover the total space (in MBytes) used from all the indexes of the database.

Solution
You should read the DBA_SEGMENTS view and use the "WHERE condition" in order to check all the indexes (partitioned and not):
  SELECT round(sum(bytes)/1024/1024) mb
  FROM dba_segments
  WHERE segment_type like 'INDEX%';



November - Different result sets

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


You have the table T1: it is "list partitioned" by the DATE1 column and "list sub-partitioned" by the FT_NO column.
Look at this:
 SELECT distinct ft_no
 FROM t1
 WHERE date1 = 20160912 AND
       le = 'LOR';

 FT_NO
 -----
   306

 SELECT *
 FROM t1
 WHERE date1 = 20160912 AND
       ft_no = 306;

 No rows.
 
Then thru one query seems to exist the FT_NO 306 with DATE1 = 20160912, thru the other query no.
The sub-partitions on the table T1 are populate thru the EXCHANGE SUBPARTITION command and among the sub-partitions doesn't exists the value 306.

How is it possible this situation?
How can you fix it?



October - Find out the duplicated rows

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


Looking at the Oracle dictionary and given a table name (the table has no unique constraint and it is under the schema where you are connected), you have to find a query useful in order to find all the duplicated rows on that table.

Example:
This is the table containing duplicated rows:
 desc STG_TABLE

 Name      Type
 --------- ------------------
 C1        NUMBER
 C2        NUMBER
 C3        VARCHAR2(300)
Launching the query reading the dictionary, you must have a dynamic result like this:
 select C1, C2, C3, count(1)
 from STG_TABLE
 group by C1, C2, C3
 having count(1) = 1;
Then, launching it, you’ll find the duplicated rows.



September - Big Tables

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


You have to write a query in order to find the biggest tables (or partitions or subpartitions) under your schema.
The size of these objects must be more than 10GB and, in the result, you want to see also their compression information.
Result set example:
 SEGMENT PARTITION_NAME SEGMENT_TYPE    COMPRESS COMPRESS_FOR COMPRESS COMPRESS_FOR    MB
 ------- -------------- --------------- -------- ------------ -------- ------------ -----
 D_ST                   TABLE                                                       19200
 F_DATA  TP_20160609_W  TABLE SUBPARTIT                       DISABLED              14406
 F_DATA  TP_20160728_D  TABLE SUBPARTIT                       DISABLED              12209
 F_VAR   F_20150219     TABLE PARTITION ENABLED  QUERY LOW                          10500



August - Sub-Partitioned Table

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


You have a composite partitioned table called FACT_TABLE created like this:
  CREATE TABLE FACT_TABLE
   ( DAY NUMBER,
     DESC VARCHAR2(10),
	...
    )
  PARTITION BY LIST (DAY)
  SUBPARTITION BY LIST (DESC)
  SUBPARTITION TEMPLATE (
    SUBPARTITION S1 VALUES ( 'SOL1' ),
    SUBPARTITION S2 VALUES ( 'SOL2' ),
    SUBPARTITION S3 VALUES ( 'SOL3' ) )
 ( PARTITION TP_20160101  VALUES (20160101),
   PARTITION TP_20160102  VALUES (20160102),
  ...);
After the table has been populated, you want to add a subpartition called S4 containing the value 'SOL4'.
This subpartition must be added only in the partitions with DAY >= 20160801.
Which is a fast way to do it?
Using the Oracle dictionary, how can you check the current subpartition template?
And write a script to modify the subpartition template: in it, you want to include the S4 subpartition.



July - Sub-Partitioned Indexes

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


You have an index called FACT_IDX1. This index is a local index in the composite-partitioned table called FACT1.
Looking at 2 different data dictionary views, you have to find the partitioning key columns and the subpartitioning key columns of FACT_IDX1.
Write the 2 queries in order to find out these info.



June - Table DEGREE

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


You have to write a query in order to find, in the schemas starting with the letter "L", all the tables with degree of parallelism different than the value 1.

For instance, you want a result like this:
 DEGREE OWNER   TABLE_NAME
------- ------- ------------
      5 LORIS   FACT1
      4 LORIS   FACT12
      2 LAS     DIM4
Solution
Connected as SYS user or another “super user”, you could launch this query on the DBA_TABLES view:
  SELECT degree, owner, table_name
  FROM dba_tables a
  WHERE owner like 'L%' and 
        degree != 1;



May - Columns Size

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


Loading the tables (also big tables) on your db schema, you often get the error "ORA-12899: value too large for column ...".
In order to reduce the probability to have this error, you have to write a PL/SQL code that checks 20.000 rows in all the VARCHAR2 columns of your schema.
If they seem little, you have to enlarge them.

Example:
In your schema you have the TABLE_1 and TABLE_2 tables so launching that program you should get something like:
 TABLE_1
 ----------
 0,15

 TABLE_2
 ----------
 0,95
 
This means that in the TABLE_2 the column where it is easier to get the error ORA-12899 is a column where there is data long 95% compared to its definition.
Example: you have the column COL3 VARCHAR2(100) and in it there is data 95 chars long, so it is better to modify it in something like VARCHAR2(200).

Additional Step: As wrote, usually you should check every table using a condition like "WHERE rownum<20000", but if the table has a column called "DAY", in that table you must use a condition like "WHERE day>sysdate-180 and rownum<20000".



April - Table Columns

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


You have to write a query in order to find all the tables (and not views) that have a column called ERROR.
For instance, you want a result like this:
 OWNER   TABLE_NAME   COLUMN_NAME  DATA_TYPE  DATA_LENGTH
 ------- ------------ ------------ ---------- -----------
 LASSI   ST_ERROR     ERROR        VARCHAR2   200
 LORIS2  DIM_ERRORS   ERROR        VARCHAR2   600
 LORIS2  FACT_ERRORS  ERROR        VARCHAR2   2000



March - Interval Partition

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


You have the table LASSI.T1 and you want to know if it's "INTERVAL partitioned" or not.
Which query can you do in order to discover this information?



February - Segments drop problem

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


You have a tablespace where there are a lot of partitioned tables without statistics.
You try to drop these tables but often the DROP operation is very slow (about 5 minutes each partition) and their sessions have the event “optimizer stats update retry".

You check the number of extents of each segment and it is not very high.

Which are 2 workarounds of this issue?
And which Oracle MOS note talks about it?



January - SQL Plan execution

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


Just now you have launched the query with SQL_ID = '57hfsgdfs876la'.
You want to see its SQL execution plan thru this query:
 SELECT *
 FROM TABLE
  (dbms_xplan.display_awr('57hfsgdfs876la', null, null, 'ALL');
But you don't receive any result.
What you could do in order to have a result?
And what you could do in a RAC? And in an Exadata?



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