oracle--06查詢語句

oracle——06查詢語句

1.create user username identified b password;//建使用者名稱和密碼oracle ,oracle
2.grant connect,resource,dba to username;//授權 grant connect,resource,dba,sysdba to username;
3.connect username/password//進入。
4.select table_name,column_name from user_tab_columns where table_name='mview_log';//查詢表中的表名，字
段名等等。
5. 如何執行指令碼SQL檔案? SQL>@PATH/filename.sql;
6.Oracle oledb 提供者在command中執行多條SQL語句與SQL SERVER有少許差別,SQL Server只需使用";"分割多條SQL
語句,而Oracle需要遵守ORACLE呼叫規範,即除分號分割外,還需以begin /end;包圍語句體.
使用C#描述應如下所示:
this.oleDbCommand1.CommandText = "begin INSERT INTO GROUP_INFO (GROUP_ID, GROUP_NAME) VALUES (1, \'2
\'); INSERT INTO GROUP_INFO(GROUP_ID, GROUP_NAME) VALUES (2, \'2\'); end;";
7.查詢使用者下的所有表 select distinct table_name from user_tab_columns;
8.如何搜尋出前N條記錄？Select a.*,rownum from (select * from cardkind order by cardkind ) a where
rownum<n
9.查詢使用者下的所有表：select * from tab;
2、顯示當前連線使用者
SQL> show user
3、檢視系統擁有哪些使用者
SQL> select * from all_users;
4、新建使用者並授權
SQL> create user a identified by a;(預設建在SYSTEM表空間下)
SQL> grant connect,resource to a;
5、連線到新使用者
SQL> conn a/a
6、查詢當前使用者下所有物件
SQL> select * from tab;
7、建立第一個表
SQL> create table a(a number);
8、查詢表結構
SQL> desc a
9、插入新記錄
SQL> insert into a values(1);
10、查詢記錄
SQL> select * from a;
11、更改記錄
SQL> update a set a=2;
12、刪除記錄
SQL> delete from a;
13、回滾
SQL> roll;
SQL> rollback;
14、提交
SQL> commit;
select * from
(select t.*,dense_rank() over (order by cardkind) rank from cardkind t)
where rank = 2;
46. 如何在字串里加回車?
select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ;
47. 中文是如何排序的？
Oracle9i之前，中文是按照二進位制編碼進行排序的。
在oracle9i中新增了按照拼音、部首、筆畫排序功能。設定NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一順序）、筆劃（第二順序）排序
SCHINESE_STROKE_M 按照筆劃（第一順序）、部首（第二順序）排序
SCHINESE_PINYIN_M 按照拼音排序
48. Oracle8i中物件名可以用中文嗎？
可以
49. 如何改變WIN中SQL*Plus啟動選項？
SQL*PLUS自身的選項設定我們可以在$ORACLE_HOME/sqlplus/admin/glogin.sql中設定。
50. 怎樣修改oracel資料庫的預設日期?
alter session set nls_date_format='yyyymmddhh24miss';
OR
可以在init.ora中加上一行
nls_date_format='yyyymmddhh24miss'
51. 如何將小表放入keep池中?
alter table xxx storage(buffer_pool keep);
52. 如何檢查是否安裝了某個patch?
check that oraInventory
53. 如何使select語句使查詢結果自動生成序號?
select rownum,COL from table;
54. 如何知道資料褲中某個表所在的tablespace?
select tablespace_name from user_tables where table_name='TEST';
select * from user_tables中有個欄位TABLESPACE_NAME，（oracle）;
select * from dba_segments where …;
55. 怎麼可以快速做一個和原表一樣的備份表?
create table new_table as (select * from old_table);
55. 怎麼在sqlplus下修改procedure?
select line,trim(text) t from user_source where name ='A' order by line;
56. 怎樣解除PROCEDURE被意外鎖定?
alter system kill session ,把那個session給殺掉，不過你要先查出她的session id
or
把該過程重新改個名字就可以了。
57. SQL Reference是個什麼東西？
是一本sql的使用手冊，包括語法、函式等等，oracle官方網站的文件中心有下載.
58. 如何檢視資料庫的狀態?
unix下
ps -ef | grep ora
windows下
看服務是否起來
是否可以連上資料庫
59. 請問如何修改一張表的主鍵?
alter table aaa
drop constraint aaa_key ;
alter table aaa
add constraint aaa_key primary key(a1,b1) ;
60. 改變資料檔案的大小?
用 ALTER DATABASE .... DATAFILE .... ;
手工改變資料檔案的大小，對於原來的 資料檔案有沒有損害。
61. 怎樣檢視ORACLE中有哪些程式在執行之中？
檢視v$sessions表
62. 怎麼可以看到資料庫有多少個tablespace?
select * from dba_tablespaces;
63. 如何修改oracle資料庫的使用者連線數？
修改initSID.ora，將process加大，重啟資料庫.
64. 如何查出一條記錄的最後更新時間?
可以用logminer 察看
65. 如何在PL/SQL中讀寫檔案？
UTL_FILE包允許使用者通過PL/SQL讀寫作業系統檔案。
66. 怎樣把"&"放入一條記錄中？
insert into a values (translate ('at{&}t','at{}','at'));
67. EXP 如何加ＱＵＥＲＹ引數？
EXP USER/PASS FILE=A.DMP TABLES(BSEMPMS)
QUERY='"WHERE EMP_NO=\'S09394\'\" ﹔
68. 關於oracle8i支援簡體和繁體的字符集問題？
ZHS16GBK可以支
69. Data Guard是什麼軟體？
就是Standby的換代產品
70. 如何建立SPFILE?
SQL> connect / as sysdba
SQL> select * from v$version;
SQL> create pfile from spfile;
SQL> CREATE SPFILE FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora';
檔案已建立。
SQL> CREATE SPFILE='E:\ora9i\database\SPFILEEYGLE.ORA' FROM
PFILE='E:\ora9i\admin\eygle\pfile\init.ora';
檔案已建立。
71. 核心引數的應用?
shmmax
含義：這個設定並不決定究竟Oracle資料庫或者作業系統使用多少實體記憶體，只決定了
最多可以使用的記憶體數目。這個設定也不影響作業系統的核心資源。
設定方法：0.5*實體記憶體
例子：Set shmsys:shminfo_shmmax=10485760
shmmin
含義：共享記憶體的最小大小。
設定方法：一般都設定成為1。
例子：Set shmsys:shminfo_shmmin=1：
shmmni
含義：系統中共享記憶體段的最大個數。
例子：Set shmsys:shminfo_shmmni=100
shmseg
含義：每個使用者程式可以使用的最多的共享記憶體段的數目。
例子：Set shmsys:shminfo_shmseg=20：
semmni
含義：系統中semaphore identifierer的最大個數。
設定方法：把這個變數的值設定為這個系統上的所有Oracle的例項的init.ora中的最大
的那個processes的那個值加10。
例子：Set semsys:seminfo_semmni=100
semmns
含義：系統中emaphores的最大個數。
設定方法：這個值可以通過以下方式計算得到：各個Oracle例項的initSID.ora裡邊的
processes的值的總和（除去最大的Processes引數）＋最大的那個Processes×2＋10×
Oracle例項的個數。
例子：Set semsys:seminfo_semmns=200
semmsl:
含義：一個set中semaphore的最大個數。
設定方法：設定成為10＋所有Oracle例項的InitSID.ora中最大的Processes的值。
例子：Set semsys:seminfo_semmsl=-200
72. 怎樣檢視哪些使用者擁有SYSDBA、SYSOPER許可權？
SQL>conn sys/change_on_install
SQL>select * from V_$PWFILE_USERS;
73. 如何單獨備份一個或多個表？
exp 使用者/密碼 tables=(表1,…,表2)
74. 如何單獨備份一個或多個使用者？
exp system/manager owner=(使用者1,使用者2,…,使用者n) file=匯出檔案
75. 如何對CLOB欄位進行全文檢索？
SELECT * FROM A WHERE dbms_lob.instr(a.a,'K',1,1)>0; 76. 如何顯示當前連線使用者?
SHOW USER
77. 如何檢視資料檔案放置的路徑 ?
col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files
order by file_id;
78. 如何檢視現有回滾段及其狀態 ?
SQL> col segment format a30
SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE _ID,STATUS FROM
DBA_ROLLBACK_SEGS
79. 如何改變一個欄位初始定義的Check範圍？
SQL> alter table xxx drop constraint constraint_name;
之後再建立新約束:
SQL> alter table xxx add constraint constraint_name check();
80. Oracle常用系統檔案有哪些？
通過以下檢視顯示這些檔案資訊：v$database,v$datafile,v$logfile v$controlfile
v$parameter;
81. 內連線INNER JOIN?
Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no;
82. 如何外連線?
Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no(+);
Select a.* from bsempms a,bsdptms b wherea.dpt_no(+)=b.dpt_no;
83. 如何執行指令碼SQL檔案?
SQL>@$PATH/filename.sql;
84. 如何快速清空一個大表?
SQL>truncate table table_name;
85. 如何查有多少個資料庫例項?
SQL>SELECT * FROM V$INSTANCE;
86. 如何查詢資料庫有多少表?
SQL>select * from all_tables;
87. 如何測試SQL語句執行所用的時間?
SQL>set timing on ;
SQL>select * from tablename;
88. CHR()的反函式是?
ASCII()
SELECT CHAR(65) FROM DUAL;
SELECT ASCII('A') FROM DUAL;
89. 字串的連線
SELECT CONCAT(COL1,COL2) FROM TABLE ;
SELECT COL1||COL2 FROM TABLE ;
90. 怎麼把select出來的結果導到一個文字檔案中？
SQL>SPOOL C:\ABCD.TXT;
SQL>select * from table;
SQL >spool off;
91. 怎樣估算SQL執行的I/O數 ?
SQL>SET AUTOTRACE ON ;
SQL>SELECT * FROM TABLE;
OR
SQL>SELECT * FROM v$filestat ;
可以檢視IO數
92. 如何在sqlplus下改變欄位大小?
alter table table_name modify (field_name varchar2(100));
改大行，改小不行（除非都是空的）
93. 如何查詢某天的資料?
select * from table_name where trunc(日期欄位)＝to_date('2003-05-02','yyyy-mm-
dd');
94. sql 語句如何插入全年日期？
create table BSYEAR (d date);
insert into BSYEAR
select to_date('20030101','yyyymmdd')+rownum-1
from all_objects
where rownum <= to_char(to_date('20031231','yyyymmdd'),'ddd');
95. 如果修改表名?
alter table old_table_name rename to new_table_name;
96. 如何取得命令的返回狀態值？
sqlcode=0
97. 如何知道使用者擁有的許可權?
SELECT * FROM dba_sys_privs ;
98. 從網上下載的ORACLE9I與市場上賣的標準版有什麼區別？
從功能上說沒有區別，只不過oracle公司有明文規定；從網站上下載的oracle產品不得用於商業用途，否則侵權。
99. 怎樣判斷資料庫是執行在歸檔模式下還是執行在非歸檔模式下？
進入dbastudio，歷程--〉資料庫---〉歸檔檢視。
100. sql>startup pfile和ifile,spfiled有什麼區別？
pfile就是Oracle傳統的初始化引數檔案，文字格式的。
ifile類似於c語言裡的include，用於把另一個檔案引入
spfile是9i裡新增的並且是預設的引數檔案，二進位制格式
startup後應該只可接pfile
101. 如何搜尋出前N條記錄？
SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno;
102. 如何知道機器上的Oracle支援多少併發使用者數?
SQL>conn internal ;
SQL>show parameter processes ;
103. db_block_size可以修改嗎?
一般不可以﹐不建議這樣做的。
104. 如何統計兩個表的記錄總數?
select (select count(id) from aa)+(select count(id) from bb) 總數 from dual;
105. 怎樣用Sql語句實現查詢一列中第N大值？
select * from
(select t.*,dense_rank() over (order by sal) rank from employee)
where rank = N;
106. 如何在給現有的日期加上2年？(
select add_months(sysdate,24) from dual;
107. USED_UBLK為負值表示什麼意思?
It is "harmless".
108. Connect string是指什麼?
應該是tnsnames.ora中的服務名後面的內容
109. 怎樣擴大REDO LOG的大小？
建立一個臨時的redolog組，然後切換日誌，刪除以前的日誌，建立新的日誌。
110. tablespace 是否不能大於4G?
沒有限制.
111. 返回大於等於N的最小整數值?
SELECT CEIL(N) FROM DUAL;
112. 返回小於等於N的最小整數值?
SELECT FLOOR(N) FROM DUAL;
113. 返回當前月的最後一天?
SELECT LAST_DAY(SYSDATE) FROM DUAL;
114. 如何不同使用者間資料匯入?
IMP SYSTEM/MANAGER FILE=AA.DMP FROMUSER=USER_OLD TOUSER=USER_NEW ROWS=Y INDEXES=Y
;
115. 如何找資料庫表的主鍵欄位的名稱?
SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and
table_name='TABLE_NAME';
116. 兩個結果集互加的函式?
SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW;
SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;
SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;
117. 兩個結果集互減的函式?
SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;
118. 如何配置Sequence?
建sequence seq_custid
create sequence seq_custid start 1 incrememt by 1;
建表時:
create table cust
{ cust_id smallint not null,
...}
insert 時:
insert into table cust
values( seq_cust.nextval, ...)
日期的各部分的常用的的寫法
119>.取時間點的年份的寫法:
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
120>.取時間點的月份的寫法:
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;
121>.取時間點的日的寫法:
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;
122>.取時間點的時的寫法:
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;
123>.取時間點的分的寫法:
SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL;
124>.取時間點的秒的寫法:
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL;
125>.取時間點的日期的寫法:
SELECT TRUNC(SYSDATE) FROM DUAL;
126>.取時間點的時間的寫法:
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL;
127>.日期,時間形態變為字元形態
SELECT TO_CHAR(SYSDATE) FROM DUAL;
128>.將字串轉換成日期或時間形態:
SELECT TO_DATE('2003/08/01') FROM DUAL; 129>.返回引數的星期幾的寫法:
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;
130>.返回引數一年中的第幾天的寫法:
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;
131>.返回午夜和引數中指定的時間值之間的秒數的寫法:
SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;
132>.返回引數中一年的第幾周的寫法:
SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL;
虛擬欄位
133. CURRVAL 和 nextval
為表建立序列
CREATE SEQUENCE EMPSEQ ... ;
SELECT empseq.currval FROM DUAL ;
自動插入序列的數值
INSERT INTO emp
VALUES (empseq.nextval, 'LEWIS', 'CLERK',
7902, SYSDATE, 1200, NULL, 20) ;
134. ROWNUM
按設定排序的行的序號
SELECT * FROM emp WHERE ROWNUM < 10 ;
135. ROWID
返回行的實體地址
SELECT ROWID, ename FROM emp WHERE deptno = 20 ;
136. 將N秒轉換為時分秒格式？
set serverout on
declare
N number := 1000000;
ret varchar2(100);
begin
ret := trunc(n/3600) || '小時' || to_char(to_date(mod(n,3600),'sssss'),'fmmi"分
"ss"秒"') ;
dbms_output.put_line(ret);
end;
137. 如何查詢做比較大的排序的程式？
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks ;
138. 如何查詢做比較大的排序的程式的SQL語句？
select /*+ ORDERED */ sql_text from v$sqltext a
where a.hash_value = (
select sql_hash_value from v$session b
where b.sid = &sid and b.serial# = &serial)
order by piece asc ;
139. 如何查詢重複記錄？
SELECT * FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
140. 如何刪除重複記錄？
DELETE FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
141. 如何快速編譯所有檢視？
SQL >SPOOL VIEW1.SQL
SQL >SELECT 'ALTER VIEW '||TNAME||'
COMPILE;' FROM TAB;
SQL >SPOOL OFF
然後執行VIEW1.SQL即可。
SQL >@VIEW1.SQL;
142. ORA-01555 SNAPSHOT TOO OLD的解決辦法
增加MINEXTENTS的值，增加區的大小，設定一個高的OPTIMAL值。
143. 事務要求的回滾段空間不夠，表現為表空間用滿（ORA-01560錯誤），回滾段擴充套件到達
引數 MAXEXTENTS的值（ORA-01628）的解決辦法.
向回滾段表空間新增檔案或使已有的檔案變大；增加MAXEXTENTS的值。
144. 如何加密ORACLE的儲存過程？
下列儲存過程內容放在AA.SQL檔案中
create or replace procedure testCCB(i in number) as
begin
dbms_output.put_line('輸入引數是'||to_char(i));
end;
SQL>wrap iname=a.sql;
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing AA.sql to AA.plb
執行AA.plb
SQL> @AA.plb ;
145. 如何監控事例的等待？
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
146. 如何回滾段的爭用情況？
select name, waits, gets, waits/gets "Ratio"
from v$rollstat C, v$rollname D
where C.usn = D.usn;
147. 如何監控表空間的 I/O 比例？
select B.tablespace_name name,B.file_name "file",A.phyrds pyr,
A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbw
from v$filestat A, dba_data_files B
where A.file# = B.file_id
order by B.tablespace_name;
148. 如何監控檔案系統的 I/O 比例？
select substr(C.file#,1,2) "#", substr(C.name,1,30) "Name",
C.status, C.bytes, D.phyrds, D.phywrts
from v$datafile C, v$filestat D
where C.file# = D.file#;
149. 如何在某個使用者下找所有的索引？
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
150. 如何監控 SGA 的命中率？
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
151. 如何監控 SGA 中字典緩衝區的命中率？
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
152. 如何監控 SGA 中共享快取區的命中率，應該小於1% ？
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload
percent"
from v$librarycache;
153. 如何顯示所有資料庫物件的類別和大小？
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size)
error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
154. 監控 SGA 中重做日誌快取區的命中率，應該小於1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses) *100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
155. 監控記憶體和硬碟的排序比率，最好使它小於 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts
(disk)');
156. 如何監控當前資料庫誰在執行什麼SQL語句？
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
157. 如何監控字典緩衝區？
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM
V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM
V$LIBRARYCACHE;
後者除以前者,此比率小於1%,接近0%為好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
158. 監控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大於0.5時，引數需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where
type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers時，引數需加大
159. 如何知道當前使用者的ID號?
SQL>SHOW USER;
OR
SQL>select user from dual;
160. 如何檢視碎片程度高的表?
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY
segment_name);
162. 如何知道表在表空間中的儲存情況?
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by
tablespace_name,segment_name;
163. 如何知道索引在表空間中的儲存情況?
select segment_name,count(*) from dba_extents where segment_type='INDEX' and
owner='&owner'
group by segment_name;
164、如何知道使用CPU多的使用者session?
11是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40)
prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;
165. 如何知道監聽器日誌檔案?
以8I為例
$ORACLE_HOME/NETWORK/LOG/LISTENER.LOG
166. 如何知道監聽器引數檔案?
以8I為例
$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA
167. 如何知道TNS 連線檔案?
以8I為例
$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA
168. 如何知道Sql*Net 環境檔案?
以8I為例
$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA
169. 如何知道警告日誌檔案?
以8I為例
$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG
170. 如何知道基本結構?
以8I為例
$ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL
171. 如何知道建立資料字典檢視?
以8I為例
$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL
172. 如何知道建立審計用資料字典檢視?
以8I為例
$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL
173. 如何知道建立快照用資料字典檢視?
以8I為例
$ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL
本講主要講的是SQL語句的優化方法! 主要基於ORACLE9I的.
174. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
175. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP'; 176. /*+CHOOSE*/
表明如果資料字典中有訪問表的統計資訊,將基於開銷的優化方法,並獲得最佳的吞吐量;
表明如果資料字典中沒有訪問表的統計資訊,將基於規則開銷的優化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
177. /*+RULE*/
表明對語句塊選擇基於規則的優化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
178. /*+FULL(TABLE)*/
表明對錶選擇全域性掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';
179. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='CCBZZP';
180. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇物件有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
181. /*+INDEX(TABLE INDEX_NAME)*/
表明對錶選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE
BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
182. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.


日期/時間 相關查詢
獲取當前月份的第一天

執行這個命令能快速返回當前月份的第一天。你可以用任何的日期值替換 “SYSDATE”來指定查詢的日期。

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month" 
    FROM DUAL;
獲取當前月份的最後一天

這個查詢語句類似於上面那個語句，而且充分照顧到了閏年，所以當二月份有 29 號，那麼就會返回 29/2 。你可以用任何的日期值替換 “SYSDATE”來指定查詢的日期。

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month" 
    FROM DUAL;
獲取當前年份的第一天

每年的第一天都是1 月1日，這個查詢語句可以使用在儲存過程中，需要對當前年份第一天做一些計算的時候。你可以用任何的日期值替換 “SYSDATE”來指定查詢的日期。

SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;
獲取當前年份的最後一天

類似於上面的查詢語句。你可以用任何的日期值替換 “SYSDATE”來指定查詢的日期。

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL
獲取當前月份的天數

這個語句非常有用，可以計算出當前月份的天數。你可以用任何的日期值替換 “SYSDATE”來指定查詢的日期。

SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days
  FROM DUAL;
獲取當前月份剩下的天數

下面的語句用來計算當前月份剩下的天數。你可以用任何的日期值替換 “SYSDATE”來指定查詢的日期。

SELECT SYSDATE,
       LAST_DAY (SYSDATE) "Last",
       LAST_DAY (SYSDATE) - SYSDATE "Days left"
  FROM DUAL;
獲取兩個日期之間的天數

使用這個語句來獲取兩個不同日期自檢的天數。

SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0)
          num_of_days
  FROM DUAL;

