ORACLE傻瓜手冊
To be DBA or not to be that is NOT the question Arron
作者允許自由散發此文檔但對其進行的任何修改應通知作者以便於維護版本
Oracle是一頭恐龍而且隨著版本號的不斷上升體系越來越龐大功能越來越復
雜以至於變成了一座偶像使許多IT技術人員心存畏懼然而偶像終究是要打碎的
技術的生命力只有在人民心中才能延續成長筆者從事過一段時間的Oracle數據庫管理
和開發深感Oracle文檔雖全但過於瑣碎蕪雜實用的精華淹沒於細節羅列的大海之
中不能迅速有效地投入應用而且缺少實踐經驗的總結高手牛人疏於為文知識由
於沒有共享流傳而不能起到倍增的效果令我等後輩不得不重起爐灶慘淡經營期間
的經驗教訓雖可寶貴卻實出無奈筆者雖愚願盡力而為集思廣益累積成文希
望能借助大家的合力將有關Oracle的知識化繁為簡化神秘為普及從而提高軟件應
用項目的水平
作者周雲飛 email
Oracle以為界分為普通版本和internet版本普通版版本號x接觸較多的是internet版版本號包括（Release ）（Release ）（Release ）普通版簡稱Oracle internet版簡稱Oracle i如果不作特別說明文中凡出現Oracle i均指版
Oraclei目前出到第二版版本號為簡稱Oracle i如果不作特別說明文中凡出現Oracle i均指版
本手冊介紹Oracle配置的基本方法描述的是所然而不是所以然全部操作以命令行方式出現不涉及GUI（只有白刃戰才是真正的戰斗）鑒於大家對Windows已經十分熟悉同時為了避免Windows和Unix兩種截然不同的使用和開發風格給描述帶來的復雜性所以本手冊不介紹在Windows上的Oracle（上帝的歸上帝恺撒的歸恺撒）
文中所有例子以oradb作為數據庫實例名數據庫用戶dbuser口令oracle如果不作特別說明關於Oracle i所有的例子都在Solaris Intel Platform+Oracle iR上通過關於Oracle i所有的例子都在RedHat Linux +Oracle iR上通過附錄文件sampletar包含全部示例簡稱附錄
大量使用表emp作為例子（參見附錄_proc/proc/single/empsql）
create table emp
(
no number() not null
name char() not null
age number() not null
duty char() not null
salary number() not null
upd_ts date not null
primary key (no)
);
開發中對應emp表結構定義其宿主結構（參見附錄_proc/proc/single/dbh）
typedef struct
{
double no;
char name[];
int age;
char duty[];
double salary;
char upd_ts[];
} emp_t;
修改歷史
/ 版本
/ 版本
增加Linux安裝exportimport使用數據庫監控及優化（utlbstatutlestat分析session）語言時間環境變量設置Oracle手工建庫腳本（wei_dick提供稍加修改）
/ 版本
修改Linux安裝中RedHat x+Oracle 數據庫優化中配置文件和session分析常用技巧中下載上傳文本數據和訪問他機數據庫增加創建數據庫實例中數據字典參考常用技巧中刪除冗余記錄應用開發常見錯誤
感謝liu_freemanjiao_julianhuang_miles等人對開發工具所作的努力
/ 版本
修改安裝部分initora配置常用技巧應用開發增加手工建庫MTS配置重寫開發工具
感謝li_bo的大力幫助
/ 版本
修改數據庫優化使之較系統化增加應用開發中多線程下的數據庫連接
/ 版本
修改數據庫優化多線程條件下數據庫編程分離附錄的程序范例
/ 版本
重新安排內容增加Oracle i安裝配置OCI開發mysql安裝配置開發補充數據庫優化PROC開發
ORACLE傻瓜手冊
安裝
通用設置
UnixWare
Oracle
HPUX
Oracle
Linux
kernel & glibc
kernel & glibc
kernel & glibc
Solaris
創建
Oracle & i
工具創建
手工創建
MTS（multithreaded server）
調整臨時表空間
調整回滾表空間
調整日志
調整用戶表空間
創建用戶
創建數據對象
創建只讀用戶
啟動及關閉數據庫實例
網絡配置
Oracle i
手工創建
創建用戶表空間
初始化文件配置
Oracle & i
Oracle i
工具
sqlldr
exp
imp
sqlplus
命令行參數
提示符命令
SET選項
例子
備份及恢復
export與import方式
冷備份
聯機全備份+日志備份
設置
步驟
恢復
注意要點
數據庫優化
通用設置
硬件配置
應用配置
日常性能監控
實戰分析
總體分析
詳細分析
專題分析
巨表查詢
對比測試
上下載數據
回滾空間快照陳舊（snapshot too old）
常用技巧
增加更改和刪除域
刪除冗余記錄
更改字符集
表數據遷移
成批生成數據
注意要點
嵌入式SQL（C）
編譯
SQL語句
內部類型與宿主類型對應
連接和斷開
事務
標准SQL語句
動態SQL語句
數組操作
編程框架
總體原則
單線程和多線程
開發工具
OCIOracle Call Interface
連接和斷開
句柄層次
連接流程
斷開流程
SQL語句
事務
無結果集的sql語句
有結果集的sql語句
LOB
編程框架
總體原則
sql語句
函數
附錄MYSQL
安裝配置
管理
初始調整
建立用戶對象
開發
連接和斷開
無結果集的sql語句
有結果集的sql
錯誤處理
安裝
所有參見內容都在附件_install__create__init/下
通用設置
文件系統swap
創建文件系統時應考慮Oracle對swap的需要大約每個oracle服務進程將占用Mswap空間通常操作系統建議倍於內存的swap空間數據庫系統可能要求更多些
操作系統用戶和環境變量
Oracle文檔要求為數據庫系統的管理和使用建立個或更多的組但這個需求是可以忽略的實踐中並沒有體現其必要性為簡化操作起見只建立dba組即擁有更新軟件和管理最高權限（SYSDBA）的操作系統用戶組此組稱為OSDBA屬於此組的用戶可以SYSDBA身份登錄進任何一個數據庫實例簡單的只建立一個用戶習慣上使用oracle的名稱
$ groupadd dba
$ useradd g dba d /home/oracle m s /bin/bash oracle
確定oracle 系統的根目錄ORACLE_BASE如/opt/oracle所有的軟件和配置都在這個目錄下展開雖然並非一定需要如此但這是一個良好的習慣同時確定軟件安裝的起始點ORACLE_HOME通常在ORACLE_BASE下
修改oracle用戶的profile加入以下各行或者修改/etc/profile使每一個用戶都獲得環境變量設置
umask
ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/product/{版本號}（如等）
ORACLE_SID=oradb
ORACLE_TERM=ansi ＃僅與Oracle字符界面安裝有關
ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data ＃字符集支持
NLS_LANG=American_America{ZHSCGB(Oracle支持)|ZHSGBK(Oraclei支持)|ZHSGB(Oraclei支持)}
NLS_DATE_FORMAT=YYYYMMDDHHMISS
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH ＃動態連接路徑Unixware中要確保/usr/ucb/lib在/usr/ccs/lib之後出現
TMPDIR=/tmp ＃安裝中Oracle會在此目錄下存儲相當數量的文件所以TMPDIR所在的磁盤分區要確保空閒空間的大小至少在G左右
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_TERM ORA_NLS NLS_LANG NLS_DATE_FORMAT LD_LIBRARY_PATH TMPDIR
參見profile
注意
NLS_LANG=American_AmericaZHSCGB(ZHSGBK)
American指顯示信息時所用的語言竊以為憑大家的英語水平足夠應付如改為SIMPLIFIED CHINESE在不帶中文支持的終端上就沒人能看懂了
America指地區
ZHSCGB指Client工具使用的字符集一般使用ZHSCGBOraclei已支持到ZHSGBK
NLS_DATE_FORMAT=YYYYMMDDHHMISS
Oracle的date類型過於靈活為統一時間格式利於編程應將時間的輸入輸出格式限定為位字符串如
據oracle文檔此參數可按照session操作系統用戶環境initora由高到低的優先級順序設置依次覆蓋
相關系統表
v$nls_parameters v$nls_valid_values
XWindow
Oracle 的安裝程序是光盤mount點/bin/orainst使用字符界面不用考慮XWindow
Oracle i和i使用光盤mount點/runInstaller進行安裝它是用Java編寫的圖形界面對中文處理有問題所以應在進入XWindow前確保語言（LANG）和地域（LC_ALLLC_TYPE…）環境變量不是中文
LANG=C
LC_ALL=C
安裝選項
Oracle i的主要軟件包在安裝選項Enterprise中但並不包括proc必須進行第二次安裝可選擇安裝選項Client中的programmer
Oracle i的主要軟件包在安裝選項Enterprise安裝選項中但並不包括proc必須進行第二次安裝一定要選擇安裝選項Client中的AdministratorrunInstaller的穩定性欠佳建議每次安裝結束後先退出再進行下一次安裝
UnixWare
Oracle
確認操作系統的交換分區swap不少於M
認為該打的補丁統統打上寧濫毋缺UnixWare必須打的補丁為ptfptfptfptfptf
將/etc/default/login中的ulimit設為大於（稍大一點即可太大會有問題）
將/etc/conf/noded/async中的改為
修改以下核心參數
核心參數 必需值 解釋
SHMMAX 共享內存段最大尺寸
SHMMNI 系統共享內存段標識最大數目
SHMSEG 每個進程所能使用最大共享內存段數目
SEMMNI 核心信號量標識最大數目
SEMMSL 每個信號量標識包含的信號量個數
SCORLIM XFFFFFFF Core文件最大尺寸
HCORLIM XFFFFFFF
SDATLIM XFFFFFFF 進程堆最大尺寸
HDATLIM XFFFFFFF
SVMMLIM XFFFFFFF 進程最大映射地址
HVMMLIM XFFFFFFF
SFSZLIM XFFFFFFF 進程文件最大偏移量
HFSZLIM XFFFFFFF
SFNOLIM 進程能打開的最大文件個數
HFNOLIM
NPROC +(*MAXUSERS) MAX:
ARG_MAX
NPBUF I/O緩沖區數目
MAXUP 用戶同時使用的最大進程個數
STRTHRESH X 流能使用的最大字節數
為優化應用系統修改以下核心參數
核心參數 參考值 解釋
MSGMAX 消息最大尺寸
MSGMNB 消息隊列尺寸
MSGMNI 系統能並存的最大消息隊列數目
MSGSSZ
MSGTQL 系統能並用的消息頭數目
SEMMNI
SEMMSL
也可通過編輯/etc/conf/cfd/stune達到同樣效果
重新連接內核重起或運行/etc/conf/bin/idbuild B
修改核心參數SEMMAP時注意要同時修改/etc/conf/mtuned/ipc中相應的MAX值
建立/var/opt/oracle使oracle成為此目錄屬主
mount oracle光盤通常mount目錄為/SDCDROM_
root用戶ORACLE_OWNER=oracle執行光盤上orainst中oratabsh建立/var/opt/oracle/oratab
安裝時選custom方式安裝時不建立數據庫字符集可選Simplified Chinese
HPUX
Oracle
流程大致與unixware相同調整kernel參數可通過sam選擇/Kernel Configuration/Actions/Apply Tuned Parameter Set/OLTP Database Server System另外為提高I/O能力還需調整以下參數
核心參數 參考值 解釋
bufpages 緩沖頁
dbc_max_pct 動態緩存占內存最大百分比
dbc_min_pct 動態緩存占內存最小百分比
nbuf
設定共享庫目錄SHLIB_PATH不是LD_LIBRARY_PATH
SHLIB_PATH=$SHLIB_PATH:$ORACLE_HOME/lib;export SHLIB_PATH
Linux
kernel & glibc
代表產品為Red Hat Linux
Oracle 在RedHat上能成功安裝安裝軟件包為shiptgz
一般不會在RedHat上安裝Oraclei以上的版本
修改共享內存最大尺寸限制
在系統初始化腳本/etc/rcd/rcsysinit中加入
echo >/proc/sys/kernel/shmmax
重啟計算機這樣做避免了Oracle分配的共享內存碎片化對提高效率有好處
原$ORACLE_HOME/precomp/admin/pcscfgcfg中sys_include有誤使proc預處理pc程序失敗安裝結束後應設為sys_include=(/usr/include/usr/lib/gcc lib/iredhatlinux/egcs/include)（視gcc版本而定）
kernel & glibc
代表產品為Red Hat Linux
修改共享內存最大尺寸限制
在系統初始化腳本/etc/rcd/rcsysinit中加入
echo >/proc/sys/kernel/shmmax
重啟計算機這樣做避免了Oracle分配的共享內存碎片化對提高效率有好處
原$ORACLE_HOME/precomp/admin/pcscfgcfg中sys_include有誤使proc預處理pc程序失敗安裝結束後應設為sys_include=(/usr/include/usr/lib/gcc lib/iredhatlinux/egcs/include) （視gcc版本而定）
Oracle
本來已經很少有人在LinuxKernel的系統中安裝Oracle但筆者實在懷念純粹的文本界面和與之相處的無數不眠之夜故收錄如下
Oracle在kernel為x的linux中是無法正常運行的運行可執行文件如svrmgrlsqlplus時會導致Segmentation fault原因在於這些linux使用了默認的libc與Oracle程序重連接所需的libc不兼容Oracle的補丁程序其實是將Oracle可執行程序的重連接腳本中libc位置重新定位到libc上去並用舊版的gccld重新連接可執行文件為此必須先在系統中安裝兼容庫和相應工具這是權宜之計而且僅對RedHat有效
root用戶
rpm ivh tclirpm Oracle的Intelligent Agent要使用
rpm ivh compatbinutilsirpm
rpm ivh compatglibcirpm
rpm ivh compategcsairpm
rpm ivh compategcsc++airpm
rpm ivh compatlibsirpm
版本號可略有差異
oracle用戶安裝Oracle但不創建instance如選擇安裝文檔則會產生如下錯誤
A write error occurred while try to copy /home/oracle/setup_oracle/unixdoc/server/install/lnx_servermap to /oracle/product//doc/server/install/lnx_server(No such file or directory)
這是安裝程序的一個bug不能創建目錄可進入$ORACLE_HOME/docmkdir p server/install再選擇Retry
從/pub/www/otn/linux下載glibcpatchtgz在某一目錄（如~/patch）下展開
cd ~/patch
glibcpatchsh
經過一段時間後看到Applied glibc patch for Oracle x successfully表明補丁成功此時就能成功創建instance
Oracle i
推薦使用典型安裝否則會產生難以預料的錯誤
kernel & glibc
代表產品為Red Hat Linux SuSE Linux
Oracle i
與Oracle在RedHat Linux 上安裝所遇到的問題一樣Oracle i使用的glibc 與操作系統自帶的glibc 不能兼容解決的方法也一樣要安裝glibc 的兼容庫並重新連接Oracle各組件
除非萬不得已不建議使用兼容方式因此省略安裝步驟可參閱網上有關文檔
Oracle i
RedHat
修改共享內存最大尺寸限制
在系統初始化腳本/etc/rcd/rcsysinit中加入
echo >/proc/sys/kernel/shmmax
修改信號量參數
在系統初始化腳本/etc/rcd/rcsysinit中加入
echo >/proc/sys/kernel/sem
這個參數依次為SEMMSL(每個用戶擁有信號量最大數量)SEMMNS(系統信號量最大數量)SEMOPM(每次semop系統調用操作數)SEMMNI(系統信號量集最大數量)事實上只有SEMOP是需要調整的
重啟計算機
在連接可執行文件過程中會發生中斷打開$ORACLE_HOME/ctx/lib/env_ctxmk找到INSO_LINK在L$(CTXLIB) L$(LDLIBFLAG)m後加入L$(LDLIBFLAG)dl重試
SuSE
與RedHat類似但SuSE沒有/etc/rcd/rcsysinit筆者選擇/etc/rcd/rc將核心參數修改添加到最後exit語句之前
安裝過程中沒有發生任何問題
Solaris
Oracle i在Solaris Intel Platform上均能順利安裝未測試Solaris Sparc Platform
Oracle i目前無Solaris Intel Platform上的版本由於條件所限未測試在Solaris Sparc Platform上的Oracle i
修改下列核心參數
核心參數 參考值 解釋
shmmax 物理內存/ 共享內存段最大尺寸
shmmin 共享內存段最小尺寸
shmmni 系統共享內存段標識最大數目
shmseg 每個進程所能使用最大共享內存段數目
semmni 系統信號量標識最大數目
semmsl initoraprocesses+ 每個信號量標識包含的信號量數目
semmns sum(initoraprocesses)*+max(initoraprocesses)+count(initora)* 系統信號量最大數目
semopm 每個semop調用最大操作數目
rlim_fd_max 系統文件句柄最大數目
rlim_fd_cur 每個進程文件句柄最大數目
修改/etc/system並重啟使核心參數生效
例
set shmsys:shminfo_shmmax=
set shmsys:shminfo_shmmin=
set shmsys:shminfo_shmmni=
set shmsys:shminfo_shmseg=
set semsys:seminfo_semmni=
set semsys:seminfo_semmsl=
set semsys:seminfo_semmns=
set semsys:seminfo_semopm=
set semsys:seminfo_semmap=
set semsys:seminfo_semmnu=
set semsys:seminfo_semvmx=
set msgsys:msginfo_msgmni=
set msgsys:msginfo_msgmap=
set msgsys:msginfo_msgmax=
set msgsys:msginfo_msgmnb=
set msgsys:msginfo_msgssz=
set msgsys:msginfo_msgtql=
set msgsys:msginfo_msgseg=
set rlim_fd_max=
set rlim_fd_cur=
參見solaris__/system
注意
一定要先重建好kernel後再安裝因為oracle安裝時根據kernel動態連接程序如果先安裝oracle即使隨後正確調整kernel也會帶來許多問題如oracle進程不能拉起instance創建失敗等
在kernel參數中對數據庫運行影響最大的主要是SHMMAXSEMMNSSEMMNISEMMSLSHMMAX取內存一半即可SEMMNS理論上應等於SEMMNI*SEMMSL實際取一個較大值即可
SEMMNS: 信號量最大個數有些系統可忽略因為他與SEMMNISEMMSL有關
創建
所有參見內容都在附件_install__create__init/下
以oracle用戶進行操作設定數據庫實例名為oradb（長度建議不要超過個字符）
Oracle & i
工具創建
Oracle
運行$ORACLE_HOME/bin/orainst（安裝數據庫時必須選中oracle installer）選擇create database object安裝界面中選Oracle Enterprise Server(RDBMS)
mount point暫為$ORACLE_BASE字符集為ZHSCGB或ZHSGBK調整systemtoolsusersrbstempredolog等尺寸
創建過程中會提示輸入osdbaosoper的UNIX組這是向instance表明此組的成員享有角色sysdba或sysoper的權限從而用connect / as sysdba替換掉connect internal
Oracle i
進入X WINDOW運行dbassist
手工創建
任何工具都有其局限性熟練的數據庫管理員可采用手工方法創建數據庫以增加對系統的靈活控制
對於手工建庫Oracle 與Oracle i的區別主要是建立的數據字典和存儲過程有些不同Oraclei的dbassistant可以生成建庫腳本供以後使用
取得/i/initoradbora編輯如db_namecontrol_filedump_dest等參數以符合實際情況如不需要生成remote_login_passwordfile可在initoradbora中設remote_login_passwordfile=none如需要在initoradbora中設remote_login_passwordfile=exclusive運行orapwd file= password= 必須創建新生成文件所要用到的目錄如在配置文件中指定的bdumpcdumpudump等目錄以及數據文件存儲目錄
將initoradbora轉移到$ORACLE_BASE/admin/oradb/pfile/並連接到$ORACLE_HOME/dbs/initoradbora
ln s $ORACLE_BASE/admin/oradb/pfile/initoradbora $ORACLE_HOME/dbs/initoradbora
取得i/createdbsh編輯如pfile數據文件目錄等參數以符合實際情況並轉移到$ORACLE_BASE/admin/oradb/create/下執行
相關系統表
v$database
v$datafile(file#ts#name)
v$tablespace(ts#name)
v$parameter（SQL>show parameter）
v$sga（SQL>show sga）
MTS（multithreaded server）
Oracle使用兩種配置模式dedicated server（專用模式）和shared server（即multithreaded server共享模式）缺省使用專用模式在連接數不很大且保持長期連接的情況下專用模式為每個連接設立一個專用oracle服務進程以保持較高的性能和穩定性而當連接數上升到非常高的數目且不保持長期連接時數據庫管理開銷增大並且占用大量系統資源給操作系統形成帶來極大的壓力在這種情況下共享模式更為有利它通過緩沖池和預先設定數目的server提供服務每個連接不再有專用的oracle服務進程每次SQL操作由分配器（dispatcher）確定oracle服務進程
multithread僅表示分配器展開的多個服務流程並非操作系統意義上的多線程
配置
￠ initoradbora
加入
mts_dispatchers = (address=(protocol=TCP))(dispatchers=) #初始分配器數量
mts_max_dispatchers = #最大分配器數量
mts_servers = #初始服務進程數量
mts_max_servers = #最大服務進程數量
mts_service = oradb #MTS方式下對外提供的數據庫服務非service_name
表明instance能夠提供MTS服務不意味著取消dedicated方式
￠ listenerora
應刪除所有SID_LISTSID_LIST的存在決定LISTENER以dedicated還是shared方式啟動oracle連接如SID_LIST存在LISTENER不再接受instance的登記以dedicated方式啟動oracle連接 如SID_LIST不存在LISTENER啟動時不為任何instance服務由instance來登記MTS service以shared方式啟動oracle連接
￠ client
MTS在client端配置頗為怪誕在tnsnamesora中的host一定要寫數據庫server的名字而且必須作全名解析似乎server端接收到client端請求後會將主機字符串返回應此client端必須能夠解析否則會報出諸如database service not exist的錯誤
tnsnamesora
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST= dbserver)(PORT = ))
)
(CONNECT_DATA =(SERVICE_NAME = oradb))
)
/etc/hosts
dbserver
啟動先起LISTENER後起instance
以下步驟均在數據庫open狀態下由system用戶完成
調整臨時表空間
alter tablespace temp temporary; #Oracle的orainst沒有將temp的缺省值permanent改為temporary這樣用戶在temp上暫存的數據均為永久對象很快將temp空間耗完Oraclei已修正
SQL>alter tablespace temp default storage (initial k next k maxextents pctincrease )
SQL查詢操作如group byorder bydistinctjoin等需要在臨時段上展開數據須充分考慮臨時段的大小
如果實例啟動參數指定hash_join_enabled=true（缺省為true）當oracle選擇以hash join方式進行表與表的聯接oracle根據查詢操作的實際情況計算出hash_multiblock_io_count此參數從屬於session平時顯示為即hash join一次I/O讀寫需要的連續數據空間這樣當此參數大於臨時段的next擴展塊時hash join操作會中斷如果預知聯接表的規模比較巨大可使用alter tablespace temp default storage(next …)將next值設為較大值待全部操作完成後再恢復正常
調整回滾表空間
先將建庫工具缺省設定的若干個回滾段刪除
SQL>alter rollback segment r offline
SQL>drop rollback segment r
根據實際需要創建回滾段（如rr）供聯機處理和批處理使用
SQL>create rollback segment r storage（initial k next k maxextents optimal M） tablespace rbs
SQL>alter rollback segment r online
注意修改$ORACLE_HOME/dbs/initoradbora中的激活回滾段段名
另創建一個尺寸無限制的回滾段（r）供特殊用途
SQL>create rollback segment r storage（initial k next k maxextents ） tablespace rbs
如果在創建回滾段時使用create public rollback segment則不需要在$ORACLE_HOME/dbs/initoradbora中用rollback_segment=(…)選項激活推薦使用public方式
相關系統表
SQL>select segment_name initial_extent next_extent max_extents extentsbytes from dba_segments where segment_type=ROLLBACK; #回滾段占用空間狀況
SQL>select segment_name status from dba_rollback_segs; #回滾段狀態
調整日志
建立日志組
SQL>alter database add logfile group x(logalogb) size M;
增加日志組成員
SQL>alter database add logfile member logc to group x
刪除日志
數據庫實例至少需要個日志組只有狀態為inactive的日志組才能被刪除而當前日志組狀態為current上一個切換的日志組狀態為active這就意味著至少存在個日志組才能刪除其中的一個如果要更新全部日志組只能刪除一個再創建一個直至全部被更新
SQL>alter database drop logfile group x
如果要刪除的日志組是當前日志組必須先將其切換至狀態為inactive再刪除
SQL>alter system switch logfile
刪除日志組成員
SQL>alter database drop logfile member logc;
相關系統表
v$log ＃日志組狀態占用空間順序號等
v$logfile ＃日志組文件
調整用戶表空間
創建表空間
假定表數據在ts_data索引在ts_index
SQL>create tablespace ts_data default storage（initial M next M maxextents pctincrease ） datafile path/data_dbf size M;
SQL>create tablespace ts_index default storage（initial M next M maxextents pctincrease ） datafile path/index_dbf size M;
參考命令刪除表空間
SQL>drop tablespace data including contents; ＃刪除表空間及其包含的所有數據對象
相關系統表
user(dba)_tablespaces
增加表空間尺寸
假定表空間ts_data由path/data_dbf和path/data_dbf（M）組成
增加一個數據文件
SQL>alter tablespace ts_data add datafile path/data_dbf size M;
擴大原有文件大小
SQL>alter database datafile path/data_dbf resize M;
移動表空間數據文件
假如要求為將path下data_dbf移至path下並把文件名改為datadbf
實例處於關閉狀態
sqlplus / as sysdba
SQL>startup mount
回到shell環境下
$ mv path/data_dbf path/datadbf
$ mv path/data_dbf path/datadbf
再到sqlplus環境中
SQL>alter database rename file path/data_dbf to path/datadbf;
或
SQL>alter tablespace tbsdata rename datafile path/data_dbf to path/datadbf;
SQL>alter database open;
查看剩余空間
SQL>select tablespace_namesum(bytes)max(bytes) from dba_free_space group by tablespace_name;
注意:空閒數據塊總和sum(bytes)夠用並不意味每個空閒塊都滿足分配需要所以當表空間不夠分配擴展塊的時候還要查看最大空閒數據塊max(bytes)的大小
合並空閒塊
如果表空間上的數據對象經常發生類似dropcreate的變動加之未采用統一的擴展塊尺寸使那些采用較大擴展塊的數據對象不能利用較小的空間碎片造成空間浪費可通過將較小的空閒塊合並成較大的空閒塊的方法減少空間浪費
SQL>alter tablespace tbsdata coalesce;
創建用戶
SQL>create user dbuser identified by oracle default tablespace data temporary tablespace temp quota unlimited on data quota on system quota on tools quota on users
SQL>grant connect to dbuser
SQL>grant create procedure to dbuser ＃這些權限足夠用於開發及生產環境
SQL>grant select on dba_pending_transactions to dbuser; ＃二階段提交過程中類似Tuxedo的軟件需要檢索掛起交易的狀態所以必須得到對此視圖的select權限以sys用戶身份賦予
修改用戶可使用alter user dbuser
參考命令
drop user dbuser cascade; ＃刪除用戶及其所有的數據對象
revoke connect from dbuser; ＃取消用戶角色權限
相關系統表
user(dba)_users
user(dba)_role_privs 角色權限
user(dba)_sys_privs 系統權限
user(dba)_tab_privs 對其他用戶表操作的權限
user_ts_quotas 表空間限額
創建數據對象
相關系統表
user_catalog(cat)
user_objects(obj)
表和索引建立在表空間上如果不指定表空間使用本用戶的缺省表空間（default tablespace）如果不指定本對象的存儲參數使用建於其上的表空間的缺省存儲參數（default storage）
表（table）
建表腳本通常是以下形式
create table emp （no number() name char() …constraint emp_x primary key(no)） storage（initial M next M pctincrease maxextents ） pctused pctfree tablespace tbs_data enable primary key using index tablespace tbs_index;
然而從簡化數據對象配置減少表空間碎片的角度考慮不推薦為每張表單獨指定storage選項存儲參數使用建於其上的表空間的缺省存儲參數不同表對擴展塊大小的要求可以通過分析歸類建立相應具有不同缺省存儲參數的表空間的方法解決這樣數據庫設計就能變得簡潔明了
命令簡化為
create table emp （no number() name char() … constraint emp_x primary key(no)）pctused pctfree tablespace tbs_data enable primary key using index tablespace tbs_index;
primary key關鍵字建立同名的primary key constraint和unique index表的每個域都有自身的constraint
相關系統表
user_tables(tabs)dba_tables #表屬性
user_tab_columns(cols)dba_tab_columns #表各列屬性
索引（index）
create index emp_x on emp(name) storage(initial M next M pctincrease maxextents ) pctfree tablespace tbs_index;
可參照表對storage的處理方式
create index emp_x on emp(name) pctfree tablespace tbs_index;
相關系統表
user_indexes(ind)dba_indexes #索引屬性
user_ind_columnsdba_ind_columns #索引各列屬性以index_position為順序
序列（sequence）
create sequence emp_seq increment by start with nomaxvalue nocycle;
相關系統表
user(dba)_sequences(seq) 序列屬性
視圖（view）
create emp_depart_view as select empnameemp_dutyname from empemp_duty where empduty=emp_dutyduty;
相關系統表
user(dba)_views 視圖屬性
Oracle將viewsequence用戶參數等定義均存放於系統表空間而用戶創建的表空間僅存放tableindex實體因此可以大膽刪除用戶表空間再用備份重新恢復不必擔心viewsequence等會被一並刪去
創建只讀用戶
假定數據庫用戶dbbrsr需要對dbuser的表emp擁有select權力
connect dbuser
grant select on emp to dbbrsr
connect dbbrsr
create synonym emp for dbuseremp;
這樣dbbrsr就能象使用自己的表一樣對dbuser的表執行select操作
啟動及關閉數據庫實例
oracle用戶dbstart和dbshut啟動及關閉/var/opt/oracle/oratab或/etc/oratab中設定的數據庫實例dbstart采用normal方式dbshut采用immediate方式
或者使用手工方式
sqlplus / as sysdba
啟動
normal
SQL>startup
mount
SQL>startup mount; #啟動實例進程載入數據庫文件允許DBA權限的某些操作但禁止對數據庫文件的一般性操作
SQL>完成某些操作
SQL>alter database open;
nomount
SQL>startup nomount; #啟動實例進程但不允許訪問數據庫常用於創建數據庫介質恢復或創建controlfile
SQL>完成某些操作
SQL>alter database open;
關閉
normal
SQL>shutdown或SQL>shutdown transactional; #等待每個連接交易完成後切斷連接再關閉數據庫
immediate
SQL>shutdown immediate; #立刻中止每個連接交易回滾
abort
SQL>shutdown abort; #立刻關閉數據庫不保證交易完整性在下一次啟動打開數據庫文件時會進行介質恢復
網絡配置
假定某一台機器為clientORACLE_SID為oraclient數據庫用戶為dbclient另一台機器為serverORACLE_SID為oraserver數據庫用戶為dbserver在server上$ORACLE_HOME/dbs/initoraserverora中有以下設定
db_name = oraserver
instance_name = oraserver
Oracle i
service_names=oraserver
TNS
Client端配置
修改$ORACLE_HOME/network/admin/tnsnamesora增加一條PROTOCOL=TCP的記錄
Oracle
db_server
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= server)(Port= ))
(CONNECT_DATA = (SID = oraserver))
)
Oraclei
db_server
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(HOST= server)(PORT= ))
(CONNECT_DATA = (SERVICE_NAME=oraserver
)
HOST可在/etc/hosts或DNS中配置或直接寫上IP地址
sqlplus dbserver/passwd@db_server
Server端配置
修改$ORACLE_HOME/network/admin/listenerora
在LISTENER中增加ADDRESS的記錄
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
(ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = ))
)
)
)
在SID_LIST_LISTENER中增加SID_DESC記錄
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/app/oracle/product/)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = oraserver)
(ORACLE_HOME = /opt/oracle/app/oracle/product/)
(SID_NAME = oraserver)
)
)
HOST可在/etc/hosts或DNS中配置或直接寫上IP地址
注意LISTENER和SID_LIST_LISTENER是成對出現的可配置多個監聽服務進程和相應的SID_LIST如LISTENER_和SID_LIST_LISTENER_
DB Link
如果client的instance需要在訪問本地數據對象同時訪問server中的數據對象可在instance中創建對server的數據庫連接實現間接訪問
在tnsnamesora中建立db_server配置
sqlplus dbclient/passwd
SQL>create database link server_link connect to dbserver identified by passwd using db_server;
使用emp@server_link訪問server上的emp如同訪問本地instance中的數據對象一樣為了更方便的使用可建立synonym
Oracle i
Oracle i相較於Oracle &i在兼容Oracle &i的基礎上回滾和臨時表空間配置發生比較大的變化導致建庫操作出現一些不同在數據庫配置文件initoradbora中有關於回滾表空間的選項詳細情況在數據庫配置中解釋而且Oracle i簡化了表空間的創建所以此小節主要描述Oracle i相對於Oracle i的差異其它相同的操作可參考Oracle i
手工創建
由於在Oracle i中工具dbassist的使用方法與在Oracle i中類似因此工具建庫過程省略只記錄手工建庫過程
Oracle i中的建庫過程已經變得極為簡潔大致如下
create database ${ORACLE_SID}
user sys identified by sys
user system identified by system
logfile group (${ORACLE_BASE}/oradata/${ORACLE_SID}/redolog) size M
group (${ORACLE_BASE}/oradata/${ORACLE_SID}/redolog) size M
group (${ORACLE_BASE}/oradata/${ORACLE_SID}/redolog) size M
maxlogfiles
maxlogmembers
maxloghistory
maxdatafiles
maxinstances
archivelog
character set ZHSGB
national character set ALUTF
datafile ${ORACLE_BASE}/oradata/${ORACLE_SID}/systemdbf size M
default temporary tablespace tbstemp tempfile ${ORACLE_BASE}/oradata/${ORACLE_SID}/tempdbf size M
undo tablespace tbsundo datafile ${ORACLE_BASE}/oradata/${ORACLE_SID}/undodbf size M;
其特點為使用專用的回滾和臨時表空間而不象Oracle i中的那樣回滾和臨時表空間與普通表空間沒有差異這樣既簡化了配置也有利於效能提高要注意臨時表空間的指定文件關鍵字是tempfile而不是通用的datafile而且臨時表空間的存儲選項必須為uniform由Oracle系統決定同樣回滾表空間也是由Oracle系統決定不必人工干預
Oracle i在$ORACLE_HOME/dbs下可使用二進制配置文件缺省為spfile{實例名}ora如spfileoradbora支持Oracle系統進程在不重啟的情況下動態調整參數這對要求不間斷運行的系統是有利的在建庫階段就可將此配置文件創建起來
create spfile from pfile= ${ORACLE_BASE}/admin/${ORACLE_SID}/init${ORACLE_SID}ora
完整步驟見/i/createdbsh編輯如pfile數據文件目錄等參數以符合實際情況並轉移到$ORACLE_BASE/admin/oradb/create/下執行
創建用戶表空間
Oracle i對於表空間管理一個明顯的變化是改數據字典管理（extent management dictionary）為表空間本地管理（extent management local）還可以根據建立的數據對象對空間的要求自動確定擴展塊的大小（autoallocate）最小為K這兩項都是創建表空間的缺省選項
create tablespace tbsdata datafile … [ extent management local ] [ autoallocate ];
而對於指定每個擴展塊大小的創建策略設立了新選項統一擴展塊大小（uniform [size xxx[K|M]]）可覆蓋autoallocate選項如果不加上具體的size xxx[K|M]缺省為M這樣就不必考慮Oracle i中的如initialnextpctincreasemaxextents等default storage參數應如何組合事實上Oracle i的這些設置原本就沒有什麼意義
不能夠同時指定extent management local和default storage換言之default storage只能和extent management dictionary一起顯式指定
如果未指定extent management的類型Oracle i缺省使用local方式如果又同時使用default storage選項就有以下的判斷
如果使用minimun extentOracle檢查是否minumum extent=initial=next且pctincrease＝如是Oracle使用uniform選項size=initial如不是Oracle忽略指定選項使用autoallocate
如果未指定minimum extentOracle檢查是否initial=next且pctincrease＝如是Oracle使用uniform選項size=initial如不是Oracle忽略指定選項使用autoallocate
為了避免與Oracle i的習慣做法混淆建議只使用Oracle i較簡潔的方法
對於存儲少量靜態數據的表空間來說如配置信息等可簡單地寫為
create tablespace tbsdata datafile …;
對於必須關心其擴展塊大小的表空間如大批量的記錄或索引可簡單地寫為
create tablespace tbsdata datafile … uniform size M;
初始化文件配置
所有參見內容都在附件_install__create__init/下
描述initoradbora中各選項
Oracle & i
具體參見i/initoradbora
db_block_size
數據庫基本數據塊尺寸字節為單位
當涉及到大量數據交換時例如export/import操作時此參數對數據庫性能有非常大的影響設定一個較大的值有利於提高數據吞吐量但由於db block是文件和內存之間交換的基本單位過大的值反而會交換不需要的記錄增加額外的I/O
一般取k就已能獲得較滿意效果
db_block_buffers
數據緩沖區db_block_size為單位不超過/內存
計算查詢緩沖命中率
SELECT name value FROM v$sysstat WHERE name IN (db block gets consistent gets physical reads);
Hit Ratio = ( physical reads / (db block gets + consistent gets) )
SELECT name phyrds phywrts FROM v$datafile df v$filestat fs WHERE dffile# = fsfile#
db block gets:在內存buffer中的命中次數
consistent gets:一致性命中次數指在內存buffer中未命中但從回滾段或數據文件中獲得命中
physical reads:在數據文件中的讀次數
注意一般HitRatio達到%以上就可以認為已達到優化這個數值應在系統運行穩定後進行統計
shared_pool_size
數據字典和SQL操作緩沖區字節為單位不超過/內存
select (sum(pins reloads)) / sum(pins) Lib Cache from v$librarycache;
select (sum(gets getmisses usage fixed)) / sum(gets) Row Cache from v$rowcache;
select * from v$sgastat where name = free memory
注意Cache命中率達到%以上就可以認為已達到優化這個數值應在系統運行穩定後進行統計
log_checkpoint_interval
日志提交點數據量間隔
以操作系統block(通常byte)為單位當日志累計至此參數會使sga中dirty buffer被同步至數據文件日志切換時也會引起此操作如設為則相當於無限大此參數失去作用日志提交僅依靠日志文件的切換
應選擇適當大小的日志文件同時使log_checkpoint_interval略大於日志文件或設為原則上應該避免過於頻繁的checkpoint操作控制在分鐘以上為好
推薦此參數設為
log_buffer
在線日志緩沖字節為單位K或K*CPU數量取較大值
processes和sessions
dedicated server模式下每一個連接都有一個Oracle服務進程（process）為之服務這個連接本身也就是一個會話（session）
shared server模式下所有連接共享一個Oracle服務進程池這樣process和session就不再是一一對應sessions要大於processes
sort_area_size和sort_area_retained_size
排序緩沖區字節為單位
當排序記錄被全部取走後緩沖區縮減到sort_area_retained_size為減少緩沖區縮放的開銷可使sort_area_size和sort_area_retained_size取相同值
hash_area_size
hash join緩沖區字節為單位缺省為*sort_area_size
db_file_multiblock_read_count
每次讀取的db block數對大規模查詢性能有提高特別是表掃描效率在線系統應避免這種類型的查詢
db_writer_processes
同步數據進程數與checkpoint的頻率和數據量有關
db_block_lru_latches
LRU鎖集一般設為CPU數目RedHat Linux x下的Oracle 設此參數會導致系統掛起疑對smp支持有問題
log_archive_start
系統啟動時是否同時啟動歸檔進程（archive）
log_archive_dest_
歸檔日志目錄最後的標號表明歸檔線程編號一般只用
log_archive_format
歸檔日志名稱%t指歸檔線程編號 %s指歸檔日志序列號
rollback_segments
如果創建回滾段（rollback segment）時不使用public選項那就是使用私有的回滾段這樣就必須在系統啟動時激活
推薦使用public rollback segment的做法這個選項可以廢棄
background_dump_dest
Oracle系統進程記錄log和trc目錄
alert_{實例名}log以文本方式記錄系統啟動關閉出錯存儲變化日志切換等log信息
系統進程以各自名稱和進程號記錄錯誤信息文件以trc為後綴文本格式
core_dump_dest
Oracle服務進程的core dump目錄
user_dump_dest
Oracle服務進程以各自名稱和進程號記錄錯誤信息文件以trc為後綴文本格式
Oracle i
參見i/initoradbora
pga_aggregate_target
以KMG為單位
sort groupby hashjoin bitmap merge bitmap create等對內存有一定需求的SQL操作都由此選項統一動態分配內存區域大小因此Oracle i中如sort_area_sizesort_area_retained_sizehash_area_sizebitmap_merge_area_size等選項可以廢棄
db_cache_size
數據緩沖區以KMG為單位自動對齊到粒度單位
取代Oracle i的db_block_buffers選項
undo_management
回滾空間管理模式缺省為manual使用回滾段（rollback segment）如設為auto則使用Oracle i的回滾表空間此選項決定了以下關於undo的其它選項
undo_retention
已提交數據在回滾表空間中保留時間以秒為單位缺省
當某些較長時間的查詢需要通過回滾數據重建老數據塊的時候此選項可使新事務盡可能使用空閒的回滾表空間這樣就減少了查詢過程因snapshot too old而失敗的幾率
然而當空閒回滾表空間不足以應付新事務時系統仍然會重用此選項保留的空間因此不能保證長查詢一定能成功執行完畢
undo_tablespace
指定系統啟動時的回滾表空間
工具
所有參見內容都在附件_tool/下
在《優化》一節中討論以下工具使用的效率
sqlldr
參見sqlldr/
用於將格式化的文本數據上載到表中去
以表emp為例
首先編寫一個控制命令的腳本文件通常以ctl結尾內容如下
empctl
load data
append
into table emp
fields terminated by |
(
no float external
name char()
age integer external
duty char()
salary float external
upd_ts date() YYYYMMDDHHMISS
)
括號裡對數據文件裡每個數據域進行解釋以此在上載時與目標表進行比對
除了append外還有insertreplacetruncate等方式與append大同小異不作更多的解釋
再將上載數據組織成數據文件通常以dat結尾內容如下
empdat
|Tom||||
|Jerry||||
分隔符要與ctl文件中fields terminated by指定的一致這個例子中為|
ctl和dat文件就緒後可以執行上載命令為
sqlldr dbuser/oracle control=empctl data=empdat
也可以將dat文件合並在ctl文件中ctl文件改寫為
empctl
load data
infile *
append
into table emp
fields terminated by |
(
no float external
name char()
age integer external
duty char()
salary float external
upd_ts date() YYYYMMDDHHMISS
)
begindata
|Mulder||||
|Scully||||
控制文件中infile選項跟sqlldr命令行中data選項含義相同如使用infile *則表明數據在本控制文件以begin data開頭的區域內
這樣命令變成
sqlldr dbuser/oracle control=empctl
conventional path
通過常規通道方式上載
rows每次提交的記錄數
bindsize每次提交記錄的緩沖區
readsize與bindsize成對使用其中較小者會自動調整到較大者
sqlldr先計算單條記錄長度乘以rows如小於bindsize不會試圖擴張rows以填充bindsize如超出則以bindsize為准
命令為
sqlldr dbuser/oracle control=empctl log=emplog rows= bindsize=
direct path
通過直通方式上載不進行SQL解析
命令為
sqlldr dbuser/oracle control=empctl log=emplog direct=true
exp
參見dmp/exp_demosh
將數據庫內的各對象以二進制方式下載成dmp文件方便數據遷移
buffer下載數據緩沖區以字節為單位缺省依賴操作系統
consistent下載期間所涉及的數據保持read only缺省為n
direct使用直通方式 缺省為n
feeback顯示處理記錄條數缺省為即不顯示
file輸出文件缺省為expdatdmp
filesize輸出文件大小缺省為操作系統最大值
indexes是否下載索引缺省為n這是指索引的定義而非數據exp不下載索引數據
loglog文件缺省為無在標准輸出顯示
owner指明下載的用戶名
query選擇記錄的一個子集
rows是否下載表記錄
tables輸出的表名列表
下載整個實例
exp dbuser/oracle file=oradbdmp log=oradblog full=y consistent=y direct=y
user應具有dba權限
下載某個用戶所有對象
exp dbuser/oracle file=dbuserdmp log=dbuserlog owner=dbuser buffer= feedback=
下載一張或幾張表
exp dbuser/oracle file=dbuserdmp log=dbuserlog tables=tabletable buffer= feedback=
下載某張表的部分數據
exp dbuser/oracle file=dbuserdmp log=dbuserlog tables=table buffer= feedback= query=\where col=\…\ and col \<…\
不可用於嵌套表
以多個固定大小文件方式下載某張表
exp dbuser/oracle file=dmpdmpdmp… filesize=m tables=emp buffer= feedback=
這種做法通常用在表數據量較大單個dump文件可能會超出文件系統的限制
直通路徑方式
direct=y取代buffer選項query選項不可用
有利於提高下載速度
consistent選項
自export啟動後consistent=y凍結來自其它會話的對export操作的數據對象的更新這樣可以保證dump結果的一致性但這個過程不能太長以免回滾段和聯機日志消耗完
imp
參見dmp/imp_demosh
將exp下載的dmp文件上載到數據庫內
buffer上載數據緩沖區以字節為單位缺省依賴操作系統
commit上載數據緩沖區中的記錄上載後是否執行提交
feeback顯示處理記錄條數缺省為即不顯示
file輸入文件缺省為expdatdmp
filesize輸入文件大小缺省為操作系統最大值
fromuser指明來源用戶方
ignore是否忽略對象創建錯誤缺省為n在上載前對象已被建立往往是一個正常現象所以此選項建議設為y
indexes是否上載索引缺省為n這是指索引的定義而非數據如果上載時索引已建立此選項即使為n也無效imp自動更新索引數據
loglog文件缺省為無在標准輸出顯示
rows是否上載表記錄
tables輸入的表名列表
touser指明目的用戶方
上載整個實例
imp dbuser/oracle file=oradbdmp log=oradblog full=y buffer= commit=y ignore=y feedback=
上載某個用戶所有對象
imp dbuser/oracle file=dbuserdmp log=dbuserlog fromuser=dbuser touser=dbuser buffer= commit=y ignore=y feedback=
上載一張或幾張表
imp dbuser/oracle file=userdmp log=userlog tables=tabletable fromuser=dbuser touser=dbuser buffer= commit=y ignore=y feedback=
以多個固定大小文件方式上載某張表
imp dbuser/oracle file=\(dmpdmpdmp…\) filesize=m tables=emp fromuser=dbuser touser=dbuser buffer= commit=y ignore=y feedback=
sqlplus
參見sqlplus/downloadsh
僅列出常用的選項對復雜的應用不作深究
命令行參數
/ as {sysdba|sysopr}使用操作系統用戶驗證以osdba或osopr一員的身份登錄如驗證通過被賦予sysdba或sysopr的權限
使用格式sqlplus / as sysdba
/nolog不執行connect操作直接進入sqlplus操作界面
ssilent模式不顯示sqlplus啟動信息和提示符
<接受sql腳本從標准輸入重定向
<<立即文檔
提示符命令
accept variable [number|char|date] [format format] [default default] [prompt text] [hide]接受輸入變量
例子accept pwd char format a prompt Password: hide
column column [format format] [heading heading]設定對某個域的顯示格式
如果要同時改變某域的輸出長度和標題必須使用column命令
見emp的定義name本為char()輸出縮為位duty本為 char()擴張為位以便有足夠的空間顯示中文標題
SQL>column name format a heading 姓名;
SQL>column duty format a heading 職位;
SQL>column age format heading 年齡;
SQL>column upd_ts format a heading 更新時間;
SQL>select namedutyageupd_ts from emp;
show option顯示SET的選項
spool [filename|off]輸出重定向文件
timing [start text|show|stop]定時器
SET選項
autocommit自動提交insertupdatedelete帶來的記錄改變缺省為off
colsep域輸出分隔符
define識別命令中的變量前綴符缺省為on也就是&碰到變量前綴符後面的字符串作為變量處理
如果待更新內容包含&（在URL中很常見）而define非設為offsqlplus會把&後面緊跟的字符串當成變量提示輸入這裡必須重新輸入&和那個字符串才能實現正常更新將define設為off就不再進行變量判斷
SQL>set define off;
SQL>update bbs_forum set url=&forum_id= where forum_id=;
echo顯示start啟動的腳本中的每個sql命令缺省為on
feedback回顯本次sql命令處理的記錄條數缺省為on
heading輸出域標題缺省為on
linesize輸出一行字符個數缺省為
如果一行輸出超過linesize會回車到第二行這樣格式就會混亂
markup htmlhtml格式輸出缺省為off
通常需要與spool配合否則html輸出就沒有意義
numwidth輸出number類型域長度缺省為
長number類型的域常常因為輸出長度的問題引起誤會
pagesize輸出每頁行數缺省為
為了避免分頁可設定為
termout顯示腳本中的命令的執行結果缺省為on
timing顯示每條sql命令的耗時缺省為off
trimout去除標准輸出每行的拖尾空格缺省為off
trimspool去除重定向（spool）輸出每行的拖尾空格缺省為off
例子
以文本形式下載表數據
oracle缺乏將表中數據輸出至文本文件的工具因此只能利用sqlplus和unix工具做變通的處理
sqlplus s dbuser/oracle </dev/null
set colsep |;
set echo off;
set feedback off;
set heading off;
set pagesize ;
set linesize ;
set numwidth ;
set termout off;
set trimout on;
set trimspool on;
spool tmptxt;
select * from emp;
spool off;
exit
EOF
tr d < tmptxt >emptxt 刪除空格可選
注意一定要用spool如果在命令行中直接用>tmptxt可能會造成數據缺失至少在Unixware上如此
假定某域是char(n)如中間出現回車\n則下載出的這條記錄的格式將會錯亂不宜采用此方法
備份及恢復
所有參見內容都在附件_backup/下
export與import方式
參見dmp/backupsh
見《工具》對exp和imp的描述
數據庫中的對象是比較多的但除了表以外占用的空間不大所以當表中記錄數量達到一定規模後以用戶的方式一下子把數據exp出來就顯得不夠靈活考慮以下的策略先exp出除表數據以外的所有對象再分別exp出每張表的數據
exp dbuser所有的數據對象
exp dbuser/oracle file=dbuserdmp log=userlog owner=user buffer= rows=n
exp單張表的數據
sqlplus s dbuser/oracle </dev/null
set colsep |;
set echo off;
set feedback off;
set heading off;
set pagesize ;
set linesize ;
set termout off;
set trimout on;
set trimspool on;
spool tablestxt;
select table_name from user_tables;
spool off;
exit;
EOF
for table in $(cat tablestxt)
do
exp dbuser/oracle file=${table}_$(date +%Y%m%d)dmp tables=$table direct=y
done
冷備份
shutdown數據庫將所有和本實例有關的文件包括datafilecontrolfileredologarchived redologinitoraora等全部備份恢復時只要將這些文件放回從前的目錄startup數據庫即可
聯機全備份+日志備份
設置
如果數據庫實例原來沒有使用歸檔日志功能則必須進行配置修改
initoradbora
log_archive_start = true #實例啟動時同時啟動歸檔進程
log_archive_dest_= location=/appl/oracle/oradata/orafe/arch/arch #歸檔日志目錄
打開歸檔日志功能
shutdown數據庫
sqlplus / as sysdba
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
可用archive log list查看狀態去除歸檔日志功能的命令為alter database noarchivelog
步驟
參見online/fullshdailysh以osdba組的用戶執行
聯機全備份
數據庫處於open狀態依次對各個表空間備份
sqlplus / as sysdba
SQL>alter tablespace system begin backup;
復制此tablespace各個datafile
SQL>alter tablespace system end backup;
注意據推測begin backup是對tablespace凍結寫入end backup是解除凍結因此復制datafile的過程不宜過長
備份controlfile
SQL>alter database backup controlfile to ……;
日志備份
sqlplus / as sysdba
SQL>alter system archive log stop;
移去日志目錄下的所有archived redolog
SQL>alter system archive log start;
恢復
數據庫處於shutdown狀態
最差情況磁盤全部損壞僅保存上次聯機全備份和每天日志備份
解決硬件故障配置系統軟件及環境
oracle用戶將全備份和日志備份轉移至相應目錄根據initoradbora中controlfile的配置將備份控制文件復制到響應目錄下
sqlplus / as sysdba
SQL>startup mount
SQL>recover database until cancel using backup controlfile;
逐個確認待恢復的archived redolog待最後一個完成後鍵入cancel使恢復結束
SQL>alter database open resetlogs;
注意由於日志已經重置所以應盡快做一次聯機全備份
丟失某數據文件
只要將此文件從上次聯機全備份中復制至其目錄並將自上次聯機全備份以來所有日志備份移至歸檔目錄
sqlplus / as sysdba
SQL>startup mount
SQL>alter database recover datafile path/file;或者簡單些recover database;
SQL>alter database open;
如果此文件損壞或丟失又無備份則只能將此文件脫機將數據exp出來重建表空間再imp進去
sqlplus / as sysdba
SQL>connect internal
SQL>startup mount
SQL>alter database datafile path/file offline;
SQL>alter database open;
注意要點
無論有多少把握恢復前先做冷備份此為第一原則
不這樣做便是無路可退一旦失誤後果不必多說
rollback段損壞
這是非常嚴重的問題可在initoraora中寫入_corrupted_rollback_segments=(rxx)啟動時避開損壞的rollback段這只是權宜之計如數據庫處於archivelog應從上一次全備份起利用備份的日志進行恢復如數據庫處於noarchivelog應盡快將全部數據export出來重建數據庫再import進去所有操作之前應做冷備份
數據庫異常中止處理
通過手工shutdown abort操作中止數據庫不會產生大的問題通常直接startup無需使用介質恢復命令
如果由於機器崩潰引起的中止則情況嚴重得多有可能要使用到上面提到的恢復方法不過這種現象並不多見一般需要顯式使用介質恢復命令如下
sqlplus / as sysdba
SQL>startup mount;
SQL>recover database;
SQL>alter database open;
From:http://tw.wingwit.com/Article/program/Oracle/201311/18303.html
