Hi, I am wanting to use the FDA feature of Oracle DB (as opposed to a more manual setup of having a bunch of triggers and audit table(s)). However, I am stuck at the first step: to what tablespace to assign my FDA. I am not a DBA, I am a Developer. Moreover, I don't have an in-house DBA with which to consult (there is a system admin who is familiar with the hardware our instance is installed on, how much space there is, and that sort of thing, but not as familiar with the internals of Oracle DB and administering). Everything I've reviewed so far doesn't really discuss this first step, and these resources seem to assume you already have a suitable tablespace for your FDA. In fact, many of these resources say “go ask your DBA" when setting up the tablespace.
Note that I am on Oracle DB 12.2.
What I would like to accomplish is:
- An FDA that can accommodate potentially gigabytes of information (I would expect to set up a Tablespace / FDA for application-related groupings of tables within my database, so potentially I could have numerous Tablespaces / FDAs that collectively store tens, or perhaps, hundreds of gigabytes)
- An FDA that has the specified retention period (e.g. 2 years) and would not fail to store change data due to the specified size of the FDA or the associated Tablespace (i.e. if there was a problem I would get a notification / error somewhere)
- An FDA that, preferably, doesn't store so much contextual information. I would prefer simply tracking the changes to a given field, the user who made the changes, and the associated timestamp. While it's great that FDA has the ability to store so much context info, it's unnecessary in most of our use cases
Those being my goals, I've looked at examples of Tablespace declaration. The examples invariably specify the size, at least if it is a permanent tablespace. This contrasts with the CREATE FLASHBACK ARCHIVE command, which I believe doesn't require a declaration of the space and seems to default to unlimited. However this documentation states:
(Optional) Maximum amount of space that the Flashback Data Archive can use in the first tablespace. The default is unlimited. Unless your space quote on the first tablespace is also unlimited, you must specify this value; otherwise, error ORA-55621 occurs.
So it looks like I can create a Tablespace with unlimited space… but what are the consequences / side-affects of this? Also, the aforementioned documentation doesn't give an example of creating a tablespace with unlimited size.
Additionally, the documentation says there are three types of tablespaces, including permanent and undo tablespaces. For an FDA, do I want to create a permanent or undo tablespace?
Ultimately, I'm simply quite uncertain of how to create a tablespace suitable for an FDA, what options to specify, and what to consider to make sure I don't mess things up. I'm quite hesitant when it comes to altering / adding to the storage characteristics of our DB.
(All this being said, many of my responsibilities are starting to include DBA-like tasks… so I'm sure I'll learn over the next few years. Please forgive my complete ignorance and understand that I'm working on building the knowledge… in the meantime, I would very much so appreciate pointers / advice / recommendations, including a confirmation that I'm overcomplicating the whole thing and a simple “do this”.)
These are the resources I reviewed, in addition to the documentation linked above: