以前搜集的一個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<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行記錄
這里是說明不知道記錄集的記錄個數的情況, 如果已知, 用上面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/[email protected]_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=<some 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=<init.ora file to be used>
有時候需要啟動數據庫，但是不讓普通用戶進入，比如為了平衡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
