Transcript cos346day20

COS 346
Day 20
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
10-1
Agenda
•
Assignment 8 Corrected
– 3 A’s, 2 B’s and 2 C’s
•
Assignment 9 Posted
– Due April 23
– 2 More to go
•
Quiz 2 Today
– DP 7 & 8, SQL 2-10
– There will only be 3 exams (3 @ 10% instead of 4 @ 7.5%)
•
Capstone Progress Reports Over Due
– 3 missing
•
•
Make sure to check corrections for Oracle text for WebCT
Today we will discuss
– Managing databases with Oracle
– Lecture starts @ 4:45
•
We will be in the other text next class
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
10-2
ELC 498
• Moved from TF 12:30-1:50 to MR 9:30 10:50 to address a BUS course conflict
(Principles of Finance) .
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
10-3
End of days? (subject to
change)
• April 30
• April 16
– Quiz 2
– DP chap 10
• April 19
– SQL Chap 12
– SQL Chap 11
– Assignment 9 due
– DP Chap 14
– Assignment 10 Due
• April 3
– DP Chap 15
– Review
– Assignment 11 Due
• May 9
– Quiz 3
– 10 AM
– Capstone presentations
• April 23
– DP Chap 12
– DP Chap 13
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
10-4
CHAPTER 12:
ORACLE DATABASE
ADMINISTRATION
Copyright 2004 Prentice Hall, Inc.
12-5
DATABASE ADMINISTRATION OVERVIEW
•
•
•
•
Database administration is a specialized area within a
large information systems department that is separate
from the application development area.
Application development includes systems analysis,
systems design, programming, and systems testing.
Database administration is concerned with
administrative tasks that must be accomplished in order
for application developers and information system users
to access an organization's databases.
A database administrator (DBA) is neither superior to
nor inferior to an application developer.
Contd.
Copyright 2004 Prentice Hall, Inc.
12-6
DATABASE ADMINISTRATION OVERVIEW
•
•
DBAs and application developers must be both
technically competent and capable of working closely
with other professionals in a support type of
relationship.
One of the primary roles of a DBA is to provide the
support needed by an application developer so that the
application developer can accomplish the task of
building and maintaining information systems.
Copyright 2004 Prentice Hall, Inc.
12-7
DBA Duties
•
•
•
•
•
Install relational database management system
software and upgrades.
Design and create a database including the
allocation of system disk storage for current and
future database storage requirements.
Start up and shut down a database.
Create user accounts and monitor user activities.
Grant database privileges to control data security
and data access.
Copyright 2004 Prentice Hall, Inc.
12-8
DBA Duties
•
•
•
•
Backup and recover a database in the event of
system failure.
Tune a database to optimize database
performance.
Manage database network connectivity.
Migrate a database to a new version of the
DBMS software.
Copyright 2004 Prentice Hall, Inc.
12-9
ORGANIZATION OF AN ORACLE DATABASE
•
•
An Oracle relational database is typical of databases running on
larger relational database management systems, and includes
both memory and disk storage components.
Figure 12.1 provides a fairly detailed conceptual model of what
is termed an Oracle Instance.
Copyright 2004 Prentice Hall, Inc.
12-10
•
•
•
•
•
Oracle Instance
The large rectangle in the figure labeled Memory
Allocated to the Instance represents part of a server
computer's random access memory (RAM).
This part of RAM is strictly allocated to a running
instance of an Oracle database.
The instance includes the system global area (SGA), and
the SGA is divided into the database buffer cache, redo
log buffer, shared pool, large pool, and JAVA pool.
The instance also includes memory allocated for
computer programs called background processes.
The background processes are part of the Oracle
instance, and are actually computer programs.
Copyright 2004 Prentice Hall, Inc.
12-11
Oracle Instance
•
•
•
•
These programs provide the services needed to enable
system developers and system users to save database
objects such as data tables and data rows in tables.
The background processes also enable database
recovery, database backup, data archiving, and other data
management tasks as necessary.
An Oracle database does not normally run on a personal
computer, although there is a version of Oracle called
Personal Oracle that will do just that.
An Oracle database is very large and resides on a
powerful server computer.
Copyright 2004 Prentice Hall, Inc.
12-12
Oracle Instance
•
•
•
•
•
A server computer is typically connected to many disk
drives.
The database files shown in the figure typically include
many different physical files stored on many disk drives.
Database files provide the primary storage for all objects
stored in a database. This is where user tables and data
are stored.
The redo log files in the figure store information that is
used to recover a database if some type of database
failure occurs.
The control files store information about the actual
physical organization of an Oracle database.
Copyright 2004 Prentice Hall, Inc.
12-13
Oracle Instance
•
•
•
•
The parameter file stores information that Oracle
needs in order to operate the database as specified by
the DBA.
When a system user logs onto an Oracle database or
when an application program executes, Oracle
allocates a memory object called a server process as
shown in the figure.
The dedicated server version of Oracle allocates one
server process to each system user process.
The multi-threaded server version of Oracle allows
server processes to be shared by more than one system
user process.
Copyright 2004 Prentice Hall, Inc.
12-14
Oracle Instance
•
•
•
A server process communicates with a user process in
order to service the data requests from the connected
user process.
The server process actually interprets the SQL
commands passed from the user process through the
server process to Oracle, and moves the requested data
from the database files into the database buffer cache.
Suppose that a system user executes an SQL command
that will modify an existing data row stored in an Oracle
database, the data row is moved from a database file to
the server process allocated to the system user, and then
to the database buffer cache in the system global area.
Copyright 2004 Prentice Hall, Inc.
12-15
Oracle Instance
•
•
•
•
The database buffer cache is a memory object that is
shared by all system users.
This sharing improves system performance because more
than one user process may need to access the same data
row.
Data inside the database buffer cache are constantly being
modified as data rows are added, deleted, and updated.
Modified data blocks (a single data block typically
contains many rows of data) are written from the database
buffer cache by a background process called the database
writer (DBWn) back to the appropriate disk data file,
thereby saving the data to the magnetic hard disk.
Copyright 2004 Prentice Hall, Inc.
12-16
Oracle Instance
•
•
•
•
As existing data rows in the database buffer cache are
modified or new data rows are created, memory images of
these modifications and new rows are stored to the redo
log buffer in the system global area.
As the redo log buffer in memory fills up, the modified
row images are written to the redo log files on disk by a
different background process called the log writer
(LGWR).
The redo log files are magnetic hard disk objects; thus,
their data are not lost if electrical power fails.
Over time, the redo log files can be archived to provide a
more permanent record of database changes.
Copyright 2004 Prentice Hall, Inc.
12-17
Oracle Instance
•
•
•
The data in redo log buffers and in redo log files are used
to recover a database in the event of some type of system
failure.
In addition to the database writer and log writer
background processes, there are other background
processes that perform additional database administration
tasks such as managing memory space allocated to Oracle
by the operating system, archiving data, and performing
backup and recovery.
Some of these processes manage the shared pool, large
pool, and JAVA pool.
Copyright 2004 Prentice Hall, Inc.
12-18
Oracle Instance
•
•
•
The shared pool is shared by all server processes in order
to improve performance efficiency of an Oracle database.
The large pool is memory that can be used to store
variables and other memory values that would be part of
an individual system user's allocated server process.
The Java Pool is used for memory allocation in support of
the execution of Java commands for Internet-based
applications accessing an Oracle database.
Copyright 2004 Prentice Hall, Inc.
12-19
TABLESPACES AND FILES
•
•
•
•
•
An Oracle database is normally divided into logical
components termed tablespaces.
Most tablespaces are created by a DBA when a database is
initially created.
As the term implies, a tablespace is used to store tables.
But, tablespaces are actually used to store all types of
database objects.
These objects include indexes, sequences, procedures,
views, and other database objects.
Copyright 2004 Prentice Hall, Inc.
12-20
TABLESPACES AND FILES
•
A typical Oracle database will have most of the
tablespaces listed here.
–
–
–
–
–
–
–
–
SYSTEM
USER
DATA
INDEXES
TEMP
UNDO
IDS
SPECIAL_APPS
Copyright 2004 Prentice Hall, Inc.
12-21
TABLESPACES AND FILES
•
•
•
•
•
•
Tablespaces are logical, not physical objects.
Tablespaces are actually stored in data files.
Data files are the corresponding physical objects.
A very large tablespace may require more than one data
file in order to store all of its objects.
A data file can only store data for a single tablespace.
Tablespaces are created with the CREATE TABLESPACE
command.
Copyright 2004 Prentice Hall, Inc.
12-22
Copyright 2004 Prentice Hall, Inc.
12-23
DATA DICTIONARY
•
•
•
•
•
The Oracle data dictionary consists of read-only, base
tables that store information about the database.
The data dictionary is stored in the system tablespace.
Information stored in the data dictionary is termed
metadata; that is, data about data.
The read-only, base tables that comprise the data
dictionary are rarely ever accessed by system developers
or system users.
Usually only the various Oracle processes will access
these tables.
Copyright 2004 Prentice Hall, Inc.
12-24
DATA DICTIONARY
•
•
In order to make it easier to access information and
manage a database, the tables of the data dictionary are
organized into various user-accessible views.
These views are also part of the data dictionary.
Copyright 2004 Prentice Hall, Inc.
12-25
DATA DICTIONARY
•
Some of the information stored in the data dictionary
includes:
– all schema object definitions – the definitions of the
tables, indexes, sequences, views, and other database
objects.
– the amount of space allocated for each object, and the
amount of space currently used by each object.
– the names of the Oracle user accounts, and the
privileges and roles granted to each account.
– information needed to enforce integrity constraints.
– other database information.
Copyright 2004 Prentice Hall, Inc.
12-26
THE SYSTEM AND SYS ACCOUNTS
•
•
•
•
•
•
When an Oracle database is created, two special user
accounts named SYS and SYSTEM are created.
The user account SYS is the owner of all base tables and
user-accessible views in the data dictionary.
The security of the SYS account is quite tight and only
DBAs can access this account.
During database operation, Oracle accesses the base tables
through the SYS account.
For this reason, only Oracle can write new data or modify
existing data in the base tables.
Individual system users should never be given privileges to
UPDATE, INSERT, or DELETE rows for these data
dictionary tables.
Copyright 2004 Prentice Hall, Inc.
12-27
THE SYSTEM AND SYS ACCOUNTS
•
•
When Oracle software or third-party software adds new
tables and views to the data dictionary, the owner of
these new objects is the user SYSTEM.
Again, access to the SYSTEM account is also tight and
is typically restricted to DBAs.
Copyright 2004 Prentice Hall, Inc.
12-28
TYPES OF VIEWS –USER, ALL, and DBA
•
•
There are many different types of data dictionary views.
In order to assist system users in using the data
dictionary, the views are divided into three different
categories: USER, ALL, and DBA.
–
–
–
USER – The user prefix is added to all views that display
information about objects that belong to an individual user. We
term this the user's schema of the database.
ALL – The all prefix is added to all views that display
information about all objects in the global database schema.
This expands on an individual user's perspective of the
database.
DBA – The dba prefix is added to all views that fall within the
database administrator's schema of the database.
Copyright 2004 Prentice Hall, Inc.
12-29
USER ACCOUNTS AND PRIVILEGES
•
•
•
Individual accounts, including those that belong to
DBAs must be created.
When a database is initially created, a DBA will logon to
the database as the user SYS or SYSTEM, and create a
personal DBA account.
The DBA account will be granted all of the privileges
needed to administer the database.
Copyright 2004 Prentice Hall, Inc.
12-30
Creating, Altering, and Dropping User Accounts
•
•
The DBA creates individual accounts for each system
user.
A simple form of the CREATE USER command is
shown here:
•
•
•
CREATE USER bock IDENTIFIED BY secret_password;
A DBA or other system administrator must have the
CREATE USER system privilege in order to create a
user account.
Even though bock now has an account, bock still cannot
connect to the database until the DBA grants bock the
CREATE SESSION system privilege.
Copyright 2004 Prentice Hall, Inc.
12-31
Creating, Altering, and Dropping User Accounts
•
•
•
The following SQL Example gives a more complete
form of the CREATE USER command.
This CREATE USER command creates a user account
and also allocates space for the storage of objects in
various tablespaces.
This command will only execute if your database has the
tablespaces named here.
CREATE USER bock IDENTIFIED BY secret_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 10M ON users
QUOTA UNLIMITED ON temp
QUOTA 5M ON data
PASSWORD EXPIRE;
Copyright 2004 Prentice Hall, Inc.
12-32
Altering User Accounts
•
•
•
Suppose that we suspect bock is doing something with
the database that is unauthorized! We can stop bock
from creating additional objects by altering the bock
account.
The commands shown in the following SQL Example
will alter bock's quota on the users and data tablespaces.
Existing objects bock has created will not be modified,
but he will be unable to create new objects.
•
•
•
ALTER USER bock QUOTA 0 ON users;
ALTER USER bock QUOTA 0 ON data;
You can also use the ALTER USER command to
allocate additional space and to restore quota allocations.
Copyright 2004 Prentice Hall, Inc.
12-33
Dropping User Accounts
•
•
The DROP USER command will delete a user account.
It is necessary to use the CASCADE option if the user
has created any objects.
•
•
•
•
•
DROP USER bock;
DROP USER bock CASCADE;
If you fail to specify CASCADE, then the DROP USER
command will fail if the user has created objects.
We need to EXERCISE CAUTION while dropping user
accounts!
What if bock is a system developer who has created
some critical objects such as tables that our organization
uses to store inventory and payroll information?
Copyright 2004 Prentice Hall, Inc.
12-34
Dropping User Accounts
•
•
•
If we drop bock with a CASCADE, the applications will
fail because the tables and indexes that bock created will
be destroyed.
A better alternative is to lock bock out of his account
because his employment has terminated.
We can do this by revoking his privilege to connect to
the system.
Copyright 2004 Prentice Hall, Inc.
12-35
Granting and Revoking Privileges
•
The GRANT command in the following SQL Example
will grant user bock the CREATE SESSION privilege
need to logon to the SQL*PLUS or any of the Oracle’s
tools.
•
•
•
•
•
GRANT PRIVILEGE create session TO bock;
There are many different privileges.
They are divided into two categories: system privileges
and object privileges.
System privileges allow a system user to perform
specific types of operations such as creating, dropping,
and altering objects.
Object privileges allow a system user to perform a
specific operation on a specific object such as a view,
table, or index. Copyright 2004 Prentice Hall, Inc.
12-36
System Privileges
•
•
System privileges are also termed system-wide
privileges, and include privileges that focus on
managing objects that you own, and privileges that focus
on managing objects that any system user may own.
Here are example system privileges:
–
–
–
–
–
CREATE SESSION; ALTER SESSION
CREATE TABLE
CREATE ANY TABLE; ALTER ANY TABLE
CREATE ANY INDEX
UNLIMITED TABLESPACE
Copyright 2004 Prentice Hall, Inc.
12-37
System Privileges
•
•
Basically, if you can create an object, you can also drop the object.
The CREATE TABLE privilege also includes the privilege to
create indexes for a table and to subsequently drop those indexes
as necessary.
•
•
Some example GRANT commands are shown here.
Note that the privileges being granted are separated by a
comma as are the user account names:
–
–
–
GRANT create session TO bock, bordoloi, user001;
GRANT create table, unlimited tablespace TO bock;
GRANT create table, create any table TO bock, bordoloi;
Copyright 2004 Prentice Hall, Inc.
12-38
System Privileges
•
•
•
•
Once a DBA has created a user account, a system
privilege may be granted to the user account with the
WITH ADMIN OPTION.
In fact, this option may be used in any GRANT
command that grants a system privilege.
The WITH ADMIN OPTION enables the grantee (the
account receiving the privilege) to, in turn, grant the
privilege to other user accounts.
Following SQL Example grants CREATE SESSION and
CREATE TABLE to bock, and also gives bock the
authority to grant these two privileges to other system
user accounts.
•
GRANT create session, create table TO bock WITH ADMIN OPTION;
Copyright 2004 Prentice Hall, Inc.
12-39
Object Privileges
•
Object privileges work on a specific object, so the form of
the GRANT command is a bit different. The general form is:
GRANT privilege1, privilege2, . . .
ON object_name TO user1, user2, . . . | public;
•
•
•
For example, if bock has created a table named employee and
wants to give the user named bordoloi the privilege to select
rows from the table, the GRANT command in the following
example enables bordoloi to select from the table.
• GRANT select ON bock.employee TO bordoloi;
Suppose that bock also wants bordoloi to be able to insert
rows into the employee table so as to relieve bock of some of
the workload associated with loading new data.
The revised GRANT command in the following SQL
Example includes the INSERT privilege.
• GRANT select, insert ON bock.employee TO bordoloi;
Copyright 2004 Prentice Hall, Inc.
12-40
Revoking Privileges
•
•
•
•
•
System privileges may be revoked with the REVOKE command.
You can only revoke a privilege that was specifically granted
previously with a GRANT command.
There are no cascading effects of revoking privileges.
This means that if DBA1 grants system privileges WITH ADMIN
OPTION to DBA2, and then DBA1 subsequently changes jobs
and is no longer authorized DBA privileges, all privileges granted
to DBA1 can be revoked without worrying about privileges that
DBA1 might have granted to DBA2 or to any system user
account, for that matter.
Following SQL Example shows the revocation of the SELECT
ANY TABLE privilege from the user account bordoloi.
•
REVOKE select any table FROM bordoloi;
Copyright 2004 Prentice Hall, Inc.
12-41
Revoking Privileges
•
•
Object privileges are revoked in a similar fashion, except
that the object for which privileges are revoked must be
named in the REVOKE command.
The keyword ALL can be used to revoke all privileges
for an object from a user account.
–
–
REVOKE select ON bock.employee FROM bordoloi;
REVOKE ALL ON bock.employee FROM bordoloi;
Copyright 2004 Prentice Hall, Inc.
12-42
ROLES
•
•
•
•
The concept of a role is a simple one, and its purpose is
to make it easier for a DBA to manage privileges.
A role is like a container of a group of privileges for a
specific type of system user, such as an inventory
manager.
Each time we hire an inventory manager, we would
assign the manager a new system user account and
authorize that account all of the privileges contained in
the role called inventory_mgr.
Further, we can simplify the management of privileges
because we can allocate a role to another role!
Copyright 2004 Prentice Hall, Inc.
12-43
ROLES
•
The following figure depicts privileges being
allocated to roles, and the roles being allocated to
system users.
Copyright 2004 Prentice Hall, Inc.
12-44
ROLES
•
•
From studying the figure, it should be obvious that if
you add a new system user who works as an Account
Manager, then you can allocate almost all of the
privileges this user will need by simply allocating the
role named account_mgr to the system user.
Further, if it is determined that all account managers
need an additional privilege, such as DELETE ON
ORDERS, that privilege can be granted to the
account_mgr role, and all of the system users who are
granted the role named account_mgr will inherit the new
privilege! This considerably simplifies user account
management.
Copyright 2004 Prentice Hall, Inc.
12-45
ROLES
•
•
•
•
•
•
•
A role name must be unique within the database.
A role can be allocated both system and object privileges.
Roles are not owned by anyone so they do not appear in any user
account schema.
You allocate privileges to roles the same way that you allocate
them to a system user account; however, you must first create the
role by using the CREATE ROLE command.
The following example commands create the role named
inventory_mgr and grant several privileges to the role.
Next the role is allocated to the system user account for bordoloi.
The system user bock is granted the inventory_mgr role with the
privilege to grant the role to other system users through the WITH
ADMIN OPTION.
Copyright 2004 Prentice Hall, Inc.
12-46
ROLES
•
•
•
•
•
•
•
•
CREATE ROLE inventory_mgr;
GRANT select ON bock.employee TO inventory_mgr;
GRANT select, unlimited tablespace TO inventory_mgr;
GRANT inventory_mgr TO bordoloi;
GRANT inventory_mgr TO bock WITH ADMIN OPTION;
There are several predefined roles that are created as part
of the task of creating a new database.
The CONNECT role is provided for backward
compatibility with earlier versions of Oracle.
Anyone granted the DBA role will be granted all system
privileges needed to function as a DBA and these
privileges will include the WITH ADMIN OPTION.
Copyright 2004 Prentice Hall, Inc.
12-47
ROLES
•
•
•
•
•
•
When a role is dropped, Oracle revokes the role and all
privileges granted through the role from all system users and
from other roles.
If you wish to alter a role, the best approach is to create a
new role with the desired privileges, grant that role to the
system users/roles that possess the role to be dropped; then,
you can drop the first role.
If you were granted a role with the ADMIN OPTION, then
you can drop the role.
You may also drop a role if you have the DROP ANY ROLE
system privilege.
The DROP ROLE command is very simple.
Following SQL Example shows the command used to drop
the account_mgr role.
» DROP ROLE account_mgr;
Copyright 2004 Prentice Hall, Inc.
12-48
DATA DICTIONARY TABLES AND VIEWS
•
•
•
There are a number of data dictionary views that will
interest you and further your understanding of database
administration.
There is also a special set of views named dynamic
performance views that are used to accumulate
information and statistics about the performance of the
database.
Each dynamic performance view begins with a prefix of
v$.
Copyright 2004 Prentice Hall, Inc.
12-49
Dynamic Performance Views
•
•
•
•
v$fixed_table – lists all x$ tables that underline and store
the dynamic performance information that can be
displayed in v$ views.
v$session – lists information about the current sessions.
You can use this information to "kill" a session if
necessary where a session may be hung up or where the
system user may no longer be authorized database
access.
v$sysstat – this view gives information about system
performance that is used by a DBA to perform database
tuning.
v$sga – lists summary information about the system
global area.
Copyright 2004 Prentice Hall, Inc.
12-50
Object Privilege Views
•
•
•
•
dba_tab_privs – lists all object privileges granted to a
user.
dba_col_privs – lists all privileges granted on specific
columns of a table.
session_privs – lists the privileges held by a user for the
current logon session.
table_privileges – this lists information on object grants
for which you are the grantor, grantee, or owner, or
where PUBLIC is the grantee.
Copyright 2004 Prentice Hall, Inc.
12-51
Role and Privilege Views
•
•
•
•
•
•
dba_roles – lists all roles in the database.
dba_role_privs – lists roles granted to system users and
to other roles.
dba_sys_privs – lists system privileges granted to users
and roles.
role_role_privs – lists roles granted to roles.
role_sys_privs – lists all system privileges granted to
roles.
role_tab_privs – lists table privileges granted to roles.
Copyright 2004 Prentice Hall, Inc.
12-52
Other Data Dictionary Views
•
•
•
•
•
•
•
•
all_all_tables – lists information about all object tables and
relational tables that you can access as a system user.
all_users – simply lists all users that are visible to you as a
system user, but this view does not describe the users.
sys.dba_tablespaces – lists information about tablespaces that
exist in the system.
sys.dba_data_files – lists information about data files
including which tablespaces are allocated to which data files.
user_constraints – lists constraint definitions for tables in your
schema.
user_indexes – lists information about your indexes for tables.
user_sequences – lists information about sequences you have
created.
user_tables – lists information about your tables.
Copyright 2004 Prentice Hall, Inc.
12-53