ManualiOracle.it
  
 
> Quizzes > Quizzes 2005-2009 - Oracle Best Pratices
 
 
 
 
  


In the page Prices and Donations, you can find how to have the solutions of the quizzes.
Nella pagina Prezzi Quiz potete vedere come avere le soluzioni dei quiz level 3.


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 2009 - 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 2009 - 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 2009 - 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 2009 - 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 2009 - 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) 2009 - 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 2009 - 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 2009 - 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 2009 - 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 2009 - 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 2009 - 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 2009 - 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 2009 - 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?




December 2008 - sqlplus "/as sysdba" problem

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


You have an Oracle db in an AIX machine.
To manage that db, you use 2 different OS users:
- osdblor: installation, patches, etc...
- oradblor: day to day administration

This is the result of their "id" shell command:
 osdblor:
 uid=20470(osdblor) gid=55(oinstall) groups=54(dba)
 oradblor:
 uid=20471(oradblor) gid=54(dba)
You see that you can do sqlplus "/as sysdba" with the first OS user but when you try it with the second user, you receive 
 ORA-01031: insufficient privileges
What is the cause of that problem? What you can do in order to use the sqlplus "/as sysdba" with the oradblor OS user?




November 2008 - Special Tablespaces

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


You are on Oracle 9i and you want to check 2 "special" tablespaces: the TEMPORARY and the UNDO tablespaces.
For the TEMPORARY tablespaces, you must find a query in order to find how much space is used in it.
Example: in this db, you have 2 temporary tablespaces then you must receive a result like this :

 TABLESPACE_NAME  MB Used  MB Size  % Used
 ---------------- -------- -------- ------
 TEMP                   16      200      8
 TEMP_NEW             3405     3405    100
For the UNDO tablespace, you must find a query that show some indicators in order to understand if its size is right or not.
For example, you must receive a result like this:

 Undo Stats
 ------------------------------------------------
 Start Statistics: 20081017 12:17
 End Statistics: 20081024 12:13
 Minimum Size suggested for the Undo tbs: 672 MB
 Max Length Query: 6109 seconds
 How many ORA-01555: 0
 How many Out-of-Space in the Undo tbs: 0
In this example, the db has an undo-size of 3 GB.
Be careful to draw a conclusion: you could think that the undo tablespace is too big because the size suggested is 672 MB.
But remind that every indicator here is refered only to the period considered in the system views: in this case, only the last week: from 20081017 12:17 to 20081024 12:13.




October 2008 - Sql*Plus Problem

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


You have the db "dblor" in 10.2.0.4 and it is on AIX.
In that machine you have the OS user "ordblor" and you try to connect in Sql*Plus with this OS user:

 $ sqlplus
 Error 6 initializing SQL*Plus
 Message file sp1.msb not found
 SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
 Exit 1
Then you have a look at its .profile:
 export ORACLE_HOME=/oracle/dblor/dbms102040
 export PATH=/usr/bin:/etc:/usr/sbin:$HOME/bin:/usr/bin/X11:/sbin:.:$ORACLE_HOME/bin
 export TNS_ADMIN=/oracle/dblor/admin/tnsadmin
 export ORACLE_SID=dblor
and you check its UID:
 $ id
 uid=18216(ordblor) gid=54(dba) groups=1(staff)
How you can use Sql*Plus with this OS user?

Solution 
Connected as the Oracle Software owner (also while the db processes are active):

 cd $ORACLE_HOME/install

 ./changePerm.sh

It runs for about 10 minutes.
It decreases the db access security then before that you launch it, you should check in the Oracle
documentation.




September 2008 - SQL Query

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


You have this query: 
 select ...
 from view1 a,
      view2 b
 where a.col1 = b.col2;
The view1 is on table10.
The view2 is a join query between table20 and table21.

When you launch this query, the Oracle Optimizer choose a bad explain plan:
it does a Full Table Scan on table21: 9 millions rows.

You want that the Optimizer uses the index table21_idx5 (7000 distinct values) on the table21.
How you can do it?

You must do it:
- without changing Oracle parameters
- without using a hint inside the view2: usually it's not a good idea to put a hint inside a view

Solution 
In the query, you can put a hint that use the index table21_idx5:

 select /*+ index( b.table21 table21_idx5 ) */
	...
 from view1 a, 
      view2 b
 where a.col1 = b.col2;

In that hint then you have:
- the type of hint: index
- the alias "b" where the Optimizer can find the table21
- the index name to use: table21_idx5




August 2008 - Rows affected

Level (*) : 1
Topic (*) : Prog


You have the table EMP. It contains the employees of your company.
You want to do an anonymous block and in it you want to do this: 
 UPDATE emp
 SET sal = sal*1.1
 WHERE sal < 1000;
Moreover, execution this block, you want to see how many rows are affected by this DML.
How you can do it?

Solution 
In order to do the update, Oracle uses an implicit cursor, than you can use a cursor attribute:
sql%rowcount:

 declare 
  a pls_integer := 0;
 begin 
  UPDATE emp
  SET sal = sal*1.1
  WHERE sal < 1000;
  a:=sql%rowcount; 
  dbms_output.put_line(a); 
 end;




July 2008 - Privs on Updates

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


In the schema LORIS, you have this table: 
 create table emp1
 ( cod number,
   name varchar2(100),
   salary number );
In it, you have some rows: 
 insert into emp1 values (10,'loris',1000);
 insert into emp1 values (20,'mirko',900);
 insert into emp1 values (30,'emanuele',800);
 insert into emp1 values (40,'pippo',600);
 insert into emp1 values (50,'pluto',600);
In your db, you have the user SCOTT and you want both:
- SCOTT could do update of the employees on EMP1 with cod>30
- SCOTT could do update only of the field SALARY

How you can do this?

Solution 
In order to reach these goals, you could use a view: 
Connected as LORIS:

 CREATE OR REPLACE VIEW view_emp1 AS
 SELECT *
 FROM emp1
 WHERE cod>30;

 GRANT UPDATE ( salary ) 
 ON view_emp1
 TO scott;

So, the user SCOTT can do something like this:

 update loris.view_emp1 
 set salary=0;

 2 rows updated

And it can’t do operations like these:

 update loris.emp1
 set salary=0;

 ORA-00942: table or view does not exist

Or

 update loris.view_emp1 
 set cod=0;

 ORA-01031: insufficient privileges




June 2008 - Strange Oracle Error

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


You can have this strange situation: 
SQL> select* from v$version;

BANNER
-----------------------------------------------------------
Oracle9i Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE	9.2.0.1.0	Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> select trunc(avg(tempo_di_risp),2), trunc(starttime),
transactionname from cwf
where trunc(starttime) < trunc(sysdate)
group by trunc(starttime), transactionname;
It works a while and after that it shows same rows, you receive: 
ORA-03113: end-of-file sul canale di comunicazione
After that, you can't access on the db and you must restart it!
After the db bounce, you discover:
- you can do "select* from cfw" without problems
- the table cfw doesn't have blocks corruption
- the describe of that table is:
Name		 Type	  Nullable Default
-------------------- ------------- -------- -------
ID		   NUMBER	Y
SOURCE_ID	    VARCHAR2(30)  Y
STARTTIME	    DATE	  Y
ELAPSEDTIME	  NUMBER	Y
TRANSACTIONNAME      VARCHAR2(255) Y
DISPONIBILITA	NUMBER	Y
TEMPO_DI_RISP	FLOAT	 Y
- than you try again the first query and you receive the same error after the same number of fetched rows.

What could be the problem?
And how you could avoid it?




May (2) 2008 - Strange Column Values

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


You can have this strange situation: 
SQL> select* from v$version; 

BANNER 
---------------------------------------------------------------- 
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE	9.2.0.7.0	Production

TNS for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

SQL> select*
from tab1;

UID	NAME
---- --------
10	Loris
11	Mirko

SQL> select uid
from tab1;

