rebuild index 时的并行度与表空间free size 的关系

SQL> alter index intest rebuild tablespace test1 parallel(degree 2);
Index altered.
SQL> alter index intest rebuild tablespace test1 parallel(degree 3);
Index altered.
SQL> alter index intest rebuild tablespace test1 parallel(degree 4);
Index altered.
SQL> alter index intest rebuild tablespace test1 parallel(degree 5);
alter index intest rebuild tablespace test1 parallel(degree 5)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P004
ORA-01658: unable to create INITIAL extent for segment in tablespace TEST1

当并行度设置为5时报错，需要扩大表空间，但是表空间扩大多少可以满足parallel(degree x)。

*****查看tablespace的free size 是256KB*****
select
  tablespace_name,
  to_char(nvl(total_bytes / 1024,0),'999,999,999') as "size(KB)",
  to_char(nvl((total_bytes - free_total_bytes) / 1024,0),'999,999,999') as "used(KB)",
  to_char(nvl(free_total_bytes/1024,0),'999,999,999') as "free(KB)",
  round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "rate(%)"
from
  ( select
      tablespace_name,
      sum(bytes) total_bytes
    from
      dba_data_files
    group by
      tablespace_name
  ),
  ( select
      tablespace_name free_tablespace_name,
      sum(bytes) free_total_bytes
    from
      dba_free_space
    group by tablespace_name
  )
where
  tablespace_name = free_tablespace_name(+) and
  tablespace_name = 'TEST1'  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25  ;
TABLESPACE_NAME                size(KB)     used(KB)     free(KB)        rate(%)
------------------------------ ------------ ------------ ------------ ----------
TEST1                                   504          248          256      49.21
6 rows selected.

*****查看index intest的INITIAL_EXTENT是64k*****
SQL> select bytes/1024,blocks,extents,initial_extent/1024 from dba_segments where segment_name ='INTEST';
BYTES/1024     BLOCKS    EXTENTS INITIAL_EXTENT/1024
---------- ---------- ---------- -------------------
        64          8          1                  64

*****扩大表空间 resize 700k*****
SQL> alter database datafile '/opt/oradata/orcl/test1_01.dbf' resize 700k;
Database altered.
SQL>select
  tablespace_name,
  to_char(nvl(total_bytes / 1024,0),'999,999,999') as "size(KB)",
  to_char(nvl((total_bytes - free_total_bytes) / 1024,0),'999,999,999') as "used(KB)",
  to_char(nvl(free_total_bytes/1024,0),'999,999,999') as "free(KB)",
  round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "rate(%)"
from
  ( select
      tablespace_name,
      sum(bytes) total_bytes
    from
      dba_data_files
    group by
      tablespace_name
  ),
  ( select
      tablespace_name free_tablespace_name,
      sum(bytes) free_total_bytes
    from
      dba_free_space
    group by tablespace_name
  )
where
  tablespace_name = free_tablespace_name(+) and
  tablespace_name = 'TEST1'  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25  ;
TABLESPACE_NAME                size(KB)     used(KB)     free(KB)        rate(%)
------------------------------ ------------ ------------ ------------ ----------
TEST1                                   704          320          384      45.45

*****tablespace free size=384k,并行度可以设置到6*****

SQL>  alter index intest rebuild tablespace test1 parallel(degree 5);
Index altered.
SQL>  alter index intest rebuild tablespace test1 parallel(degree 6);
Index altered.
SQL> alter index intest rebuild tablespace test1 parallel(degree 7);
alter index intest rebuild tablespace test1 parallel(degree 7)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P006
ORA-01658: unable to create INITIAL extent for segment in tablespace TEST1

*****扩大表空间 resize 760k*****

SQL> alter database datafile'/opt/oradata/orcl/test1_01.dbf' resize 760k;
Database altered.
SQL>select
  tablespace_name,
  to_char(nvl(total_bytes / 1024,0),'999,999,999') as "size(KB)",
  to_char(nvl((total_bytes - free_total_bytes) / 1024,0),'999,999,999') as "used(KB)",
  to_char(nvl(free_total_bytes/1024,0),'999,999,999') as "free(KB)",
  round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "rate(%)"
from
  ( select
      tablespace_name,
      sum(bytes) total_bytes
    from
      dba_data_files
    group by
      tablespace_name
  ),
  ( select
      tablespace_name free_tablespace_name,
      sum(bytes) free_total_bytes
    from
      dba_free_space
    group by tablespace_name
  )
where
  tablespace_name = free_tablespace_name(+) and
  tablespace_name = 'TEST1'  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25  ;
TABLESPACE_NAME                size(KB)     used(KB)     free(KB)        rate(%)
------------------------------ ------------ ------------ ------------ ----------
TEST1                                   760          312          448      41.05

*****tablespace free size=448k,并行度可以设置到7,不能设置8*****
SQL> alter index intest rebuild tablespace test1 parallel(degree 8);
alter index intest rebuild tablespace test1 parallel(degree 8)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P005
ORA-01658: unable to create INITIAL extent for segment in tablespace TEST1

*****继续扩大表空间*****
SQL>  alter database datafile'/opt/oradata/orcl/test1_01.dbf' resize 830k;
Database altered.
SQL>select
  tablespace_name,
  to_char(nvl(total_bytes / 1024,0),'999,999,999') as "size(KB)",
  to_char(nvl((total_bytes - free_total_bytes) / 1024,0),'999,999,999') as "used(KB)",
  to_char(nvl(free_total_bytes/1024,0),'999,999,999') as "free(KB)",
  round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "rate(%)"
from
  ( select
      tablespace_name,
      sum(bytes) total_bytes
    from
      dba_data_files
    group by
      tablespace_name
  ),
  ( select
      tablespace_name free_tablespace_name,
      sum(bytes) free_total_bytes
    from
      dba_free_space
    group by tablespace_name
  )
where
  tablespace_name = free_tablespace_name(+) and
  tablespace_name = 'TEST1'  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25  ;
TABLESPACE_NAME                size(KB)     used(KB)     free(KB)        rate(%)
------------------------------ ------------ ------------ ------------ ----------
TEST1                                   832          320          512      38.46

*****tablespace free size=512k,并行度可以设置到8*****
SQL> alter index intest rebuild tablespace test1 parallel(degree 8);
Index altered.

index intest的INITIAL_EXTENT=64k

degree 5 * INITIAL_EXTENT=5*64k=320k
degree 6 * INITIAL_EXTENT=6*64k=384k
degree 7 * INITIAL_EXTENT=7*64k=448k
degree 8 * INITIAL_EXTENT=8*64k=512k


执行alter index intest rebuild tablespace test1 parallel(degree x)时，表空间的free size要满足tablespace free size >= degree x * INITIAL_EXTENT
例如：
alter index intest rebuild tablespace test1 parallel(degree 8);
INITIAL_EXTENT=64k
tablespace free size >= degree 8 * INITIAL_EXTENT=8*64k=512k

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24756186/viewspace-751612/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24756186/viewspace-751612/