Oracle 常見問題_PHP教學課程
- WBOY原創
- 2016-07-21 16:10:42961瀏覽
以前蒐集的一個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 ROWNUMB. SELECT * FROM
( SELECT * FROM CAT ORDER BY TABLE_TYPE )
WHERE ROWNUM
2. 選取N1-N2行記錄
A. SELECT TABLE_N TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ BETWEEN N1 AND N2;
或:
SELECT * FROM CAT WHERE ROWNUMMINUS
( SELECT ROWNUM ROWSEQ,X.* FROM (SELECT * FROM CAT ORDER BY TABLE_TYPE) X)
WHERE ROWSEQ BETWEEN N1 1 AND N.選取FOOT N行記錄
這裡是說明不知道記錄集的記錄個數的情況, 如果已知, 用上面2的方法即可
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ ,TABLE_NAME,TABLE_TYPE _TYPE )
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
或
SELECT * FROM
( SELECT TABLE_NAME,TABLE_TYPE FROM CAT ORDER BYTABLE_&A. 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
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, GEGEORGE
305 12 ELLISON, GEGEORGE
128 17 SIMPSON, LINDA
305 22 JACKSON, DREW
使用下面的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:
Table Example, with duplicate values:
EMP_ID OFFICE_ID EMPNAME
305 12 ELLISON, GEORGE
305 12 MERCURIO, JASON
305 12 MERCURIO, JASON
305 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
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 =
1old_column_name; 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=''; 每次使用一張表的時候，這張表的儲存參數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$export DISPLAY