Oracle & Linux Blog

Flashback database offer a simple way for performing a point in time recovery. This feature was introduced in oracle 10g. Let’s take a scenario, where we have generated huge amount of flashback logs. Now to reclaim the space, we can reduce the db_flashback_retention_target parameter to a very small value, which will make the logs obsolete after some time & will delete them in case of space pressure in FRA (Flash / Fast Recovery Area). But if we want to reclaim the space immediately, we can trun off the flashback. In 10g we’ll have to shutdown immediate startup mount alter database flashback off; alter database open; But with 11gR2, Oracle introduced a new feature. We can now turn flashback on / off, when database is OPEN SQL> select * from v$version; BANNER ------------------------------ ------------------------ Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS ...

Imran> select * from student;         Sno ----------       1   2 3 Imran> select xmlagg (xmlelement (e,Sno ||',')).EXTRACT('//text()') as Sno from (select distinct Sno from student);   Sno -------------------------------------------------------------------------------------------------------------------------- 1,2,3, If we observe the above result it contains comma(,) at the end. In order to remove comma(,) we will use RTRIM function: Imran> select rtrim(xmlagg (xmlelement (e,Sno ||',')).EXTRACT('//text()'), ',') as Sno from (select distinct Sno from student); Sno ------------------------------------------------------------------------------------------------------------------------- 1 ,2,3