Hemant's Oracle DBA Blog

10 April, 2016

FBDA -- 5 : Testing AutoPurging

Tracking data changes after one row added (ID_COLUMN=2000) on 06-Apr

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
06-APR-16 10.53.20.328132 PM +08:00

SQL> select scn_to_timestamp(startscn), scn_to_timestamp(endscn), count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4  order by 1,2;

SCN_TO_TIMESTAMP(STARTSCN)
---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ENDSCN)
---------------------------------------------------------------------------
  COUNT(*)
----------
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM
       450

02-APR-16 11.32.55.000000000 PM
03-APR-16 11.45.24.000000000 PM
       550

02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM
  5

02-APR-16 11.46.11.000000000 PM
03-APR-16 11.45.24.000000000 PM
       445

03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM
  5

03-APR-16 11.45.24.000000000 PM
04-APR-16 11.05.33.000000000 PM
      1000

06-APR-16 10.40.43.000000000 PM
06-APR-16 10.42.54.000000000 PM
  1


7 rows selected.

SQL> 
SQL> select count(*) from sys_fba_tcrv_93250;    

  COUNT(*)
----------
      1002

SQL>

More changes on 07-Apr

SQL> insert into test_fbda
  2  select 3000, to_char(3000), trunc(sysdate)
  3  from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> update test_fbda
  2  set date_inserted=date_inserted
  3  where id_column=3000;

1 row updated.

SQL> delete test_fbda
  2  where id_column < 1001 ;

1000 rows deleted.

SQL> commit;

Commit complete.

SQL> 
SQL> l
  1  select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4* order by 1,2,3
SQL> /

STARTTIME    ENDTIME       COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM  02-APR-16 11.46.11.000000000 PM  450
02-APR-16 11.32.55.000000000 PM  03-APR-16 11.45.24.000000000 PM  550
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.41.33.000000000 PM    5
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.45.24.000000000 PM  445
03-APR-16 11.41.33.000000000 PM  03-APR-16 11.45.24.000000000 PM    5
03-APR-16 11.45.24.000000000 PM  04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM  06-APR-16 10.42.54.000000000 PM    1
07-APR-16 10.27.35.000000000 PM  07-APR-16 10.28.03.000000000 PM    1
07-APR-16 10.28.03.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000

10 rows selected.

SQL> 
SQL> l
  1  select id_column, trunc(date_inserted), count(*)
  2  from test_fbda
  3  group by id_column, trunc(date_inserted)
  4* order by 1
