Shrink SYSAUX of a Pluggable Database

The todays topic is inspired by an experience that I had some time ago. A customer comlpained about a huge SYSAUX tablespace inside of one of his PDBs. They needed tp add a second datafile since the first datafile already reached its maximum size of 32G. When we investigated, what was consuming all this space, we found that it was the unified audit trail. As it turned out there were lots of “select any table” usages recorded. There was an application user that should only read some tables. We revoked the “select any table” privilege from that user and istead granted “read” on the tables. Now, that the unified audit trail wasn’t growing that much anymore we cleaned it up. You can read about this in one of my previous posts.

So we ended up with a 32GB SYSAUX tablespace that only holds a handful GB of data. Reorganizing is not as easy as with an user tablespace for two reasons. Reason 1: There are lot of “special” segments like partitioned LOBs, nested tables, clusters etc. Reason 2: We are not allowed to touch objects inside SYSAUX.

SQL> alter table wmsys.WM$LOCKROWS_INFO$ move tablespace sysaux;
alter table wmsys.WM$LOCKROWS_INFO$ move tablespace sysaux
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

SQL> !oerr ora 65040
65040, 00000, "operation not allowed from within a pluggable database"
// *Cause: An operation was attempted that can only be performed in the root
// container.
// *Action: Switch to the root container to perform the operation.
//

Ok. Doing this from the root container does not bring us any step further since there is another SYSAUX tablespace which is not in scope of our reorganization. So we need to find another way to touch objects in our PDBs SYSAUX tablespace. Basically there are two ways to do that, either by setting the underscore session parameter “_oracle_script” or by doing the commands via “dbms_pdb.exec_as_oracle_script”.

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> alter table wmsys.WM$LOCKROWS_INFO$ move tablespace sysaux;

Table altered.

SQL> alter session set "_oracle_script"=false;

Session altered.

SQL> exec dbms_pdb.exec_as_oracle_script('alter table wmsys.WM$LOCKROWS_INFO$ move tablespace sysaux');

PL/SQL procedure successfully completed.

Now let’s check who has segments in the SYSAUX tablespace:

SQL> select distinct owner from dba_segments where tablespace_name='SYSAUX' order by 1;

OWNER
--------------------------------------------------------------------------------
APEX_040200
AUDSYS
CTXSYS
DBSNMP
DVSYS
GSMADMIN_INTERNAL
MDSYS
ORDDATA
ORDSYS
SYS
SYSTEM
WMSYS
XDB

13 rows selected.

In order to shrink the tablespace we create an intermediate tablespace where we can move most of the segments to. That will hopefully free up enough space so that we can shrink the datafile(s) and move the segments back.

SQL> create tablespace sysaux_neu datafile size 500M autoextend on;

Tablespace created.


All the owners need quota for the new tablespace.

SQL> alter user xdb quota unlimited on sysaux_neu;

User altered.

Do this for all the other users that own segments in SYSAUX.

Now we can start moving segments. For XDB there is a procedure that does that. It is described in MOS Note 1271560.1.

SQL> alter system enable restricted session;

System altered.

SQL> select any_path from resource_view;

ANY_PATH
--------------------------------------------------------------------------------
/home
/home/MDSYS

[...]
99 rows selected.

SQL> set serveroutput on
SQL> begin
  2  xdb.dbms_xdb_admin.movexdb_tablespace('SYSAUX_NEU', trace=> TRUE);
  3  end;
  4 /
[...]
sql stmt alter user xdb default tablespace "SYSAUX_NEU"

PL/SQL procedure successfully completed.

Another speciality are the AQ tables. For those Oracle provides a PL/SQL package "move_qt_pkg" which is described in and can be downloaded from MOS Note 1410195.1.

We can simply loop through all AQ tables and move them to the new tablespace.

begin
  for rec in (
    select q.owner ow, q.queue_table nm
	  from dba_queue_tables q 
	  join dba_segments s on (q.queue_table = s.segment_name and q.owner = s.owner)
	 where s.tablespace_name = 'SYSAUX'
  )
  loop
    dbms_output.put( rpad(rec.ow || '.' || rec.nm, 60, '.') );
	begin  
      dbms_pdb.exec_as_oracle_script('begin move_qt_pkg.move_queue_table('''||rec.ow||''' , '''||rec.nm||''', ''SYSAUX'', ''SYSAUX_NEU''); end;');
	  dbms_output.put_line('OK');
	exception
	  when others then
	    dbms_output.put_line('NOK');
		dbms_output.put_line(SQLERRM);
    end;
  end loop;
end;
/

Now the rest is more or less straight forward:

move non-partitioned heap tables
move non-partitioned object tables
move non-partitioned IOTs
move non-partitioned IOT Overflows
move indexes of non-partitioned IOTs
move partitioned heap tables
move LOBs
move non-partitioned indexes
move partitioned indexes

That covers most of the segments and freed up enough space to shrink the datafiles. After that we did the whole procedure all over again to move the segments back.

You can find my script here (in .docx format since WordPress does not allow SQL or TXT).
But be aware, there is no support from Oracle for these actions.

Share this:

Related