Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Cleanup AUD$ before moving from SYSTEM tablespace

RobKNov 23 2018 — edited Nov 28 2018

Hello!

This question is related Oracle 11.2 and 12.x versions where we are supposed to use DBMS_AUDIT_MGMT package.

Question is related to Standard Audit Trail with conventional auditing (AUD$ and not unified auditing)

So imagine your AUD$ table is in SYSTEM tablespace. It is 200GB with data 10 years old.

This is the current situation and setting

-- CURRENT tablespace of AUD$

select  tablespace_name as current_aud$_ts

from dba_segments

where (owner = 'SYS' and segment_name = 'AUD$');

CURRENT_AUD$_TS

------------------------------

SYSTEM

-- CURRENT destination

column current_aud$_destination_ts format a30

select parameter_value as current_aud$_destination_ts

from DBA_AUDIT_MGMT_CONFIG_PARAMS

where parameter_name = 'DB AUDIT TABLESPACE'

  and audit_trail = 'STANDARD AUDIT TRAIL';

CURRENT_AUD$_DESTINATION_TS

------------------------------

SYSAUX

What is my goal?

I would like to cleanup data older than 1 year. I would like to delete old records.

After the cleanup I would possibly move data to SYSAUX.

The point is that it makes no sense to move such huge amount of data and then delete 90% of it. I would like to move only the needed 10% to SYSAUX.

I would NOT like to move the whole data to SYSAUX.

What is the problem?

INIT_CLEANUP moves the data to the default SYSAUX tablespace (or somewhere else if specified with SET_AUDIT_TRAIL_LOCATION parameter)

https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm#ARPLS65423

SET_AUDIT_TRAIL_LOCATON procedure also moves the data and does not only set a parameter. (Why does it not only set the location???)

https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm#ARPLS65427

My idea was to SET_AUDIT_TRAIL_LOCATION to SYSTEM tablespace like this

begin

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

   DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

   'SYSTEM') ;

end;

/

But it had no effect. No errors. No nothing.... It is still SYSAUX tablespace.

column current_aud$_destination_ts format a30

select parameter_value as current_aud$_destination_ts

from DBA_AUDIT_MGMT_CONFIG_PARAMS

where parameter_name = 'DB AUDIT TABLESPACE'

  and audit_trail = 'STANDARD AUDIT TRAIL';

CURRENT_AUD$_DESTINATION_TS

------------------------------

SYSAUX

Question

The question is how one can avoid moving the huge amount of data and start the cleanup while still using SYSTEM tablespace.

Shall I just delete from AUD$ without the DBMS_AUDIT_MGMT package? Is plain delete still supported?

Thanks,

RobK
This post has been answered by Mark D Powell on Nov 26 2018
Jump to Answer

Comments

Frank Kulash
Hi, Andrew,

Analytic fucntions are great for that, but not as easy as you might wish.

Using the analytic LAG (or LEAD) function, you can see iwhat the difference is between a year and the last year for the same id: 
LAG (year) OVER (PARTITION BY id ORDER BY year DESC) 
- year
Using a CASE staemnet, you can mark each row where the difference was 1 as 0, and all the rows where the difference was more than 1 as 1.
Then, using the analytic SUM function, you can add up all those numbers through the present row 
SUM (dif) OVER (PARTITION BY id ORDER BY year DESC) AS grp
Since analytic functions can't be nested, this usually involves two sub-queries. 
WITH   got_new_grp  AS
(
	SELECT	id
	,	year
	,	CASE
			WHEN	LAG (year) OVER ( PARTITION BY	id
				    	   	  ORDER BY  	year	DESC
						) - year > 1
			THEN	1
		END	AS new_grp
	FROM	table_x
)
,	got_grp	AS
(
	SELECT	got_new_grp.*
	,	COUNT (new_grp) OVER ( PARTITION BY	id
		      		       ORDER BY	 	year	DESC
				     )   AS grp
	FROM	got_new_grp
)
SELECT	id
,	COUNT (*)	AS cnt
FROM	got_grp
WHERE	grp	= 0
GROUP BY	id
ORDER BY	id;
In this solution, ecery id can have a different starting point. For example, if you delete the row with id=2 and year=2009, then the count for id=2 is 1, not 0.

You might have a special case, where the combination of id and year is unique, and the difference is always 1 or more. If so, there's a cute trick to avoid one of the sub-queries.

Edited by: Frank Kulash on Feb 25, 2009 1:48 PM
Frank Kulash
Hi,

Here's a solution for the special case (no duplicate years, difference is always 1 or more): 
WITH	got_grp	AS
(
	SELECT	id
	,	year
	,	(	MAX (year) OVER (PARTITION BY id)
		-	year
		) - ROW_NUMBER () OVER (PARTITION BY id ORDER BY year DESC)
		        AS  grp
	FROM	table_x
)
SELECT	id
,	COUNT (*)	AS cnt
FROM	got_grp
WHERE	grp	= -1
GROUP BY	id
ORDER BY	id;
589626
Works like a charm, thanks so much.

Andrew
Aketi Jyuuzou
I used sense of TabibitoZan B-) 
with YearT as(
select 1 as ID,2009 as YEAR from dual union
select 1,2008 from dual union
select 1,2007 from dual union
select 1,2006 from dual union
select 1,2005 from dual union
select 1,2004 from dual union
select 1,2002 from dual union
select 1,2001 from dual union
select 2,2009 from dual union
select 2,2008 from dual union
select 2,2005 from dual)
select ID,
count(*) Keep(Dense_Rank Last order by distance) as cnt
from (select ID,
      Year+Row_Number() over(partition by ID order by YEAR desc) as distance
        from YearT)
group by ID;

ID  CNT
--  ---
 1    6
 2    2
user503699
Aketi,

Hats off to you and your "sense of TabibitoZan" (whatever that means)...
Wish I can learn to "THINK" like this.
Aketi Jyuuzou
I mentions "Tabibitozan" in below threads ;-)
584668
450745

"Tabibitozan" is one of math problem.
I do not know what "Tabibitozan" is called in English.
589626
That's some sweet code. Thanks for all the advice guys.
1 - 7

Post Details

Added on Nov 23 2018
#general-database-discussions
14 comments
8,210 views