Database Administration (MOSC)

MOSC Banner

DBMS_REDIFINITION for partitioned table - why did it created 100% more fragmentation instead of defr

edited Apr 20, 2020 12:46PM in Database Administration (MOSC) 8 commentsAnswered ✓

I have a single table and its dependent objects like indexes constraints in a single tablespace. The tablespace is around 200 GB size. When I wanted to reduce a column size for this table, I chose the online DBMS_REDEFINITION package. This option, as I understood, not only modified the table structure but defragments table as well. But it did the other way.

Before redefinition :

Datafile size - 237GB

Used space - 217GB

After redefinition :

Datafile size - 416GB

Used space - 196GB

So now I have 200GB unused space.I cannot resize the tablespace since 416GB is treated as used even though its just 196GB. Why did this happen after DBMS_DEFINITION? Is there anything else I need set up before going with this approach. NOTE: This table has a function-based index hence I did not go with the SHRINK option. Also, this table is list partitioned.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center