user_flashback_archive does not contain any information
Date: Mon, 22 Apr 2013 14:17:59 +0200
Message-ID: <51752A77.3060506_at_mgm-tp.com>
Hello,
I'm trying to retrieve flashback archive information from my database, but it seems that USER_FLASHBACK_ARCHIVE is not populated correctly:
There is no row in USER_FLASHBACK_ARCHIVE but there is one in DBA_FLASHBACK_ARCHIVE for the current user:
SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 22 14:06:42 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table foo
2 (
3 id number(22,0) not null
4 );
Table created.
SQL> create flashback archive foo_archive
2 tablespace users 3 retention 2 year;
Flashback archive created.
SQL> alter table foo
2 flashback archive foo_archive;
Table altered.
SQL> select flashback_archive_name
2 from user_flashback_archive;
no rows selected
SQL> select flashback_archive_name
2 from dba_flashback_archive
3 where owner_name = user;
FLASHBACK_ARCHIVE_NAME
FOO_ARCHIVE SQL> I can reproduce this on 11.2.0.3 running on CentOS or Windows7
The table does show up in USER_FLASHBACK_ARCHIVE_TABLES though. But as I want to find details about the archive as well (especially the retention period), I would need the information from USER_FLASHBACK_ARCHIVE.
The user that should eventually retrieve the archive information does not have select privileges on the DBA_xxx views, so using dba_flashback_archive is not really an option right now.
Any ideas? I did not find anything on metalink.
Regards
Thomas
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 22 2013 - 14:17:59 CEST