Query my block size oracle

Question

Is there a way I can tell the block size of my Oracle database if I don't have access to the v$parameter view?

Thanks!

f.

Solution 1

You could do this:

select distinct bytes/blocks from user_segments;

Solution 2

Since the block size is different for each tablespace you better use the following query :

select block_size, tablespace_name from dba_tablespaces;

Solution 3

In oracle, the block size is set per tablespace. You can query the user_tablespaces or dba_tablespaces (if you have access) and see the block size for each tablespace; these views usually run instantly.

You can also join either of those 2 to dba_tables, user_tables, or all_tables and multiply the number of blocks by the block size to get the total size of the table in bytes (divide by 1024*1024 to get size in MB, etc.)

Comments

[ibre5041]

Note: Oracle supports different blocksize for each tablespaspace. So the valuse in v$parameter is just a "default".

[Guido Leenders]

This query is very expensive, better consider something like select value from v$parameter where name = 'db_block_size'

[Tony Andrews]

@GuidoLeenders: "if I don't have access to the v$parameter view"!

[Tony Andrews]

Really? He wanted to know the block size and didn't have access to v$parameter view. Even if the query against user_segments took 5 minutes he still had an answer in 5 minutes. Unless there is a peculiar need to check the database block size several times a day it seems OTT to ask the DBA for a privilege just to find out more quickly?