CODEWITHSUNDEEP
oracle-databaseoracle11g
Matt E
Matt E

Reputation: 477

Oracle Table Size without Data

Is it possible to have a table that reports a size but does not have any rows in it? When I run the following query one of the tables reports a size but does not contain any rows. How is this possible?

select table_name, 
       b.tablespace_name,
       sum( bytes)/1024/1024 "SIZE IN MB" 
  from USER_segments a,
       user_tables b 
  where table_name=segment_name 
  group by segment_name,
        b.tablespace_name,
        table_name;

Upvotes: 1

Views: 545

Answers (1)

user123664
user123664

Reputation:

Table segements grow when data is inserted into them. Since 11g a new created table can be created without a segment. When data is inserted into such a newly created table, the segment is created.

The space that is occupied by the segment is not automatically returned to the free space in the datafile when rows are deleted. So, your table is created empty with direct segment creation, or it had rows and they are deleted.

Upvotes: 3

Related Questions