SQL> desc tab_a
 Name    Type	 Nullable
 ------- ------------ --------
 FILE#   NUMBER       Y     
 STATUS  VARCHAR2(18) Y
 CHANGE# NUMBER       Y
 TIME    DATE	 Y
 FLAG    CHAR(1)
 SUM     NUMBER(10,2) Y

 CREATE TABLE TAB_ORDERED (
 CHANGE# NUMBER
 ,FILE# NUMBER
 ,FLAG CHAR(1) NOT NULL
 ,STATUS VARCHAR2(18)
 ,SUM NUMBER(10,2)
 ,TIME DATE
 );

 select 'CREATE TABLE TAB_ORDERED (' 
 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;

 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';

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;

Solution

 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.

Solution

This is the right query:

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

Explanation:
 - 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
index.

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
   good

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.

Solution

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?

Solution

In order to do a trace of a procedure you must have the execute privilege on that
procedure:
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:

  declare
   v_sid integer;
   v_serial integer;

  begin

   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);

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

  end;

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
  (sort=execu)
 - 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 alert_20070119_01525.zip)
- 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)



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

© Copyright 2005-Today Loris ASSI
This website is not connected with Oracle Corporation or the official Oracle website. Some words used here are registered trade marks of their owners.