OR

SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;
如果你需要查詢一些特定日期的天數，可以使用第二個查詢語句。這個例子是計算員工入職的天數。

顯示當前年份截止到上個月每個月份開始和結束的日期

這個是個很聰明的查詢語句，用來顯示當前年份每個月的開始和結束的日期，你可以使用這個進行一些型別的計算。你可以用任何的日期值替換 “SYSDATE”來指定查詢的日期。

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,
       TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date
  FROM XMLTABLE (
          'for $i in 0 to xs:int(D) return $i'
          PASSING XMLELEMENT (
                     d,
                     FLOOR (
                        MONTHS_BETWEEN (
                           ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),
                           SYSDATE)))
          COLUMNS i INTEGER PATH '.');
獲取直到目前為止今天過去的秒數（從 00：00 開始算）

SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning
  FROM DUAL;
獲取今天剩下的秒數（直到 23：59：59 結束）

SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left
  FROM DUAL;
資料字典查詢

檢查在當前資料庫模式下是否存在指定的表

這是一個簡單的查詢語句，用來檢查當前資料庫是否有你想要建立的表，允許你重新執行建立表指令碼，這個也可以檢查當前使用者是否已經建立了指定的表（根據這個查詢語句在什麼環境下執行來查詢）。

SELECT table_name
  FROM user_tables
 WHERE table_name = 'TABLE_NAME';
