Oracle入門資料.doc

ORACLE快速入門隨筆修改密碼方法：1.SQL; grant connect to USRNAME identified by USRPWD ;2.SQL; alter user USRNAME identified by USRPWD ;3.SQL; password USRNAME ;更改 USRNMAE 的口令舊口令：新口令：重新鍵入新口令：修改成oracle認證方法：1.修改參數(shù)文件中remote_login_passwordfile=EXCLUSIVE 或SHARED2.修改/home/network/admin/SQLNET.ORA 文件增加 sqlnet.authentication_services=(NFS)ORACLE_SID是操作系統(tǒng)級的參數(shù)INSTANCE_NAME是數(shù)據(jù)庫級的參數(shù)SPFILE 是服務器參數(shù)文件PFILE 是文本參數(shù)文件create pfile=/oracle/test.ora from spfile ;show parameter spfile ; 后如果VALUE值是零，則沒有使用spfile。啟動數(shù)據(jù)庫是可以指定是用pfile還是用spfile啟動 如下：startup pfile=/oracle/test.orashow parameter instanceshow parameter dbalter system set job_queue_processes=50comment =temporaty change onscope = memory|spfile|both ;alter session set CONCAT(Good, String) GoodStringSUBSTR(String,1,3) Str LENGTH(String) 6 INSTR(String, r) 3 LPAD(sal,10,*) *5000union union allminus 第一個查詢減去第二個查詢intersect 交集 SQL; select replace(itpub oracle,oracle,training)itpub from dual /ITPUB-itpub trainingTranslate SQL; select translate(This is second trianing of itpub in Beijing!,abcde,12345) itpub2from dual;ITPUB-This is s53on4 tri1ning of itpu2 in B5ijing!FloorSQL; select floor(4.55) from dual;FLOOR(4.55)- 4CeilSQL; select ceil(4.55) from dual;CEIL(4.55)- 5ROUND: 4舍5入到指定的小數(shù)位ROUND(45.926, 2) 45.93TRUNC: 將值截斷到指定的小數(shù)位TRUNC(45.926, 2) 45.92MOD: 返回相除后的余數(shù)MOD(1600, 300) 100select trunc(sysdate, D) - 3 from dual; trunc(sysdate, D), 截取到本周的第一天(0:00) trunc(sysdate, M), 截取到本月的第一天(0:00) trunc(sysdate, MM), 截取到本月的第一天(0:00)trunc(sysdate, DD), 截取到本日的0:00 trunc(sysdate,yyyy)得到今年的第一天的0:00.十進制與十六進制EYGLEvilen; select to_char(2463,xxxx) from dual;TO_CH-99f已選擇 1 行。十六進制與十進制EYGLEvilen; select to_number(99f,xxxx) from dual;TO_NUMBER(99F,XXXX)- 2463SQL; select ename,sal,2case job WHEN MANAGER THEN 1.1*sal3 WHEN ANALYST THEN 1.2*SAL4 WHEN CLERK THEN 1.5*SAL5ELSE sal END NEW_SAL6from emp;ENAME SAL NEW_SAL- - -SMITH 800 1200ALLEN 1600 1600WARD 1250 1250JONES 2975 3272.5MARTIN 1250 1250BLAKE 2850 3135CLARK 2450 2695SCOTT 3000 3600KING 5000 5000TURNER 1500 1500ADAMS 1100 1650JAMES 950 1425FORD 3000 3600MILLER 1300 1950SQL; SELECT job, sal,2 DECODE(job, ANALYSTSAL*1.1,3 CLERK, SAL*1.15,4 MANAGER, SAL*1.20,5 SAL)6 REVISED_SALARY7FROM emp;JOB SAL REVISED_SALARY- - -PRESIDENT 5000 5000MANAGER 2850 3420MANAGER 2450 2940將空值轉(zhuǎn)換為實際的值數(shù)據(jù)格式可以是日期,字符,數(shù)字數(shù)據(jù)類型必須匹配 NVL(comm,0)NVL(hiredate,01-JAN-97)NVL(job,No Job Yet)NVL2(expr1,expr2,expr3)如果expr1不為Null，返回expr2，如果expr1為Null，返回expr3。expr1可以為任何數(shù)據(jù)類型NULLIF(expr1,expr2)比較兩個表達式，如果相等返回空值，如果不等返回第一個值。COALESCE(expr1,expr2,.,exprn)返回表達式列表里的第一個非空表達式錯誤:在啟動數(shù)據(jù)庫時出現(xiàn)ORA-01157,ORA-01110或操作系統(tǒng)級錯誤例如ORA-07360,在關(guān)閉數(shù)據(jù)庫(使用shutdown normal或shutdown immediate) 時將導致錯誤ORA-01116,ORA-01110以及操作系統(tǒng)級錯誤ORA-07368解決:以下有兩種解決方案:1.用戶的表空間可以被輕易地重建即最近導出的對象是可用的或表空間中的對象可以被輕易地重建等.在這種情況下,最簡單的方法是offline并刪除該數(shù)據(jù)文件,刪除表空間并重建表空間以及所有的對象. svrmgrl; startup mountsvrmgrl; alter database datafile filename offline drop;svrmgrl; alter database open;svrmgrl; drop tablespace tablespace_name including contents;重建表空間及所有對象.2.用戶的表空間不能夠被輕易地重建 在大多數(shù)情況下,重建表空間是不可能及太辛苦的工作.方法是倒備份及做介質(zhì)恢復.如果您的系統(tǒng)運行在NOARCHIVELOG模式下,則只有丟失的數(shù)據(jù)在online redo log中方可被恢復。步驟如下:1)Restore the lost datafile from a backup2)svrmgrl; startup mount3)svrmgrl; select v1.group#,member,sequence#,first_change#; from v$log v1,v$logfile v2 ; where v1.group#=v2.group#;4)如果數(shù)據(jù)庫運行在NOARCHIVELOG模式下則:svrmgrl; select file#,change# from v$recover_file; 如果 CHANGE# 大于最小的FIRST_CHANGE#則數(shù)據(jù)文件可以被恢復。如果 CHANGE# 小于最小的FIRST_CHANGE#則數(shù)據(jù)文件不可恢復。 恢復最近一次的全備份或采用方案一. 5)svrmgrl; recover datafile filename; 6)確認恢復成功 7)svrmgrl; alter database open resetlogs; 3.只讀表空間無需做介質(zhì)恢復,只要將備份恢復即可.唯一的例外是: 表空間在最后一次備份后被改為read-write 模式表空間在最后一次備份后被改為read-only 模式在這種情況下,均需進行介質(zhì)恢復二、臨時表空間臨時表空間并不包含真正的數(shù)據(jù),恢復的方法是刪除臨時表空間并重建即可. 三、系統(tǒng)表空間如果備份不可用,則只能采用重建數(shù)據(jù)庫的方法 四、回滾表空間有兩種情況: 1、數(shù)據(jù)庫已經(jīng)完全關(guān)閉(使用shutdown immediate或shutdown命令) 1) 確認數(shù)據(jù)庫完全關(guān)閉 2) 修改init.ora文件,注釋rollback-segment 3) svrmgrl; startup restrict mount 4) svrmgrl; alter database datafile filename offline drop; 5) svrmgrl; alter database open; 基于出現(xiàn)的結(jié)果: statement processed 轉(zhuǎn)(7) ORA-00604,ORA-00376,ORA-01110轉(zhuǎn)(6) 6) svrmgrl; shutdown immediate 修改init.ora文件,增加如下一行: _corrupted_rollback_segments = (,.) svrmgrl; startup restrict 7) svrmgrl; drop tablespace tablespace_name including contents; 重建表空間及回滾段 9) svrmgrl; alter system disable restricted session; 10) 修改init.ora文件 2、數(shù)據(jù)庫未完全關(guān)閉(數(shù)據(jù)庫崩潰或使用shutdown abort命令關(guān)閉數(shù)據(jù)庫) 1) 恢復備份 2) svrmgrl; startup mount 3) svrmgrl; select file#,name,status from v$datafile; svrmgrl; alter database datafile filename online; 4) svrmgrl; select v1.group#,member,sequence#,first_change# ; from v$log v1,v$logfile v2 ; where v1.group#=v2.group#; 5) svrmgrl; select file#,change# from v$recover_file; 見一方案2-4 6) svrmgrl; recover datafile filename; 7) svrmgrl; alter database open; 3、數(shù)據(jù)庫處于打開狀態(tài) 1) 刪除回滾段和表空間 2) 重建表空間和回滾段 五、控制文件恢復 1所有的控制文件均被破壞 將備份的控制文件拷貝至原目錄下 對于RAW DEVICE,則: dd if=con.bak of=/dev/rdrd/drd1 seek=128 2并非所有的控制文件均被破壞用其他的控制文件啟動數(shù)據(jù)庫。3. 數(shù)據(jù)文件上所存儲的表select a.segment_name from dba_extents a,dba_data_files b where a.file_id = b.file_id and b.file_name = 文件名全路徑 and a.segment_type = TABLEjob不能自動運行check db parameters job_queue_processes if it set to 0 then set it larger than 0 job_queue_processes=1問題描述執(zhí)行如下語句select session_id,object_name from v$locked_object a,dba_objects bwhere a.object_id=b.object_iderror: ora-03212 Temporary Segment cannt be cteated in locally-managed tablespace.更改為sys 用戶 以dba登錄,可以查詢.SHUTDOWN IMMEDIATE； STARTUP MOUNT； RECOVER DATABASE； ALTER DATABASE OPEN；查看表大?。elect segment_name,(bytes/1024)/1024|M 大小， (BLOCKS*DB_BLOCK_SIZE)/1024)/1024|M占用空間 from dba_segments wheresegment_name = .db_block_size 是參數(shù)查詢未提交事務select username, SID|,| SERIAL# ses, T.STATUS , START_TIME, RN.NAME, USED_UBLK, USED_UREC from v$transaction T, v$session S, v$rollstat RS, v$rollname RN where T.SES_ADDR = S.SADDR and T.XIDUSN = RS.USN and RN.USN = RS.USN order by USED_UBLK;如何將數(shù)據(jù)庫中被鎖表解鎖 作者：來自：閱讀次數(shù)： 18 大 中 小 -我們在操作數(shù)據(jù)庫的時候，有時候會由于操作不當引起數(shù)據(jù)庫表被鎖定，這么我們經(jīng)常不知所措，不知怎么給這些表解鎖，在pl/sql Developer工具的的菜單“tools”里面的“sessions”可以查詢現(xiàn)在存在的會話，但是我們很難找到那個會話被鎖定了，想找到所以被鎖的會話就更難了，下面這叫查詢語句可以查詢出所以被鎖的會話。如下：SELECT sn.username, m.SID,sn.SERIAL#, m.TYPE, DECODE (m.lmode, 0, None, 1, Null, 2, Row Share, 3, Row Excl., 4, Share, 5, S/Row Excl., 6, Exclusive, lmode, LTRIM (TO_CHAR (lmode, 990) ) lmode, DECODE (m.request, 0, None, 1, Null, 2, Row Share, 3, Row Excl., 4, Share, 5, S/Row Excl., 6, Exclusive, request, LTRIM (TO_CHAR (m.request, 990) ) request, m.id1, m.id2 FROM v$session sn, v$lock m WHERE (sn.SID = m.SID AND m.request != 0) -存在鎖請求，即被阻塞 OR ( sn.SID = m.SID -不存在鎖請求，但是鎖定的對象被其他會話請求鎖定 AND m.request = 0 AND lmode != 4 AND (id1, id2) IN ( SELECT s.id1, s.id2 FROM v$lock s WHERE request != 0 AND s.id1 = m.id1 AND s.id2 = m.id2) )ORDER BY id1, id2, m.request;通過以上查詢知道了sid和 SERIAL#就可以開殺了 alter system kill session sid,SERIAL#;在SQLPLUS里： 1.不顯示 行頭 set heading off pages 0 2.不顯示行數(shù)信息 set feedback off 3。不在屏幕上顯示結(jié)果 set termout off 1.由于該問題由oarcle工程師定位為無法恢復，并在網(wǎng)上進行了求助也每人能解決，所以我可以方下包袱，去大膽的嘗試新辦法，按照我對數(shù)據(jù)庫的框架理解去尋求辦法。2.因為無法聯(lián)機的表空空間為用戶表空間，所以不影響數(shù)據(jù)庫系統(tǒng)的啟動，所以通過 ：SQL;startup mount; SQL;alter database open;啟動數(shù)據(jù)庫3.我查看了sys.col#,sys.obj#表，想從表中得到關(guān)于datafile0016的信息，但沒有能找到我認為需要的信息；4.后來我查看了sys.file#,發(fā)現(xiàn)了所有數(shù)據(jù)文件的記錄，然后我找到對應的file0016的記錄，想通過刪除該記錄來屏蔽表空間啟動時的數(shù)據(jù)文件的完整性檢測，于是現(xiàn)對sys.file#進行了備份，然后刪除了file0016對應的記錄。5.此時執(zhí)行SQL;alter database tablespace ts_user online;提示數(shù)據(jù)庫已被修改，我驚喜的發(fā)現(xiàn)表空間被聯(lián)機成功了。由于相應的數(shù)據(jù)文件在這之前已經(jīng)損壞，而且該數(shù)據(jù)文件已經(jīng)被設置成了offline，并且局方在數(shù)據(jù)庫關(guān)閉后，在mount狀態(tài)下直接對該文件所在表空間進行了offline操作，所以現(xiàn)在如果再通過“alter database datafile filename offline drop”已經(jīng)不起作用，因為此時的表空間默認的狀態(tài)為offline，該數(shù)據(jù)文件與其所在表空間（聯(lián)機或脫機)具有一致性。所以此時如果想啟動該表空間，只能通過屏蔽數(shù)據(jù)文件的完整性檢查！ 數(shù)據(jù)庫業(yè)務表空間聯(lián)機失敗 數(shù)據(jù)庫業(yè)務表空間聯(lián)機失敗1.由于該問題由oarcle工程師定位為無法恢復，并在網(wǎng)上進行了求助也每人能解決，所以我可以方下包袱，去大膽的嘗試新辦法，按照我對數(shù)據(jù)庫的框架理解去尋求辦法。2.因為無法聯(lián)機的表空空間為用戶表空間，所以不影響數(shù)據(jù)庫系統(tǒng)的啟動，所以通過 ：SQL;startup mount;SQL;alter database open;啟動數(shù)據(jù)庫3.我查看了sys.col#,sys.obj#表，想從表中得到關(guān)于datafile0016的信息，但沒有能找到我認為需要的信息；4.后來我查看了sys.file#,發(fā)現(xiàn)了所有數(shù)據(jù)文件的記錄，然后我找到對應的file0016的記錄，想通過刪除該記錄來屏蔽表空間啟動時的數(shù)據(jù)文件的完整性檢測，于是現(xiàn)對sys.file#進行了備份，然后刪除了file0016對應的記錄。5.此時執(zhí)行SQL;alter database tablespace ts_user online;提示數(shù)據(jù)庫已被修改，我驚喜的發(fā)現(xiàn)表空間被聯(lián)機成功了。/dev/rlvdata0016文件需要介質(zhì)恢復，表空間聯(lián)機失??！ SQLPLUS常用命令假設當前執(zhí)行命令為：select * from tab;(a)ppend 添加文本到緩沖區(qū)當前行尾aorder by tname結(jié)果：select * from tab order by tname;（注：a后面跟2個空格）(c)hange/old/new 在當前行用新的文本替換舊的文本c/*/tname結(jié)果：select tname from tab;(c)hange/text從當前行刪除文本c/tab結(jié)果：select tname from ;del刪除當前行del n刪除第n行(i)nput 文本 在當前行之后添加一行(l)ist 顯示緩沖區(qū)中所有行(l)ist n 顯示緩沖區(qū)中第 n 行(l)ist m n 顯示緩沖區(qū)中 m 到 n 行run執(zhí)行當前緩沖區(qū)的命令/執(zhí)行當前緩沖區(qū)的命令r執(zhí)行當前緩沖區(qū)的命令文件名運行調(diào)入內(nèi)存的sql文件，如：SQL; edit s;如果當前目錄下不存在s.sql文件，則系統(tǒng)自動生成s.sql文件，在其中輸入“select * from tab;”，存盤退出。SQL; s;系統(tǒng)會自動查詢當前用戶下的所有表、視圖、同義詞。文件名 在.sql文件中調(diào)用令一個.sql文件時使用save 文件名將緩沖區(qū)的命令以文件方式存盤，缺省文件擴展名為.sqlget 文件名 調(diào)入存盤的sql文件start 文件名 運行調(diào)入內(nèi)存的sql文件spool 文件名 把這之后的各種操作及執(zhí)行結(jié)果“假脫機”即存盤到磁盤文件上，默認文件擴展名為.lstspool顯示當前的“假脫機”狀態(tài)spool off停止輸出例：SQL; spool aSQL; spool正假脫機到 A.LSTSQL; spool offSQL; spool當前無假脫機exit 退出SQL*PLUSdesc 表名顯示表的結(jié)構(gòu)show user顯示當前連接用戶show error 顯示錯誤show all 顯示所有68個系統(tǒng)變量值edit 打開默認編輯器，Windows系統(tǒng)中默認是notepad.exe，把緩沖區(qū)中最后一條SQL語句調(diào)入afiedt.buf文件中進行編輯edit 文件名把當前目錄中指定的.sql文件調(diào)入編輯器進行編輯clear screen 清空當前屏幕顯示ORACLE管理常用命令第一章：日志管理1.forcing log switches sql; alter system switch logfile;2.forcing checkpoints sql; alter system checkpoint;3.adding online redo log groups sql; alter database add logfile group 4 sql; (/disk3/log4a.rdo,/disk4/log4b.rdo) size 1m;4.adding online redo log members sql; alter database add logfile member sql; /disk3/log1b.rdo to group 1, sql; /disk4/log2b.rdo to group 2;5.changes the name of the online redo logfile sql; alter database rename file c:/oracle/oradata/oradb/redo01.log sql; to c:/oracle/oradata/redo01.log;6.drop online redo log groups sql; alter database drop logfile group 3;7.drop online redo log members sql; alter database drop logfile member c:/oracle/oradata/redo01.log;8.clearing online redo log files sql; alter database clear unarchived logfile c:/oracle/log2a.rdo;9.using logminer analyzing redo logfiles a. in the init.ora specify utl_file_dir = b. sql; execute dbms_logmnr_d.build(oradb.ora,c:oracleoradblog); c. sql; execute dbms_logmnr_add_logfile(c:oracleoradataoradbredo01.log, sql; dbms_logmnr.new); d. sql; execute dbms_logmnr.add_logfile(c:oracleoradataoradbredo02.log, sql; dbms_logmnr.addfile); e. sql; execute dbms_logmnr.start_logmnr(dictfilename=;c:oracleoradblogoradb.ora); f. sql; select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters sql; v$logmnr_logs); g. sql; execute dbms_logmnr.end_logmnr;第二章：表空間管理1.create tablespaces sql; create tablespace tablespace_name datafile c:oracleoradatafile1.dbf size 100m, sql; c:oracleoradatafile2.dbf size 100m minimum extent 550k logging/nologging sql; default storage (initial 500k next 500k maxextents 500 pctinccease 0) sql; online/offline permanent/temporary extent_management_clause2.locally managed tablespace sql; create tablespace user_data datafile c:oracleoradatauser_data01.dbf sql; size 500m extent management local uniform size 10m;3.temporary tablespace sql; create temporary tablespace temp tempfile c:oracleoradatatemp01.dbf sql; size 500m extent management local uniform size 10m;4.change the storage setting sql; alter tablespace app_data minimum extent 2m; sql; alter tablespace app_data default storage(initial 2m next 2m maxextents 999);5.taking tablespace offline or online sql; alter tablespace app_data offline; sql; alter tablespace app_data online;6.read_only tablespace sql; alter tablespace app_data read only|write;7.droping tablespace sql; drop tablespace app_data including contents;8.enableing automatic extension of data files sql; alter tablespace app_data add datafile c:oracleoradataapp_data01.dbf size 200m sql; autoextend on next 10m maxsize 500m;9.change the size fo data files manually sql; alter database datafile c:oracleoradataapp_data.dbf resize 200m;10.Moving data files: alter tablespace sql; alter tablespace app_data rename datafile c:oracleoradataapp_data.dbf sql; to c:oracleapp_data.dbf;11.moving data files:alter database sql; alter database rename file c:oracleoradataapp_data.dbf sql; to c:oracleapp_data.dbf;第三章：表1.create a table sql; create table table_name (column datatype,column datatype.) sql; tablespace tablespace_name pctfree integer pctused integer sql; initrans integer maxtrans integer sql; storage(initial 200k next 200k pctincrease 0 maxextents 50) sql; logging|nologging cache|nocache2.copy an existing table sql; create table table_name logging|nologging as subquery3.create temporary table sql; create global temporary table xay_temp as select * from xay; on commit preserve rows/on commit delete rows4.pctfree = (average row size - initial row size) *100 /average row size pctused = 100-pctfree- (average row size*100/available data space)5.change storage and block utilization parameter sql; alter table table_name pctfree=30 pctused=50 storage(next 500k sql; minextents 2 maxextents 100);6.manually allocating extents sql; alter table table_name allocate extent(size 500k datafile c:/oracle/data.dbf);7.move tablespace sql; alter table employee move tablespace users;8.deallocate of unused space sql; alter table table_name deallocate unused keep integer9.truncate a table sql; truncate table table_name;10.drop a table sql; drop table table_name cascade constraints;11.drop a column sql; alter table table_name drop column comments cascade constraints checkpoint 1000; alter table table_name drop columns continue;12.mark a column as unused sql; alter table table_name set unused column comments cascade constraints; alter table table_name drop unused columns checkpoint 1000; alter table orders drop columns continue checkpoint 1000 data_dictionary : dba_unused_col_tabs第四章：索引1.creating function-based indexes sql; create index summit.item_quantity on summit.item(quantity-quantity_shipped);2.create a B-tree index sql; create unique index index_name on table_name(column,. asc/desc) tablespace sql; tablespace_name pctfree integer initrans integer maxtrans integer sql; logging | nologging nosort storage(initial 200k next 200k pctincrease 0 sql; maxextents 50);3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows4.creating reverse key indexes sql; create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k sql; next 200k pctincrease 0 maxextents 50) tablespace indx;5.create bitmap index sql; create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k sql; pctincrease 0 maxextents 50) tablespace indx;6.change storage parameter of index sql; alter index xay_id storage (next 400k maxextents 100);7.allocating index space sql; alter index xay_id allocate extent(size 200k datafile c:/