SQL> /

 ID_COLUMN TRUNC(DAT   COUNT(*)
---------- --------- ----------
      2000 06-APR-16       1
      3000 07-APR-16       1

SQL>

I see two new 1000 row sets (04-Apr and 07-Apr). I should expect only one.

Now that rows for ID_COLUMN less than 1001 have been deleted on 07-Apr, we have to see if and when they get purged from the History table.

On 09-Apr:

SQL> insert into test_fbda
  2  select 4000, to_char(4000),trunc(sysdate)
  3  from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> update test_fbda
  2  set date_inserted=date_inserted
  3  where id_column=4000;

1 row updated.

SQL> commit;

Commit complete.

SQL> l
  1  select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4* order by 1,2,3
SQL> /

STARTTIME    ENDTIME       COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM  02-APR-16 11.46.11.000000000 PM  450
02-APR-16 11.32.55.000000000 PM  03-APR-16 11.45.24.000000000 PM  550
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.41.33.000000000 PM    5
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.45.24.000000000 PM  445
03-APR-16 11.41.33.000000000 PM  03-APR-16 11.45.24.000000000 PM    5
03-APR-16 11.45.24.000000000 PM  04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM  06-APR-16 10.42.54.000000000 PM    1
07-APR-16 10.27.35.000000000 PM  07-APR-16 10.28.03.000000000 PM    1
07-APR-16 10.28.03.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
09-APR-16 11.10.25.000000000 PM  09-APR-16 11.10.48.000000000 PM    1

11 rows selected.

SQL> 
SQL> select * from user_flashback_archive
  2  /

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------ -----------------
CREATE_TIME
---------------------------------------------------------------------------
LAST_PURGE_TIME
---------------------------------------------------------------------------
STATUS
-------
SYSTEM
FBDA
   1     3
02-APR-16 11.24.39.000000000 PM
02-APR-16 11.24.39.000000000 PM



SQL>

As on the morning of 10-Apr (after leaving the database instance running overnight) :

SQL> select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4  order by 1,2,3
  5  /

STARTTIME    ENDTIME       COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM  02-APR-16 11.46.11.000000000 PM  450
02-APR-16 11.32.55.000000000 PM  03-APR-16 11.45.24.000000000 PM  550
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.41.33.000000000 PM    5
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.45.24.000000000 PM  445
03-APR-16 11.41.33.000000000 PM  03-APR-16 11.45.24.000000000 PM    5
03-APR-16 11.45.24.000000000 PM  04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM  06-APR-16 10.42.54.000000000 PM    1
07-APR-16 10.27.35.000000000 PM  07-APR-16 10.28.03.000000000 PM    1
07-APR-16 10.28.03.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
09-APR-16 11.10.25.000000000 PM  09-APR-16 11.10.48.000000000 PM    1

11 rows selected.

SQL> select systimestamp from dual
  2  /

SYSTIMESTAMP
---------------------------------------------------------------------------
10-APR-16 08.51.29.398107 AM +08:00

SQL> 
SQL> select * from user_flashback_archive
  2  /

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------ -----------------
CREATE_TIME
---------------------------------------------------------------------------
LAST_PURGE_TIME
---------------------------------------------------------------------------
STATUS
-------
SYSTEM
FBDA
   1     3
02-APR-16 11.24.39.000000000 PM
02-APR-16 11.24.39.000000000 PM



SQL>

So auto-purge of the data as of earlier days (02-Apr to 06-Apr) hasn't yet kicked in ? Let's try a manual purge.

SQL> alter flashback archive fbda purge before timestamp (sysdate-4);

Flashback archive altered.

SQL> select * from user_flashback_archive;

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------ -----------------
CREATE_TIME
---------------------------------------------------------------------------
LAST_PURGE_TIME
---------------------------------------------------------------------------
STATUS
-------
SYSTEM
FBDA
   1     3

05-APR-16 11.52.16.000000000 PM



SQL> 
SQL> ! sleep 300
SQL> l
  1  select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4* order by 1,2,3
SQL> /

STARTTIME    ENDTIME       COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM  02-APR-16 11.46.11.000000000 PM  450
02-APR-16 11.32.55.000000000 PM  03-APR-16 11.45.24.000000000 PM  550
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.41.33.000000000 PM    5
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.45.24.000000000 PM  445
03-APR-16 11.41.33.000000000 PM  03-APR-16 11.45.24.000000000 PM    5
03-APR-16 11.45.24.000000000 PM  04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM  06-APR-16 10.42.54.000000000 PM    1
07-APR-16 10.27.35.000000000 PM  07-APR-16 10.28.03.000000000 PM    1
07-APR-16 10.28.03.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
09-APR-16 11.10.25.000000000 PM  09-APR-16 11.10.48.000000000 PM    1

11 rows selected.

SQL>

Although USER_FLASHBACK_ARCHIVE shows that a purge till 05-Apr (the 11:52pm timestamp is strange) has been done, I still see older rows in the History table. The query on the active table does correctly exclude the rows that should not be available.

SQL> select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4  order by 1,2,3;

STARTTIME      ENDTIME    COUNT(*)
---------------------------------- ---------------------------------- ----------
02-APR-16 11.32.55.000000000 PM    02-APR-16 11.46.11.000000000 PM      450
02-APR-16 11.32.55.000000000 PM    03-APR-16 11.45.24.000000000 PM      550
02-APR-16 11.46.11.000000000 PM    03-APR-16 11.41.33.000000000 PM        5
02-APR-16 11.46.11.000000000 PM    03-APR-16 11.45.24.000000000 PM      445
03-APR-16 11.41.33.000000000 PM    03-APR-16 11.45.24.000000000 PM        5
03-APR-16 11.45.24.000000000 PM    04-APR-16 11.05.33.000000000 PM     1000
04-APR-16 11.09.43.000000000 PM    07-APR-16 10.28.03.000000000 PM     1000
06-APR-16 10.40.43.000000000 PM    06-APR-16 10.42.54.000000000 PM        1
07-APR-16 10.27.35.000000000 PM    07-APR-16 10.28.03.000000000 PM        1
07-APR-16 10.28.03.000000000 PM    07-APR-16 10.28.03.000000000 PM     1000
09-APR-16 11.10.25.000000000 PM    09-APR-16 11.10.48.000000000 PM        1

11 rows selected.

SQL> select * from user_flashback_archive;

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME
------------------ ----------------- ---------------------------------------------------------------------------
LAST_PURGE_TIME            STATUS
--------------------------------------------------------------------------- -------
SYSTEM
FBDA
   1     3
05-APR-16 11.52.16.000000000 PM


SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
10-APR-16 10.52.12.361412 PM +08:00

SQL> select count(*) from test_fbda as of timestamp (sysdate-3);

  COUNT(*)
----------
  2

SQL> 
SQL> select partition_position, high_value
  2  from user_tab_partitions
  3  where table_name = 'SYS_FBA_HIST_93250'
  4  order by 1;

PARTITION_POSITION HIGH_VALUE
------------------ --------------------------------------------------------------------------------
   1 MAXVALUE

SQL>

Support Document 16898135.1 states that if Oracle isn't maintaining partitions for the History table, it may not be purging data properly. Even an ALTER FLASHBACK ARCHIVE ... PURGE doesn't purge data (unless PURGE ALL is issued). I'd seen this behaviour in 11.2.0.4 . The bug is supposed to have been fixed in 12.1.0.2 but my 12.1.0.2 environment shows the same behaviour. The fact that my test database has very little activity (very few SCNs being incremented) shouldn't matter. The "Workaround" is, of course, unacceptable.
.
.
.

05 April, 2016

FBDA -- 4 : Partitions and Indexes

Continuing our investigation of the FBDA architecture.

oracle@ora12102 Desktop]$ sqlplus hemant/hemant

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 5 23:25:10 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Apr 05 2016 23:23:47 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select table_name, def_tablespace_name, partitioning_type, partition_count, status
  2  from user_part_tables
  3  order by 1;

