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


The quizzes often have several solutions. Here is one solution that often takes into account of the performance tuning.
If it is not specified, the commands must be executed connected as SYS.
Remember that the solution could change because of the Oracle version (eg. Oracle 8.0 is quite different than Oracle 11.2!).
This document is not warranted to be error-free.


December - Segments

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


On the schema LORIS, you have create these 4 segments:
	create table t1 (a number) parallel 3;
	create table t2 (b number);
	create index ix1 on t1(a) parallel 2;
	create index ix2 on t2(b);
You want to find out a query to get the segments list ordered by degree. Then you want to have a result like this:
	TYPE  OWNER    OBJECT_NAME      DEGREE
	----- -------- ------------- ---------
	TABLE LORIS    T1                    3
	INDEX LORIS    IX1                   2
	INDEX LORIS    IX2                   1
	TABLE LORIS    T2                    1
Pay attention to the order! Try my example!

Solution

Connected as SYS, you should use the views DBA_INDEXES and DBA_TABLES.
On them, the DEGREE column is not a NUMBER.
So if you can't do this :
	select*
	from (
	  select 'TABLE' type, owner, table_name object_name, degree
	  from dba_tables
	  where table_name in ('T1','T2')
	   and owner = 'LORIS'
		  union all
	  select 'INDEX', owner, index_name, degree
	  from dba_indexes
		where index_name in ('IX1','IX2')
	   and owner = 'LORIS'
	)
	order by degree desc;

	TYPE  OWNER     OBJECT_NAME  DEGREE
	----- --------- ------------ ----------
	INDEX LORIS     IX1          2
	INDEX LORIS     IX2          1
	TABLE LORIS     T1                    3
	TABLE LORIS     T2                    1
So you should use the function TO_NUMBER:
	select*
	from (
	  select 'TABLE' type, owner, table_name object_name, to_number(degree) degree
	  from dba_tables
	  where table_name in ('T1','T2')
		and owner = 'LORIS'
		  union all
	  select 'INDEX', owner, index_name, to_number(degree)
	  from dba_indexes
		where index_name in ('IX1','IX2')
		and owner = 'LORIS'
	)
	order by degree desc;



November - Oracle Error

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


Your database is in Oracle 10g on an AIX machine.
You want to bounce the database but you receive an error:
	SQL> shut immediate;
	Database closed.
	Database dismounted.
	ORACLE instance shut down.
	SQL>
	SQL> startup mount;
	ORA-24324: service handle not initialized
	ORA-01041: internal error. hostdef extension doesn't exist
	SQL>
What you can do in order to avoid this error?



October - RMAN Problem

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


In RMAN, you have a script starting like this:
	RUN
	{
	  SET UNTIL TIME '02-07-09 15:00:00';
	...
You try to launch it or similar versions (for example, changing the order between months and days) but you receive errors like these:
	ORA-01843: not a valid month
	or
	ORA-01861: literal does not match format string
Then you check this:
	SQL> sho parameter nls_time_format

	NAME              TYPE        VALUE
	----------------- ----------- ------
	nls_time_format   string
Its value is null... not really useful for us.
What is the easiest way to solve this problem?

Solution

You could use the function to_date:
	RUN
	{
	SET UNTIL TIME "to_date('20090702 15:00:00','yyyymmdd hh24:mi:ss')";
	Ö



September - DB Link

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


The developers' group asks you to do a db link from DB_1 to DB_TARGET (user lassi, password lassi).
You write this in the tnsnames of DB_1:
DB_TARGET.COMPANY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.25.222 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DB_TARGET )
    )
  )
You launch this:
 CREATE PUBLIC DATABASE LINK DB_TARGET 
 CONNECT TO lassi
 IDENTIFIED BY lassi
 USING 'DB_TARGET.COMPANY';
After you check the db link:
 SELECT*FROM DUAL@DB_TARGET.COMPANY
                  *
 ERROR at line 1:
 ORA-02085: database link DB_TARGET.COMPANY connects to DB_TARGET
