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

Insert script with multiple cursors and condition check

DECLARE CURSOR C1 IS   SELECT ID FROM TABLE_NAME_1 WHERE COLUMN IN ('');   CURSOR C2     IS       SELECT ID FROM TABLE_NAME_2 WHERE COLUMN IN ('');              CURSOR C3 (CP_TABLE_01_ID NUMBER,CP_TABLE_02_ID NUMBER)         IS           SELECT COUNT(*) AS COUNT_UP           FROM TABLE_NAME_3           WHERE COLUMN_CONDITION_01=CP_TABLE_02_ID           AND COLUMN_CONDITION_02=CP_TABLE_01_ID; COUNT_UP NUMBER; BEGIN FOR R1 IN C1 LOOP     FOR R2 IN C2     LOOP          OPEN C3(R1.ID,R2.ID);        FETCH C3 INTO COUNT_UP;        CLOSE C3;               IF (COUNT_UP=0) THEN           INSERT           INTO TABL...
Post a Comment
Read more

Oracle Data Dump Import and Export

Login to server : sqlplus / as sysdba exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y or exp SYSTEM/password PARFILE=param.dat where param.dat contains the following information  FILE=dba.dmp   GRANTS=y   FULL=y   ROWS=y   LOG=dba.log To dump a single schema to disk. exp / FILE=dump.dmp OWNER=production Export Specific tables to disk exp SYSTEM/password FILE=export.dmp TABLES=(user1.table,user2.table) Export Data in one user  exp / FILE=export.dmp TABLES=(table1,table2) Using imp:   To import the full database exported in the example above. imp SYSTEM/password FULL=y FIlE=dba.dmp To import just the dept and emp tables from the scott schema imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp) To import tables and change the owner  imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager) To import just the scott schema exported in t...
Post a Comment
Read more