特殊語法
select* from nls_session_parameters;
select* from nls_database_parameters;
select* from nls_instance_parameters;
複製B表格架構與資料至A
create table A as select * from B
exp/imp table
D:\>exp user/pass@sid1 file=test1.dmp tables=(tab1,tab2) log=exp_tt.log
D:\>imp user/pass@sid2 file=test1.dmp fromuser=user1 touser=user2 log=imp_test.log
查詢tablespace使用量
select segment_name, bytes/1024/1024 M from user_segments order by 2 desc
select sum(m) from ( select segment_name, bytes/1024/1024 m from user_segments )
即時analyze table
analyze table testtable compute statistics
取得所有表格的資訊
select * from all_tables;
取得表格所有的欄位
select * from USER_TAB_COLUMNS WHERE TABLE_NAME = 'MOMOTEST';
取得表格所有的限制constraints
select * from user_constraints WHERE TABLE_NAME = 'MOMOTEST';
取得表格所有被限制constraints的欄位
select * from user_cons_columns WHERE TABLE_NAME = 'MOMOTEST';
TableSpace用完的處理方式(ORA-01654: unable to extend index)
http://www.lslnet.com/linux/f/docs1/i45/big5315237.htm
以下語句可以發現所有用戶鎖
如果有鎖等待或死鎖,可以通過以下語句發現
查詢、刪除執行中的SQL SESSION
查詢所有的表格constraint
使用Merge在一次執行中,同時對資料庫進行修改與新增
參考 http://www.ithome.com.tw/itadm/article.php?c=38332
函式
lpad()在字串左邊補上特定字元
lpad('tech', 7); would return ' tech'
lpad('tech', 2); would return 'te'
lpad('tech', 8, '0'); would return '0000tech'
lpad('tech on the net', 15, 'z'); would return 'tech on the net'
lpad('tech on the net', 16, 'z'); would return 'ztech on the net'
to_char()轉化數值或日期型態成文字型態
to_char(1210.73, '9999.9') would return '1210.7'
to_char(1210.73, '9,999.99') would return '1,210.73'
to_char(1210.73, '$9,999.00') would return '$1,210.73'
to_char(21, '000099') would return '000021'
to_date()轉化字串成日期型態
to_date('2003/07/09', 'yyyy/mm/dd') would return a date value of July 9, 2003.
to_date('070903', 'MMDDYY') would return a date value of July 9, 2003.
to_date('20020315', 'yyyymmdd') would return a date value of Mar 15, 2002.
修改clob型態為varchar
alter TABLE DCS3_CUST_REC ADD TMP_CR_REC VARCHAR(4000 BYTE);
update DCS8_CHF_ASIG set TMP_CR_REC = TO_CHAR(CR_REC);
commit;
alter table DCS3_CUST_REC drop column CR_REC;
alter table DCS3_CUST_REC rename column TMP_CR_REC to CR_REC;
add_month()日期加減月份
add_months(sysdate ,1) 系統日加ㄧ個月
add_months(sysdate ,-1) 系統日加減個月
sum(col) over()
原始資料

加總column B
select a ,b sum(b) over() from aatest

以column A為group加總column B
select a ,b sum(b) over(partition by a ) from aatest

以column A為group累加column B
select a ,b sum(b) over(partition by a order by a ,b) from aatest

with tmp as (select '1' as id , a from aa)
select id , a ,sum(a) over(partition by id order by id ,a) as b from tmp

connect by level
select rownum from dual connect by level <= 5

select rownum, startdt + level - 1 col1
from (select to_date(extract(year from sysdate) || '0101', 'yyyymmdd') startdt
from dual) a1 connect by level <= 5

使用臨時表
日期加減天數
sysdate + 1 系統日加ㄧ天
sysdate - 1 系統日減ㄧ天
參考網站
http://www.techonthenet.com/oracle/functions/index.php