0
Introduction To ORACLE
RDBMS
• 1960‘s Hard Data
• 1970’s File System
• 1970, RDBMS, E.F.Codd
• 1979, First ORACLE RDBMS
• ORDBMS
1-2
Types of Oracle Users
• Database Administrators
• Security Officers
• Network Administrators
• Application Developers
• Application Administrators
• Database Users
1-3
Database Administrators I
• Installing and upgrading the Oracle server and
application tools
• Allocating system storage and planning future
storage requirements for the database system
• Creating primary database storage structures
(tablespaces) after application developers have
designed an application
• Creating primary objects (tables, views, indexes)
once application developers have designed an
application
• Modifying the database structure, as necessary,
from information given by application developers
1-4
Database Administrators II
• Enrolling users and maintaining system security
• Ensuring compliance with your Oracle license
agreement
• Controlling and monitoring user access to the
database
• Monitoring and optimizing the performance of the
database
1-5
Database Administrators III
• Planning for backup and recovery of database
information
• Maintaining archived data on tape
• Backing up and restoring the database
• Contacting Oracle Corporation for technical
support
1-6
Tasks of a Database
Administrator
• Task 1: Evaluate the Database Server Hardware
• Task 2: Install the Oracle Software
• Task 3: Plan the Database
• Task 4: Create and Open the Database
• Task 5: Back Up the Database
1-7
Tasks of a Database
Administrator
• Task 6: Enroll System Users
• Task 7: Implement the Database Design
• Task 8: Back Up the Fully Functional Database
• Task 9: Tune Database Performance
1-8
Database Objects
• Table, Column, Datatypes
• Index
• View
• Sequence
• Synonym
• Partition
• Cluster
1-9
Database Objects
• Procedure and Function
• Package
• Trigger
• Users
• Privileges and Roles
• Database Links
1-10
Native Datatypes
• Character Datatypes
– CHAR Datatype
– VARCHAR2 and VARCHAR Datatypes
– NCHAR and NVARCHAR2 Datatypes
– LONG Datatype
• NUMBER Datatype
• DATE Datatype
• LOB Datatypes
• RAW and LONG RAW Datatypes
• ROWID and UROWID Datatypes
1-11
SQL Overview
• SQL: Structured Query Language
• SQL is a database access, nonprocedural
language.
• SQL-92
• SQL-99
• Extensions to SQL-99
1-12
Oracle SQL Categories
• Data Manipulation Language Statements
• Data Definition Language Statements
• Transaction Control Statements
• Session Control Statements
• System Control Statements
• Embedded SQL Statements
1-13
Data Manipulation Language (DML)
• Retrieve data from one or more tables or views
(SELECT) (DQL)
• Add new rows of data into a table or view (INSERT)
• Change column values in existing rows of a table
or view (UPDATE)
• Update or insert rows conditionally into a table or
view (MERGE)
• Remove rows from tables or views (DELETE)
• See the execution plan for a SQL statement
(EXPLAIN PLAN)
• Lock a table or view, temporarily limiting other
users' access (LOCK TABLE)
1-14
Data Definition Language (DDL)
• Create, alter, and drop schema objects and other
database structures, including the database itself
and database users (CREATE, ALTER, DROP)
• Change the names of schema objects (RENAME)
• Delete all the data in schema objects without
removing the objects' structure (TRUNCATE)
• Grant and revoke privileges and roles (GRANT,
REVOKE)
• Turn auditing options on and off (AUDIT,
NOAUDIT)
• Add a comment to the data dictionary (COMMENT)
1-15
Transaction Control Statements
• Make a transaction's changes permanent
(COMMIT)
• Undo the changes in a transaction, either since the
transaction started or since a savepoint
(ROLLBACK)
• Set a point to which you can roll back
(SAVEPOINT)
• Establish properties for a transaction (SET
TRANSACTION)
1-16
Session Control Statements
• Alter the current session by performing a
specialized function, such as enabling and
disabling the SQL trace facility (ALTER SESSION)
• Enable and disable roles (groups of privileges) for
the current session (SET ROLE)
1-17
System Control Statements
• System control statements change the properties
of the Oracle server instance.
• The only system control statement is ALTER
SYSTEM.
1-18
Embedded SQL Statements
• Define, allocate, and release cursors (DECLARE
CURSOR, OPEN, CLOSE)
• Specify a database and connect to Oracle
(DECLARE DATABASE, CONNECT)
• Assign variable names (DECLARE STATEMENT)
• Initialize descriptors (DESCRIBE)
• Specify how error and warning conditions are
handled (WHENEVER)
• Parse and run SQL statements (PREPARE,
EXECUTE, EXECUTE IMMEDIATE)
• Retrieve data from the database (FETCH)
1-19
