oracle文章分類:數(shù)據(jù)庫(kù)1. 命令
1) 連接數(shù)據(jù)庫(kù) sqlplus "/as sysdba" sqlplus system/manager@mplus 2) 啟動(dòng),、停止數(shù)據(jù)庫(kù) (在orace用戶下以dba用戶進(jìn)入sql) shutdown immediate; startup; 3) 查看當(dāng)前服務(wù)中有多少數(shù)據(jù)庫(kù)(即用戶) (以dba用戶進(jìn)入sql) select USERNAME from all_users; 4) 查看當(dāng)前數(shù)據(jù)庫(kù)中有多少表 (以數(shù)據(jù)庫(kù)名進(jìn)入sql) select TABLE_NAME from user_tables; 或select table_name from all_tables where owner='aaaaa'; 5) 查看當(dāng)前表是否有分區(qū) select PARTITION_NAME from USER_TAB_PARTITIONS where table_name = 'TBL_USER_STORAGE'; 6) 查看某個(gè)分區(qū)表中的數(shù)據(jù)存儲(chǔ)在哪個(gè)分區(qū) SQL> SELECT rowid, user_id from tbl_user_storage; //從表中查出行號(hào),,最好帶個(gè)表的主鍵以便確認(rèn)是哪條記錄 ROWID USER_ID ------------------ ---------- AABml0AAKAAAAdnAAA 1 AABml1AAKAAAAduAAA 2 AABml2AAKAAAAd2AAA 3 SQL> select dbms_rowid.ROWID_OBJECT('AABml2AAKAAAAd2AAA') from dual; //從dual中查出行的對(duì)象 DBMS_ROWID.ROWID_OBJECT('AABML2AAKAAAAD2AAA') --------------------------------------------- 420214 SQL> select subobject_name from dba_objects where data_object_id = 420214; //根據(jù)對(duì)象ID查出分區(qū) SUBOBJECT_NAME ------------------------------------------------------------ PART_4 ps: 在建表時(shí)指定做為分區(qū)索引的字段,如MC中的part_index,, 此字段是根據(jù)userid%分區(qū)數(shù)來(lái)計(jì)算的,。 如在插入一條記錄時(shí),userid為2,,分區(qū)數(shù)為100,,所以可計(jì)算出part_index為2,插入數(shù)據(jù)庫(kù)的記錄中也為(2,2) 但實(shí)際上,,此條記錄是被存儲(chǔ)在分區(qū)3中的,,查詢時(shí),需要指定在分區(qū)3中查詢,。 插入記錄時(shí)通過指定part_index指定分區(qū),,但在其它操作時(shí),必需顯式指定分區(qū)號(hào) 如 TBL_USER_STORAGE partition(PART_4) 7) 創(chuàng)建數(shù)據(jù)庫(kù) 以oracle/oracle登錄后,,sqlplus "/ as sysdba" SQL> create tablespace zyan datafile '/opt/oracle/oradata/oracle9/zyan01.dbf' size 100M; //create tablespace zhaoyan datafile '/dev/rmmslv3' size 100M; 表空間已創(chuàng)建,。 (其中mmsc_data為表空間的名稱,mmscdata01.dbf為數(shù)據(jù)庫(kù)文件,,這兩個(gè)名稱自己修改) //SQL> create tablespace mmsc_index datafile '/opt/oracle/ora_data/ora817/mmscindx01.dbf' size 100M; //表空間已創(chuàng)建,。 //(其中mmsc_index為表空間的索引名,mmscindx01.dbf為索引文件,,這兩個(gè)名稱自己修改,,這個(gè)操作可選,如果不作的話,,默認(rèn)使用的是oracle系統(tǒng)的) SQL> create user zyan identified by zyan default tablespace zyan; 用戶已創(chuàng)建 (如果第二步?jīng)]有做的話,,這一句要省去“temporary tablespace temp”) SQL> grant connect,resource to zyan; 授權(quán)成功。 SQL> commit; 提交完成,。 注意,,裸設(shè)備的話不能讓表空間自動(dòng)增長(zhǎng)(AUTOEXTEND OFF) 啟動(dòng)偵聽 oracle> lsnrctl lsnrctl> start lsnrctl> stop 或是直接執(zhí)行 lsnrctl start/status/stop 9) ping service_name是否可用 tnsping service_name 如 tnsping mplus 10) 顯示Oraclenb 系統(tǒng)參數(shù) select name,value ,issys_modifiable from v$parameter; 或show parameter; 11) 查看哪些表被鎖住了 select a.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status from v$locked_object a,dba_objects b where a.object_id=b.object_id; 或 SELECT /*+ rule */ s.username,decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT Null 解鎖: alter system kill session 'sid,serial#'; 12) 檢查Sql最后執(zhí)行的SQL語(yǔ)句 select sql_text,count(*) from v$sql s , v$session se where se.prev_hash_value =s.hash_value group by sql_text; 13) 查看回滾段信息 select segment_name,tablespace_name, status from sys.dba_rollback_segs; select segment_name, tablespace_name,bytes,blocks,extents from sys.dba_segments where segment_type='ROLLBACK'; 14) Oracle字符集 在數(shù)據(jù)庫(kù)端:選擇需要的字符集(通過create database中的CHARACTER SET與NATIONAL CHARACTER SET子句指定),; 在客戶端:設(shè)置操作系統(tǒng)實(shí)際使用的字符集(通過環(huán)境變量NLS_LANG設(shè)置) select * from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET'; update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET'; select * from v$nls_parmater; 一般使用AL32UTF8 15) 用對(duì)戶進(jìn)行解鎖 alter user PORTALDB account unlock; 16) 用戶權(quán)限查詢 確定角色的權(quán)限 select * from role_tab_privs; select * from role_role_privs; select * from role_sys_privs; 確定用戶帳戶所授予的權(quán)限 select * from DBA_tab_privs; select * from DBA_role_privs; select * from DBA_sys_privs; 確定當(dāng)前用戶的權(quán)限 select * from session_privs; 17) 某個(gè)文件需要恢復(fù) SQL> startup ORACLE instance started. Total System Global Area 217157632 bytes Fixed Size 2142976 bytes Variable Size 159386880 bytes Database Buffers 50331648 bytes Redo Buffers 5296128 bytes Database mounted. ORA-01113: file 5 needs media recovery ORA-01110: data file 5: '/opt/oracle/oracle/oradata/zyan01.dbf' //試圖啟動(dòng)數(shù)據(jù)庫(kù)時(shí)報(bào)錯(cuò),某個(gè)文件出錯(cuò) //解決方法 SQL> alter database datafile '/opt/oracle/oracle/oradata/zyan01.dbf' online; Database altered. SQL> recover database; Media recovery complete. SQL> shutdown //先關(guān)閉 ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> startup //再次啟動(dòng) ORACLE instance started. Total System Global Area 217157632 bytes Fixed Size 2142976 bytes Variable Size 159386880 bytes Database Buffers 50331648 bytes Redo Buffers 5296128 bytes Database mounted. Database opened. 或: 如果出錯(cuò)的文件不重要的話,,,,可以 先 startup mount 再 alter database datafile '/opt/oracle/oracle/oradata/zyan01.dbf' offline; 18) ORA-01031 insufficient proivileges 解決方法 1. 首先查看 network/admin下sqlnet.ora文件的權(quán)限是否正確 2. 檢查環(huán)境變量是否正確 ORACLE_HOME ORACLE_SID等 3. 查看共享內(nèi)存是否釋放 ipcs -m /ipcs -s 如果沒有釋放,就強(qiáng)制刪除,。 20) 歸檔模式 查看當(dāng)前模式: archive log list #從歸檔修改成非歸檔,,歸檔模式會(huì)導(dǎo)致程序掛起,直到手動(dòng)歸檔完成 SQL> startup mount SQL> alter database noarchivelog SQL> alter system set log_archive_start=TRUE scope=spfile; SQL> shutdown immediate; SQL> startup #從非歸檔修改成歸檔模式 SQL> startup mount SQL> alter database archivelog; SQL> alter system set log_archive_start=FALSE scope=spfile; SQL> shutdown immediate; SQL> startup 21) oracle內(nèi)存管理 sga_max_size表示當(dāng)前總空間,,sga_target值必須和sga_max_size一樣,。 db_cache_size java_pool_size large_pool_size shared_pool_size 全部設(shè)置為0,讓oracle自動(dòng)管理內(nèi)存,,均衡分配,。 建議值: alter system set sga_max_size = 1536M scope=spfile; alter system set sga_target = 1536M scope=spfile; alter system set db_cache_size = 0 scope=spfile; alter system set java_pool_size = 0 scope=spfile; alter system set large_pool_size = 0 scope=spfile; alter system set shared_pool_size = 0 scope=spfile; alter system set shared_pool_reserved_size = 31037849 scope=spfile; 查看當(dāng)前SGA值 select sum(value)/1024/1024 from v$sga; 查看當(dāng)前buffer剩余值 select current_size from v$buffer_pool; 查看當(dāng)前buffer分配 select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool; PGA: program globle area 程序全局區(qū),服務(wù)器進(jìn)程所使用的內(nèi)存區(qū)域,。 oracle會(huì)盡是保證總PGA內(nèi)存不超過pag_aggregate_target,,但如果不夠用,,為了避免操作磁盤, oralce還是會(huì)使用內(nèi)存,,PGA值將超過pag_aggregate_target 查看advice: select * from v$pga_target_advice; 如果內(nèi)存夠,,可以選擇命中率接近100%的值 pag_aggregate_target 也建議設(shè)置為 (總內(nèi)存 - SGA) alter system set pga_aggregate_target = 1659m scope=spfile; 查看SGA的狀態(tài) select * from v$SGA 22) parallel相關(guān) parallel_execution_message_size = 4k parallel_max_servers = cpus * parallel_threads_per_cpu * 4 * 5 parallel_threads_per_cpu = 2(default) alter system set parallel_max_servers = 80 scope=spfile; 23) 查看錯(cuò)誤碼含義 oerr ora 3499(error code) 24) R2板參數(shù)建議值 alter system set sga_max_size = 1536M scope=spfile; alter system set sga_target = 1536M scope=spfile; alter system set db_cache_size = 0 scope=spfile; alter system set java_pool_size = 0 scope=spfile; alter system set large_pool_size = 0 scope=spfile; alter system set shared_pool_size = 0 scope=spfile; alter system set pga_aggregate_target = 1659m scope=spfile; alter system set shared_pool_reserved_size = 31037849 scope=spfile; alter system set parallel_max_servers = 80 scope=spfile; alter system set open_cursors = 800 scope=spfile; alter system set processes = 1100 scope=spfile; alter system set sessions=1215 scope=spfile; alter system set transactions=1336 scope=spfile; 25) 查看所有的表空間 select * from dba_tablespaces 查看每個(gè)表空間總的尺寸 select tablespace_name, sum(bytes) / 1048576 from dba_data_files group by tablespace_name 查看每個(gè)表空間剩余空間 select TABLESPACE_NAME, sum(bytes)/1024/1024,sum(blocks) from dba_free_space group by tablespace_name 查詢某個(gè)表空間使用哪個(gè)設(shè)備文件: select * from dba_data_files where TABLESPACE_NAME = 'OME_SM_IDX_3_32K'; 查詢?cè)O(shè)備文件的表空間 select * from dba_data_files where FILE_NAME = '/dev/raw/raw24'; 查看表空間的使用情況 (能顯示使用率) select 'RESULT='||df.tablespace_name "Tablespace",df.bytes/(1024*1024) "Total Size(MB)", sum(fs.bytes)/(1024*1024) "Free Size(MB)", round(sum(fs.bytes)*100/df.bytes) "% Free",round((df.bytes-sum(fs.bytes))*100/df.bytes) "% Used" from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df where fs.tablespace_name=df.tablespace_name group by df.tablespace_name, df.bytes; 查看表/索引所占的空間 select sum(bytes) / 1048676 from user_extents where segment_name='T_PUB_COMMONINFO‘ 查看所有的表空間 select * from dba_tablespaces 查看當(dāng)前用戶下所有的對(duì)象 select * from user_objects 26) 數(shù)據(jù)導(dǎo)入導(dǎo)出 exp icd/icd@orauidb tables=tbilllog1 rows=y indexes=n file=tbilllog1.dmp imp icd/icd@orauidb feedback=5000 full=y file=full.dmp 按用戶備份 exp icd/icd@orauidb owner=icd feedback=5000 file=icd.dmp 全庫(kù)備份 exp icd/icd@orauidb feedback=5000 full=y file=full.dmp 備份指定的表空間 exp icd/icd@orauidb tablespaces=service_core_dat file=service_core_dat.dmp 按條件導(dǎo)出 exp icd/icd@orauidb tables=tbilllog1 query=\”where logdate>to_date('2005-06-01','yyyy-mm-dd')\" file=tbilllog1.dmp 恢復(fù)整個(gè)備份文件 imp icd/icd@orauidb feedback=5000 full=y file=full.dmp 27) 表空間 因db_block_size的限制,,表空間每個(gè)文件的大小是有限制的,。 一般 db_block_size=8192,即一個(gè)表空間文件最大32G,。超過此值時(shí),,建表空間會(huì)失敗。 ORA-01144: File size (125440000 blocks) exceeds maximum of 4194303 blocks oracle中db_block_size=8192,,此參數(shù)據(jù)在創(chuàng)建oracle實(shí)例時(shí)定義 在init.ora文件中,,值為8192表示,當(dāng)前實(shí)例中,,創(chuàng)建表空間可使用的單個(gè)文件或裸設(shè)備最大為32G,且大小不可更改 計(jì)算方式(oracle最大允許4194303個(gè)block,,) block個(gè)數(shù)*大小/(1024*1024) 即4194303*8192/1024*1024 = 32.767G 解決方法: CREATE BIGFILE TABLESPACE OME_SM_DATA_1_32K DATAFILE '/dev/raw/raw19' SIZE 980000M REUSE; 使用bigfile建表空間,但bigfile有一個(gè)限制,,只能建一個(gè)表空間文件,。 28) #刪除一個(gè)用戶下的所有表 select 'Drop table '||table_name||';' from all_tables where owner='ZXF'; 執(zhí)行上面的sql可以生成刪除這個(gè)用戶下所有表的sql,拷貝出來(lái)到sqlPlus下執(zhí)行即可,。 29) 查找失效的對(duì)象 select object_name from user_objects where status = 'INVALID‘ 編譯失效的對(duì)象 alter procedure p_my_proc compile 查看被鎖的對(duì)象 select * from v$locked_object 30) 查找耗用資源高的SQL語(yǔ)句 select SQL_TEXT, EXECUTIONS, DISK_READS, BUFFER_GETS, DISK_READS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS) as AVER_DISK, BUFFER_GETS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS) as AVER_BUFF from v$sqlarea order by AVER_BUFF desc 31) 查看數(shù)據(jù)庫(kù)使用的數(shù)據(jù)文件 select name, bytes from v$datafile Data buffer 命中率檢查 select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO" from v$sysstat cur, v$sysstat con, v$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads'; 32) 如何測(cè)試SQL語(yǔ)句執(zhí)行所用的時(shí)間 SQL>set timing on ; SQL>select * from tablename; 33) 密碼有效期 查看密碼有效期 SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'; 修改為永久有效: ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 34) 35) 2. 語(yǔ)句使用 1) in 與 not in 的性能差異 父表存儲(chǔ)父親,,子表存儲(chǔ)孩子,然后通過pid和父表關(guān)聯(lián),查詢需要的結(jié)果是找到尚未有孩子的父親,。 select * from parent where id not in (select pid from childen) 花費(fèi) 10 秒 select * from parent where id in ( select id from parent minus select pid from childen ) 花費(fèi) 1 秒內(nèi) 三,。 問題解決 1. 11.1.0.6版本安裝完成后,,/home/oracle/db/diag/rdbms/orcl/orcl/cdump 目錄每分鐘有三個(gè)以上的core文件產(chǎn)生 方法1 升級(jí)到11.1.0.7即可解決此問題,。 方法2 設(shè)置:alter system set JAVA_JIT_ENABLED= FALSE scope = both; 重啟 |
|