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.

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!

	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

	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;

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?

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?

	RUN
	{
	SET UNTIL TIME "to_date('20090702 15:00:00','yyyymmdd hh24:mi:ss')";
	…

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?

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?

 SELECT /*+ index (tab1 idx_function) */ *
 FROM tab1
 WHERE col1 in (1,2,3)
   AND substr(col2, 1, 3) = 'AAA';

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?


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

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?

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?

 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'

 drop public DATABASE LINK "ENWI.COMPANY ";

 CREATE PUBLIC DATABASE LINK "ENWI.COMPANY"
 CONNECT TO "WEBBY" IDENTIFIED BY WEB123
 USING 'ENWI.COMPANY';

 select* from dba_db_links;

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

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/
  ...

 select distinct '! df -k ' || substr(file_name,1,instr(file_name,'/' ,1,3)) 
 from dba_data_files;

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

 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;

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?

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?


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

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?

© 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.