We have a table named DS_AUDIT_ENTRY where logs of audits are recorded. It contains many years data.
Now we want to keep 6 months data only and archive old data. Also if required we have to fetch the old data whenever asked. How can I perform this activity?
What we have tried is below :
1. we created a new empty table with name 'ds_audit_entry_old1' in a new schema.
2. swapped the existing table (ds_audit_entry <> ds_audit_entry_old1) with the above one
3. Now the ds_audit_entry_old1 is populated with the last ~6 months of audit logs
4. Archived ds_audit_entry_old1.
5. Ds_audit_entry table now contains 6 months old data.
Now we want to restore the data before 6 months in the table along with the current data.
How to do that?
-----------------------------------------------------------------------
Table Creation:
DS_AUDIT_ENTRY
CREATE TABLE "UCDUSER"."DS_AUDIT_ENTRY"
( "ID" VARCHAR2(36 BYTE) NOT NULL ENABLE,
"VERSION" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE,
"USER_ID" VARCHAR2(64 BYTE),
"USER_NAME" VARCHAR2(255 BYTE),
"EVENT_TYPE" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(255 BYTE),
"OBJ_TYPE" VARCHAR2(255 BYTE),
"OBJ_NAME" VARCHAR2(255 BYTE),
"OBJ_ID" VARCHAR2(255 BYTE),
"CREATED_DATE" NUMBER(*,0) NOT NULL ENABLE,
"STATUS" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"DELETABLE" VARCHAR2(1 BYTE) DEFAULT 'Y',
"IP_ADDRESS" VARCHAR2(40 BYTE),
PRIMARY KEY ("ID")
USING INDEX 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 "UCD_TESTCASE" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
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 "UCD_TESTCASE" ;
DS_AUDIT_ENTRY_OLD1
CREATE TABLE "ARCIVER"."DS_AUDIT_ENTRY_OLD1"
( "ID" VARCHAR2(36 BYTE) NOT NULL ENABLE,
"VERSION" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE,
"USER_ID" VARCHAR2(64 BYTE),
"USER_NAME" VARCHAR2(255 BYTE),
"EVENT_TYPE" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(255 BYTE),
"OBJ_TYPE" VARCHAR2(255 BYTE),
"OBJ_NAME" VARCHAR2(255 BYTE),
"OBJ_ID" VARCHAR2(255 BYTE),
"CREATED_DATE" NUMBER(*,0) NOT NULL ENABLE,
"STATUS" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"DELETABLE" VARCHAR2(1 BYTE) DEFAULT 'Y',
"IP_ADDRESS" VARCHAR2(40 BYTE),
PRIMARY KEY ("ID")
USING INDEX 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 "TEST_ARCHIVAL" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
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 "TEST_ARCHIVAL" ;
----------------------------------------------------
Swapping the data in DS_AUDIT_ENTRY_OLD1:
insert into DS_AUDIT_ENTRY_OLD1
select * from DS_AUDIT_ENTRY;
Let's start from the beginning:
Why do you want to archive data older than 6 months?
And what precisely does "
fetch the old data whenever asked" mean?
Often archiving means the data only exists in a backup. So to get it you have to restore the database, then run the query. If you rarely query the old data and only need it for compliance reasons this is often "good enough".
If you'll often query old archived rows this method is impractical. Transferring rows to an archive table is another way (which is my guess at what you're currently doing).
You can easily get the complete picture by unioning the two together:
select * from DS_AUDIT_ENTRY
union all
select * from DS_AUDIT_ENTRY_OLD1
Partitioning can also help here. This makes it easy to copy data from the current to old table. And remove it from the current with something along the lines of:
insert into DS_AUDIT_ENTRY_OLD1
select * from DS_AUDIT_ENTRY partition ( pold );
alter table DS_AUDIT_ENTRY drop partition pold;
Of course, with partitioning you may not need to archive the old data at all... But to really help here, we need to understand your goals.