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

Post a Comment

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...
Post a Comment
Read more

REF Cursor

REF CURSOR WILL BE DYNAMICALLY OPENS OR OPEN BASED ON A LOGIC. DECLARE TYPE C1 IS REF CURSOR ; CURSOR C IS SELECT * FROM DUAL; REF_CURSOR RC; BEGIN IF (TO_CHAR(SYSDATE, 'DD' ) = 30 ) THEN OPEN REF_CURSOR FOR 'SELECT * FROM TABLE1' ; ELSIF ( TO_CHAR(SYSDATE, 'DD' ) = 29 ) THEN OPEN REF_CURSOR FOR SELECT * FROM TABLE2; ELSE OPEN REF_CURSOR FOR SELECT * FROM DUAL; END IF ; OPEN C; END ;
Post a Comment
Read more