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.

You want to delete all the data inside the tables in the STATSPACK schema that are older than 1 year.
What is a sample way to do it?

 Delete stats$snapshot 
 where SNAP_TIME>sysdate-365;

You are in Oracle 10g and you have a PL/SQL procedure called pr_trunc.
This procedure can be used but you want that nobody (neither the schema owner) can see its source.
How can you do it?

You have the index LASSI.idx_1.

You want to discover whether it is used, or not:
- how can you enable this check?
- which view you have to examine if you are connected as LASSI? and if you are connected as SYS?
- how can you stop this check?

You are trying to compile a package but you receive the error

  ORA-04021: timeout occurred while waiting to lock object 

You want to know who is using it. How can you do it?
You want a result like this:

  (103) - LASSI
  (211) - USER2

where 103 and 211 are the SIDs and LASSI and USER2 are the users that are using the package.

 set serveroutput on

 exec who_is_using ('PKG_LASSI')
  (103) - LASSI
  (211) - USER2

You have to write a query that find out your hidden parameters and some its characteristics.
Example of its result:

  NAME                                  VALUE        DEFLT     TYPE
  ------------------------------------- ------------ --------- ----------------
  _abort_recovery_on_join               FALSE        TRUE      boolean
  _active_standby_fast_reconfiguration  TRUE         TRUE      boolean
  _adaptive_direct_read                 TRUE         TRUE      boolean
  _adaptive_fetch_enabled               TRUE         TRUE      boolean
  _adjust_literal_replacement           FALSE        TRUE      boolean
  _affinity_on                          TRUE         TRUE      boolean
  _aiowait_timeouts                     100          TRUE      number
  ...

 select a.ksppinm name,
	b.ksppstvl value,
	b.ksppstdf deflt,
	decode
	(a.ksppity, 1,
	'boolean', 2,
	'string', 3,
	'number', 4,
	'file', a.ksppity) type
	--,a.ksppdesc description
 from
	sys.x$ksppi a,
	sys.x$ksppcv b
 where
	a.indx = b.indx  AND
	a.ksppinm like '\_%' escape '\'
 order by 1;

You are in Unix and inside the directory /oracle/db_lor/dbms102/rdbms/audit
you have thousands of files having the name starting with "ora_1".

You want to move them inside the directory
/oracle/db_lor_audit_ora_1

You try it normally, but you receive one error:

  mv ora_1* /oracle/db_lor_audit_ora_1/
  bash: /bin/mv: Argument list too long

How can you do this?

You want to find the tablespaces that have the datafiles only in /oracled02.
Find out the query that do this.

 select distinct tablespace_name  
 from dba_data_files 
 where file_name  like '/oracled02%'
   minus
 select tablespace_name  
 from dba_data_files 
 where file_name not like  '/oracled02%';

You have created a single db instance in Oracle 10.2.
When you start the instance, you receive the error ORA-32004 :

 SQL> startup

 ORA-32004: obsolete and/or deprecated parameter(s) specified
 ORACLE instance started.

 Total System Global Area  981467136 bytes
 Fixed Size                  2088800 bytes
 Variable Size             436207776 bytes
 Database Buffers          536870912 bytes
 Redo Buffers                6299648 bytes

 Database mounted.
 Database opened.

How can you find which is the "wrong" parameter ?

You find that the "wrong" parameter is max_commit_propagation_delay.
You are using a modified SPFILE copied from a RAC db.
How can you adjust that parameter?

You will find that parameter inside the alert.log :

 ...
  pga_aggregate_target     = 536870912
  _optimizer_connect_by_cost_based= FALSE
  _right_outer_hash_enable = FALSE
 Deprecated system parameters with specified values:
  max_commit_propagation_delay
 End of deprecated system parameter listing
 Wed Apr  7 16:55:36 2010
 Oracle instance running with ODM: Veritas 5.0 ODM Library, Version 1.1
 PMON started with pid=2, OS id=1298548
 PSP0 started with pid=3, OS id=1290286
 ...

You can change that parameter using this command:

 alter system reset max_commit_propagation_delay
 scope=spfile
 sid='*';

After that, you have to do the bounce of the database.

You are in Oracle 10g and you want to disable one database link without dropping it.
Actually, in Oracle the Database links cannot be altered.
But there is a workaround.
Which is it?

db_lor =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.122.144 )(PORT = 1521)))
    (CONNECT_DATA =
      (SERVICE_NAME = db_lor ))
  )

# db_lor =
#   (DESCRIPTION =
#     (ADDRESS_LIST =
#       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.122.144 )(PORT = 1521)))
#     (CONNECT_DATA =
#       (SERVICE_NAME = db_lor ))
#   )

You want to do a stored procedure in order to check every 30 seconds how many sessions (active and inactive) are on your db:
When you launch this check, the procedure must do 10 snapshots and you must see graphically the result.
Example of the result:

 Time                Active Ses    Inact Ses        Active | Inactive
 20091119 16.33.01       13           229             **** | ***************
 20091119 16.33.31       14           227             **** | ***************
 20091119 16.34.01       16           225             **** | ***************
 20091119 16.34.31       16           226             **** | ***************
 20091119 16.35.01       13           229             **** | ***************
 20091119 16.35.31       13           229             **** | ***************
 20091119 16.36.01       16           223             **** | ***************
 20091119 16.36.31       22           210            ***** | **************
 20091119 16.37.03       22           220            ***** | ***************
 20091119 16.37.33       15           245             **** | ****************

