Resolving ORA-10635: Invalid Segment or Tablespace Type

 Encountering the ORA-10635 error while trying to shrink a compressed table? Don’t worry, I’ve got you covered! Here’s an easy guide to solve this issue.

The Problem

When you attempt to shrink a table, you might see an error like this:

SQL> alter table table_name modify partition part_name shrink space;
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

Or this:

SQL> alter table <table_name> enable row movement;
Table altered.

SQL> alter table <table_name> shrink space compact;
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

So, what’s going on here? There are two main possibilities causing this error:

1. The table is compressed.
2. Automatic Segment Space Management (ASSM) is not enabled for the tablespace.

Solutions

    [ A ] Dealing with Compressed Tables

    Shrink operations are not directly supported on compressed tables. If your table is compressed, follow these steps:

1. Connect as SYSDBA & Disable compression
2. Enable row movement:
3. Shrink the table:

    [ B ] Enabling ASSM for the Tablespace

    Shrink operations require the tablespace to use ASSM. If your tablespace isn't set up with ASSM, you’ll need to convert it:

1. Export all objects from the current tablespace (assuming it’s using Manual Segment Space Management, MSSM).

2. Create a new tablespace with ASSM.

3. Import all objects into the new ASSM tablespace.

Within an ASSM tablespace, note that certain segment types aren’t eligible for online segment shrink:

• IOT mapping tables
• Tables with ROWID-based materialized views
• Tables with function-based indexes

By following these steps, you’ll be able to overcome the ORA-10635 error and manage your tablespaces effectively. Now, go ahead and give it a try – your tables will thank you! 😉

Stay tuned for more Oracle adventures, and don’t forget to share your thoughts and questions in the comments below! 

Regards,

Nikhil