檢查在當前表中是否存在指定的列

這是個簡單的查詢語句來檢查表裡是否有指定的列，在你嘗試使用 ALTER TABLE 來新增新的列新到表中的時候非常有用，它會提示你是否已經存在這個列。

SELECT column_name AS FOUND
  FROM user_tab_cols
 WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';
顯示錶結構

這 個查詢語句會顯示任何表的 DDL 狀態資訊。請注意我們已經將‘TABLE’作為第一個資訊提交了。這個查詢語句也可以用來獲取任何資料庫物件的 DDL 狀態資訊。舉例說明，只需要把第一個引數替換成‘VIEW’，第二個修改成檢視的名字，就可以查詢檢視的 DDL 資訊了。

SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;
獲取當前模式

這是另一個可以獲得當前模式的名字的查詢語句。

SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;
修改當前模式

這是另一個可以修改當前模式的查詢語句，當你希望你的指令碼可以在指定的使用者下執行的時候非常有用，而且這是非常安全的一個方式。

ALTER SESSION SET CURRENT_SCHEMA = new_schema;
資料庫管理查詢

資料庫版本資訊

返回 Oracle 資料庫版本

SELECT * FROM v$version;
資料庫預設資訊

返回一些系統預設的資訊

SELECT username,
       profile,
       default_tablespace,
       temporary_tablespace
  FROM dba_users;
資料庫字元設定資訊

顯示資料庫的字元設定資訊

SELECT * FROM nls_database_parameters;
獲取 Oracle 版本

SELECT VALUE
  FROM v$system_parameter
 WHERE name = 'compatible';
儲存區分大小寫的資料，但是索引不區分大小寫

某些時候你可能想在資料庫中查詢一些獨立的資料，可能會用 UPPER(..) = UPPER(..) 來進行不區分大小寫的查詢，所以就想讓索引不區分大小寫，不佔用那麼多的空間，這個語句恰好能解決你的需求 。

CREATE TABLE tab (col1 VARCHAR2 (10));

CREATE INDEX idx1
   ON tab (UPPER (col1));

ANALYZE TABLE a COMPUTE STATISTICS;
調整沒有新增資料檔案的表空間

另一個 DDL 查詢來調整表空間大小

ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;
檢查表空間的自動擴充套件開關

在給定的表空間中查詢是否開啟了自動擴充套件開關

SELECT SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;

(OR)

SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;
在表空間新增資料檔案

在表空間中新增資料檔案

ALTER TABLESPACE data01 ADD DATAFILE '/work/oradata/STARTST/data01.dbf'
    SIZE 1000M AUTOEXTEND OFF;
增加資料檔案的大小

給指定的表空間增加大小

ALTER DATABASE DATAFILE '/u01/app/Test_data_01.dbf' RESIZE 2G;
查詢資料庫的實際大小

給出以 GB 為單位的資料庫的實際大小

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;
查詢資料庫中資料佔用的大小或者是資料庫使用細節

給出在資料庫中資料佔據的空間大小

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;
查詢模式或者使用者的大小

以 MB 為單位給出使用者的空間大小

