updatable views and instead of triggers

I am data-modeling those days.

Each table has it own description table (yes, it is Java). To keep it simple, FRUIT[id,description] and MEAT[id,description] (35 of them right now). There must be one table which contains all descriptions in it GLOBAL[type,id,description]. I wish to preserve the data quality. Both the little (FRUIT,MEAT) and the big (GLOBAL) must be selectable and editable. Someone, the little ones are views of the big one, or the big one is a view of the little ones.

Plan 1: there is one big table and many small views


create table global(
  type varchar2(15),
  id number,
  description varchar2(10),
  primary key(type,id));
create or replace view fruit as 
  select id,description 
  from global 
  where type='fruit';
create or replace view meat as 
  select id,description 
  from global 
  where type='meat';

plan 2: one view which union all all little tables


create table fruit(
  id number,
  description varchar2(10), 
  primary key(id));
create table meat(
  id number,
  description varchar2(10), 
  primary key(id));
create or replace view global as 
  select 'fruit' type,id,description 
  from fruit 
  union all
  select 'meat',id,description 
  from meat;

Is this fine? As long as I only select rows, both are possible. However, a select * from fruit will take much longer if fruit is a view and the rows are retrieved from a huge table, probably per Index scan.

In the solution 2, I keep small lookup tables, and it should be faster to retrieve data from those. However, the UNION ALL view is not updatable.

In solution 1, the small views seem updatable, but it will not work.

Let’s see


SQL> create table global(
  2    type varchar2(15),
  3    id number,
  4    description varchar2(10),
  5    primary key(type,id));

Table created.

SQL> create or replace view fruit as
  2    select id,
  3      description
  4    from global
  5    where type='fruit';

View created.

SQL> insert into fruit values(1,'Mango');
insert into fruit values(1,'Mango')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("AUDBA"."GLOBAL"."TYPE")

I cannot tell the view to default the type to fruit when inserting in fruit

In plan 2, the view is not updatable at all


SQL> create table fruit(
  2    id number,
  3    description varchar2(10),
  4    primary key(id));

Table created.

SQL> create table meat(
  2    id number,
  3    description varchar2(10),
  4    primary key(id));

Table created.

SQL> create or replace view global as
  2    select 'fruit' type,id,description
  3    from fruit
  4    union all
  5    select 'meat',id,description
  6    from meat;

View created.

SQL> insert into global values(
  2    'meat',1,'beef');
