Oracle Concepts - 12 Logical Storage Structures

Logical and Physical Storage

Blocks

Database Structures - Blocks

• smallest unit of space in an oracle DB
• blocks are read into buffers
• defined by db_block_size parameter
• cannot be changed after creation
• Anatomy
1. Header info (Meta data about columns and rows in block)
2. Free Space
3. Used Space

- Sizing possibilities - 2k-32k

1. 2K - less often used
2. 4K - OLTP
3. 8K - Default and Hybrid
4. 16K - Data Warehousing, binary large objects
5. 32K - less often used

Extents

Database Structures - Extents

• composed of blocks

• Managing Extents
  • Dictionary Managed Extents - old way before oracle 9i
  • Locally Managed Extents - oracle self Managed Extents (better performance)

Segments

Database Structures - Segments

• Physical analogue of some logical database objects like table or index ..etc
• composed of extents
• structure

• Types of Segments
1. Table segments - store raw data which store of blocks which form extents which compose segments
2. Index segments - physical storage structure takes a partition or a column of table to make table more easy querying and discovered and speed performance
3. Undo segments - to store before image of data “Undo changes” through transaction
4. Temporary segments - helping memory to store temp data like sorting a table
5. Table Partition segments - used with table partitions to allow split table to partitions to speed querying big table
6. Index Partition segments - used with index rely on partitions rather than the table itself
7. LOB/LOB Index - large objects data types like video, images, mp3 using pointers in table to point to these objects in it’s own segments 

• practice:

SQL> select * from user_tables where table_name = 'EMP';

SQL> select * from user_segments where segment_name = 'EMP';

Datafiles

Database Structures - Datafile

• Datafiles contains segments

• Types of Datafiles
  • Non-specific data - application and user data
  • Undo Data - within transaction occur ‘data changes’ or before image of data
  • Temporary Data (tempfiles) - used when memory full of data
• Limits
  • Max size = 4.194.304 times the block size
    • Eg. 4.194.304 × 8K Block = 32G max size
  • Max number of datafiles in a DB = 65.533
  • Max database size = 8 Petabytes
• Data Dictionary views
  • DBA_DATA_FIles
  • DBA_TEMP_FILES
  • V$DATAFILE
  • V$TEMPFILE
• Practice

$ connect system/oracle

SQL> select * from dba_data_files;

SQL> select * from v$datafile;

SQL> select * from dba_temp_files;

SQL> select * from v$tempfile;

Smallfiles Tablespaces

Database Structures - small Tablespaces (Default)

• Tablespace is the logical name given to one or more physical datafiles
• smallfile tablespaces are the default type
• Max number of datafiles per smallfile tablespace = 1022
• Data dictionary views
  1. DBA_TABLESPACES
  2. V$TBALESPACE
• Practice

            $ connect system/oracle

SQL> select * from dba_tablespaces;

SQL> select * from v$tablespace;

SQL> select * from dba_data_files;

SQL> create tablespace example03 datafile '/u01/app/oracle/oradata/ORCL/datafile/example03.dbf' size 100M;

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/datafile/example04.dbf' size 50M;

SQL> select * from dba_data_files;

SQL> alter database datafile 8 resize 100m;

SQL> select * from dba_data_files;

SQL> drop tablespace example02 including contents and datafiles;

SQL> select * from dba_tablespaces;

               SQL> select bytes/1024/1024 datafile_size_MB from dba_data_files where file_id=12;

Bigfiles Tablespace

in Red Hat: Everything better in RED
in Oracle: Everything better in Big like Big Data  or any other Bigggyyyy

Database Structures - big-files tablespaces

• Created to address needs of VLDB - what if DB is huge?
• Single datafile per bigfile tablespace
• Eases manageability and increases capacity
• Limits
  • Max size = block size × 4.294.967.296
    • Eg. 32K block × 4.294.967.296 = 128 TB
  • Max size of database = 128 TB × 65.533 files
    • 9.007.199.254.740.992 bytes = 8 Exabytes
• Practice

select * from dba_data_files;

create bigfile tablespace big_ts datafile

'/u01/app/oracle/oradata/ORCL/datafile/big_ts01.dbf' size 100M;

select * from dba_tablespaces;

alter tablespace big_ts offline;

create table bigtbl1 (col number) tablespace big_ts;

alter tablespace big_ts online;

create table bigtbl1 (col number) tablespace big_ts;

alter tablespace big_ts read only;

insert into bigtbl1 values (7);

alter tablespace big_ts read write;

insert into bigtbl1 values (7);.

select * from bigtbl1;

drop tablespace big_ts including contents and datafiles;

Automatic Storage Management (ASM)

Database Structures - Automatic Storage Management (ASM)

• Filesystem
  • Datafiles stored on general OS Filesystem, NTFS, EXT3
  • Pros - easy to use, easy to see and manage files
  • Cons - Slower since filesystem overhead is involved
• RAW Devices
  • Datafiles stored on an unformatted logical volume or disk
  • Pros - much faster than filesystem with 30%
  • Cons - very difficult to work with, even dangerous
• Automatic Storage Management (ASM) - a new way
  • First introduced in oracle 10g
  • Designed to store Oracle database files
  • Runs in its own instance
  • Present raw devices to ASM and it manages them
  • ASM benefits
    • speed of raw devices - no OS file-system overhead
    • Simpler to manage
    • Safer to manage
    • Many ‘hot’ features - RAC, dynamic re-balancing
  • ASM Architecture
    • ASM Disks
    • ASM diskgroups
    • ASM instance

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

- Data block corresponds to a specific number of bytes of physical disk space

- Extent is a set of logically contiguous data blocks allocated for storing a specific type of information

- Segment is a set of extents allocated for a specific database object, such as a table or index

- Data for the employees table is stored in its own data segment, whereas each index for employees is stored in its own index segment. 

• Each segment belongs to one and only one tablespace. 
• Thus, all extents for a segment are stored in the same tablespace. 
• Within a tablespace, a segment can include extents from multiple data files
• No Relationship between tablespace and schema

- Logical Space Management

- PCTFREE. This parameter specifies the percentage of space to be reserved in a block for future updates

- ASSM is more efficient and is the default for permanent, locally managed tablespaces.