Basic Oracle storage principles in 1 hour

PROBLEM:

You want to learn some basic Oracle storage principles in 30 minutes:

1. Logical & Physical

• Database
• Datafiles
• Tablespace
• Segments
• Extents (and HWM)
• Oracle blocks
• OS blocks
• Schemas

2. Tablespace Types

• SYSTEM
• SYSAUX
• Temporary
• UNDO
• User Defined tablespaces

3. In which Tablespace a new table will be created?

• Explicitly specifing the Tablespace
• If the Tablespace not explicitly specified, new table will be created in user Default Tablespace
• If there is no User Defalt Tablespace, it will be created in the Database Default Tablespace (10g)
• If there is no Database Defalt Tablespace, the table will be created in the System Tablespace

4. Extent Management

• Locally managed VS. Dictionary managed
• Locally managed - Extent allocation: AUTOALLOCATE VS. UNIFORM

5. Resize Tablespace

• Add a new file
• Increase an existing datafile
• Manual Resize - RESIZE
• Automatic resize - AUTOEXTEND

6. Online/Offline, Read Only/Read Write

7. OMF Oracle-Managed Files

SOLUTION:

-- STORAGE - Logical & Physical
-- 1. Database
-- Database ---< Tablespace
-- Tablespace: Within a database the tablespace is the largest logical storage structure. Every database must consist of one or more tablespaces. Every tablespace must belong to one and only -- one database. Tablespace size = sum of data files size

-- Database ---< Datafiles
-- Every Database must consist of one or more datafiles. Each datafile must belong to one and only one Database.

-- 2. Tablespace
-- Tablespace ---< Segments
-- Segment: Within a tablespace, space is allocated to segments. A segment is an object in the database that requires storage, such as a table or an index. A view, on the other hand, is not a -- segment, since it does not store data; it is just a prewritten query that allows easy access to data stored in tables.
-- Every tablespace may contain one or more segments. Every segment must exist in one and only one tablespace.

-- Tablespace ---< Datafiles
-- Every tablespace must consist of one or more datafiles. Each datafile must belong to one and only one tablespace.

-- 3. Segments
-- Segment ---<  Extents
-- Extent: When space is allocated for segments in a tablespace, it is allocated in extent units. Every segment must consist of one or more extents. Each extent must belong to one and only one segment.

-- Segments >---< Datafiles
-- Every datafile consists of one or more segments. Each segmets can belong to one or more datafiles

-- 4. Extents
-- Extents  ---< Oracle blocks (Default 8k)
-- Oracle block: Oracle block is the smallest logical storage unit for every Oracle I/O request at least one Oracle block is being read. Every extent must consist of one or more Oracle blocks. Each Oracle block may belong to one and only one extent.

-- Extents >--- Datafiles
-- Every extent must be located in one and only one datafile. The space in the datafile may be allocated as one or more extents. Delete operations (with / without commit) won't change table High Water Mark (HWM)

-- 5. Oracle blocks
-- Oracle block ---< OS blocks
-- Every Oracle block must consist of one or more operating system blocks. Every operating system block may be part of one and only one Oracle block. It is a good idea to keep the operating -- system block size the same as the Oracle block size. This way, for every Oracle I/O request the operating system needs to retrieve only one block.

-- Datafiles ---< OS Blocks
-- OS Block: Operating system blocks are the minimum allocation unit for the file system. Each file system has its own minimum and default size. Every datafile must consist of one or more operating system blocks. Each operating system block may belong to one and only one datafile.

-- Schemas: There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.

-- Tablespace Types

-- The SYSTEM tablespace
-- * The SYSTEM tablespace contains the data dictionary—internal tables that describe the structure of the database itself, all of the objects in it, users, roles, and privileges.
-- * The SYSTEM tablespace also contains the SYSTEM undo or rollback segment (also called SYSTEM), which can be used only for operations on objects stored in the SYSTEM tablespace.
-- * Oracle Database creates The SYSTEM tablespace automatically when the database is created.
-- * User objects should be kept out of the SYSTEM tablespace in order to keep it operating efficiently.
-- * Cannot be taken offline

-- The SYSAUX tablespace (10g)
-- * The SYSAUX tablespace is also considered a SYSTEM tablespace and is used to store statistical and other Oracle tools information (AWR, OEM and many more)
-- * The SYSAUX tablespace is always created during database creation or database upgrade

