Oracle常用命令大全

1、Oracle常用命令大全（很有用，做筆記）一、ORACLE的啟動(dòng)和關(guān)閉 1、在單機(jī)環(huán)境下 要想啟動(dòng)或關(guān)閉ORACLE系統(tǒng)必須首先切換到ORACLE用戶，如下 su - oracle a、啟動(dòng)ORACLE系統(tǒng) oracle>svrmgrl SVRMGR>connect internal SVRMGR>startup SVRMGR>quit b、關(guān)閉ORACLE系統(tǒng) oracle>svrmgrl SVRMGR>connect internal 

2、SVRMGR>shutdown SVRMGR>quit 啟動(dòng)oracle9i數(shù)據(jù)庫(kù)命令： $ sqlplus /nolog SQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 31 13:53:53 2003 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. SQL> connect / as sysdba  Connected to an idle

3、instance. SQL> startupC SQL> startup ORACLE instance started. 2、在雙機(jī)環(huán)境下 要想啟動(dòng)或關(guān)閉ORACLE系統(tǒng)必須首先切換到root用戶，如下 su root a、啟動(dòng)ORACLE系統(tǒng) hareg y oracle b、關(guān)閉ORACLE系統(tǒng) hareg n oracle Oracle數(shù)據(jù)庫(kù)有哪幾種啟動(dòng)方式 說明： 有以下幾種啟動(dòng)方式： 1、startup nomount 非

4、安裝啟動(dòng)，這種方式啟動(dòng)下可執(zhí)行：重建控制文件、重建數(shù)據(jù)庫(kù) 讀取init.ora文件，啟動(dòng)instance，即啟動(dòng)SGA和后臺(tái)進(jìn)程，這種啟動(dòng)只需要init.ora文件。 2、startup mount dbname 安裝啟動(dòng)，這種方式啟動(dòng)下可執(zhí)行： 數(shù)據(jù)庫(kù)日志歸檔、 數(shù)據(jù)庫(kù)介質(zhì)恢復(fù)、 使數(shù)據(jù)文件聯(lián)機(jī)或脫機(jī)， 重新定位數(shù)據(jù)文件、重做日志文件。 執(zhí)行“nomount”，然后打開控制文件，確認(rèn)數(shù)據(jù)文件和聯(lián)機(jī)日志文件的位置， 但此時(shí)不對(duì)數(shù)據(jù)文件和日志文件進(jìn)行校驗(yàn)檢查。 3、startup open dbn

5、ame 先執(zhí)行“nomount”，然后執(zhí)行“mount”，再打開包括Redo log文件在內(nèi)的所有數(shù)據(jù)庫(kù)文件， 這種方式下可訪問數(shù)據(jù)庫(kù)中的數(shù)據(jù)。 4、startup，等于以下三個(gè)命令 startup nomount alter database mount alter database open 5、startup restrict 約束方式啟動(dòng) 這種方式能夠啟動(dòng)數(shù)據(jù)庫(kù)，但只允許具有一定特權(quán)的用戶訪問 非特權(quán)用戶訪問時(shí)，會(huì)出現(xiàn)以下提示： ERROR： ORA-01035: O

6、RACLE 只允許具有 RESTRICTED SESSION 權(quán)限的用戶使用 6、startup force 強(qiáng)制啟動(dòng)方式 當(dāng)不能關(guān)閉數(shù)據(jù)庫(kù)時(shí)，可以用startup force來完成數(shù)據(jù)庫(kù)的關(guān)閉 先關(guān)閉數(shù)據(jù)庫(kù)，再執(zhí)行正常啟動(dòng)數(shù)據(jù)庫(kù)命令 7、startup pfile=參數(shù)文件名 帶初始化參數(shù)文件的啟動(dòng)方式 先讀取參數(shù)文件，再按參數(shù)文件中的設(shè)置啟動(dòng)數(shù)據(jù)庫(kù) 例：startup pfile=E:Oracleadminoradbpfileinit.ora 8、startup EXCLUSIVE 二、用戶

7、如何有效地利用數(shù)據(jù)字典   ORACLE的數(shù)據(jù)字典是數(shù)據(jù)庫(kù)的重要組成部分之一，它隨著數(shù)據(jù)庫(kù)的產(chǎn)生而產(chǎn)生, 隨著數(shù)據(jù)庫(kù)的變化而變化, 體現(xiàn)為sys用戶下的一些表和視圖。數(shù)據(jù)字典名稱是大寫的英文字符。     數(shù)據(jù)字典里存有用戶信息、用戶的權(quán)限信息、所有數(shù)據(jù)對(duì)象信息、表的約束條件、統(tǒng)計(jì)分析數(shù)據(jù)庫(kù)的視圖等。 我們不能手工修改數(shù)據(jù)字典里的信息。 很多時(shí)候，一般的ORACLE用戶不知道如何有效地利用它。 dictionary全部數(shù)據(jù)字典表的名稱和解釋，它有一個(gè)同義詞dict  

8、0;  dict_column 全部數(shù)據(jù)字典表里字段名稱和解釋     如果我們想查詢跟索引有關(guān)的數(shù)據(jù)字典時(shí)，可以用下面這條SQL語(yǔ)句:     SQL>select * from dictionary where instr(comments,'index')>0;     如果我們想知道user_indexes表各字段名稱的詳細(xì)含義，可以用下面這條SQL語(yǔ)句:     SQL>select

9、 column_name,comments from dict_columns where table_name='USER_INDEXES'     依此類推，就可以輕松知道數(shù)據(jù)字典的詳細(xì)名稱和解釋，不用查看ORACLE的其它文檔資料了。     下面按類別列出一些ORACLE用戶常用數(shù)據(jù)字典的查詢使用方法。     1、用戶           &

10、#160; 查看當(dāng)前用戶的缺省表空間             SQL>select username,default_tablespace from user_users;         查看當(dāng)前用戶的角色         SQL>select * from user_role_privs; &

11、#160;       查看當(dāng)前用戶的系統(tǒng)權(quán)限和表級(jí)權(quán)限         SQL>select * from user_sys_privs;         SQL>select * from user_tab_privs;     2、表       &#

12、160;     查看用戶下所有的表             SQL>select * from user_tables;             查看名稱包含log字符的表           

13、0; SQL>select object_name,object_id from user_objects                 where instr(object_name,'LOG')>0;             查看某表的創(chuàng)建時(shí)間   

14、60;         SQL>select object_name,created from user_objects where object_name=upper('&table_name');             查看某表的大小           

15、;  SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments                 where segment_name=upper('&table_name');           

16、;  查看放在ORACLE的內(nèi)存區(qū)里的表             SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;     3、索引             查看索引個(gè)數(shù)和類別 &

17、#160;           SQL>select index_name,index_type,table_name from user_indexes order by table_name;             查看索引被索引的字段           

18、  SQL>select * from user_ind_columns where index_name=upper('&index_name');             查看索引的大小             SQL>select sum(bytes)/(1024*1024) as "size(M

19、)" from user_segments                 where segment_name=upper('&index_name');     4、序列號(hào)             查看序列號(hào)，last_number是當(dāng)前值&

20、#160;            SQL>select * from user_sequences;     5、視圖             查看視圖的名稱             SQL>sel

21、ect view_name from user_views;             查看創(chuàng)建視圖的select語(yǔ)句             SQL>set view_name,text_length from user_views;          

