| How to convert Blocks to MB [message #496943] |
Thu, 03 March 2011 04:22  |
 |
alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia
|
Junior Member |
|
|
I'm confusing about the blocks how to convert to MB, some of the website saying that blocks*8/1024 and blocks*512/1024/1024 , please help me and guide me which one is correct.
Besides, i got researched some instance of sql statement to get the table blocks size and it is as the following.
SQL> select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
2 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"
3 from dba_tables
4 where owner='RAJA';
He was mentioned the actual size and the fragmented space that lead me more confuse about it. Some shows me the proper way to reach my goal.
My goal is to convert blocks to MB where my statement as below.
select table_name , blocks from user_tables order by blocks DESC;
|
|
|
|
| Re: How to convert Blocks to MB [message #496967 is a reply to message #496943] |
Thu, 03 March 2011 05:56  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Depends. A block size can be specified when the database is created (db_block_size), so it can be different in different database.
To find out what it is in your specific database:
SELECT * FROM v$parameter WHERE name = 'db_block_size'
|
|
|
|
|
|
| Re: How to convert Blocks to MB [message #497053 is a reply to message #496967] |
Thu, 03 March 2011 18:59 |
|
alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia
|
Junior Member |
|
|
[ThomasG]
i have tried the sql statement that you provided
SELECT * FROM v$parameter WHERE name = 'db_block_size'
, however it prints out some soft of columns and only hash column displays certain value. May i know what the exact point that you are mentioned?
[Michel Cadot]
I cant get what you are trying to say, can you please more specify for guiding me?
[Updated on: Thu, 03 March 2011 19:02] Report message to a moderator |
|
|
|
| Re: How to convert Blocks to MB [message #497071 is a reply to message #497053] |
Thu, 03 March 2011 20:41 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following examples produce the space allocated to the table, which includes space that is not used. The value or block_size is the number of bytes per block. You multiply the blocks per table by bytes per block for your system, then divide by 1048576 bytes per megabyte, which is the same as 1024 * 1024, to get the megabytes per table.
-- similar to what Thomas described:
SCOTT@orcl_11gR2> column value format a10
SCOTT@orcl_11gR2> select ut.table_name, ut.blocks, vp.value,
2 round ((ut.blocks * vp.value / 1048576), 2) MB
3 from user_tables ut,
4 (select value
5 from v$parameter
6 where name = 'db_block_size') vp
7 order by blocks DESC
8 /
TABLE_NAME BLOCKS VALUE MB
------------------------------ ---------- ---------- ----------
RUN_STATS 8192
SPENDING 13 8192 .1
SALGRADE 5 8192 .04
DEPT 5 8192 .04
BUDGET 5 8192 .04
ORDERS 5 8192 .04
EMP 5 8192 .04
BONUS 0 8192 0
8 rows selected.
-- similar to what Michel described:
SCOTT@orcl_11gR2> select ut.table_name, ut.blocks, dt.block_size,
2 round ((ut.blocks * dt.block_size / 1048576), 2) MB
3 from user_tables ut, dba_tablespaces dt
4 where ut.tablespace_name = dt.tablespace_name
5 order by blocks DESC
6 /
TABLE_NAME BLOCKS BLOCK_SIZE MB
------------------------------ ---------- ---------- ----------
SPENDING 13 8192 .1
EMP 5 8192 .04
BUDGET 5 8192 .04
ORDERS 5 8192 .04
DEPT 5 8192 .04
SALGRADE 5 8192 .04
BONUS 0 8192 0
7 rows selected.
SCOTT@orcl_11gR2>
Note: I initially typed the wrong number of bytes per megabyte. I updated it to reflect the correct number.
[Updated on: Thu, 03 March 2011 20:56] Report message to a moderator |
|
|
|
|
|
| Re: How to convert Blocks to MB [message #497172 is a reply to message #497118] |
Fri, 04 March 2011 04:25 |
|
alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia
|
Junior Member |
|
|
Can i make it like that ?
select table_name , blocks*512/1024/1024 as MB, blocks from user_tables order by blocks DESC;
select table_name , blocks*8/1024 as MB, blocks from user_tables order by blocks DESC;
Which one correct or both are wrong?
[Updated on: Fri, 04 March 2011 06:27] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: How to convert Blocks to MB [message #497625 is a reply to message #497253] |
Sun, 06 March 2011 10:23 |
|
alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia
|
Junior Member |
|
|
Barbara Boehmer wrote on Fri, 04 March 2011 11:388/1024 is the same as the 8192/1048576 on my system, so that would produce the same results as what I posted for my system. 8192 is the default block size, but your system may not have the same block size, which is why you need to use one of the methods previously suggested to find out what the block size is on your system. You need to not just ask which is correct, but try to understand why. The fact that you have repeated the question indicates that you have either not read or not understood the previous answers.
[Barbara] Finally i got what you are trying to say, i have to identify my system block_size first because different system may have diverse block_size, and i have used your provided statement to get my system block_size and it's works perfectly. Thank you so much all of you whose contributes for giving me help and advise to me.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
