Oracle之常用FAQ–Oracle 構架體系

第二部分、ORACLE構架體系[Q]ORACLE的有那些資料類型[A]常見的資料類型有CHAR固定長度字元域，最大長度可達2000個位元組NCHAR多位元組字元集的固定長度字元域，長度隨字元集而定，最多為2000個字元或2000個位元組VARCHAR2可變長度字元域，最大長度可達4000個字元NVARCHAR2多位元組字元集的可變長度字元域，長度隨字元集而定，最多為4000個字元或4000個位元組DATE用於儲存全部日期的固定長度(7個位元組)字元域，時間作為日期的一部分儲存其中。除非通過設定init.ora檔案的NLS_DATE_FORMAT參數來取代日期格式，否則查詢時，日期以DD-MON-YY格式表示，如13-APR-99表示1999.4.13NUMBER可變長度數值列，允許值為0、正數和負數。NUMBER值通常以4個位元組或更少的位元組儲存，最多21位元組LONG可變長度字元域，最大長度可到2GBRAW表示位元據的可變長度字元域，最長為2000個位元組LONGRAW表示位元據的可變長度字元域，最長為2GBMLSLABEL只用於TrustedOracle，這個資料類型每行使用2至5個位元組BLOB二進位大對象，最大長度為4GBCLOB字元大對象，最大長度為4GBNCLOB多位元組字元集的CLOB資料類型，最大長度為4GBBFILE外部二進位檔案，大小由作業系統決定ROWID表示RowID的位元據，Oracle8RowID的數值為10個位元組，在Oracle7中使用的限定RowID格式為6個位元組UROWID用於資料定址的位元據，最大長度為4000個位元組[Q]Oracle有哪些常見關鍵字，不能被用於對象名[A]以8i版本為例，一般保留關鍵字不能用做對象名ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT BETWEEN BY CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT FOR FROM GRANT GROUP HAVING IDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT INTO IS LEVEL LIKE LOCK LONG MAXEXTENTS MINUS MLSLABEL MODE MODIFY NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER OF OFFLINE ON ONLINE OPTION OR ORDER PCTFREE PRIOR PRIVILEGES PUBLIC RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS SELECT SESSION SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM SYSDATE TABLE THEN TO TRIGGER UID UNION UNIQUE UPDATE USER VALIDATE VALUES VARCHAR VARCHAR2 VIEW WHENEVER WHERE WITH詳細資料可以查看v$reserved_words視圖[Q]怎麼查看資料庫版本[A]select * from v$version包含版本資訊，核心版本資訊，位元資訊(32位或64位)等至於位元資訊，在linux/unix平台上，可以通過file查看，如file $ORACLE_HOME/bin/oracle[Q]怎麼查看資料庫參數[A]show parameter 參數名如通過show parameter spfile可以查看9i是否使用spfile檔案或者select * from v$parameter除了這部分參數，Oracle還有大量隱含參數，可以通過如下語句查看:SELECT NAME,VALUE,decode(isdefault, 'TRUE','Y','N') as "Default",decode(ISEM,'TRUE','Y','N') as SesMod,decode(ISYM,'IMMEDIATE', 'I','DEFERRED', 'D','FALSE', 'N') as SysMod,decode(IMOD,'MODIFIED','U','SYS_MODIFIED','S','N') as Modified,decode(IADJ,'TRUE','Y','N') as Adjusted,descriptionFROM ( --GV$SYSTEM_PARAMETERSELECT x.inst_id as instance,x.indx+1,ksppinm as NAME,ksppity,ksppstvl as VALUE,ksppstdf as isdefault,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM,decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ,ksppdesc as DESCRIPTIONFROM x$ksppi x,x$ksppsv yWHERE x.indx = y.indxAND substr(ksppinm,1,1) = '_'AND x.inst_id = USERENV('Instance'))ORDER BY NAME[Q]怎麼樣查看資料庫字元集[A]資料庫伺服器字元集select * from nls_database_parameters，其來源於props$，是表示資料庫的字元集。用戶端字元集環境select * from nls_instance_parameters,其來源於v$parameter，表示用戶端的字元集的設定，可能是參數檔案，環境變數或者是註冊表會話字元集環境 select * from nls_session_parameters，其來源於v$nls_parameters，表示會話自己的設定，可能是會話的環境變數或者是alter session完成，如果會話沒有特殊的設定，將與nls_instance_parameters一致。用戶端的字元集要求與伺服器一致，才能正確顯示資料庫的非Ascii字元。如果多個設定存在的時候，alter session>環境變數>註冊表>參數檔案字元集要求一致，但是語言設定卻可以不同，語言設定建議用英文。如字元集是zhs16gbk，則nls_lang可以是American_America.zhs16gbk。[Q]怎麼樣修改字元集[A]8i以上版本可以通過alter database來修改字元集，但也只限於子集到超集，不建議修改props$表，將可能導致嚴重錯誤。Startup nomount;Alter database mount exclusive;Alter system enable restricted session;Alter system set job_queue_process=0;Alter database open;Alter database character set zhs16gbk;[Q]怎樣建立基於函數索引[A]8i以上版本，確保Query_rewrite_enabled=trueQuery_rewrite_integrity=trustedCompatible=8.1.0以上Create index indexname on table (function(field));[Q]怎麼樣移動表或表分區[A]移動表的文法Alter table tablename move[Tablespace new_nameStorage(initial 50M next 50Mpctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]移動分區的文法alter table tablename move (partition partname)[update global indexes]之後之後必須重建索引Alter index indexname rebuild如果表有Lob段，那麼正常的Alter不能移動Lob段到別的資料表空間，而僅僅是移動了表段，可以採用如下的方法移動Lob段alter table tablename movelob(lobsegname) store as (tablespace newts);[Q]怎麼獲得當前的SCN[A]9i以下版本select max(ktuxescnw*power(2,32)+ktuxescnb) from x$ktuxe;如果是9i以上版本，還可以通過以下語句擷取select dbms_flashback.get_system_change_number from dual;[Q]ROWID的結構與組成[A]8以上版本的ROWID組成OOOOOOFFFBBBBBBRRR8以下ROWID組成（也叫受限Rowid）BBBBBBBB.RRRR.FFFF其中，O是對象ID，F是檔案ID，B是塊ID，R是行ID如果我們查詢一個表的ROWID，根據其中塊的資訊，可以知道該表確切佔用了多少個塊，進而知道佔用了多少資料空間（此資料空間不等於表的分配空間）[Q]怎麼樣擷取對象的DDL語句[A]第三方工具就不說了主要說一下9i以上版本的dbms_metadata1、獲得單個對象的DDL語句set heading offset echo offset feedback offset pages offset long 90000select dbms_metadata.get_ddl('TABLE','TABLENAME','SCAME') from dual;如果擷取整個使用者的指令碼，可以用如下語句select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;當然，如果是索引，則需要修改相關table到index[Q]如何建立約束的索引在別的資料表空間上[A]1、先建立索引，再建立約束2、利用如下語句建立create table test(c1 number constraint pk_c1_id primary keyusing index tablespace useridex,c2 varchar2(10)) tablespace userdate;[Q]怎麼知道那些表沒有建立主鍵[A]一般的情況下，表的主鍵是必要的，沒有主鍵的表可以說是不符合設計規範的。SELECT table_nameFROM User_tables tWHERE NOT EXISTS(SELECT table_nameFROM User_constraints cWHERE constraint_type = 'P'AND t.table_name=c.table_name)其它相關資料字典解釋user_tables        表user_tab_columns   表的列user_constraints    約束user_cons_columns  約束與列的關係user_indexes       索引user_ind_columns   索引與列的關係[Q]dbms_output提示緩衝區不夠，怎麼增加[A]dbms_output.enable(20000);另外，如果dbms_output的資訊不能顯示，需要設定set serveroutput on[Q]怎麼樣修改表的列名[A]9i以上版本可以採用rname命令ALTER TABLE UserName.TabNameRENAME COLUMN SourceColumn TO DestColumn9i以下版本可以採用create table …… as select * from SourceTable的方式。另外，8i以上可以支援刪除列了ALTER TABLE UserName.TabNameSET UNUSED (ColumnName) CASCADE CONSTRAINTSALTER TABLE UserName.TabNameDROP (ColumnName) CASCADE CONSTRAINTS[Q]怎麼樣給sqlplus安裝協助[A]SQLPLUS的協助必須手工安裝，shell指令碼為$ORACLE_HOME/bin/helpins在安裝之前，必須先設定SYSTEM_PASS環境變數，如：$ setenv SYSTEM_PASS SYSTEM/MANAGER$ helpins如果不設定該環境變數，將在運行指令碼的時候提示輸入環境變數當然，除了shell指令碼，還可以利用sql指令碼安裝，那就不用設定環境變數了，但是，我們必須以system登入。$ sqlplus system/managerSQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql安裝之後，你就可以象如下的方法使用協助了SQL> help index[Q]怎麼樣快速下載Oracle補丁[A]我們先獲得下載伺服器位址，在http頁面上有ftp://updates.oracle.com然後用ftp登入，使用者名稱與密碼是metalink的使用者名稱與密碼如我們知道了補丁號3095277 (9204的補丁集)，則ftp> cd 3095277250 Changed directory OK.ftp> ls200 PORT command OK.150 Opening data connection for file listing.p3095277_9204_AIX64-5L.zipp3095277_9204_AIX64.zip……p3095277_9204_WINNT.zip226 Listing complete. Data connection has been closed.ftp: 208 bytes received in 0.02Seconds 13.00Kbytes/sec.ftp>知道了這個資訊，我們用用flashget，網路螞蟻就可以下載了。添加如下串連ftp://updates.oracle.com/3095277/p3095244_9204_AIX64-5L.zip或替換後面的部分為所需要的內容注意，如果是flashget，網路螞蟻請輸入認證使用者名稱及密碼，就是你的metalink的使用者名稱與密碼！[Q]如何移動資料檔案[A]1、關閉資料庫，利用os拷貝a.shutdown immediate關閉資料庫b.在os下拷貝資料檔案到新的地點c.Startup mount 啟動到mount下d.Alter database rename datafile '老檔案' to '新檔案';e.Alter database open; 開啟資料庫2、利用Rman聯機操作RMAN> sql "alter database datafile ''file name'' offline";RMAN> run {2> copy datafile 'old file location'3> to 'new file location';4> switch datafile ' old file location'5> to datafilecopy ' new file location';6> }RMAN> sql "alter database datafile ''file name'' online";說明：利用OS拷貝也可以聯機操作，不關閉資料庫，與rman的步驟一樣，利用rman與利用os拷貝的原理一樣，在rman中copy是拷貝資料檔案，相當於OS的cp，而switch則相當於alter database rename，用來更新控制檔案。[Q]如果管理聯機日誌組與成員[A]以下是常見操作，如果在OPA/RAC下注意線程號增加一個記錄檔組Alter database add logfile [group n] '檔案全名' size 10M;在這個組上增加一個成員Alter database add logfile member '檔案全名' to group n;在這個組上刪除一個日誌成員Alter database drop logfile member '檔案全名';刪除整個日誌組Alter database drop logfile group n;[Q]怎麼樣計算REDO BLOCK的大小[A]計算方法為(redo size + redo wastage) / redo blocks written + 16具體見如下例子SQL> select name ,value from v$sysstat where name like '%redo%';NAME VALUE---------------------------------------------------------------- ----------redo synch writes 2redo synch time 0redo entries 76redo size 19412redo buffer allocation retries 0redo wastage 5884redo writer latching time 0redo writes 22redo blocks written 51redo write time 0redo log space requests 0redo log space wait time 0redo log switch interrupts 0redo ordering marks 0SQL> select (19412+5884)/51 + 16 '"Redo black(byte)" from dual;Redo black(byte)------------------512[Q]控制檔案包含哪些基本內容[A]控制檔案主要包含如下條目，可以通過dump控制檔案內容看到DATABASE ENTRYCHECKPOINT PROGRESS RECORDSREDO THREAD RECORDSLOG FILE RECORDSDATA FILE RECORDSTEMP FILE RECORDSTABLESPACE RECORDSLOG FILE HISTORY RECORDSOFFLINE RANGE RECORDSARCHIVED LOG RECORDSBACKUP SET RECORDSBACKUP PIECE RECORDSBACKUP DATAFILE RECORDSBACKUP LOG RECORDSDATAFILE COPY RECORDSBACKUP DATAFILE CORRUPTION RECORDSDATAFILE COPY CORRUPTION RECORDSDELETION RECORDSPROXY COPY RECORDSINCARNATION RECORDS[Q]如果發現表中有壞塊，如何檢索其它未壞的資料[A]首先需要找到壞塊的ID（可以運行dbverify實現），假設為<BID>，假定檔案編碼為<FID>。運行下面的查詢尋找段名：SELECT segment_name,segment_type,extent_id,block_id, blocksfrom dba_extents twherefile_id = <FID>AND <BID> between block_id and (block_id + blocks - 1)一旦找到壞段名稱，若段是一個表，則最好建立一個暫存資料表，存放好的資料。若段是索引，則刪除它，再重建。create table good_tableasselect from bad_table where rowid not in(select rowidfrom bad_table where substr(rowid,10,6) = <BID>在這裡要注意8以前的受限ROWID與現在ROWID的差別。還可以使用診斷事件10231SQL> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';建立一個暫存資料表good_table的表中除壞塊的資料都檢索出來SQL>CREATE TABLE good_table as select * from bad_table;最後關閉診斷事件SQL> ALTER SYSTEM SET EVENTS '10231 trace name context off ';關於ROWID的結構，還可以參考dbms_rowid.rowid_create函數。[Q]我建立了資料庫的所有使用者，我可以刪除這些使用者嗎[A]ORACLE資料庫建立的時候，建立了一系列預設的使用者和資料表空間，以下是他們的列表·SYS/CHANGE_ON_INSTALL or INTERNAL系統使用者，資料字典所有者，超級許可權所有者(SYSDBA)建立指令碼：?/rdbms/admin/sql.bsq and various cat*.sql建議建立後立即修改密碼此使用者不能被刪除·SYSTEM/MANAGER資料庫預設系統管理使用者，擁有DBA角色許可權建立指令碼：?/rdbms/admin/sql.bsq建議建立後立即修改密碼此使用者不能被刪除·OUTLN/OUTLN最佳化計劃的儲存大綱使用者建立指令碼：?/rdbms/admin/sql.bsq建議建立後立即修改密碼此使用者不能被刪除---------------------------------------------------·SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.實驗、測試使用者，含有例表EMP與DEPT建立指令碼：?/rdbms/admin/utlsampl.sql可以修改密碼使用者可以被刪除，在產品環境建議刪除或鎖定·HR/HR (Human Resources), OE/OE (Order Entry), SH/SH (Sales History).實驗、測試使用者，含有例表EMPLOYEES與DEPARTMENTS建立指令碼：?/demo/schema/mksample.sql可以修改密碼使用者可以被刪除，在產品環境建議刪除或鎖定·DBSNMP/DBSNMPOracle Intelligent agent建立指令碼：?/rdbms/admin/catsnmp.sql, called from catalog.sql可以改變密碼--需要放置新密碼到snmp_rw.ora檔案如果不需要Intelligent Agents，可以刪除---------------------------------------------------以下使用者都是可選安裝使用者,如果不需要，就不需要安裝·CTXSYS/CTXSYSOracle interMedia (ConText Cartridge)系統管理使用者建立指令碼：?/ctx/admin/dr0csys.sql·TRACESVR/TRACEOracle Trace server建立指令碼：?/rdbms/admin/otrcsvr.sql·ORDPLUGINS/ORDPLUGINSObject Relational Data (ORD) User used by Time Series, etc.建立指令碼：?/ord/admin/ordinst.sql·ORDSYS/ORDSYSObject Relational Data (ORD) User used by Time Series, etc建立指令碼：?/ord/admin/ordinst.sql·DSSYS/DSSYSOracle Dynamic Services and Syndication Server建立指令碼：?/ds/sql/dssys_init.sql·MDSYS/MDSYSOracle Spatial administrator user建立指令碼：?/ord/admin/ordinst.sql·AURORA$ORB$UNAUTHENTICATED/INVALIDUsed for users who do not authenticate in Aurora/ORB建立指令碼：?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql·PERFSTAT/PERFSTATOracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT建立指令碼：?/rdbms/admin/statscre.sql