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.

Amphora

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

Answer 1

You could do this:

select distinct bytes/blocks from user_segments;

Girth

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

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

Answer 2

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.)

Heroine

Answer 3

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

select block_size, tablespace_name from dba_tablespaces;

Joejoeann

Answer 4

Yes, then you have a major problem. Querying user_segments can be really slow, especially when running SAP or Infor BAAN that create ten thousands of segments and tend to fragment the data dictionary. Best is to convince your DBA to grant you access in some way, maybe through a view with v_$.

Alternative, which performs better: when you can create segments, you have some tablespace access (please note that the distinct does not work when you don't have any segments). This list is generally shorter, so for instance use:

select bytes/blocks from user_ts_quotas

That still leaves you with a social engineering problem with the local DBA when the user is so restricted that it does not have a quota :-)

Equivalent

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? – Girth