SELECT SUM (bytes / 1024 / 1024) "size"
  FROM dba_segments
 WHERE owner = '&owner';
查詢資料庫中每個使用者最後使用的 SQL 查詢

此查詢語句會顯示當前資料庫中每個使用者最後使用的 SQL 語句。

SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,
         s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,
         s.sid || '/' || s.serial# sid,
         s.status "Status",
         p.spid,
         sql_text sqltext
    FROM v$sqltext_with_newlines t, V$SESSION s, v$process p
   WHERE     t.address = s.sql_address
         AND p.addr = s.paddr(+)
         AND t.hash_value = s.sql_hash_value
ORDER BY s.sid, t.piece;
效能相關查詢

查詢使用者 CPU 的使用率
這個語句是用來顯示每個使用者的 CPU 使用率，有助於使用者理解資料庫負載情況

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;
查詢資料庫長查詢進展情況

顯示執行中的長查詢的進展情況

SELECT a.sid,
         a.serial#,
         b.username,
         opname OPERATION,
         target OBJECT,
         TRUNC (elapsed_seconds, 5) "ET (s)",
         TO_CHAR (start_time, 'HH24:MI:SS') start_time,
         ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
    FROM v$session_longops a, v$session b
   WHERE     a.sid = b.sid
         AND b.username NOT IN ('SYS', 'SYSTEM')
         AND totalwork > 0
ORDER BY elapsed_seconds;
獲取當前會話 ID，程式 ID，客戶端 ID 等

這個專門提供給想使用程式 ID 和 會話 ID 做些 voodoo magic 的使用者。

SELECT b.sid,
       b.serial#,
       a.spid processid,
       b.process clientpid
  FROM v$process a, v$session b
 WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');
V$SESSION.SID AND V$SESSION.SERIAL# 是資料庫程式 ID

V$PROCESS.SPID 是資料庫伺服器後臺程式 ID

V$SESSION.PROCESS 是客戶端 PROCESS ID, ON windows it IS : separated THE FIRST # IS THE PROCESS ID ON THE client AND 2nd one IS THE THREAD id.

查詢特定的模式或者表中執行的最後一個 SQL 語句

SELECT CREATED, TIMESTAMP, last_ddl_time
  FROM all_objects
 WHERE     OWNER = 'MYSCHEMA'
       AND OBJECT_TYPE = 'TABLE'
       AND OBJECT_NAME = 'EMPLOYEE_TABLE';
查詢每個執行讀取的前十個 SQL

SELECT *
  FROM (  SELECT ROWNUM,
                 SUBSTR (a.sql_text, 1, 200) sql_text,
                 TRUNC (
                    a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
                    reads_per_execution,
                 a.buffer_gets,
                 a.disk_reads,
                 a.executions,
                 a.sorts,
                 a.address
            FROM v$sqlarea a
        ORDER BY 3 DESC)
 WHERE ROWNUM < 10;
在檢視中查詢並顯示實際的 Oracle 連線

SELECT osuser,
         username,
         machine,
         program
    FROM v$session
ORDER BY osuser;
查詢並顯示通過開啟連線程式開啟連線的組

SELECT program application, COUNT (program) Numero_Sesiones
    FROM v$session
GROUP BY program
ORDER BY Numero_Sesiones DESC;
查詢並顯示連線 Oracle 的使用者和使用者的會話數量

SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones
    FROM v$session
GROUP BY username
ORDER BY Numero_Sesiones DESC;
獲取擁有者的物件數量

SELECT owner, COUNT (owner) number_of_objects
    FROM dba_objects
GROUP BY owner
ORDER BY number_of_objects DESC;
實用/數學 相關的查詢

把數值轉換成文字
更多資訊可以檢視： Converting number into words in Oracle

SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;
輸出:

one thousand five hundred twenty-six
在包的原始碼中查詢字串

這個查詢語句會在所有包的原始碼上搜尋‘FOO_SOMETHING’ ，可以幫助使用者在原始碼中查詢特定的儲存過程或者是函式呼叫。

--search a string foo_something in package source code
SELECT *
  FROM dba_source
 WHERE UPPER (text) LIKE '%FOO_SOMETHING%' 
AND owner = 'USER_NAME';
把用逗號分隔的資料插入的表中

當 你想把用逗號分隔開的字串插入表中的時候，你可以使用其他的查詢語句，比如 IN 或者是 NOT IN 。這裡我們把‘AA,BB,CC,DD,EE,FF’轉換成包含 AA,BB，CC 等作為一行的表，這樣你就很容易把這些字串插入到其他表中，並快速的做一些相關的操作。

WITH csv
     AS (SELECT 'AA,BB,CC,DD,EE,FF'
                   AS csvdata
           FROM DUAL)
    SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char
      FROM DUAL, csv
CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL;
查詢表中的最後一個記錄

這個查詢語句很直接，表中沒有主鍵，或者是使用者不確定記錄最大主鍵是否是最新的那個記錄時，就可以使用這個語句來查詢表中最後一個記錄。

SELECT *
  FROM employees
 WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

(OR)

SELECT * FROM employees
MINUS
SELECT *
  FROM employees
 WHERE ROWNUM < (SELECT COUNT (*) FROM employees);
在 Oracle 中做行資料乘法

這個查詢語句使用一些複雜的數學函式來做每個行的數值乘法。更多內容請查閱： Row Data Multiplication In Oracle

WITH tbl
     AS (SELECT -2 num FROM DUAL
         UNION
         SELECT -3 num FROM DUAL
         UNION
         SELECT -4 num FROM DUAL),
     sign_val
     AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val
           FROM tbl
          WHERE num < 0)
  SELECT EXP (SUM (LN (ABS (num)))) * val
    FROM tbl, sign_val
GROUP BY val;
在 Oracle 生成隨機資料

每個開發者都想能輕鬆生成一堆隨機資料來測試資料庫多好，下面這條查詢語句就可以滿足你，它可以在 Oracle 中生成隨機的資料插入到表中。詳細資訊可以檢視 Random Data in Oracle

SELECT LEVEL empl_id,
           MOD (ROWNUM, 50000) dept_id,
           TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
           DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)),  1, 'M',  2, 'F') gender,
           TO_DATE (
                 ROUND (DBMS_RANDOM.VALUE (1, 28))
              || '-'
              || ROUND (DBMS_RANDOM.VALUE (1, 12))
              || '-'
              || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
              'DD-MM-YYYY')
              dob,
           DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address
      FROM DUAL
CONNECT BY LEVEL < 10000;
在 Oracle 中生成隨機數值

這是 Oracle 普通的舊的隨機數值生成器。這個可以生成 0-100 之間的隨機數值，如果你想自己設定數值範圍，那麼改變乘數就可以了。

--generate random number between 0 and 100
SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;
檢查表中是否含有任何的資料

這個可以有很多中寫法，你可以使用 count(*) 來檢視錶裡的行的數量，但是這個查詢語句比較高效和快速，而且我們只是想知道表裡是否有任何的資料。

SELECT 1
  FROM TABLE_NAME
 WHERE ROWNUM = 1;

Oracle查詢語句
select * from scott.emp ;


1.--dense_rank()分析函式（查詢每個部門工資最高前三名員工資訊）
select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) a from scott.emp) where a<=3 order by deptno asc,sal desc ;
結果：

--rank()分析函式(執行結果與上語句相同)
select * from (select deptno,ename,sal,rank() over(partition by deptno order by sal desc) a from scott.emp ) where a<=3 order by deptno asc,sal desc ;
結果：

--row_number()分析函式（執行結果與上相同）
select * from(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) a from scott.emp) where a<=3 order by deptno asc,sal desc ;
--rows unbounded preceding 分析函式（顯示各部門的積累工資總和）
select deptno,sal,sum(sal) over(order by deptno asc rows unbounded preceding) 積累工資總和 from scott.emp ;
結果：

--rows 整數值 preceding(顯示每最後4條記錄的彙總值)
select deptno,sal,sum(sal) over(order by deptno rows 3 preceding) 每4彙總值 from scott.emp ;
結果：

--rows between 1 preceding and 1 following（統計3條記錄的彙總值【當前記錄居中】）
select deptno,ename,sal,sum(sal) over(order by deptno rows between 1 preceding and 1 following) 彙總值 from scott.emp ;
結果：

--ratio_to_report(顯示員工工資及佔該部門總工資的比例)
select deptno,sal,ratio_to_report(sal) over(partition by deptno) 比例 from scott.emp ;
結果：

--檢視所有使用者
select * from dba_users ;
select count(*) from dba_users ;
select * from all_users ;
select * from user_users ;
select * from dba_roles ;
--檢視使用者系統許可權
select * from dba_sys_privs ;
select * from user_users ;
--檢視使用者物件或角色許可權
select * from dba_tab_privs ;
select * from all_tab_privs ;
select * from user_tab_privs ;
--檢視使用者或角色所擁有的角色
select * from dba_role_privs ;
select * from user_role_privs ;
-- rownum:查詢10至12資訊
select * from scott.emp a where rownum<=3 and a.empno not in(select b.empno from scott.emp b where rownum<=9);
結果：

