Thinking Out Loud

September 1, 2013

Moving Objects to Shrink Data Files

Filed under: oracle — mdinh @ 1:38 am

At a company I used to work for, I would frequently get asked, “There’s 200 GB free in the tablespace. Why can’t you just shrink it?”

First, the tablespace consists of many data files and there are objects at the end of the data file, preventing the shrink.

There’s probably over a dozen post out there about the subject matter; however, I believe this method is much simpler and will let you be the judge.

Find the data files to shrink, provide the size to shrink to, a list of tables will be provided to be moved.

After the move, rebuild all UNUSABLE indexes. Don’t forget, this is an offline operation.

Find data files for tablespace

SQL> set pages 10000
SQL> select tablespace_name, file_id, bytes/1048576 mb from dba_data_files
where tablespace_name = '&_tbs' order by 1,2 asc
;  2    3
Enter value for _tbs: USERS
old   2: where tablespace_name = '&_tbs' order by 1,2 asc
new   2: where tablespace_name = 'USERS' order by 1,2 asc

TABLESPACE_NAME                   FILE_ID         MB
------------------------------ ---------- ----------
USERS                                   4      23824
USERS                                   7      24576
USERS                                   8      23684
USERS                                   9      20480
USERS                                  10      20480
USERS                                  11      20450
USERS                                  12      20439
USERS                                  14      20353
USERS                                  15      20480
USERS                                  17      20480
USERS                                  19      20480
USERS                                  20      20480
USERS                                  21      20480
USERS                                  31      20453
USERS                                  34      20450
USERS                                  36      18315
USERS                                  39      20480
USERS                                  41      20467
USERS                                  43      20161
USERS                                  44      20353
USERS                                  48      20458
USERS                                  50      10240
USERS                                  51      10941
USERS                                  54      11003
USERS                                  57      10240
USERS                                  61      10193
USERS                                  62      10117
USERS                                  64      10240
USERS                                  66      10240
USERS                                  68      28449
USERS                                  69      10240
USERS                                  70      10178
USERS                                  71      10240
USERS                                  76      25000
USERS                                  83      24556
USERS                                  86      10218
USERS                                  87      13994
USERS                                  88       8721

38 rows selected.

Elapsed: 00:00:00.05

Identify file_id and final size

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2       V_FILE_ID NUMBER;
  3       V_BLOCK_SIZE NUMBER;
  4       V_RESIZE_SIZE NUMBER;
  5  BEGIN
  6       V_FILE_ID := &_FILE_ID;
  7       V_RESIZE_SIZE := &_RESIZE_FILE_TO_MB;
  8
  9       SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;
 10
 11       DBMS_OUTPUT.PUT_LINE('.');
 12       DBMS_OUTPUT.PUT_LINE('.');
 13       DBMS_OUTPUT.PUT_LINE('.');
 14       DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' MB');
 15       DBMS_OUTPUT.PUT_LINE('===================================================================');
 16       DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');
 17       DBMS_OUTPUT.PUT_LINE('===================================================================');
 18
 19       for my_record in (
 20            SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
 21            FROM DBA_EXTENTS
 22            WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1048576 > V_RESIZE_SIZE
 23            AND FILE_ID = V_FILE_ID
 24            AND SEGMENT_TYPE NOT LIKE '%PARTITION%'
 25            ORDER BY 1) LOOP
 26                 DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
 27       END LOOP;
 28
 29       DBMS_OUTPUT.PUT_LINE('===================================================================');
 30       DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');
 31       DBMS_OUTPUT.PUT_LINE('===================================================================');
 32
 33       for my_record in (
 34            SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
 35            FROM DBA_EXTENTS
 36            WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1048576 > V_RESIZE_SIZE
 37            AND FILE_ID = V_FILE_ID
 38            AND SEGMENT_TYPE LIKE '%PARTITION%'
 39            ORDER BY 1) LOOP
 40                 DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
 41       END LOOP;
 42
 43  END;
 44  /