UID
----
78
78
Maybe an update is happened. Then you try again: 
SQL> select*
from tab1;

UID	NAME
-------------
10	Loris
11	Mirko
Then it wasn't an update.
How is it possible that Oracle show different UID values according to the query?




May 2008 - Problem with user creation

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


You can have this strange situation: 
SQL> select* from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create user loris identified by loris
default tablespace users temporary tablespace temp;

ORA-00600: internal error code, arguments: [kzdugn], [], [], [], [], [], [], []
The user monitor_db doesn't exists. And you receive the same error trying to create others users.
You try to restart the db but you still receive that error.
What you can do to create that user?




April 2008 - SQL and Possessive Form

Level (*) : 1
Topic (*) : Prog


You have a table like this: 
 create table x (first_name varchar2(50), last_name varchar2(50));
And you have 2 rows, such as below, inside that table: 
 insert into x VALUES ('SCOTT','TIGER');
 insert into x VALUES ('HENRY','FLOWERS');
You must find a query that has this result: 
 Scott Tiger's
 Henry Flowers'
Then in this query, these are your goals:
- the names and surnames must have the first letter in capital form and the others letters not in capital form
- you must use the possessive form: at the end of the surname you must put 's. But if at the end of the surname there is an S, you must put only the '

Solution

You must use: - initcap: because of the capital letter - decode: to check the last letter 
 SELECT initcap(trim(first_name)||' '||initcap(trim(last_name)))|| 
 decode( 
 substr(lower(trim(last_name)),-1,1) 
 , 's', '''', '''s') 
 FROM X;




March 2008 - Double Table

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


You can have this strange situation: 
 SQL> select* from v$version;

 BANNER 
 ---------------------------------------------------------------- 
 Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production 
 PL/SQL Release 9.2.0.4.0 - Production 
 CORE 9.2.0.3.0 Production 

 TNS for Solaris: Version 9.2.0.4.0 - Production 
 NLSRTL Version 9.2.0.4.0 - Production 

 SQL> desc b 
 Object b does not exist. 

 SQL> create table b (bb number); 

 Table created 
 
 SQL> create table b (cc char); 

 Table created
 
 SQL> desc b
 Name Type Nullable Default Comments 
 ---- ------ -------- ------- -------- 
 BB NUMBER Y
On the second creation you don't receive any errors!
What could be the reason? And what could be a simple workaround to bypass that problem?

Solution 
Of course, this an Oracle bug.
Maybe the problem is that you don't have the unique index on sys.tab$.
In this case you have to create it (look at the details in the Oracle documentation).

If you already have that index, try an Oracle restart: it could be the solution!




February 2008 - kill a job

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


In your db, there is a running job and you want to stop it.
In order to do it, initially you put the job in BROKEN state.
After that, you search that job among the sessions but it is not easy to find it.

You must write a query that shows the running jobs and creates the script to kill these jobs.

E.g:
When you launch the query, you must have a result like this:

Job Schema_user What This_date Script
-------- --------- ------------------ ----------------- ------------------------
156 LASSI pr_archive; 18/02/2008 18.29.53 alter system kill session '20, 59149';
1976 SCOTT pkg_test.pr_rebuild; 18/02/2008 17.30.13 alter system kill session '87, 41857';

Solution 
You can use this query reading 3 views:

select jobs.job,
       jobs.schema_user,
       jobs.what,
       jobs_run.this_date,
       'alter system kill session ''' || sess.sid || ', ' || sess.serial# || ''';' script
from dba_jobs   jobs,
     dba_jobs_running  jobs_run,
     v$session  sess
where jobs.job = jobs_run.job  and
      jobs_run.sid = sess.sid;




January 2008 - Clone Db between 2 different Oracle version

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


You have a Oracle db in the machine machine1 and you want to clone it to the machine2.

There are 2 cases:
- in the machine2, the db has the same Oracle version as in machine1 (example Oracle 10g in both cases)
- in the machine2, the db has not the same Oracle version as in machine1 (e.g. the machine1 has Oracle9 and the machine2 has Oracle10).

How can you have the same data between the 2 dbs?

Actually in the last case, you shouldn't speak of "clone db" because you have a real "clone db" only if the 2 dbs has the same Oracle version.


Solution 
First case:

If target and source dbs have same version, you can use the DUPLICATE DATABASE RMAN command.

Second case:

If the db versions are different, the easiest way is thru export and import:
 1. machine2: Use the same parameters such as the source db
 2. machine1: export the schemas that you want (no system or similar)
 3. machine2: import the data


December 2007 - Clone Db

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


You have an Oracle db in the machine machine1.
You want to clone it in the machine2 that has exactly the same features of the machine1.
During this operation nobody can access to these dbs and you don't want to stop the db in the machine1.
Which is the fastest way to do it?

Solution 
This method is usually faster (and better) than export-import: 

 1.	target machine: install Oracle (same version than the source db) with a database

 2.	source machine: hot backup of the whole db:
	alter tablespace TBS1 begin backup;
	copy (not “cut and paste”!) its datafiles in a different location. 
	Eg in sql*plus in Windows: 
	  host xcopy  R:\ORADATA\TBS1.DBF  X:\oracle_hotbackup  /Y;
	alter tablespace TBS1 end backup;
	etc… etc… for the others tablespaces (except the temporary tablespace)
	alter system switch logfile;

 3.	source machine: backup of the controlfile:
	alter database backup controlfile to trace;   (you can find it in the udump)

 4.	target machine: db shutdown and here you copy the datafiles and the controlfile
	in the same directories than in the source machine

 5.	startup the target db




November 2007 - Existing Files

Level (*) : 1
Topic (*) : Prog


We are in Oracle 9i on a Sun Solaris machine.
You have to do a PLSQL program that, when is launched, do this:

1. checks if exists the file /store2/errlog/pippo.txt
2. if it's existing, you have to write (by dbms_output) 'The file exists'
3. if it's not existing, you have to write (by dbms_output) 'The file doesn't exists'

Solution 
You can check if the file exists by trying to open it:

 declare 

  dir constant varchar2(50):= '/store2/errlog'; -- directory where you check
  name varchar2(30):= 'pippo.txt'; -- file_name 
  id_file utl_file.file_type; -- type 
  exists_file exception; -- for exception 
  pragma exception_init (exists_file, -29283); -- for exception 

 begin 

  id_file:=utl_file.fopen(location => dir, filename => name, open_mode => 'r'); 
  dbms_output.put_line ('The file exists');
  utl_file.fclose (id_file);

 exception 

  when exists_file then 
      dbms_output.put_line ('The file doesn''t exist');
      utl_file.fclose (id_file);  

 end;




October 2007 - Log Tables

Level (*) : 3
Topic (*) : Prog


In our database, as in many others, we write information about some important transactions in a log table.
This is our log table:

create table LOG_MSG
(nr_sequence number, procedure_name varchar2(70), message varchar2(200), dt_insert date);


alter table LOG_MSG
add constraint LOG_MSG_PK
primary key (nr_sequence);
In this table, our application write about 200.000 rows every day.
You want to keep this information for 1 month.
You must find a way to access to this information always fast (then do every night a delete of the old rows is not a good solution).

Your solution must be suitable for Oracle8i, 9i and 10g and must be suitable also if in your db is not enabled the PARTITIONING option.



September 2007 - Datafile Contents

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


You know that the tablespace EXAMPLE has many free space: it's big 100M and has 95M free space.
You use the query used in the quiz of August 2007 so you see that you can do the resize of his datafile until his size will be 80M.
Then you want to look at inside his datafile contents to discover if it's easy to save more space in it.

Then you have to do a query that show the tablespace contents like that (ordered by block_id desc):

Tablespace File_id Owner || Segment Partition_name Segment_type Block_id Kbytes
-------------- --------- ------------------ -------------- ------------------ --------- ---------
EXAMPLE 5 Free Space 10265 20288
EXAMPLE 5 SH.FW_PSC_S_MV_IDX INDEX 10257 64
EXAMPLE 5 LASSI.EXAMPLE_PK EXAMPLE_PK_2 INDEX PARTITION 10157 800
EXAMPLE 5 Free Space 2157 64000
...
...

These are just the first rows of the result.
This result is useful because now you know that if you rebuild the indexes SH.FW_PSC_S_MV_IDX and LASSI.EXAMPLE_PK then you can resize the datafile until about 17M (of course, before that you rebuild these indexes you should check how much big they are).

In the query that you have to find, you have to use two substitution variables: one useful if you want to give the tablespace name, the other one if you want to give the datafile name (especially if the tablespace has more than one datafile).



August 2007 - Datafile resize and Dictionary Views

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


You are working in Oracle 9.2. In your machine there is just a little disk free space.
You know that the USERS tablespace has many unsed space then you decide to shrink his 2 data files.
You must find a SQL query that you can use in order to find how much big are these data files and how much big you can make them.

Example:
When you launch the SQL query, you must receive a result like this:

Tablespace File_name Size(KB) Highwater(KB) DDL Resize
--------- ---------------------- -------- ------------ ----------------------------
USERS D:\ORA92\ORCL\USERS1.DBF 80000 8792 alter database datafile 'D:\ORA92\ORCL\USERS1.DBF' resize 8792K;
USERS D:\ORA92\ORCL\USERS2.DBF 102400 96816 alter database datafile 'D:\ORA92\ORCL\USERS1.DBF' resize 96816K;

And you must find another SQL query for Oracle 8.0 and 8i.



July 2007 - Create table and Dictionary views

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


You have the table TAB_A and this table contains columns only with these datatypes: char, varchar2, date and number. These columns don't have any default value.

You must do a SQL query that you can launch in order to obtain the TAB_ORDERED such as TAB_A but with the column created in alphabetic order.
It's not important to take into account the tablespace_name, the constraints and the storage clause.

Example:
 SQL> desc tab_a
 Name    Type	 Nullable
 ------- ------------ --------
 FILE#   NUMBER       Y     
 STATUS  VARCHAR2(18) Y
 CHANGE# NUMBER       Y
 TIME    DATE	 Y
 FLAG    CHAR(1)
 SUM     NUMBER(10,2) Y
And when you launch your SQL query, you must obtain a result like this:
 CREATE TABLE TAB_ORDERED (
 CHANGE# NUMBER
 ,FILE# NUMBER
 ,FLAG CHAR(1) NOT NULL
 ,STATUS VARCHAR2(18)
 ,SUM NUMBER(10,2)
 ,TIME DATE
 );


Solution 
 select 'CREATE TABLE TAB_ORDERED (' 
 from dual 
   union all 
 select decode(rownum,1,null,',') || x.script 
 from ( 
  select a.column_name || ' ' || a.data_type || 
   decode(a.data_type,'VARCHAR2', '('||to_char(a.data_length)||')', 
  'NUMBER', decode(data_precision,null,null,'('||data_precision||','||a.data_scale||')'),
  'DATE', null, 
  'CHAR', '('||a.data_length||')') 
  || decode (a.nullable,'N', ' NOT NULL', NULL) script 
  from user_tab_columns a 
  where a.table_name = 'TAB_A' 
  order by column_name) x 
   union all 
 select ');' 
 from dual;




June (2) 2007 - SQL and System Views

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


You have many concurrencies in the tables and indexes of the schema MONITOR_DB.
Then you decide to modify their initrans like that:
- the table A will have initrans 8 and its indexes with initrans 9
- the table XXX will have initrans 6 and its indexes with initrans 7
- the other tables will have initrans 4 and their indexes with initrans 5

You must do ONE single SQL query that compose this.


Solution 
 select 'alter table ' || owner || '.' || table_name || ' initrans ' ||
        decode(table_name, 'A',8, 'XXX', 6, 4) || ';'
 from dba_tables
 where owner = 'MONITOR_DB'
   union all
 select 'alter index ' || owner || '.' || index_name || ' initrans ' ||
        decode(table_name, 'A',9, 'XXX', 7, 5) || ';'
 from dba_indexes
 where owner = 'MONITOR_DB';




June 2007 - Import, Export and Estimated Time

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


You are working in Oracle 9.2 and you want to do a long export of a single schema.
You must do a SQL query useful in order to estimate when this export will be finished.

The SQL query will be launched during the export and it must to take into account how big are the tables and their lobs.
For instance, when you launch the query, you must receive a result like that:
 EXPORT (tables and lobs):
 Start: 200706191500
 Now  : 200706191558
 Owner: MPH
 You are doing the table TR3L (Rate of this table already done: 90%)
 3872 MB Done
 1655 MB Not Done Yet
 The export will finish on 20070619 1623
When you execute this query you have to write:
- the time when you have started the import. In the example above: 200706191500
- the schema name that you want to export. In the example above: MPH
- the table_name that is exported in the moment that you launch the query. In the example above: TR3L
- the rate of the current table that has been already exported (estimation). In the example above: I have estimated 90%. If you don't know it, write 50.




May 2007 - Backup and Estimated Time

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


You are working in Oracle 9.2. and you are doing an hot backup.
You do the hot backup (very day) launching a script that do the backup of every tablespace except the temp tablespace.
You want to do a SQL query useful to estimate when the backup that now is happening will finish.

For example, you want a result like this:

 Start Backup: 26/05/2007 10.21.19 Now: 26/05/2007 10.24.52
 124 M backupped today.
 989 M missing.
 Estimated finish time: 26/05/2007 10.53.11
Remember that you work in Oracle 9.2 than you can use the "WITH clause" in order to have a query more efficient.



April 2007 - Index Rebuild

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


You want to do a SQL query that creates the commands useful to do the rebuild of any index (not SYS or SYSTEM's indexes) not partitioned that have more than 100 extents.
In that rebuild you want to put the storage and the tablespace that the index had before the rebuild.

For example, if you launch the SQL the result is this:

alter index MONITOR_DB.XXX2_IDX rebuild storage (initial 786432) tablespace USERS;
alter index MONITOR_DB.XXX_IDX rebuild storage (initial 7340032) tablespace PROVA_STRIPE;
alter index MONITOR_DB.A_IDX rebuild storage (initial 23068672) tablespace USERS;



Solution 

 select 'alter index ' || owner || '.' || segment_name || ' rebuild storage (initial ' ||
	 bytes || ') tablespace ' || tablespace_name || ';'
 from dba_segments a
 where segment_type = 'INDEX' and 
       owner not in ('SYS','SYSTEM')  and
       extents > 100;




March 2007 - Use Indexes

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


We are thinking to create an b-tree index in the ID_MOV column on the PIPPO table.
We have to calculate if that index could be selective, or not.

We don't want to do any statistics and we don't want to look at any dictionary views.
Then we decide to do a query and in that query we also want to calculate how many rows are in the PIPPO table.
Then you have to find the right query and you have to say one important init parameter that the optimizer use to decide if it is better to do a Full Table Scan or to use an index.


Solution 
This is the right query:

	SELECT count(1), count(distinct ID_MOV ),
		100 / count(distinct ID_MOV
		) || ' %' pct_selettivo
	FROM pippo;

Explanation:
 - first column: the rows in the table
 - second column: the distinct value in the column ID_MOV
 - last column: the average percentage (of the total rows) selected with a single
   ID_MOV value

Then you also have be careful at the distribution of the value in the ID_MOV.

Here we suppose that the distribution is uniform.
However if the last column of the result is less than 2-5% it could be good to add an
index.

But this decision also depends on:
 - how big is every table row: if the row is big, the Full Table Scan (FTS) is more
   probably heavy
 - the init parameter db_file_multiblock_read_count: if it is big, the FTS is more
   probably good in the small table: in the big table is very difficult that the FTS is
   good




February 2007 - Administration

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


After one db creation, it's a good idea to create a "superuser" on the db.
This superuser is useful because you can use it in order to do normal administrative taskes (instead of using SYS or SYSTEM) and because in his schema you can put customized procedures or tables that are important to automatic checkes or taskes on the db.

Find the syntax in order to create that user, his grants and a dedicated tablespace for it.
Do it for Oracle 8.0, Oracle 8i, Oracle 9i and Oracle 10g.


Solution 
Dedicated tablespace:

 oracle 8.0:
   create tablespace monitor
   datafile ‘D:\ORACLE\DATABASE\MONIT_01.ORA'     -- for example
   size 50M       
   default storage (initial 64k next 128k pctincrease 0);

 oracle 8i:
   create tablespace monitor
   datafile 'D:\ORACLE\DATABASE\MONIT_01.ORA'    -- for example
   size 50M        
   extent management local;

 oracle 9i/10g:
   create tablespace monitor
   datafile 'D:\ORACLE\DATABASE\MONIT_01.ORA'     -- for example
   size 50M        
   extent management local
   segment space management auto;

User script:

 oracle 8.0/8i/9i/10g:
   create user monitor_db
   identified by 
   default tablespace monitor
   temporary tablespace temp;

   grant dba to monitor_db;
   grant select any table to monitor_db;
   grant analyze any to monitor_db;
   grant execute on dbms_system to monitor_db;  -- this is in order to do the sql traces

 oracle 9i/10g:
   grant select any dictionary to monitor_db;




January (2) 2007 - Trace and TKPROF

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


In the schema SCOTT there is the procedure pr_pippo. This procedure do many operations and we would tune it in order to have it faster.
Then we decide to do a trace of that procedure and after use the tkprof.
How you can do this steps?


Solution 

In order to do a trace of a procedure you must have the execute privilege on that
procedure:
Then as SYS you have to do:

  grant execute on sys.dbms_system to scott;

In order to have also the CPU statistics and the elapsed_time you must have
the init parameter timed_statistics=true.

After this, you can do the trace of the procedure:

  declare
   v_sid integer;
   v_serial integer;

  begin

   select sid, serial#  
   into v_sid, v_serial
   from v$session
   where audsid = userenv ('sessionid'); 

   sys.dbms_system.set_sql_trace_in_session (v_sid, v_serial, true);

   pr_pippo;
                      
   sys.dbms_system.set_sql_trace_in_session (v_sid, v_serial, false);

  end;

So Oracle create the trace file (e.g., called ora00696.trc) on the user_dump_dest.
In the end, from the command line you launch the tkprof in order to understand easily
the trace file. 
Here an example:

  tkprof ora00696.trc sys=no sort=execu output=pr_pippo_execu.log

The result of this example is:
 - you don’t have the recursive SQL (sys=no)
 - ordered by the number of data blocks read from SGA to execute the statement
  (sort=execu)
 - saved in the file pr_pippo_execu.log


January 2007 - DOS commands & DB Administration

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


We have an Oracle db on a Windows machine not in cluster.
We want to create a batch file that every month do these:

- rename the alert log and zip it (i.e. from alert_orcl10.log to alert_20070119_01525.zip)
- compress the trace files contained in bdump to a unique zip
- compress the trace files contained in udump to a unique zip
- rename the listener.log and zip it (without stopping the listener service)

Warning: ALL these operations can happen while the users are working on the db.
This exercise is usefull also because you can see some IMPORTANT activities in the db maintainance (in Windows and in Unix)


December 2006 - Strangenesses in the Oracle Administration

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


1° Case:
We are in Oracle 9i.
During the SGA tuning, from the dynamic views we calculate the Library Cache Hit Ratio:
the result is -1,5%.
Why the hit ratio is negative??

2° Case:
We are in Oracle 8i and we have the db in ARCHIVELOG mode.
On the db, there are constantly about 100 redo log switches a day.
The last log sequence number is 80000.
In about 100 days time, which strange thing could happen to the archived log?


Solution 

1° Case:

To calculate the library cache hit ratio :

select 100*sum(reloads)/sum(pins)
from v$librarycache;

The hit ratio can be negative because of the Oracle bug 2171066.
In our case the right value of the metric will be +1,5%.
Then if you have negative metrics, the right value is with the opposite sign.

2° Case:

You have this problem only if the log_archive_format is similar this: %%ORACLE_SID%%_%S.ARC.
To be precise, with the %S.
You don’t have this problem if log_archive_format has the s like this: %s.

The effect of that problem is on the archived log names: 
In our case, in 100 days time the log sequence number will be about 180000.
And with the log sequence number 100000, the archive will have a name similar this ORCL_00000.ARC 
(instead of ORCL_100000.ARC).
And with the log sequence number 100001, the archive will have a name similar this ORCL_00001.ARC 
(instead of ORCL_100001.ARC).
Etc…
So, if the log sequence number is longer than 5 digits, the archive log name will be truncated.

However, this is not necessarily a problem for the Backup and Restore but it’s better to take into
account of this problem (according someone, this is a real Oracle bug).
You can see details on the metalink note 125146.1.


November 2006 - PL/SQL and System Tables

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


We are in Oracle 9i.
We want to do a PLSQL procedure. When you execute that procedure, you receive (through the dbms_output) an analysis of all the tablespaces like this: 

TbsName  MB_Tot  MB_Used  %_Used  Autoext  Bl_size  Pct_Inc  Contents  Ext_Mana  Allocat_tp  Segm_sp

TEMP       1096    1096     100     YES      8192      0     TEMPORARY    LOCAL     UNIFORM   MANUAL
EXAMPLE   148,7     149    99,9     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO
LORIS_T     600     595    99,1     YES     16384      0     PERMANENT    LOCAL     UNIFORM   AUTO
SYSTEM      470     461      98     YES      8192            PERMANENT    LOCAL     SYSTEM    MANUAL
XDB        46,8      44    94,4     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO

CWMLITE      20      18    87,8     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO
TOOLS        10       6    60,6     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO
DRSYS        20      10    48,4     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO
ODM          20       9    46,6     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO
UNDOTBS    2770      70     2,5     YES      8192            UNDO         LOCAL     SYSTEM    MANUAL

MONITOR     100       0      ,1     NO       8192            PERMANENT    LOCAL     SYSTEM    AUTO
Solution

According this procedure, a tablespace is autoextensible <=> at least one of its datafiles is autoextensible : 

Create procedure pr_tbs_92 is
  
  v_cont     pls_integer := 0;

begin
  dbms_output.put_line
    (' Tablespace_Name            MB_Tot  MB_Used  %_Used  Autoext  Bl_size  Pct_Incr   Contents' ||
     '   Ext_Managem   Allocat_type  Segm_sp_manag');

  for rec in (
      select rpad(b.tablespace_name, 25) || 
             lpad(Mbytes_alloc,8) || 
             lpad(round(Mbytes_alloc-nvl(Mbytes_free,0)),8) || 
             lpad(round(((Mbytes_alloc-nvl(Mbytes_free,0))/
                                Mbytes_alloc)*100,1),8) || '     ' ||
             rpad(decode(autoext,0,'NO ', 'YES'),4) || 
             lpad(c.block_size,9)  ||
             lpad(nvl(to_char(c.pct_increase),' '),8)  || '      ' ||
             rpad(c.contents,12) || '  ' || 
             rpad(c.extent_management,13)  ||
             rpad(c.allocation_type,13) ||
             c.segment_space_management  all_columns
      from ( select sum(bytes)/1048576 Mbytes_free,
                    tablespace_name
             from  sys.dba_free_space
             group by tablespace_name 
           ) a,
           ( select sum(bytes)/1048576 Mbytes_alloc,
                    sum(decode(autoextensible,'YES',1,0))  autoext,
                    tablespace_name
             from sys.dba_data_files
             group by tablespace_name
             union all
             select sum(bytes)/1048576 Mbytes_alloc,
                    sum(decode(autoextensible,'YES',1,0))  autoext,
                    tablespace_name
             from sys.dba_temp_files
             group by tablespace_name  
           ) b,
           dba_tablespaces c
      where a.tablespace_name (+) = b.tablespace_name and
            c.tablespace_name = b.tablespace_name
      order by (Mbytes_alloc-nvl(Mbytes_free,0))/
                        Mbytes_alloc desc 
          ) loop

	v_cont := v_cont+1;        
	if mod(v_cont,5) = 1 then
	  dbms_output.new_line;
	end if;

	dbms_output.put_line(' ' || rec.all_columns);

  end loop;

end pr_tbs_92;



October 2006 - SQL and UNIX

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


We are in Oracle 9i on a Sun Solaris machine.
We want to move a datafile (belonging to the USERS tablespace) without doing an istance shutdown.
The name and the path of the datafile is '/old/dbfile1.bdf'.
And we want to move it in '/new/dbfile1.bdf'.

Which steps you have to do?
And if you wanted just rename it in dbfile2.dbf without changing location?


Solution 

1. check to have db backups (useful if you have any problems)
2. say to the db users that could not accede to some db information
3. put OFFILINE the tablespace users:
  ALTER TABLESPACE users OFFLINE;
4. move the file through the O.S. commands. In Unix:
  move '/old/dbfile1.dbf' '/new/dbfile1.dbf'
5. Oracle will have to point to the new file:
  ALTER TABLESPACE users RENAME DATAFILE '/old/dbfile1.dbf' TO '/new/dbfile1.dbf';
6. put ONLINE the tablespace users:
  ALTER TABLESPACE users ONLINE;
  
If you wanted rename the file without changing the location, you had to do the same steps.



September 2006 - SQL and SYSTEM TABLES

Level (*) : 1
Topic (*) : DBA


We want to do a query that show how many space is used in the tablespaces that we have in our instance.
This query must be suitable in Oracle 8, 8i, 9i and 10g.
For instance, the result must be like this:

TABLESPACE_NAME MB Used MB Tot Percent Used
SYSTEM 473 480 98,5
SYSAUX 231 240 96,2
EXAMPLE 77 100 77,4
UNDOTBS1 20 30 66,4
USERS 23 50 46,1

Solution 

I use the views sys.sm$ts_used and sys.sm$ts_free:

 select b.tablespace_name, round(a.bytes/1048576) "MB Used", 
	round((a.bytes+b.bytes)/1048576) "MB Tot",
	round(100*a.bytes/(a.bytes+b.bytes),1) "Percent Used"
 from sys.sm$ts_used a, sys.sm$ts_free b
 where a.tablespace_name (+) = b.tablespace_name
 order by round(100*a.bytes/(a.bytes+b.bytes),1) desc;





August 2006 - PL/SQL

Level (*) : 1
Topic (*) : Prog


We are in Oracle 8i and the optimizer is RBO.
We have a query on 4 tables (t1, t2, t3, t4) and, because of the RBO optimizer, the tables order on the "FROM clause" of the query can change the explain plan.

We want to do a little plsql program where in input you put the 4 tables name and in output (through the dbms_output) you have:
from t1, t2, t3, t4
from t1, t2, t4, t3
from t1, t3, t2, t4
from t1, t3, t4, t2
from t1, t4, t2, t3
from t1, t4, t3, t2
from t2, t1, t3, t4
from t2, t1, t4, t3
from t2, t3, t1, t4
from t2, t3, t4, t1
from t2, t4, t1, t3
from t2, t4, t3, t1
from t3, t1, t2, t4
from t3, t1, t4, t2
from t3, t2, t1, t4
from t3, t2, t4, t1
from t3, t4, t1, t2
from t3, t4, t2, t1
from t4, t1, t2, t3
from t4, t1, t3, t2
from t4, t2, t1, t3
from t4, t2, t3, t1
from t4, t3, t1, t2
from t4, t3, t2, t1
Through this output, you can prepare 24 queries and you can see which of these queries has the best explain plan (or you can see which of these queries is faster).


Solution 

I use a varray:

 declare
  type sequenzaTab is varray(10) of varchar2(80);
  elencoTab sequenzaTab:= sequenzaTab( 't1'  ,'t2'
                                      ,'t3'  ,'t4'
                                      );
 begin 
  for d in 1..elencoTab.count loop
    for c in 1..elencoTab.count loop
      for b in 1..elencoTab.count loop
        for a in 1..elencoTab.count loop
          if elencoTab(a) != elencoTab(b) and elencoTab(a) != elencoTab(c) and 
             elencoTab(b) != elencoTab(c) and elencoTab(a) != elencoTab(d) and
             elencoTab(b) != elencoTab(d) and elencoTab(c) != elencoTab(d)  then
            dbms_output.put_line ('from ' || elencoTab(d) || ', ' || elencoTab(c) || ', ' || 
				  elencoTab(b) || ', ' || elencoTab(a));
          end if;
        end loop;
      end loop;
    end loop;
  end loop;
 end;



July (2) 2006 - Number of brackets

Level (*) : 1
Topic (*) : Prog


Sometimes you do a big query and you don't know anymore if the number of the opened brackets and the number of the closed braket are the same (so you could receive an error from the query during its parsing).

You must do a little plsql program that, having in input the query to check, calculate the number of opened brackets and the number of the closed brackets on in the query.

For example, we want a result (through the dbms_output) such as this:
Opened: 6
Closed: 5


Solution 

The opened brackets has ascii 40.
The closed brackets has ascii 41.
This procedure is easily suitable to find any character (you just have to know its ascii).


declare
-- !!! IF YOU HAVE ' YOU MUST CHANGE IT IN ''
 v_ascii pls_integer := 0;
 v_aperta pls_integer := 0;
 v_chiusa pls_integer := 0;
 
 a varchar2(2000):= '
select dbms_xmlgen.getxml (
           ctx => dbms_xmlgen.newContext (
               ''select    xmlelement ("Catal", cd_catalog, 
                                     xmlForest (clingua as "Ling", 
                                                pr_item as "Item",
                                                cd_feat as "Feat")
                                     )
                from  a2''),
           tmpclob => ''select   xmlelement ("Catal", cd_catalog, 
                           xmlForest (clingua as "Ling", 
                                      pr_item as "Item",
                                      cd_feat as "Feat")
                                           )
                      from  a2''
from dual
  ';
 
begin
 for i in 1..length(a) loop
  
  v_ascii := ascii(substr(a,i,1));
  
  if v_ascii = 40 then
    v_aperta := v_aperta+1;
  elsif v_ascii = 41 then
    v_chiusa := v_chiusa+1;
  end if;
  
 end loop;
 dbms_output.put_line ('Opened: ' || v_aperta);
 dbms_output.put_line ('Closed: ' || v_chiusa);
 
end;



July 2006 - SQL and System Views

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


We are in Oracle9i (then in the db you can have more than one block size).
All the tables in your schema has the own statistics.

You must do a query in order to find the following information of those tables (not partitioned):
- table name
- number of rows
- space allocated (in KB)
- space used (in KB)
- empty space (that is space allocated = space used + empty space)
- how big is the average row (in bytes)
- the date of the last analysis

And you have to find a query with the same result but on Oracle 8i.


Solution 

I use the user_tables and the dba_tablespaces:

select a.table_name, a.num_rows "Number of Rows", 
(a.blocks+empty_blocks) *
 (select block_size/1024 from dba_tablespaces where tablespace_name = a.tablespace_name) KB_Allocated,
(a.blocks) *
 (select block_size/1024 from dba_tablespaces where tablespace_name = a.tablespace_name) "KB Used", 
 a.empty_blocks * 
 (select block_size/1024 from dba_tablespaces where tablespace_name = a.tablespace_name) "KB Empty", 
 avg_row_len "Avg_row_len",
 a.last_analyzed "Last_analyzed"
from user_tables a
where a.partitioned = 'NO';

In Oracle 8i, the subquery inside the query must be this:

 select value/1024  from v$parameter where name = 'db_block_size'



June 2006 - SQL and System Views

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


We are in Oracle8i (then we don't have the package dbms_metadata).

You have to find a SQL query that create scripts useful to re-create the views belonging to a schema.
For instance, launching this query about the schema SYS, a part of the result is this: 
create or replace view
SYS.ALL_APPLY_CONFLICT_COLUMNS as
select c.object_owner, c.object_name, c.method_name,
	 c.resolution_column, c.column_name, c.apply_database_link
  from dba_apply_conflict_columns c, all_tables o
 where c.object_owner = o.owner
   and c.object_name = o.table_name
;
You must see the result through Sql*Plus.


Solution 

In Sql*Plus in order to see 2000 characters of the LONG columns you have to launch:

 set long 2000

And this is the query:

 select 'create or replace view ' start_create
        ,a.owner || '.' || a.view_name  complete_name
        ,' as ' congiunzione
        , text
	, ';'
 from dba_views a
 where owner = '&user';



May 2006 - SQL and System Views

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


We are in Oracle9i and we are connected as SYSTEM.

You have to do a PL/SQL script that create some commands (through the dbms_output).

The execution of these commands must:
- for any user (e.g., SCOTT) create a spool file for any table that he have
- these files will contain all the rows that these tables have (every table has just few columns and few rows)
- in the file you must put just the tables data without the column names
- these files must have a name like username__tablename.LST



Solution 

set serveroutput on
declare 
  cursor cur is
    select owner, a.table_name
    from DBA_TABLES a
    where a.owner = 'SCOTT'
    order by a.table_name;
begin
  dbms_output.put_line('set lines 200');
  dbms_output.put_line('set pages 1000000');
  dbms_output.put_line('set head off'); -- so you haven’t the columns name
  -- LOOP
  for rec in cur loop
    dbms_output.put_line('SPOOL d:/temp/' || rec.owner || '__' || rec.table_name || '.LST;');
    dbms_output.put_line('SELECT * FROM ' || rec.owner || '.' || rec.table_name || ';');
    dbms_output.put_line('SPOOL off;');
  end loop;
end;

In my db, its result is this:

 set lines 200
 set pages 1000000
 set head off
 SPOOL d:/temp/SCOTT__BONUS.LST;
 SELECT * FROM SCOTT.BONUS;
 SPOOL off;
 SPOOL d:/temp/SCOTT__DEPT.LST;
 SELECT * FROM SCOTT.DEPT;
 SPOOL off;
 SPOOL d:/temp/SCOTT__EMP.LST;
 SELECT * FROM SCOTT.EMP;
 SPOOL off;
 SPOOL d:/temp/SCOTT__SALGRADE.LST;
 SELECT * FROM SCOTT.SALGRADE;
 SPOOL off;

Launching this, you will create 4 files.



April 2006 - SQL and System Views

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


We are in Oracle9i and we are connected as SYSTEM.
On the schema SCOTT we have just created the same views that are in schema LORIS.
All the LORIS and SCOTT's views read from the LORIS' tables.

You must do a query that create the SQL that SYSTEM must launch in order to give SCOTT the privileges to read all the LORIS tables that are referenced from the views.

For instance, LORIS have the SALES view and this view reference the tables CUSTOMER, ITEM, ORD and PRODUCT; then we want that the query give this result: 
grant select on LORIS.CUSTOMER to SCOTT;
grant select on LORIS.ITEM to SCOTT;
grant select on LORIS.ORD to SCOTT;
grant select on LORIS.PRODUCT to SCOTT;
Solution 

In the dba_dependencies you can see which tables are referenced from the views:

select distinct 
       'grant select on ' || b.referenced_owner || '.' || b.referenced_name ||
       ' to SCOTT;' grant_select
from  dba_views a,
      dba_dependencies b
where a.owner = 'LORIS' and
      b.owner = 'LORIS' and
      a.view_name = b.name;


March (2) 2006 - Increase the use of a narrow column

Level (*) : 3
Topic (*) : Prog


We are in Oracle9i. We have the table TAB_NUM with a numeric column that contain maximum the value 30.000.000
We know that this maximum value is not going to increase really much.

All of the values of this column must be inserted in a varchar2(5) column in the table TAB_CHAR.
This column can't be enlarged because of some applications that use it.

You must do an Oracle procedure that convert a number (maximum value: 50-60 million) in a string with 5 characters.

For example, my solution return these results: 
fz(1) -> '00001'
fz(60111000) -> 'ZSDY0'



March 2006 - PL/SQL and System Views

Level (*) : 1
Topic (*) : Prog


We are in Oracle9i without statistics on the tables.
You have to do an anonymous block that calculate how many rows are in the tables of the user that launch this block.
For instance, we want (through the dbms_output) a result like this:

Date: 05/03/2006 16:48
User Analyzed: LASSI

ITEM: 64
ORD: 21
PIPPO: more than 1000
PLAN_TABLE: 0

Tables Analyzed: 4
As you can see, the tables with more than 1000 rows must be marked with the sentence "more than 1000". You have to solve the problem reading the system views and using the "execute immediate" .


Solution 

declare

  cursor cur is
    select*
    from user_tables;
  
  rec cur%rowtype;    
  v_select varchar2(2000);
  v_count pls_integer := 0;
    
begin

  -- HEAD LINES
  dbms_output.put_line ('Date: ' || to_char(sysdate, 'dd/mm/yyyy hh24:mi'));
  dbms_output.put_line ('User Analyzed: ' || user);
  dbms_output.new_line;

  -- WE WANT TO WRITE HOW MANY TABLES WE HAVE ANALYZED, THEN I PREFER DON’T USE THE
  -- “FOR..LOOP” (PERFORMANCE REASONS)
  open cur;
  loop 
    fetch cur into rec;
    exit when cur%notfound;    
  
      v_select := 'select count(*)  from ' || rec.table_name || ' where rownum<=1001';  
      execute immediate v_select into v_count;

      if v_count>1000 then
        dbms_output.put_line (rec.table_name || ': more than 1000');
      else
        dbms_output.put_line (rec.table_name || ': ' || v_count);
      end if;
    
  end loop;
  dbms_output.new_line;
  dbms_output.put_line ('Tables Analyzed: ' || cur%rowcount);
  close cur;
  
end;





February 2006 - SQL and System Views

Level (*) : 3
Topic (*) : Prog


We have a table like this: 
create table A
( STATEMENT_ID VARCHAR2(30), PLAN_ID NUMBER, TIMESTAMP DATE, REMARKS VARCHAR2(4000), 
OPERATION VARCHAR2(30), OPTIONS VARCHAR2(255), 
OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), 
OBJECT_ALIAS VARCHAR2(65), OBJECT_INSTANCE INTEGER, 
OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER, ID INTEGER, 
PARENT_ID INTEGER, DEPTH INTEGER, POSITION INTEGER, 
COST INTEGER, CARDINALITY INTEGER, BYTES INTEGER, OTHER_TAG VARCHAR2(255), 
PARTITION_START VARCHAR2(255), PARTITION_STOP VARCHAR2(255), 
PARTITION_ID INTEGER, OTHER LONG, DISTRIBUTION VARCHAR2(30), CPU_COST INTEGER );
We are doing a procedure that insert records in that table.
We do it through a SQL that begin like this:

insert into A
( STATEMENT_ID ,PLAN_ID
,TIMESTAMP ,REMARKS
,OPERATION ,OPTIONS
,OBJECT_NODE ,OBJECT_OWNER
,OBJECT_NAME ,OBJECT_ALIAS
,OBJECT_INSTANCE ,OBJECT_TYPE
,OPTIMIZER ,SEARCH_COLUMNS
,ID ,PARENT_ID
,DEPTH ,POSITION
,COST ,CARDINALITY
,BYTES ,OTHER_TAG
,PARTITION_START ,PARTITION_STOP
,PARTITION_ID ,OTHER
,DISTRIBUTION ,CPU_COST
)
values (

An SQL wrote like this is useful because make the code easily readable.
But in order to write an SQL long like this, you spend a lot of time and you could forget to write some columns.
Then you must write a template that, having in input the tablename that you want to fill up, create an SQL INSERT as that one above (included the rows "insert..." and "values (".
That template must be done in SQL.




January 2006 - Duplicate the users

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


We have the instance dblor and from it, we want to take information about some users in order to recreate them in one another instance.
In order to do it, we use a PL/SQL anonymous block that will write, through dbms_output, all the SQL commands that after you can launch.

For example, launching the block in my db, I obtain:
-- *************************************************************
-- Purpose : CREATE THE SCRIPTS OF SOME EXISTING USERS IN THE INSTANCE
-- Date : 28/01/2006 13:28
-- Instance : dblor
--
*************************************************************

-- USERS INVOLVED --------------------------------------------
These are the users that meet the condition put in the main cursor:
1 LORIS
2 LORIS2

-- SCRIPTS FOR THE USER CREATION -----------------------------
create user LORIS identified by values '2570FE42941FF4FF'
default tablespace USERS temporary tablespace TEMP
profile DEFAULT
;
create user LORIS2 identified by values
'F894844C34402B67'
default tablespace SYSTEM temporary tablespace TEMP
profile DEFAULT
;

-- SYSTEM PRIVILEGES --------------------------------------------
These are the system privileges directly assigned to the users:
grant UNLIMITED TABLESPACE to LORIS;
grant UNLIMITED TABLESPACE to LORIS2;

-- ASSIGN ROLE PRIVILEGES ---------------------------------------
Attention: I don't display the object privileges not assigned through roles!
grant DBA to LORIS;
grant LORIS_USERS to LORIS;
grant DBA to LORIS2;
grant LORIS_USERS to LORIS2;
grant AQ_USER_ROLE to LORIS2;

-- ASSIGN DEFAULT ROLE ------------------------------------------
Here I get disabled some roles assigned previously:
alter user LORIS2 default role all except
DBA
,AQ_USER_ROLE
;
Details:
In the program you have to declare:
- the username if you want to get information about one user; or
- the role belonging to many users if u want to get information about the role

In the example above, I have the role LORIS_USERS that have associated the users LORIS and LORIS2.
The procedure is suitable also in old Oracle releases such as Oracle 8.0.
The new users that you'll create must have the same password of the old users (even if you don't know that password).

In the example below, I launch the procedure only for the user LORIS: 
-- *************************************************************
-- Purpose : CREATE THE SCRIPTS OF SOME EXISTING USERS IN THE INSTANCE
-- Date : 28/01/2006 14:12
-- Instance : dblor
-- *************************************************************

-- USERS INVOLVED --------------------------------------------
These are the users that meet the condition put in the main cursor:
1 LORIS

-- SCRIPTS FOR THE USER CREATION -----------------------------
create user LORIS identified by values '2570FE42941FF4FF'
default tablespace USERS temporary tablespace TEMP
profile DEFAULT
;

-- SYSTEM PRIVILEGES --------------------------------------------
These are the system privileges assigned directly to the users:

grant UNLIMITED TABLESPACE to LORIS;

-- ASSIGN ROLE PRIVILEGES ---------------------------------------
Attention: I don't display the object privileges not assigned through roles!

grant DBA to LORIS;
grant LORIS_USERS to LORIS;

-- ASSIGN DEFAULT ROLE ------------------------------------------
Here I get disabled some roles assigned previously:
Note: The part where the roles are get disabled is probably the most difficult.
Especially if the roles to get disabled are more than one.



December (2) 2005 - SQL and Features Oracle9i

Level (*) : 3
Topic (*) : Prog


We are in Oracle9i and we have a table like this: 
create table telefoni
(
cod_cliente varchar2(8),
contatore number,
tel varchar2(12)
);

ALTER TABLE telefoni
ADD CONSTRAINT telefoni_pk
PRIMARY KEY (cod_cliente, contatore);
We insert some rows in this table: 
insert into telefoni values ('AAAAA', 1, '01/11111');
insert into telefoni values ('AAAAA', 2, '01/22222');
insert into telefoni values ('AAAAA', 3, '01/33333');
insert into telefoni values ('BBBBB', 1, '01/44444');
insert into telefoni values ('CCCCC', 1, '01/55555');
insert into telefoni values ('CCCCC', 2, '01/66666');


We want a query that, given a cod_cliente, show in a row all his telephones (separated by commas).


For instance, if we want to know the telephones of the cod_cliente='AAAAA', then the query result must be this:

COD_CLIENTE TELEFONO
----------------- ----------------------
AAAAA 01/11111, 01/22222, 01/33333

For the cod_cliente BBBBB we want:

COD_CLIENTE TELEFONO
----------------- ----------------------
BBBBB 01/44444





December 2005 - SQL and Oracle9i Features

Level (*) : 3
Topic (*) : Prog


We are in Oracle8.0 ed we have a table like this: 
create table a
(
day varchar2(10),
kg number
);
insert into a values ('mon', 1000);
insert into a values ('tue', 2000);
insert into a values ('wed', 3000);
insert into a values ('thu', 4000);
insert into a values ('fri', 5000);
insert into a values ('sat', 6000);
insert into a values ('sun', 7000);
Then this table has always 7 rows.
We want a query that show the 7 rows in a unique row and in 7 columns, then:

Kg mon Kg tue Kg wed Kg thu Kg fri Kg sat Kg sun
------- ------- ------- ------- ------- ------- -------
1000 2000 3000 4000 5000 6000 7000


Now we are in Oracle9 (then you can use the scalar subqueries) and we have a clob column: 
create table a
(
day varchar2(10),
descr clob
);

insert into a values ('mon', 'on monday ...');
insert into a values ('tue', 'on tuesday ...');
insert into a values ('wed', 'on wednesday ...');
insert into a values ('thu', 'on thursday ...');
insert into a values ('fri', 'on friday ...');
insert into a values ('sat', 'on saturday ...');
insert into a values ('sun', 'on sunday ...');
We want to have a result like this:


descr mon descr tue descr wed descr thu descr fri descr sat descr sun
---------- ---------- ---------- ---------- ---------- ---------- ----------
on monday ... on tuesday ... on wednesday ... on thursday ... on friday ... on saturday ... on sunday ...


In the solution of this second problem you don't have to use to_char. You could use the "to_char" just if you are sure that the column descr can be converted in char; then you could only if in the column there aren't string very long.




November 2005 - PL/SQL and Administration

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


We want to do a job that, every night at 21.15, kills all the Apache sessions connected to the database.
How you can do it?

Some difficulties of this exercise are:

- the job must start always at 21.15: if you don't pay attention, the job could start later (e.g., the first time it starts at 21.15, after one week it starts at 21.18, after 2 weeks it starts at 21.20, etc...)
- be sure that you kill also possible parallel processes started from the Apache sessions


We suppose that the Oracle user that do it has the right privileges (e.g., "alter system") and the Apache doesn't start automatically the process killed.


Solution 
I suggest to create a table where you can trace what happen:

 create table kill_apache
 (
 cod_session  varchar2(20),
 spid      number(10),
 osuser    varchar2(40),
 time	  date,
 result     varchar2(500)    -- here you put ‘ok’ or the error description
 );

Now you can create a procedure like this:

create or replace procedure pr_kill_apache is

  v_err varchar2(500);
  
  cursor cur is 
    select se.sid || ', ' || se.serial#  sessione,
           pr.spid,
           se.osuser
    from v$session se,
         v$process pr
    where se.paddr = pr.addr  and
          se.module = 'Apache.exe';   -- use "module" instead of "program" to be sure that you take
					-- also the parallel child processes
begin

  -- INSERT A ROW IN kill_apache
  insert into kill_apache 
  values ( 'PROCEDURE LAUNCHED', null, null, 
           sysdate, null);

  commit;
   
  -- KILL THE Apache PROCESSES
  for rec in cur loop
    begin
    
      execute immediate 'alter system kill session ''' || rec.sessione || '''';
      
      insert into kill_apache 
      values ( rec.sessione, rec.spid, rec.osuser,
               sysdate, 'OK');
      
      commit;
      
    exception
      when others then
        v_err := sqlerrm;
        
        insert into kill_apache 
        values ( rec.sessione, rec.spid, rec.osuser,
               sysdate, v_err);
               
        commit;
      
    end;

  end loop;
  commit;

end;


Create the job:

declare
  job_number number := null;
begin
 dbms_job.submit
 (job=>job_number,
  what=>'PR_KILL_APACHE;',
  next_date => to_date('26-10-2005 21.15.00','dd-mm-yyyy hh24.mi.ss'), -- next_date must be adjusted
  interval=>'trunc(sysdate)+1+21/24+15/(24*60)');	-- in this way it starts always at 21.15
 commit;
end;





October 2005 - SQL

Level (*) : 3
Topic (*) : Prog


We are in a typical datawarehouse. In the normalized part of the db, we have a fact table like this: 
CREATE TABLE sales_ym
 ( date_ym varchar2(6), -- format ‘yyyymm’
 cod_product varchar2(5),
 cod_province number(3),
 kg_sold number(4),
 takings_euro number(6)
 );

ALTER TABLE sales_ym
ADD CONSTRAINT sales_ym_pk PRIMARY KEY (date_ym, cod_product, cod_province);
This table has sales data of the company in last 2 years. It has about 50.000.000 rows (24 months for 20.000 distinct products for 100 provinces).

In the denormalized area of the db, often there are queries that compare the data of the corrent month and the same month of the previous year (e.g. the sale of august 2005 compared with the sale of august 2004).

In order to do QUICKLY this comparison, you must load this table: 
CREATE TABLE sales_m
( month varchar2(2), -- format ‘mm’
  cod_product varchar2(5),
  cod_province number(3),
  kg_sold_cy number, -- sold of the Current Year-month
  takings_euro_cy number,
  kg_sold_py number(4), -- sold of Previous Year-month
  takings_euro_py number(6)
);

ALTER TABLE sales_m 

ADD CONSTRAINT sales_m_pk PRIMARY KEY (month, cod_product, cod_province);
You have to create a SQL INSERT that insert data from sales_ym to sales_m.


Example:

We insert some rows in sales_ym: 
insert into sales_ym values ('200507', 'A1111', 1, 111, 11000);
insert into sales_ym values ('200507', 'A2222', 1, 222, 22000);
insert into sales_ym values ('200407', 'A1111', 1, 333, 33000);
insert into sales_ym values ('200406', 'A1111', 1, 444, 44000);
insert into sales_ym values ('200406', 'A1111', 2, 555, 55000);
insert into sales_ym values ('200411', 'A1111', 2, 666, 66000);

After the INSERT, in the sales_m you must have data like this:

MONTH COD_PROD COD_PROV KG_SOLD_CY TAKINGS_CY KG_SOLD_PY TAKINGS_PY
06 A1111 1 0 0 444 44000
06 A1111 2 0 0 555 55000
07 A1111 1 111 11000 333 33000
07 A2222 1 222 22000 0 0
11 A1111 2 0 0 666 66000


!!! Attention: to solve this problem, you must find a query that read JUST ONE TIME the table sales_ym.




September 2005 - SQL, SYSTEM VIEW and UNIX

Level (*) : 1
Topic (*) : DBA


We have the client-machine OMILA76 connected to the db.

On that machine there are many sessions and we want to kill the unique session that do a query on the table PIPPO.

Then, how we can find that session? and how we can kill it from SQL*Plus?

Sometimes, Oracle is not able to kill the session then, after 60 seconds, its result is the message "ORA-00031 session marked for kill".

In this case, you have to find the spid of the process, so you can kill it directly from the O.S. (we are in Unix).

Then, how you can find the spid? and how you can kill it from O.S?


Solution 
You find the session that you want to kill through the following query (the v$session shows the 
sessions, the v$sql shows the sql that the sessions are executing):

 select distinct logon_time,  a.sid,  serial#,  a.action,  status,  
	b.sql_text,  machine,  program,  osuser,  username
 from v$session a, v$sql b
 where sql_hash_value = hash_value (+) –with the  outer-join, you can find also the no-log processes 
   and machine like 'OMILA76'
   and b.sql_text like '%PIPPO%';

Suppose that the result is SID = 50 and SERIAL# = 12345.

So, from SQL*Plus you do this command:
	alter system kill session ’50, 12345’;

If Oracle is not able to stop the session, you find the process on the server in this way:

 select p.spid 
 from v$process p, v$session s
 where s.paddr = p.addr  and  sid = 50;

Suppose that the result is 22005.
Then, you connect the user oracle in the Unix machine where you have the db, and kill the process:
	kill -9 22005





August 2005 - PL/SQL and SYSTEM VIEWS

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


You must do a PL/SQL block that, given the name and the owner of a Primary Key (or Unique), do the scripts useful to create all the foreign keys that reference it.

- this PLSQL block must be suitable also for the Primary keys do on more than one column
- in the scripts you must write also if these foreign keys have the "on delete cascade" option or if they are "deferrable"


Example:

We have the table LASSI.WB_RT_AUD and on that table there is a Primary key called PK_RTBB.

You must do a PLSQL block that, through the dbms_output, create scripts like these: 
alter table LASSI.WA_RT_AUD_AMOUNTS
add constraint FK_RTM_RTBB foreign key (RTBB_IID)
references LASSI.WB_RT_AUD (RTA_IID)
;

alter table LASSI.WA_RT_ERR
add constraint FK_RTE_RTBB foreign key (RTBB_IID)
references LASSI.WB_RT_AUD (RTA_IID)
deferrable
;

alter table LASSI.WA_RT_ERR_SOU
add constraint FK_RTS_RTBB foreign key (RTBB_IID)
references LASSI.WB_RT_AUD (RTA_IID) on delete cascade
;



July 2005 - SQL

Level (*) : 3
Topic (*) : Prog


We are in Oracle 8i and we have the table A like this: 
CREATE TABLE A
 (aa NUMBER, 
 aaa CHAR(1));
This table has these rows: 
insert into a values (10,'a');
insert into a values (10,'b');
insert into a values (20,'c'); 
insert into a values (30,'e'); 
insert into a values (30,'d'); 
insert into a values (40,'f'); 
insert into a values (40,'g'); 
insert into a values (50,'i'); 
insert into a values (50,'h'); 
insert into a values (60,'z');
insert into a values (60,'y');
We know that for every distinct value in aa, we have maximum 2 rows.

Now we suppose the result of SELECT* FROM A;
is exactly this:

10 a
10 b
20 c
30 e
30 d
40 f
40 g
50 i
50 h
60 z
60 y

(I remind you that not always the rows are shown in the same order than you have used to insert them)

I want a query that group by aa giving this result on 2 columns:

10 a, b
20 c
30 e, d
40 f, g
50 h, i
60 y, z

That is, for every value of the column aa, group by the values of aaa in ascendent alphabetic order and separating them with a comma.

And I want another query that group like this:

10 a, b
20 c
30 e, d
40 f, g
50 i, h
60 z, y


That is, I want to have the second column in the same order than you read the table; that is with the first "i" before than "h" and the "z" before than "y".

Obviously, in the queries you can't write something about the real data in the table; that is it must be a solution always suitable.

(you can do them also without using the analytic functions).



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

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