ManualiOracle.it
  
 
> Quizzes > Quizzes 2013 - 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 - Platform

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



Which query can you do in order to see the Operating System in your Oracle server?
For example, you must have a result like this:
  PLATFORM_NAME
  ----------------------------
  Microsoft Windows x86 64-bit
Solution

You should use the V$DATABASE view:
 SELECT platform_name 
 FROM v$database;



November - Pmon in Solaris

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



You work in some Unix-like machines.
In all of them, you have a db called DBLOR. The owner of the db files is called osdblor but you start the dbs with a user called ordblor.
If you want to see who has started the dbs, usually you correctly see this:
  lormachine > ps -ef | grep pmon
  ordblor   3095     1  0 Oct31 ?        00:30:57 ora_pmon_DBLOR
But in the Solaris machine, you see this "wrong" information:
  lormachine > ps -ef | grep pmon
  osdblor   3095     1  0 Oct31 ?        00:30:57 ora_pmon_DBLOR
If you check the listener process doing "ps -ef | grep tns", you don't have this problem.
Why this happens with the pmon process and not with the listener process?
Instead using "ps -ef", how can you see the correct information in Solaris?



October - NLS_INSTANCE_PARAMETERS

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



Your database is in Oracle 11.2 and you want to change the parameters contained in the view NLS_INSTANCE_PARAMETERS.
How can you do it?
Show all steps useful to do it.



September - AWR

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



These questions are about the AWR and its management through SQL*Plus :
- in which dictionary view can you see the frequence of the snapshots?
- how can you change this frequence? for example, if you want to have one snapshot every 30 minutes
- how can you do a "manual" snapshot?
- you want to launch the script awrrpt.sql about old information (10 days old). How can you do it?



August - Index Information

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



You have to write a query in order to find the indexes created during the last month.
About these indexes (partitioned and not), you have to find the following information:
 INDEXNAME               OBJECT_TYPE       TABLENAME            MB START_CREATION
 ----------------------- ----------------- ----------------- ----- --------------
 CRP_OWN.IDX_CA_IDX      INDEX             CRP_OWN.CA_IMPORT    25 20130826 11.36
 SYS.WRH$_SQLSTAT_INDEX  INDEX PARTITION   SYS.WRH$_SQLSTAT      0 20130826 23.20
 SYS.WRH$_WAITSTAT_PK    INDEX PARTITION   SYS.WRH$_WAITSTAT     0 20130826 23.20




July - Meta Script for RMAN

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



You have the RMAN recovery catalog database called REP1 in Oracle 10g.
In REP1, you have a lot of Oracle users and usually each user contain information about a backed up database.
You want to know which Oracle users contain recent information of the backed up databases.

E.g. when you launch that query, you have to receive something like:
 ENVIRONMENT   MAX_START_BCK_PIECE
 ------------- -------------------
 RMAN_DB1
 RMAN_DB2      2006-02-10 06.53
 RMAN_DB3      2006-12-06 14.14
 ...
 RMAN_DB8      2013-07-30 06.10
 RMAN_DB9      2013-07-30 07.00




June - Meta Script for ROLES

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



You have to find a query useful to give object privileges to the roles SELECT_ROLE and ALL_ROLE.
On all the tables of the LASSI schema, this script must give:
- grant SELECT to SELECT_ROLE
- grant ALL to ALL_ROLE

This query must be written to be executed from LASSI or from SYS:
E.g. when you launch that query connected as LASSI, you have to receive something like:
 GRANT_ON_OBJECT
 ---------------------------------------------------------
 grant SELECT on PESO_CLIENTI to SELECT_ROLE;
 grant ALL on PESO_CLIENTI to ALL_ROLE;

 grant SELECT on VENDITE to SELECT_ROLE;
 grant ALL on VENDITE to ALL_ROLE;

 grant SELECT on ATTIVAZIONI to SELECT_ROLE;
 grant ALL on ATTIVAZIONI to ALL_ROLE;
 ...
