Tuesday, August 31, 2010

ORA-30009: Not enough memory for CONNECT BY operation and lotto

SQL> @lotto
SQL> select winnend_getal
2 from (
3 select winnend_getal, count(*)
4 from (
5 select round(DBMS_RANDOM.VALUE(1,45)) winnend_getal
6 from
7 ( select level n from dual
8 connect by level <= &aantal_nummers )
9 )
10 group by winnend_getal order by count(*) desc
11 )
12 where rownum <=6
13 /
Enter value for aantal_nummers: 1000000
old 8: connect by level <= &aantal_nummers )
new 8: connect by level <= 1000000 )
select round(DBMS_RANDOM.VALUE(1,45)) winnend_getal
*
ERROR at line 5:
ORA-30009: Not enough memory for CONNECT BY operation


Elapsed: 00:00:06.32
SQL> select winnend_getal
2 from (
3 select winnend_getal, count(*)
4 from (
5 select round(DBMS_RANDOM.VALUE(1,45)) winnend_getal
6 from
7 (
8 with n(n) as ( select 0
9 from dual
10 union all
11 select n+1 from n
12 where n<=&aantal_nummers
13 )
14 select * from n
15 )
16 )
17 group by winnend_getal order by count(*) desc
18 )
19 where rownum <= 6
20 /
Enter value for aantal_nummers: 1000000
old 12: where n<=&aantal_nummers
new 12: where n<=1000000

WINNEND_GETAL
-------------
35
15
12
9
41
11

6 rows selected.

Elapsed: 00:00:46.28
SQL>

I found this on asktom from a reply of user Sokrates ....

BTW Hope someone will win the lotto ....

Monday, August 30, 2010

filsystem_ioORA-56708

In DBCONSOLE I received the following message on a hpux 11.31 machine while trying to run the io calibrate feature of 11g.
DBCONSOLE> Information

The following Initialization parameters need to be set to the specified
value in order to run the I/O calibration tool successfully:
filesystemio_options - ASYNCH or SETALL
This error is a bit misleading...because filesystemio_options is already set, the cause is that the async io is disabled at the operating level......

cat calibrate_io.sql
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (8, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
/

SQL> @"calibrate_io.sql"
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 456
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1285
ORA-06512: at line 7

SQL> show parameter asyn

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
disk_asynch_io boolean
TRUE
tape_asynch_io boolean
TRUE
SQL> show parameter filesys

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
filesystemio_options string
SETALL
SQL> select name,asynch_io from v$datafile f,v$iostat_file i
where f.file#=i.file_no
and (filetype_name='Data File' or filetype_name='Temp File');

NAME ASYNCH_IO
------------------------------ ---------------------------

/oracle/dikpater/db/apps_st/da ASYNC_OFF
ta11g/DIKPATEROCP/datafile/o1_
mf_undotbs1_6677o0hr_.dbf

Tuesday, August 24, 2010

ora-27492

job_queue_processes checked it was 1000 ;

exec dbms_ijob.set_enabled(true) ;

Solved the problem.

11gR2 SP2-1503: Unable to initialize Oracle call interface

$ which sqlplus
/oracle/system1/db/tech_st/11.2.0/bin/sqlplus
ibes01@system1: sqlplus
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
****************************************************************************
The cause is that the variable ORA_TZFILE was pointing to a 10g environment.
****************************************************************************
$ set | grep TZ
ORA_TZFILE=/oracle/system1/db/tech_st/10.2.0/oracore/zoneinfo/timezlrg.dat
TZ=MET-1METDST

$ unset ORA_TZFILE
$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 24 08:58:55 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name:

Monday, August 23, 2010

imp hang import hang 25475

If your import doesn't continues
and current statement is :
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
then you might be using the wrong client s/w.
In our case we used 9206 instead of 9208. The import hangs.
Thanks to Frank van Bortel who run into this case.