22、   SQL>set long 2000;                說明：可以根據(jù)視圖的text_length值設(shè)定set long 的大小             SQL>select text from user_views where view_name=upper('&

23、;view_name');     6、同義詞             查看同義詞的名稱             SQL>select * from user_synonyms;     7、約束條件     

24、60;       查看某表的約束條件             SQL>select constraint_name, constraint_type,search_condition, r_constraint_name                

25、 from user_constraints where table_name = upper('&table_name');         SQL>select c.constraint_name,c.constraint_type,cc.column_name             from user_constraints c,user_cons_colum

26、ns cc             where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')             and c.owner = cc.owner and c.constraint_name = c

27、c.constraint_name             order by cc.position;     8、存儲(chǔ)函數(shù)和過程             查看函數(shù)和過程的狀態(tài)            

28、 SQL>select object_name,status from user_objects where object_type='FUNCTION'             SQL>select object_name,status from user_objects where object_type='PROCEDURE'        

29、60;    查看函數(shù)和過程的源代碼             SQL>select text from all_source where owner=user and name=upper('&plsql_name'); 三、查看數(shù)據(jù)庫(kù)的SQL 1、查看表空間的名稱及大小     select t.tablespace_name, round(sum(byt

30、es/(1024*1024),0) ts_size     from dba_tablespaces t, dba_data_files d     where t.tablespace_name = d.tablespace_name     group by t.tablespace_name; 2、查看表空間物理文件的名稱及大小     select tablespace_name, file_id, file_name

31、,     round(bytes/(1024*1024),0) total_space     from dba_data_files     order by tablespace_name; 3、查看回滾段名稱及大小     select segment_name, tablespace_name, r.status,     (initial_extent/1024) Initi

32、alExtent,(next_extent/1024) NextExtent,     max_extents, v.curext CurExtent     From dba_rollback_segs r, v$rollstat v     Where r.segment_id = v.usn(+)     order by segment_name ; 4、查看控制文件     s

33、elect name from v$controlfile; 5、查看日志文件     select member from v$logfile; 6、查看表空間的使用情況     select sum(bytes)/(1024*1024) as free_space,tablespace_name     from dba_free_space     group by tablespace_name; 

34、    SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,     (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"     FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C     WHE

35、RE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 7、查看數(shù)據(jù)庫(kù)庫(kù)對(duì)象     select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status; 8、查看數(shù)據(jù)庫(kù)的版本     Select version FROM Product_compone

36、nt_version     Where SUBSTR(PRODUCT,1,6)='Oracle' 9、查看數(shù)據(jù)庫(kù)的創(chuàng)建日期和歸檔方式     Select Created, Log_Mode, Log_Mode From V$Database; 四、ORACLE用戶連接的管理 用系統(tǒng)管理員，查看當(dāng)前數(shù)據(jù)庫(kù)有幾個(gè)用戶連接： SQL> select username,sid,serial# from v$session; 如果要停某個(gè)連接用

37、60;SQL> alter system kill session 'sid,serial#' 如果這命令不行,找它UNIX的進(jìn)程數(shù) SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr; 說明：21是某個(gè)連接的sid數(shù) 然后用 kill 命令殺此進(jìn)程號(hào)。 五、SQL*PLUS使用 a、近入SQL*Plus $sqlplus 用戶名/密碼  &#

38、160; 退出SQL*Plus SQL>exit b、在sqlplus下得到幫助信息 列出全部SQL命令和SQL*Plus命令 SQL>help 列出某個(gè)特定的命令的信息 SQL>help 命令名 c、顯示表結(jié)構(gòu)命令DESCRIBE SQL>DESC 表名 d、SQL*Plus中的編輯命令 顯示SQL緩沖區(qū)命令 SQL>L 修改SQL命令 首先要將待改正行變?yōu)楫?dāng)前行 SQL>n 用CHANGE命令修改內(nèi)容

39、60;SQL>c/舊/新 重新確認(rèn)是否已正確 SQL>L 使用INPUT命令可以在SQL緩沖區(qū)中增加一行或多行 SQL>i SQL>輸入內(nèi)容 e、調(diào)用外部系統(tǒng)編輯器 SQL>edit 文件名 可以使用DEFINE命令設(shè)置系統(tǒng)變量EDITOR來改變文本編輯器的類型，在login.sql文件中定義如下一行 DEFINE_EDITOR=vi f、運(yùn)行命令文件 SQL>START test SQL>test 常用SQL*Plus語(yǔ)句

40、 a、表的創(chuàng)建、修改、刪除 創(chuàng)建表的命令格式如下： create table 表名 （列說明列表）； 為基表增加新列命令如下： ALTER TABLE 表名 ADD （列說明列表） 例：為test表增加一列Age，用來存放年齡     sql>alter table test         add （Age number(3)）； 修改基表列定義命令如下： ALTER TABLE 表名&#

41、160;MODIFY （列名 數(shù)據(jù)類型） 例：將test表中的Count列寬度加長(zhǎng)為10個(gè)字符     sql>alter atble test         modify （County char(10)）； b、將一張表刪除語(yǔ)句的格式如下： DORP TABLE 表名； 例：表刪除將同時(shí)刪除表的數(shù)據(jù)和表的定義 sql>drop table test c、表空間的創(chuàng)建、刪除 六、ORACLE

42、邏輯備份的SH文件 完全備份的SH文件：exp_comp.sh rq= date +"%m%d"  su - oracle -c "exp system/manager full=y inctype=complete file=/oracle/export/db_comp$rq.dmp" 累計(jì)備份的SH文件：exp_cumu.sh rq= date +"%m%d"  su - oracle -c "exp system/manager full=y inctype=

43、cumulative file=/oracle/export/db_cumu$rq.dmp" 增量備份的SH文件: exp_incr.sh rq= date +"%m%d"  su - oracle -c "exp system/manager full=y inctype=incremental file=/oracle/export/db_incr$rq.dmp" root用戶crontab文件 /var/spool/cron/crontabs/root增加以下內(nèi)容 0 2 1 *

44、 * /oracle/exp_comp.sh 30 2 * * 0-5 /oracle/exp_incr.sh 45 2 * * 6 /oracle/exp_cumu.sh 當(dāng)然這個(gè)時(shí)間表可以根據(jù)不同的需求來改變的，這只是一個(gè)例子。 七、ORACLE 常用的SQL語(yǔ)法和數(shù)據(jù)對(duì)象 一.數(shù)據(jù)控制語(yǔ)句 (DML) 部分 1.INSERT  (往數(shù)據(jù)表里插入記錄的語(yǔ)句) INSERT INTO 表名(字段名1, 字段名2, ) VALUES ( 值1, 值2, ); INSERT INTO 表名(字段名1, 字段

45、名2, )  SELECT (字段名1, 字段名2, ) FROM 另外的表名; 字符串類型的字段值必須用單引號(hào)括起來, 例如: GOOD DAY 如果字段值里包含單引號(hào) 需要進(jìn)行字符串轉(zhuǎn)換, 我們把它替換成兩個(gè)單引號(hào)''. 字符串類型的字段值超過定義的長(zhǎng)度會(huì)出錯(cuò), 最好在插入前進(jìn)行長(zhǎng)度校驗(yàn). 日期字段的字段值可以用當(dāng)前數(shù)據(jù)庫(kù)的系統(tǒng)時(shí)間SYSDATE, 精確到秒 或者用字符串轉(zhuǎn)換成日期型函數(shù)TO_DATE(2001-08-01,YYYY-MM-DD) TO_DATE()還有很多種日期格式, 可以參看ORACL

46、E DOC. 年-月-日 小時(shí):分鐘:秒 的格式Y(jié)YYY-MM-DD HH24:MI:SS INSERT時(shí)最大可操作的字符串長(zhǎng)度小于等于4000個(gè)單字節(jié), 如果要插入更長(zhǎng)的字符串, 請(qǐng)考慮字段用CLOB類型, 方法借用ORACLE里自帶的DBMS_LOB程序包. INSERT時(shí)如果要用到從1開始自動(dòng)增長(zhǎng)的序列號(hào), 應(yīng)該先建立一個(gè)序列號(hào) CREATE SEQUENCE 序列號(hào)的名稱 (最好是表名+序列號(hào)標(biāo)記) INCREMENT BY 1  START  WITH  1 MAXVALUE  999

47、99  CYCLE  NOCACHE; 其中最大的值按字段的長(zhǎng)度來定, 如果定義的自動(dòng)增長(zhǎng)的序列號(hào) NUMBER(6) , 最大值為999999 INSERT 語(yǔ)句插入這個(gè)字段值為: 序列號(hào)的名稱.NEXTVAL 2.DELETE  (刪除數(shù)據(jù)表里記錄的語(yǔ)句) DELETE FROM表名 WHERE 條件; 注意：刪除記錄并不能釋放ORACLE里被占用的數(shù)據(jù)塊表空間. 它只把那些被刪除的數(shù)據(jù)塊標(biāo)成unused. 如果確實(shí)要?jiǎng)h除一個(gè)大表里的全部記錄, 可以用 TRUNCATE 命令, 它可以釋放占用的數(shù)據(jù)塊

