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

On Jan 15, 7:58*am, CenturionX <darwinbaldr... (AT) gmail (DOT) com> wrote:
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

I really appreciate your help.
What I’m intended to do is to check if the refresh of the snapshots
was successful.
So, I have two questions:
1] In Oracle 10g R2, how can I ensure that?
2] The MLOG$ tables keep the records all time or they are delete
before the refresh?

Thanks.

On Jan 15, 2:15*pm, CenturionX <darwinbaldr... (AT) gmail (DOT) com> wrote:
To verify your snapshot refresh was successful you can run this query:

select owner, name, table_name, last_refresh, error
from DBA_SNAPSHOTS
where error <> 0;

'no rows selected' indicates error-free refreshes for all snapshots/
materialized views.

The materialized view logs are not purged; why would they be? They are
created against the master tables and record data changes to those
tables to allow fast refreshes of the snapshots/materialized views.
TRUNCATE TABLE can purge those logs, but won't by default. You'd need
to specify PURGE MATERIALIZED VIEW LOG in the TRUNCATE command before
that would occur:

truncate table emp purge materialized view log;

You can substitute SNAPSHOT for MATERIALIZED VIEW and Oracle won't
complain:

truncate table emp purge snapshot log;


David Fitzjarrell