以前搜集的一個Oracle比較常見問題的列表,忘記了是從哪來的
關於 SELECT N 問題
有感於一些網友多次諮詢和討論選取某些指定行資料的問題, 我寫了下面這樣的簡單說明, 請大家指正.
這裡描述的 SELECT N 包括這樣幾種情況:
1. 選取TOP N行記錄
2. 選取N1-N2行記錄
3. 選取FOOT N行記錄
當然需要考慮是否有ORDER BY子句的情況, 下面試以系統檢視表CAT為例分別說明.
注: A. 為沒有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 ROWNUMB. 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行記錄
這裡是說明不知道記錄集的記錄個數的情況, 如果已知, 用上面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 上測試通過
-- end --
oracle FAQ(1) from chao_ping
1.快速整理破碎的表(在Oracle8i裡邊才可以這樣使用)
ALTER TABLE table_name MOVE ( TABLESPACE XXX);
如何移動一張表所在的資料表空間
方法一：
1． Export 這張表
2． Drop這張表
3． Create table xxx tablespace xxx;
4． Imp Ignore=y
還要注意的一點是，所有要讀取這張表的PL/SQL儲存過程都會失效。需要重新編譯。
1． SELECT * FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
2． 對這些包，函數，過程重新編譯。
方法二：
僅對Oracle8i適用。
使用下面的語句：
ALTER TABLE table_name MOVE
TABLESPACE new_tablespace;
這樣的話，所有的約束、索引、觸發器都不會受到影響。
但是需要rebuild這個標上的所有索引。
2.怎樣直接進入sql*plus而不用輸入使用者名稱，密碼：
sqlplus /nolog;
sqlplus username/password@connect_string
3.怎樣快速重建索引：
alter index xxx rebuild storage();
alter index xxx coalesce;
4. 為什麼我看不到dbms_output的結果？
SET SERVEROUTPUT ON
5． 進行一次大的事務以後，已經COMMIT了，但為什麼我的復原段還是那樣大？
因為沒有設定OPTIMAL的值，所以不會自動收縮。
可以用alter rollback segment shrink to Xm;來手工進行收縮。
6． 為什麼要使用VARCHAR2，而不用CHAR?
A．CHAR只支援2000位元組長，而VARCHAR2支援4000位元組的長度，適用性更好
B． CHAR 佔用更多的儲存空間，定義多長，它就佔用多長的空間，插入字元後面自動加空格填充；而VARCHAR2不論定義多長，都只使用實際插入的長度。
7． 為什麼從不同的資料字典看，表/索引所佔用的空間不一樣？
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
這是因為第一個資料庫檢視DBA_TABLES的BLOCKS列是指實際上使用的BLOCK數目，還有一些BLOCK雖然被佔用了，但是沒有資料存在，不計入裡邊。而在DBA_SEGMENTS這個資料庫檢視裡邊，BLOCKS列是指這個表總共佔用的BLOCK的數目，包括有資料和沒有資料的BLOCK總量。如果把第一個視圖裡邊的BLOCKS和EMPTY_BLOCKS地總和加起來，正好等於第二個視圖的BLOCKS列的大小。
8． 怎樣把資料庫的一張，多張表存為一個普通的文字檔？
可以在SQL*Plus裡邊用SPOOL命令把選出來的資料儲存在SPOOL指定的檔案裡邊。
9． 怎樣從一張表裡重複資料刪除的記錄
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語句來識別那些重複的記錄：
SQL> SELECT COUNT(*), EMP_ID, OFFICE_ID
FROM EMP
GROUP BY EMP_ID, OFFICE_ID
HAVING COUNT(*) > 1;
結果如下：
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
使用下面的語句來重複資料刪除的記錄：
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裡想資料庫插入特殊字元？
可以使用CHR函數。
11． 怎樣刪除一個列？
在Oracle8i裡邊，可以直接Drop一個列。文法為alter table table_name drop column_name;
但是注意要在initsid.ora裡邊設定compatible=8.1.0以上。
12． 怎樣重新命名一個列？
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． 怎樣為事務指定一個大的復原段？
Set transaction use rollback segment rbs_name;
15． 怎樣知道一張表上有那些許可權賦予了哪些人，給他們了什麼許可權？
select * from dba_tab_privs where table_name='表名';
16． 怎麼發現是誰鎖住了你需要的一張表？
Select object_id from v$locked_object;
Select object_name, object_type from dba_objects where object_id='';
每次清空一張表的時候，（使用truncate），這張表的儲存參數NEXT自動複位到最後被刪除的那個extent的大小。同樣，如果顯式地從一張表裡邊釋放空間，NEXT參數也會自動被設定成最後被釋放的那個extent的大小。
在SQL*Plus裡邊可以為一個事務指定一個復原段：這在有大的事務將要發生的話時候還是很有用的。使用下面的語句可以為這個事務指定一個復原段：
SQL>SET TRANSACTION USE ROLLABCK SEGMENT 復原段名稱；
還可以在PL/SQL裡邊為一個事務指定一個復原段（不使用動態sql語句）。這個需要使用Oracle提供的包：DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT(‘復原段名稱');
在有些平台上的Oracle，在啟動的時候會自動產生一個sgadefSID.dbf，用這個檔案是否存在就可以判斷一個執行個體是否在運行。這個檔案包含了SGA在記憶體中的地址。在資料庫關閉的時候，Oracle會自動刪除這個檔案。但是在Oracle8i裡邊，這個檔案不再存在了。需要使用新的判斷方式來斷定究竟某個執行個體是否在運行。比如PS命令。
在Oracle7裡邊，想要知道資料檔案是否可以自動擴充，必須從sys.filext$這張表裡邊查取，但是在Oracle8裡邊，從dba_data_files裡邊就可以知道資料檔案是否可以自動擴充了。
從Oracle8i開始，可以建立另一類資料庫一級的觸發器，比如資料庫啟動、關閉，使用者登入、登出等事務，都可以觸發這個事件的發生，從而作某些記錄。在資料庫一級定義的觸發器會在所有使用者相應事件發生的時候觸發，而在Schema一級定義的觸發器只有在某個特定使用者的相應事件發生的時候才會觸發。
從Oracle8i開始，多了一種關閉資料庫的方式：SHUTDOWN TRANSACTIONAL。這種方式允許所有的使用者提交它們的工作。但是一旦提交之後就馬上被切斷聯結，等所有使用者都完成了各自的事務，shutdown就開始了。
從Oracle8開始，可以建立暫存資料表，這些表的定義對於所有該使用者的會話都是可以看到的，但是每個會話查詢、插入、刪除的資料和別的會話查詢、插入、刪除的資料都是不相關的。就像每個會話都分別有這樣一份表一樣。
從Oracle8i開始，對於那些沒有進行分區的表，可以不用IMP/EXP就可以快速重組。但是這需要兩倍於該表容量的資料表空間。這個語句就是：
ALTER TABLE TB_NAME MOVE TABLESPACE TS_NAME;
在Oracle8i裡邊可以建立反序索引。（CREATE INDEX i ON t (a,b,c) REVERSE;）。由於反序索引的相鄰索引值不是存放在物理相鄰的位置，因此只有全索引掃描或者通過單個列這一類語句才能夠有效利用這些索引。這類反序索引在Oracle並行伺服器上能夠較好地協調不同執行個體對資料庫的修改，可以在一定程度上提高系統效能。
從Oracle8開始，$instance視圖可以查獲許多有用的資訊：比如主機名稱，執行個體名，啟動時間，版本號碼等。
暫存資料表空間裡邊建立的臨時段只有在shutdown地時候才會被釋放。
但是在permanent資料表空間裡邊建立的臨時段在一個事務結束之後就會被釋放，有Smon進程來完成這個任務。
oracle FAQ(2) from chao_ping
關於OPTIMAL參數
optimal是用於限制復原段大小的一個儲存參數。在執行一個長的事務之後，那個事務所使用的復原段會比較大，而設定了Optimal這個參數以後，一旦事務提交結束，復原段自動收縮到Optimal所指定的大小。
如果你的系統中有許多長時間啟動並執行事務的話，那麼應該把復原段的Optimal參數設定的比較大一點。這樣有利於保持復原段資料表空間的連續性。否則不斷的擴張、收縮會使資料表空間更加破碎。
如果系統中主要的事務都是短時間的，那麼應該把復原段設定的比較小一些，這樣有利於讓復原段裡面的資訊可以儲存在SGA裡邊，以利於提高系統效能。
復原段的Optimal參數可以在建立復原段的時候指定，也可以用
ALTER ROLLBACK SEGMENT SEGMENT_NAME OPTIMAL XX M;這樣來重新設定。
Oracle8i 裡邊的
ALTER SESSION SET CURRENT_SCHEMA=
可以用來更改當前的使用者模式。
Oracle公司已經宣稱，不再支援server manager,這個工具自從Oracle 6.0開始，就一直是管理Oracle資料庫的主要工具。現在，SQL*Plus替代了Server Manager的地位，因此，Server Manager中相應的功能也都整合到了SQL*Plus之中。
SQL*Plus新增加的主要命令是startup, shutdown, archive log,和recover。當然，標準的SQL文法仍然是支援的了，比如一系列的CREATE, ALTER等語句。但是也對其中的一些有了一些改變，比如原來不支援的ALTER DATABASE OPEN，ALTER DATABASE MOUNT,ALTER DATABASE BACKUP 等句子。
對於SET命令，也多了一些新的選項，用來包含一些如自動回復等。SHOW命令也開始可以用來直接顯示參數SHOW PARAMETER ,SHOW SGA等。而這些，原來都只是在Server Manager裡面才有的功能。
Oracle8i仍舊保留了盡人皆知的INTERNAL賬戶，但是要記住，這主要是為了向後相容。INTERNAL賬戶的功能現在開始有SYSDBA,SYSOPER這兩個角色來支援了。INTERNAL/SYS的口令可以用下面這個方法來進行修改：
O/S Prompt> orapwd password=
當資料庫剛剛建立的時候，SYS的密碼預設為change_on_install，而SYSTEM的密碼是manager，而INTERNAL則根本就沒有密碼。因此，建立完資料庫之後，第一件要做的事情就是改變以上三個使用者的口令。INTERNAL的口令可以用前面提到過的方法來改變，而SYSTEM，SYS則可以直接用ALTER USER username IDENTIFIED BY password；來更改。注意，在Oracle8i開始，ALTER USER SYS IDENTIFIED BY password；同時也會更改INTERNAL的密碼，如果你為INTERNAL設定了密碼的話，同時，馬上把SYSDBA,SYSOPER這兩個角色授予負責管理這個資料庫的使用者。對於那些使用INTERNAL來串連資料庫的指令碼，也要相應的作一些修改。
啟動和關閉資料庫：
要從SQL*Plus裡邊啟動資料庫，請按照以下的步驟進行：
O/S Prompt>sqlplus /nolog
SQL> connect scott/tiger as SYSDBA
SQL> startup
如果希望用不同於預設得參數來啟動資料庫，可以使用下面代參數的啟動命令：
SQL> startup PFILE=
有時候需要啟動資料庫，但是不讓普通使用者進入，比如為了平衡IO，需要移動一個資料檔案的位置，這時候就需要改變預設得啟動選項：
SQL> startup mount
當完成了維護任務之後，可以選擇關閉資料庫然後再重新按照正常方式開啟，或者直接在SQL*Plus裡邊輸入下面的命令，Oracle就可以開始正常運行了：
SQL> alter database open
有時候需要建立一個新的資料庫，或者需要重建控制檔案，就需要用下面的語句：
SQL> startup nomount
有時候，資料庫難以正常啟動，就可以考慮使用下面的辦法來強迫啟動：使用FORCE選項，STARTUP FORCE實際上相當於一個SHUTDOWN ABORT然後再STARTUP這樣一個過程。
SQL> startup force
SHUTDOWN這個命令也有好幾種參數可以選擇：
正常關閉是等待所有使用者都從系統退出以後，再正常關閉系統。這是一種最最理想的關閉資料庫的方式。一般都應該使用這種方式來正常關閉資料庫。
SQL> shutdown
在Oracle8i開始，新加了一個關閉選項：SHUTDOWN TRANSACTIONAL。這允許所有使用者都完成它們的事務，一旦事務提交，馬上被中斷連線。這樣既保證了使用者不會丟失它們的事務，也保證了資料庫可以及時關閉，進行必要的維護操作。這種方式關閉的話，下次系統啟動之後，也不用進行執行個體一級的恢複。比下面提到的另外兩種方式都要理想。
SQL> shutdown transactional
SHUTDOWN IMMEDIATE是馬上中止使用者的當前事務，並不等這些事務完成，復原這些使用者的當前事務。但是如果有一些事務很久沒有提交的話，那麼SHUTDOWN IMMEDIATE或許就不像說得那樣IMMEDIATE了。可能也要花很多時間來復原這些事務。
SQL> shutdown immediate
在Oracle8i裡邊最後一種關閉方式是SHUTDOWN ABORT。這種關閉方式和直接關閉電腦的電源其實沒有太多的區別。任何當前串連的使用者都被馬上斷開聯結，在下次執行個體再次啟動的時候，必須進行執行個體一級的恢複，用以復原沒有提交的事務。
SQL> shutdown abort
ALTER TABLE table_name MOVE之後，表上的索引標誌為UNUSABLE？
在Oracle8i開始，可以直接使用alter table table_name move [tablespace tablespace_name];
來為一張表移動到另一個資料表空間，或者重新組織表的儲存方式，以減少片段。但是，這樣使用過之後，所有這張表上的索引都將被標誌為unusable。這是因為MOVE一張表之後，表中列對應的物理位置都改變了，就是所有行的ROWID都變化了，而這張表的索引就用到了其中行的ROWID。由於Oracle不會自動更新索引對應的ROWID，這時候，索引上的ROWID就指向了錯誤的地方。因此，索引被標誌為UNUSABLE。這時候，你就需要手工重建索引。可以使用下面的文法來重建索引：
ALTER INDEX index_name REBUILD;當然，還可以為索引指定特定的合適的儲存參數，來最佳化索引的儲存。或許Oracle之所以沒有自動維護索引，就是為了讓你可以為索引指定合適的儲存參數。
如何遠程安裝Oracle：
如果需要從PC機上的X－window用戶端安裝Unix上的Oracle系統，要注意下面這一點：Oracle8i使用的是Universal Installer，使用了Java技術，必須在圖形介面下安裝。如果是遠程安裝，必須設定一下什麼地方來顯示Universal Installer的圖形介面：使用
$DISPLAY=workstation_name:0.0
$export DISPLAY
舉一個例子，你的PC機的IP地址是150.150.4.128，機器名字叫做test，那麼就可以使用下面的文法來進行為安裝作準備工作：
$DSIPLAY=150.150.4.128:0.0
$export DISPLAY
或者使用下面的文法，但是必須這個test機器的資訊寫在hosts檔案裡邊：
DSIPLAY=test:0.0
$export DISPLAY
http://www.bkjia.com/PHPjc/314169.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/314169.htmlTechArticle以前搜集的一個Oracle比較常見問題的列表,忘記了是從哪來的 關於 SELECT N 問題 有感於一些網友多次諮詢和討論選取某些指定行資料的問題...
