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.

Answer 1

You could do this:

select distinct bytes/blocks from user_segments;

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

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;

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