set serveroutput on size 1000000;

declare

 v_sessions	varchar2(2000) := null; -- USEFUL FOR THE CURSOR RESULT
 v_snapshots	pls_integer := 10;	-- NUMBER OF SNAPSHOTS
 v_wait		pls_integer := 30;	-- NUMBER OF SECONDS BETWEEN THE SNAPSHOT

BEGIN

 dbms_output.put_line (' Time                Active Ses    Inact Ses        Active | Inactive');

 for i in 1..v_snapshots loop

	select  to_char(sysdate,'yyyymmdd hh24.mi.ss') || 
 		LPAD(sum(decode(status,'ACTIVE',count(1))),9,' ')  ||
 	 	LPAD(sum(decode(status,'INACTIVE',count(1))),14,' ') || '  ' ||
		LPAD(LPAD('*',round(sqrt(sum(decode(status,'ACTIVE',count(1))))) ,'*'),15,' ')
		|| ' | ' || 
		RPAD('*',round(sqrt(sum(decode(status,'INACTIVE',count(1))))),'*')
	into v_sessions
	from v$session
	group by to_char(sysdate,'yyyymmdd hh24.mi.ss'),
		 status;

	dbms_output.put_line (v_sessions);

	if i != v_snapshots then 
		dbms_lock.sleep(seconds => v_wait);
	end if;

 end loop;

end;

Your db is in Oracle 9.2.0.6.0 in a Sun Microsystems machine.
The datafiles are in the File System (FS) /oracle/dblor/data.
The tempfile is in the FS /oracle/dblor/temp.

The FS /oracle/dblor/temp is totally full then you ask the UNIX group to extend it.
But they say that in order to do this the FS can't be totally full.

In it there is only one file and it's the file of the temporary tablespace TEMP.
Its size is 300 MB and it is in AUTOEXTEND till 400 MB.

Which is the 2 ways to check if the TEMP is offline?
How you can bring it again online?

After that you bring it online, you could make that file a little bit smaller (295M) but you want to be sure that, till the FS will not be larger, new sessions will not use it.
What are the last steps in order to solve your problem?
And why could be useful the view database_properties?

You want to know the names of the tablespaces where at least one datafile has been added today.
You don't have access to the file system then you have to find them using a SQL query.
Which is this query?

 select  distinct tbs.name
 from  v$datafile dbf,
       v$tablespace tbs
 where trunc(dbf.CREATION_TIME)=trunc(sysdate) and
       dbf.ts# = tbs.ts#;

In the machine SERVER_1, you have the database DB1 and in the TNSNAMES.ORA you have this entry:

 db2.world =
   (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER_2)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SERVICE_NAME = DB2000)
    )
   )

Then you create a database link:

  CREATE DATABASE LINK DB2000
  CONNECT TO user1 IDENTIFIED BY pwd1
  USING 'db2.world';

From the SERVER_1, you try this database link and it works:

  select * from dual@DB2000;

  D
  -
  X

Then, you try the same query from a client connected to the DB1 and you receive this error:

  "ORA-12154: TNS:could not resolve the connect identifier specified"

How you could create the database link in order to avoid this error?

In the database link creation, you could use the TNSNAMES.ORA entry:

  CREATE DATABASE LINK DB2000
  CONNECT TO user1 IDENTIFIED BY pwd1
  USING '(DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER_2)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SERVICE_NAME = DB2000)
    )
   )';

You want to do a SQL tuning of this:

  SELECT COUNT(*) 
  FROM tab_1 , tab_2
  WHERE tab_1.IDDOC = tab_2.IDDOC 
	AND tab_1.LASTVERSION = tab_2.VERSION
	AND tab_2.DIR = :b1;

Then you decide to use an Oracle event:
Which undocumented Oracle event is useful in this situation?
And which level you have to use in this event if you want to have also the wait event (as the example below)?

 Elapsed times include waiting on following events:
   Event waited on                 Times   Max. Wait  Total Waited
   -----------------------------  Waited  ----------  ------------
   SQL*Net message to client           3        0.00          0.00
   SQL*Net message from client         3       16.10         16.14
   db file sequential read            32        0.00          0.02

And how you can stop the Oracle event?

You have to use the event 10046 and, after, use the TKPROF to interpret the trace file.
In order to activate this event :

  alter session set events '10046 trace name context forever, level num'; 

Where num is either 1,4,8 or 12, with:
- 1 being the same as a normal set sql_trace=true,
- 4 including values of bind variables,
- 8 including wait events,
- 12 including both bind variables and wait events.
Then in this example, you have to use the level 12:

  alter session set events '10046 trace name context forever, level 12'; 

In order to stop it:

  alter session set events '10046 trace name context off';

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