use free space in tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: use free space in tablespace

  1. #1
    Join Date
    Nov 2001
    Posts
    34

    use free space in tablespace

    hello,

    please i need your help about how use free space in tablespace,
    why i have this map tablespace
    block_id size_in_blocks segments
    394,834 8,975 DBXCOL.INV_PICKING_I4
    403,809 1,795 DBXCOL.INV_PICKING_I4
    405,604 6,160 DBXCOL.INV_PICKING_I5
    411,764 1,540 DBXCOL.INV_PICKING_I5
    413,304 6,160 "free"
    419,464 5,125 DBXCOL.INV_PICKING_PK
    424,589 1,025 DBXCOL.INV_PICKING_PK
    425,614 3,590 DBXCOL.PRUEBA_PICKING
    429,204 515 DBXCOL.PRUEBA_PICKING

    in this map , i have 48M free , but i can`t create a object in this space ,

    disclaimer

    I coalesce tablespace , and pctincrese is 50 in tablespace storage


    How i can create objects in this free space?

    thanks for your information
    Last edited by cazpa; 10-12-2004 at 01:48 PM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: use free space in tablespace

    Originally posted by cazpa
    ... pctincrese is 50 in tablespace storage...
    That's your problem right there -- keep it at 0 to avoid fragmentation, and preferably use LMT's.

    Moving along ... you'll just have to mnake sure that each extent that you specify has a block available, unless you want to defragment by moving all your tables and indexes to new TS's.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    Why does Oracle still default that to 50% anyhow.
    Does anyone use it at 50%?
    I remember when this place was cool.

  4. #4
    Join Date
    Nov 2001
    Posts
    34
    but, i want to know if is posible used this space , because i create a table initial 5m next 3m and no used this space , used new space in tablespace , why?


    thanks

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by cazpa
    but, i want to know if is posible used this space , because i create a table initial 5m next 3m and no used this space , used new space in tablespace , why?


    thanks
    The algorithm by which it determines which space to use is not documented, as far as i know.

    Also, inital 5M and next 3M is perpetuating the problem.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2001
    Posts
    34
    this is the script :

    col tablespace form a15 head 'Tablespace' just c trunc
    col file_id form 990 head 'File' just c
    col block_id form 9,999,990 head 'Block Id' just c
    col blocks form 999,990 head 'Size' just c
    col segment form a38 head 'Segment' just c trunc

    break -
    on tablespace skip page -
    on file_id skip 1

    select
    tablespace_name tablespace,
    file_id,
    1 block_id,
    1 blocks,
    '' segment
    from
    dba_extents
    where
    tablespace_name = upper('&ts')
    union
    select
    tablespace_name tablespace,
    file_id,
    1 block_id,
    1 blocks,
    '' segment
    from
    dba_free_space
    where
    tablespace_name = upper('&ts')
    union
    select
    tablespace_name tablespace,
    file_id,
    block_id,
    blocks,
    owner||'.'||segment_name segment
    from
    dba_extents
    where
    tablespace_name = upper('&ts')
    union
    select
    tablespace_name tablespace,
    file_id,
    block_id,
    blocks,
    ''
    from
    dba_free_space
    where
    tablespace_name = upper('&ts')
    order by
    1,2,3
    /

    undef ts

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