--not exists；查詢emp表在dept表中沒有的資料
select * from scott.emp a where not exists(select * from scott.dept b where a.empno=b.deptno) ;
結果：

--rowid;查詢重複資料資訊
select * from scott.emp a where a.rowid>(select min(x.rowid) from scott.emp x where x.empno=a.empno);
--根據rowid來分頁(一萬條資料，查詢10000至9980時間大概在0.03秒左右)
select * from scott.emp where rowid in(select rid from(select rownum rn,rid from(select rowid rid,empno from scott.emp order by empno desc) where rownum<10)where rn>=1)order by empno desc ;
結果：

--根據分析函式分頁(一萬條資料，查詢10000至9980時間大概在1.01秒左右)
select * from(select a.*,row_number() over(order by empno desc) rk from scott.emp a ) where rk<10 and rk>=1;
結果：

--rownum分頁(一萬條資料，查詢10000至9980時間大概在0.01秒左右)
select * from(select t.*,rownum rn from(select * from scott.emp order by empno desc)t where rownum<10) where rn>=1;
select * from(select a.*,rownum rn from (select * from scott.emp) a where rownum<=10) where rn>=5 ;
--left outer join:左連線
select a.*,b.* from scott.emp a left outer join scott.dept b on a.deptno=b.deptno ;
--right outer join:右連線
select a.*,b.* from scott.emp a right outer join scott.dept b on a.deptno=b.deptno ;
--inner join
select a.*,b.* from scott.emp a inner  join scott.dept b on a.deptno=b.deptno ;
--full join
select a.*,b.* from scott.emp a full join scott.dept b on a.deptno=b.deptno ;
select a.*,b.* from scott.emp a,scott.dept b where a.deptno(+)=b.deptno ;
select distinct ename,sal from scott.emp a group by sal having ;
select * from scott.dept ;
select * from scott.emp ;
--case when then end (交叉報表)
select ename,sal,case deptno when 10 then '會計部' when 20 then '研究部' when 30 then '銷售部' else '其他部門' end 部門 from scott.emp ;
結果：

select ename,sal,case when sal>0 and sal<1500 then '一級工資' when sal>=1500 and sal<3000 then '二級工資' when sal>=3000 and sal<4500 then '三級工資' else '四級工資' end 工資等級 from scott.emp order by sal desc ;
結果：

--交叉報表是使用分組函式與case結構一起實現
select 姓名,sum(case 課程 when '數學' then 分數 end)數學,sum(case 課程 when '歷史' then 分數 end)歷史 from 學生 group by 姓名 ;
--decode 函式
select 姓名,sum(decode(課程,'數學',分數,null))數學,sum(decode(課程,'語文',分數,null))語文,sum(decode(課程,'歷史','分數',null))歷史 from 學生 group by 姓名 ;
--level。。。。connect by(層次查詢)
select level,emp.* from scott.emp connect by prior empno = mgr order by level ;
結果：

--sys_connect_by_path函式
select ename,sys_connect_by_path(ename,'/') from scott.emp start with mgr is null connect by prior empno=mgr ;
結果：

--start with connect by prior 語法
select lpad(ename,3*(level),'')姓名,lpad(ename,3*(level),'')姓名 from scott.emp where job<>'CLERK' start with mgr is null connect by prior mgr = empno ;
--level與prior關鍵字
select level,emp.* from scott.emp start with ename='SCOTT' connect by prior empno=mgr;
select level,emp.* from scott.emp start with ename='SCOTT' connect by empno = prior mgr ;
結果：

--等值連線
select empno,ename,job,sal,dname from scott.emp a,scott.dept b where a.deptno=b.deptno and (a.deptno=10 or sal>2500);
結果：

--非等值連線
select a.ename,a.sal,b.grade from scott.emp a,scott.salgrade b where a.sal between b.losal and b.hisal ;
結果：

--自連線
select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno ;
結果：

--左外連線
select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno(+);
結果：

--多表連線
select * from scott.emp ,scott.dept,scott.salgrade where scott.emp.deptno=scott.dept.deptno and scott.emp.sal between scott.salgrade.losal and scott.salgrade.hisal ;
結果：

select * from scott.emp a join scott.dept b on a.deptno=b.deptno join scott.salgrade s on a.sal between s.losal and s.hisal where a.sal>1000;
select * from(select * from scott.emp a join scott.dept b on a.deptno=b.deptno where a.sal>1000) c join scott.salgrade s on c.sal between s.losal and s.hisal ;
--單行子查詢
select * from scott.emp a where a.deptno=(select deptno from scott.dept where loc='NEW YORK');
select * from scott.emp a where a.deptno in (select deptno from scott.dept where loc='NEW YORK');
結果：

--單行子查詢在 from 後
select scott.emp.*,(select deptno from scott.dept where loc='NEW YORK') a from scott.emp ;

--使用 in ,all,any 多行子查詢
--in：表示等於查詢出來的對應資料
select ename,job,sal,deptno from scott.emp where job in(select distinct job from scott.emp where deptno=10);
--all:表示大於所有括號中查詢出來的對應的資料資訊
select ename,sal,deptno from scott.emp where sal>all(select sal from scott.emp where deptno=30);
--any:表示大於括號查詢出來的其中任意一個即可（只隨機一個）
select ename,sal,deptno from scott.emp where sal>any(select sal from scott.emp where deptno=30);

--多列子查詢
select ename,job,sal,deptno from scott.emp where(deptno,job)=(select deptno,job from scott.emp where ename='SCOTT');
select ename,job,sal,deptno from scott.emp where(sal,nvl(comm,-1)) in(select sal,nvl(comm,-1) from scott.emp where deptno=30);
--非成對比較
select ename,job,sal,deptno from scott.emp where sal in(select sal from scott.emp where deptno=30) and nvl(comm,-1) in(select nvl(comm,-1) from scott.emp where deptno=30);

--其他子查詢
select ename,job,sal,deptno from scott.emp where exists(select null from scott.dept where scott.dept.deptno=scott.emp.deptno and scott.dept.loc='NEW YORK');
select ename,job,sal from scott.emp join(select deptno,avg(sal) avgsal,null from scott.emp group by deptno) dept on emp.deptno=dept.deptno where sal>dept.avgsal ;
create table scott.test(
       ename varchar(20),
       job varchar(20)
);
--drop table test ;
select * from scott.test ;
--Insert與子查詢(表間資料的拷貝)
insert into scott.test(ename,job) select ename,job from scott.emp ;
--Update與子查詢
update scott.test set(ename,job)=(select ename,job from scott.emp where ename='SCOTT' and deptno ='10');
--建立表時，還可以指定列名
create table scott.test_1(ename,job) as select ename,job from scott.emp ;
select * from scott.test_1 ;
--delete與子查詢
delete from scott.test where ename in('');
--合併查詢
--union語法（合併且去除重複行，且排序）
select ename,sal,deptno from scott.emp where deptno>10 union select ename,sal,deptno from scott.emp where deptno<30 ;
select a.deptno from scott.emp a union select b.deptno from scott.dept b ;
--union all(直接將兩個結果集合並，不排序)
select ename,sal,deptno from scott.emp where deptno>10 union all select ename,sal,deptno from scott.emp where deptno<30 ;
select a.deptno from scott.emp a union all select b.deptno from scott.dept b ;
--intersect:取交集
select ename,sal,deptno from scott.emp where deptno>10 intersect select ename,sal,deptno from scott.emp where deptno<30;
--顯示部門工資總和高於僱員工資總和三分之一的部門名及工資總和
select dname as 部門,sum(sal) as 工資總和 from scott.emp a,scott.dept b where a.deptno=b.deptno group by dname having sum(sal)>(select sum(sal)/3 from scott.emp c,scott.dept d where c.deptno=d.deptno);
結果：

--使用with得到以上同樣的結果
with test as (select dname ,sum(sal) sumsal  from scott.emp ,scott.dept where scott.emp.deptno=scott.dept.deptno group by dname) select dname as 部門,sumsal as 工資總和 from scott.test where sumsal>(select sum(sumsal)/3 from scott.test);
結果：

--分析函式
select ename,sal,sum(sal) over(partition by deptno order by sal desc) from scott.emp ;
--rows n preceding(視窗子句一)
select deptno,sal,sum(sal) over(order by sal rows 5 preceding) from scott.emp ;
結果：

