Table Size

[jmodic]

Originally posted by abhaysk
Ok you got so many KBs?????? ( for jus 5 rows, confused here )...

I realy don't understand your remark. Your query is returning that many of KBs (40 Kbytes for just 5 rows, yes). So why are you confused? Your query is reporting those 5 rows occupy 40 KB, while it is evident to everybody with a bit of common sence that those 5 rows actually require about 100 Kb!

Ok lets see what i get..

US18_DEV_DBA> SELECT value FROM v$parameter
  2  WHERE name = 'db_block_size';

VALUE
------------------------------------------------------------------------
4096

US18_DEV_DBA> SELECT 
  2  COUNT(DISTINCT SUBSTR(ROWID, 1, 15))*4096/1024 
  3  AS "Table Size in KBytes" 
  4  FROM test_2;

Table Size in KBytes
--------------------
                  20

The one which should have taken min of 100KB is well within 20KB??
Thus we need to understand a bit more on how the space is used up....

There's nothing special to understand about to understand about "how the space is used up". It's like I said in the beginning: "Your query tell's us more or less nothing about the size of a tablespace". It's as simple as that. For the same amount of data that realy takes approximately 100KB of space in a table segment your query reported the size to be 40KB in my case and 20 Kb in your case. Both time plain wrong, so it simply sucks, that's all.

[bhallar]

You both guys are right the problem here is, concept of getting actual data thru ROWID query will not work if u will have a chained rows since in the given example of block_size 8K -- 1 row will fit into 2.5 blocks and 5 will in 15 blocks but query is reading one row from the table and thowing one block per record ( as size of one rows exceeds 8K), There is now way to get the another 10 link listed chained blocks.

[stecal]

Originally posted by bhallar
You both guys are right the problem here is, concept of getting actual data thru ROWID query will not work if u will have a chained rows since in the given example of block_size 8K -- 1 row will fit into 2.5 blocks and 5 will in 15 blocks but query is reading one row from the table and thowing one block per record ( as size of one rows exceeds 8K), There is now way to get the another 10 link listed chained blocks.

Still waiting to see how you can do create spfile from pfile after you've shut down the database.
http://www.dbasupport.com/forums/sho...threadid=39801

[abhaysk]

Originally posted by jmodic
I realy don't understand your remark.

In ur post I saw...

Table Size in KBytes
--------------------
          40,0195313

So was the remark..did it report 400195313 KB? huh..

Originally posted by jmodic
There's nothing special to understand about to understand about "how the space is used up". It's like I said in the beginning: "Your query tell's us more or less nothing about the size of a tablespace". It's as simple as that. For the same amount of data that realy takes approximately 100KB of space in a table segment your query reported the size to be 40KB in my case and 20 Kb in your case. Both time plain wrong, so it simply sucks, that's all.

Yes there is some thing we need to understand...Row Chaining is what i meant when i said "Thus we need to understand a bit more on how the space is used up" -- Quite though i expected you would have hinted that..

And Yes as "bhallar" said ROWID really cant be used in cases of too many Row Chains..

And most often, in any application that i saw, will not have too much of chaining...so one can evaluate almost accurate no of blocks used up and thus size ( PS i aint considering effciency of block utilization )

Abhay.

[jmodic]

Originally posted by abhaysk
In ur post I saw...

Table Size in KBytes
--------------------
          40,0195313

So was the remark..did it report 400195313 KB? huh..

Ah. There is a concept called "National Language Support" in Oracle. And as you might know, not everyone's got that set to American/America or something like that, where NLS_NUMERIC_CHARACTERS by default is set to '.,'. There are many countries that uses just the opposite combination - "." for thousands separator and "," as decimal character. And I can't see how you drived number "40,0195313" to mean "400195313" - it simply can't be, even if "," would be thousands sepparator. Everyone that saw this number must have clearly realised that ',' could only be a decimal character here, no other possibility. Don't you think? (And everyone could also noticed that I mistyped the 8K block size number in that query - used 8196 instead of 8192).

[abhaysk]

Originally posted by jmodic
Ah. There is a concept called "National Language Support" in Oracle. And as you might know, not everyone's got that set to American/America or something like that, where NLS_NUMERIC_CHARACTERS by default is set to '.,'. There are many countries that uses just the opposite combination - "." for thousands separator and "," as decimal character. And I can't see how you drived number "40,0195313" to mean "400195313" - it simply can't be, even if "," would be thousands sepparator. Everyone that saw this number must have clearly realised that ',' could only be a decimal character here, no other possibility. Don't you think? (And everyone could also noticed that I mistyped the 8K block size number in that query - used 8196 instead of 8192).

I quite really didnt notice u mis typed 8K as 8196..

and abt the seperator, i was wondering it to be thoushand or some thing like it..so i thought how on earth wud u get so many (PS i did have a feeling that it might be decimal seperator but 8K/1K will be 8 so how on earth will that lead to decimal, and person like u wont do any mistakes in specifing block size so overlooked that one and was wondering) ...