Thanks for the question, SANDEEP.

Asked: July 20, 2017 - 7:40 pm UTC

Last updated: July 27, 2017 - 1:20 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have DB with one tablespace and many datafiles whereas tables are spread over many datafiles. I used below query to check fragmented tables list.

select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE_MB", round((num_rows*avg_row_len
/1024/1024),2) "ACTUAL_SIZE_MB", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB" from
dba_tables where owner in ('XXXXX') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 1024 order by 8 desc;

post that I took one table and used below queries to reduce fragmentation

alter table xxx enable row movement;
alter table xxx shrink space;
alter table xxx disable row movement;

I verified result if fragmentation is reduced or not using below query but not much difference

Query 1:
select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE_MB", round((num_rows*avg_row_len
/1024/1024),2) "ACTUAL_SIZE_MB", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB" from
dba_tables where owner in ('XXXXX') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 1024 order by 8 desc;

Query 2:
select segment_name,bytes/1024/1024 as mb from user_segments where segment_name='XXX';

Question:
1. My understanding is fragmented space shown by above query and segment advisor (reclaimable space) must be reduced to near about 0 but it is not happening. is it because table is spread across multiple datafiles? Why is it showing me wrong frangmented size?
2. What is the standard way to reduce fragmentation if table is spread across many datafiles? Or Am i getting wrong fragmentation space value due to table spread over many datafiles?

and Connor said...

The numbers in your first query are only updated after a fresh gather of statistics (see at end for a demo), so you need to do that

But the first question I have is - what are you looking to achieve ?

If someone said to me - my table is spread all over lots of datafiles in lots of extents, my response is typically: "I dont care".

There are specific reasons why you might want to do a "shrink space" command, eg

- improve overall resource cost of full table scan
- reclaim space for a table that will never grow again

but improving how the table is spread across files is rarely one of them.

SQL> create table t pctfree 0 as select d.* from dba_objects d,
  2  ( select 1 from dual connect by level <= 10 );

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2  blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE_MB",
  3  round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE_MB",
  4  round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB"
  5  from dba_tables
  6  where owner = user
  7  and table_name = 'T';

    BLOCKS   NUM_ROWS AVG_ROW_LEN TOTAL_SIZE_MB ACTUAL_SIZE_MB FRAGMENTED_SPACE_MB
---------- ---------- ----------- ------------- -------------- -------------------
     13769     780490         132        107.57          98.25                9.32

1 row selected.

SQL>
SQL> delete from t where mod(object_id,2) = 0 ;

389690 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> select
  2  blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE_MB",
  3  round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE_MB",
  4  round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB"
  5  from dba_tables
  6  where owner = user
  7  and table_name = 'T';

    BLOCKS   NUM_ROWS AVG_ROW_LEN TOTAL_SIZE_MB ACTUAL_SIZE_MB FRAGMENTED_SPACE_MB
---------- ---------- ----------- ------------- -------------- -------------------
     13769     780490         132        107.57          98.25                9.32

1 row selected.

SQL>
SQL> alter table t enable row movement;

Table altered.

SQL> alter table t shrink space;

Table altered.

SQL>
SQL> select
  2  blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE_MB",
  3  round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE_MB",
  4  round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB"
  5  from dba_tables
  6  where owner = user
  7  and table_name = 'T';

    BLOCKS   NUM_ROWS AVG_ROW_LEN TOTAL_SIZE_MB ACTUAL_SIZE_MB FRAGMENTED_SPACE_MB
---------- ---------- ----------- ------------- -------------- -------------------
     13769     780490         132        107.57          98.25                9.32

1 row selected.

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2  blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE_MB",
  3  round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE_MB",
  4  round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB"
  5  from dba_tables
  6  where owner = user
  7  and table_name = 'T';

    BLOCKS   NUM_ROWS AVG_ROW_LEN TOTAL_SIZE_MB ACTUAL_SIZE_MB FRAGMENTED_SPACE_MB
---------- ---------- ----------- ------------- -------------- -------------------
      6813     390800         132         53.23           49.2                4.03

1 row selected.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I would like to understand about shrink of table spread over multiple data files

A reader, July 21, 2017 - 8:55 pm UTC

I would like to understand about shrink of table spread over multiple data files

Sandeep karade, July 21, 2017 - 9:02 pm UTC

Hi there, could you please explain about using shrink for table which spread over multiple data files with fragmentation?

July 24, 2017 - 1:45 am UTC

I'm not sure what is there to explain ?

Shrink reduces the high water mark of a table is there is empty space under the high water mark that can be reclaimed. Whether that space is on 1 datafile, or 100 datafiles is not really of relevance.

vineet, July 24, 2017 - 10:23 am UTC

Hi Connor,

being a fan of this site i do try out the examples you give as a reason i was trying on

create table t pctfree 0 as select d.* from dba_objects d,
( select 1 from dual connect by level <= 10 );

alter table t compress;
alter table t enable row movement;
alter table t shrink space compact;

but while alter table t shrink space compact;

i am getting this error.

13:57:01 SQL> alter table t shrink space compact;
alter table t shrink space compact
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

July 25, 2017 - 7:22 am UTC

It will be your tablespace. It must be segment space managed.

SQL> select TABLESPACE_NAME, SEGMENT_SPACE_MANAGEMENT, EXTENT_MANAGEMENT
  2  from dba_tablespaces;

TABLESPACE_NAME      SEGMEN EXTENT_MAN
-------------------- ------ ----------
...
USERS                AUTO   LOCAL
DEMO                 AUTO   LOCAL
ASKTOM               AUTO   LOCAL
LARGETS              AUTO   LOCAL

Alex, July 24, 2017 - 4:16 pm UTC

If someone said to me - my table is spread all over lots of datafiles in lots of extents, my response is typically: "I dont care".

This situation would make a table point in time recovery considerably more time consuming though correct?

July 25, 2017 - 7:23 am UTC

Fair point.

But generally if you want to recover a table to a point in time (and not the other tables), then you have larger issues to worry about :-)

Space management - Auto Vs Manual

SANDEEP KARADE, July 26, 2017 - 7:20 pm UTC

With reference to my initial question to understand reclaim space for table spread over multiple datafiles.
I gone further with analysis of fragmentation and found something. If LMT tablespace SEGMENT_SPACE_MANAGEMENT is auto then there are less chances of fragmentation (there will fragmentation but less) and if LMT tablespace SEGMENT_SPACE_MANAGEMENT is Manual then there are chances of more fragmentation due to uneven extend allocation done on table level (if any) so either any Technic (depends of limitation) for defragmentation will reclaim the space but it is better to set tablespace SEGMENT_SPACE_MANAGEMENT to AUTO before doing defragmentation else fragmentation will happen again after some days.

Am I correct with understanding?

July 27, 2017 - 1:20 am UTC

Definitely. Any database should be migrating to locally managed tablespaces and automatic segment space management.

There are tools in dbms_space_admin to do so.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

I would like to understand about shrink of table spread over multiple data files

I would like to understand about shrink of table spread over multiple data files

Space management - Auto Vs Manual

More to Explore

PL/SQL demos

PL/SQL docs

Not able to recover Space even after Shrink space

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

I would like to understand about shrink of table spread over multiple data files

I would like to understand about shrink of table spread over multiple data files

Space management - Auto Vs Manual

More to Explore

PL/SQL demos

PL/SQL docs