Home » Infrastructure » Linux » How to convert Blocks to MB (Oracle 10g)
How to convert Blocks to MB [message #496943] Thu, 03 March 2011 04:22 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3211
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 #496974 is a reply to message #496943] Thu, 03 March 2011 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 66934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to join with dba_segments to know the tablespace then join to dba_tablespaces to know the tablespace block size.

Regards
Michel
Re: How to convert Blocks to MB [message #497053 is a reply to message #496967] Thu, 03 March 2011 18:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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 #497118 is a reply to message #497071] Fri, 04 March 2011 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A table may not have its tablespace_name column set in %_tables view (this column should not be in this table), its associated segment/s has/have; this is why I suggested to join with dba_segments (even with this it does cover all the cases).

Regards
Michel
Re: How to convert Blocks to MB [message #497172 is a reply to message #497118] Fri, 04 March 2011 04:25 Go to previous messageGo to next message
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 #497194 is a reply to message #497172] Fri, 04 March 2011 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Both are wrong.

Regards
Michel
Re: How to convert Blocks to MB [message #497253 is a reply to message #497194] Fri, 04 March 2011 11:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
8/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.


Re: How to convert Blocks to MB [message #497625 is a reply to message #497253] Sun, 06 March 2011 10:23 Go to previous message
alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia
Junior Member
Barbara Boehmer wrote on Fri, 04 March 2011 11:38
8/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.

Previous Topic: send mail script- Help
Next Topic: problem in oracle installation
Goto Forum:
  


Current Time: Sun Apr 05 22:19:19 CDT 2020