ITPub博客

首页 > Linux操作系统 > Linux操作系统 > storage存储参数(2))

storage存储参数(2))

原创 Linux操作系统 作者:zgdiyy 时间:2011-04-27 13:00:33 0 删除 编辑
SQL> conn / as sysdba
已连接。
SQL> col tablespace_name format a10
SQL>  select tablespace_name,initial_extent,next_extent,min_extents,max_extents,ext
ent_management,allocation_type from dba_tablespaces;

TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS EXTENT_MAN
---------- -------------- ----------- ----------- ----------- ----------
ALLOCATIO
---------
SYSTEM              65536                       1  2147483645 LOCAL
SYSTEM

UNDOTBS1            65536                       1  2147483645 LOCAL
SYSTEM

SYSAUX              65536                       1  2147483645 LOCAL
SYSTEM


TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS EXTENT_MAN
---------- -------------- ----------- ----------- ----------- ----------
ALLOCATIO
---------
TEMP              1048576     1048576           1             LOCAL
UNIFORM

USERS               65536                       1  2147483645 LOCAL
SYSTEM

EXAMPLE             65536                       1  2147483645 LOCAL
SYSTEM


TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS EXTENT_MAN
---------- -------------- ----------- ----------- ----------- ----------
ALLOCATIO
---------
TEST                65536                       1  2147483645 LOCAL
SYSTEM

TEST2               65536                       1  2147483645 LOCAL
SYSTEM

TEST3               65536                       1  2147483645 LOCAL
SYSTEM


TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS EXTENT_MAN
---------- -------------- ----------- ----------- ----------- ----------
ALLOCATIO
---------
TEST4             2097152     2097152           1  2147483645 LOCAL
UNIFORM


已选择10行。


SQL> create table t1(a int) tablespace test4;

表已创建。

SQL> col segment_name format a10
SQL> col owner format a10
SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T1';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T1         SYS        TEST4               0               2        256
SQL> alter table t1 allocate extent;

表已更改。
SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T1';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T1         SYS        TEST4               0               2        256
T1         SYS        TEST4               1               2        256


SQL> alter table t1 allocate extent(size 1m);

表已更改。
SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T1';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T1         SYS        TEST4               0               2        256
T1         SYS        TEST4               1               2        256
T1         SYS        TEST4               2               2        256


SQL> alter table t1 allocate extent(size 5m);

表已更改。
SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T1';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T1         SYS        TEST4               0               2        256
T1         SYS        TEST4               1               2        256
T1         SYS        TEST4               2               2        256
T1         SYS        TEST4               3               2        256~
T1         SYS        TEST4               4               2        256~
T1         SYS        TEST4               5               2        256~

已选择6行。
可以看到对于uniform大小的 exntent 如果指定的 大小 uniform. 那么这个区大小为 指定大小n/uniform的整数ceil(5/2)=3

 

~~~~~ 表级别的存储参数

SQL> create table t2 (a int) storage(initial 1m next 2m minextents 1 maxextents 10)
tablespace test4;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~按storage看 初始大小应该为1M (最小一个区 mintents)

表已创建。

SQL> create table t3 (a int) storage(initial 1m next 1m minextents 1 maxextents 10)
tablespace test4;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~按storage看 初始大小应该为1M (最小一个区 mintents)


表已创建。

SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T2';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T2         SYS        TEST4               0               2        256~~~~~~~~~~~~~~~~~~实际~初始为2M

SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T3';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T3         SYS        TEST4               0               2        256~~~~~~~~~~~~~~~~实际~初始为2M
                                                                             当设置的初始大小oracle uniform大下
                                                                              
SQL> create table t4 (a int) storage(initial 3m next 3m minextents 1 maxextents 10)
tablespace test4;~~~~~~~~~~~~~~~~~~~~~~按storage看 初始大小应该为3M (最小一个区 mintents)


表已创建。


SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T4';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T4         SYS        TEST4               0               2        256~~~~~~~~~~~~~~~~~~~实际2个extent 4M>oracle uniform. ,还是按设置的CEIL(N/uniform)大小
T4         SYS        TEST4               1               2        256

SQL> create table t5 (a int) storage(initial 3m next 5m minextents 1 maxextents 10)
tablespace test4;~~~~~~~~~~~~~~~~~~~~~~按storage看 初始大小应该为3M (最小一个区 mintents)

