CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Владислав Крутенко
Владислав Крутенко

Reputation: 25

Oracle table size

I am new to SQL, currently using Oracle SQL Developer. How can I get the size of my table in it? I know about this query:

SELECT BYTES/1024
    FROM dba_segments
    WHERE segment_type='TABLE' and segment_name='T_LOG';

(T_LOG is my table) However this returns 64 KB for an empty table. I get, that the empty table still takes space, but 64 KB seems too much to me, especially compared to other database servers, which have around 8 KB. Then I found this query, which returns the block sizes of the files containing the tables:

SELECT NAME "Filename",
    BLOCK_SIZE/1024 "Block size"
    FROM v$datafile;

I don't actually know what BLOCK_SIZE really is but this returns 8 KB which is much more credible to me. Now is there any other query to get the size of the table not using dba_segments or are the tables actually created taking up 64 KB in Oracle?

Upvotes: 0

Views: 6301

Answers (3)

Chamly Lorence
Chamly Lorence

Reputation: 1

dba_segments view contains information about segments across the entire database. To execute this query, you usually need to have the necessary privileges, such as being a DBA or having access to the DBA role.

select bytes/1024/1024 MB from dba_segments where segment_name='TABLE_NAME' ;

Run this query with the necessary privileges to get Table size

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191560

The Concepts guide has an overview of how objects, segments, extents and blocks are related.

Your table has a logical segment, which has one or more extents, and those extents have one or more data blocks (in data files). Your data file has an 8K blocksize, but your tablespace has an initial extent of 64K, which is 8 blocks. You can see the initial/next extent sized in the dba_tablespaces view, e.g.:

select tablespace_name, initial_extent, next_extent
from dba_tablespaces;

You can create a table using no space by specifying `segment creation deferred'. The first extent is then not allocated until you actually insert some data into it.

With immediate creation:

create table t_log (id number) segment creation immediate;

select bytes
from user_segments
where segment_type='TABLE' and segment_name='T_LOG';

     BYTES
----------
     65536

select extent_id, bytes, blocks
from user_extents
where segment_type='TABLE' and segment_name='T_LOG';

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8

With deferred segment creation:

create table t_log (id number) segment creation deferred;

select bytes
from user_segments
where segment_type='TABLE' and segment_name='T_LOG';

no rows selected

select extent_id, bytes, blocks
from user_extents
where segment_type='TABLE' and segment_name='T_LOG';

no rows selected

insert into t_log(id) values (42);

select bytes
from user_segments
where segment_type='TABLE' and segment_name='T_LOG';

     BYTES
----------
     65536

select extent_id, bytes, blocks
from user_extents
where segment_type='TABLE' and segment_name='T_LOG';

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8

If you delete data from a table then the extents/blocks are not released, and will be reused by later inserts (usually). If you truncate the table then all the data is removed and all but the initial extent are freed. (But be careful with truncate, as it can't be rolled back.)

Upvotes: 1

EdStevens
EdStevens

Reputation: 3872

Space is allocated in 'extents'. An extent is a series of contiguous blocks, so will be some multiple of your block size. Even when a table is initially created, it will have at least one extent allocated to it. 64K for one extent does not seem odd at all. 

select bytes,
       blocks,
       extents,
       initial_extent
from dba_segments
where .....

Bone up on this section of the Database Concepts guide: https://docs.oracle.com/database/121/CNCPT/part_dbarch.htm#CNCPT88984

Upvotes: 1

Related Questions