-- The Temporary tablespaces (9i)
-- * Temporary tablespaces are used mainly to manage space for database sort operations (Server Process will try to sort data in the PGA by default, if it's not possible it will sort it in the Temporary tablespaces)
-- * When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database.
-- * A locally managed SYSTEM tablespace cannot be used for default temporary storage.
-- * If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage. However, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.
-- * Cannot be taken offline

-- The UNDO tablespaces
-- * Holds information that is used to roll back, or undo, changes to the database
-- * A database can contain more than one undo tablespace, but only one can be in use at any time.
-- * Cannot be taken Offline

-- User Defined tablespaces
-- * User defined tablespaces created by the user

-- Viewing logical & physical storage using System Tables

-- Redo Log Files:
SELECT * FROM v$log;
SELECT * FROM v$logfile;

-- Manual log switch:
-- ALTER SYSTEM SWITCH LOGFILE;

-- Tablespaces and Data files
DESC DBA_TABLESPACES

-- Information about all tablespaces in the DB:
SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES;

-- Information about all data files in the DB:
DESC DBA_DATA_FILES

col file_name for a50
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB
FROM DBA_DATA_FILES;

-- Tablespace Size
-- Allocated Size:
SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb, COUNT(*) files
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;

-- Free Space:
SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;

-- Via OEM: Server => (Storage) => Tablespaces / Datafiles

-- In which Tablespace a new table will be created?

-- Explicitly specifing the Tablespace
CREATE TABLE newtab (id NUMBER) TABLESPACE users;

-- If the Tablespace not explicitly specified, new table will be created in user
-- Defalt Tablespace
SELECT username, DEFAULT_TABLESPACE
FROM dba_users;

-- If there is no User Defalt Tablespace, it will be created in the
-- Database Defalt Tablespace (10g)
SELECT * FROM  database_properties
WHERE PROPERTY_NAME LIKE '%TABLESPACE%'

ALTER DATABASE DEFAULT TABLESPACE example;

-- If there is no Database Defalt Tablespace, the table will be created in the System Tablespace

-- Creating a new Tablespace

CREATE TABLESPACE MY_TBS
DATAFILE 'c:\my_tbs_file1.dbf' SIZE 10M ,
'c:\my_tbs_file2.dbf' SIZE 10M

DROP TABLESPACE MY_TBS ;

DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES ;

CREATE BIGFILE TABLESPACE BIGFILE_TBS
DATAFILE 'C:\BIGFILE.DBF'SIZE 100M ;

DROP TABLESPACE BIGFILE_TBS INCLUDING CONTENTS AND DATAFILES ;

-- Bigfile VS. Smallfile
-- * A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single datafile or tempfile is 32TB (for a tablespace with 8K blocks).
-- * A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks. (32 GB for 8k block)

-- Locally managed VS. Dictionary-managed:

-- Dictionary managed
-- * Dictionary-managed tablespaces are the historical way of managing extents requires any allocation or deallocation of an extent to update a table in the data dictionary.
-- * This means that any time an extent is allocated to a table, the data dictionary must be touched to record the change. When a table is dropped or truncated, the data dictionary must be changed. Because of the amount of work required on the data dictionary when using dictionary-managed extents, they are no longer recommended and exist primarily for backward compatibility.
-- * All tablespaces should be created withlocal extent management

CREATE TABLESPACE dict_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\dicttbs.dbf' SIZE 10m
EXTENT MANAGEMENT DICTIONARY

CREATE TABLE am05 (col1 number)
STORAGE (initial 100mb next 10mb pctincrease 50);

Dictionary managed Extent allocation
INITIAL - size of the first extent (100)
NEXT    - size for the next extent (10)
PCTINCREASE - (15)

-- If System TBS is Locally managed, It's not possible to create DM TBS

-- Locally managed
-- * Locally managed extents are more efficient and are recommended for all database data
-- * In a tablespace where extents are locally managed, free extents are stored in a bitmap in the tablespace
-- * As an extent is allocated to a segment or freed up because a segment was dropped, truncated, or resized, the bitmap is updated to reflect the change

CREATE TABLESPACE auto_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL

-- Locally managed - Extent allocation: AUTOALLOCATE VS. UNIFORM
-- * AUTOALLOCATE, allocation method
-- * First 16  extents --> 64kb
-- * Next  63  extents --> 1MB
-- * Next  120 extents --> 8MB 
-- * Ramaining extents --> 64MB 

-- DROP TABLESPACE auto_tbs INCLUDING CONTENTS AND DATAFILES ;

CREATE TABLESPACE auto_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES;

col FILE_NAME for a40
COL TABLESPACE_NAME for a15
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB
FROM DBA_DATA_FILES;

-- Create a table in the tablespace:
CREATE TABLE auto_tab (a VARCHAR2(3000))
   TABLESPACE auto_tbs;

SELECT TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLESPACE_NAME = 'AUTO_TBS';

-- See how much space is allocated to the table:
col SEGMENT_NAME for a30
SELECT SEGMENT_NAME, BYTES/1024, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'AUTO_TAB';

-- See how many extents:
DESC DBA_EXTENTS
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'AUTO_TAB';

-- Fill up the table with data:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO AUTO_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

-- Check segment size and extents again...

-- How to drop Tablespace

DROP TABLESPACE auto_tbs
DROP TABLESPACE auto_tbs INCLUDING CONTENTS

-- but what about the datafiles ?
-- Creating the tablespace again with the [REUSE] option
CREATE TABLESPACE auto_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M
REUSE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

-- 1 [DROP TABLESPACE] and remove data files manually
-- 2 DROP TABLESPACE auto_tbs INCLUDING CONTENTS AND DATAFILES


CREATE TABLE [STORAGE]

CREATE TABLE mytab2 (id number)
STORAGE (INITIAL 100m);

SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'MYTAB2';

DROP TABLE MYTAB2

-- Advantages
-- 1. reduce I/O when the table is being scanned
-- 2. Improve performance when Oracle allocates the extents

-- Local - UNIFORM SIZE

DROP TABLESPACE uniform_tbs INCLUDING CONTENTS AND DATAFILES ;

CREATE TABLESPACE uniform_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\uni_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE, NEXT_EXTENT / 1024
FROM DBA_TABLESPACES;

-- Create a new table in it:
CREATE TABLE uniform_tab (a VARCHAR2(3000))
TABLESPACE uniform_tbs;

-- See how much space is allocated to the table:
SELECT SEGMENT_NAME, BYTES/1024, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'UNIFORM_TAB';

-- See how many extents:
col segment_name for a15
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'UNIFORM_TAB';

-- Fill up the table with data:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

-- See how many extents:
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'UNIFORM_TAB';

-- Other tablespaces:
SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES;

-- Check free space:
SELECT TABLESPACE_NAME, BYTES/1024/1024
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'UNIFORM_TBS';

RESIZE TABLESPACE
-- Fill up:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/

[ORA-01653: unable to extend table SYS.UNIFORM_TAB by 256 in tablespace]

-- Add a new file

ALTER TABLESPACE UNIFORM_TBS
 ADD DATAFILE  'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' size 25m;

ALTER TABLESPACE UNIFORM_TBS
 DROP DATAFILE  'C:\UNIFORM_TBS02.DBF' ;

-- Fill up:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

SELECT FILE_NAME, BYTES/1024/1024 File_Size, MAXBYTES/1024/1024 Max_Size,
AUTOEXTENSIBLE, INCREMENT_BY*8/1024 Growth
FROM dba_data_files
WHERE TABLESPACE_NAME = 'UNIFORM_TBS';

-- Fill up again:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

-- Increase an existing datafile, Manual Resize  - RESIZE

ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' RESIZE 50M;

ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' RESIZE 30M;

SELECT FILE_NAME, BYTES/1024/1024 File_Size, MAXBYTES/1024/1024 Max_Size,
AUTOEXTENSIBLE, INCREMENT_BY*8/1024 Growth
FROM dba_data_files
WHERE TABLESPACE_NAME = 'UNIFORM_TBS';

SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb, COUNT(*) files
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;

BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/

-- Automatic resize - AUTOEXTEND

ALTER DATABASE
 DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF'
 AUTOEXTEND ON NEXT 10m MAXSIZE 150m;

DROP TABLESPACE uniform_tbs INCLUDING CONTENTS AND DATAFILES ;

CREATE TABLESPACE uniform_tbs
 DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\uni_tbs.dbf' SIZE 25M
 AUTOEXTEND ON NEXT 10M ;

CREATE TABLE uniform_tab (a VARCHAR2(3000))
 TABLESPACE uniform_tbs;

SELECT SEGMENT_NAME, BYTES/1024, EXTENTS
 FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = 'UNIFORM_TAB';

col segment_name for a15
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
 FROM DBA_EXTENTS
 WHERE segment_name = 'UNIFORM_TAB';

BEGIN
    FOR i IN 1..7500 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/

SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
  FROM DBA_EXTENTS
  WHERE segment_name = 'UNIFORM_TAB';

col FILE_NAME for a40
COL TABLESPACE_NAME for a15
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB
FROM DBA_DATA_FILES;


ONLINE/OFFLINE  READ ONLY/READ WRITE

SELECT tablespace_name, status
FROM dba_tablespaces;

-- ONLINE/OFFLINE
-- You may want to take a tablespace offline for any of the following reasons:
-- * To make a portion of the database unavailable while allowing normal access to the remainder of the database
-- * To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)
-- * To rename or relocate tablespace datafiles
-- * When a tablespace is taken offline, the database takes all the associated files offline.
-- * You cannot take the following tablespaces offline: SYSTEM, The undo tablespace, Temporary tablespaces

