CREATE TYPE t_rp_row AS OBJECT ( scn NUMBER, DATABASE_INCARNATION# number, GUARANTEE_FLASHBACK_DATABASE varchar(3), STORAGE_SIZE number, time timestamp(9), restore_point_time timestamp(9), PRESERVED varchar2(3), name varchar2(128) ); CREATE TYPE t_rp_tab IS TABLE OF t_rp_row; create or replace FUNCTION get_rp_info return t_rp_tab pipelined as begin for i in ( select * from v_$restore_point ) loop pipe row (t_rp_row(i.scn,i.DATABASE_INCARNATION#,i.GUARANTEE_FLASHBACK_DATABASE,i.STORAGE_SIZE,i.time,i.restore_point_time,i.PRESERVED,i.name)); end loop; return; end;
and you can grant select on this funtion to every one, without FLASHBACK ANY TABLE\SELECT_CATALOG_ROLE\SELECT ANY grants:
TEST_RP@u10pgp1> select * from table(SYS.get_rp_info) 2 ; SCN DATABASE_INCARNATION# GUARANTEE STORAGE_SIZE TIME RESTORE_POINT_TIME PRESERVED NAME ---------- --------------------- --------- ------------ ------------------------------- ------------------------------ --------- ---------- 3.3816E+11 2 NO 0 14-DEC-15 04.58.27.000000000 PM NO T1