以前搜集的一個(gè)Oracle比較常見(jiàn)問(wèn)題的列表,忘記了是從哪來(lái)的

關(guān)于 SELECT N 問(wèn)題

有感于一些網(wǎng)友多次咨詢和討論選取某些指定行數(shù)據(jù)的問(wèn)題, 我寫了下面這樣的簡(jiǎn)單說(shuō)明, 請(qǐng)大家指正.

這里描述的 SELECT N 包括這樣幾種情況:
1. 選取TOP N行記錄
2. 選取N1-N2行記錄
3. 選取FOOT N行記錄
當(dāng)然需要考慮是否有ORDER BY子句的情況, 下面試以系統(tǒng)視圖CAT為例分別說(shuō)明.
注: A. 為沒(méi)有ORDER BY的情況
B. 有ORDER BY的情況

1. 選取 TOP N 行記錄
A. SELECT * FROM CAT WHERE ROWNUM<=N
B. SELECT * FROM
( SELECT * FROM CAT ORDER BY TABLE_TYPE )
WHERE ROWNUM<=N

2. 選取N1-N2行記錄
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ BETWEEN N1 AND N2;
或:
SELECT * FROM CAT WHERE ROWNUM<=N2
MINUS
SELECT * FROM CAT WHERE ROWNUM<N1
B. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,X.* FROM (SELECT * FROM CAT ORDER BY TABLE_TYPE) X)
WHERE ROWSEQ BETWEEN N1+1 AND N2;

3. 選取FOOT N行記錄
這里是說(shuō)明不知道記錄集的記錄個(gè)數(shù)的情況, 如果已知, 用上面2的方法即可
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
B. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE)
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
或
SELECT * FROM
( SELECT TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE DESC)
WHERE ROWNUM<=N

以上在ORACLE8.1.5 for Windows2000pro 上測(cè)試通過(guò)
-- end --

oracle FAQ(1) from chao_ping
1.快速整理破碎的表(在Oracle8i里邊才可以這樣使用)

ALTER TABLE table_name MOVE ( TABLESPACE XXX);

如何移動(dòng)一張表所在的表空間

方法一：
1． Export 這張表
2． Drop這張表
3． Create table xxx tablespace xxx;
4． Imp Ignore=y

還要注意的一點(diǎn)是，所有要讀取這張表的PL/SQL儲(chǔ)存過(guò)程都會(huì)失效。需要重新編譯。
1． SELECT * FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
2． 對(duì)這些包，函數(shù)，過(guò)程重新編譯。
方法二：
僅對(duì)Oracle8i適用。
使用下面的語(yǔ)句：
ALTER TABLE table_name MOVE
TABLESPACE new_tablespace;

這樣的話，所有的約束、索引、觸發(fā)器都不會(huì)受到影響。

但是需要rebuild這個(gè)標(biāo)上的所有索引。

2.怎樣直接進(jìn)入sql*plus而不用輸入用戶名，密碼：

sqlplus /nolog;

sqlplus username/password@connect_string

3.怎樣快速重建索引：

alter index xxx rebuild storage();

alter index xxx coalesce;

4. 為什么我看不到dbms_output的結(jié)果？

SET SERVEROUTPUT ON

5． 進(jìn)行一次大的事務(wù)以后，已經(jīng)COMMIT了，但為什么我的回滾段還是那樣大？

因?yàn)闆](méi)有設(shè)置OPTIMAL的值，所以不會(huì)自動(dòng)收縮。

可以用alter rollback segment shrink to Xm;來(lái)手工進(jìn)行收縮。

6． 為什么要使用VARCHAR2，而不用CHAR?

A．CHAR只支持2000字節(jié)長(zhǎng)，而VARCHAR2支持4000字節(jié)的長(zhǎng)度，適用性更好

B． CHAR 占用更多的存儲(chǔ)空間，定義多長(zhǎng)，它就占用多長(zhǎng)的空間，插入字符后面自動(dòng)加空格填充；而VARCHAR2不論定義多長(zhǎng)，都只使用實(shí)際插入的長(zhǎng)度。

7． 為什么從不同的數(shù)據(jù)字典看，表/索引所占用的空間不一樣？

SQL> select blocks , empty_blocks from dba_tables where table_name=’表名';
BLOCKS EMPTY_BLOCKS
---------- ------------
1575 1524

SQL> select bytes,blocks,extents from dba_segments where segment_name='表名';
BYTES BLOCKS EXTENTS
---------- - --------- ----------

6348800 3100 1

這是因?yàn)榈谝粋�(gè)數(shù)據(jù)庫(kù)視圖DBA_TABLES的BLOCKS列是指實(shí)際上使用的BLOCK數(shù)目，還有一些BLOCK雖然被占用了，但是沒(méi)有數(shù)據(jù)存在，不計(jì)入里邊。而在DBA_SEGMENTS這個(gè)數(shù)據(jù)庫(kù)視圖里邊，BLOCKS列是指這個(gè)表總共占用的BLOCK的數(shù)目，包括有數(shù)據(jù)和沒(méi)有數(shù)據(jù)的BLOCK總量。如果把第一個(gè)視圖里邊的BLOCKS和EMPTY_BLOCKS地總和加起來(lái)，正好等于第二個(gè)視圖的BLOCKS列的大小。

8． 怎樣把數(shù)據(jù)庫(kù)的一張，多張表存為一個(gè)普通的文本文件？

可以在SQL*Plus里邊用SPOOL命令把選出來(lái)的數(shù)據(jù)保存在SPOOL指定的文件里邊。

9． 怎樣從一張表里刪除重復(fù)的記錄

SQL> SELECT * FROM EMP;

EMP_ID OFFICE_ID EMPNAME

305 12 ELLISON, GEORGE

305 12 MERCURIO, JASON

128 17 SIMPSON, LINDA

305 22 JACKSON, DREW

使用下面的SQL語(yǔ)句來(lái)識(shí)別那些重復(fù)的記錄：

SQL> SELECT COUNT(*), EMP_ID, OFFICE_ID

FROM EMP

GROUP BY EMP_ID, OFFICE_ID

HAVING COUNT(*) > 1;

結(jié)果如下：

COUNT(*) EMP_ID OFFICE_ID

2 305 12

Table Example, with duplicate values:

SQL> SELECT * FROM EMP;

EMP_ID OFFICE_ID EMPNAME

305 12 ELLISON, GEORGE

305 12 MERCURIO, JASON

128 17 SIMPSON, LINDA

305 22 JACKSON, DREW

使用下面的語(yǔ)句來(lái)刪除重復(fù)的記錄：

SQL> DELETE FROM EMP A WHERE

(EMP_ID, OFFICE_ID, 2) IN

(SELECT EMP_ID, OFFICE_ID, decode(count(*),1,1,2)

FROM EMP B

WHERE A.EMP_ID=B.EMP_ID AND

A.OFFICE_ID = B.OFFICE_ID

GROUP BY EMP_ID, OFFICE_ID);

10． 怎樣在SQL*PLUS里想數(shù)據(jù)庫(kù)插入特殊字符？

可以使用CHR函數(shù)。

11． 怎樣刪除一個(gè)列？

在Oracle8i里邊，可以直接Drop一個(gè)列。語(yǔ)法為alter table table_name drop column_name;

但是注意要在initsid.ora里邊設(shè)定compatible=8.1.0以上。

12． 怎樣重命名一個(gè)列？

1 alter table "table_name" add
(new_column_name data_type);

2 update table_name set new_column_name =
old_column_name where rowid=rowid;

3 alter table table_name drop column
old_column_name;

13． 怎樣快速清空一張表？

Truncate table table_name;

14． 怎樣為事務(wù)指定一個(gè)大的回滾段？

Set transaction use rollback segment rbs_name;

15． 怎樣知道一張表上有那些權(quán)限賦予了哪些人，給他們了什么權(quán)限？

select * from dba_tab_privs where table_name='表名';

16． 怎么發(fā)現(xiàn)是誰(shuí)鎖住了你需要的一張表？

Select object_id from v$locked_object;

Select object_name, object_type from dba_objects where object_id=’’;

每次清空一張表的時(shí)候，（使用truncate），這張表的存儲(chǔ)參數(shù)NEXT自動(dòng)復(fù)位到最后被刪除的那個(gè)extent的大小。同樣，如果顯式地從一張表里邊釋放空間，NEXT參數(shù)也會(huì)自動(dòng)被設(shè)置成最后被釋放的那個(gè)extent的大小。

在SQL*Plus里邊可以為一個(gè)事務(wù)指定一個(gè)回滾段：這在有大的事務(wù)將要發(fā)生的話時(shí)候還是很有用的。使用下面的語(yǔ)句可以為這個(gè)事務(wù)指定一個(gè)回滾段：

SQL>SET TRANSACTION USE ROLLABCK SEGMENT 回滾段名稱；

還可以在PL/SQL里邊為一個(gè)事務(wù)指定一個(gè)回滾段（不使用動(dòng)態(tài)sql語(yǔ)句）。這個(gè)需要使用Oracle提供的包：DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT(‘回滾段名稱’);

在有些平臺(tái)上的Oracle，在啟動(dòng)的時(shí)候會(huì)自動(dòng)生成一個(gè)sgadefSID.dbf，用這個(gè)文件是否存在就可以判斷一個(gè)實(shí)例是否在運(yùn)行。這個(gè)文件包含了SGA在內(nèi)存中的地址。在數(shù)據(jù)庫(kù)關(guān)閉的時(shí)候，Oracle會(huì)自動(dòng)刪除這個(gè)文件。但是在Oracle8i里邊，這個(gè)文件不再存在了。需要使用新的判斷方式來(lái)斷定究竟某個(gè)實(shí)例是否在運(yùn)行。比如PS命令。

在Oracle7里邊，想要知道數(shù)據(jù)文件是否可以自動(dòng)擴(kuò)展，必須從sys.filext$這張表里邊查取，但是在Oracle8里邊，從dba_data_files里邊就可以知道數(shù)據(jù)文件是否可以自動(dòng)擴(kuò)展了。

從Oracle8i開(kāi)始，可以創(chuàng)建另一類數(shù)據(jù)庫(kù)一級(jí)的觸發(fā)器，比如數(shù)據(jù)庫(kù)啟動(dòng)、關(guān)閉，用戶登錄、注銷等事務(wù)，都可以觸發(fā)這個(gè)事件的發(fā)生，從而作某些記錄。在數(shù)據(jù)庫(kù)一級(jí)定義的觸發(fā)器會(huì)在所有用戶相應(yīng)事件發(fā)生的時(shí)候觸發(fā)，而在Schema一級(jí)定義的觸發(fā)器只有在某個(gè)特定用戶的相應(yīng)事件發(fā)生的時(shí)候才會(huì)觸發(fā)。

從Oracle8i開(kāi)始，多了一種關(guān)閉數(shù)據(jù)庫(kù)的方式：SHUTDOWN TRANSACTIONAL。這種方式允許所有的用戶提交它們的工作。但是一旦提交之后就馬上被切斷聯(lián)接，等所有用戶都完成了各自的事務(wù)，shutdown就開(kāi)始了。

從Oracle8開(kāi)始，可以創(chuàng)建臨時(shí)表，這些表的定義對(duì)于所有該用戶的會(huì)話都是可以看到的，但是每個(gè)會(huì)話查詢、插入、刪除的數(shù)據(jù)和別的會(huì)話查詢、插入、刪除的數(shù)據(jù)都是不相關(guān)的。就像每個(gè)會(huì)話都分別有這樣一份表一樣。

從Oracle8i開(kāi)始，對(duì)于那些沒(méi)有進(jìn)行分區(qū)的表，可以不用IMP/EXP就可以快速重組。但是這需要兩倍于該表容量的表空間。這個(gè)語(yǔ)句就是：

