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.

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

You are in Oracle 10 and you have done a good PL/SQL procedure, called pr_trunc.
This procedure can be used but you want that nobody 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 it.

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

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.

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 took from a RAC db.
How can you adjust that parameter?

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?

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

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?

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 should create the database link in order to avoid this error?

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?

webmaster	© Copyright 2005-Today Loris ASSI
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.