Skip to main content

Storage - Oracle Architecture

Logical Storage

Blocks

Data Block is the smallest unit of logical storage for a database object.

Extents

Extends is the next level of logical grouping in the database. An extent contains one or more database blocks.

Segments

The next level of logical grouping in a database in the segments. A segment is a group of extents from a database object that Oracle treats as a unit.


  • Data Segments: Every table in the database resides in a single data segment, consisting of one or more extents.
  • Index Segments: Each index is stored in its own index segment. As with partitioned tables, each partition of a partitioned index is stored in its own segment.
  • Temporary Segments: When a user's SQL statement needs disk space to complete an operation such as a sorting operation that cannot fit in memory, Oracle allocates a temporary segment. Temporary segments exist only for the duration of SQL execution.
  • Rollback Segments: Rollback Segments only exists in the SYSTEM tablespace, and typically the DBA doesn't need to maintain the SYSTEM rollback segment.


Comments

Popular posts from this blog

SQL Blocking query

SELECT s.session_id, r.status, r.blocking_session_id 'Blk by' , r.wait_type, wait_resource, r.wait_time / ( 1000.0 ) 'Wait Sec' , r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / ( 1000.0 ) 'Elaps Sec' , Substring (st. TEXT ,(r.statement_start_offset / 2 ) + 1 , (( CASE r.statement_end_offset WHEN - 1 THEN Datalength (st. TEXT ) ELSE r.statement_end_offset END - r.statement_start_offset) / 2 ) + 1 ) AS statement_text, Coalesce ( Quotename ( Db_name (st.dbid)) + N '.' + Quotename (Object_sch...

Identify Missing PrimaryKeys in tables

-- DEFINE &TableOwnerPattern = &1 -- DEFINE &TableNamePattern = &2 -- COLUMN owner FORMAT A25 HEADING 'Owner' -- COLUMN table_name FORMAT A30 HEADING 'Table' -- COLUMN constraint_name FORMAT A30 HEADING 'FK constraint' -- COLUMN column_list FORMAT A62 HEADING 'FK columns(s)' WORD WRAP WITH constr AS ( SELECT c.owner, c.table_name, c.constraint_name, listagg(col.column_name, ', ' ) within group ( ORDER BY col.column_name) column_list_alphabetic, listagg(col.column_name, ', ' ) within group ( ORDER BY col.position) column_list FROM dba_constraints c JOIN dba_cons_columns col ON ( col.owner = c.owner AND col.constraint_name = c.constraint_name AND col.table_name = c.table_name ) WHERE c.constraint_type = 'R' AND c.owner like '&TableOwnerPattern' AND c.table_name like '&TableNamePatte...