ALTER TABLESPACE example OFFLINE;
ALTER TABLESPACE example ONLINE;

READ ONLY/READ WRITE

ALTER TABLESPACE example READ ONLY;
SELECT last_name from hr.employees WHERE employee_id = 100;
UPDATE hr.employees SET salary=salary;
ALTER TABLESPACE example READ WRITE ;

-- OMF Oracle-Managed Files

-- Oracle-Managed Files
-- * Using Oracle-managed files simplifies the administration of an Oracle Database.
-- * Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system -- files comprising an Oracle Database.
-- * You specify operations in terms of database objects rather than filenames.
-- * The database internally uses standard file system interfaces to create and delete files as needed.

-- Enabling the Creation and Use of Oracle-Managed Files

-- DB_CREATE_FILE_DEST
-- This initialization parameter allow Oracle use the Oracle-managed files for Datafiles
-- and Temporary files

show parameter db_create_file

ALTER SYSTEM SET db_create_file_dest = 'E:\app\Owner\oradata\orcl';

CREATE TABLESPACE tbs_3 ;
-- By default, OMF Files are 100MB, Autoextentsible (unlimited)
DROP TABLESPACE tbs_3  ;
CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M;
DROP TABLESPACE tbs_3  ;
CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M, SIZE 20M ;
DROP TABLESPACE tbs_3  ;

CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M, SIZE 20M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ALTER TABLESPACE tbs_3 ADD DATAFILE;

DROP TABLESPACE tbs_3  ;

-- Is it still possible to use Non-OMF?
CREATE TABLESPACE mytbs
DATAFILE 'C:\oracle\mytbs.dbf' SIZE 10m

-- Advantages
-- * DBAs don't need to specify file names, locations and sizes when creating a tablespace or database
-- * Automatic removal of files when a tablespace or log file is dropped
-- * Simplified creation of test and development systems

-- Disadvantages
-- * Can only be used with file systems, not with RAW Volumes
-- * Generated file names and locations might not be according to the site's naming standards
-- * Limited scope for file placement and I/O tuning may impact performance (although locations can be altered dynamically)

-- DEMO - MIGRATION
SELECT tablespace_name, block_size
FROM dba_tablespaces;

-- Create table
CREATE TABLE longrec_table (a varchar2(3000),
                            b varchar2(3000),
                            c varchar2(3000))
   TABLESPACE USERS;

BEGIN
FOR i IN 1..100 LOOP
    INSERT INTO LONGREC_TABLE(a)
     VALUES (LPAD('*',3000,'*'));
END LOOP;
END;

-- Check stats - How many blocks is the table taking and are there any migrated rows? (CHAIN_CNT):
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TABLE';

-- Still no data in dba_tables - must analyze the table:
-- Analyze
ANALYZE TABLE longrec_table COMPUTE STATISTICS;

-- Increase each row size from 3K to 6K:
UPDATE longrec_table
SET b=LPAD('*',3000,'*');

ANALYZE TABLE longrec_table COMPUTE STATISTICS;

-- Since block size is 8K, Half the rows have migrated (The first row in each block that was updated):
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TABLE';

-- Rebuild the table to fix the problem:
ALTER TABLE longrec_table MOVE TABLESPACE users;

-- ASSM - Automatic Segment Space Management - From 9i, Default from 10g
-- Managing block available for insert in bitmaps instead of free-lists (Manual)

SELECT tablespace_name, segment_space_management
FROM dba_tablespaces;

-- If the tablespace is Locally managed,
-- we can simply add the SEGMENT SPACE MANAGEMENT clause:
CREATE TABLESPACE uniform_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\uni_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
SEGMENT SPACE MANAGEMENT AUTO;

DEMO - Chaining
-- Add the third column:
UPDATE longrec_table
SET c=LPAD('*',3000,'*');

-- Analyze
ANALYZE TABLE longrec_table COMPUTE STATISTICS;

-- Check stats, and see that ALL rows are now split:
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TABLE';

-- Rebuilding the table - does not help... :
ALTER TABLE longrec_table MOVE TABLESPACE users;
-- (Check stats...)

Multiple Block Size Support
-- See current tablespaces and their block size
SELECT tablespace_name, block_size
FROM dba_tablespaces;

-- Try to create one with a block size of 16K
CREATE TABLESPACE longrec_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\longrec_tbs01.dbf' SIZE 40M
BLOCKSIZE 16K;

-- See current configured Database Buffer Caches:
SHOW PARAMETER cache_size

ALTER SYSTEM SET db_16k_cache_size=12M [scope=spfile];

CREATE TABLESPACE longrec_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\longrec_tbs01.dbf' SIZE 40M
BLOCKSIZE 16K;

-- Solve with moving the table to a tablespace with a bigger block size:
ALTER TABLE longrec_table MOVE TABLESPACE longrec_tbs;

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!