Re: Snapshot log problem

From: ddf <oratune_at_msn.com>
Date: Thu, 15 Jan 2009 11:33:07 -0800 (PST)
Message-ID: <a6e56434-98e3-44a1-a772-87ab084c59f8_at_r36g2000prf.googlegroups.com>

On Jan 15, 7:58 am, CenturionX <darwinbaldr..._at_gmail.com> wrote:
> Hello,
>
> Recently we migrated an Oracle database from version “8.1.6.0.0” to
> “10.2.0.4.0”.
> This database is replicated.  This replication is performed by a job
> that runs this:
>         dbms_refresh.refresh('"USR_OWNER"."GROUP1"');
> after this I have a function:
>    oldest_date DATE := (sysdate - 0.08333);
>    oldest_log_date DATE;
>    -- oldest_log_date is returned by the dbms_snapshot.get_log_age
> function.
>    begin
>        dbms_snapshot.get_log_age(oldest_log_date, snapowner,
> snaptable);
>        if (oldest_date <= oldest_log_date) then
>            RETURN NULL;
>        else
>            RETURN oldest_log_date;
>        end if;
>    end;
> The curious thing is that in the old database it returns NULL even if
> oldest_date is greater than oldest_log_date.
> In the new 10g it returns the oldest_log_date.
>
> I’ll appreciate your hellp.
>
> Thanks

The DBMS_SNAPSHOT.GET_LOG_AGE function is obsolete, and strictly for internal use by the package; the create script from 8.0 onwards clearly states this:

  ---

#######################################################################
  --- INTERNAL PROCEDURES

  ---

• The following procedure provide internal functionality and should
• not be called directly. ---
• These interfaces are obselete in V8 and are present only for
• providing backwards compatibility --- #######################################################################

  PROCEDURE set_up(mowner   IN     VARCHAR2,
                   master   IN     VARCHAR2,
                   log      IN OUT VARCHAR2,
                   snapshot IN OUT DATE,
                   master   IN     VARCHAR2,
                   log      IN OUT VARCHAR2,
                   snapshot IN OUT DATE,
                   snaptime IN OUT DATE);

  PROCEDURE wrap_up(mowner IN VARCHAR2,
                    master IN VARCHAR2,
                    sshot  IN DATE,
                    stime  IN DATE);

  PROCEDURE get_log_age(oldest IN OUT DATE,
                        mow    IN     VARCHAR2,
                        mas    IN     VARCHAR2);

• obselete interface, present for backward compatability PROCEDURE drop_snapshot(mowner IN VARCHAR2, master IN VARCHAR2, snapshot IN DATE);

  PROCEDURE testing;

What information, exactly, do you think is returned by this procedure? Why have you not considered using

select created
into oldest_log_date
from user_objects
where object_name in (
select log_table
from user_mview_logs
where log_owner = snapown
and master = snaptable);

as that returns the same data as your call to dbms_snapshot.get_log_age:

SQL> select get_oldest_log('BING','EMP') from dual;

GET_OLDEST_LOG('BING

---

15-JAN-2009 13:07:48 SQL>
SQL> declare

  2     snapown varchar2(30):='BING';
  3     snaptable varchar2(30):='EMP';
  4
  5     oldest_log_date DATE;
  6     oldest_date DATE:=sysdate - 0.083333;
  7  begin
  8     select created
  9     into oldest_log_date
 10     from user_objects
 11     where object_name in (
 12     select log_table
 13     from user_mview_logs
 14     where log_owner = snapown
 15     and master = snaptable);
 16
 17     dbms_output.put_line(nvl(oldest_log_date, oldest_date));

 18 end;
 19 /
15-JAN-2009 13:07:48 PL/SQL procedure successfully completed.

SQL> As this procedure is obsolete since version 8, is strictly intended for internal use and can change dramatically between releases no one can assess what is causing your 'problem'.

Perhaps you should find another route to the desired information.

David Fitzjarrell Received on Thu Jan 15 2009 - 13:33:07 CST