以前搜集的一個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 ROWNUM ( 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參數可以在創