--rum(..) over(..)..
select sal,sum(1) over(order by sal) aa from scott.emp  ;
select deptno,ename,sal,sum(sal) over(order by ename) 連續求和,sum(sal) over() 總和,100*round(sal/sum(sal) over(),4) as 份額 from scott.emp;
結果：

select deptno,ename,sal,sum(sal) over(partition by deptno order by ename) 部門連續求和,sum(sal) over(partition by deptno) 部門總和,100*round(sal/sum(sal) over(),4) as 總份額 from scott.emp;
結果：

select deptno,sal,rank() over (partition by deptno order by sal),dense_rank() over(partition by deptno order by sal) from scott.emp order by deptno ;
結果；

select * from (select rank() over(partition by 課程 order by 分數 desc) rk,分析函式_rank.* from 分析函式_rank) where rk<=3 ;
--dense_rank():有重複的數字不跳著排列
--row_number()
select deptno,sal,row_number() over(partition by deptno order by sal) rm from scott.emp ;
結果：

--lag()和lead()
select deptno,sal,lag(sal) over(partition by deptno order by sal) 上一個,lead(sal) over(partition by deptno order by sal) from scott.emp ;
結果：

--max(),min(),avg()
select deptno,sal,max(sal) over(partition by deptno order by sal)最大,min(sal) over(partition by deptno order by sal)最小,avg(sal) over(partition by deptno order by sal)平均 from scott.emp ;
結果：

--first_value(),last_value()
select deptno,sal,first_value(sal) over(partition by deptno)最前,last_value(sal) over(partition by deptno )最後 from scott.emp ;
結果：

--分組補充 group by grouping sets
select deptno ,sal,sum(sal) from scott.emp group by grouping sets(deptno,sal);
select null,sal,sum(sal) from scott.emp group by sal union all select deptno,null,sum(sal) from scott.emp group by deptno ;
結果：

--rollup
select deptno,job,avg(sal) from scott.emp group by rollup(deptno,job) ;
--理解rollup等價於
select deptno,job,avg(sal) from scott.emp group by deptno,job union select deptno ,null,avg(sal) from scott.emp group by deptno union select null,null,avg(sal) from scott.emp ;
結果：

select deptno,job,avg(sal) a from scott.emp group by cube(deptno,job) ;
--理解CUBE
select deptno,job,avg(sal) from scott.emp group by cube(deptno,job) ;
--等價於
select deptno,job,avg(sal) from scott.emp group by grouping sets((deptno,job),(deptno),(job),());
結果：

--查詢工資不在1500至2850之間的所有僱員名及工資
select ename,sal from scott.emp where sal not in(select sal from scott.emp where sal between 1500 and 2850 );
--部門10和30中的工資超過1500的僱員名及工資
select deptno,ename,sal from scott.emp a where a.deptno in(10,30) and a.sal>1500 order by sal desc ;
結果：

--在1981年2月1日至1981年5月1日之間僱傭的僱員名，崗位及僱傭日期，並以僱傭日期先後順序排序
select ename as 姓名,job as 崗位,hiredate as 僱傭日期 from scott.emp a where a.hiredate between to_date('1981-02-01','yyyy-mm-dd') and to_date('1981-05-01','yyyy-mm-dd') order by a.hiredate asc ;
結果：

select * from scott.emp where hiredate >to_date('1981-02-01','yyyy-MM-dd');

--查詢獲得補助的所有僱傭名，工資及補助額，並以工資和補助的降序排序
select ename,sal,comm from scott.emp a where a.comm > all(0) order by comm desc;
--工資低於1500的員工增加10%的工資，工資在1500及以上的增加5%的工資並按工資高低排序（降序）
select ename as 員工姓名,sal as 補發前的工資,case when sal<1500 then (sal+sal*0.1) else (sal+sal*0.05) end 補助後的工資 from scott.emp order by sal desc ;
結果：

--查詢公司每天，每月，每季度，每年的資金支出數額
select sum(sal/30) as 每天發的工資,sum(sal) as 每月發的工資,sum(sal)*3 as 每季度發的工資,sum(sal)*12 as 每年發的工資 from scott.emp;
結果：

--查詢所有員工的平均工資，總計工資，最高工資和最低工資
select avg(sal) as 平均工資,sum(sal) as 總計工資,max(sal) as 最高工資,min(sal) as 最低工資 from scott.emp;
結果：

--每種崗位的僱員總數和平均工資
select job as 崗位,count(job) as 崗位僱員總數,avg(sal) as 平均工資 from scott.emp group by job order by 平均工資 desc;
結果：

--僱員總數以及獲得補助的僱員數
select count(*) as 公司僱員總數,count(comm) as 獲得補助的僱員人數 from scott.emp ;
--管理者的總人數
--僱員工資的最大差額
select max(sal),min(sal),(max(sal) - min(sal)) as 員工工資最大差額 from scott.emp ;
--每個部門的平均工資
select deptno,avg(sal) from scott.emp a group by a.deptno;
結果：

--查詢每個崗位人數超過2人的所有職員資訊
select * from scott.emp a,(select c.job,count(c.job) as sl from scott.emp c group by c.job ) b where b.sl>2 and a.job=b.job;
結果：

select * from scott.emp a where a.empno in(select mgr from scott.emp ) and (select count(mgr) from scott.emp)>2 ;
結果：

--處理重複行資料資訊（刪除，查詢，修改）
select * from a1 a where not exists (select b.rd from (select rowid rd,row_number() over(partition by LOAN, BRANCH order by BEGIN_DATE desc) rn from a1) b where b.rn = 1 and a.rowid = b.rd);
--查詢emp表資料資訊重複問題
select * from scott.emp a where exists(select b.rd from(select rowid rd,row_number() over(partition by ename,job,mgr,hiredate,sal,comm,deptno order by empno asc) rn from scott.emp) b where b.rn=1 and a.rowid=b.rd);
--initcap：返回字串，字串第一個字母大寫
select initcap(ename) Upp from scott.emp ;
結果：

--ascii:返回與指定的字元對應的十進位制數
select ascii(a.empno) as 編號,ascii(a.ename) as 姓名,ascii(a.job) as 崗位 from scott.emp a ;
結果：

--chr:給出整數，返回對應的字元
select chr(ascii(ename)) as 姓名 from scott.emp ;
結果：

--concat:連線字串
select concat(a.ename,a.job)|| a.empno as 字元連線 from scott.emp a;
結果：

--instr:在一個字串中搜尋指定的字元,返回發現指定的字元的位置
select instr(a.empno,a.mgr,1,1) from scott.emp a ;
--length:返回字串的長度
select ename,length(a.ename) as 長度,a.job,length(a.job) as 長度 from scott.emp a ;
--lower：返回字串，並將所返回的字元小寫
select a.ename as 大寫,lower(a.ename) as 小寫 from scott.emp a ;
結果：

--upper：返回字串，並將返回字串都大寫
select lower(a.ename) as 小寫名字,upper(a.ename) as 大寫名字 from scott.emp a ;
結果：

--rpad:在列的右邊貼上字元，lpad: 在列的左邊貼上字元(不夠字元則用*來填滿)
select lpad(rpad(a.ename,10,'*'),16,'*') as 貼上 from scott.emp a ;
結果：

--like不同角度的使用
select * from scott.emp where ename like '%XXR%';
select * from scott.emp where ename like '%S';
select * from scott.emp where ename like 'J%';
select * from scott.emp where ename like 'S';
select * from scott.emp where ename like '%S_';
--每個部門的工資總和
select a.ename,sum(sal) from scott.emp a group by ename;
--每個部門的平均工資
select a.deptno,avg(sal) from scott.emp a group by deptno ;
--每個部門的最大工資
select a.deptno,max(sal) from scott.emp a group by deptno ;
--每個部門的最小工資
select a.deptno,min(sal) from scott.emp a group by deptno ;
--查詢原工資佔部門工資的比率
select deptno ,sal,ratio_to_report(sal) over(partition by deptno) sal_ratio from scott.emp ;
--查詢成績不及格的所有學生資訊（提示：沒有對應的表，只是意思意思。不及格人數大於等於三才能查）
select * from scott.emp where empno in(select distinct empno from scott.emp where 3<(select count(sal) from scott.emp where sal<3000) and empno in(select empno from scott.emp where sal<3000));
結果：

