【正文】 memory structures: DB buffer cache, shared SQL pool, and redo log buffer ? Explain primary background processes: DBW0, LGWR, CKPT, PMON, SMON, and ARC0 ? Explain SQL processing steps: parse, execute, fetch 數(shù)據(jù)字典 數(shù)據(jù)字典 Data Dictionary ? 數(shù)據(jù)字典 – 數(shù)據(jù)字典是 Oracle數(shù)據(jù)庫的重要組成。它由一組只讀表組成。 – 包括： ? 數(shù)據(jù)庫所有對象的定義（表，視圖，索引，簇，同義詞，序列 ，過程，函數(shù)，程序包，觸發(fā)器） ? 空間的分配和使用狀況 ? 列的缺省值 ? 完整性約束信息 ? 用戶名 ? 已授予用戶的角色和權(quán)限 ? 審計信息等 ? 數(shù)據(jù)字典結(jié)構(gòu) – 基表 ? 存放相關(guān)數(shù)據(jù)庫信息的基礎(chǔ)表 – 視圖 ? 匯總并顯示存放數(shù)據(jù)字典基表中信息的視圖 ? 數(shù)據(jù)字典所有者 – Oracle的 sys用戶擁有數(shù)據(jù)字典所有的基表和視圖。 任何 Oracle用戶都不能改變數(shù)據(jù)字典的任何數(shù)據(jù)。不能更新、插入、 刪除。數(shù)據(jù)字典的改變會影響數(shù)據(jù)庫的正常運行。 – 系統(tǒng)管理員需要嚴格管理系統(tǒng)用戶 .(sys and system) 數(shù)據(jù)字典分類 – USER_* :用戶擁有的對象 – ALL_*： 可訪問的對象 – DBA_*： 所有對象 . 93個 – 118個 – 165個 –從 DICTIONARY（ DICT）中可查到名稱 常用的數(shù)據(jù)字典 Dba_tablespaces Dba_data_files Dba_tables Dba_users Dba_views Dba_indexes Dba_db_links Dba_free_space Dba_ind_columns Dba_jobs Dba_objects Dba_rollback_segs Dba_segments Dba_sequences Dba_snappshots Dba_synonyms Dba_tab_columns Dba_triggers Dba_constraints Dba_cons_columns 前綴 范圍 USER 用戶視圖 ALL 用戶可訪問的部分 DBA 數(shù)據(jù)庫管理員視圖 PL*SQL PL*SQL – SQL是結(jié)構(gòu)化的查詢語言 ? Oracle的 SQL語言包含對 ANSI/ISO標準 SQL語言的擴充。 ? Oracle的 SQL語言分為以下幾類： – DML（數(shù)據(jù)操縱語言） – DDL（數(shù)據(jù)定義語言） – 事物處理的控制語言 – 會話控制語句 – 系統(tǒng)控制語句 – SQL*PLUS ? 是 Oracle的一個使用程序，它可以運行于客戶端，也可以運行 服務(wù)器端，通過它能夠查看數(shù)據(jù)字典的信息，以及查看用戶數(shù)據(jù)和結(jié)構(gòu)等，運行程序。 – PL*SQL ? 是 Oracle的一種過程化的ＳＱＬ語言。他有自己的程序結(jié)構(gòu)， 有各種條件控制和循環(huán)控制。通過ＰＬ＊ＳＱＬ語言，能夠?qū)憯?shù)據(jù)庫的存儲過程和包。甚至通過 PL*SQL語言還可以開發(fā)基于 Web的應(yīng)用。 Oracle SQL， SQL*Plus and PL*SQL ? DML – Select (從一個或多個表或視圖中檢索數(shù)據(jù) ) select empno,ename,sal,deptno from emp where deptno=10。 ? 復(fù)雜的 select 語句： – group by 子句 select deptno,min(sal),max(sal) from emp group by deptno。 – having 子句 select deptno,min(sal),max(sal) from emp where job=‘ CLERK’ group by deptno having min(sal)1000。 – Order by select ename ,deptno,sal from emp order by deptno ASC,sal DESC。 – for upfate select empno,sal,m from emp,dept where job=‘ CLERK’ and = and loc=‘ NEW YORK’ for update。 select empno,sal,m from emp,dept where job=‘ CLERK’ and = and loc=‘ NEW YORK’ for update of 。 – join select empno,ename,dname from emp,dept where =。 ? DML – update(改變表與視圖現(xiàn)有行與列的值 ) update emp set sal=1000 where empno=7369。 – Insert (向表與視圖中增加新行 ) insert into emp values(8888,‘ abc’ ,‘ clerk’ ,0000,sysdate,2023,0,10)。 – delete (從表與視圖中刪除行 ) delete from emp where deptno=10。 ? 不支持 select into ，要用 create as create table as select * from tablename 常用的 sql函數(shù) ? number function – abs(n) – mod(m,n) – power(m,n) – round(m,n) – sign(n) – sort(n) 開平方 – trunc(n,m) ? Date function – ADD_MONTHS(d,n) – LAST_DAY(d) – MONTHS_BETWEEN(d1, d2) – NEXT_DAY(d, char) – SYSDATE ... ? Conversion Functions – TO_CHAR(d ,format) – TO_CHAR(n ,format) – TO_NUMBER – TO_DATE ? character function – CONCAT(char1, char2) / *返回 char1與 char2的連接 */ – INITCAP(char) /* 返回第一個字母大寫的 string */ – LPAD(char1,n [,char2]) – LTRIM(char [,set]) – LOWER – REPLACE(char,search_string[,replacement_string]) – SUBSTR(char, m [,n]) – INSTR (char1,char2 [,n[,m]]) – UPPER(char) – LENGTH(char) ... ? 其它函數(shù) – NVL(expr1, expr2) – USER ... SQL*Plus SQLPlus 是 Oracle 的一個實用程序 . ? sql*plus的使用： –數(shù)據(jù)庫創(chuàng)建后，將安裝 scott/tiger的 demo用戶。 sqlplus scott/tiger sql /* sql*plus的命令行狀態(tài) */ sql set pause on spool filename spool off 維護參數(shù)文件（ ） Managing an Oracle Instance Uses of Parameters – The parameter file, monly referred to as the init file, is a text file that can be maintained using a standard operating system editor. By default, it is located in the %ORACLE_HOME%\database directory on Windows NT. With Oracle8i on Windows NT, the parameter file points to the %ORACLE_HOME%\admin\ sid \pfile directory where the actual parameter file is stored. The parameters in the init file can have a significant effect on database performance, and some need to be modified in the following ways for production systems: ? Size the System Global Area (SGA). ? Set database and instance defaults. ? Set user or process limits. ? Set limits on database resources. ? Define various physical attributes of the database,such as the database block size. ? Specify control files, archived log files, the ALERT file, and trace file locations. Rules for Specifying Parameters ? Specify the values in the following format: keyword=value. ? All parameters are optional. ? The server has a default value for each parameter. This value may be operating system dependent, depending on the parameter. ? Parameters can be specified in any order. ? Comment lines begin with the symbol. ? Enclose parameters in double quotation marks to include character literals. ? Additional files can be included with the keyword IFILE. ? If case is significant for the operating system, then it is also significant in filenames. ? Multiple values are enclosed in parentheses and separated by mas. Note: Develop a standard for listing parameters。 either list them alphabetically or group them by functionality. Starting the Instance Usually you would start an instance without mounting a database only during database creation or the recreation of control files. Starting an instance includes the following tasks: ? Reading the parameter file init ? Allocating the SGA ? Starting the background processes ? Opening the ALERT file and the trace files The database must be named with the DB_NAME parameter either in the init file or in the STARTUP mand. Mounting the Database To perform specific maintenan