ORA-01653: unable to extend table SYS.SQLOBJ$PLAN by 128 in tablespace SYSAUX

user_15312440 0
Viewed 97

I have run into an interesting problem while working on partitioned table in Oracle database.

Observation:

when I execute the query

select partition_name, high_value
from all_tab_partitions
where table_name = 'TABLE_NAME_P';

I get an ora error

ORA-01653: unable to extend table SYS.SQLOBJ$PLAN by 128 in tablespace SYSAUX

However, the query successfully runs without the high_value. For example: the queries

select partition_name from all_tab_partitions where table_name = 'TABLE_NAME_P';
select partition_position from all_tab_partitions where table_name = 'TABLE_NAME_P';

are throwing no errors.

Moreover, when I replace table_name with tablespace_name column like:

select partition_name, high_value from all_tab_partitions where tablespace_name = 'TABLESPACE_D1'

then also the query runs successfully without any errors. However, I need to use the table_name for finding the partitions and their corresponding high_value for a specific table in my user tablespace.

  • user Tablespace size = 4GB
  • SYSAUX free space = 147MB
  • Oracle 19c version 19.3.

Help me understand:

  1. Why does this error occur with high_value and not with other columns in all_tab_partitions?
  2. How can I make space in SYSAUX for the table SYS.SQLOBJ$PLAN without creating additional datafile?
  3. Is it safe to purge or delete some entries in SYS.SQLOBJ$PLAN to create space and what can be its impacts? I cannot find any documentation for SYS.SQLOBJ$PLAN and information about its columns.
oracle
1 Answers

Ad 1.

Don't know - I can make just an assumption that this query was "a new one" and database tried to save statistics about it. But it could be a bug also.

Ad 2.

You can query v$sysaux_occupants for information about SYSAUX usage.

It is possible to shrink objects in SYSAUX tablesapce. This will free up space that is allocated but not used by some objects (for ex. occupied by deleted rows).

In general you can shrink tables with alter table statement:

-- You have to enable row movement:
ALTER TABLE <table name> ENABLE ROW MOVEMENT;

-- This command will recover space, but it doesn't change the high water mark
-- so it will not free up space (it just moves free block at the "end of table")
-- but it's an "online" operation and makes only row locks 
-- so it's better to use  this one as first for big tables
ALTER TABLE <table name> SHRINK SPACE COMPACT;

--
-- This one will recover free space and it will amend the high water mark
ALTER TABLE <table name> SHRINK SPACE;

-- This statement will do the same but for all dependant objects too:
ALTER TABLE <table name> SHRINK SPACE CASCADE;

Be aware that YOU MUST NOT follow any general guides about shrinking tables and indexes because moving some objects from SYSAUX tablespace via ALTER TABLE is NOT SUPPORTED as it could cause corruption. There are special procedures for some of these objects. Do you have access to "My Oracle Support"? There is an article (Doc ID 1563921.1) titled How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes, which explains procedure for SYSAUX tablespace. So don't use the above statements without checking in the mentioned article that it is allowed for them.

Ad 3.

You could drop an unused baselines (not enabled, not accepted) - it should be safe. You can query DBA_SQL_PLAN_BASELINES to find those. More about it is in Managing SQL Plan Baselines chapter of documentation.

user_96551770
Related