Home » RDBMS Server » Performance Tuning » Re:Table of 1.4 gb size returns 0rows
Re:Table of 1.4 gb size returns 0rows [message #64803] Sat, 07 February 2004 23:52 Go to next message
paramaguru_k
Messages: 1
Registered: February 2004
Junior Member
Hi Friends

 I amd facing an new problem .There is an table which is size 1.4 gb on querying data dictionary gives 0 rows on passing  count on that table and returns 0 rows in dab_tables on analysing that table

if you have any idea about that one plz reply

Paramaguru

 

 
Re:Table of 1.4 gb size returns 0rows [message #64804 is a reply to message #64803] Sun, 08 February 2004 03:23 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
thiru@9.2.0:SQL>create table t (x int) storage(initial 5m next 5m);

Table created.

-- User_Segments shows 5M, the allocated size, but there is no data yet

thiru@9.2.0:SQL>select segment_name,bytes from user_Segments where segment_name='T';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                 5242880

thiru@9.2.0:SQL>select count(*) from t;

  COUNT(*)
----------
         0

thiru@9.2.0:SQL>analyze table t compute statistics;

Table analyzed.

-- User_tables shows 0 blocks, the maximum used 

thiru@9.2.0:SQL>select table_name,blocks from user_Tables where table_name='T';

TABLE_NAME                         BLOCKS
------------------------------ ----------
T                                       0

-- Lets insert some data

thiru@9.2.0:SQL>insert into t select rownum from all_objects;

29798 rows created.

thiru@9.2.0:SQL>commit;

Commit complete.

-- the overall size of the table doesnt increase becos it uses space within the allocated size 5M

thiru@9.2.0:SQL>select segment_name,bytes from user_Segments where segment_name='T';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                 5242880

thiru@9.2.0:SQL>analyze table t compute statistics;

Table analyzed.

-- User_tables shows 122 used blocks now

thiru@9.2.0:SQL>select table_name,blocks from user_Tables where table_name='T';

TABLE_NAME                         BLOCKS
------------------------------ ----------
T                                     122

-- Lets reset the High water mark.

thiru@9.2.0:SQL>truncate table t;

Table truncated.

thiru@9.2.0:SQL>analyze table t compute statistics;

Table analyzed.

thiru@9.2.0:SQL>select table_name,blocks from user_Tables where table_name='T';

TABLE_NAME                         BLOCKS
------------------------------ ----------
T                                       0

-- Now there is no user data in the table. Zero used blocks.

-- Still the initial allocated size remains the same

thiru@9.2.0:SQL>select segment_name,bytes from user_Segments where segment_name='T';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                 5242880



-Thiru
Previous Topic: Criteria for Initial Extents and Next Extents of Blank Table and Having Rows in above 8 Lakhs
Next Topic: 1oo Hit Ratio
Goto Forum:
  


Current Time: Wed Jan 27 11:12:41 CST 2021