--查詢每個部門的平均工資
select distinct deptno,avg(sal) from scott.emp group by deptno  order by deptno desc;
--union組合查出的結果，但要求查出來的資料型別必須相同
select sal from scott.emp where sal >=all(select sal from scott.emp ) union select sal from scott.emp ;
select * from scott.emp a where a.empno between 7227 and 7369 ;--只能從小到大
---------建立表空間  要用擁有create tablespace許可權的使用者，比如sys
create tablespace tbs_dat datafile 'c:\oradata\tbs_dat.dbf' size 2000M;
---------新增資料檔案
alter tablespace tbs_dat add datafile 'c:\oradata\tbs_dat2.dbf' size 100M;
---------改變資料檔案大小
alter database datafile 'c:\oradata\tbs_dat.dbf' resize 250M;
---------資料檔案自動擴充套件大小
alter database datafile 'c:\oradata\tbs_dat.dbf' autoextend on next 1m maxsize 20m;
---------修改表空間名稱
alter tablespace tbs_dat rename to tbs_dat1;
---------刪除表空間  and datafiles 表示同時刪除物理檔案
drop tablespace tbs_dat including contents and datafiles;
--substr(s1,s2,s3)：擷取s1字串,從s2開始,結束s3
select substr(job,3,length(job)) from scott.emp ;
--replace：替換字串
select replace(ename,'LL','aa') from scott.emp;
select * from scott.test;
insert into scott.test(ename,job) values('weather','好');
insert into scott.test(ename,job) values('wether','差');
--soundex：返回一個與給定的字串讀音相同的字串
select ename from scott.test where soundex(ename)=soundex('wether');
--floor：取整數
select sal,floor(sal) as 整數 from scott.emp ;
--log（n,s）：返回一個以n為低，s的對數
select empno,log(empno,2) as 對數 from scott.emp ;
--mod(n1,n2)：返回一個n1除以n2的餘數
select empno,mod(empno,2) as 餘數 from scott.emp ;
結果：

--power(n1,n2)：返回n1的n2次方根
select empno,power(empno,2) as 方根 from scott.emp ;
--round和trunc：按照指定的精度進行舍入
select round(41.5),round(-41.8),trunc(41.6),trunc(-41.9) from scott.emp ;
--sign：取數字n的符號,大於0返回1,小於0返回-1,等於0返回0
select sign(45),sign(-21),sign(0) from scott.emp ;
結果：

select * from scott.emp;
oracle相關的資料庫SQL查詢語句：
1.  在職員表中查詢出基本工資比平均基本工資高的職工編號。
2.  查詢一個或者多個部門的所有員工資訊，該部門的所有員工工資都高於公司的平均工資。
3.  現有張三的出生日期：1985-01-15 01:27:36，請各自新建表，將此日期時間插入表中，並計算出張三的年齡，顯示張三的生日。
4.  生日的輸出格式要求為MM-DD（未滿兩位的用0不全），張三的生日為01-15。
5.  算年齡要求用三個方式實現。
6.  生日要求用兩個方式實現。
7.  在資料庫表中有以下字元資料，如：
13-1,14-2,13-15，13-2，13-108，13-3，13-10,13-200,13-18,100-11,14-1
現在希望通過一條SQL語句進行排序，並且首先要按照前半部分的數字進行排序，然後再按照後半部分的數字進行排序，輸出要拍成如下所示：
13-1,13-2,13-3,13-10,13-15,13-18,13-108,13-200,14-1,14-2,100-11
資料庫表名：SellRecord;欄位ListNumber;
8.  顯示所有僱員的姓名以及滿10年服務年限後的日期。
9.  顯示僱員姓名，根據其服務年限，將最老的僱員排在最前面。
10顯示所有僱員的姓名和加入公司的年份和月份，按僱員受僱日期所在月排序，將最早年份的職員排在最前面。
10.             顯示假設一個月為30天的情況下所有僱員的日薪金。
11.             找出在（任何年份的）2月受聘的所有僱員（用兩種方式實現）。
12.             對於每個僱員，顯示其加入公司的天數。
13.             以年，月和日的方式顯示所有僱員的服務年限（入職多少年/入職了多少月/入職了多少天）。
14.             找出各月最後一天受僱的所有僱員。
15.             找出早於25年之前受僱的僱員（用兩種方式實現）。
16.             工資最低1500的職員增加10%，1500以上的增加5%的工資，用一條update語句實現（用兩種方式實現）。
17.             按照部門統計每種崗位的平均工資，要求輸出的格式如下圖所示：


部落格分類： oracle
oracleselectjoincountunion 
 

1、當在select語句中，同時使用where、group by和having子句時，它們的作用和執行順序如下：
（1）where用於篩選由from指定的資料物件
（2）group by用於對where的結果進行分組
（3）having用於對group by分組後的資料進行過濾

2、count(*)與count(columnName|columnIndex)的區別：
count(*)會統計表中的null計算在內，而count(columnName|columnIndex)會忽略對應欄位的null記錄。

3、以join關鍵字指定的連線主要有四種：inner join、left outer join、right outer join和 full outer join。
（1）inner join是內連線，是系統預設的連線方式，可以省略inner，它會把所有滿足條件的記錄列出來。
（2）left outer join是左外連線，其中outer可以省略，它除了列出所有滿足條件的記錄外，還包括左表的所有行。
（3）right outer join是右外連線，其中outer可以省略，它除了列出所有滿足條件的記錄外，還包括右表的所有行。
（4）full outer join是全外連線，其中outer可以省略，它除了列出所有滿足條件的記錄外，還包括左表和右表的所有行。

4、select中的union子句
使用union子句可以將兩個或兩個以上的查詢結果合併成一個結果集。其語法格式如下：
(query expression) union [all] (query expression) [union [all] (query expression)]
--使用關鍵字all表示合併的結果集中包括所有行，不去除重複行，而不使用all關鍵字則會去除重複行。
使用union的基本規則是：
（1）所有查詢中的列數和列的順序必須相同
（2）對應列的資料型別必須相容


1、 檢視當前使用者的預設表空間
SQL>select username,default_tablespace from user_users;
2、 檢視當前使用者的角色
SQL>select * from user_role_privs;
3、 檢視當前使用者的系統許可權和表級許可權
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
4、 檢視使用者下所有的表
SQL>select * from user_tables;
5、 檢視使用者下所有的表的列屬性
SQL>select * from USER_TAB_COLUMNS where table_name=:table_Name;
6、 顯示使用者資訊(所屬表空間)
selectdefault_tablespace, temporary_tablespace
fromdba_users
where username = 'GAME';
7、 顯示當前會話所具有的許可權
SQL>select * from session_privs;
8、 顯示指定使用者所具有的系統許可權
SQL>select * from dba_sys_privs where grantee='GAME';
9、 顯示特權使用者
select * from v$pwfile_users;
10、 顯示使用者資訊(所屬表空間)
selectdefault_tablespace,temporary_tablespace
fromdba_users where username='GAME';
11、 顯示使用者的PROFILE
select profile from dba_users where username='GAME';
二、 表
1、 檢視使用者下所有的表
SQL>select * from user_tables;
2、 檢視名稱包含log字元的表
SQL>select object_name,object_id from user_objects
whereinstr(object_name,'LOG')>0;
3、 檢視某表的建立時間
SQL>select object_name,created from user_objects
object_name=upper('&table_name');
4、 檢視某表的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
wheresegment_name=upper('&table_name'); where

5、 檢視放在Oracle的記憶體區裡的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;
三、 索引
1、 檢視索引個數和類別
SQL>select index_name,index_type,table_name from user_indexes order by table_name;
2、 檢視索引被索引的欄位
SQL>select * from user_ind_columns where index_name=upper('&index_name');
3、 檢視索引的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
wheresegment_name=upper('&index_name');
四、 序列號
1、 檢視序列號，last_number是當前值
SQL>select * from user_sequences;
五、 檢視
1、 檢視檢視的名稱
SQL>select view_name from user_views;
2、 檢視建立檢視的select語句
SQL>set view_name,text_length from user_views;
SQL>set long 2000; 說明：可以根據檢視的text_length值設定set long 的大小
SQL>select text from user_views where view_name=upper('&view_name');
六、 同義詞
1、 檢視同義詞的名稱
SQL>select * from user_synonyms;
七、 約束條件
1、 檢視某表的約束條件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
fromuser_constraints where table_name = upper('&table_name');
SQL>select c.constraint_name,c.constraint_type,cc.column_name
fromuser_constraintsc,user_cons_columns cc
wherec.owner = upper('&table_owner') and c.table_name = upper('&table_name')
andc.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
八、 儲存函式和過程
1、 檢視函式和過程的狀態
SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';
2、 檢視函式和過程的原始碼
SQL>select text from all_source where owner=user and name=upper('&plsql_name');


九、 常用的資料字典：
dba_data_files:通常用來查詢關於資料庫檔案的資訊
dba_db_links:包括資料庫中的所有資料庫鏈路，也就是databaselinks。
dba_extents:資料庫中所有分割槽的資訊
dba_free_space:所有表空間中的自由分割槽
dba_indexs:關於資料庫中所有索引的描述
dba_ind_columns:在所有表及聚集上壓縮索引的列
dba_objects:資料庫中所有的物件
dba_rollback_segs:回滾段的描述
dba_segments:所有資料庫段分段的儲存空間
dba_synonyms:關於同義詞的資訊查詢
dba_tables:資料庫中所有資料表的描述
dba_tabespaces:關於表空間的資訊
dba_tab_columns:所有表描述、檢視以及聚集的列
dba_tab_grants/privs:物件所授予的許可權
dba_ts_quotas:所有使用者表空間限額
dba_users:關於資料的所有使用者的資訊
dba_views:資料庫中所有檢視的文字

