change Logs for v$backup table in oracle 10g

[Former Member]

  Dear Team,

   How can we check change logs for v$backup table in oracle 10g.

   I want to check what changes has been done in fields of v$backup & who made these modifications in last 2-3 days...

    Please check

[Former Member · Accepted solution]

Hello Sumit,

V$BACKUP is based on x$kcvfhonl and cannot be changed by database users:

SQL> delete from v$backup;

delete from v$backup

            *

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

You can use the Flashback Query feature to query old versions of the V$BACKUP view which still reside in the undo segments:

select * from v$backup as of timestamp(sysdate-0.5); -- to go back 12 hours

Regards,

Mark

[Former Member · Accepted solution]

  Dear Mark,

  Thanks you very much for helpful answer.

I have following queries regarding same:

1. Could you elaborate meaning of " as of timestamp(sysdate-0.5)"

2. After executing SQL> select * from v$backup as of timestamp(sysdate-0.5); i got below

      output:

FILE# STATUS

CHANGE# TIME

---------- ------------------ ---------- ---------------

	1 NOT ACTIVE	7.8920E+12 07-JUL-12
	2 NOT ACTIVE	7.8920E+12 07-JUL-12
	3 NOT ACTIVE	7.8920E+12 07-JUL-12
	4 NOT ACTIVE	7.8920E+12 07-JUL-12
	5 NOT ACTIVE	7.8920E+12 07-JUL-12

would u please let me know what "CHANGE" column values 7.8920E+12; 7.8920E+12....... meaning?

3. If i want to check logs of 2-3 days back, then what would be the SQL query...

[Former Member · Accepted solution]

   Hi,

  Please reply...............

[Former Member · Accepted solution]

   Dear mark,

  could u update me on above query. i am very thankful to u for the same..

[Former Member · Accepted solution]

Hello Sumit,

you can check via show parameter undo_retention how many seconds of data are kept in the undo segments. Typically this is set to 12 hours and not several days.

sysdate -0.5 means the actual date/time minus half a day (12 hours).

When numbers get to big, Oracle displays them in the exponential notation. Use this command to tell sqlplus to print numbers up to 13 digits:

set numwidth 13

Regards,

Mark

[Former Member · Accepted solution]

Dear Mark,

Thanks a Tonnnnnnnnnnnn!!!!!!!!!!!
Really, much appreciated, u clear all my doubts...