Creating tablespace with 16K blocksize

Creating tablespace with 16K blocksize

===============================

How To Create 16K tablespace

===============================

Check on current status:

SELECT TABLESPACE_NAME, BLOCK_SIZE FROM DBA_TABLESPACES;

TABLESPACE_NAME                BLOCK_SIZE

------------------------------ ----------

SYSTEM                               8192

SYSAUX                               8192

UNDOTBS                              8192

TEMPORARY                            8192

IGT_TABLE                            8192

IGT_INDEX                            8192

WORKAREA                             8192

IGT_TABLE_BIG                       16384

GG_TBS                               8192

Check on space usage

SELECT TABLESPACE_NAME, 

       ROUND(SUM(BYTES)/1024/1024) used_mb, 

       ROUND(SUM(MAXBYTES)/1024/1024) max_mb 

  FROM DBA_DATA_FILES 

GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME                   USED_MB     MAX_MB

------------------------------ ---------- ----------

IGT_TABLE                            9200      24000

IGT_INDEX                            3000      20000

WORKAREA                              900       6000

SYSAUX                               1000       6000

GG_TBS                                100          0

UNDOTBS                              9400      12000

SYSTEM                                700       6000

IGT_TABLE_BIG                         100          0

 SELECT FILE_NAME, 

        ROUND(SUM(BYTES)/1024/1024) used_mb, 

        ROUND(SUM(MAXBYTES)/1024/1024) max_mb 

   FROM DBA_DATA_FILES  

   GROUP BY FILE_NAME;

FILE_NAME                                          USED_MB  MAX_MB

----------------------------------------------- ---------- -------

/oracle_db/db1/db_igt/ora_igt_table_01.dbf            9200   24000

/oracle_db/db1/db_igt/ora_igt_index_01.dbf            3000   20000

/oracle_db/db1/db_igt/ora_sysaux_01.dbf               1000    6000

/oracle_db/db1/db_igt/ora_workarea_01.dbf              900    6000

/oracle_db/db1/db_igt/ora_igt_table_big_01.dbf         100       0

/oracle_db/db1/db_igt/data/ora_gg_tbs_01.dbf           100       0

/oracle_db/db1/db_igt/ora_system_01.dbf                700    6000

/oracle_db/db1/db_igt/ora_undotbs_01.dbf              9400   12000

ALTER SYSTEM SET db_16k_cache_size = 1G SCOPE=BOTH;

SELECT name, value 
  FROM V$PARAMETER 
 WHERE name = 'db_16k_cache_size';

db_16k_cache_size
-----------------
1073741824

CREATE TABLESPACE IGT_TABLE_BIG LOGGING DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_big_01.dbf' SIZE 100M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO  BLOCKSIZE 16k;

Use the new tablespace:

impdp user/passw@orainst DIRECTORY=IG_EXP_DIR LOGFILE=imp_user.log DUMPFILE=exp_full_20210729.dmp TABLES=BIG_TABLE REMAP_TABLESPACE=IGT_TABLE:IGT_TABLE_BIG

ALTER TABLE USER.SGA_W_IPN_SUBSCRIBER MOVE TABLESPACE IGT_TABLE_BIG;

===============================

When to use  tablespace with 16k block

===============================

As rule of Thumb:

1. Use 8K block size for OLTP applications

2. Use 16K Data Warehouse applications. Optionally use 32K

3. For mixed block sizes, it is possible, provided there is enough SGA to create buffer caches for each block size in the database.

In Details, when to use and not to use 16k blocksize

1. Full Table Scans - no difference between 8k and 16k

2. For Index Range scans - no major difference between 8k and 16k

3. When there is row chaining having 16K block size might be applicable. And need to review application design.

4. The minus in using 8k and 16k tablespaces, is the need to manage their buffer cache manually.