十、 常用的動態效能檢視：
v$datafile：資料庫使用的資料檔案資訊
v$librarycache：共享池中SQL語句的管理資訊
v$lock：通過訪問資料庫會話，設定物件鎖的所有資訊
v$log：從控制檔案中提取有關重做日誌組的資訊
v$logfile有關例項重置日誌組檔名及其位置的資訊
v$parameter：初始化引數檔案中所有項的值
v$process：當前程式的資訊
v$rollname：回滾段資訊
v$rollstat：聯機回滾段統計資訊
v$rowcache：記憶體中資料字典活動/效能資訊
v$session:有關會話的資訊
v$sesstat：在v$session中報告當前會話的統計資訊
v$sqlarea：共享池中使用當前游標的統計資訊，游標是一塊記憶體區域，有Oracle處理SQL語句時開啟。
v$statname：在v$sesstat中報告各個統計的含義
v$sysstat：基於當前操作會話進行的系統統計
v$waitstat：出現一個以上會話訪問資料庫的資料時的詳細情況。當有一個以上的會話訪問同一資訊時，可出現等待情況。


總結了一下這些，徹底區別了檢視與資料字典，也不那麼容易混淆。嘿嘿!!!

十一、 常用SQL查詢：
1、檢視錶空間的名稱及大小
selectt.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
fromdba_tablespaces t, dba_data_files d
wheret.tablespace_name = d.tablespace_name
group by t.tablespace_name;

2、檢視錶空間物理檔案的名稱及大小
selecttablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
fromdba_data_files
order by tablespace_name;

3、檢視回滾段名稱及大小
selectsegment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curextCurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name;

4、檢視控制檔案
select name from v$controlfile;

5、檢視日誌檔案
select member from v$logfile;

6、檢視錶空間的使用情況
select sum(bytes)/(1024*1024) as free_space,tablespace_name
fromdba_free_space
group by tablespace_name;
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
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND


A.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、檢視資料庫庫物件
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

8、檢視資料庫的版本
Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle';

9、檢視資料庫的建立日期和歸檔方式
Select Created, Log_Mode, Log_ModeFromV$Database;

10、捕捉執行很久的SQL
column username format a12
columnopname format a16
column progress format a8
selectusername,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
fromv$session_longops , v$sql
wheretime_remaining<> 0
andsql_address = address
andsql_hash_value = hash_value

11。檢視資料表的引數資訊
SELECT partition_name, high_value, high_value_length, tablespace_name,
pct_free, pct_used, ini_trans, max_trans, initial_extent,
next_extent, min_extent, max_extent, pct_increase, FREELISTS,
freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
last_analyzed
FROM dba_tab_partitions
--WHERE table_name= :tname AND table_owner = :towner
ORDER BY partition_position

12.檢視還沒提交的事務
select * from v$locked_object;
select * from v$transaction;

13。查詢object為哪些程式所用
select
p.spid,
s.sid,
s.serial# serial_num,
s.usernameuser_name,
a.typeobject_type,
s.osuseros_user_name,
a.owner,
a.objectobject_name,
decode(sign(48 - command),
1,
to_char(command), 'Action Code #' || to_char(command) ) action,
p.programoracle_process,
s.terminal terminal,
s.program program,
s.statussession_status
fromv$session s, v$access a, v$process p
wheres.paddr = p.addr and
s.type = 'USER' and
a.sid = s.sid and
a.object='SUBSCRIBER_ATTR'
order by s.username, s.osuser

14。回滾段檢視
selectrownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents
Extents, v$rollstat.rssizeSize_in_Bytes, v$rollstat.xactsXActs,
v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,
sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,
v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and
v$rollstat.usn (+) = v$rollname.usn order by rownum

15。耗資源的程式（top session）
selects.schemanameschema_name, decode(sign(48 - command), 1,
to_char(command), 'Action Code #' || to_char(command) ) action, status
session_status, s.osuseros_user_name, s.sid, p.spid ,s.serial# serial_num, nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,
s.program program, st.valuecriteria_value from v$sesstatst, v$session s , v$process p wherest.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'
ors.status = 'ALL') and p.addr = s.paddr order by st.valuedesc, p.spidasc, s.usernameasc, s.osuserasc

16。檢視鎖（lock）情況
select /*+ RULE */ ls.osuseros_user_name, ls.usernameuser_name,
decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',
'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,
o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,
'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)
lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2
fromsys.dba_objects o, ( select s.osuser, s.username, l.type,
l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,
v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner
<> 'SYS' order by o.owner, o.object_name

17。檢視等待（wait）情況
SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',
'consistent gets') group by v$waitstat.class, v$waitstat.count

18。檢視sga情況
SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC

19。檢視catched object
SELECT owner, name, db_link, namespace, type, sharable_mem, loads, executions, locks, pins, kept FROM v$db_object_cache
20。檢視V$SQLAREA
SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,
VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS, USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS, BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA

21。檢視object分類數量
select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,
'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from
sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3
, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select
'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from

22。按使用者檢視object種類
select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes,
sum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL))
clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1, NULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences,
sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))
7/11頁
others from sys.obj$ o, sys.user$ u where o.type# >= 1 and u.user# =
o.owner# and u.name <> 'PUBLIC' group by u.name order by
sys.link$ union select 'CONSTRAINT' , count(*) from sys.con$

23。有關connection的相關資訊
1）檢視有哪些使用者連線
selects.osuseros_user_name, decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command) ) action, p.programoracle_process, statussession_status, s.terminal terminal, s.program program,
s.usernameuser_name, s.fixed_table_sequenceactivity_meter, '' query, 0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num fromv$session s, v$process p where s.paddr=p.addr and s.type = 'USER' order by s.username, s.osuser

2）根據v.sid檢視對應連線的資源佔用等情況
select n.name,
v.value,
n.class,
n.statistic#
from v$statname n,
v$sesstat v
wherev.sid = 71 and
v.statistic# = n.statistic#
order by n.class, n.statistic#

3）根據sid檢視對應連線正在執行的sql
select /*+ PUSH_SUBQ */
command_type,
sql_text,
sharable_mem,
persistent_mem,
runtime_mem,
sorts,
version_count,
loaded_versions,
open_versions,
users_opening,
executions,
users_executing,
loads,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
sysdatestart_time,
sysdatefinish_time,
'>' || address sql_address,
'N' status
fromv$sqlarea
where address = (select sql_address from v$session where sid = 71)

2）根據v.sid檢視對應連線的資源佔用等情況
select n.name,
v.value,
n.class,
n.statistic#
from v$statname n,
v$sesstat v
wherev.sid = 71 and
v.statistic# = n.statistic#
order by n.class, n.statistic#

3）根據sid檢視對應連線正在執行的sql
select /*+ PUSH_SUBQ */
command_type,
sql_text,
sharable_mem,
persistent_mem,
runtime_mem,
sorts,
version_count,
loaded_versions,
open_versions,
users_opening,
executions,
users_executing,
loads,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
sysdatestart_time,
sysdatefinish_time,
'>' || address sql_address,
'N' status
fromv$sqlarea
where address = (select sql_address from v$session where sid = 71)

24．查詢表空間使用情況
select a.tablespace_name "表空間名稱",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "佔用率(%)",
round(a.bytes_alloc/1024/1024,2) "容量(M)",
round(nvl(b.bytes_free,0)/1024/1024,2) "空閒(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
Largest "最大擴充套件段(M)",
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "取樣時間"
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
fromdba_data_files f
group by tablespace_name) a,
(select f.tablespace_name,
sum(f.bytes) bytes_free
fromdba_free_space f
group by tablespace_name) b,
(select round(max(ff.length)*16/1024,2) Largest,
ts.name tablespace_name
fromsys.fet$ ff, sys.file$ tf,sys.ts$ ts
wherets.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
group by ts.name, tf.blocks) c
wherea.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name

25. 查詢表空間的碎片程度
selecttablespace_name,count(tablespace_name)
tablespace_name
having count(tablespace_name)>10;
altertablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
selecttablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space union all
selecttablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
selecttablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space from dba_free_space group by

group by tablespace_name;

26。查詢有哪些資料庫例項在執行
selectinst_name from v$active_instances;
//取得伺服器的IP 地址
selectutl_inaddr.get_host_address from dual
//取得客戶端的IP地址
select sys_context('userenv','host'),sys_context('userenv','ip_address') from dual