Get documents about "DataWarehouse Discoverer Development Tool Forms Reports PLSQL
Document Sample
Discoverer
Forms& Reports
Pro*C
S.Q.L.
PL/SQL
Development Tool
DataWarehouse
UniTO Lezione 1 07/05/03 1
Oracle Architectural Components (Physical)
Memory
Instance
Processes
DataBase
Files
UniTO Lezione 1 07/05/03 2
Shared pool SGA
Library
User cache Data buffer Redo log
process Data dict. cache buffer
cache
PGA
SMON
Server DBWR LGWR
process
PMON
Others
Data
files Control ARCH
Redo
ALERT file log
Parameter
file files
file
Trace Password Archived
files file log files
UniTO Lezione 1 07/05/03 3
Parameter File Example
Parameter File Example
•• db_name
db_name = UNITO
= UNITO
•• control_files
control_files = (/DISK1/control01.con,
= (/DISK1/control01.con,
•• /DISK2/control02.con)
/DISK2/control02.con)
•• db_block_size
db_block_size = 8192
= 8192
•• db_block_buffers = 2048
db_block_buffers = 2048
•• shared_pool_size = 52428800
shared_pool_size = 52428800
•• log_buffer
log_buffer = 64K
= 64K
•• processes
processes = 50
= 50
•• db_files
db_files = 1024
= 1024
•• log_files
log_files = 10
= 10
•• max_dump_file_size = 10240
max_dump_file_size = 10240
•• background_dump_dest = (/home/disk3/unito/BDUMP)
background_dump_dest = (/home/disk3/unito/BDUMP)
•• user_dump_dest
user_dump_dest = (/home/disk3/unito/UDUMP)
= (/home/disk3/unito/UDUMP)
•• core_dump_dest
core_dump_dest = (/home/disk3/unito/CDUMP)
= (/home/disk3/unito/CDUMP)
•• rollback_segments
rollback_segments = (r01,r02,r03,r04,r05,r06,r07,r08)
= (r01,r02,r03,r04,r05,r06,r07,r08)
•• ...
... UniTO Lezione 1 07/05/03 4
Startup
STARTUP OPEN
All files opened as
described by the control
MOUNT file for this instance.
Control file
opened for this
NOMOUNT instance.
Instance
started.
SHUTDOWN
UniTO Lezione 1 07/05/03 5
Shutdown
Shutdown Mode A I T N
Allow new connections x x x x
Wait until current sessions end x x x o
Wait until current transactions end x x o o
Force a checkpoint and close files x o o o
Shutdown mode:
A Abort I Immediate x NO
T Transactional N Normal o YES
UniTO Lezione 1 07/05/03 6
Transaction
S0 Consistent State
S2 = S0 (99%)
Consistent
Set Statement SQL-DML
Not Accept
State
S1 Consistent Require State Commit RollBack
UniTO Lezione 1 07/05/03 7
Oracle Architectural Components (Logical)
DataBase Tablespace DataFile Oracle Block
O.S. Block
Owner Partition
Schema Segment Extent
Table
Index
Cluster
Snapshot
UniTO Lezione 1 07/05/03 8
Structure of a Row
Row header
Column length
Database block
Column value
UniTO Lezione 1 07/05/03 9
Oracle Data Types
Data type
User-defined Built-in
Scalar Collection Relationship
CHAR(N), NCHAR(N) VARRAY REF
VARCHAR2(N),
NVARCHAR2(N) TABLE
NUMBER(P,S)
DATE
RAW(N)
BLOB, CLOB,
NCLOB, BFILE
LONG, LONG RAW
ROWID, UROWID
UniTO Lezione 1 07/05/03 10
Data Dictionary
Data Dictionary
The data dictionary provides information about:
TheCentral to every Oracle database
•• Central to every Oracle database
data dictionary provides information about:
Logical and physical database structure
•• Describes the database and its structure
Describes the database and its objects
Logical and physical database objects
Definitions and space allocations of objects
•• Contains read-only tables and views objects
Definitions and space allocations of
Contains read-only tables and views System
Integrity constraints
•• Updated by SQL commands:
Integrity constraints tablespace
Updated by SQL commands:
•• Users
– DDL
Users
– DDL
•• Roles
– Some DML
Roles
– Some DML
• Privileges the user SYS
• Owned by the user SYS
Privileges
Owned by
Other
Auditing the SYSTEM tablespace
•• Stored in the SYSTEM tablespace
Auditing
Stored in database
Other information
•• Accessed with SELECT statements
Other information files
Accessed with SELECT statements
Database
UniTO Lezione 1 07/05/03 11
Data Dictionary Views
Data Dictionary Views
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
UniTO Lezione 1 07/05/03 12
Statement SQL Syntax Validation
Terminology Validation
Parse Grant / User Validation
Execution Plan
Bind
OK
Implicit Cursor
Describe
KO
Execute
OK
Fetch
UniTO Lezione 1 07/05/03 13
KO
Data Base Administrator Responsibility
Installing & Upgrading R.D.B.M.S. + Application Tools
Creating Primary DB Structure & Primary Objects
Planning Storage for User Objects
Modifying DB Structure
Enrolling Users
Monitoring & Controlling User Access
Backing Up & Recovering
Maintaining & Security
Tuning (Optimizing Performances)
Consulting (Development)
UniTO Lezione 1 07/05/03 14