ALTER TABLE TB_NAME MOVE TABLESPACE TS_NAME;

在Oracle8i里邊可以創(chuàng)建反序索引。（CREATE INDEX i ON t (a,b,c) REVERSE;）。由于反序索引的相鄰鍵值不是存放在物理相鄰的位置，因此只有全索引掃描或者通過(guò)單個(gè)列這一類語(yǔ)句才能夠有效利用這些索引。這類反序索引在Oracle并行服務(wù)器上能夠較好地協(xié)調(diào)不同實(shí)例對(duì)數(shù)據(jù)庫(kù)的修改，可以在一定程度上提高系統(tǒng)性能。

從Oracle8開(kāi)始，$instance視圖可以查獲許多有用的信息：比如主機(jī)名稱，實(shí)例名，啟動(dòng)時(shí)間，版本號(hào)等。

臨時(shí)表空間里邊創(chuàng)建的臨時(shí)段只有在shutdown地時(shí)候才會(huì)被釋放。

但是在permanent表空間里邊創(chuàng)建的臨時(shí)段在一個(gè)事務(wù)結(jié)束之后就會(huì)被釋放，有Smon進(jìn)程來(lái)完成這個(gè)任務(wù)。







oracle FAQ(2) from chao_ping
關(guān)于OPTIMAL參數(shù)

optimal是用于限制回滾段大小的一個(gè)存儲(chǔ)參數(shù)。在執(zhí)行一個(gè)長(zhǎng)的事務(wù)之后，那個(gè)事務(wù)所使用的回滾段會(huì)比較大，而設(shè)置了Optimal這個(gè)參數(shù)以后，一旦事務(wù)提交結(jié)束，回滾段自動(dòng)收縮到Optimal所指定的大小。

如果你的系統(tǒng)中有許多長(zhǎng)時(shí)間運(yùn)行的事務(wù)的話，那么應(yīng)該把回滾段的Optimal參數(shù)設(shè)置的比較大一點(diǎn)。這樣有利于保持回滾段表空間的連續(xù)性。否則不斷的擴(kuò)張、收縮會(huì)使表空間更加破碎。

如果系統(tǒng)中主要的事務(wù)都是短時(shí)間的，那么應(yīng)該把回滾段設(shè)置的比較小一些，這樣有利于讓回滾段里面的信息可以存儲(chǔ)在SGA里邊，以利于提高系統(tǒng)性能。

回滾段的Optimal參數(shù)可以在創(chuàng)建回滾段的時(shí)候指定，也可以用

ALTER ROLLBACK SEGMENT SEGMENT_NAME OPTIMAL XX M;這樣來(lái)重新設(shè)定。

Oracle8i 里邊的

ALTER SESSION SET CURRENT_SCHEMA=

可以用來(lái)更改當(dāng)前的用戶模式。


Oracle公司已經(jīng)宣稱，不再支持server manager,這個(gè)工具自從Oracle 6.0開(kāi)始，就一直是管理Oracle數(shù)據(jù)庫(kù)的主要工具�，F(xiàn)在，SQL*Plus替代了Server Manager的地位，因此，Server Manager中相應(yīng)的功能也都集成到了SQL*Plus之中。

SQL*Plus新增加的主要命令是startup, shutdown, archive log,和recover。當(dāng)然，標(biāo)準(zhǔn)的SQL語(yǔ)法仍然是支持的了，比如一系列的CREATE, ALTER等語(yǔ)句。但是也對(duì)其中的一些有了一些改變，比如原來(lái)不支持的ALTER DATABASE OPEN，ALTER DATABASE MOUNT,ALTER DATABASE BACKUP 等句子。

對(duì)于SET命令，也多了一些新的選項(xiàng)，用來(lái)包含一些如自動(dòng)恢復(fù)等。SHOW命令也開(kāi)始可以用來(lái)直接顯示參數(shù)SHOW PARAMETER ,SHOW SGA等。而這些，原來(lái)都只是在Server Manager里面才有的功能。



