Oracle Instance

1
Oracle Instance

• Data buffer stores recently used data blocks and
  rollback blocks
• Redo Log records changes (new values) in the
  blocks
• DBW0 and LGWR

2
Database

• Data files application data and rollback
  segments
• Rollback segments save the old value of data
• Control files control info to maintain DB
• Redo Log files record of changes made to DB

3
SQL transaction processing

• SCN-System Change Number
• COMMIT write redo log buffer to files
• CHECKPOINT write all changed data blocks in
  data buffer to disk

4
Data organization

• Logical and Physical
• Segment table, cluster, index, rollback, LOB
• Extent space allocation

5
Create users
create tablespace data datafile '/space/oradata/db
01/data.dbf' size 2M
CREATE PROFILE Student_prof LIMIT SESSIONS_PER_USE
R 1 CONNECT_TIME_240 IDLE_TIME 45
CREATE USER orauser01 IDENTIFIED BY
oracle DEFAULT TABLESPACE data TEMPORARY
TABLESPACE temp QUOTA 1M ON data QUOTA 1M ON
temp PROFILE student_prof

• CREATE ROLE student
• GRANT connect, create session, select table
• TO student
• GRANT student TO orauser01

6
Create tables
CREATE TABLE major (major char (4), description
varchar2 (40), CONSTRAINT pk_major PRIMARY KEY
(major) USING INDEX TABLESPACE indx) TABLESPACE
data
INSERT INTO major VALUES ('FIN ',
'Finance') INSERT INTO major VALUES ('MKT ',
'Marketing')
CREATE TABLE student (ssn NUMBER(9), major char
(4), CONSTRAINT pk_student PRIMARY KEY
(ssn), CONSTRAINT fk_major FOREIGN KEY (major)
REFERENCES major (major))
INSERT INTO student VALUES (123456789, FIN
') INSERT INTO student VALUES (987654321, 'MKT
')
7
Conclusion

• Not completed yet