This table SYS_FBA_CONTEXT_AUD will contain only audit related details for the FBDA tables. So, the space usage will be relatively be very less.

Moving any Sys objects out of System tablespace is not supported. Please check the below document.

Moving or Reorganizing SYS Owned Objects to different tablespace or within SYSTEM tablespace ( Doc ID 1073588.1 )

But one alternative you can adopt is that, if you see the table is taking more space as per your setup, then you can take a backup of the table to another tablespace and purge the CONTEXT.

SQL> exec DBMS_FLASHBACK_ARCHIVE.PURGE_CONTEXT;

Since implementing FDA, over time selecting context information has got slower and slower.

running 12.1.0.2 sys.SYS_FBA_CONTEXT_AUD has (naturally) got larger and larger. 10046 a call to any table in the FDA for context information and query is getting slower as sys.SYS_FBA_CONTEXT_AUD gets bigger (several million rows in ours).

sys.SYS_FBA_CONTEXT_AUD is unindexed, it needs an index on the xid which resolves the performance, but as this is a sys table someone in oracle should be building that into it.

Can someone take a look at that please.

Sure theres workarounds, the index creation is a workaround. But Im then re-writing the following type of a query which is unworkable for me at the moment with the current size of our aud table. The base table has 250k records, the aud table has 3.8 million rows. for each context call below, the aud table has an FTS for each row in the base table. so 250k * 7 . the 10046 is horrendous. Use at your peril.

I put in a feature enhancement request.


select
* ,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','HOST') AS host,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','MODULE') AS MODULE,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SERVICE_NAME') AS SERVICE,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','TERMINAL') AS TERMINAL,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','OS_USER') AS OS_USER,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier
FROM table_in_fda
VERSIONS BETWEEN TIMESTAMP trunc(SYSTIMESTAMP) -1 AND SYSTIMESTAMP
ORDER BY versions_startscn;

with t as (
select 
* , versions_xid xid
FROM table_in_fda 
VERSIONS BETWEEN TIMESTAMP trunc(SYSTIMESTAMP) -1 AND SYSTIMESTAMP 
)
select * 
from t, 
    clone_of_sys_fba_context_aud c
where t.xid = c.xid

I'll use that, thanks.

When you made

alter table tablename NO FLASHBACK ARCHIVE; 

oracle will not clean up data from sys.SYS_FBA_CONTEXT_AUD.
You will have many garbage in this system table and it slow down DBMS_FLASHBACK_ARCHIVE.get_sys_context function.

Using proposed code

insert into MY_CLONE select * from SYS_FBA_CONTEXT_AUD 
exec DBMS_FLASHBACK_ARCHIVE.PURGE_CONTEXT; 

can make an issue.
When some transaction on table with FDA occurs between "insert into" and "purge_context" commands you will lost context of this transactions.

Also I found next issue in 12.2 (I did not test this on other versions)
When you do

EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');
shutdown abort;
startup;

Oracle will forget to record FDA user context and you have to run

DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');

again after startup.

>Also I found next issue in 12.2 (I did not test this on other versions)
>When you do
>EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');
>shutdown abort;
>startup;
>Oracle will forget to record FDA user context and you have to run
>DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');
>again after startup.

This issue also occurrs with version 19.17