Oracle8i仍舊保留了盡人皆知的INTERNAL賬戶，但是要記住，這主要是為了向后兼容。INTERNAL賬戶的功能現(xiàn)在開(kāi)始有SYSDBA,SYSOPER這兩個(gè)角色來(lái)支持了。INTERNAL/SYS的口令可以用下面這個(gè)方法來(lái)進(jìn)行修改：

O/S Prompt> orapwd password=<some password>

當(dāng)數(shù)據(jù)庫(kù)剛剛創(chuàng)建的時(shí)候，SYS的密碼默認(rèn)為change_on_install，而SYSTEM的密碼是manager，而INTERNAL則根本就沒(méi)有密碼。因此，創(chuàng)建完數(shù)據(jù)庫(kù)之后，第一件要做的事情就是改變以上三個(gè)用戶的口令。INTERNAL的口令可以用前面提到過(guò)的方法來(lái)改變，而SYSTEM，SYS則可以直接用ALTER USER username IDENTIFIED BY password；來(lái)更改。注意，在Oracle8i開(kāi)始，ALTER USER SYS IDENTIFIED BY password；同時(shí)也會(huì)更改INTERNAL的密碼，如果你為INTERNAL設(shè)置了密碼的話，同時(shí)，馬上把SYSDBA,SYSOPER這兩個(gè)角色授予負(fù)責(zé)管理這個(gè)數(shù)據(jù)庫(kù)的用戶。對(duì)于那些使用INTERNAL來(lái)連接數(shù)據(jù)庫(kù)的腳本，也要相應(yīng)的作一些修改。

啟動(dòng)和關(guān)閉數(shù)據(jù)庫(kù)：

要從SQL*Plus里邊啟動(dòng)數(shù)據(jù)庫(kù)，請(qǐng)按照以下的步驟進(jìn)行：

O/S Prompt>sqlplus /nolog

SQL> connect scott/tiger as SYSDBA

SQL> startup

如果希望用不同于默認(rèn)得參數(shù)來(lái)啟動(dòng)數(shù)據(jù)庫(kù)，可以使用下面代參數(shù)的啟動(dòng)命令：

SQL> startup PFILE=<init.ora file to be used>

有時(shí)候需要啟動(dòng)數(shù)據(jù)庫(kù)，但是不讓普通用戶進(jìn)入，比如為了平衡IO，需要移動(dòng)一個(gè)數(shù)據(jù)文件的位置，這時(shí)候就需要改變默認(rèn)得啟動(dòng)選項(xiàng)：

SQL> startup mount

當(dāng)完成了維護(hù)任務(wù)之后，可以選擇關(guān)閉數(shù)據(jù)庫(kù)然后再重新按照正常方式打開(kāi)，或者直接在SQL*Plus里邊輸入下面的命令，Oracle就可以開(kāi)始正常運(yùn)行了：

SQL> alter database open

有時(shí)候需要?jiǎng)?chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)，或者需要重建控制文件，就需要用下面的語(yǔ)句：

SQL> startup nomount

有時(shí)候，數(shù)據(jù)庫(kù)難以正常啟動(dòng)，就可以考慮使用下面的辦法來(lái)強(qiáng)迫啟動(dòng)：使用FORCE選項(xiàng)，STARTUP FORCE實(shí)際上相當(dāng)于一個(gè)SHUTDOWN ABORT然后再STARTUP這樣一個(gè)過(guò)程。

SQL> startup force

SHUTDOWN這個(gè)命令也有好幾種參數(shù)可以選擇：

正常關(guān)閉是等待所有用戶都從系統(tǒng)退出以后，再正常關(guān)閉系統(tǒng)。這是一種最最理想的關(guān)閉數(shù)據(jù)庫(kù)的方式。一般都應(yīng)該使用這種方式來(lái)正常關(guān)閉數(shù)據(jù)庫(kù)。

SQL> shutdown

