Printable View
Hi,
The actual space occupied by table data is = blocks * db_block_size
Where blocks we get from dba_tables.
Also the Total size we get from the field, bytes from DBA_SEGMENTS, initial_extent and next_extent from DBA_TABLES
In the similar way how to find , how much space occupied by the index in a table..? There is no field blocks in DBA_INDEXES..?
Its as simple as that:
SELECT bytes FROM dba_segments WHERE segment_name = :your_index_name;
You'll get the exact and accurate size of an index.
If I am wrong please rectify me.
If I give the following command
SELECT bytes FROM dba_segments WHERE segment_name = :your_table_name;
It gives me the space occupied by the table. Not the actually occupied by the data. To find actually occupied by the data we need to multiply blocks from DBA_TABLES with DB_BLOCK_SIZE.
But for INDEX if I give the following command
SELECT bytes FROM dba_segments WHERE segment_name = :your_index_name;
Will it give me the actual space occupied by index.
For more specific example, if I specify initial and next as 2MB for table creation, and after that I insert some records in the table, then the following command gives me
SELECT bytes FROM dba_segments WHERE segment_name = :your_table_name;
the result 2MB.
But actually space occupied by data is .01 MB which is blocks (which is 1) * 8k (db_block_size)
In the similar way if I create an index with initial and next as 2MB.
SELECT bytes FROM dba_segments WHERE segment_name = :your_index_name;
command gives me 2MB. Are you telling me that the Index it self has taken complete space of 2MB...?
If I understand you correctly, you want to find the number of blocks that actually contain any data of a particular table/index.
Well, for tables I know for sure: there is no *general* method to get the exact answer, short of dumping all the data blocks below the highwatter mark of a table. None. Multiplying dba_tables.blocks*blocksize does not give you any accurate answer.
I don't think there is anything different with indexes.
Hi ,
You are right.
But if I import the table which was exported with compress, then it will exactly take the number of blocks, may be some short fall will be there, I mean last block may not be filled up completely.
In this case if it takes 1 block, then I will be sure that the data is not more than 8K which is my DB_BLOCK_SIZE.
But if I select bytes from DBA_SEGMENT it still will show 2MB , which is INITIAL extent size.
Any way hope there is no way out, to find the same for Index. Thanks for your reply.
Neither DBA_SEGMENTS nor DBA_TABLES would give you the actual no. of blocks used.
Here is the work around to get the actual no. of blocks used.
This is written for Oracle8 and will not work for Oracle7 because the ROWID format is different, however it can be easily modified to use with Oracle7 also.
SanjayCode:select count(distinct(substr(rowid,7,3)||substr(rowid,10,6)))
from :your_table_name;
Try DBMS_SPACE.UNUSED_SPACE()Quote:
Originally posted by jmodic
If I understand you correctly, you want to find the number of blocks that actually contain any data of a particular table/index.
Well, for tables I know for sure: there is no *general* method to get the exact answer, short of dumping all the data blocks below the highwatter mark of a table. None. Multiplying dba_tables.blocks*blocksize does not give you any accurate answer.
I don't think there is anything different with indexes.
Hi,
You can do the following:
1. Create a table:
create table vj_test(no number, name varchar2(20))
storage(initial 1m next 1m pctincrease 0 minextents 3) ;
2. Issue a select statement from user_segments.
select blocks, bytes, extents from user_segments where
segment_name='VJ_TEST'
SQL> /
BLOCKS BYTES EXTENTS
---------- ---------- ----------
384 3145728 3
3. Analyze the table.
SQL> analyze table vj_test compute statistics ;
4. Insert some records into the table and reanalyze the
table and then query USER_TABLES:
SQL> begin
2 for i in 1..100 loop
3 insert into vj_test values(i,'Name'||i) ;
4 end loop ;
5 end ;
6 /
PL/SQL procedure successfully completed.
SQL> select count(*) from vj_test ;
COUNT(*)
----------
100
SQL> analyze table vj_test compute statistics ;
Table analyzed.
SQL> select blocks, empty_blocks from user_tables where table_name='VJ_TEST' ;
BLOCKS EMPTY_BLOCKS
---------- ------------
1 382
5. Issue the following query:
SQL> select bytes, blocks, extents from user_segments where segment_name='VJ_TEST_NO_PK' ;
BYTES BLOCKS EXTENTS
---------- ---------- ----------
16384 2 1
This shows that for the index Oracle shows the no. of blocks allocated(since it took the default values for the storage parameters).
You can also check the same thing using DBMS_SPACE.UNUSED_SPACE package:
SQL> var total_blocks number ;
SQL> var total_bytes number ;
SQL> var unused_blocks number ;
SQL> var unused_bytes number ;
SQL> var file_id number ;
SQL> var block_id number ;
SQL> var last_used_block number ;
SQL> exec dbms_space.unused_space('SYSTEM','VJ_TEST','TABLE',:total_blocks,:total_bytes,:unused_bloc
ks,:unused_bytes,:file_id,:block_id,:last_used_block) ;
SQL> select :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, :file_id, :block_id, :last_u
sed_block from dual ;
:TOTAL_BLOCKS :TOTAL_BYTES :UNUSED_BLOCKS :UNUSED_BYTES :FILE_ID :BLOCK_ID
------------- ------------ -------------- ------------- ---------- ----------
:LAST_USED_BLOCK
----------------
384 3145728 382 3129344 1 18405
2
HTH.
Vijay.
Later method is faster :)
This gives the number of used blocks only under certain conditions. Consider the following example:Quote:
Originally posted by SANJAY_G
Code:select count(distinct(substr(rowid,7,3)||substr(rowid,10,6)))
from :your_table_name;
See, your query still reports that only one single block is occupied by table data. But it is more than clear that you can't stuff that row consisting of 4x4000 bytes into one block! It is because that row is chained into 2 or even 3 database blocks, yet it still have only one rowid.Code:SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> CREATE TABLE bla (c1 VARCHAR2(4000),
2 c2 VARCHAR2(4000),
3 c3 VARCHAR2(4000),
4 c4 VARCHAR2(4000));
Table created.
SQL> INSERT INTO bla(c1) VALUES (RPAD('1',4000,'1'));
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT COUNT(DISTINCT(SUBSTR(ROWID,7,3)||SUBSTR(ROWID,10,6)))
2 FROM bla;
COUNT(DISTINCT(SUBSTR(ROWID,7,3)||SUBSTR(ROWID,10,6)))
------------------------------------------------------
1
SQL> UPDATE bla SET c2 = RPAD('2',4000,'2'),
2 c3 = RPAD('3',4000,'3'),
3 c4 = RPAD('4',4000,'4');
1 row updated.
SQL> commit;
Commit complete.
SQL> SELECT COUNT(DISTINCT(SUBSTR(ROWID,7,3)||SUBSTR(ROWID,10,6)))
2 FROM bla;
COUNT(DISTINCT(SUBSTR(ROWID,7,3)||SUBSTR(ROWID,10,6)))
------------------------------------------------------
1
SQL>
And chaned/migrated rows are not the only cases where the above query gives incorrect results - there are quite a few other cases as well.
Neither of the two methods (selecting from data dictionary or using DBMS_SPACE) give you any information about the number of blocks that contain actual data. They only report you the number opf blocks below and above HWM, nothing more. You can verify this very easily, do the following:Quote:
Originally posted by oravijay
Hi,
You can do the following:
....
DELETE FROM vj_test; -- delete all rows
ANALYZE TABLE vj_test compute STATISTICS;
Now perform your queries from data dictionary and call DBMS_SPACE.UNUSED_SPACE and see what you get! It should report that all blocks are free, but it is not what your methods actually show.
Agree with you, jmodic. My query WILL return inaccurate results if tere are chained rows in table.
Sanjay