Hello guys,
It's my some test code on Oracle 11.2.0.4.0 for Linux x86_64, please take a closer look at as follow,
SQL> show arraysize
arraysize 15
SQL>
SQL> set arraysize
SP2-0267: arraysize option 0 out of range (1 through 5000)
SQL>
SQL> set arraysize 500
SQL>
SQL> show arraysize
arraysize 500
SQL> set timing on
SQL>
SQL> create table test2
2 segment creation immediate
3 nologging
4 as
5 select rownum as id
6 , to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
7 , trunc(dbms_random.value(0, 100)) as random_id
8 , dbms_random.string('x', 20) random_string
9 from dual
10 connect by level <= 10000000
11 ;
Table TEST2 created.
Elapsed: 00:15:58.255
SQL>
SQL> set timing off
SQL>
SQL> desc test2
Name Null? Type
------------- ----- --------------
ID NUMBER
INC_DATETIME VARCHAR2(19)
RANDOM_ID NUMBER
RANDOM_STRING VARCHAR2(4000)
SQL>
SQL> select count(*) from test2;
COUNT(*)
----------
10000000
SQL> select blocks,num_rows from user_tables where table_name = 'TEST2';
BLOCKS NUM_ROWS
---------- ----------
76567 10000000
SQL> select sum(blocks),sum(bytes)/1024/1024 size_mb from user_segments
2 where segment_name = 'TEST2'
3 and segment_type = 'TABLE';
SUM(BLOCKS) SIZE_MB
----------- ----------
76800 600
Hence my question is why the total number of blocks is a little different from user_tables and user_segments?
Could you give me some advice? Thanks in advance.
Best Regards
Quanwen Zhao