Enter value for _file_id: 83
old   6:      V_FILE_ID := &_FILE_ID;
new   6:      V_FILE_ID := 83;
Enter value for _resize_file_to_mb: 10240
old   7:      V_RESIZE_SIZE := &_RESIZE_FILE_TO_MB;
new   7:      V_RESIZE_SIZE := 10240;
.
.
.
OBJECTS IN FILE 83 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 10240 MB
===================================================================
NON-PARTITIONED OBJECTS
===================================================================
SCHEMA.D_DATA - OBJECT TYPE = TABLE
SCHEMA.LOGIN_LOG - OBJECT TYPE = TABLE
SCHEMA.COMPANY - OBJECT TYPE = TABLE
SCHEMA.ORDER - OBJECT TYPE = TABLE
SCHEMA.FIELD - OBJECT TYPE = TABLE
SCHEMA.REQUEST - OBJECT TYPE = TABLE
SCHEMA.SENDER - OBJECT TYPE = TABLE
SCHEMA.SUMMARY - OBJECT TYPE = TABLE
SCHEMA.TERMINAL - OBJECT TYPE = TABLE
SCHEMA.ORG - OBJECT TYPE = TABLE
SCHEMA.SESSION_LOG - OBJECT TYPE = TABLE
SCHEMA.AGENT - OBJECT TYPE = TABLE
===================================================================
PARTITIONED OBJECTS
===================================================================

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30
SQL>

When asked about how much space was saved, the answer is 24556-10240=14316 MB

Disclaimer, this has on been run; hence, the actual results is unknown.

SQL script and Reference:

– How to Resize a Datafile (Doc ID 1029252.6)

select owner, segment_name name, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks
from dba_extents
where file_id = &file_id
order by block_id
;

SET SERVEROUTPUT ON
DECLARE
     V_FILE_ID NUMBER;
     V_BLOCK_SIZE NUMBER;
     V_RESIZE_SIZE NUMBER;
BEGIN
     V_FILE_ID := &FILE_ID;
     V_RESIZE_SIZE := &RESIZE_FILE_TO_MB;

     SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;

     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' MB');
     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in (
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
          FROM DBA_EXTENTS
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1048576 > V_RESIZE_SIZE
          AND FILE_ID = V_FILE_ID
          AND SEGMENT_TYPE NOT LIKE '%PARTITION%'
          ORDER BY 1) LOOP
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
     END LOOP;

     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in (
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
          FROM DBA_EXTENTS
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1048576 > V_RESIZE_SIZE
          AND FILE_ID = V_FILE_ID
          AND SEGMENT_TYPE LIKE '%PARTITION%'
          ORDER BY 1) LOOP
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
     END LOOP;

END;
/

 

About these ads

2 Comments »

  1. Hi, I’ve developped a similar proc, which actually moves different kinds of segments (e.g. [sub/non]partitioned tables/indexes, tables with Spatial data, and tables with [B/C]LOBs) but something I’ve come across is that it seems that sometimes you can not force moving segments into the free spaces, below the threshold you define; i.e. the segments are actually moved, without any errors, but not more than couple of hundreds of blocks, and they are still way over the margin you’d want them to be. And that’s despite the fact that enough free space does exist below the set margin. Do you know why that happens, and is there a way to force moving a segment to a specific block id?

    I have got a remedy for that: simply move the segment to an auxiliary/temp tablespace you’ve created for shrinking purpose, and then move it back to the original tablespace. The same segment would be placed below the set margin!!!

    Any suggestions?

    Thanks,
    Babak Tourani.

    Comment by Babak Tourani — September 1, 2013 @ 5:53 pm | Reply

    • I am not aware of a way to move a segment to a specific block id. Sorry.

      Comment by mdinh — September 4, 2013 @ 12:51 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 302 other followers

%d bloggers like this: