1

I have a problem in tablespace SYSAUX.

SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE tablespace_name = 'SYSAUX'
ORDER BY bytes DESC;

The issue is with these specific records:

SYS WRI$_ADV_OBJECTS                TABLE   14784
SYS WRI$_ADV_OBJECTS_IDX_01         INDEX   6829
SYS WRI$_ADV_OBJECTS_IDX_02         INDEX   5632
SYS WRI$_ADV_OBJECTS_PK             INDEX   4713
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST  INDEX   136
SYS I_WRI$_OPTSTAT_H_ST             INDEX   72
SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST   INDEX   36
SYS SYS_LOB0000007847C00005$$       LOBSEGMENT  24,1875

Can someone help me clear this space? There is only less than 2% left. I recently took care of this database and I don't know which records can be deleted and which ones can't. I am asking for your support.

I have two production and test databases, and the archivelog mode is enabled on the production one.

SELECT value
FROM v$parameter
WHERE name = 'audit_trail';

The answer is: DB.

What does that mean?

SELECT
    fs.tablespace_name,
    fs.bytes / 1024 / 1024 AS "Total Size (MB)",
    (fs.bytes - NVL(sum(ads.bytes), 0)) / 1024 / 1024 AS "Used Size (MB)",
    NVL(sum(ads.bytes), 0) / 1024 / 1024 AS "Free Space (MB)",
    (1 - (NVL(sum(ads.bytes), 0) / fs.bytes)) * 100 AS "Free Space (%)"
FROM
    dba_free_space fs
    LEFT JOIN dba_data_files ads ON fs.tablespace_name = ads.tablespace_name
GROUP BY
    fs.tablespace_name,
    fs.bytes
HAVING
    fs.tablespace_name = 'SYSAUX';
SYSAUX  2,0625  -32742,9375 32745   -1587536,36363636363636363636363636363636
SYSAUX  8   -32737  32745   -409212,5
SYSAUX  0,625   -32744,375  32745   -5239100
SYSAUX  0,375   -98234,625  98235   -26195900
SYSAUX  0,5625  -32744,4375 32745   -5821233,33333333333333333333333333333333
SYSAUX  0,125   -98234,875  98235   -78587900
SYSAUX  0,5 -32744,5    32745   -6548900
SYSAUX  0,9375  -32744,0625 32745   -3492700
SYSAUX  0,0625  -163724,9375    163725  -261959900
SYSAUX  0,1875  -32744,8125 32745   -17463900
SYSAUX  1,0625  -32743,9375 32745   -3081782,35294117647058823529411764705882
SYSAUX  26  -32719  32745   -125842,3076923076923076923076923076923077
SYSAUX  1,125   -32743,875  32745   -2910566,66666666666666666666666666666667

i have that scrpit for removing old audit

/* procedure for removing redundant audit entries To check and remove unnecessary audit entries using the DBMS_AUDIT_MGMT routines, follow these steps:

Verify that auditing is enabled on your Oracle database. You can do this with the command: */

SELECT value
FROM v$parameter
WHERE name = 'audit_trail';
If audit_trail is set to 'DB', auditing is enabled.

To use the DBMS_AUDIT_MGMT routines, you must first run a set of SQL scripts provided by Oracle to create and prepare the appropriate audit objects. You can find these scripts in the Oracle Home directory under the path rdbms/admin.

Log in to your SYS or SYSTEM account using SQL*Plus and run the cataudit.sql script:

CONNECT SYS AS SYSDBA
@<path_to_oracle_home>/rdbms/admin/cataudit.sql

/* This script will create the appropriate tables and other objects necessary to manage the audit data.

To remove unnecessary audit entries, you can use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL routines with the appropriate parameters. Below is an example SQL query that shows how to use this procedure: */

DECLARE
   l_retval BOOLEAN;
BEGIN
   l_retval := DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
                  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
                  use_last_arch_timestamp => TRUE);
   IF l_retval THEN
     DBMS_OUTPUT.PUT_LINE('Unnecessary audit entries have been removed.');
   ELSE
     DBMS_OUTPUT.PUT_LINE('Failed to remove unnecessary audit entries.');
   ENDIF;
end;

/* The above code will remove unnecessary audit entries from the AUDIT_TRAIL_AUD_STD audit type. You can customize this code by adjusting the parameters of the CLEAN_AUDIT_TRAIL procedure to your needs.

After executing the CLEAN_AUDIT_TRAIL procedure, it is recommended to check the size of the SYSAUX tablespace again to ensure that space has been freed.

It is important that you always perform a full database backup before performing an audit cleanup and consult with your database administrators to ensure procedures are appropriate for your environment and audit requirements. */

but I never use this procedure and I don't what is happend if I use this script.


Reslut of clean

Enter user-name: sys as sysdba
Enter password:

Polaczono z:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

Zmieniono sesje.
    
SQL> ALTER TABLE WRI$_OPTSTAT_TAB_HISTORY MOVE TABLESPACE sysaux;

Zmieniono tabele.

SQL> alter table WRI$_OPTSTAT_TAB_HISTORY  move tablespace sysaux;

Zmieniono tabele.

SQL> alter table WRI$_OPTSTAT_IND_HISTORY  move tablespace sysaux;

Zmieniono tabele.

SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY  move tablespace sysaux
            *
BLAD w linii 1:
ORA-14511: nie mozna wykonac operacji na podzielonym obiekcie


SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY  move tablespace sysaux
            *
BLAD w linii 1:
ORA-14511: nie mozna wykonac operacji na podzielonym obiekcie


