[root@myLinux ~]# env | grep LANG ------------>查看本地字符编码 ???如何修改字符集
LANG=en_US.UTF-8
[root@myLinux ~]# top -------------> 查看系统进程、登陆用户等信息
[root@myLinux ~]#
top - 22:07:55 up 1:09, 1 user, load average: 0.40, 0.22, 0.08
Tasks: 48 total, 1 running, 47 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 1091388k total, 314132k used, 777256k free, 25516k buffers
Swap: 1052248k total, 0k used, 1052248k free, 227984k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 root 16 0 2232 596 508 S 0.0 0.1 0:00.83 init
2 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/0
4 root 5 -10 0 0 0 S 0.0 0.0 0:00.01 events/0
[root@myLinux ~]# rpm -qa |grep vim -------------->查看有没有安装vim
vim-minimal-6.3.046-0.40E.7
vim-common-6.3.046-0.40E.7
vim-enhanced-6.3.046-0.40E.7
第一课：
oralce 体系结构包括两部分：
1.Instance
2.Database
Oracle Server:
是一个最大的概念。可以是物理上的服务器。也可以是一整套软件。
是一个数据库管理系统，提一个开放的 广泛的 完整的 管理信息的方法。（Is a database management system that provides an open comprehensive integrated approach to information management）
包含Instance和DataBase两个部分。
Oracle Instance：
访问Oracle Database的一个中介，一个手段。
一个Instance仅仅只能打开一个databses。但一个database可以被多个Instance打开。
由内存和后台进程组成的
Connection & Session
tcp 连接
process: 1.后台进程(Oracle prosess) 2.服务器进程(Server process) 3.用户进程(User process).
1注重于数据 2则注重于提供用户连接
Oracle Database
说白了就是一堆文件
被当作一个单位的数据的集合。 Is a collection of data that is treated as a unit
包括三种文件类型：
Data files
Control files
RedoLog files :用于恢复Data files
--SQL> ps -ef ;(查看进程)
Memory Structure
包含两块： 1.SGA(System Global Area):
Instance的一个基本组件。帮助Instance启动用的。 一个Instance一个SGA。
Allocated at instance start up,and is a fundamental component of an Oracle instance.
2.PGA(Program Global Area):
Allocated when the server process is started.
第二课：
SGA包含：
1.Shared Pool
2.Database Buffer Cache
3.Redo Log Buffer
4.other structures(for example.lock and latch management,statistical data)
5.Large Pool(可选)
6.Java Pool(可选)
--SQL> show sga;
SGA 在9i以后可以动态调整大小，参数为SGA_MAX_SIZE,基本单位为：granules，对于小于128M的SGA，granules为4M，大于的为16M。
SGA是一个连续分配的内存区域。
--SQL>select component,granule_size from v$sga_dynamic_components;(查看SGA)
Shared Pool
Used to store:
Most recently executed SQL statements.
Most recently used data definitions.
it consists of two key performance-related memory structures
Library Cache(存储经过解析后的SQL语句与存储过程语句)
Data Dictionary Cache(存储描述数据库各个方面的数据库控制信息)
Database Buffer Cache
包含三个独立的子缓存：
DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
PGA：
一个process一个PGA
第三课 进程
Process 在windows平台下指线程，在unix/linux下指进程
User process
Server process
Oracle process
IPC :Inter Process Communication 包括共享内存 队列 信号量等几种形式。
Background Process
Mandatory background processes:
DBWn PMON CKPT LGWR SMON
Optional background processes:
ARCn LMDn QMNn CJQ0 LMON RECO Dnnn LMS Snnn LCKn Pnnn
DBWn:Database Writer
LGWR:Log Writer
SMON:System Monitor (系统清洁员)
PMON:Process Monitor (process 清洁员)
CKPT:Checkpoint(辅助LGWR)
Logical Structure
第四课
练习
第五课
Oracle Universal Installer :OUI
oralce统一安装工具
用java编的，各个操作平台都能跑。
Oracle Database Configuration Assistant :DBCA
Database Administrator Users :
自动创建两个账户：
SYS： 数据字典的所有者 默认密码：change_on_install
SYSTEM: 权力比SYS小。
DB UA
ONM
Oracle Enterprise Manager:OEM
为DBA提供把系统集中起来管理的工具
可以管理多个数据库
可以管理网络上的多个节点，从各个地方获得服务。
可以用来于与其他管理员共同完成工作
提供用来管理类似服务和重复的数据库的工具。
但是不稳定，版本变化大。
SQL*Plus
sqlplus /nolog
第六课
PFile：
SPFile：
由PFile生成，
CREATE SPFILE='$ORACLE_HOME/dbs/spfileDBA01.ora' from PFILE ='$ORALCE_HOME/dbs/initDBA01.ora'
能够在Instance启动前或者启动后被执行
Modify Parameters in SPFILE
Alert system set parameter=value<comment='text'><deferred><scope=memory|spfile|both><sid='sid|*'>
第八课
spfile VS pfile
1.spfile 可以被RMAN备份，pfile不行。
2.spfile是二进制文件，必须通过命令修改，减少人为错误。
3.远程启动服务器时pfile会被download，导致多处存在pfile。难以统一版本。
4.spfile容易找到。
所以先用pfile生成spfile，以后就用spfile，不要用pfile。
第九课
startup command behavior
-spfileSID.ora
-Default SPFILE ---> spfile.ora
-initSID.ora
-Default PFILE ---->???
for e.
startup pfile='%url%/XXX.ora'
startup 和shutdown immediate 是功能强大的命令，必须拥有管理权限的人才能使用。
两种情况自动拥有Oralce数据库管理权限：
1.拥有操作系统顶级权限的用户自然拥有Oralce数据库管理权限。
2.数据库采用密码管理，并且用户被授予了SYSDBA或者SYSOPER权限。
***** restrict vt. 限制; 约束
启动数据库的四种状态 (可以通过alter命令一步一步打开 但是不能一步一步关闭)
1.shutdown
2.nomount（Instance started）
3.mount
4.open
Instance如何启动：
When Oracle Databases starts an instance,it reads the server parameter file(spfile) or initialization parameter file to determine the values of
initialization parameters Then,it allocates an SGA,which is a shared area of memory used for database information,and creates background
processes.At this point,no database is associated whith these memory structures and processes.
when the instance starts ,the database writes all explicit parameter settings to the alert log in valid parameter syntax.If necessary,you can
copy and paste this text into a new parameter file and restart the instance.
DB如何被Mounted
The instance mounts a database to associate the database with that instance.To mount the database ,the instance finds the database control
files and opens them.Control files are specified in the CONTROL_FILES initialization parameter in the parameter file used to start the instance .Oracle
Database then reads the control files to get the names of the database's datafiles and redo log files.
At this point ,the database administrator can keep the database closed while completing specific maintenance operations.However,the database is
not yet available for normal operations.
DB如何打开(处于正常工作的状态)
Opening a mounted database makes it available for normal database operations.Any valid user can connect to an open database and access its information.
Usually,a database administrtor opens the database to make it available for general use.
When you open the database ,Oracle Database open the online datafiles and redo log files.If a tablespace was offline when the database was previously
shut down,the tablespace and its corresponding datafiles will be offline when you reopen the database.
If any of the datafiles or redo log files are not present when you attempt to open the database,then Oracle Database returns an error.You must perform
recovery on a backup of any damaged or missing files before you can open the database.
第十课
STARTUP Command
startup [force][restrict][nomount][migrate][quiet][pfile='<file_name>'][mount [exclusive]<database_name>x] | open<read {only | write[recver]} | recover><database_name>]
[force]:强制启动 ，清空历史旧账。
ALTER DATABASE Command
alter database db01 mount
alter database db01 open read only
Restricted Mode(受限模式)
startup restrict
alter system enable restricted session
踢掉登录中的用户：
select sid,serial# username from v$session;
Xx Yy Zz
alter system kill session 'Xx,Yy';
Read-Only Mode(只读模式)
startup mount
alter database open read only
Can be used to:
-Execute queries
-Execute disk sorts using locally managed tablespaces
-Take data files offline and online ,but not tablespaces
-Perform recovery of offline data files and tablespaces
关闭数据库
Close a Database
When you close a database .Oracle Database writes all database data and recovery data in the SGA to the datafiles and redo log files,resoectively,Next,Oracle Database closes all online datafiles and redo log files.(Any offline
datafiles of any offline tablespaces have been closed already.If you subsequently reopen the database ,anytablespaces that was offline and its datafiles remain offline and closed,respectively) At this point,the database is closed and inaccessible for normal operations.The control files remain open after a tatabase is closed but still mounted.
Unmount a Database
After the database is closed,Oracle Database unmounts the database to disassociate it from the instance.At this point,the instance remains in the memory of your computer.
After a database is unmounted ,Oracle Database closes the control files of the database.
Shut Down an Instance
The final step in database shutdown is shutting down the instance.When you shut down an instance,the SGA is removed from memory and the background processes are terminated.
第十一课
Diagnostic Files(诊断文件)
--Contain information about significant events encountered
--Used to resolve problems
--Used to better manage the database on a day-to-day basis
Several types exist:(分为三类)
--alertSID.log file
--Background trace files : SID_processName_PID.trc
--User trace files : SID_ora_PID.trc
Alert Log File:
--Records the commands
--Records results of major events
--Used for day-to-day operstional information
--Used for diagnosing database errors
Each entry has a time stamp associated with it.
Must be managed by DBA
Location defined by BACKGROUND_DUMP_DEST(SQL> show parameter background_dump_dest)
Background Trace Files
--Log errors detected by any background process
--Are used to diagnose and troubleshoot errors
Created when a background process encounters an error
Location defined by BACKGROUND_DUMP_DEST
User Trace Files
--Produced by the user process
--Can be generated by a server process
--Contain statistics for traced SQL statements
--Contain user error messages
Created when a user encounters user session errors
Location is defined by USER_DUMP_DEST
Size Defined by MAX_DUMP_FILE_SIZE
Enable/Disable User Tracing
Session level:
--Using the ALTER SESSION command:
alter session set SQL_TRACE=true;
--Executing DBMS procedure:
dbms_system.SET_SQL_TRACE_IN_SESSION
Instance level:
--Setting the initialization parameter
SQL_TRACE=TRUE
第十二课
insufficient privileges
绝对不能下载文件到本地来修改！！！！！！！！！！！！！！！！！！！！
否则会造成稀奇古怪的错误！！！！！！！！！！！！
startup pfile='/u01/admin/denver/pfile/initdenver.ora.2312009223146';
Background Trace Files
--Log errors detected by any background process
--Are used to diagnose and troubleshoot errors
Created when a background process encounters an error
Location defined by BACKGROUND_DUMP_DEST
SQL> desc dba_users;
SQL> select username,account_status from dba_users;
SQL> alter user hr account unlock;
SQL> alter user hr identified by hr;
[oracle@myLinux dbs]$ sqlplus hr/hr;
SQL> startup mount;
SQL> alter database open read only;
数据库被改为只读状态后必须先关闭才能恢复为正常状态。
第十三课 Chapter 4 Creating a Database
数据库命名最好遵循OFA规范。
OFA involves three major rules:
--Establish a directory structure where any database file can be stored on any disk resource.
--Separate objects with different behavior into different tablespaces.
--Maximize database reliability and performance by separating database components across different disk resources.
第十四课
修改环境变量
[oracle@myLinux dbs]$ env | grep ORA
ORACLE_SID=denver ///修改前
ORACLE_BASE=/u01
ORACLE_HOME=/u01/oracle
[oracle@myLinux dbs]$ export ORACLE_SID=chris
[oracle@myLinux dbs]$ env | grep ORA
ORACLE_SID=chris ///修改后
ORACLE_BASE=/u01
ORACLE_HOME=/u01/oracle
工作环境下的数据库必须置为Archive 模式，否则数据库崩溃之后无法恢复。
第十五课 手工创建数据库
Creating a DB Manually
--Choose a unique instance and database name
--choose a database character set
--Set operating system variables
--Create the initialization parameter file
--Start the instance in NOMOUNT stage
--Create and execute the CREATE DATABASE command
--Run scripts to generate the data dictionary and accomplish post-creation steps
--Create additional tablespaces as needed
Step1:Specify an Instance Identifier(SID)(指定SID)
Step2:Ensure That the required Environment Variables Are Set
Step3:Choose a Database Administrator Authentication Method
Step4:Create the Initialization Parameter File
Step5:(Windows Only)Create an Instance
Step6:Connect to the Instance
Step7:Create a Server Parameter File
Step8:Start the Instance
Step9:Issue the CREATE DATABASE Statement
Step10:Create Additional Tablespaces
Step11:Run Scripts to Build Data Dictionary Views
Step12:Run Scripts to Install Additional Options(Optional)
Step13:Back up the Database
Step14:Enable Automatic Instance Startup.(Optional)
SID最长12 Oracle Name最长8，但是一般Oracle Name和SID保持一致 所以SID也不要超过8。
清除一个数据库：三个地方删除
[oracle@myLinux u01]$ ls
admin Disk3 doc jre oracle oradata oraInventory oui
[oracle@myLinux u01]$ cd admin
[oracle@myLinux admin]$ ls
denver
[oracle@myLinux admin]$ rm -fR denver/ <----------------
[oracle@myLinux admin]$ ls
[oracle@myLinux admin]$ cd /i01/oradate/
-bash: cd: /i01/oradate/: No such file or directory
[oracle@myLinux admin]$ cd u01/oradata/
-bash: cd: u01/oradata/: No such file or directory
[oracle@myLinux admin]$ cd /u01/oradate/;
-bash: cd: /u01/oradate/: No such file or directory
[oracle@myLinux admin]$ cd /u01;
[oracle@myLinux u01]$ ls
admin Disk3 doc jre oracle oradata oraInventory oui
[oracle@myLinux u01]$ cd oradata
[oracle@myLinux oradata]$ ls
denver
[oracle@myLinux oradata]$ rm -fR denver/ <----------------
[oracle@myLinux oradata]$ cd dbs
-bash: cd: dbs: No such file or directory
[oracle@myLinux oradata]$ cd
[oracle@myLinux ~]$ cd dbs
[oracle@myLinux dbs]$ ls
initdenver.ora initdw.ora init.ora lkDENVER lkORACLE orapwdenver spfiledenver.ora
[oracle@myLinux dbs]$ rm -f initdenver.ora orapwdenver lkDENVER <----------------
[oracle@myLinux dbs]$ ls
initdw.ora init.ora lkORACLE spfiledenver.ora
[oracle@myLinux dbs]$
ORACLE_BASE=/u01
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_SID=bbk
ORACLE_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
PATH=$ORACLE_HOME/bin:$PAHT
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_NLS33 PATH LD_LIBRARY_PATH
---》设置环境变量的文件bbk.env 的内容
注意 ：
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
ORACLE_SID=bbk
ORACLE_BASE=/u01
ORACLE_HOME=/u01/oracle
ORACLE_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
PATH=$ORACLE_HOME/bin:$PATH:
LD_ASSUME_KERNEL=2.4.19
LD_LIBRARY_PATH=$ORACLE_HOME/Lib:$LD_LIBRAR_PATH
export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH LD_ASSUME_KERNEL LD_LIBRARY_PATH
stty erase ^h
DISPLAY=192.168.0.21:0; export DISPLAY
---------------------------------------------------------------------------------------------
[oracle@myLinux dbs]$ ll
total 60
-rw-r--r-- 1 oracle oinstall 219 Apr 5 16:42 bbk.env
-rw-r--r-- 1 oracle oinstall 12920 Mar 8 2002 initdw.ora
-rw-r--r-- 1 oracle oinstall 8384 Apr 2 22:01 init.ora
-rw-rw---- 1 oracle oinstall 24 Apr 5 11:37 lkORACLE
-rw-r----- 1 oracle oinstall 3584 Apr 5 11:50 spfiledenver.ora
[oracle@myLinux dbs]$ chmod +x bbk.env <---------------------------------给bbk.env加权限
[oracle@myLinux dbs]$ . ./bbk.env <---------------------------------执行
第十六课
cp -R denver/ bbk ----> 拷贝目录
%s/aaa/bbb/g ----> 替换aaa 为bbb
[oracle@myLinux dbs]$ orapwd file=orapwbbk password=oracle entries=10 -----> 创建pwd文件
[oracle@myLinux ~]$ tail -f /u01/admin/bbk/bdump/alert_bbk.log ------>查看log文件
[oracle@myLinux dbs]$ strings spfiledenver.ora > initbbk2.ora -------> 复制文件内容到另一个文件
df -k ----> 查看硬盘空间
第十七课
0.设置环境变量
1.建立pfile 。注意统一oracle_sid.
2.在/u01/oradata下建立%SID%目录。
3.然后建立/u01/oradata/%SID%/archive目录
4./u01/admin/%SID% 建立目录结构。
drwxr-xr-x 2 oracle oinstall 4096 Apr 11 16:43 bdump
drwxr-xr-x 2 oracle oinstall 4096 Apr 11 16:43 cdump
drwxr-xr-x 2 oracle oinstall 4096 Apr 11 16:43 create
drwxr-xr-x 2 oracle oinstall 4096 Apr 11 16:43 pfile
drwxr-xr-x 2 oracle oinstall 4096 Apr 11 16:43 udump
5.准备sql文件。
6.创建pwd文件
7. SQL> @$ORACLE_HOME/dbs/crdb01.sql; --------------->执行sql文件
8.SQL> create tablespace mytbs datafile '/u01/oradata/bbk2mytbs01.dbf' size 100M extent management local; ---> 创建用户表空间
9.SQL>spool log1.log
SQL>@?/rdbms/admin/catalog.sql;
10.SQL> spool log2.log
SQL> @?/rdbms/admin/catproc.sql
11.SQL> spool log3.log
SQL> @?/sqlplus/admin/pupbld.sql
第十八课
Data Dictionary
是每一个数据库的中心
包含数据库的描述和它的对象
它的表和视图都是只读的
在SYSTEM tablespace 中存储
SYS账户拥有
由Oracle server 维护
用select关键字查询
由两部分组成：
1.基本表：保存数据库的描述，在使用关键字CREATE DATABASE之后创建。
2.数据字典视图：用来简化基本表的信息，被catalog.sql创建，Accessed through public synonyms.
数据字典提供的信息包括：
逻辑上的和物理上的数据库结构
对象的定义和空间分配
数据完整性的约束
用户
角色
权限
审计
How the DD is used
Oracle server uses it to find information about:
users
schema objects
storage structures
Oracle server modifies it when a DDL statement is executed.
Users and DBAs use it as a read-only reference for information about the database.
DD View Categories（Category 种类）
Three sets of static views
Distinguished by their scope:
- DBA :What is in all the schemas
- ALL :What the user can access
- USER:What is in the user's schema
SQL> col object_name format a30
SQL> select object_name , object_id from user_objects;
第十九课 1167
Dynamic Performance Tables 动态性能视图
Virtual tables : 实际上它不是表，是在内存中的信息的一种表现形式。
Record current database activity: 记录数据库当前的情况。
Continually updated while the database is operational:及时更新数据
Information is accessed from memory and control file:从内存和控制文件中获取数据。
Used to monitor and tune the database.
Owned by SYS user.
Synonyms begin with V$.
Listed in V$FIXED_TABLE.
SQL> select * from v$controlfile;
SQL> select * from dictionary ;
V$DATABASE 查询数据库名
V$THREAD instance 名
V$PARAMETER 数据库blocks的size
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
bbk2
第二十课 1168
Control File
A small binary file
Defines current state of physical database
Maintains integrity of database
Required:
At mount state during database start up
To operate the database
Linked to a single database
Loss may require recovery
Sized initially by Create Database
只要数据文件和联机重做文件不丢失，就能够恢复数据库。-------TOM大师
Starting the Instance (nomount)
Reading the initialization file from $Oracle_home/dbs in the following order:
- First spfileSID.ora
- If not found then ,spfile.ora
- If not found then ,initSID.ora
Allocating the SGA
Starting the background processes
Opening the alertSID.log file and the trace files.
Mounting the Database(Mount)
Associating a database with a previously started instance
Locating and opening the control files specified in the parameter file
Reading the control files to obtain the names and status of the data files and online redo log files. However,no checks are performed to verify the existence of the data files and online redo log files at this time.
Opening the Database(OPEN)
Opening the online data files
Opening the online redo log files
If any of the data files or online redo log files are not present when you attempt to open the database,the Oracle server returns an error.
During this final stage,the Oracle server verifies that all the data files and online redo log files can be opened and checks the consistency of the database.If necessary ,the SMON background process initiates instance recovery.
A control file contains the following entries:
Database name and identifier
Time stamp of database creation
Tablespace names
Names and locations of data files and online redo log files
Current online redo log file sequence number
Checkpoint information
Begin and end of undo segments
Redo log archive information
Backup information
What does Tom say....
The control file is a fairly samll file(it can grow up to 64M or so in extreme cases)that contains a directory of the other files Oracle needs.The parameter file tells the instance where the control files are,and control files tell the instance where the database and online redo log files are.
The control files alse tell Oracle other things,such as information about checkpoints that have taken place,the name of the database (which should match the DB_NAME parameter ),the timestamp of the database as it was created ,an archive redo log history(this can make a control files large in some cases),RMAN information ,and so on.
Control files should be multiplexed either by hardware(RAID) or by Oracle when RAID or mirroring is not available.More than one copy of them should exist, and they should be stored on separate disks, to avoid losing them in the event you have a disk failure.It is not fatal to lose your control files - it just makes recovery that much harder.
Control files are something a developer will probably never have to actually deal with.To a DBA they are an important part of the databse ,but to a software developer they are not extremely relevant.
Multiplexing the control file
Every Oracle Database should have at least two control files,each stored on a different physical disk.If a control file is damaged due to a disk failure,the associated instance must be shut down.Once the disk drive is repaired,the damaged control file can be restored using the intact copy of the controlfile from the other disk and the instance can be restarted .In this case,no media recovery is required.
The behavior of multiplexed control files is this:
The database writes to all filenames listed for the initialization parameter CONTROL_FILES in the database initialization parameter file.
The database reads only the first file Listed in the CONTROL_FILES parameter during database operation.
If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.
Note : Oracle strongly recommends that your database has a minimum of two control files and that they are located on separate physical disks.
第二十一课 1169
增加一个control file
1 SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/oradata/bbk2/control01.ct
l, /u01/oradata/bbk2/control02
.ctl, /u01/oradata/bbk2/contro
l03.ctl
2 SQL> alter system set control_files='u01/oradata/bbk2/control01.ctl','u01/oradata/bbk2/control02.ctl','u01/oradata/bbk2/control03.ctl','u01/oradata/bbk2/control04.ctl' scope=spfile;
System altered.
3 SQL> shutdown immediate
4 [oracle@myLinux dbs]$ cd /u01/oradata/bbk2
[oracle@myLinux bbk2]$ cp control01.ctl control04.ctl
Get Control File Information
Information about control file status and locations can be retrieved by querying the following views.
v$controlfile:Lists the name and status of all control files associated with the instance.
v$parameter:Lists status and location of all parameters.
v$controlfile_record_section:Provides information about the control file record sections.
show parameter control_files:Lists the name,status,and location of the control files.(与二是一样的)
第二十二课 Chapter 7
Maintaining Online Redo Log Files
Using Online Redo Log Files
Online Redo log files have the following Characteristics:
Record all changes made to data
Provide a recovery mechanism
Can be organized into groups
At least two groups required
Online Redo Log File Groups
A set of identical copies of online redo log files is called an online redo log file group
The LGWR background process concurrently writes the same information to all online redo log files in a group.
The Oracle server needs a minimum of two online redo log file groups for the normal operation of a database.
Online Redo Log File Members
Each online redo log file in a group is called a member
Each member in a group has identical log sequence numbers and are of the same size.
The LSN(log sequence number)is assigned each time that the oracle server writes to a log group to uniquely identify each online redo log file.
Multiplexed Redo Log Files
To protect against a failure involving the redo log itself, Oracle Database allows a multiplexed redo log,meaning that two or more identical copies of the redo log can be automatically maintained in separate locations.For the most benefit ,these locations should be on separate disks.Even if all copies of the redo log are on the same disk, however ,the redundancy can help protect against I/O errors, file corruption ,and so on.When redo log files are multiplexed,LGWR concurrently writes the same redo log information to multiple identical redo log files,thereby eliminating a single point of redo log failure.
Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies.Each identical copy is said to be a member of the group.Each redo log group is defined by a number ,such as group 1 ,group 2 ,and so on.
Note:Oracle recommends that you multiplex your redo log files.The loss of the log file data can be catastrophic if recovery is required.Note that when you multiplex the redo log ,the database must invrease the amount of I/O that it performs. Depending on your configration ,this may impact overall database performance.
How Redo Files Work
Online Redo log files are used in a cyclic fashion
When a online redo log file is full,LGWR will move to the next log group.
- Called a log switch(The act of switching from one log file group to the other is called a log switch)
- Checkpoint operation also occurs
- Information written to the control file
A checkpoint is the writing of dirty(modified) blocks from the buffer cache to disk.
log switch 一定会引发 checkpoint ，但相反不成立。
When will LGWR write redo?
When a transaction commits
Every three seconds
When the Redo Log Buffer becomes one-third full
When there is more than a megabyte of changed records in the Redo Log Buffer.
Before the DBWn writes modified blocks in the Database Buffer Cache to the data files.
第二十三课
LS & LSN
A log switch is the point at which the database stops writing to one redo log file group and begins writing to another.Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file.However,you can configure log switchs to occur at regular intervals,regardless of whether the current redo log file is completely filled.You can also force log switches manually.
Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it.When the database archives redo log files,the archived log retains its log sequence number.A redo log file that is cycled back for use is given the next available log sequence number.
Each online or archived redo log file is uniquely identified by its log sequence number.During crash,instance ,or media recovery,the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.
Forcing a log switch:
alter system switch logfile;
SQL> select group#,thread#,sequence#,status from v$log; ------> 查看当前使用的logfile
Checkpoints can be forced by:
- Setting FAST_START_MTTR_TARGET parameter
FAST_START_MTTR_TARGET = 600
- Alert system checkpoint command
Alert system checkpoint;
SQL> show parameter fast
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 300
fast_start_parallel_rollback string LOW
SQL> alter system set fast_start_mttr_target=600 scope=both; ----->修改checkpotint 写的时间间隔 （与性能相关，谨慎设置）
SQL> alter system checkpoint; -----> 手动触发checkpoint
Adding Redo Files Groups ------增加redo logfiles
SQL> desc V$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
SQL> col member format a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE /u01/oradata/bbk2/redo01a.rdo
1 ONLINE /u01/oradata/bbk2/redo01b.rdo
2 ONLINE /u01/oradata/bbk2/redo02a.rdo
2 ONLINE /u01/oradata/bbk2/redo02b.rdo
3 ONLINE /u01/oradata/bbk2/redo03a.rdo
3 ONLINE /u01/oradata/bbk2/redo03b.rdo
SQL> alter database add logfile group 4 ----------手工增加logfile
2 ('/u01/oradata/bbk2/redo04.rdo') size 50M;
SQL> alter database add logfile member ----------向已知组里面添加redo logfile 文件
2 '/u01/oradata/bbk2/redo04a.log' to group 4;
第二十四课
Dropping Redo File Members
It is permissible to drop redo log files so that a multiplexed redo log becomes temporarily asymmetric . For example , if you use duplexed groups of redo log files, you can drop one member of one group,even though all other groups have two members each.However, you should rectify this situation immediately so that all groups have at least two members,and thereby eliminate the single point of failure possible for the redo log.
An insance always requires at least two valid groups of redo log files,regardless of the number of members in the groups.(A group comprises one or more members.)If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid.To see a redo log file status,use the v$logfile view.A redo log file becomes INVALID if the database cannot access it .It becomes STALE if the database suspects that it is not complete or correct.A stale log file becomes valid again the next time its group is made the active group.
在生产环境中，每组至少保证有两个redo log file。否则数据库会崩溃。
You can drop a redo log member only if it is not part of an active or current group. If you want to drop a member of an active group ,first force a log switch to occur.
Make sure the group to which a redo log member belongs is archived (if archiving is enabled) before dropping the member.To see whether this has happened , use the v$log view.
When a redo log member is dropped from the database,the operating system file is not deleted from disk.Rather,the control files of the associated database are updated to drop the member from the database structure.After dropping a redo log file,make sure that the drop completed successfully,and then use the appropriate operating system command to delete the dropped redo log file.
SQL> alter database drop logfile member '/u01/oradata/bbk2/redo04a.log'; --------> 在数据库层面删除redo log file。
[oracle@myLinux bbk2]$ rm -f redo04a.log; --------> 在操作系统层面删除redo log file，这才算完全删除。
Dropping Redo Files Groups
An instance requires at least two groups of online redo log files.
An active or current group cannot be dropped.
when an online redo log file group is dropped, the operating system files are not deleted.
Relocate & Rename
You can use operating system commands to relocate redo logs ,then use the Alter Database statement to make their new names(locations) known to the database. This procedure is necessary, for example,if the disk currently used for some redo log files is going to be removed, or if datafiles and a number of redo log files are stored on the same disk and should be separated to reduce contention.
To rename redo log members,you must have the alter database system privilege.Additionally,you might also need operating system privileges to copy files to the desired location and privileges to open and back up the database.
Before relocating your redo logs ,or making any other structural changes to the database , completely back up the database in case you experience problems while performing the operation .As a precaution , after renaming or relocating a set of redo log files, immediately back up the database control file.
最简单的重命名方法：删掉原来的，增加一个新的。
第二种方法：
- shut down the database
- copy the online redo log files to the new location
- place the database in mount mode
- execute the command
- open database for normal operation
Alter database rename file '$home/oradata/u01/log2a.rdo' to '$home/oradata/u02/log1a.rdo'
第二十五课
Clear Redo Files
alter database clear logfile command can be used to reinitialize an online redo log file
Alter database clear logfile group 2;
Use the unarchived keyword to avoid archiving the corrupted online redo log file.
alter database clear unarchived logfile group 2;
要把redo log file 放在不同的磁盘上，这样的话负载才会均衡，而且万一某个磁盘坏掉也不至于丢失数据。
Tips: On Different Disks
When setting up a multiplexed redo log ,place members of a group on different physical disks.If a single disk fails , then only one member of a group becomes unavailavle to LGWR and other members remain accessible to LGWR ,so the instance can continue to function.
If you archive the redo log,spread redo log members across disks to eliminate contention between the LGWR and ARCn background processes .For example ,if you have two groups of multiplexed redo log members(a duplexed redo log),place each member on a different disk and set your archiving destination to a fifth disk.Doing so will avoid contention between LGWR(writing to the members)and ARCn(reading the members).
Datafiles should also be placed on different disks from redo log files to reduce contention in writing data blocks and redo records.
Information about a group and its members can be obtained by querying the following views:
- v$log Displays the redo log file information from the control file
- v$logfile Identifiles redo log groups and members and meber status
- v$log_history Contains log history information
What is Redo Threads?
When speaking in the context of multiple database instance,the redo log for each database instance is also referred to as a redo thread.
In typical configurations,only one database instance accesses an Oralce Database,so only one thread is present.In an Oracle Real Application Clusters environment,however two or more instances concurrently access a single database and each instance has its own thread of redo . A separate redo thread for each instance avoids contention for a single set of redo log files,thereby eliminating a potential performance bottleneck.
Archived Redo Log Files
Filled oneline redo log files can be archived
There are two advantages in running the database in ARCHIVELOG mode and archiving online redo log files:
- Recovery: A database backup together with online and archived redo log files can guarantee recovery of all committed transactions.
- Backup:This can be performed while the database is open.
By default ,the database is created in NOARCHIVELOG mode.
SQL> desc v$database; -----------> 查看当前数据库的模式
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
Archived Redo Log Files
Accomplished automatically by ARCn
Accomplished manually through SQL statments
When successfully archived:
- An entry in the control file is made
- Records:archive log name,log sequence number,and high and low system change number(SCN).
Filled online redo log files cannot be reused until:
- A checkpoint has taken place
- File has been archived by ARCn
Can be multiplexed
Maintained by DBA
第二十六课 Chapter 8
Managing Tablespace & Data Files
Oracle stores data logically in tablespaces and physically in data files.
Tablespaces:
- can belong to only one database at a time
- Consist of one or more data files
- Are further divided into logical units of storage
Data files:
- Can belong to only one tablespace and one database
- Are a repository for schema object data.
1. A database is made up of one or more tablespaces
2. A tablespaces is made up of one or more data files.These files might be cooked files in a file system,raw partitions,ASM managed database files,or a file on a clustered file system.A tablespace contains segments.
3. A segment(table,index and so on)is made up of one or more extens.A segment exists in a tablespace,but may have data in many data files within that tablespace.
4. An extent is a logically contiguous set of blocks on disk.An extent is in a single tablespace and furthermore,is always in a single file within that tablespace.
5. A block is the smallest unit of allocation in the database .A block is the smallest unit of I/O used by a database.
Types of Tablespaces
permanent undo temporary
Creating Tablespaces
create tablespace userdata datafile '/u01/oradata/userdata01.dbf' size 5M;
SQL> select * from v$tablespace ----------------->查看有多少个表空间
SQL> select file_name,tablespace_name from dba_data_files; ----------------->查看表空间的位置
SQL> create tablespace paul datafile ----------------->创建表空间
2 '/u01/oradata/bbk2/paul01.dbf' size 20M;
Tablespace created.
Space Mgmt in Tablespaces
Locally managed tablespace:
- Free extents are managed in the tablespace.
- Bitmap is used to record free extens
- Each bit corresponds to a block or group of blocks
- Bit value indicates free or used
Dictionary-managed tablespace:
- Free extens are managed by the data dictionary.
- Appropriate tables are updated when extends are allocated or deallocated
What does Tom say..
Before we move on,we will look at one more topic related to tablespaces:how extents are managed in a tablespace.Prior to Oracle8.1.5,there was only one method to manage the allocation of extents within a tablespace:a dictionary-managed tablespace.That is,the space within a tablespace was managed in data dictionary tables,in much the same way you would manage accounting data,perhaps with a DEBIT and CREDIT table.
On the debit side,we have all of the extens allocated to objects.On the credit side,we have all of the free extents available for use.When an object needed another extent,it would ask the system to get one.
Oracle would then go to its data dictionary tables,run some queries,find the space(or not),and then update a row in one table(or remove it all together)and insert a row into another.Oralce managed space in very much the same way you will write your applicaitons:by modifying data and moving it around.
This SQL ,executed on your behalf in the background to get the additional space,is referred to as recursive SQL.Your SQL INSERT statement caused other recursive SQL to be executed to get more space.This recursive SQL can be quite expensive if it is done frequently.Such updates to the data dictionary must be serialized;they cannot be done simultaneously.They are something to be avoided.
第二十七课
What does Tom say ...
With a locally-managed tablespace,a bitmap stored in each data file is used to manage the extents.Now to get an extent,all the system needs to do is set a bit to 1 in the bit map.To free space,the system sets a bit back to 0.Compared to using dictionary-managed tablespaces,this is incredibly fast.We no longer serialize for a long-running operation at the database level for a very fast operation.Locally-managed tablespaces have other nice attributes as well,such as the enforcement of uniform extent size,bu that is starting to get heavily into the role of the DBA.
Going forward ,the only storage management method you should be using is a locally-managed tablespace.In fact ,in Oracle9i and above ,if you create a database using the database configuration assistant(DBCA),it will create SYSTEM as a locally-managed tablespace,and if SYSTEM is locally managed,all other tablespaces in that database will be ldocally managed as well,and the legacy dictionary-managed method will not work.It is not that dictionary-managed tablespaces are not supported in a database where SYSTEM is locally managed ,it is that the simply cannot be created.
Temporary Tablespaces
When the SYSTEM tablespace is locally managed ,you must define at least one default temporary tablespace when creating a database.A locally managed SYSTEM tablespace cannot be used for default temporary storage.
If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database,then SYSTEM is still used for default temporary storage.However,you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.
SQL> select tablespace_name,contents,extent_management from dba_tablespaces; --------> 查询表空间的名称和类型
SQL> create temporary tablespace mytemp --------> 创建临时表空间
2 tempfile '/u01/oradata/bbk2/mytemp01.dbf' size 10M
3 extent management local;
SQL> alter database default temporary tablespace mytemp; --------> 修改默认临时表空间
Restrictions
Default temporary tablespaces cannot be:
- Dropped until after a new default is made available
- Taken offine
- Altered to a permanent tablespace
第二十八课 Read-Only Tablespaces
SQL> create tablespace wenchuan ------------> 创建表空间
2 datafile '/u01/oradata/bbk2/wenchuan1.dbf' size 20M
3 extent management local uniform size 128K;
SQL> create user dz identified by dz default tablespace wenchuan; ----------> 创建用户 同时设置表空间
SQL> grant connect,resource to dz; ----------> 赋予权限
SQL> clear scr -------> 清屏
SQL> alter tablespace wenchuan read only; ------> 改变表空间的属性为read only ，注意但是必须没有未提交的事物，否则会阻塞，一直到事务提交。
SQL> truncate table t; --------> 删除表记录，保持表结构。在表空间为只读的情况下不能执行成功。
SQL> drop table t; --------> 删除表，在表空间为只读的情况下能执行成功。
Taking a Tablespace offline
Not available for data access
Tablespaces that cannot be taken offline
- SYSTEM tablespace
- Tablespaces with active undo segments
- Default temporary tablespace
To take a tablespace offline:
alter tablespace userdata offline;
To bring a tablespce online:
alter tablespace userdata online;
SQL> alter tablespace wenchuan offline;
SQL> alter tablespace wenchuan online;
第二十九课 Changing Storage Settings
Alter tablespace userdata minimum extent 2M;
Alter tablespace userdata default storage (initial 2M next 2M maxextents 999);
以上的不要 因为以上的用dictionary管理，应该用local管理。
tablespace可以通过设置一个选项让它自动变大。
------------------------------------------------查询表空间的使用情况------------------------------------------------------------
select a.tablespace_name,a.bytes bytes_use,b.largest,round(((a.bytes - b.bytes)/a.bytes)*100,2) percent_used
from
(select tablespace_name ,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes - b.bytes)/ a.bytes ) desc
SQL> alter tablespace wenchuan add datafile ------------------------> 在表空间不够用时 增加dbf文件。
2 '/u01/oradata/bbk2/wenchuan2.dbf' size 10M
3 autoextend on next 5M maxsize 100M;
SQL> col file_name format a20 ------------------------> 查询dbf文件信息
SQL> select file_name,autoextensible from dba_data_files;
SQL> alter database datafile '/u01/oradata/bbk2/wenchuan1.dbf' ------------------------> 改变dbf文件属性
2 autoextend on next 10M maxsize 100M;
第三十课
SQL> alter database datafile '/u01/oradata/bbk2/wenchuan1.dbf' resize 40M; ------------> 重新设置dbf文件大小，可以由大改小，但是不能小于实际存储数据的大小。
Methods for Moving DF (移动dbf文件)
- Tablespace must be offline.
- Target data files must exist.
Method one:
1. SQL> alter tablespace wenchuan offline;
2. 移动数据文件到需要的地方（/u01/oradata/wenchuan2.dbf）
3. SQL> alter tablespace wenchuan rename datafile '/u01/oradata/bbk2/wenchuan2.dbf' to '/u01/oradata/wenchuan2.dbf';
4. SQL> alter tablespace wenchuan online;
Method two:
1.SQL> shutdown immediate;
2.移动数据文件到需要的地方([oracle@myLinux oradata]$ mv wenchuan2.dbf bbk2/)
3.SQL> startup mount;
4.SQL> alter database rename file '/u01/oradata/wenchuan2.dbf' to '/u01/oradata/bbk2/wenchuan2.dbf';
5.SQL> alter database open;
Dropping Tablespaces
You cannot drop a tablespace if it:
- Is the SYSTEM tablespace;
- Has active segments;
Including contents drops the segments
Including contents and datafiles deletes data files.
Cascade constraints drops all referential integrity constraints.
SQL> drop tablespace wenchuan including contents and datafiles; -----------------> 干掉表空间 ，同时数据文件也干掉了。
Get Tablespace Information
Obtaining tablespace and data file information can be obtained by querying the following:
Tablespace information:
- dba_tablespaces
- v$tablespace
Data file information:
- dba_data_files;
- v$datafile
Temp file information:
- dba_temp_files
- v$tempfile
第三十一课 Chapter Storage Structure & Relationships
segments:就是数据库的对象，比如说表，索引等。
extents:一连串在逻辑上连续的blocks.有可能在物理上不是连续的，分散在不同的扇区。
Types of Segments
- Table
- Table partition (分区表)
- Cluster(集群)
- Index
- Index-Organized (IOT)
- Index partition
- Undo segment
- Temporary segment
- Large Object Segment
- Nested table(嵌套表)
- Bootstrap segment
Storage Clause Precedence(存储依据的优先权)
1.If storage parameters are altered,the new options apply only to the extents not yet allocated.
2.Some parameter cannot be specified at the tablespace level.These parameters must be specified at the segment level only.
3.If minimum extent size has been specified for the tablespace,this size applies to all extents that are allocated for segments in the tablespace in the future.
Extent Alloc & Dealloc
An extent is a chunk of space used by a segment within a tablespace.
An extent is allocated when the segment is:
- Created
- Extended
- Altered
An extent is deallocated when the segment is :
- Dropped
- Altered
- Truncated
Database Block
Minimum unit of I/O
Consists of one or more operting system blocks
Set at tablespace creation
DB_BLOCK_SIZE is the default block size
第三十二课
[oracle@myLinux dbs]$ strings spfilebbk2.ora | grep -i db_block ---------------> 查看设置的block size 的大小
*.db_block_size=8192
Multiple Block Size Support
A database can be created with a standard block size and up to four nonstandard block sizes.(总共支持五种block size)
Block sizes can have any power-of-two value between 2 KB and 32 KB.
Standard Block Size
在创建的时候设立，一旦创建就不能改了。
SYSTEM and TEMPORARY tablespaces 的格式一定是Standard Block Size(标准表空间)
granule:SGA分配的最小内存块。当总内存小于128M的时候是4M，内存越大增加值越多，增加数为4M的整数倍。
SQL> select tablespace_name,block_size from dba_tablespaces; ---------------> 查看表空间的block的大小
Multiple Block Size Rules
All partitions of a partitioned object must reside in tablespaces of the same block size.
All temporary tablespaces,including the permanent ones that are being used as default temporary tablespaces,must be of standard block size.
Index-oganized table overflow and out-of-line LOB segments can be stored in a tablespace with a block size different from the base table.
Block Contents
- Header
- Free space
- Data
Block Space Util Parameters
INITRANS and MAXTRANS:Specify the initial and the maximum number of transaction slots(事务漕) that are created in an index or a data block.The transaction slots are used to store information about transactions that are making changes to the block at a point in time.A transaction uses only one transaction slot,even if it is changing more than one row or index entry.
INITRANS:Guarantees a minimum level of concurrency .It defaults to 1 for a data segment and 2 for an index segment,guarantees a minimum level of concurrency.For example ,if set to 3,INITRANS ensures that at least three transactions can concurrently make changes to the block .If necessary,additional transaction slots can be allocated from the free space in the block to permit more concurrent transactions to modify rows in the block.（确定最小并发数）
MAXREANS:Default value is 255,set the limit for the number of concurrent transactions that can make changes to a data or an index block.When set,this value restricts use of space for transaction slots and therefore guarantees that there is sufficient space in the block for use by row or index data.（确定最大并发数）
PCTFREE:This parameter specifies for a data segment ,the percentage of space in each data block that is reserved for growth resulting from updates to rows in the block.The default for PCTFREE is 10%.(最少要有多少剩余空间)
PCTUSED:For a data segment,this parameter represents the minimum percentage of used space that the Oracle server tries to maintain for each data block of the table.A block is put back on the free list when its used space falls below PCTUSED .The free list of a segment is a list of blocks that are candidates for accommodating future inserts.A segment ,by default, is created with one free list.Segments can be created with a higher number of free lists by setting the FREELISTS parameter of the storage clause.The default for PCTUSED is 40%.Both PCTFREE and PCTUSED are calculated as percentages of available data space,that is ,the block space that remains after deducting the header space from the total block size.
Note:The use of these parameters for indexes is discussed in detail in the "Managing Indexes" lesson.
Specifying FREELISTS is discussed in detail in the course Oracle9i:Performance Tuning.
第三十三课
Data Block Management
Automatic segment-space management
Manual management
Automatic segment-space management：
用bitmaps管理
Better space utilization (更好的空间利用率)
Better performance for concurrent INSTERT operations(更好的实现并发插入)
- Bitmap segments contain a bitmap that describes the status of each block in the segment with respect to its available space.
- The map is contained in a separate set of blocks referred to as bitmapped blocks(BMBs).
- When inserting a new row,the server searches the map for a block with sufficient space.
- As the amount of space available in a block changes , its new state is reflected in the bitmap.
Can be enabled at the tablespace level only,for locally managed tablespaces.(只能在表空间这一级别使用，而且只对本地管理方式的表空间有效。)
Manual management
用freelist管理
通过以下参数管理：PCTFREE PCTUSED FREELIST
SQL> select * from user_objects; ---------------> 当前用户建的表
SQL> desc user_segments;
SQL> select segment_name from user_segments;
HWM (High-Water Mark)
SQL> begin ---------------> 执行存储过程
2 for i in 1 .. 100000
3 loop
4 insert into m values (i,'neo');
5 end loop
6 ;
7 commit;
8 end;
9 /
设置Autotrace,用来监视SQL语句在执行过程中资源使用情况。
1.SQL> @?/rdbms/admin/utlxplan
2. SQL> create public synonym plan_table for plan_table;
3. SQL> grant all on plan_table to public;
4. SQL> @?/sqlplus/admin/plustrce
5. SQL> grant plustrace to dz;(把trce的权限赋给dz)
或者 SQL> grant plustrace to public;(把trce的权限赋给所有账户)
到此设置完成。
关于Autotrace几个常用选项的说明:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出
SQL> set autotrace on stat ---------------> 用命令打开trace
SQL> set autot off ---------------> 用命令关闭trace
第三十四课
SQL> truncate table m; --------------> 清空表里面的数据，同时重置HWM(与delete命令的区别)
Tom's Suggestion
High HWM and low HWM are relative terms,and that when using ASSM only PCTFREE applies.（高水位与低水位是相对的概念，在使用自动管理模式的时候只能设置PCTFREE参数）
什么情况下使用 High PCTFREE ,low PCTUSED组合：
This setting is for when you insert lots of data that will be updated and updates will increase the size of the rows frequently.This setting reserves a lot of spece on the block after inserts(high PCTFREE)and makes it so that the block must almost be empty before getting back onto the freelist(low PCTUSED)
什么情况下使用 low PCTFREE , High PCTUSED组合：
This setting is for if you tend to only ever insert or delete from the table,or if you do update,the update tends to shrink the row in size.
Getting Storage Information
by querying the following views:
dba_extents
dba_segments
dba_tablespaces
dba_data_files
dba_free_space
第三十五课
Practice 9 - 1
3. --------------------> This query is useful in identifying any segments that are likely to generate errors during futrue data load.
SQL> select segment_name,segment_type,max_extents,extents from dba_segments where extents+5 > max_extents and segment_type <> 'CACHE';
6. --------------------> List the free space available by tablespace (列出表空间里面的空余空间)
SQL> select tablespace_name ,count(*) as fragments,sum(bytes) as total,max(bytes) as largest from dba_free_space group by tablespace_name;
7. --------------------> List segments that will generate errors because of lack of space when they try to allocate an additional extent.(列出在新增extent的时候可能由于空间不够而报错的segments)
SQL> select s.segment_name,s.segment_type,s.tablespace_name,s.next_extent from dba_segments s
2 where not exists
3 (select 1 from dba_free_space f where s.tablespace_name = f.tablespace_name having max(f.bytes) > s.next_extent);
第三十六课 Managing Undo Data
了解Undo Data 的目的
自动管理Undo Date
创建和配置Undo segments
第三十七课
Types of Undo Segments
- SYSTEM: Used for objects in the SYSTEM tablespace
- Non-SYSTEM:Used for objects in other tablespaces:
Auto mode: Requires an undo tablespace
Manual mode:
Private:Acquired by a single instance
Public:Acquired by any instance
- Deferred:Used when tablespaces are taken offline immediate,temporary,or for recovery.
Auto undo mgmt: Concepts
- Undo data is managed using an indo tablespace
- You allocate one undo tablespace per instance with enough space for the workload of the instance.
- The oracle server automatically maintains undo data within the undo tablespace.
Configure two parameters in the initialization file:
- undo_management：specifies whether the system should use auto or Manual mode
- undo_tablespace: specifies a particular undo tablespace to be used.
Create at least one undo tablespace(同一时刻只会有一个有效)
如果在创建数据库的时候没有指定Undo tablespace,那么数据库会自动给你创建一个。
SQL> show parameter undo
SQL> select tablespace_name,contents from dba_tablespaces; --------------------------> 查看当前数据库undo tablespace 的设置
SQL> create undo tablespace myundotbs -------------------------->创建undo tablespace
2 datafile '/u01/oradata/bbk2/myundotbs1.dbf' size 10M;
第三十八课 Managing undo data
AUM : Switching UNDO TS
- You can switch from using one UNDO tablespace to another
- Only one undo tablespace can be in assigned to a database at a time.
- More than one undo tablespace may exist within an instance,but only one can be active.
- Use the alter system command for dynamic switching between undo tablespace.
有未提交事务的情况下可以改变undo tablespace,完全不影响数据库的操作。
AUM : other parameters
undo_supperss_errors
- set to true,this parameter suppresses errors while attempting to execute manual operations in auto mode.
undo_retention
- this parameter controls the amount of undo data to retain for consistent read.单位是秒，指undo数据保存的时间。
SQL> alter system set undo_retention= 900 scope=both; --------------------------> 改变undo数据保持的时间 单位是秒
SQL> select addr,used_ublk from v$transaction; --------------------------> 查询有当前未提交的事务
SQL> select begin_time,end_time,undoblks from v$undostat; --------------------------> 查询undo表空间的历史记录，用于参考决定undo表空间的大小。
第三十九课
Determing a size for the undo tablespace requires three pieces of information:
1. undo_retention in seconds.
2. Number of undo data blocks generated per second.(v$undostat)
3. Overhead varies based on extent and file size(db_block_size).
SQL> select max(undoblks/((end_time - begin_time)*24*3600)) as blockPerSecond from v$undostat; ------------------> 查询每秒最多使用多少个block
BLOCKPERSECOND
--------------
.013333333
SQL> select sum(undoblks) / sum((end_time - begin_time)*24*3600) as averagePerSecond from v$undostat; ------------------> 查询平均每秒使用多少个block
AVERAGEPERSECOND
----------------
.006845053
Get undo segment Info
- dba_rollback_segs (所有的segments的信息)
- v$rollname (在线的segments的信息)
- v$rollstat
- v$undostat
- v$session
- v$transaction
练习题：
1. List the undo segments in tablespace undotbs.（列出某一个表空间的undo segments）
SQL> select segment_name , tablespace_name from dba_segments where tablespace_name ='MYUNDOTBS';
Chapter 11 Managing Tables
第四十一课
Oracle表最大支持1000列，但是除非有特殊需要，每张表不要超过254列，超过之后性能会大大降低。
A tablespace can have at most 1022 files(although there are new bigfile tablespaces in oracle 10g that will get you beyond these file size limits,too)
第四十二课
SQL> select table_name from user_all_tables; -------------------------> 查看当前用户下表的信息
RowId characteristics:
- is a unique identifier for each row in the database
- is not stored explicitly as a column value
- Although the rowid does not directly give the physical address of a row,it can be used to locate the row.
- provides the fastest means of accessing a row in a table
- are stored in indexes to specify rows with a given set of key values.
- 不是一成不变的，在导入导出之后rowid会发生变化
Structure of a Row
Row data is stored in database blocks as variable-length records.Columns for a row are generally stored in the order in which they are defined and any trailing null columns are not stored.
Note:A single byte for column length is required for non trailing null columns.Each row in a table has:
- Row header: Used to store the number of columns in the row,the chaining information ,and the row lock status
- Row data:For each column,the oracle server stores the column length and value (one byte is needed to store the column length if the column will require more than 250 bytes of storage in which case three bytes will be used for column length.The column value is stored immediately following the column length bytes.)
第四十三课
SQL> create tablespace mssm datafile '/u01/oradata/bbk2/mssm_1.dbf' size 100M extent management local uniform size 128K segment space management manual; ------> 创建表空间
SQL> create user as1 identified by as1 default tablespace assm; -----------> 创建用户并指定表空间
SQL> grant connect,resource to as1; -----------> 给as1赋予权限
SQL> create table t(id int,name char(10)) storage (initial 128K next 128K pctincrease 0 minextents 1 maxextents 5 ) tablespace assm; -----------> 创建表时指定表空间
Freelists:MSSM only.
If you anticipate heavy insertion into a table by many concurrent users,configuring more than one freelist can have a major positive impact on performance(at the cost of possible additional storage).
Pctfree:Both ASSM and MSSM.(很重要)
A measure of how full a block can be is made during the insert process.As shown earlier,this is used to control whether a row may be added to a block or not based on how fukk the block currently is.This option is also used to vontrol row migrations caused by subsequent uodates and needs to be set based on how you use the table.
pctused:MSSM only.
A measure of how empty a block must become before it can be a candidate for insertion again.A block that has less than pctused space used is a candidate for insertion of new rows.Again,like pctfree,you must consider how you will be using your table to set this option appropriately.
Initrans:both ASSM and MSSM.
同时操作block的transaction个数。
If you believe you will have many concurrent updates to the same blocks,you should consider increasing this value.
第四十四课
Creating a table : Guidelines
- Place tables in separate tablespaces.
- Use locally-managed tablespaces to avoid fragmentation.
- Use few standard extent sizes for tables to reduce tablespace fragmentation.
Creating Temporary Tables
- creating clause: create global temporary table hr.employees_temp
- Temporary Tables retain data only for the duration of a transaction or session
- DML locks are not acquired on the data
- You can create indexes,views,and triggers on temporary tables.
Temporary Tables
Temporary Tables are used to hold intermediate resultsets,for the duration of either a transaction or a session.The data held in a temporary table is only ever visible to the current session - no other session will see any other session's data ,even if the curren session commits the data.Multiuser concurrency is not an issue with regard to temporary tables either,as one session can never block another session by using a temporary table.Even if we "lock" the temporary table,it will not prevent other sessions using their temporary table.As we observed in charpter9,Temporary Tables generate significantly less redo than regular tables.However, since they must generate undo information for the data they contain,they will generate some amount of redo.Updates and deletes will generate the largest amount;Inserts and selects the least amount.
Temporary Tables will allocate storage from the currently logged-in user's temporary tablespace,or if they are accessed from a definer rights procedure,the temporary tbalespace of the owner of that procedure will be used.A global temporary table is really just a template for the table itself.The act of creating a temporary table involves table is really just a template for the table itself.The act of creating a temporary table involves no storage allocation;no initial extent is allocated,as it would be for a regular table.Rather,at runtime when a session first puts data into the temporary table, a temporary segment for that session will be created.Since each session gets its own temporary segment(not just an extent of an existing segment),every user might be allocating space for her temporary table in different tablespaces.User1 might have his Temporary Tables set to temp1,so his Temporary Tables will be allocated from this space .User2 migt have temp2 as her Temporary Tables,and her Temporary Tables will be allocated there.
临时表和一般表的区别：
临时表创建后没有segment分配。创建临时表只是定义了一张表，知道在运行时刻临时表用到时才会被分配segment。每个session都会得到自己的临时segment（不是已经存在的segment里面的extent。），
Oracle 的Temporary Tables 可以事先定义，定义好就用。（statically defined）.这样做的好处是可以像对待普通表一样，建立索引 视图等。
如果只对临时表进行insert和select 那么产生的redo信息可以忽略不计。
SQL> create global temporary table tmp_session on commit preserve rows as select * from m where 1=0; ---------------利用m的表结构创建一张空的临时表（session级别的）
第四十五课
Setting PCTFREE & PCTUSED
Compute PCTFREE
((Average Row Size - Initial Row Size)* 100)/Average Row Size
Compute PCTUSED
100 - PCTFREE - (Average Row Size * 100 )/Available Data Space
Row Migration
If PCTFREE is set to a low value,there may be insufficient space in a block to accommodate a row that grows as a result of an update.When this happens,the Oracle server will move the entire row to a new block and leave a pointer from original block to the new location.This process is referred to as row migration.When a row is migrated,I/O performance associated with this row decreases beacuse the Oracle server must scan two data blocks to retrieve the data.
Row Chaining
Row chaining occurs when a row is too large to fit into any block.This might occur when the row contains columns that are very long.In this case,the Oracle server divides the row into smaller chunks called row pieces.Each row piece is stored in a block along with the necessary points to retrieve and assemble the entire row.Row chaining can be minimized by choosing a higher block size or by splitting the table into multiple tables with fewer columns,if possible.
修改miniextents没有什么效果，除非表被truncated以后才会生效。
Manually allocating extents:
1.To control the distribution of extents of a table across files.( )
2.Before loading data in bulk to avoid dynamic extension of tables.(在批量插入数据前就做好准备，防止动态（无序）扩展表。)
Note:The next_extent value in dba_tables will not be affected by manual extent allocation.The oracle server will not recalculate the size of the next extent when this command is executed.
第四十六课 重构表
非分区表才能执行的命令：alter table hr.employees move tablespace data1;
除了用desc查看表结构(sqlplus的命令)外，还有一种方法查看表定义的详细信息。
SQL> select dbms_metadata.get_ddl('TABLE','T') from dual; ---------------> 9i版本下有bug，需要打补丁才能正确运行。10g 及以上可以正常使用。
SQL> alter table m move tablespace mssm; ---------------> 更改表空间
Truncating a table
会释放使用的空间，(delete 不一定会)
相关索引也会被删掉。
没有undo数据，无法回滚。而且立马执行，因为是DDL语句。
如果有外键引用，表不能被truncating.
不会触发删除引起的触发器。
Dropping a table
When a table is dropped , the extents used by the table are released.If they are contiguous, they may be coalesced either automatically or manually at a later stage.
The cascade constraints option is necessary if the table is the parent table in a foreign key relationship.
Note : Refer to the "Maintaining Data Integrity" lesson for details regarding cascade constraints.
删除列时，最好在语句后面加上 checkpoint 1000 ，表示1000行提交一次。从而加快执行速度。
如果在删除列时，instance重启了，使用以下语句继续执行删除列：
alter table hr.employees drop columns continue;
第四十七课
SQL > alter table rename column name to myname; ----------------->给列改名。注意：会使得索引，触发器等失效。
可以把列标记为unused ，（意味着不可见）然后在数据库负荷小的时候再删除它,或者删除几列时先打标记再在删除它可以只做一次update，在删除之前可以加上同名列。
SQL > alter table t set unused column myname; -----------------> 把列标记为unused
SQL > alter table t drop unused columns checkpoint 1000; --------------------> 删除标记为unused的列
SQL > alter table t drop columns continue checkpoint 1000; --------------------> 如果数据库在删除unused列过程中挂掉了，可以在再次启动时继续删除unused列。
SQL > col object_name format a30
SQL > select object_name,object_type from dba_obects where object_type = 'TABLE'; ----------------------->查处所有表
获取表信息可以用两个views：
dba_tables
dba_object
dba_extents
SQL > truncate table orders reuse storage;-----------------> truncate表空间时保留占用的block
第四十八课 索引
Classification of Indexes
1.Logical:
- Single column or concatenated
- Unique or nonunique
- Function - based
- Domain
2.Physical:
-Partitioned or nonpartitioned
-B-Tree:Normal or reverse key
-Bitmap
B-Tree:
balance-Tree,意思为后代节点差不多的树。
SQL> create index t_idx1 on t(id); ------------------> 创建索引 t_idx1 索引名 t 表明 id 字段
SQL> create index t_idx2 on t(sex,name); ------------------>sex name 字段
创建索引导致查询速度快，删除和修改速动慢。
第四十九课
B-Tree VS Bitmap
1. 值多样 值种类相对于总记录数非常小
2. 更新操作代价小 更新操作代价高
3. or查询速度慢 or查询速度快
4. Useful for OLTP for data warehousing
创建B-Tree索引要注意的：
1. 建立索引会降低删除和修改的速度，要综合考虑建立索引的必要性。
2. 与表不要在一个表空间里面。便于提高性能。
3. Use uniform extent sizes:Multiples of five blocks or Minimum extent size for tablespace.以表空间设置的最小extent为单位设置增加的extent，优化I/O;
4. 表很大时，创建索引时尽量不要创建logging。
5. Initrans should generally be higher on indexes than on the corresponding tables; Initrans 在索引里面要比对应的表大一些
SQL> create bitmap index my_bit_idx on t(sex); -------------------> 创建位图索引
第五十课
Offline Rebuilding Indexes
1. lock the table
2. 读取老索引新建一个临时索引
3. 删除老索引
4. 重命名临时索引为重构后的索引名
5. remove the table lock
什么情况下需要rebuilding indexes
1.改变index所属表空间的时候
2.index包含很多删除的entries时，即表做了很多删除工作的时候。
3.index需要反向排序的时候
4.把表从一个表空间move到另一个表空间的时候
Online Rebuilding Indexes
1.lock the table
2.新建临时的空的index和用来存储增删改数据的IOT
3.release the table lock
4.读取老索引新建一个临时索引
5.合并IOT的内容到新index
6.lock the table
7.最后合并IOT，删除原来的索引
8.remove the table lock
SQL> alter index t_idx1 rebuild; -----------------> 重构索引
SQL> alter index t_idx1 coalesce; -----------------> 为索引整理碎片
索引碎片程度高则适合用重构索引，程度低则适合整理碎片。
第五十一课
SQL> analyze index t_idx1 validate structure; -------------> 分析索引 t_idx1
SQL> select height,name ,lf_rows,lf_blks,del_lf_rows from index_stats; ----------------> 查看索引状态
HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ------------------------------ ---------- ---------- -----------
2 T_IDX1 1000 3 100
LF_ROWS VS DEL_LF_ROWS 过大就需要rebuild了。
Dropping indexes
1.大批量插入数据的时候。
2.不经常使用的索引
Identifying Unused Indexes
可以通过开启关闭对某个索引的监控来判断索引是否有存在的必要。
SQL> alter index t_idx1 monitoring usage; -------------------> 开启索引监控
SQL> alter index t_idx1 nomonitoring usage; -------------------> 关闭索引监控
**********************************************************开启SQL语句执行监控？计划？
SQL> set autot on exp
SQL> select * from t where id=888;
ID S NAME
---------- - ----------
888 m neo
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE)
**********************************************************
SQL> set autot off;-------------------关闭SQL语句执行监控？计划？
SQL> select * from v$object_usage; ---------------------------->通过查询视图v$object_usage来查询索引的使用情况，前提是打开索引监控
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
-------------------- ------------------------------ --- --- ------------------- -------------------
T_IDX1 T YES YES 05/01/2009 15:48:23
Getting Index Information
dba_indexes
dba_ind_columns
V$object_usage
第五十二课 约束
保持数据完整性的方法以其选择优先顺序：
1.Integrity constraint(约束)
2.Database trigger(触发器)
3.Application code(硬编码)
Types of Constraints
1.not null
2.unique
3.primary key
4.foreign key
5.check
SQL> alter table t modify name not null; -------------------------> 加约束条件，字段name为非空
SQL> create table tt(id int not null,name varchar2(10) not null,sex varchar2(10) not null,age int); ------------>创建表时指定列非空
SQL> alter table tt add constraint age_ct check(age>0); ----------------> 给列加上约束条件
check的参数不能使用伪列，系统函数，查询语句。
第五十三课
unique：
1 单列唯一可以定义在列级别，组合列唯一要定义在表级别。
2 unique约束允许有null，而primary key则不行。
3 Oracle creates a unique index on the unique key columns to enforce uniqueness .If a unique index or a non-unique index already exists on the table with the same columns in the index,Oracle uses the existing index .To use the existing non-Unique index,the table must not contain any duplicate keys.
4 在指定列唯一(unique)后，oracle会自动给该列创建索引，并且不能手动删除该索引。
primary key：
1. not null + unique
2. 一张表只能有一个主键
第五十四课
foreign key：
1.只要主表没有规定列为not null ，则可以插入null值。
2. - Delete no action : 不允许删除有依赖关系的外键值
- Delete cascade : 删除外键的时候同时删除相关父表的记录
- Delete set null : 删除外键的时候同时设置相关父表的记录为null
3.设为外键的列必须有唯一性约束
SQL> alter table dept add primary key(dept_id); ---------------> 给即将要设为外键的列加上唯一性约束
SQL> alter table emp add constraint fk_emp1 foreign key(dept_id) references dept(dept_id) on delete cascade; ---------------> 增加外键约束
Creating Disabled Constraints(创建暂时无效的约束)
SQL> alter table emp add primary key (id) disable; --------------->创建暂时无效的约束
SQL> alter table emp enable primary key; --------------->使约束生效（对于唯一性约束和外键约束，实际上是重建索引）
第五十五课
从disable novalidate 做好不要直接变为 enable validate ，因为如果直接变的话，过程中会把表锁住。最好的方式是先变为enable novalidate ，再变为enable validate。这样的话，两步过程都不会锁表，而且第一步非常快。
SQL> alter table t disable validate primary key;---------------------> 改变表的约束状态
SQL> alter table t disable novalidate primary key;
SQL> alter table t enable validate primary key;
SQL> alter table t enable novalidate primary key;
tip：enable /disable 是指当前的约束生效与否
validate /novalidate 是指是否检查之前插入的数据
注意：只有在索引是非唯一性的时候在能在四种状态下切换，否则切不能成功。
第五十六课
推迟约束
SQL> alter table t add primary key(id) deferrable; -------------> 设置推迟约束主键（数据库自动创建非唯一约束）
SQL> select constraint_name ,status,validated from user_constraints; ----------------> 查看当前约束信息
SQL> set constraints all deferred; -----------> 推迟所有约束
SQL> set constraints all immediate; ------------> 开始所有约束
删除外键：
1.删除父表的时候把子表相应记录干掉
drop table t cascade constraints
2.truncate 父表
disable or drop foreign key
3.删掉包含子表的表空间
drop tablespace tname including contents cascade constraints
在子表（目录）的foreign key 建立索引能加快速度，增加并发。
Guidelines for constraints
primary and unique constraints:
- Place indexes in a separate tablespace
- Use nonunique indexes if bulk loads are frequent
Self-referencing foreign keys:
- define or enable foreign keys after the initial load
- defer constraint checking
第五十七课
SQL> alter table t rename constraint SYS_C001459 to my_constraint; -------------> 给约束重命名
如何找出不符合约束条件的记录：
1. SQL> @?/rdbms/admin/utlexpt1.sql -------------> 创建工具表 用来存放不符合约束的记录
2. SQL> alter table t enable validate primary key exceptions into exceptions; --------------> 让不符合约束的条件导入到第一步建立的表中
3. SQL> select rowid,id ,name from t where rowid in (select row_id from exceptions); ---------------> 找出不符合条件的记录
Getting Constraint Inform
dba_constraints
dba_cons_columns
Charpter 14 Managing Password Security & Resources
Profiles:
- A profile is a named set of password and resource limits.
- Profiles are assigned to users by the create user or alter user command.
- Profiles can be enable or disable.
- Profiles can relate to the default profile.
Enable口令管理必须运行：
SQL> @?/rdbms/admin/utlpwdmg --------------->口令管理的准备工作
第五十九课
Database Schema
- A schema is a named collection of objects.
- A user is created,and a corresoponding schema is created.
- A user can be associated with only one schema.
- Username and shema are often used interchangeably.
Schema Objects:
Tables
Triggers
Constraints
Indexes
Views
Sequences
Stored program units
Synonyms
User-defined data types
Database links
默认表空间只能有一个，而临时表空间可以有多个，并且可以同时使用。
SQL> col PROPERTY_NAME format a20
SQL> col PROPERTY_VALUE format a20
SQL> col DESCRIPTION format a20
SQL> select * from database_properties; -----------------------> 查询系统默认临时表空间。
第六十课
使用系统用户登陆（不要密码）用于运行脚本程序
1. [root@myLinux ~]# useradd bbker ----------------> 增加系统用户
[root@myLinux ~]# passwd bbker
2. SQL> show parameter auth
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE
SQL> create user ops$bbker identified externally default tablespace ASSM; --------------------> 给系统用户赋予权限
SQL> grant create session to ops$bbker;
3.用该用户登陆，设置环境变量
[oracle@myLinux ~]$ echo $ORACLE_SID -----------------> 查看当前oracle_sid
[bbker@myLinux ~]$ export ORACLE_SID=bbk2
[bbker@myLinux ~]$ export ORACLE_HOME=/u01/oracle
[bbker@myLinux ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[bbker@myLinux ~]$ sqlplus /
Getting User Information
DBA_USERS
DBA_TS_QUOTAS
第六十一课 Chapter 16 Managing Privileges
There are two types of Oracle user Privileges:
- System: Enables users to perform particular actions in the database
More than 100 distinct system privileges
Any keyword in privileges signifies that users have the privilege in any schema.
Grant command adds a privilege to a user or a group of users.
Revoke command deletes the privileges.
- Object: Enables users to access and manipulate a specific object
The public
PUBLIC is a user group defined in the database ;it is not a database user or a role.Every user in the database belongs to this group.Therefore ,if you grant privileges to public,they are available to all users of the database.
SQL> create user u1 identified by bbk_12345 default tablespace mytbs quota unlimited on mytbs;---------------> 创建用户的同时指定表空间，并且不受限制的使用表空间。
SQL> grant create session to u1;--------------------> 给u1赋予登陆权限（不能创建表 只能连上来 查看）
SQL> select * from session_privs;------------------>查看当前用户权限
SQL> grant create table to u1;--------------------> 给u1赋予创建表的权限
SQL> grant select any table to u2 --------------> 给u2赋予查看其他人表的权利
SQL> select * from u1.tablename;----------->用u2登陆 查询u1的表 但是不能查看系统表（数据字典）
第六十二课
SQL> show parameter o7; --------------> 赋予select any table权限 能否访问数据字典的开关
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile; ---------------> 修改o7这个参数 注意：数据库需要重启
这个时候用户（u2）重新登陆，就可以查询数据字典了。
安全漏洞：
1. SQL> create user hacker identified by bbk_12345 default tablespace mytbs quota unlimited on mytbs; ------> 创建一个用户
2. SQL> grant create session to hacker;
3. SQL> grant create any procedure,execute any procedure to hacker;
SQL> select * from session_privs; ---------------> 查询当前用户的权限
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE ANY PROCEDURE
EXECUTE ANY PROCEDURE
4. SQL> create procedure system.h1(h1_str in varchar2) as
2 begin
3 execute immediate h1_str;
4 end;
5 /
5.SQL> execute system.h1('grant dba to hacker');----------------> hacker 用户被赋予了dba的权利
Some Points
To connect to the database,you need the create session privilege;
To truncate a table that belongs to another schema,you need the drop any table privilege;
The create any procedure(or execute any procedure) privilege allows the user to create,replace,or drop(or execute)procedures,packages,and functions;this includes java classes;
The create table privilege gives you the ability to create ,alter ,drop,and query tables in a schema.
Select any,insert any ,update any,and delete any are system privileges(in other words,they do not apply to a particular object)
SQL> show user; ---------------------------->把t表的查询权利赋予给u3
USER is "U1"
SQL> grant select on t to u3;
SQL> grant grant any object privileges to u2; --------------->把任何权限赋予u2
SQL> grant select on u1.m to u3; ---------------> 用u2账户把u1的表赋给u3
revoke with admin option 没有传递性
revoke with grant option 有传递性，一旦撤销，其他被被撤销的权限都没有了。
Some Points
Object privileges can be granted to a user , a role or public
If a view refers to tables or views from another user ,you must have the privilege with grant option on the underlying tables of the view to grant ant privilege on the view to another user.For example,john owns a view,which references a table from james,To grant the select privilege on the view to another user,john should have received the select privilege on the table with grant option.
Any object privilege received on a table provides the grantee the privilege to lock the table.(有访问表的权利就有锁定表的权利)
The select privilege cannot be specified on columns;to grant column -level select privileges ,create a view with the required columns and grant select on the view.（select 特权不能指定列 insert和update能指定列，变通办法是建立相应的视图，然后赋予权力）
You can specify all or all privileges to grant all available privileges on an object(for example,grant all on customer to james).
Even if you have DBA privilege ,to grant privileges on objects owned by another user you must have been granted the appropriate privilege on the object with grant option.
Multiple rivileges can be granted to multiple users and /or roles in one statement .For example,grant insert ,update,select on customer to admin_role,julie,scott
第六十三课
Getting Privileges Inform
dba_sys_privs
session_privs
dba_tab_privs（object）
dba_col_privs（object）
SQL> select * from user_sys_privs; ------------------------->直接赋给当前用户的特权，也就说查不出该用户的角色的特权
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DZ UNLIMITED TABLESPACE NO
Chapter 17 Managing Roles
角色里面可以包括：
system privilege
object privilege
role（角色）
角色(role)能够像操作系统权限（system privileges）一样赋予(grant)和收回(revoke)。
角色能够granted to 任何用户或者角色。但是，不能granted to 自己，也不能循环grant 到自己。
可以为使用角色赋予密码
角色必须在所有的用户和角色唯一
角色不被任何人拥有，也不在任何schema里面
角色可以通过数据库来控制
角色可以动态修改权限
SQL> create role r1;-----------------> 创建用户
SQL> create role r2 identified by bbk_12345;-----------------> 创建带密码的用户
SQL> select * from dba_roles ;------------>查看当前系统的所有角色
SQL> grant r1 to u1; --------------> 把角色r1赋予用户u1
赋予角色之后需要重新连接。
第六十四课
SQL> select * from role_role_privs;--------------->查询当前角色由哪些角色组成
SQL> select * from role_sys_privs;--------------->查询当前角色有哪些系统特权
SQL> select * from role_tab_privs;--------------->查询当前角色有哪些object privs
SQL> alter user u1 default role none; ----------------> 去除u1的默认角色
SQL> alter user u1 default role r1; -----------------> 修改u1的默认角色为r1
SQL> set role r1,r2 identified by bbk_12345; ------------->给r2角色的使用加上密码
SQL> set role r1;-------------------> enable a role
SQL> set role r2 identified by bbk_12345;------------------->enable 一个有密码的role
SQL> drop role r1;--------->删除角色r1
Getting Role Information
dba_roles 数据库里面的所有角色
dba_role_privs 被赋予用户和角色的角色
role_role_privs 被赋予角色的角色
dba_sys_privs 被赋予用户和角色的系统特权
role_sys_privs 被赋予角色的系统特权
role_tab_privs 被赋予角色的对象特权
session_roles 当前用户生效的角色
第六十五课 Chapter 18 Auditing 审计
审计的目的：对特定用户操作数据库的监控
Auditing Types
1. Standard auditing :are written to either of the following locations:
SYS.AUD$system table:dba_audit_trail dba_common_audit_trail
Operating system files.
2. Fine_grained auditing:
are written to the SYS.FGA_LOG$ system table.To view the contents of the table ,query the DBA_FGA_AUDIT_TRAIL data dictionary view or the DBA_COMMON_AUDIT_TRAIL view.
To control how ** are written,set the audit_trail parameter in the DBMS_FGA . ADD_POLICY procedure.the audit_trail parameter writes the records to either the FGA_LOG$ system table or to an XML operating system file.
3. Administrator auditing.
audit_sys_operations
audit_suslog_level
audit trail :审计结果记录
SQL> show parameter audit; ---------------------------------------------> 查看审计相关参数
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string ?/rdbms/audit
audit_sys_operations boolean FALSE
audit_trail string NONE
transaction_auditing boolean TRUE
[oracle@myLinux audit]$ ls -ltr -----------------> 按时间顺序列出当前文件夹下的目录
第六十六课
1.SQL> alter system set audit_trail=db scope=spfile;----------------> 打开审计功能，由于这是一个静态参数，所以必须在spfile范围改动，并且重启数据库
2.SQL> audit session by u1;-------------->开启对用户u1的审计
3.SQL> select os_username ,username,action_name from dba_audit_trail; ----------------> 查询相应的审计记录
Auditing Options
Statement:audit table
Privilege:audit create any trigger
Object:audit select on emi.orders
SQL> audit select on u1.t; -------------> 对用户u1创建的表t加上select审计
SQL> noaudit select on u1.t;-------------> 去除对用户u1创建的表t加上select审计
SQL> select username,action_name from dba_audit_trail; ----------------> 查询审计的内容
aud$是基表，dba_audit_trail等是基于这张基表的视图。
SQL> noaudit session by u1; -----------------> 去除对用户u1的审计
细粒度审计依赖于DBMS_FGA 这个包。可以做到根据内容的不同留下审计内容。
Getting Auditing Infomation
all_def_audit_opts
dba_stmt_audit_opts
dba_priv_audit_opts
dba_obj_audit_opts
Get Audit Records Inform (from aud$)
dba_audit_trail
dba_audit_exists
dba_audit_object
dba_audit_session
dba_audit_statement
第六十七课 Chapter 19 Loading Data into a Database
Direct Load
- During conventional INSERT:the database also maintains referential integrity constraints.(依然保持完整的引用)
- During direct-path INSERT:在以后数据后追加，写到高水位以上的位置。 性能高。
可以序列load 也可以并发(in parallel)load
可以插入分区表或者非分区表或者一个表的一个分区
可以不生产redo date
Direct Load
1. Serial Direct-Path INSERT into Partitioned or Non-partitioned Tables .The single process inserts data beyond the current high water mark of the table segment or of each partition segment .( The high-water mark is the level at which blocks have never been formatted to receive data).When a commit runs,the high - water mark is updated to the new value,marking the data visible to users.
2. Parallel Direct-Path INSERT into Partitioned Tables . This situation is analogous to serial direct-path INSERT.Each parallel execution server is assigned one or more partitions, with no more than one process working on a single partition.Each parallel execution server inserts data beyond the current high-water mark of its assigned partition segments.When a commit runs ,the high-water mark of each partition segment is update to its new value,making the data visible to users.
3. Parallel Direct-Path INSERT into Non-partitioned Tables . Each parallel execution server allocates a new temporary segment and inserts data into that temporary segment.When a commit runs,the parallel execution coordinator merges the new temporary segments into the primary table segment ,where it is visible to users.
SQL> insert /*+ append */ into m1 nologging select * from m;---------------> (Serial Direct-Path INSERT 串行插入)在高水位后插入数据，速度很快，但是空间利用率不高，更关键的是需要commit后才能查询数据，否则报错.注意这里写的nologging 并不能保证不记录日志
SQL> alter table u1.bt nologging;--------------------------->只有经过显示指定的表,或者表空间默认nologging 才会没有redo信息，即不记录日志
第六十八课
SQL> create table emp(empno int,ename varchar2(20)) -----------------> 创建分区表 partition table
2 partition by hash(empno)(partition part1,partition part2)
3 /
Direct Load Loggin Mode
Direct-path INSERT lets you choose whether to log redo and undo information during the insert operation.
- You can specify logging mode for a table,partition ,index,or LOB storage at create time(in a create statement)or subsequently(in an alter statement).
- If you not specify either LOGGING or NOLOGGING at these times:
- The logging attribute of a partition defaults to the logging attribute of its table.
- The logging attribute of a table or index defaults to the logging attribute of the tablespace in which it resides.
- The logging attribute of LOB storage defaults to LOGGING if you specify cache for LOB storage .If you do not specify cache,then the logging attributes defaults to that of the tablespace in which the LOB values resides.
- You set the logging attribute of a tablespace in a create tablespace or alter tablespace statements.
Note: If the database or tablespace is in force logging mode, then direct path insert always logs,regardless of the logging setting.
Direct-Path insert with Logging :
1.in archivelog mode, you can archive redo logs to tape
2.in noarchivelog mode ,then you can recover instance crashes but not disk failures.
Direct-Path insert without Logging :
make little logging to mark new extents invalid.
This mode improves performance.However ,if you subsequently must perform media recovery,the extent invalidation records mark a range of blocks as logically corrupt,because no redo data was logged for them.Therefore ,it is important that you back up the data after such an insert operation.
[oracle@myLinux bin]$ sqlldr
第六十九课 SQL*Loader
一 用控制文件导入数据：
1. [oracle@myLinux ~]$ vi demo1.ctl ------------> 写导入控制文件
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
2.SQL> create table dept(deptno number(2) constraint dept_pk primary key,dname varchar2(14),loc varchar2(13)) ------------>建表
2 /
3.[oracle@myLinux ~]$ sqlldr userid=u1/bbk_12345 control=demo1.ctl ------------>导入数据
二 数据文件导入
1.[oracle@myLinux ~]$ vi demo1.ctl ------------>编辑配置文件
LOAD DATA
INFILE demo.data
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,DNAME,LOC)
2.[oracle@myLinux ~]$ vi demo.data ------------> 建立数据文件
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
3.[oracle@myLinux ~]$ sqlldr userid=u1/bbk_12345 control=demo1.ctl log=bbk.log; --------> 导入数据
汤大师著作 694 页 有详细描述
SQL*Loader Case Studies
SQL*Loader features are illustrated in a variety of case studies.The case studies are based upon the Oracle demonstration database tables,emp and dept,owned by the user scott.(In some case studies ,additional columns have been added)
The case studies are numbered 1 through 11,starting with the simplest scenario and progressing in complexity.
The following is a summary of the case studies:
- Case Study 1:Loading Variable-Length Data -Loads stream format records in which the fields are terminated by commas and maybe enclosed
- Case Study 2:Loading Fixed-Format Fields - Loads data from a separate datafile.
- Case Study 3:Loading a Delimited , Free - Format File - Loads data from stream format records with delimited fields and sequence numbers.The data is found at the end of the control file.
- Case Study 4:Loading Combined Physical Records- Combines multiple physical records into one logical record corresponding to one database row.
.
.
.
.
.
.
[oracle@myLinux demo]$ pwd
/u01/oracle/rdbms/demo -------------------------------------------------------> demo 都在这个目录下
[oracle@myLinux demo]$ ls ulc*
ulcase10.ctl ulcase11.sql ulcase2.dat ulcase4.dat ulcase5.sql ulcase7.ctl ulcase8.ctl ulcase92.dat ulcase96.dat
ulcase10.sql ulcase1.ctl ulcase3.ctl ulcase4.sql ulcase6.ctl ulcase7.dat ulcase8.dat ulcase93.dat ulcase9.ctl
ulcase11.ctl ulcase1.sql ulcase3.sql ulcase5.ctl ulcase6.dat ulcase7e.sql ulcase8.sql ulcase94.dat ulcase9.sql
ulcase11.dat ulcase2.ctl ulcase4.ctl ulcase5.dat ulcase6.sql ulcase7s.sql ulcase91.dat ulcase95.dat ulcase.sh
控制文件的注释是 --
constant zone是关键字，控制文件内容里面不要包含。
Data file can be in one of three formats:
Fixed-record format
Variable-record format
Stream-record format
第七十课
Logical Records
SQL*Loader can be instructed to follow one of the following two logical record-forming strategies:
- Combine a fixed number of physical records to form each logical record
- Combine physical records into logical records while a certain condition is true.
Direct VS Conventional Loads:
Conventional Load
Uses commit to make changes permanent
Redo entries always generated
Enforces all constraints
insert triggers fire
can load into clistered tables
Other users can make changes to tables
Direct Path Load
uses data saves
Generates redo only under specific conditions
Enforces only primary key,unique,and not null
insert triggers do not fire
Cannot load into clustered tables
other users cannot make changes to tables
Data Conversion
During a conventional path load,data fields in the data file are converted into columns in the database in two steps:
- The field specifications in the control file are used to interpret the format of the data file and convert it to a SQL insert statement using that data
- The Oracle database server accepts the data and executes the insert statement to store the data in the database.
Discarded or Rejected Rec
Bad file:
- SQL*Loader rejects records when the input format is invalid.
- If the Oracle database finds that the row is invalid,then the record is rejected and SQL*Loader puts it in the bad file.
Discard file:
- This can be used only if it has been enabled.
- This file contains records that were filtered out because they did not match any record-selection criteria specified in the control file.
SQL*Loader Exit Code
Result Exit Code
------------------------------------------------------------------------------------------
All rows loaded successfully EX_SUCC
All or some rows rejected EX_WARN
All or some rows discarded EX_WARN
Discontinued load EX_WARN
Command-line or syntax errors EX_FAIL
Oracle errors nonrecoverable for SQL*Loader EX_FAIL
Operating system errors(such as file open/close and malloc) EX_FAIL
SQL*Loader Guidelines
Use a parameter file to specify commonly used command line options.
Place data within the control file only for a small,one-time load.
Improve performance by:
- Allocating sufficient space
- （X）Sorting the data on the largest index（不要）
- Specifying different files for temporary segments for parallel loads
第七十一课 Chapter 20 Using Globalization Support
有一本PDF专门介绍 books - GLO - Globalization Support Guide
第七十二课
Character Sets：
Defined at creation time
Cannot be change without recreation
Store data columns of type char,varchar2,clob,long
Can store varying-width character sets
National Character Sets
Defined at creation time
Cannot be changed without recreation ,few exceptions
Store data columns of type nchar,nvarchar2 ,nclob
Can store Unicode using either al16utf16 or utf8
SQL> col PARAMETER format a30
SQL> col VALUE format a30
SQL> select * from nls_database_parameters; -----------------------> 查询当前数据库的字符集情况
Length Semantics
In single-byte character sets,the number of bytes and the number of characters in a string are the same.In multibyte character sets,a character or code point(码点) consists of one of more bytes.Calculating the number of characters based on byte lengths can be difficult in a variable-width character set.Calculating column lengths in bytes is called byte semantics,while measuring column lengths in characters is called character semantics.
Character semantics is useful for defining the storage requirements for multibyte string of varying widths.For example ,in a Unicode database(al21utf8),suppose that you need to define a varchar2 column that can store up to five chinese characters together with five English characters .Using byte semantics,this column requires 15 bytes for the chinese characters,which are three bytes long,and 5 bytes for the English characters ,which are one byte long,for a total of 20 bytes.Using character semantics,the column requires 10 characters.
SQL> show parameter semantic -------------------------------->按哪种方式计算长度 byte semantics or character semantics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE
SQL> create table mm(name varchar2(10));------------>如果创建数据库的时候不显示指定字符计算方式，则以参数 semantic 为准
substrb(string,1,20) / substr(string,1,20) ------------->加b的函数说明按照byte方式取子串
SQL> create table tm (name varchar2(10 byte));------------>显示指定字符计算方式 byte semantics
SQL> create table vm(name varchar2(10 character));------------>显示指定字符计算方式character semantics
第七十三课
SQL> select dump(ename,1016) from emp; ---------------------------查询某一列的编码情况
DUMP(ENAME,1016)
--------------------------------------------------------------------------------
Typ=1 Len=19 CharacterSet=UTF8: ef,bf,bd,ef,bf,bd,ef,bf,bd,ef,bf,bd,6c,69,61,6e,
67,79,69
sqlplus的字符集和操作系统的字符集一样时才不会发生乱码。
UTF8 ： 可变长度 速度慢 但是记录的以英文为主的话，省空间
AL16UTF16 : 固定长度 速度快 汉字记录为主的话 省空间
第七十四课
UTF-16 是UCS-2 的增强版
Unicode Database /DataType
To choose the correct Unicode solution for your database,consider the following questions:
- Programming environment:What ate the main programming languages used in your applications?How do they support Unicode
?
- Ease of migration:How easily can your data and applications be migrated to table advantage of the Unicode solution?
- Types of data:Is your data mostly Asian or European？Do you need to store multilingual documents into LOB columns?
- Types of applications:What type of applications are you implementing:a packaged application or a customized end-user application?
Unicode Database
- Easy code migration for java or PL/SQL
- Easy data migration from ASCII based data
- Evenly distributed multilingual data
- InterMedia Text Search
文档b28298.pdf P142
Unicode Data Type
- While adding multilingual support incrementally
- Packaged applications（打包销售的应用程序）
- Performance:Single byte database character set with a fixed-width national character set
- Better support for UTF-16 with Windows clients
主字符集不能选择UTF-16,与内部字段冲突。
oracle的文本搜索引擎需要使用utf-8 字符集。
建议主字符集Al32utf8 次字符集al16utf16
服务端的字符集应该是客户端字符集的超集
*************让SecureCRT中的sqlplus支持上下左右键*************
[root@oracle9i ~]# tar zxvf FileName.tar.gz
[root@oracle9i rlwrap-0.30]# ./configure
[root@oracle9i rlwrap-0.30]# make
[root@oracle9i rlwrap-0.30]# make install
切到oracle用户，编辑bash文件，加两行~
[root@oracle9i ~]# su - oracle
[oracle@oracle9i ~]$ vi .bash_profile
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"
注意要重启，重连
************************************
改变字符集：
SQL> alter session set nls_language=
字符集设置优先顺序
Lang-Dependent Behavior
1(highest) Explicitly set in SQL functions
2 Set by an alter session statement
3 Set as an environment variable
4 Specified in the initialization parameter file
5 Default
SQL> col parameter format a30
SQL> col value format a30
SQL> select * from nls_session_parameters; -------------------> 查看当前字符集
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
只需要调这两个参数就可以控制整个字符集了
也可以设置一个环境变量：
NLS_LANG=French_France.utf8
NLS_LANGUAGE=SIMPLIFIED CHINESE
NLS_TERRITORY=CHINA
第七十六课
[oracle@neo ~]$ echo $XXX ----------------> 查看环境变量
[oracle@neo ~]$ export NLS_LANG=American_Amierica.ZHS16GBK -----------------> 修改环境变量
[oracle@neo ~]$ export NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
alter session set nls_date_format='DD.MM.YYYY'; --------------------------> 设置时间显示格式
DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''DD.MM.YYYY''');
select to_char(hire_date,'DD.Mon.YYYY','NLS_DATE_LANGUAGE=FRENCH') from employees;
select enmae,to_char(sal,'9G999D99','NLS_NUMERIC_CHARACTERS=',.''') from emp;
SQL> select * from h order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M'); -------------按拼音排序
SQL> select * from h order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M); ---------------按笔画排序
SQL> select * from v$nls_valid_values where parameter ='SORT'; --------------查询有多少种排序方式
create index list_word on list (NLSSORT(word,'NLS SORT=French_M')); -------------为列创建排序索引
工具：Character set scanner
Oracle locale builder
Getting NLS Settings Inform
NLS_INSTANCE_PARAMETERS
NLS_SESSION_PARAMETERS
9066
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