insert into global values(
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

So the solution is called INSTEAD OF trigger. An instead of trigger is a very special trigger specific to views. It will make all views updatable, how magic!

As a DBA, I am not very confident with triggers, because the developer is doing the consistency check, no more the Oracle Engine. It will therefore be much less performant and it may contain errors. Here I write a trigger for each case. They certainly have bugs. I am myself not convinced by using triggers at all. Those kind of DML home-made rewrites are just too dangerous, but what else can I do…

OK, plan 1: one instead of trigger for every single view


create or replace trigger fruit_insteadof
  instead of insert or update or delete on fruit
begin
  if inserting then
    insert into global 
      values('fruit',:new.id,:new.description);
  elsif deleting then
    delete from global 
    where type='fruit' 
      and id=:old.id 
      and description=:old.description;
  elsif updating then
    update global 
      set type='fruit', 
        id=:new.id,
        description=:new.description
     where type='fruit' 
       and id=:old.id 
       and description=:old.description;
  end if;
end;
/

Plan 2: One instead of trigger for the global view

 create or replace trigger global_insteadof
  instead of insert or update or delete on global
begin
  if inserting then
    if (:new.type='fruit') then
      insert into fruit values(
        :new.id,:new.description); 
    elsif (:new.type='meat') then
      insert into meat values(
        :new.id,:new.description);
    else
      raise_application_error(-20001, 
        'Trigger cannot insert into '||:new.type); 
  end if;
  elsif deleting then
    if (:old.type='fruit') then
      delete fruit where 
        id=:old.id 
        and description=:old.description;
    elsif (:old.type='meat') then
      delete meat where 
        id=:old.id 
        and description=:old.description;
    else
      raise_application_error(-20001, 
        'Trigger cannot delete from '
          || :old.type); 
    end if;
  elsif updating then
    if (:new.type!=:old.type) then
      delete from global 
      where type=:old.type 
        and id=:old.id 
        and description=:old.description;
      insert into global values(
        :new.type,:new.id,:new.description);
    elsif (:new.type='fruit') then
      update fruit set  
        id=:new.id,
        description=:new.description
      where id=:old.id 
        and description=:old.description;
    elsif (:new.type='meat') then
      update meat set  
        id=:new.id,
        description=:new.description
      where id=:old.id 
        and description=:old.description;
    else
      raise_application_error(-20001, 
        'Trigger cannot update '||:old.type); 
    end if;
  end if;
end;

I am not at all convinced about the data consistency in such an approach.

Well, I will try to push a solution with no GLOBAL table. We probably do not need it.

How do i store the counts of all tables …

How do i store the counts of all tables …

My answer to the question above using dbms_xmlgen

SQL> select
  2    table_name,
  3    to_number(
  4      extractvalue(
  5        xmltype(
  6 dbms_xmlgen.getxml('select count(*) c from '||table_name))
  7        ,'/ROWSET/ROW/C')) count
  8  from user_tables;

TABLE_NAME                      COUNT
------------------------------ ------
DEPT                                4
EMP                                14
BONUS                               0
SALGRADE                            5

CPU2007Apr

I just downloaded and installed the Critical Patch Update April 2007

$ lsnrctl stop 
...
The command completed successfully
$ sqlplus / as sysdba
SQL> shutdown immediate
...
ORACLE instance shut down.
SQL> quit
$ cd /tmp/5901891
$ ORACLE_HOME/OPatch/opatch apply
... Finish at Wed Apr 18 10:28:17 CEST 2007
$ lsnrctl start
...
The command completed successfully
$ sqlplus / as sysdba
SQL> startup
...
Database opened.
SQL> @?/cpu/CPUApr2007/catcpu
SQL> @?/rdbms/admin/utlrp

The logfile of opatch is $ORACLE_HOME/cfgtoollogs/opatch/opatchdate.log and the logfile of the catcpu is APPLY_sid_date.log. ORA-02303 can be safely ignored.

SQL> select * from dba_registry_history

ACTION_TIME
------------------------------
ACTION
------------------------------
NAMESPACE
------------------------------
VERSION
------------------------------
        ID
----------
COMMENTS
------------------------------
18-APR-07 10.38.09.565465 AM
CPU
SERVER
10.2.0.3.0
   5901891
CPUApr2007

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

Successfully applied 8-)

backup database keep forever logs

If you want to keep some backups forever, you maybe tried

RMAN> backup database keep forever logs;

Starting backup at 13.04.2007 13:58:04
...
backup will never be obsolete
archived logs required to recover from this backup 
will expire when this backup expires
...
Finished backup at 13.04.2007 13:58:23

but the problem is that the archivelogs to recover this backup at any time after the backup will NEVER be obsolete.

You could well try the NOLOGS option, but this requires you to take the database in the MOUNT state.

RMAN> backup database keep forever nologs;

Starting backup at 13.04.2007 14:06:36
...
backup will never be obsolete
archived logs required to recover from this backup 
will not be kept
...
Finished backup at 13.04.2007 14:07:25

This is fine if you can stop your database. But you probably wants online backup. What’s next?

Ok, here is the way to go. You do your online backup, then you mark what you want to keep !

First I backup the old archivelogs, because I do not need to keep those yet.

RMAN> backup archivelog all;

Starting backup at 13.04.2007 14:10:00
...
Finished backup at 13.04.2007 14:10:08

Now I do a backup plus archivelog (with a tag for simplicity)

RMAN> backup database tag backuplsc 
plus archivelog tag backuplsc;

Starting backup at 13.04.2007 14:10:42
...
Finished backup at 13.04.2007 14:11:00

Now I can mark my backup as keep

RMAN> change backup tag backuplsc keep forever;

