Event trigger after logon

This trigger was necessary to execute 1-2 remote selects after login, because all the selects trough db links were working only the third time it was issued, a very strange behaviour. I had to create two similar triggers and they were very effective until we found the root cause.

CREATE OR REPLACE TRIGGER toni_bdn AFTER LOGON ON database
DECLARE
v_user VARCHAR2(30); x number;
a integer; b integer; c integer; a1 integer; a2 integer; a3 integer;
b1 integer; b2 integer; b3 integer;
BEGIN
if sys_context('userenv','session_user') in ('SYSTEM','SYS','BDNOWN01','BDNUSR01') then
a:=dbms_sql.open_cursor;
dbms_sql.parse(a, 'select count(1) from midual@BDN_DL_ROMEGA', dbms_sql.native);
execute immediate 'select count(1) into x from midual@BDN_DL_ROMEGA';
b:=dbms_sql.execute(a);
b1:=dbms_sql.execute(c);
b2:=dbms_sql.execute(a);
b3:=dbms_sql.execute(a);
b:=dbms_sql.execute(a);
dbms_sql.close_cursor(a);
end if;
END;
/

Creating objects without STORAGE clauses

In some teams it's usual not to indicate neither storage clauses nor tablespace clauses, so I've gotten some interest in studying the consecuences. Here are the results with a single table in Oracle 10GR2:

I create a single table with no clauses:

create table sense_clausules (col varchar(2000)) tablespace PL_DATOS;

col segment_name for a30
col INITIAL_EXTENT for 999,999
col next_extent, bytes for 999,999
select segment_name, INITIAL_EXTENT, next_extent, bytes
from dba_segments
where segment_name=upper('sense_clausules')
order by 1;


SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 65536

Next I load a lot of rows:


set serveroutput on
declare i number;
j number;
begin
for j in 1..100000 loop
for i in 1..1000 loop
insert into sense_clausules values ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') nologging;
end loop;
commit;
dbms_output.put_line(j*1000);
end loop;
end;
/


SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 75.497.472

select count(1) from sense_clausules;

COUNT(1)
------------
1.998.010

select sum(bytes)
from dba_extents
where segment_name=upper('sense_clausules')
order by 1;

SUM(BYTES)
------------
148.897.792

And now the quantity and size of the extensions after the load:

col segment_name format a30
select segment_name,bytes, count(*)
from dba_extents
where segment_name=upper('sense_clausules')
group by segment_name,bytes
order by 2,3;

SEGMENT_NAME BYTES COUNT(*)
------------------------------ ------------ ------------
SENSE_CLAUSULES 65536 16
SENSE_CLAUSULES 1048576 63
SENSE_CLAUSULES 6291456 1
SENSE_CLAUSULES 8388608 9

4 files seleccionades.

Now a more heavy load and observe:


COUNT(1)
------------
100.000.000

SEGMENT_NAME BYTES COUNT(*)
------------------------------ ------------ ------------
SENSE_CLAUSULES 65536 16
SENSE_CLAUSULES 1048576 63
SENSE_CLAUSULES 8388608 120
SENSE_CLAUSULES 35651584 1
SENSE_CLAUSULES 67108864 94

5 files seleccionades.

294 extensions created for 100 milions rows.

SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 148.897.792


drop table sense_clausules cascade constraints;

I, personally, prefer to indicate the INITIAL and NEXT clauses in the way the capacity plan indicates.