3,491
社区成员
发帖
与我相关
我的任务
分享
[code=SQL]
SQL> DROP TABLE TINY.ETEST;
Table dropped
SQL>
SQL> create table tiny.etest
2 (
3 id number,
4 name varchar2(100)
5 )
6 pctfree 15
7 pctused 40
8 initrans 2
9 maxtrans 255
10 storage
11 (
12 initial 14k
13 next 1m
14 minextents 1
15 maxextents unlimited
16 )
17 /
Table created
SQL> select
SQL> select segment_name,bytes,initial_extent from dba_segments s where s.owner = 'TINY' and s.segment_name = 'ETEST'
2 ;
SEGMENT_NAME BYTES INITIAL_EXTENT
-------------------------------------------------------------------------------- ---------- --------------
ETEST 65536 16384
SQL> ------我这里的inital 14k,为什么在我的表dba_segment表里是16348?
SQL> DROP TABLE TINY.ETEST;
Table dropped
SQL> select * from dba_extents where segment_name = 'FTEST';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
TINY FTEST TABLE TINY 0 5 9 65536 8 5
TINY FTEST TABLE TINY 1 5 17 65536 8 5
TINY FTEST TABLE TINY 2 5 25 65536 8 5
TINY FTEST TABLE TINY 3 5 33 65536 8 5
TINY FTEST TABLE TINY 4 5 41 65536 8 5
TINY FTEST TABLE TINY 5 5 49 65536 8 5
TINY FTEST TABLE TINY 6 5 57 65536 8 5
TINY FTEST TABLE TINY 7 5 65 65536 8 5
TINY FTEST TABLE TINY 8 5 73 65536 8 5
TINY FTEST TABLE TINY 9 5 81 65536 8 5
TINY FTEST TABLE TINY 10 5 89 65536 8 5
TINY FTEST TABLE TINY 11 5 97 65536 8 5
TINY FTEST TABLE TINY 12 5 105 65536 8 5
TINY FTEST TABLE TINY 13 5 113 65536 8 5
TINY FTEST TABLE TINY 14 5 121 65536 8 5
TINY FTEST TABLE TINY 15 5 129 65536 8 5
TINY FTEST TABLE TINY 16 5 137 65536 8 5
17 rows selected
SQL> create tablespace tiny datafile 'E:\oracle\product\10.2.0\oradata\myorcl\tiny01.dbf' size 10M
2 extent management local uniform size 64k;
Tablespace created
SQL> create table ftest(id number)
> tablespace tiny
> pctfree 10
> pctused 40
> initrans 2
> maxtrans 255
> storage(
> initial 7k
> next 1m
> minextents 2
> maxextents 255);
Table created
SQL> select * from dba_segments where segment_name='FTEST';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_POOL
-------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------ -----------
TINY FTEST TABLE TINY 5 11 1114112 136 17 1064960 65536 1 2147483645 0 5 DEFAULT
SQL> SELECT ceil(14 * 1024 / 8192) * 8192 FROM dual;
CEIL(14*1024/8192)*8192
-----------------------
16384
SQL> SELECT ceil(65 * 1024 / 8192) * 8192 FROM dual;
CEIL(65*1024/8192)*8192
-----------------------
73728
SQL>
SQL>
SQL> create table tiny.etest
2 (
3 id number,
4 name varchar2(100)
5 )
6 pctfree 15
7 pctused 40
8 initrans 2
9 maxtrans 255
10 storage
11 (
12 initial 65k
13 next 1m
14 minextents 1
15 maxextents unlimited
16 )
17 /
Table created
SQL> select segment_name,bytes,initial_extent from dba_segments s where s.owner = 'TINY' and s.segment_name = 'ETEST';
SEGMENT_NAME BYTES INITIAL_EXTENT
-------------------------------------------------------------------------------- ---------- --------------
ETEST 131072 73728
SQL> -------我把initial 改为了65k,dba_segment表里是变成了73728?这是个什么关系呢?到底dba_segment的initial_extent是取的什么值?
SQL> select segment_name,bytes,blocks from dba_extents where segment_name = 'ETEST';
SEGMENT_NAME BYTES BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
ETEST 65536 8
ETEST 65536 8
SQL> ------这里是申请了2个extent,这个每次申请的大小应该是一样的,为什么不是我建表的next 1M呢,而是65536,而且增大的extent每次都可能不一样
SQL> truncate table tiny.etest;
Table truncated
SQL> insert into tiny.etest select rownum,rownum||'test' from dual connect by rownum<=100000;
100000 rows inserted
SQL> commit;
Commit complete
SQL> select segment_name,bytes,blocks from dba_extents where segment_name = 'ETEST';
SEGMENT_NAME BYTES BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 65536 8
ETEST 1048576 128
ETEST 1048576 128
18 rows selected
SQL> --------为什么我的最后两条记录的extent的大小和前面的不一样,有哪个高手给我解答下呢?