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