TABLE_NAME
--------------------------------------------------------------------------------
DEF_TABLESPACE_NAME        PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
SYS_FBA_HIST_93250
FBDA          RANGE         1 VALID


SQL> 
SQL> set pages600
SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE','SYS_FBA_HIST_93250') from dual;

DBMS_METADATA.GET_DDL('TABLE','SYS_FBA_HIST_93250')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."SYS_FBA_HIST_93250"
   ( "RID" VARCHAR2(4000),
 "STARTSCN" NUMBER,
 "ENDSCN" NUMBER,
 "XID" RAW(8),
 "OPERATION" VARCHAR2(1),
 "ID_COLUMN" NUMBER,
 "DATA_COLUMN" VARCHAR2(15),
 "DATE_INSERTED" DATE,
 "D_1729869_NEW_COL_1" VARCHAR2(5)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FBDA"
  PARTITION BY RANGE ("ENDSCN")
 (PARTITION "HIGH_PART"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FBDA" )


SQL>

So, although my active table (TEST_FBDA) is not partitioned, the History table is Range Partitioned on the ENDSCN column, with a single partition ! All the rows in the History table are in the MAXVALUE partition. I wonder if and when it will ever be split ?

What if I create a partitioned table and then define Flashback Archiving on it ?

SQL> create table test_fbda_partitioned (
  2  id_column number,
  3  data_column varchar2(15),
  4  date_inserted date)
  5  partition by range (id_column)
  6  (partition p_100 values less than (101),
  7  partition p_200 values less than (201),
  8  partition p_300 values less than (301),
  9  partition p_400 values less than (401),
 10  partition p_max values less than (MAXVALUE))
 11  /

Table created.

SQL> alter table test_fbda_partitioned flashback archive fbda;

Table altered.

SQL> insert into test_fbda_partitioned
  2  select rownum, to_char(rownum), trunc(sysdate)
  3  from dual connect by level < 701;

700 rows created.

SQL> commit;

Commit complete.

SQL> update test_fbda_partitioned
  2  set data_column=data_column;

700 rows updated.

SQL> commit;

Commit complete.

SQL> col subobject_name format a15
SQL> select object_type, subobject_name, object_id
  2  from user_objects
  3  where object_name = 'TEST_FBDA_PARTITIONED'
  4  order by 3,1;

OBJECT_TYPE  SUBOBJECT_NAME  OBJECT_ID
----------------------- --------------- ----------
TABLE          93342
TABLE PARTITION  P_100       93343
TABLE PARTITION  P_200       93344
TABLE PARTITION  P_300       93345
TABLE PARTITION  P_400       93346
TABLE PARTITION  P_MAX       93347

6 rows selected.

SQL> 
SQL> select table_name     
  2  from user_tables 
  3  where table_name like '%93342%'
  4  order by 1;

TABLE_NAME
--------------------------------------------------------------------------------
SYS_FBA_DDL_COLMAP_93342
SYS_FBA_HIST_93342
SYS_FBA_TCRV_93342

SQL> 
SQL> select dbms_metadata.get_ddl('TABLE','SYS_FBA_HIST_93342') from dual;

DBMS_METADATA.GET_DDL('TABLE','SYS_FBA_HIST_93342')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."SYS_FBA_HIST_93342"
   ( "RID" VARCHAR2(4000),
 "STARTSCN" NUMBER,
 "ENDSCN" NUMBER,
 "XID" RAW(8),
 "OPERATION" VARCHAR2(1),
 "ID_COLUMN" NUMBER,
 "DATA_COLUMN" VARCHAR2(15),
 "DATE_INSERTED" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FBDA"
  PARTITION BY RANGE ("ENDSCN")
 (PARTITION "HIGH_PART"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FBDA" )


SQL>

So, even though my active table (TEST_FBDA_PARTITIONED) is created as a Range Partitioned Table partitioned on ID_COLUMN, the corresponding History table is Range Partitioned on ENDSCN with a single MAXVALUE partition.
Therefore, Oracle ignores my partitioning definition when creating the History table. The History table (seems to be ?) always Range Partitioned on ENDSCN and starts using the MAXVALUE partition up-front. (When will this Partition be split ? I could search MoS for Docs / Bugs, but I'll do that exercise later).

Now that we know that the History table doesn't use our Partition Key, we must wonder about Partition Pruning when running AS OF queries on the active table that need to access the History Table. If we can't Partition Prune, can we Index the History table ?

SQL> select table_name                   
  2  from user_tables
  3  where table_name like 'SYS_FBA_HIST%'
  4  /

TABLE_NAME
--------------------------------------------------------------------------------
SYS_FBA_HIST_93250
SYS_FBA_HIST_93342

SQL> create index sys_fba_hist_93250_ndx_1 on sys_fba_hist_93250(id_column) tablespace fbda;

Index created.

SQL> create index sys_fba_hist_93342_ndx_1 on sys_fba_hist_93342(id_column) tablespace fbda;

Index created.

SQL> select dbms_metadata.get_ddl('INDEX','SYS_FBA_HIST_93250_NDX_1') from dual;

DBMS_METADATA.GET_DDL('INDEX','SYS_FBA_HIST_93250_NDX_1')
--------------------------------------------------------------------------------

  CREATE INDEX "HEMANT"."SYS_FBA_HIST_93250_NDX_1" ON "HEMANT"."SYS_FBA_HIST_932
50" ("ID_COLUMN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FBDA"


SQL> select dbms_metadata.get_ddl('INDEX','SYS_FBA_HIST_93342_NDX_1') from dual;

DBMS_METADATA.GET_DDL('INDEX','SYS_FBA_HIST_93342_NDX_1')
--------------------------------------------------------------------------------

  CREATE INDEX "HEMANT"."SYS_FBA_HIST_93342_NDX_1" ON "HEMANT"."SYS_FBA_HIST_933
42" ("ID_COLUMN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FBDA"


SQL>

Yes ! Oracle does allow us to build custom indexes on the History tables. (I believe I saw this somewhere in the documentation or a note).

Next Post (possibly) : More DML operations and whether auto-purging based on the defined RETENTION 3 DAY period. (Note : The Retention Period is defined at the Flashback Archive level, not at the individual table (active/History) level. If you need to have tables with different Retention Periods, you need to define different Flashback Archives, although they can all be in the same Tablespace).
.
.
.

04 April, 2016

FBDA -- 3 : Support for TRUNCATEs

One of the points in the previous post was how ADD and DROP Column commands are supported in FBDA.

Let's look at support for TRUNCATEs.

(Just to prove that I am not using the Undo tablespace for the AS OF query, I drop the Undo tablespace) :

SQL> startup
ORACLE instance started.

Total System Global Area  402653184 bytes
Fixed Size      2924928 bytes
Variable Size    260050560 bytes
Database Buffers   134217728 bytes
Redo Buffers      5459968 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled       boolean  FALSE
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS1
SQL> show parameter spfile

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
spfile         string  +DATA/NONCDB/PARAMETERFILE/spf
       ile.267.896483727
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled       boolean  FALSE
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS1
SQL> create undo tablespace undotbs2;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS2';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  402653184 bytes
Fixed Size      2924928 bytes
Variable Size    260050560 bytes
Database Buffers   134217728 bytes
Redo Buffers      5459968 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  402653184 bytes
Fixed Size      2924928 bytes
Variable Size    260050560 bytes
Database Buffers   134217728 bytes
Redo Buffers      5459968 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled       boolean  FALSE
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS2
SQL>

I now TRUNCATE my test table and then run an AS OF query against it.

SQL> connect hemant/hemant
Connected.
SQL> select count(*) from test_fbda;

  COUNT(*)
----------
      1000

SQL> truncate table test_fbda;

Table truncated.

SQL> select count(*) from test_fbda;

  COUNT(*)
----------
  0

SQL> select count(*) from test_fbda as of timestamp (sysdate-1);

  COUNT(*)
----------
      1000

SQL>

The AS OF query was satisfied by the FBDA History table (and not from Undo).

However, because of yesterday's test of ADD and DROP columns (see yesterday's post), the table structure doesn't match AS OF yesterday. So, I work around the difference by naming the columns.

SQL> insert into test_fbda select * from test_fbda as of timestamp (sysdate-1);
insert into test_fbda select * from test_fbda as of timestamp (sysdate-1)
            *
ERROR at line 1:
ORA-00913: too many values


SQL> 
SQL> desc test_fbda
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_COLUMN         NUMBER
 DATA_COLUMN         VARCHAR2(15)
 DATE_INSERTED         DATE

SQL> insert into test_fbda
  2  select id_column, data_column, date_inserted
  3  from test_fbda as of timestamp (sysdate-1);

1000 rows created.

SQL> commit;

Commit complete.

SQL>

Thus, when my query matches the table structure, I can use the data from the previous version of the query.

Note how I did NOT use the SYS_FBA_HIST_93250 table as I'd shown in my previous posts. The History table is NOT to be accessed directly but I demonstrated queries against it it to show the underlying architecture in FBDA.
.
.
.