DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: tablespace COALESCE or shrinking datafile

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    tablespace COALESCE or shrinking datafile

    We have quarterly and yearly processes that deletes, archives millions of rows
    from different tables. To make room on the file system I would either like
    to shrink back down the datafiles (because auto extend is on) or coalesce them.

    Is there a rule of thumb I should follow when shrinking or coalescing? Can somebody provide provide a query that would show me the free and used
    extents in a TS, and when I should coalesce or a query to show me how much free space is in a datafile so I can shrink it.

    THanks in advance to all who answer.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Oracle will not allow you to re-size the data file (10g and above) if it has data in it. I didn't remember any query that tells you how much free size is available in each DATA FILE. If you have past data file growth data with you then you can re-size up to that point in a smaller chunks like 5GB one time till Oracle warns you.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    FYI


    http://download.oracle.com/docs/cd/B...21/tspaces.htm



    Monitoring Free Space
    You can use the following views for monitoring free space in a tablespace:

    DBA_FREE_SPACE
    DBA_FREE_SPACE_COALESCED
    The following statement displays the free space in tablespace tabsp_4:

    SELECT BLOCK_ID, BYTES, BLOCKS
    FROM DBA_FREE_SPACE
    WHERE TABLESPACE_NAME = 'TABSP_4'
    ORDER BY BLOCK_ID;

    BLOCK_ID BYTES BLOCKS
    ---------- ---------- ----------
    2 16384 2
    4 16384 2
    6 81920 10
    16 16384 2
    27 16384 2
    29 16384 2
    31 16384 2
    33 16384 2
    35 16384 2
    37 16384 2
    39 8192 1
    40 8192 1
    41 196608 24
    13 rows selected.


    This view shows that there is adjacent free space in tabsp_4 (for example, blocks starting with BLOCK_IDs 2, 4, 6, 16) that has not been coalesced. After coalescing the tablespace using the ALTER TABLESPACE statement shown previously, the results of this query would read:

    BLOCK_ID BYTES BLOCKS
    ---------- ---------- ----------
    2 131072 16
    27 311296 38
    2 rows selected.


    The DBA_FREE_SPACE_COALESCED view displays statistics for coalescing activity. It is also useful in determining if you need to coalesce space.

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    what is your oracle version?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Jul 2006
    Posts
    195
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
    Running on an AIX 5.3 box

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    are you using locally managed TS with segment space management auto or dictionary managed TS?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  7. #7
    Join Date
    Jul 2006
    Posts
    195
    Quote Originally Posted by vnktummala View Post
    are you using locally managed TS with segment space management auto or dictionary managed TS?

    Thanks,
    locally managed TS with segment space management.

    Here is a typical example of how I create tablespaces. Depending on what tables are going in the TS most of the time I increase the maxsize, but try to stay away from "unlimited" option.

    CREATE TABLESPACE "xxx"
    DATAFILE '/oracle/data/pri/xxx01.dbf' SIZE 50M
    autoextend on NEXT 50M maxsize 200M
    LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width