Oracle Database Storage Structures Considerations

Storage Structures
Three files types must be present in a database: The controlfile, the online redo log files, and any number of datafiles.


Damage to any controlfile copy will cause the database instance to terminate immediately.



Every database must have at least two online redo log file groups to function. Each group should have at least two members for safety.


Server processes read from the datafiles; DBWn writes to datafiles.


Other Database Files

■ The Instance Parameter File
■ The Password File
■ Archive Redo Log Files
■ Alert Log and Trace Files

The Logical Database Structures


SQL> select distinct segment_type from dba_segments;

SEGMENT_TYPE
------------------
LOBINDEX
INDEX PARTITION
TABLE SUBPARTITION
ROLLBACK
TABLE PARTITION
NESTED TABLE
LOB PARTITION
LOBSEGMENT
INDEX
TABLE
TYPE2 UNDO

SEGMENT_TYPE
------------------
CLUSTER

12 rows selected.


The SYSAUX tablespace must be created at database creation time in Oracle 10g and later. If you do not specify it, one will be created by default.


Which view will show you ALL the tables in the database? DBA_TABLES, not ALL_TABLES


select tablespace_name, extent_id, file_id,block_id, bytes from
dba_extents where owner='USER' and segment_name='TEST'
SQL> /

TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES
------------------------------ ---------- ---------- ---------- ----------
USERS                                   0          4        520      65536


SQL> select name from v$datafile where file#=4;

NAME
--------------------------------------------------------------------------------
+DATA/oratest/datafile/users.259.773672963

SQL> select block_size * &block_id from dba_tablespaces
  2  where tablespace_name='USERS';
Enter value for block_id: 520
old   1: select block_size * &block_id from dba_tablespaces
new   1: select block_size * 520 from dba_tablespaces

BLOCK_SIZE*520
--------------
       4259840