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

Thread: ora-1659

  1. #1
    Join Date
    Jul 2001
    Posts
    93
    Hi,

    I want to move an index from tablespace TS1 to tablespace TS2. Tablespace TS2 has 700mb free. The index size is 100mb.

    I tried to do this with alter index 'index_name' rebuild online storage(initial 5m next 5m minextents 2 pctincrease 0) tablespace ts02;

    It is giving me the ora-01659: unable to allocate MINEXTENTS beyond 8 in tablespace TS2.

    Does any one know why?


  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    U have fragmentation of free extents.
    Before create (or rebuild) index execute:
    alter tablespace TS2 coalesce;

  3. #3
    Join Date
    Jul 2001
    Posts
    93
    Hi,

    Coalesceing the tablespace did not help.

    Any Idea Why?

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Check size of largest free extent.
    Then try to decrease inital/next extent size little bit less then max free extent.
    U can use this script for check:
    koef of free extents fragmentation
    max size of free extent.

    COLUMN tablespace FORMAT A20 TRUNCATE heading 'Tablespace|name' justify left
    COLUMN Total FORMAT 9999990 heading 'Size|(MB)' justify right
    COLUMN used FORMAT 9999990 heading 'Used|(MB)' justify right
    COLUMN free FORMAT 9999990 heading 'Free|(MB)' justify right
    COLUMN pct_free FORMAT 9999.99 heading '% Free' justify right
    COLUMN largest FORMAT 9999990 heading 'Largest|Free|Extent' justify right
    COLUMN fragment FORMAT 9999990 heading '# free|exetents' justify right
    COLUMN k_frag FORMAT 9999.99 heading 'Fragment|Ratio' justify right

    select substr(a.tablespace_name,1,13) tablespace,
    round(sum(a.total1)/1024/1024, 1) Total,
    round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used,
    round(sum(a.sum1)/1024/1024, 1) free,
    round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free,
    round(sum(a.maxb)/1024/1024, 1) largest,
    max(a.cnt) fragment,
    100 - max(a.k_frag) k_frag
    from
    (select tablespace_name,
    0 total1,
    sum(bytes) sum1,
    max(bytes) MAXB,
    count(bytes) cnt,
    sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) k_frag
    from dba_free_space
    group by tablespace_name
    union
    select tablespace_name,
    sum(bytes) total1,
    0,
    0,
    0,
    0
    from dba_data_files
    group by tablespace_name) a
    group by a.tablespace_name
    order by 8 desc
    /

    COLUMN tablespace clear
    COLUMN Total clear
    COLUMN used clear
    COLUMN free clear
    COLUMN pct_free clear
    COLUMN largest clear
    COLUMN fragment clear
    COLUMN k_frag clear


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