Oracle 创建表空间时的 “Next” 子句

你好，

我最近想知道 “next” 子句在create table space语句中的限制是什么。如果我创建一个表空间的 “Next” 子句值为50mb，maxsize无限制 (使用autoextend on)，这是否意味着数据库将抓取增长表空间的最大块为50mb？

我主要是对潜在的性能影响感到好奇，这个小的 “下一个” 范围可能会对一个正在快速增长的大文件数据文件产生影响，如果Oracle无论如何都会看到数据文件是如何增长的，并抓住更大的范围。


谢谢!

在本地管理表空间的世界中，initial/next在某种程度上是一个有争议的问题，因为我们以管理空间的内部位图定义的大小分配范围。

例如

SQL> create tablespace blah datafile 'C:\ORACLE\ORADATA\NP12\BLAH.DBF' size 500m
  2  default storage ( initial 1m next 100m );

Tablespace created.

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x char(100)) tablespace blah;

Table created.

SQL>
SQL> insert into t
  2  select rownum from dual connect by level <= 500;

500 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0      65536
         1      65536

SQL> insert into t
  2  select rownum from dual connect by level <= 5000;

5000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0      65536
         1      65536
         2      65536
         3      65536
         4      65536
         5      65536
         6      65536
         7      65536
         8      65536
         9      65536
        10      65536
        11      65536

12 rows selected.

SQL> insert into t
  2  select rownum from dual connect by level <= 50000;

50000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0      65536
         1      65536
         2      65536
         3      65536
         4      65536
         5      65536
         6      65536
         7      65536
         8      65536
         9      65536
        10      65536
        11      65536
        12      65536
        13      65536
        14      65536
        15      65536
        16    1048576
        17    1048576
        18    1048576
        19    1048576
        20    1048576
        21    1048576

22 rows selected.

如果我在表级别进行这些存储分配，则对该意图有一些基本的尊重，但是它仍然与固定的范围大小保持一致，例如

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x char(100)) storage ( initial 10m next 100m ) tablespace blah;

Table created.

SQL>
SQL> insert into t
  2  select rownum from dual connect by level <= 500;

500 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0    8388608
         1    1048576
         2    1048576

3 rows selected.

SQL>
SQL> insert into t
  2  select rownum from dual connect by level <= 5000;

5000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0    8388608
         1    1048576
         2    1048576

3 rows selected.

SQL>
SQL> insert into t
  2  select rownum from dual connect by level <= 50000;

50000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0    8388608
         1    1048576
         2    1048576

3 rows selected.

SQL>
SQL> insert into t
  2  select rownum from dual connect by level <= 500000;

500000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0    8388608
         1    1048576
         2    1048576
         3    8388608
         4    8388608
         5    8388608
         6    8388608
         7    8388608
         8    8388608
         9    8388608

10 rows selected.