Then you try:

- writing the entry in the tnsnames (the SID in it is correct) like this:
 DB_TARGET =
  (DESCRIPTION =
	...
- and launching this:
 CREATE PUBLIC DATABASE LINK DB_TARGET.COMPANY
	...
- and try any combination of these.

But the db link never works.
In DB_1 the value of the parameter global_names is true.
After, you see this:
 DB_1:
 NAME           TYPE        VALUE
 -------------- ----------- --------
 db_domain      string      COMPANY

 DB_TARGET:
 NAME           TYPE        VALUE
 -------------- ----------- --------
 db_domain      string
Why is it happened?
And what you can do in order to solve this problem?




August - Query Optimization

Level (*) : 1
Topic (*) : Progr


You are in Oracle 9.2 and you have this query:
 SELECT*
 FROM tab1
 WHERE col1 in (1,2,3)
   AND substr(col2, 1, 3) = 'AAA';
You have a function-based index on the columns col1-col2.
But the optimizer doesn't use this index even if you analyze the table and the index.

How you could write the query in order to be sure (almost sure) that the optmizer will use that function-based index?

Solution

You should use a hint. If the name of the function based is IDX_FUNCTION, you should write the query like this:
 SELECT /*+ index (tab1 idx_function) */ *
 FROM tab1
 WHERE col1 in (1,2,3)
   AND substr(col2, 1, 3) = 'AAA';




July (2) - Compile error

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



You are trying to recompile the package LASSI.DB_PKG but you receive this error:
CREATE OR REPLACE PACKAGE LASSI.DB_PKG
*
ERRORE alla riga 1:
ORA-04021: timeout occurred while waiting to lock object LASSI.DB_PKG
How you can find who is accessing to that package?
What you can do in order to recompile that package?

Solution

You use the V$ACCESS:
 select*
 from  V$ACCESS
 where object='DB_PKG';

      SID OWNER   OBJECT     TYPE
 -------- ------- ---------- --------
      173 LASSI   DB_PKG     PACKAGE
Then, that package is used from the session with sid=173.
So to recompile the package, you have 2 solution:
1. wait that the session above will be finished
2. kill the session above

About the second solution:
 select SID, SERIAL#, SCHEMANAME, OSUSER, PROGRAM, LOGON_TIME
 from v$session 
 where sid=173;

    SID  SERIAL#  SCHEMANAME  OSUSER   PROGRAM	 LOGON_TIME
 ------ --------- ----------- -------- --------- -----------
    173    12644  LASSI		  a809870  TOAD.exe	 16-JUN-09

 alter system kill session '173, 12644';





July - Strange OS error

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


You are in Oracle 9.2.0.8.0 in an AIX server.
You are connected with the OS user lassi and you try to connect to the db dblor:
 /home/lassi > sqlplus loris/loris

 SQL*Plus: Release 9.2.0.8.0 - Production on Wed May 13 11:50:01 2009

 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 ERROR:
 ORA-01034: ORACLE not available
 ORA-27121: unable to determine size of shared memory segment
 IBM AIX RISC System/6000 Error: 13: Permission denied
Then you check its environment:
 /home/lassi >env
 ...
 ORACLE_HOME=/oracle/dblor/dbms9
 ORACLE_SID=DBLOR
 PATH=/usr/bin:/etc:/usr/sbin:/home/lassi/bin:/usr/bin/X11:/sbin:.:/oracle/dblor/dbms9/bin
And everything is ok.
Then you try to launch the changePerm.sh, but you still have the problem.

Why is it happened?
How can you solve it?




June - Database Link

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


You have the db DB1 in Oracle 9.2.
The developer group of this db asks you to change the password of the database link ENWI.COMPANY.
This request is because they changed the password of the remote user.
The connection must use the same user and the new password is WEB123.
In this moment nobody is using this db link.


How can you do it?

Solution

The ALTER DATABASE LINK doesnít exist. Then you have to drop it and re-create it. To have its script, you can use the function dbms_metadata.get_ddl (feature Oracle 9i):
 set long 2000
 SELECT dbms_metadata.get_ddl ('DB_LINK', 'ENWI.COMPANY', 'PUBLIC' )
 FROM dual;

 DBMS_METADATA.GET_DDL('DB_LINK','ENWI. COMPANY ','PUBLIC')
 -----------------------------------------------------------
  CREATE PUBLIC DATABASE LINK "ENWI.COMPANY"
   CONNECT TO "WEBBY" IDENTIFIED BY "A1ABCDE$"
   USING 'ENWI.COMPANY'
Then you can do:
 drop public DATABASE LINK "ENWI.COMPANY ";

 CREATE PUBLIC DATABASE LINK "ENWI.COMPANY"
 CONNECT TO "WEBBY" IDENTIFIED BY WEB123
 USING 'ENWI.COMPANY';
You also could take the info from the dba_db_links:
 select* from dba_db_links;

 OWNER    DB_LINK       USERNAME    HOST          CREATED
 -------- ------------- ----------- ------------- --------------
 PUBLIC   ENWI.COMPANY  WEBBY       ENWI.COMPANY  30-APR-09




May - SQL and File System

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



You have your db in a Unix machine and in this machine you have only that db.
You have to add a data file (quite big, example 20G).

Before adding it, you want to find out:
- the file systems where there is already at least a datafile
- among them, the file system less occupied. You will add the data file in this file system.

You know that every file system are like this:
  /oracleXX/YYY/
Then you have to find a query that has a result like this:

  ! df -k /oracle01/data/
  ! df -k /oracle01/data2/
  ! df -k /oracle03/data11/
  ! df -k /oracle05/da_33/
  ...

Solution

Use the function instr to find the position of the third "/":
 select distinct '! df -k ' || substr(file_name,1,instr(file_name,'/' ,1,3)) 
 from dba_data_files;




April - Hot Backup Script

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



You have your dbs in Windows and you want to do a procedure that create a file, named hot_backup.sql, like this:
	conn system/your_pwd ;
	alter tablespace TBL1 begin backup ;
	host xcopy  R:\ORADATA\TBL_01.DBF  R:\Oracle\Backup\Database  /Y  ;
	alter tablespace TBL1 end backup ;
	alter tablespace TBL_IDX_1 begin backup ;
	host xcopy  R:\ORADATA\TBL_IDX_01.DBF  R:\Oracle\Backup\Database  /Y  ;
	host xcopy  R:\ORADATA\TBL_IDX_02.DBF  R:\Oracle\Backup\Database  /Y  ;
	alter tablespace TBL_IDX_1 end backup ;
	Ö
	alter system switch logfile;
This file is useful in order to do a hot backup (not RMAN) of the database.

What the file says:
- the user that will do the hot backup
- for every tablespace (not temporary), it will put the tablespace in "begin backup", it will do a copy of every its datafile (from its location to R:\Oracle\Backup\Database), and after it will put the tablespace in "end backup"
- in the end, it will do a logfile switch

Solution

You should be connected as SYSTEM or another administrator user (this second case is better) :

 CREATE OR REPLACE PROCEDURE hot_backup IS

    -- VARIABLES USEFUL TO USE THE utl_file PACKAGE
    dest  varchar2(400);
    td  utl_file.file_type;

 BEGIN

    td := utl_file.fopen('data_pump_dir','hot_backup.sql' ,'w',32000);
    dest := 'conn system/your_pwd;' ;
    utl_file.put_line(td,dest);
    
    -- EXTERNAL LOOP: TABLESPACE NOT TEMPORARY
    for x in 
      (select * 
       from v$tablespace 
       where name not in 
          (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY' ))
    loop
          dest := 'alter tablespace ' || x.name || ' begin backup ;';
          utl_file.put_line(td,dest);
          -- INNER LOOP: DATAFILES FOR EACH TABLESPACE
          for j in  
            ( select name  
              from v$datafile t 
              where t.ts#=x.ts# )
          loop
                dest := 'host xcopy  ' || j.name || '  R:\Oracle\Backup\Database  /Y  ;';
                utl_file.put_line(td,dest);
          end loop;
          dest :=  'alter tablespace  '|| x.name || ' end backup ;';
          utl_file.put_line(td,dest);
    end loop ;
    
    dest := 'alter system switch logfile;';
    utl_file.put_line(td,dest);
    utl_file.fclose(td);
 END;




March - Problems deleting a User

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



You want to delete the user LASSI. Then you, as SYS, launch:
	drop user LASSI cascade;
While it's working, you lose the connection.
Then you again connect SYS in the db and you try again:
	drop user LASSI cascade
	*
	ERROR at line 1:
	ORA-01940: cannot drop a user that is currently connected

In the dba_users, of course you still see that user. You launch a query in the v$session and you see that LASSI has active sessions!
And you try to see in v$locked_objects joined with dba_objects, but there aren't its objects locked.
Then what you can do in order to delete that user?




February - Free Space Problem

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



In your db, you have a tool that always checks the db status.
From this tool, you receive an alarm where it shows that the biggest chunk free in the tablespace LA_DT01 is 100M and in this tablespace there is a segment that has NEXT=135M.
Then Oracle will not able to allocate next extent.
The name of this segment is SYS_LOB0000024116C00002$$.

In the DBA_SEGMENTS you see that this segment has EXTENTS=3 and PCT_INCREASE=50.
And you discover that the tablespace size is 2G and its data files have AUTOEXTEND OFF.
Now the total size of the segments in that tablespace is only 700M.
In your hard disk, you don't have free space.

What's a fast solution of this problem?
And which is a DML that you should use as workaround of that problem?

Solution

A fast solution of this problem is to decrease the NEXT of that segment.
1.  You find out which segment is that:

 select segment_name, segment_type, next_extent, tablespace_name
 from dba_segments 
 where segment_name = 'SYS_LOB0000024116C00002$$';

 PARTITION_NAME             SEGMENT_TYPE    NEXT_EXTENT    TABLESPACE_NAME
 -------------------------- --------------- -------------- ----------------
 SYS_LOB0000024116C00002$$  LOBSEGMENT      141557760	   LA_DT01

2.  You find out in which table is that lob segment:

 select owner||'.'||table_name "TABLE", column_name, segment_name 
 from all_lobs 
 where segment_name= 'SYS_LOB0000024116C00002$$';

 TABLE       COLUMN_NAME   SEGMENT_NAME
 ----------- ------------- ----------------------------
 LA.SDI_XML  XML           SYS_LOB0000024116C00002$$

3.  Then this is the correct DML:

 alter table LA.SDI_XML
 modify lob (XML) 
  (STORAGE (NEXT 60m PCTINCREASE 0));

In it, itís important :
-  to have PCTINCREASE 0
-  the NEXT smaller then 100M (that is the biggest chunk free).







January - Redo logs archived

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



You want to calculate:
- how many archive logs the db has generated during every day in the last week
- how much disk-space these archive logs occupied (every day)

For example, you have to find a query that has this result:

	Date        N.Archive		Tot.MB
	---------- ---------- --------------------
	2008-12-16	173		32400
	2008-12-17	140		26207
	2008-12-18	166		30644
	2008-12-19	162		30279
	2008-12-20	173		32434
	2008-12-21	174		32404
	2008-12-22	187		35683
	2008-12-23	184		35783
In this example, on 21st the db has generated more archive logs than on 20th but their total space occupied has been less.
how is it possible? and why this is more probable in an environment with standby db?

If you want to do the same calculation about more then one week, which Oracle parameter use should check and maybe you should change?




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