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.
Printable View
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)2) Move.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....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}
;
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.
I guess it depends what steps are acceptable to you.Quote:
Originally posted by gschuah
2. How to reorg a table if it grow too large?
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.
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..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....
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.
Ok, let's see:Quote:
Originally posted by abhaysk
I am not sure wat is that u wana tell?
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 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
It is more than evident that those 5 rows can not fit into just 40 Kbytes.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>
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:
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
Ok you got so many KBs?????? ( for jus 5 rows, confused here )...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 lets see what i get..
The one which should have taken min of 100KB is well within 20KB??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
Thus we need to understand a bit more on how the space is used up....
Its upto one how one thinks, if you want the effeciency of the utilazation of blocks then you gotta take an AVG & interpret thenQuote:
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.
But i dont think the original poster wanted that, if yes then we can giv him that query as well !!
Abhay.