And when you launch the same query connected as SYS, you have to receive something like:
 GRANT_ON_OBJECT
 ---------------------------------------------------------------
 grant SELECT on LASSI.PESO_CLIENTI to SELECT_ROLE;
 grant ALL on LASSI.PESO_CLIENTI to ALL_ROLE;

 grant SELECT on LASSI.VENDITE to SELECT_ROLE;
 grant ALL on LASSI.VENDITE to ALL_ROLE;

 grant SELECT on LASSI.ATTIVAZIONI to SELECT_ROLE;
 grant ALL on LASSI.ATTIVAZIONI to ALL_ROLE;
 ...




May - Role problem

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



You have to find a query useful to discover when the roles have been created on the db.
E.g. when you launch that query, you have to receive something like:
 ROLE          CREATION_TIME
 ------------- -------------------
 CONNECT       18/09/2012 11:36:12
 RESOURCE      18/09/2012 11:36:12
 DBA           18/09/2012 11:36:12
 ...

 UPDATE_ECOS   23/04/2013 10:41:16
 SELECT_EFF    23/04/2013 11:44:44




April - EM cloud control

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



You have an EM cloud control 12.1.0.2.
In the interface, you see an error like this:
  EMGC_GCDomain/instance1/ohs1 Target Down 
How can you see if that target is really down?
If this alert is false, how can you avoid that EM shows it again?
Which metalink note show this problem?



March - Data Guard

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



You have a dataguard environment in Oracle 11.2.
You have to find a query that has a result like this:

In the primary db ORCL:

Instance: ORCL             Status: OPEN         Startup: 20120912 22.24

 NAME                       DATABASE_ROLE        OPEN_MODE               PROTECTION_MODE      
 -------------------------- -------------------- ----------------------- -------------------- 
 SWITCHOVER_STATUS  STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS     FS_FAILOVER_OBSERVER_HOST
 ------------------ -------------------------- ---------------------- -------------------------
 Database: ORCL             PRIMARY              READ WRITE              MAXIMUM PERFORMANCE  
 TO STANDBY                           10468375 TARGET UNDER LAG LIMIT HOST_075

 Instance: ORCL             Status: OPEN         Startup: 20120912 22.24
In the physical standby STDBY:

 NAME                       DATABASE_ROLE        OPEN_MODE               PROTECTION_MODE      
 -------------------------- -------------------- ----------------------- -------------------- 
 SWITCHOVER_STATUS  STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS     FS_FAILOVER_OBSERVER_HOST
 ------------------ -------------------------- ---------------------- -------------------------
 Database: ORCL             PHYSICAL STANDBY     MOUNTED                 MAXIMUM PERFORMANCE  
 SWITCHOVER PENDING                          0 TARGET UNDER LAG LIMIT HOST_075

 Instance: STDBY            Status: MOUNTED      Startup: 20120912 22.42
In order to have a result like this, you should use the UNION operator.




February - Hidden Parameters

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



You have to find a query in order to see some information about the Oracle hidden parameters that have the string 'shared_p' in their name.
The result must be something like this:
 NAME                            VALUE        DEFAULT   TYPE
 ------------------------------- ------------ --------- --------
 __shared_pool_size              1241513984   FALSE     6
 _dm_max_shared_pool_pct         1            TRUE      number
 _enable_shared_pool_durations   TRUE         TRUE      boolean
 _io_shared_pool_size            4194304      TRUE      6
 _shared_pool_max_size           0            TRUE      6
 _shared_pool_minsize_on         FALSE        TRUE      boolean
 _shared_pool_reserved_min_alloc 4400         TRUE      6
 _shared_pool_reserved_pct       5            TRUE      number
And, if you want to see all the hidden parameters?




January - ORA-600 error

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



Your db is in Oracle 11.2.0.3.
You have a SQL statement like this:
  MERGE INTO table_1
	USING ( SELECT  a.col_1, 
			b.col_2
		  FROM  tab_2 a,
			tab_3 b,
			tab_4 c
....
....
Until few days ago, the result was fine but now you receive:
 ERROR at line 2:
 ORA-00600: internal error code, arguments: [kkmupsViewDestFro_4], [0], [85883], [], []
Why now you have the error and before not?
Which metalink note explain this error?
What is a simple workaround to avoid it?




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