...
keep attributes for the backup are changed
backup will never be obsolete
backup set key=405 RECID=116 STAMP=619798257
keep attributes for the backup are changed
backup will never be obsolete
backup set key=406 RECID=117 STAMP=619798260

Now if I do a delete obsolete, it will never delete my backup.

RMAN> backup database plus archivelog
Starting backup at 13.04.2007 14:16:46
...
Finished backup at 13.04.2007 14:17:10

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
...
Deleting the following obsolete backups and copies:
...
Deleted 7 objects

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time    
------- -- -- - ----------- ------------------- 
 #Pieces #Copies Compressed Tag
------- ------- ---------- ---
...
405     B  F  A DISK        13.04.2007 14:10:57 
1       1       YES        BACKUPLSC
406     B  F  A DISK        13.04.2007 14:11:00 
1       1       YES        BACKUPLSC
...

10’000 columns in a query

You cannot have more than 1000 columns in a view, but what about a query?

I tried

select 1,2,3,4,....,10000 from dual;

it seems to work fine. However, when increasing, I am facing various errors :

select 1,2,3,4,....,17000 from dual;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [15201], 
[], [], [], [], [], [], []

or even

select 1,2,3,4,....,50000 from dual;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

variable in a view

Imagine you have a view and you want to have a parameter in your view. You cannot have a bind variable in your view. But you could have a function which return a package variable. And this package variable could be set manually for your session

Here we go

SQL> create or replace package p is n number; end p;
  2  /

Package created.

SQL> create or replace function f return number is 
  2  begin return p.n; end;
  3  /

Function created.

SQL> create or replace view v as select ename from 
  2  emp where empno=f;

View created.

SQL> select * from v;

no rows selected

SQL> exec p.n:=7788

PL/SQL procedure successfully completed.

SQL> select * from v;

ENAME
----------
SCOTT

long lines in ps output

In Solaris, the ps output is truncated, so if you have a command with many long parameters, you will not see them all.

If you are interested to see the parameters of a java program, /usr/bin/ps -ef will not give you the expected output

$ ps -ef | grep java
 ldapusr 10744 10692  0 09:50:23 ?        0:09
    /var/opt/mps/oracle/oid/jdk/bin/java -server -Djava
.security.policy=/var/opt/mp

So you could use /usr/ucb/ps awwx to get the long line

$ /usr/ucb/ps awwx  | grep java
 10744 ?        S  0:09 /var/opt/mps/oracle/oid/jdk/bin/
java -server -Djava.security.policy=/var/opt/mps/oracle/
oid/j2ee/oca/config/java2.policy -Djava.awt.headless=tru
e -Xmx256M -Djava.awt.headless=true -Doracle.ons.oracleh
ome=/var/opt/mps/oracle/oid -DIASHOST=novgaasdv01 -java.
library.path=/var/opt/mps/oracle/oid/lib32:/var/opt/mps/
oracle/oid/lib:/var/opt/mps/oracle/oid/jlib:/var/opt/mps
/oracle/oid/oca/lib -DORACLE_HOME=/var/opt/mps/oracle/oi
d -Doracle.home=/var/opt/mps/oracle/oid -Doracle.ons.ora
clehome=/var/opt/mps/oracle/oid -Doracle.home=/var/opt/m
ps/oracle/oid -Doracle.ons.oracleconfighome=/var/opt/mps
/oracle/oid -Doracle.ons.clustername=C_160.61.98.160.161
7189.1119d9146e1.-8000 -Doracle.ons.instancename=oid1014
.novgaasdv01 -Dopmn.compatible=904 -Doracle.ons.indexid=
oca.default_island.1 -Doracle.ons.uid=120848407 -Doracle
.oc4j.instancename=oca -Doracle.oc4j.islandname=default_
island -DOPMN=true -jar oc4j.jar -config /var/opt/mps/or
acle/oid/j2ee/oca/config/server.xml -properties -propert
ies -ports ajp:12502,rmi:12402,jms:12602

Apparently some OS have the ps -w flag to get long lines. Read Note 395113.1 on Metalink (or man ps)