48、表空間 TRUNCATE TABLE 表名; 此操作不可回退. 3.UPDATE  (修改數(shù)據(jù)表里記錄的語(yǔ)句) UPDATE表名 SET 字段名1=值1, 字段名2=值2, WHERE 條件; 如果修改的值N沒有賦值或定義時(shí), 將把原來的記錄內(nèi)容清為NULL, 最好在修改前進(jìn)行非空校驗(yàn); 值N超過定義的長(zhǎng)度會(huì)出錯(cuò), 最好在插入前進(jìn)行長(zhǎng)度校驗(yàn). 注意事項(xiàng): A.        以上SQL語(yǔ)句對(duì)表都加上了行級(jí)鎖,  &#

49、160;      確認(rèn)完成后, 必須加上事物處理結(jié)束的命令 COMMIT 才能正式生效,         否則改變不一定寫入數(shù)據(jù)庫(kù)里.         如果想撤回這些操作, 可以用命令 ROLLBACK 復(fù)原. B.        在運(yùn)行INSERT, DELETE 和 UPDATE 語(yǔ)句前最好估算一下可能

50、操作的記錄范圍,         應(yīng)該把它限定在較小 (一萬條記錄) 范圍內(nèi),. 否則ORACLE處理這個(gè)事物用到很大的回退段.         程序響應(yīng)慢甚至失去響應(yīng). 如果記錄數(shù)上十萬以上這些操作, 可以把這些SQL語(yǔ)句分段分次完成,         其間加上COMMIT 確認(rèn)事物處理. 二.數(shù)據(jù)定義 (DDL) 部分 1.CREAT

51、E (創(chuàng)建表, 索引, 視圖, 同義詞, 過程, 函數(shù), 數(shù)據(jù)庫(kù)鏈接等) ORACLE常用的字段類型有 CHAR                        固定長(zhǎng)度的字符串 VARCHAR2            &

