Table Size

Hi,
1. How can I check the size of a table? is it using the initial_extents from user_tables?

2. How to reorg a table if it grow too large?

Please advise.

Thank in advance.

1)
Select sum(bytes) from dba_segments where segment_name = ;
or
If u need actual blocks used (below the HWM) for that table do the following
SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) "Used" FROM table_name;

2) Export/Import or MOVE command.

1)

Code:

Select Count(Distinct Substr(Rowid, 1, 15))*{Block_Size}/1024/1024 "Table Size in MB" from {Table_Name} ;

2) Move.

Quote:

Originally posted by abhaysk
1)

Code:

Select Count(Distinct Substr(Rowid, 1, 15))*{Block_Size}/1024/1024 "Table Size in MB" from {Table_Name} ;

I wonder what this query is supposed to tell you about the table size in MB.... More or less nothing. But we've been through that many times in this forum....

Here's a script that will tell you the allocated space for the
table and the used space for the table ...

Change the OWNER, TABLE_NAME in the top of the script for your
structure

/**************************************************/
/* Find the highwater mark in a table */
/* ALTER TABLE dquon DEALLOCATE UNUSED KEEP 20K; */
/* gb - PSAConsulting */
/**************************************************/

set serveroutput on

declare
v_user varchar2(50) := 'SYSADM';
v_object_name varchar2(50) := 'PART';
v_object_type varchar2(15) := 'TABLE';
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;
v_partition_name varchar2(50);
v_initial number;
v_next number;
v_number_extents number;
v_num_rows number;
v_avg_row_len number;
v_tablespace_name varchar2(30);

begin

IF v_object_type = 'TABLE'
THEN
SELECT initial_extent,next_extent,num_rows,avg_row_len, tablespace_name
INTO v_initial, v_next, v_num_rows, v_avg_row_len, v_tablespace_name
FROM all_tables
WHERE owner = v_user
AND table_name = v_object_name;
ELSE
SELECT initial_extent,next_extent, tablespace_name
INTO v_initial, v_next, v_tablespace_name
FROM all_indexes
WHERE owner = v_user
AND index_name = v_object_name;
END IF;

SELECT count(*)
INTO v_number_extents
FROM dba_extents
WHERE owner = v_user
AND segment_name = v_object_name;

DBMS_SPACE.UNUSED_SPACE( v_user
,v_object_name
,v_object_type
,v_total_blocks
,v_total_bytes
,v_unused_blocks
,v_unused_bytes
,v_last_used_extent_file_id
,v_last_used_extent_block_id
,v_last_used_block
,v_partition_name );

dbms_output.put_line('Object Name: '||v_object_name||' - '||v_object_type||' Tablespace: '||v_tablespace_name);
dbms_output.put_line('. ');
dbms_output.put_line('Number of Extents: '||v_number_extents||' Initial: '||v_initial||' Next: '||v_next);
dbms_output.put_line('......Approx Rows: '||v_num_rows||' Avg Row Len: '||v_avg_row_len);
dbms_output.put_line('Blocks in segment: '||v_total_blocks);
dbms_output.put_line('Bytes in segment(Size): '||v_total_bytes);
dbms_output.put_line('MegaBytes in segment: '||v_total_bytes/1048576);
dbms_output.put_line('Blocks not used: '||v_unused_blocks);
dbms_output.put_line('Bytes not used: '||v_unused_bytes);
dbms_output.put_line('MegaBytes not used: '||v_unused_bytes/1048576);
dbms_output.put_line('File ID - last extent with data(Block): '||v_last_used_extent_file_id);
dbms_output.put_line('File ID - last extent with data(Byte): '||v_last_used_extent_block_id);
dbms_output.put_line('Last block with data: '||v_last_used_block);

end;
/

HTH
Gregg

SELECT a.table_name,
a.tablespace_name,
ROUND(SUM (b.bytes) / 1024 / 1024, 3) gigabytes
FROM user_tables a,
user_extents b
WHERE a.table_name = b.segment_name
AND a.tablespace_name = b.tablespace_name
GROUP BY a.tablespace_name, a.table_name
ORDER BY gigabytes DESC, table_name ASC;

This will show all the tables sizes in a schema. Modify it to show only the table you want.

Re: Table Size

Quote:

Originally posted by gschuah
2. How to reorg a table if it grow too large?

I guess it depends what steps are acceptable to you.

Obviously, you could delete data. I expect that isn't what you meant, though.

If you have a table for which the data is pretty static, meaning it's hardly ever modified, then you could consider compressing it.

If you have been deleting data and the free space is not getting reused, then you may have too low a PCTUSED value, which sets the used-space threshold below which a block can be reused for the insert of new rows.

To do a "one off" removal of the empty space you could use the "move table" command to get he gaps refilled -- "alter table my_table move pctfree 10 pctused 85" would rebuild the table and change pctused at the same time.

If you have columns which are commonly null, then you could place them as the last columns in the table definition.

Quote:

Originally posted by jmodic
I wonder what this query is supposed to tell you about the table size in MB.... More or less nothing. But we've been through that many times in this forum....

Wud'nt that query tell you how many blocks it has used, I mean the blocks that actually the table has data and not allocted, ofcourse allocated size will be higher than this..

I am not sure wat is that u wana tell?

