Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why the total number of blocks is a little different from user_tables and user_segments?

Quanwen ZhaoJun 4 2018 — edited Jun 5 2018

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

This post has been answered by unknown-7404 on Jun 4 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 3 2018
Added on Jun 4 2018
11 comments
857 views