[[email protected] ~]# env | grep LANG ------------>檢視本地字元編碼 ???如何修改字元集
LANG=en_US.UTF-8
[[email protected] ~]# top -------------> 檢視系統程序、登陸使用者等資訊
[[email protected] ~]#
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
[[email protected] ~]# 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;
[[email protected] 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.
第十四課
修改環境變量
[[email protected] dbs]$ env | grep ORA
ORACLE_SID=denver ///修改前
ORACLE_BASE=/u01
ORACLE_HOME=/u01/oracle
[[email protected] dbs]$ export ORACLE_SID=chris
[[email protected] 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。
清除一個資料庫：三個地方删除
[[email protected] u01]$ ls
admin Disk3 doc jre oracle oradata oraInventory oui
[[email protected] u01]$ cd admin
[[email protected] admin]$ ls
denver
[[email protected] admin]$ rm -fR denver/ <----------------
[[email protected] admin]$ ls
[[email protected] admin]$ cd /i01/oradate/
-bash: cd: /i01/oradate/: No such file or directory
[[email protected] admin]$ cd u01/oradata/
-bash: cd: u01/oradata/: No such file or directory
[[email protected] admin]$ cd /u01/oradate/;
-bash: cd: /u01/oradate/: No such file or directory
[[email protected] admin]$ cd /u01;
[[email protected] u01]$ ls
admin Disk3 doc jre oracle oradata oraInventory oui
[[email protected] u01]$ cd oradata
[[email protected] oradata]$ ls
denver
[[email protected] oradata]$ rm -fR denver/ <----------------
[[email protected] oradata]$ cd dbs
-bash: cd: dbs: No such file or directory
[[email protected] oradata]$ cd
[[email protected] ~]$ cd dbs
[[email protected] dbs]$ ls
initdenver.ora initdw.ora init.ora lkDENVER lkORACLE orapwdenver spfiledenver.ora
[[email protected] dbs]$ rm -f initdenver.ora orapwdenver lkDENVER <----------------
[[email protected] dbs]$ ls
initdw.ora init.ora lkORACLE spfiledenver.ora
[[email protected] 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
---------------------------------------------------------------------------------------------
[[email protected] 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
[[email protected] dbs]$ chmod +x bbk.env <---------------------------------給bbk.env權重限
[[email protected] dbs]$ . ./bbk.env <---------------------------------執行
第十六課
cp -R denver/ bbk ----> 拷貝目錄
%s/aaa/bbb/g ----> 替換aaa 為bbb
[[email protected] dbs]$ orapwd file=orapwbbk password=oracle entries=10 -----> 建立pwd檔案
[[email protected] ~]$ tail -f /u01/admin/bbk/bdump/alert_bbk.log ------>檢視log檔案
[[email protected] 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 [[email protected] dbs]$ cd /u01/oradata/bbk2
[[email protected] 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。
[[email protected] 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.移動資料檔案到需要的地方([[email protected] 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
第三十二課
[[email protected] 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. [[email protected] ~]# useradd bbker ----------------> 增加系統使用者
[[email protected] ~]# 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.用該使用者登陸，設定環境變量
[[email protected] ~]$ echo $ORACLE_SID -----------------> 檢視目前oracle_sid
[[email protected] ~]$ export ORACLE_SID=bbk2
[[email protected] ~]$ export ORACLE_HOME=/u01/oracle
[[email protected] ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[[email protected] ~]$ 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
[[email protected] 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 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.
[[email protected] bin]$ sqlldr
第六十九課 SQL*Loader
一 用控制檔案導入資料：
1. [[email protected] ~]$ 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.[[email protected] ~]$ sqlldr userid=u1/bbk_12345 control=demo1.ctl ------------>導入資料
二 資料檔案導入
1.[[email protected] ~]$ vi demo1.ctl ------------>編輯配置檔案
LOAD DATA
INFILE demo.data
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,DNAME,LOC)
2.[[email protected] ~]$ vi demo.data ------------> 建立資料檔案
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
3.[[email protected] ~]$ 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.
.
.
.
.
.
.
[[email protected] demo]$ pwd
/u01/oracle/rdbms/demo -------------------------------------------------------> demo 都在這個目錄下
[[email protected] 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支援上下左右鍵*************
[[email protected] ~]# tar zxvf FileName.tar.gz
[[email protected] rlwrap-0.30]# ./configure
[[email protected] rlwrap-0.30]# make
[[email protected] rlwrap-0.30]# make install
切到oracle使用者，編輯bash檔案，加兩行~
[[email protected] ~]# su - oracle
[[email protected] ~]$ 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
第七十六課
[[email protected] ~]$ echo $XXX ----------------> 檢視環境變量
[[email protected] ~]$ export NLS_LANG=American_Amierica.ZHS16GBK -----------------> 修改環境變量
[[email protected] ~]$ 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
![CentOS 6.5 部署 Apache 伺服器(httpd)[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)