Month: May 2007

Multirow Inserts

While attempting to insert several rows into a table in our Oracle database, a colleague dutifully copied the exact ANSI/ISO SQL standard syntax for his purposes. Guess what happened?INSERT INTO table (column1, column2)VALUES (value1, value2), (value1…

What Makes a Great Oracle Blog?

Along the side of my page, you’ll see my favourite Oracle blogs listed. I carefully maintain this list of fellow enthusiasts whose opinions and insights I most especially want to follow among the seemingly hundreds of Oracle blogs that are out there. …

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

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.

ANSI Joins

Like most of us, I still join tables in my SQL queries the old-school way. Simply put:SELECT whateverFROM table1 t1, table2 t2WHERE t1.id = t2.idAND t1.value > 10;But increasingly often I run into people who use ANSI joins instead. They were either introduced to SQL with Oracle 9 (or Sybase 12, etc), and were taught to use the SQL standard way, or else they made the conversion at some point in

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569