表已创建。

SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T5';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T5         SYS        TEST4               0               2        256~~~~~~~~~~~~~~~~~~~实际2个extent 4M>oracle uniform. ,还是按设置的CEIL(N/uniform)大小
T5         SYS        TEST4               1               2        256


SQL> alter database datafile 'd:\test4.dbf' autoextend on
  2  ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~防止datafile 空间不足 开自动扩展

SQL> alter tablespace test4 autoextend on;
alter tablespace test4 autoextend on
*
第 1 行出现错误:
ORA-32773: 不支持对小文件表空间 TEST4 的操作~~~~~~~~~~~~~~注意应该针对datafile  not tablespace
数据库已更改。
SQL> create table t66 (a int) storage(initial 3m next 5m minextents 5 maxextents 10
)tablespace test4;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~初试为  3m+5M+5M+5M+5M=23M(初始要求最小 5个区)

表已创建。

SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T66';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T66        SYS        TEST4               0               2        256
T66        SYS        TEST4               1               2        256
T66        SYS        TEST4               2               2        256
T66        SYS        TEST4               3               2        256~~~~~~~~~~~ORACLE 比较后 分配CEIL(23/2) 12个区
T66        SYS        TEST4               4               2        256
T66        SYS        TEST4               5               2        256
T66        SYS        TEST4               6               2        256              可以看到maxextents没用~~初始就分了12个区
T66        SYS        TEST4               7               2        256
T66        SYS        TEST4               8               2        256
T66        SYS        TEST4               9               2        256
T66        SYS        TEST4              10               2        256

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T66        SYS        TEST4              11               2        256

已选择12行。

看接下来的allocate extent

SQL> alter table t2 allocate extent;

表已更改。

SQL> alter table t3 allocate extent;

表已更改。

SQL> alter table t5 allocate extent;

表已更改。

SQL> alter table t66 allocate extent;

表已更改。

SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T2';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T2         SYS        TEST4               0               2        256
T2         SYS        TEST4               1               2        256~~~~~~~~~~~~都是只分配一个extent 2M

SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T3';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T3         SYS        TEST4               0               2        256
T3         SYS        TEST4               1               2        256~~~~~~~~~~~~~~都是只分配一个extent 2M


SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T4';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T4         SYS        TEST4               0               2        256
T4         SYS        TEST4               1               2        256~~~~~~~~~~~~~~~都是只分配一个extent 2M


SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T5';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T5         SYS        TEST4               0               2        256

T5         SYS        TEST4               1               2        256
T5         SYS        TEST4               2               2        256~~~~~~~~~~~都是只分配一个extent 2M

SQL> select segment_name,owner,tablespace_name ,extent_id,bytes/1024/1024,blocks fr
om dba_extents where segment_name='T66';

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T66        SYS        TEST4               0               2        256
T66        SYS        TEST4               1               2        256
T66        SYS        TEST4               2               2        256
T66        SYS        TEST4               3               2        256
T66        SYS        TEST4               4               2        256
T66        SYS        TEST4               5               2        256
T66        SYS        TEST4               6               2        256
T66        SYS        TEST4               7               2        256
T66        SYS        TEST4               8               2        256
T66        SYS        TEST4               9               2        256
T66        SYS        TEST4              10               2        256~~~~~~~~~~~~~~~~~~~~~~都是只分配一个extent 2M

SEGMENT_NA OWNER      TABLESPACE  EXTENT_ID BYTES/1024/1024     BLOCKS
---------- ---------- ---------- ---------- --------------- ----------
T66        SYS        TEST4              11               2        256
T66        SYS        TEST4              12               2        256~~~~~~~~~~~~~~~~~~~都是只分配一个extent 2M
已选择13行。

~~~~~~~~~~~在接下来的 下一个分区中都是 使用INITIAL_EXTENT中大小 分一个

!!!!!!!!!其实我上面说的 oracle 比较uniform. 说的不太正确,~~~因为autoallocate(system)时候也是这个原理, 准确些 是比较  user_tablespaces中的INITIAL_EXTENT 跟这

tablespace 设置的参数比较 <则用 tablespace中INITIAL_EXTENT  则分一个initial_extent大小的区 ,大于则分 ceil( N/INITIAL_EXTENTS) 个initial_extent大小的区


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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-04-26

  • 博文量
    3
  • 访问量
    14926