SQL> alter table WRI$_OPTSTAT_AUX_HISTORY  move tablespace sysaux;

Zmieniono tabele.

SQL> alter table WRI$_OPTSTAT_OPR  move tablespace sysaux;

Zmieniono tabele.

SQL> alter table WRH$_OPTIMIZER_ENV  move tablespace sysaux;

Zmieniono tabele.

SQL> select partition_name from dba_tab_partitions where table_Name = 'WRI$_OPTSTAT_HISTGRM_HISTORY';

PARTITION_NAME
--------------------------------------------------------------------------------
P_PERMANENT
SYS_P161
SYS_P204

SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move partition p_permanent;

Zmieniono tabele.

SQL> alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild;

Zmieniono indeks.

SQL> alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild;

Zmieniono indeks.

SQL> alter index I_WRI$_OPTSTAT_H_ST rebuild;

Zmieniono indeks.

SQL> select 'alter index '||segment_name||'  rebuild;' from dba_segments where tablespace_name = 'SYSAUX';

Result

SYSAUX  32745   32619,625   125,375 0,9961711711711711711711711711711711711712  1

SYSAUX  11  -32734  32745   -297581,8181818181818181818181818181818182
SYSAUX  0,375   -163724,625 163725  -43659900
SYSAUX  0,625   -130979,375 130980  -20956700
SYSAUX  0,25    -196469,75  196470  -78587900
SYSAUX  0,5625  -65489,4375 65490   -11642566,66666666666666666666666666666667
SYSAUX  0,4375  -32744,5625 32745   -7484471,42857142857142857142857142857143
SYSAUX  6   -32739  32745   -545650
SYSAUX  0,125   -425684,875 425685  -340547900
SYSAUX  0,3125  -360194,6875    360195  -115262300
SYSAUX  0,9375  -98234,0625 98235   -10478300
SYSAUX  0,5 -130979,5   130980  -26195900
SYSAUX  0,8125  -196469,1875    196470  -24180823,07692307692307692307692307692308
SYSAUX  1,375   -65488,625  65490   -4762809,09090909090909090909090909090909
SYSAUX  0,0625  -1473524,9375   1473525 -2357639900
SYSAUX  0,1875  -360194,8125    360195  -192103900
SYSAUX  0,875   -98234,125  98235   -11226757,14285714285714285714285714285714
SYSAUX  1,1875  -32743,8125 32745   -2757373,68421052631578947368421052631579
SYSAUX  13  -32732  32745   -251784,6153846153846153846153846153846154
SYSAUX  0,75    -32744,25   32745   -4365900
SYSAUX  1,0625  -32743,9375 32745   -3081782,35294117647058823529411764705882
SYSAUX  9,75    -32735,25   32745   -335746,1538461538461538461538461538461538
SYSAUX  0,6875  -98234,3125 98235   -14288627,27272727272727272727272727272727
SYSAUX  1   -458429 458430  -45842900
SYSAUX  1,125   -32743,875  32745   -2910566,66666666666666666666666666666667
SYSAUX  3   -32742  32745   -1091400
SYSAUX  5   -65485  65490   -1309700
SYSAUX  2   -32743  32745   -1637150
SYSAUX  18  -32727  32745   -181816,6666666666666666666666666666666667
2
  • 1
    I have added the answers you left as supplementary edits to the question. Please edit further as necessary to make your question fully coherent and answerable.
    – Paul White
    Aug 3, 2023 at 15:23
  • Welcome to the DBA.SE community. Please have a look at help center and How to Ask. Stack Exchange is not a thread-based Q&A site, instead we ask one question and get multiple answers. As already pointed out by Paul, if you have additional information, then please add this to your initial question by clicking on the edit link. Thanks and good luck.
    – John K. N.
    Jan 10 at 6:52

1 Answer 1

2

WRI$ is optimizer history (OPTSTAT) that keeps old statistics info. You can clear this and also reduce how much it keeps:

--How long old stats are kept
select dbms_stats.get_stats_history_retention from dual;

--Set retention of old stats to 10 days
exec dbms_stats.alter_stats_history_retention(10);

--Purge stats older than 10 days (best to do this in stages if there is a lot of data (sysdate-30,sydate-25 etc)
exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

--Show available stats that have not been purged
select dbms_stats.get_stats_history_availability from dual;

--Reorg tables to reduce segment sizes and release space
alter table WRI$_OPTSTAT_TAB_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR  move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV  move tablespace sysaux;

-- the HIST*HISTORY tables may be partitioned. eg:
select partition_name from dba_tab_partitions where table_Name = 'WRI$_OPTSTAT_HISTGRM_HISTORY';
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move partition p_permanent;
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild;
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild;
alter index I_WRI$_OPTSTAT_H_ST rebuild;

--get DDL to rebuild any unusable indexes
select 'alter index '||segment_name||'  rebuild;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX' 

-- run DDL from above output

Lastly, in a non-production environments in a pinch you can actually just truncate those WRI tables and nothing breaks. Not an ideal solution but I've done it many times on non-prod envs and never had any side-effects. Still, I recommend a proper cleanup as I outlined above instead.

2
  • what is the end this command's -- run DDL from above output Jun 16, 2023 at 11:57
  • I mean get the output from the select statement that generates the alter index DDLs and run it manually. You could script the execution too, but that'd require a PL/SQL block. Either way, it's pretty clear what to do. You have to run the DDL, not just see it.
    – Paul W
    Jun 16, 2023 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.