Database Tuning (MOSC)

MOSC Banner

Performance issue- Need suggestion for defragmenting partitioned tables and reclaiming wasted space

Oracle database version: 12.1.0.2

Performance issue- Need suggestion for defragmenting partitioned tables and reclaiming wasted space


Hi,

We have a schema in prod db environment on which excessive dml is performed and we are facing performance issue on db.

1.We have many tables in a schema which are highly fragmented.

Tables are partitioned.(a table has around 20 partition and subpartition)

I want to know what can be the best defragmentation steps for reclaiming wasted space from partitioned table?


2.Also I have used below scripts for finding table fragmentation.

A-select * from (

select owner,table_name,round((blocks*8),2) "size (kb)" ,

round((num_rows*avg_row_len/1024),2) "actual_data (kb)",

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

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center