52、#160;   可變長(zhǎng)度的字符串 NUMBER(M,N)                數(shù)字型M是位數(shù)總長(zhǎng)度, N是小數(shù)的長(zhǎng)度 DATE                       

53、; 日期類型 創(chuàng)建表時(shí)要把較小的不為空的字段放在前面, 可能為空的字段放在后面 創(chuàng)建表時(shí)可以用中文的字段名, 但最好還是用英文的字段名 創(chuàng)建表時(shí)可以給字段加上默認(rèn)值, 例如 DEFAULT SYSDATE 這樣每次插入和修改時(shí), 不用程序操作這個(gè)字段都能得到動(dòng)作的時(shí)間 創(chuàng)建表時(shí)可以給字段加上約束條件 例如 不允許重復(fù) UNIQUE, 關(guān)鍵字 PRIMARY KEY 2.ALTER        (改變表, 索引, 視圖等) 改變表的名稱 

54、;ALTER TABLE 表名1  TO 表名2; 在表的后面增加一個(gè)字段 ALTER TABLE表名 ADD 字段名 字段名描述; 修改表里字段的定義描述 ALTER TABLE表名 MODIFY字段名 字段名描述; 給表里的字段加上約束條件 ALTER TABLE 表名 ADD CONSTRAINT 約束名 PRIMARY KEY (字段名); ALTER TABLE 表名 ADD CONSTRAINT 約束名 UNIQUE (字段名); 把表放在或取出數(shù)據(jù)庫(kù)的內(nèi)存區(qū) ALTER TABLE

55、 表名 CACHE; ALTER TABLE 表名 NOCACHE; 3.DROP        (刪除表, 索引, 視圖, 同義詞, 過程, 函數(shù), 數(shù)據(jù)庫(kù)鏈接等) 刪除表和它所有的約束條件 DROP TABLE 表名 CASCADE CONSTRAINTS; 4.TRUNCATE (清空表里的所有記錄, 保留表的結(jié)構(gòu)) TRUNCATE 表名; 三.查詢語(yǔ)句 (SELECT) 部分 SELECT字段名1, 字段名2, FROM 表名1, 表名

56、2, WHERE 條件; 字段名可以帶入函數(shù)   例如:  COUNT(*), MIN(字段名),  MAX(字段名),  AVG(字段名), DISTINCT(字段名),            TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS') NVL(EXPR1, EXPR2)函數(shù) 解釋: IF EXPR1=NULL  

57、0;              RETURN EXPR2 ELSE                        RETURN EXPR1 DECODE(AAV1R1V2R2.)函數(shù) 解釋: IF A

58、A=V1 THEN RETURN R1 IF AA=V2 THEN RETURN R2 . ELSE RETURN NULL LPAD(char1,n,char2)函數(shù) 解釋: 字符char1按制定的位數(shù)n顯示，不足的位數(shù)用char2字符串替換左邊的空位 字段名之間可以進(jìn)行算術(shù)運(yùn)算 例如:  (字段名1*字段名1)/3 查詢語(yǔ)句可以嵌套 例如: SELECT FROM (SELECT FROM表名1, 表名2, WHERE 條件) WHERE 條件2; 兩

59、個(gè)查詢語(yǔ)句的結(jié)果可以做集合操作 例如: 并集UNION(去掉重復(fù)記錄), 并集UNION ALL(不去掉重復(fù)記錄), 差集MINUS,  交集INTERSECT 分組查詢 SELECT字段名1, 字段名2, FROM 表名1, 表名2, GROUP BY字段名1 HAVING 條件 ; 兩個(gè)以上表之間的連接查詢 SELECT字段名1, 字段名2, FROM 表名1, 表名2, WHERE           

60、0;     表名1.字段名 = 表名2. 字段名 AND ; SELECT字段名1, 字段名2, FROM 表名1, 表名2, WHERE                 表名1.字段名 = 表名2. 字段名(+) AND ; 有(+)號(hào)的字段位置自動(dòng)補(bǔ)空值 查詢結(jié)果集的排序操作, 默認(rèn)的排序是升序ASC, 降序是DESC SELECT字段名1, 字段名2, FROM 表名1, 表名2,  ORDER BY字段名1, 字段名2 DESC; 字符串模糊比較的方法 INSTR(字段名, 字符串)>0 字段名 LIKE  字符串%  %字符串% 每個(gè)表都有一個(gè)隱含的字段ROWID, 它標(biāo)記著記錄的唯一性. 四.ORACLE里常用的數(shù)據(jù)對(duì)象 (S