在Oracle8i開(kāi)始，新加了一個(gè)關(guān)閉選項(xiàng)：SHUTDOWN TRANSACTIONAL。這允許所有用戶都完成它們的事務(wù)，一旦事務(wù)提交，馬上被斷開(kāi)連接。這樣既保證了用戶不會(huì)丟失它們的事務(wù)，也保證了數(shù)據(jù)庫(kù)可以及時(shí)關(guān)閉，進(jìn)行必要的維護(hù)操作。這種方式關(guān)閉的話，下次系統(tǒng)啟動(dòng)之后，也不用進(jìn)行實(shí)例一級(jí)的恢復(fù)。比下面提到的另外兩種方式都要理想。

SQL> shutdown transactional

SHUTDOWN IMMEDIATE是馬上中止用戶的當(dāng)前事務(wù)，并不等這些事務(wù)完成，回滾這些用戶的當(dāng)前事務(wù)。但是如果有一些事務(wù)很久沒(méi)有提交的話，那么SHUTDOWN IMMEDIATE或許就不像說(shuō)得那樣IMMEDIATE了。可能也要花很多時(shí)間來(lái)回滾這些事務(wù)。

SQL> shutdown immediate

在Oracle8i里邊最后一種關(guān)閉方式是SHUTDOWN ABORT。這種關(guān)閉方式和直接關(guān)閉計(jì)算機(jī)的電源其實(shí)沒(méi)有太多的區(qū)別。任何當(dāng)前連接的用戶都被馬上斷開(kāi)聯(lián)接，在下次實(shí)例再次啟動(dòng)的時(shí)候，必須進(jìn)行實(shí)例一級(jí)的恢復(fù)，用以回滾沒(méi)有提交的事務(wù)。

SQL> shutdown abort

ALTER TABLE table_name MOVE之后，表上的索引標(biāo)志為UNUSABLE？

在Oracle8i開(kāi)始，可以直接使用alter table table_name move [tablespace tablespace_name];

來(lái)為一張表移動(dòng)到另一個(gè)表空間，或者重新組織表的存儲(chǔ)方式，以減少碎片。但是，這樣使用過(guò)之后，所有這張表上的索引都將被標(biāo)志為unusable。這是因?yàn)镸OVE一張表之后，表中列對(duì)應(yīng)的物理位置都改變了，就是所有行的ROWID都變化了，而這張表的索引就用到了其中行的ROWID。由于Oracle不會(huì)自動(dòng)更新索引對(duì)應(yīng)的ROWID，這時(shí)候，索引上的ROWID就指向了錯(cuò)誤的地方。因此，索引被標(biāo)志為UNUSABLE。這時(shí)候，你就需要手工重建索引�？梢允褂孟旅娴恼Z(yǔ)法來(lái)重建索引：

ALTER INDEX index_name REBUILD;當(dāng)然，還可以為索引指定特定的合適的存儲(chǔ)參數(shù)，來(lái)優(yōu)化索引的存儲(chǔ)�；蛟SOracle之所以沒(méi)有自動(dòng)維護(hù)索引，就是為了讓你可以為索引指定合適的存儲(chǔ)參數(shù)。

如何遠(yuǎn)程安裝Oracle：

如果需要從PC機(jī)上的X－window客戶端安裝Unix上的Oracle系統(tǒng)，要注意下面這一點(diǎn)：Oracle8i使用的是Universal Installer，使用了Java技術(shù)，必須在圖形界面下安裝。如果是遠(yuǎn)程安裝，必須設(shè)置一下什么地方來(lái)顯示Universal Installer的圖形界面：使用

$DISPLAY=workstation_name:0.0

$export DISPLAY

舉一個(gè)例子，你的PC機(jī)的IP地址是150.150.4.128，機(jī)器名字叫做test，那么就可以使用下面的語(yǔ)法來(lái)進(jìn)行為安裝作準(zhǔn)備工作：

$DSIPLAY=150.150.4.128:0.0

$export DISPLAY

或者使用下面的語(yǔ)法，但是必須這個(gè)test機(jī)器的信息寫在hosts文件里邊：

DSIPLAY=test:0.0

$export DISPLAY