Friday, May 18, 2007

What is Timeout?

The problem with semi documented APIs like DBMS_SPACE is that some of the subprograms might not work as you might expect them to.
For example the object_space_usage procedure has a parameter called timeout_value which I would assume to mean the amount of time after the procedure will abort if it cannot complete its space calculations. But this does not work as I would expect it to

SQL> set timing on
SQL> set serveroutput on

SQL> declare
v_space_used number;
v_space_allocated number;
begin
dbms_space.object_space_usage('SCOTT','EMP','TABLE',0,v_space_used, v_space_allocated,'',TRUE,5);
dbms_output.put_line('SPACE USED = '||v_space_used);
dbms_output.put_line('SPACE ALLOCATED = '||v_space_allocated);
end;
/

SPACE USED = 0
SPACE ALLOCATED = 68034756608

PL/SQL procedure successfully completed.

Elapsed: 00:00:36.12

So even if I specify a timeout value of 5 the procedure completes in 36 seconds.

This behaviour reproduces on 10.1.0.5 and 10.2.0.3.