ORACLE之常用FAQ:ORACLE構架體系

第二部分、ORACLE構架體系

[@more@]

[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.13
NUMBER可變長度數值列，允許值為0、正數和負數。NUMBER值通常以4個位元組或更少的位元組儲存，最多21位元組
LONG可變長度字元域，最大長度可到2GB
RAW表示二進位制資料的可變長度字元域，最長為2000個位元組
LONGRAW表示二進位制資料的可變長度字元域，最長為2GB
MLSLABEL只用於TrustedOracle，這個資料型別每行使用2至5個位元組
BLOB二進位制大物件，最大長度為4GB
CLOB字元大物件，最大長度為4GB
NCLOB多位元組字符集的CLOB資料型別，最大長度為4GB
BFILE外部二進位制檔案，大小由作業系統決定
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
,description
FROM ( --GV$SYSTEM_PARAMETER
SELECT 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 DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND 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=true
Query_rewrite_integrity=trusted
Compatible=8.1.0以上
Create index indexname on table (function(field));

[Q]怎麼樣移動表或表分割槽
[A]移動表的語法
Alter table tablename move
[Tablespace new_name
Storage(initial 50M next 50M
pctincrease 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 move
lob(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組成
OOOOOOFFFBBBBBBRRR
8以下ROWID組成（也叫受限Rowid）
BBBBBBBB.RRRR.FFFF
其中，O是物件ID，F是檔案ID，B是塊ID，R是行ID
如果我們查詢一個表的ROWID，根據其中塊的資訊，可以知道該表確切佔用了多少個塊，進而知道佔用了多少資料空間（此資料空間不等於表的分配空間）

[Q]怎麼樣獲取物件的DDL語句
[A]第三方工具就不說了主要說一下9i以上版本的dbms_metadata
1、獲得單個物件的DDL語句
set heading off
set echo off
set feedback off
set pages off
set long 90000
select 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 key
using index tablespace useridex,
c2 varchar2(10)
) tablespace userdate;

[Q]怎麼知道那些表沒有建立主鍵
[A]一般的情況下，表的主鍵是必要的，沒有主鍵的表可以說是不符合設計規範的。
SELECT table_name
FROM User_tables t
WHERE NOT EXISTS
(SELECT table_name
FROM User_constraints c
WHERE 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.TabName
RENAME COLUMN SourceColumn TO DestColumn
9i以下版本可以採用create table …… as select * from SourceTable的方式。
另外，8i以上可以支援刪除列了
ALTER TABLE UserName.TabName
SET UNUSED (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE UserName.TabName
DROP (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/manager
SQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql
安裝之後，你就可以象如下的方法使用幫助了
SQL> help index

[Q]怎麼樣快速下載Oracle補丁
[A]我們先獲得下載伺服器地址，在http頁面上有
然後用ftp登入，使用者名稱與密碼是metalink的使用者名稱與密碼
如我們知道了補丁號3095277 (9204的補丁集)，則
ftp> cd 3095277
250 Changed directory OK.
ftp> ls
200 PORT command OK.
150 Opening data connection for file listing.
p3095277_9204_AIX64-5L.zip
p3095277_9204_AIX64.zip
……
p3095277_9204_WINNT.zip
226 Listing complete. Data connection has been closed.
ftp: 208 bytes received in 0.02Seconds 13.00Kbytes/sec.
ftp>
知道了這個資訊，我們用用flashget，網路螞蟻就可以下載了。
新增如下連線
或替換後面的部分為所需要的內容
注意，如果是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 2
redo synch time 0
redo entries 76
redo size 19412
redo buffer allocation retries 0
redo wastage 5884
redo writer latching time 0
redo writes 22
redo blocks written 51
redo write time 0
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0
SQL> select (19412+5884)/51 + 16 '"Redo black(byte)" from dual;
Redo black(byte)
------------------
512

[Q]控制檔案包含哪些基本內容
[A]控制檔案主要包含如下條目，可以透過dump控制檔案內容看到
DATABASE ENTRY
CHECKPOINT PROGRESS RECORDS
REDO THREAD RECORDS
LOG FILE RECORDS
DATA FILE RECORDS
TEMP FILE RECORDS
TABLESPACE RECORDS
LOG FILE HISTORY RECORDS
OFFLINE RANGE RECORDS
ARCHIVED LOG RECORDS
BACKUP SET RECORDS
BACKUP PIECE RECORDS
BACKUP DATAFILE RECORDS
BACKUP LOG RECORDS
DATAFILE COPY RECORDS
BACKUP DATAFILE CORRUPTION RECORDS
DATAFILE COPY CORRUPTION RECORDS
DELETION RECORDS
PROXY COPY RECORDS
INCARNATION RECORDS

[Q]如果發現表中有壞塊，如何檢索其它未壞的資料
[A]首先需要找到壞塊的ID（可以執行dbverify實現），假設為，假定檔案編碼為。執行下面的查詢查詢段名：
SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where
file_id =
AND between block_id and (block_id + blocks - 1)
一旦找到壞段名稱，若段是一個表，則最好建立一個臨時表，存放好的資料。若段是索引，則刪除它，再重建。
create table good_table
as
select from bad_table where rowid not in
(select rowid
from bad_table where substr(rowid,10,6) = )
在這裡要注意8以前的受限ROWID與現在ROWID的差別。
還可以使用診斷事件10231
SQL> 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/DBSNMP
Oracle Intelligent agent
建立指令碼：?/rdbms/admin/catsnmp.sql, called from catalog.sql
可以改變密碼--需要放置新密碼到snmp_rw.ora檔案
如果不需要Intelligent Agents，可以刪除
---------------------------------------------------
以下使用者都是可選安裝使用者,如果不需要，就不需要安裝
·CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge)管理使用者
建立指令碼：?/ctx/admin/dr0csys.sql
·TRACESVR/TRACE
Oracle Trace server
建立指令碼：?/rdbms/admin/otrcsvr.sql
·ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series, etc.
建立指令碼：?/ord/admin/ordinst.sql
·ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series, etc
建立指令碼：?/ord/admin/ordinst.sql
·DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
建立指令碼：?/ds/sql/dssys_init.sql
·MDSYS/MDSYS
Oracle Spatial administrator user
建立指令碼：?/ord/admin/ordinst.sql
·AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
建立指令碼：?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql
·PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
建立指令碼：?/rdbms/admin/statscre.sql