Tablespaces that make use of nonstandard block sizes

A block size of 8 KB is optimal for most systems. On the other hand, OLTP systems can use smaller block sizes (such as 4K) and DSS systems can use larger block sizes (such as 16K, 32K). Tablespaces that make use of nonstandard block sizes can be determined by using the following  below script:

set echo off 
feedback off 
pagesize 50
linesize 80
spool  tblspce_with_non_std_blksize.txt
select Tablespace_Name,block_Size from DBA_TABLESPACES
where Block_Size != (select Value from V$PARAMETER where name = 'db_block_size');
spool off

Sample output of running the above script is shown below:
TABLESPACE_NAME                BLOCK_SIZE(K)
------------------------------         ----------
DRSYS                                         8192
TABLESPC4K                              4096
TABLESPC8K                              8192
INDX                                            4096
PROJECTS                                   8192
TOOLS                                        16384
USERS                                         8192

Note:  We can query the parameter db_block_size to determine the standard block size in use for a given database. The above output indicates that the nonstandard block sizes in use for the database are as follows: 

4KB   (tablespaces TABLESPC4K and INDX)
8KB   (tablespaces DRSYS, TABLESPC8K, PROJECTS,and USERS)
16KB (tablespace TOOLS)




Enjoy     :-)