Laboratoire Supinfo des technologies Oracle

Laboratoire Supinfo des technologies Oracle Rappels

Shared pool Library cache Data dict.cache Aperçu de l’architecture Oracle Instance Userprocess SGA Data buffercache Redo logbuffer Serverprocess PGA SMON PMON CKPT LGWR Others DBW0 Database Data files Control files Redo logfiles Parameter file Archived log files Password file

SMON DBWR PMON CKPT LGWR ARCH Le fichier de paramètre Instance SGA Shared pool Library cache Data buffercache Redo logbuffer Data dict.cache initU15.ora SQL> CONNECT / AS SYSDBA SQL> STARTUP PFILE=/DISK1/initU15.ora

Startup et shutdown STARTUP OPEN All files opened as described by the control file for this instance. MOUNT Control file opened for this instance. NOMOUNT Instance started. SHUTDOWN SHUTDOWN

Options de Shutdown A x x x x Shutdown Mode Allow new connections Wait until current sessions end Wait until current transactions end Force a checkpoint and close files I x x x o T x x o o N x o o o Shutdown mode: A Abort I Immediate T Transactional N Normal x o NO YES

Creating the database SPOOL creU16.log STARTUP NOMOUNT PFILE=initU16.ora CREATE DATABASE U16 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXLOGHISTORY 100 LOGFILE GROUP 1 ('/DISK3/log1a.rdo',/DISK4/log1b.rdo’) SIZE 1 M, GROUP 2 ('/DISK3/log2a.rdo',/DISK4/log2b.rdo’) SIZE 1 M DATAFILE '/DISK1/system01.dbf' size 50M autoextend on CHARACTER SET WE8ISO8859P1;

Catégories de vues DBA_xxx All of the objects in the database ALL_xxx Objects accessible by the current user USER_xxx Objects owned by the current user

Examples • DICT_COLUMNS • DBA_TAB_COLUMNS • DBA_CONSTRAINTS • DBA_EXTENTS • General overview • DICTIONARY • Schema objects • DBA_TABLES • DBA_OBJECTS • Space allocation • DBA_ SEGMENTS • DBA_FREE_SPACE • Database structure • DBA_DATA_FILES • DBA_ROLLBACK_SEGS • DBA_TABLESPACES

Script de création de vues Script Purpose catalog.sql Creates commonly used data dictionary views and synonyms catproc.sql Runs scripts required for server-side PL/SQL

L’utilité du fichier de controle • Small, binary file • Required: • At mount • To operate • Linked to a single database • Should be multiplexed • Loss may require recovery Data files Control files Redo logfiles Database

control_files=(/DISK1/control01.con,/DISK2/control02.con) Disk 1 Disk 2 control01.con control02.con Multiplexage

Groupes et membres de redo Group 1 Group 2 Group 3 Disk 1 Member Member Member Disk 2 Member Member Member

Sans archivage Backup Disk failure Control files Data files 50 51 100 101 t2 t1

Avec Archivage Backup Archived redo logs Disk failure 50 Control files Data files 99 50 51 100 101 t2 t1

Hiérarchie de stockage Database Tablespace Data file Logical Physical Segment Extent Oracle block OS block

Création de tablespace CREATE TABLESPACE app_data DATAFILE '/DISK4/app_data_01.dbf' SIZE 100M, '/DISK5/app data_ 02.dbf' SIZE 100M MINIMUM EXTENT 500K DEFAULT STORAGE ( INITIAL 500K NEXT 500K MAXEXTENTS 500 PCTINCREASE 0 );

Rollback: Fonctionnalités Transaction rollback Transaction recovery Read consistency Rollback segment

Héritage des clause de stockage Oracle default Tablespace Segment

Vue du dictionnaire de données Used extents DBA_EXTENTS Free extents DBA_FREE_SPACE Segments DBA_SEGMENTS Data files DBA_DATA_FILES Tablespaces DBA_TABLESPACES

Capacité de fragmentation Tablespace SYSTEM TOOLS DATAn INDEXn RBSn TEMPn Usage Data dictionary Applications Data segments Index segments Rollback segments Temporary segments Fragmentation Zero Very low Low Low High Very high* * Relevant only if tablespace PERMANENT

Structure d’une ligne Row header Column length Database block Column value

Classification des indexes • Logical • Single column or concatenated • Unique or nonunique • Function-based • Physical • Partitioned or nonpartitioned • B-tree • Normal or reverse key • Bitmap

Fin du rappel VOUS VOUS RAPPELEZ DE TOUT, ON FAIT UN CONTRÔLE

Laboratoire Supinfo des technologies Oracle