Apply DDL in a replcation environment - not working as expected in 8i

1. The structure of table t3 before applying the DDL

repadmin@TESTDBA> desc myowner.t3

Name Null? Type
---- ----- -------
A NOT NULL NUMBER
B NOT NULL NUMBER


2. Apply DDL through dbms_repcat.execute_ddl

repadmin@TESTDBA> @ddl.sql
PL/SQL procedure successfully completed.

repadmin@TESTDBA> ho more ddl.sql
begin
dbms_repcat.execute_ddl (
gname => 'RG_MYOWNER',
ddl_text => 'alter table myowner.t3 add (c date) '
);
end;
/

Note: before column b, c are added through the above API, there is only
column A in the table t3, which is a replicated table.

3. The structure of table t3 after applying the DDL
repadmin@TESTDBA> desc myowner.t3;

Name Null? Type
---- ----- ----

A NOT NULL NUMBER
B NOT NULL NUMBER
C DATE

4.Adding columns DDL in TESTDBA are replicated in TESTDBB as expected

repadmin@TESTDBA> desc myowner.t3@genqb
Name Null? Type
--- ---- -----

A NOT NULL NUMBER
B NOT NULL NUMBER
C DATE

5. DML is only good for the colum A and doesn't work for column B and C

repadmin@TESTDBA> insert into myowner.t3 values(1,2, sysdate);

1 row created.

repadmin@TESTDBA> commit;

Commit complete.

repadmin@TESTDBA> select * from myowner.t3;

A B C
---------- ---------- --------------------
1 2 20-MAR-2007 13:58:04

repadmin@TESTDBA> select * from myowner.t3@testdbb

A B C
---------- ---------- --------------------
1 0


repadmin@TESTDBA> insert into myowner.t3 values(2,3, sysdate);

1 row created.

repadmin@TESTDBA> commit;

Commit complete.

repadmin@TESTDBA> select * from myowner.t3
2 /

A B C
---------- ---------- --------------------
1 2 20-MAR-2007 13:58:04
2 3 20-MAR-2007 14:06:29

repadmin@TESTDBA> select * from myowner.t3@testdbb
2 /

A B C
--------- ---------- --------------------
1 0
2 0

Using %TYPE

This sounds strange.

I encountered complilaton error for a pacakge in 8.1.7

I suspect that %TYPE may have problem with the the last column of a table, and

this column is created through the 'alter table add ...' syntax. I added a dummy

column to the table, then re-complile, the package became valid. Then I drop the

dummy column, and recompile all invalid packages, the package and its body now becomes valid.

Sort in memory vs in disk

- We can use the following SQL to find out the ratio of sort in disk and in memory:

system@TESTDB> SELECT d.value "Disk", m.value "Memory",
(d.value/m.value)*100 "Ratio"
2 FROM v$sysstat m, v$sysstat d
3 WHERE m.name = 'sorts (memory)'
4 AND d.name = 'sorts (disk)';

Disk Memory Ratio
---------- ---------- ----------
6795 14302589 .047508881

- Issue a query:

system@TESTDB> select distinct dloc_id as entity_id
2 , 'DLC' as entity_type
3 , overline_amt
4 from cf_dloc_overline_detail_vw
5 where overline_amt > 0
6 union all
7 select distinct global_credit_line_id as entity_id
8 , 'GBL' as entity_type
9 , overline_amt
10 from cf_glbl_overline_detail_vw
11 where overline_amt > 0;

ENTITY_ID ENT OVERLINE_AMT
---------- --- ------------
27149 GBL 11095.46
27153 GBL 144883.33
27161 GBL 126123.53
27165 GBL 31763.67
27245 GBL 791812.01
27283 GBL 4236230.25
27287 GBL 287088.26
27341 GBL 311599.72
27351 GBL 197678.27
27364 GBL 61064.84
27385 GBL 53273.65
27405 GBL 6681.74
27422 GBL 201599.51
27441 GBL 47826.68
27477 GBL 311296.57
27747 GBL 239035.16
27813 GBL 2360.55
27833 GBL 1136063
27834 GBL 168946.11
27872 GBL 2197805.51
27900 GBL 178448.4
27903 GBL 93843.26
27906 GBL 3654392.11
27947 GBL 21221.42
27964 GBL 89876.4
27974 GBL 27472
27975 GBL 140322.64
28005 GBL 613007.23
28081 GBL 1051201.35
28084 GBL 1629683.02
28091 GBL 2884553.45
28186 GBL 1302735.85
28255 GBL 704366.3
28295 GBL 12101.04
28356 GBL 201794.01
28413 GBL 21836.82
28464 GBL 11013.7
28479 GBL 413712.05
28501 GBL 349044.59
28506 GBL 52859.13
28533 GBL 11204.95
28549 GBL 268773
28571 GBL 281151.84
28610 GBL 199603.15
28662 GBL 504578.93
28675 GBL 141756
28691 GBL 81749.29
28705 GBL 36518
28730 GBL 69078.95
28775 GBL 63985.25
28780 GBL 387.08
28799 GBL 30246.34

52 rows selected.

- Check the ratio again:

system@TESTDB> SELECT d.value "Disk", m.value "Memory",
(d.value/m.value)*100 "Ratio"
2 FROM v$sysstat m, v$sysstat d
3 WHERE m.name = 'sorts (memory)'
4 AND d.name = 'sorts (disk)';

Disk Memory Ratio
---------- ---------- ----------
6795 14303459 .047505991

FW: locked pkg in gen2

The contents are removed. 03-26-09