Re:Table of 1.4 gb size returns 0rows [message #64803] |
Sat, 07 February 2004 23:52 |
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 |
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
|
|
|