Snapshot - Non-Consistent captures due to PctFree or Storage (Initial/Next)?

From: Patrick Tan <r19262_at_email.sps.mot.com>
Date: 1998/01/25
Message-ID: <34CB3781.2F7DF13B@email.sps.mot.com>#1/1

Hello,
I have a problem which in need of urgent assistance......

I am currently creating snapshots every 8 hours. Some of the snapshots are table-snapshots.
However, I have 2 Snapshots which does multiple joins between 3 tables. It's these 2 snapshots
which I am having problems with.

create snapshot snap_output

         pctfree 10
         storage (initial 500K next 500K pctincrease 0)
        refresh complete start with sysdate next (sysdate + 1)

as select .......

A sql script is run periodically every 8 hours, which in turn creates the snapshots.
At the end of the snapshots, I proceeded to execute a package PL/SQL to generate
summaries.

My problem is that each time I run the script, the results vary, i.e.

• The first time, the snapshots have captured data.
• When snapshots are created again 5 minutes later, it's all empty. Since the data captured in non-historical (static with time), it doesn't make sense.

I am also seeing errors when I tried to execute the PL/SQL Package. At times, it can execute. On other times, it'll generate an error that it could not find the Procedure or package.

It's probably due to some memory problems, but this is making it difficult to get my work done. Any suggestions is deeply appreciated.

PS: I'm running Oracle 7.3 on Windows NT 4.0. Snapshots are created here.

     The remote database runs on AIX 4.1, which stores transaction data.

Best Regards,
-Patrick Tan- Received on Sun Jan 25 1998 - 00:00:00 CST