1. 命令
1) 連接數據庫
sqlplus "/as sysdba"
sqlplus system/manager@mplus
2) 啟動(dòng)、停止數據庫 (在orace用戶(hù)下以dba用戶(hù)進(jìn)入sql)
shutdown immediate;
startup;
3) 查看當前服務(wù)中有多少數據庫(即用戶(hù)) (以dba用戶(hù)進(jìn)入sql)
select USERNAME from all_users;
4) 查看當前數據庫中有多少表 (以數據庫名進(jìn)入sql)
select TABLE_NAME from user_tables;
或select table_name from all_tables where owner='aaaaa';
5) 查看當前表是否有分區
select PARTITION_NAME from USER_TAB_PARTITIONS where table_name = 'TBL_USER_STORAGE';
6) 查看某個(gè)分區表中的數據存儲在哪個(gè)分區
SQL> SELECT rowid, user_id from tbl_user_storage; //從表中查出行號，最好帶個(gè)表的主鍵以便確認是哪條記錄
ROWID USER_ID
------------------ ----------
AABml0AAKAAAAdnAAA 1
AABml1AAKAAAAduAAA 2
AABml2AAKAAAAd2AAA 3
SQL> select dbms_rowid.ROWID_OBJECT('AABml2AAKAAAAd2AAA') from dual; //從dual中查出行的對象
DBMS_ROWID.ROWID_OBJECT('AABML2AAKAAAAD2AAA')
---------------------------------------------
420214
SQL> select subobject_name from dba_objects where data_object_id = 420214; //根據對象ID查出分區
SUBOBJECT_NAME
------------------------------------------------------------
PART_4
ps: 在建表時(shí)指定做為分區索引的字段，如MC中的part_index， 此字段是根據userid%分區數來(lái)計算的。
如在插入一條記錄時(shí)，userid為2，分區數為100，所以可計算出part_index為2，插入數據庫的記錄中也為(2,2)
但實(shí)際上，此條記錄是被存儲在分區3中的，查詢(xún)時(shí)，需要指定在分區3中查詢(xún)。
插入記錄時(shí)通過(guò)指定part_index指定分區，但在其它操作時(shí)，必需顯式指定分區號 如 TBL_USER_STORAGE partition(PART_4)
7) 創(chuàng )建數據庫
以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為表空間的名稱(chēng)，mmscdata01.dbf為數據庫文件，這兩個(gè)名稱(chēng)自己修改）
//SQL> create tablespace mmsc_index datafile '/opt/oracle/ora_data/ora817/mmscindx01.dbf' size 100M;
//表空間已創(chuàng )建。
//（其中mmsc_index為表空間的索引名，mmscindx01.dbf為索引文件，這兩個(gè)名稱(chēng)自己修改，這個(gè)操作可選，如果不作的話(huà)，默認使用的是oracle系統的）
SQL> create user zyan identified by zyan default tablespace zyan;
用戶(hù)已創(chuàng )建
（如果第二步?jīng)]有做的話(huà)，這一句要省去“temporary tablespace temp”）
SQL> grant connect,resource to zyan;
授權成功。
SQL> commit;
提交完成。
注意，裸設備的話(huà)不能讓表空間自動(dòng)增長(cháng)（AUTOEXTEND OFF）
啟動(dòng)偵聽(tīng)
oracle> lsnrctl
lsnrctl> start
lsnrctl> stop
或是直接執行 lsnrctl start/status/stop
9) ping service_name是否可用
tnsping service_name 如 tnsping mplus
10) 顯示Oraclenb 系統參數
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最后執行的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字符集
在數據庫端：選擇需要的字符集（通過(guò)create database中的CHARACTER SET與NATIONAL CHARACTER SET子句指定）；
在客戶(hù)端：設置操作系統實(shí)際使用的字符集（通過(guò)環(huán)境變量NLS_LANG設置）
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) 用對戶(hù)進(jìn)行解鎖
alter user PORTALDB account unlock;
16) 用戶(hù)權限查詢(xún)
確定角色的權限
select * from role_tab_privs;
select * from role_role_privs;
select * from role_sys_privs;
確定用戶(hù)帳戶(hù)所授予的權限
select * from DBA_tab_privs;
select * from DBA_role_privs;
select * from DBA_sys_privs;
確定當前用戶(hù)的權限
select * from session_privs;
17) 某個(gè)文件需要恢復
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í)報錯，某個(gè)文件出錯
//解決方法
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.
或：
如果出錯的文件不重要的話(huà)，，可以
先 startup mount
再 alter database datafile '/opt/oracle/oracle/oradata/zyan01.dbf' offline;
18) ORA－01031 insufficient proivileges 解決方法
1. 首先查看 network/admin下sqlnet.ora文件的權限是否正確
2. 檢查環(huán)境變量是否正確 ORACLE_HOME ORACLE_SID等
3. 查看共享內存是否釋放 ipcs -m /ipcs -s 如果沒(méi)有釋放，就強制刪除。
20) 歸檔模式
查看當前模式： archive log list
#從歸檔修改成非歸檔，歸檔模式會(huì )導致程序掛起，直到手動(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內存管理
sga_max_size表示當前總空間，sga_target值必須和sga_max_size一樣。
db_cache_size java_pool_size large_pool_size shared_pool_size 全部設置為0，讓oracle自動(dòng)管理內存，均衡分配。
建議值：
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;
查看當前SGA值 select sum(value)/1024/1024 from v$sga;
查看當前buffer剩余值 select current_size from v$buffer_pool;
查看當前buffer分配 select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
PGA: program globle area 程序全局區，服務(wù)器進(jìn)程所使用的內存區域?！?
oracle會(huì )盡是保證總PGA內存不超過(guò)pag_aggregate_target，但如果不夠用，為了避免操作磁盤(pán)，
oralce還是會(huì )使用內存，PGA值將超過(guò)pag_aggregate_target
查看advice: select * from v$pga_target_advice;
如果內存夠，可以選擇命中率接近100%的值
pag_aggregate_target 也建議設置為 (總內存 - 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) 查看錯誤碼含義
oerr ora 3499(error code)
24) R2板參數建議值
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
查詢(xún)某個(gè)表空間使用哪個(gè)設備文件：
select * from dba_data_files where TABLESPACE_NAME = 'OME_SM_IDX_3_32K';
查詢(xún)設備文件的表空間
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
查看當前用戶(hù)下所有的對象 select * from user_objects
26) 數據導入導出
exp icd/icd@orauidb tables=tbilllog1 rows=y indexes=n file=tbilllog1.dmp
imp icd/icd@orauidb feedback=5000 full=y file=full.dmp
按用戶(hù)備份 exp icd/icd@orauidb owner=icd feedback=5000 file=icd.dmp
全庫備份 exp icd/icd@orauidb feedback=5000 full=y file=full.dmp
備份指定的表空間 exp icd/icd@orauidb tablespaces=service_core_dat file=service_core_dat.dmp
按條件導出 exp icd/icd@orauidb tables=tbilllog1 query=\”where logdate>to_date('2005-06-01','yyyy-mm-dd')\" file=tbilllog1.dmp
恢復整個(gè)備份文件 imp icd/icd@orauidb feedback=5000 full=y file=full.dmp
27) 表空間
因db_block_size的限制，表空間每個(gè)文件的大小是有限制的。
一般 db_block_size=8192，即一個(gè)表空間文件最大32G。超過(guò)此值時(shí)，建表空間會(huì )失敗。
ORA-01144: File size (125440000 blocks) exceeds maximum of 4194303 blocks
oracle中db_block_size=8192，此參數據在創(chuàng )建oracle實(shí)例時(shí)定義
在init.ora文件中，值為8192表示，當前實(shí)例中，創(chuàng )建表空間可使用的單個(gè)文件或裸設備最大為32G,且大小不可更改
計算方式（oracle最大允許4194303個(gè)block，） block個(gè)數＊大小/(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è)用戶(hù)下的所有表
select 'Drop table '||table_name||';' from all_tables where owner='ZXF';
執行上面的sql可以生成刪除這個(gè)用戶(hù)下所有表的sql，拷貝出來(lái)到sqlPlus下執行即可。
29) 查找失效的對象 select object_name from user_objects where status = 'INVALID‘
編譯失效的對象 alter procedure p_my_proc compile
查看被鎖的對象 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) 查看數據庫使用的數據文件 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) 如何測試SQL語(yǔ)句執行所用的時(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 的性能差異
父表存儲父親，子表存儲孩子，然后通過(guò)pid和父表關(guān)聯(lián)，查詢(xún)需要的結果是找到尚未有孩子的父親。
select * from parent where id not in (select pid from childen) 花費 10 秒
select * from parent where id in ( select id from parent minus select pid from childen ) 花費 1 秒內
三?！?wèn)題解決
1. 11.1.0.6版本安裝完成后，/home/oracle/db/diag/rdbms/orcl/orcl/cdump 目錄每分鐘有三個(gè)以上的core文件產(chǎn)生
方法1 升級到11.1.0.7即可解決此問(wèn)題。
方法2 設置：alter system set JAVA_JIT_ENABLED= FALSE scope = both; 重啟
