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 are in Oracle 10g and, on the schema LORIS, you have 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 have the list of these segments 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!

Your database is Oracle 10g on 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 instance, 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?

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

Then, what you can do in order to solve this problem?

You are in Oracle 9.2.0.6 and you have this query:

SELECT*
FROM tab1
WHERE col1 in (1,2,3)
  AND substr(col2, 1, 3) = 'AAA'
ORDER BY col3;

You have a function-based index on the columns col1-col2.
But the optimizer don'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 index?

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 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 db_lor:

/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/db_lor/dbms9
ORACLE_SID=DB_LOR
PATH=/usr/bin:/etc:/usr/sbin:/home/lassi/bin:/usr/bin/X11:/sbin:.:/oracle/db_lor/dbms9/bin

And everything is ok.
Then you try to launch the changePerm.sh, but you still have the problem.
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?

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

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

You want to delete the user LASSI. Then you, as SYS, launch:

	drop user LASSI cascade;

While it works, 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 receive:

       SID    SERIAL#    COMMAND USERNAME                       STATUS   SQL_HASH_VALUE 
   ------- ---------- ---------- ------------------------------ -------- -------------- 
         1          1          0                                ACTIVE                0 
         2          1          0                                ACTIVE                0 
         3          1          0                                ACTIVE                0 
         4          1          0                                ACTIVE                0
         5          1          0                                ACTIVE       4115672663
         6          1          0                                ACTIVE       2618850598
         7          1          0                                ACTIVE                0
         8          1          0                                ACTIVE                0
        10      10542          0 SYS                            INACTIVE              0
        16      17566          0 SYS                            ACTIVE       3395058227
        19         38          0 SYS                            INACTIVE              0
        21         10          3 SYS                            ACTIVE       3692607210

Then that user is not connected!
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 show 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?

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?

webmaster	© Copyright 2005-2010 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.