Well what i meant by the ** "Table Size in MB" ** was that the actual size used by table. I wonder wat is the mistake in knowing actual Size as u can always query DBA views to know Allocated one.

Abhay.

Quote:

Originally posted by abhaysk
I am not sure wat is that u wana tell?

Ok, let's see:

Code:

SQL> SELECT value FROM v$parameter 2 WHERE name = 'db_block_size'; VALUE -------------------------------------------- 8192 SQL> CREATE TABLE test1 2 (c1 VARCHAR2(4000), 3 c2 VARCHAR2(4000), 4 c3 VARCHAR2(4000), 5 c4 VARCHAR2(4000), 6 c5 VARCHAR2(4000)) 7 PCTFREE 0; Table created. SQL> INSERT INTO test1 (c1,c2,c3,c4,c5) VALUES 2 (RPAD('x',4000,'x'), 3 RPAD('x',4000,'x'), 4 RPAD('x',4000,'x'), 5 RPAD('x',4000,'x'), 6 RPAD('x',4000,'x')); 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT COUNT(*) FROM test1; COUNT(*) ---------- 5

So what we have now is a table with 5 rows in it, each row holding 5 columns, each of them filled with 4000 bytes. So each row should occupy about 20 Kbytes, all of them together abot 100 Kbytes. And what does your query return?

Code:

SQL> SELECT 2 COUNT(DISTINCT SUBSTR(ROWID, 1, 15))*8196/1024 3 AS "Table Size in KBytes" 4 FROM test1; Table Size in KBytes -------------------- 40,0195313 SQL>

It is more than evident that those 5 rows can not fit into just 40 Kbytes.

Quote:

Well what i meant by the ** "Table Size in MB" ** was that the actual size used by table. I wonder wat is the mistake in knowing actual Size

Another problematic term - "actual size used by table". What is the "actual size used by table"? Sum of all table blocks that contain at least one row? Doesn't make sence to me, as it tells you nothing about how full those blocks actually are. Your query returns the same result no matter if each block contains only one tiny record of few bytes (and each block still has more than 95% free space left for new records or for updates of existing one, for example) or if each block is 100% fully packed with data, not able to accept a single byte more.

Quote:

Originally posted by jmodic
Ok, let's see:
So what we have now is a table with 5 rows in it, each row holding 5 columns, each of them filled with 4000 bytes. So each row should occupy about 20 Kbytes, all of them together abot 100 Kbytes. And what does your query return?

Code:

SQL> SELECT 2 COUNT(DISTINCT SUBSTR(ROWID, 1, 15))*8196/1024 3 AS "Table Size in KBytes" 4 FROM test1; Table Size in KBytes -------------------- 40,0195313 SQL>

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

Ok lets see what i get..

Code:

US18_DEV_DBA> SELECT value FROM v$parameter 2 WHERE name = 'db_block_size'; VALUE ------------------------------------------------------------------------ 4096 US18_DEV_DBA> US18_DEV_DBA> CREATE TABLE test_2 2 (c1 VARCHAR2(4000), 3 c2 VARCHAR2(4000), 4 c3 VARCHAR2(4000), 5 c4 VARCHAR2(4000), 6 c5 VARCHAR2(4000)) 7 PCTFREE 0; Table created. US18_DEV_DBA> US18_DEV_DBA> INSERT INTO test_2 (c1,c2,c3,c4,c5) VALUES 2 (RPAD('x',4000,'x'), 3 RPAD('x',4000,'x'), 4 RPAD('x',4000,'x'), 5 RPAD('x',4000,'x'), 6 RPAD('x',4000,'x')); 1 row created. US18_DEV_DBA> / 1 row created. US18_DEV_DBA> / 1 row created. US18_DEV_DBA> / 1 row created. US18_DEV_DBA> / 1 row created. US18_DEV_DBA> COMMIT; Commit complete. US18_DEV_DBA> US18_DEV_DBA> SELECT COUNT(*) FROM test_2; COUNT(*) -------------------- 5 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 US18_DEV_DBA> ed Wrote file afiedt.buf 1 SELECT 2 COUNT(DISTINCT SUBSTR(ROWID, 1, 15)) AS "No Of Blocks Used By Table" 3* FROM test_2 US18_DEV_DBA> / No Of Blocks Used By Table -------------------------- 5

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....

Quote:

Originally posted by jmodic
Another problematic term - "actual size used by table". What is the "actual size used by table"? Sum of all table blocks that contain at least one row? Doesn't make sence to me, as it tells you nothing about how full those blocks actually are. Your query returns the same result no matter if each block contains only one tiny record of few bytes (and each block still has more than 95% free space left for new records or for updates of existing one, for example) or if each block is 100% fully packed with data, not able to accept a single byte more.

Its upto one how one thinks, if you want the effeciency of the utilazation of blocks then you gotta take an AVG & interpret then
But i dont think the original poster wanted that, if yes then we can giv him that query as well !!

Abhay.

Quote:

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!

Quote:

Ok lets see what i get..

Code:

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.

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.

Quote:

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

Quote:

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

In ur post I saw...

Code:

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

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

Quote:

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.

Quote:

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

Code:

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). :o

Quote:

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). :o

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) ...

:rolleyes: :rolleyes: