特殊語法

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)

--查詢各區的tablespace目前狀況 select tablespace_name,sum(bytes/1024/1024) bytes from user_free_space group by tablespace_name -------------------------------------------------------------- --查詢各區的tablespace中各table的運用情況,由大到小 SELECT * FROM ( SELECT BYTES/1024/1024 M,SEGMENT_NAME,TABLESPACE_NAME FROM USER_SEGMENTS ) ORDER BY 1 DESC -------------------------------------------------------------- --搬table space ALTER TABLE TEST MOVE TABLESPACE TBS_A; ALTER INDEX TEST REBUILD;

http://www.lslnet.com/linux/f/docs1/i45/big5315237.htm

以下語句可以發現所有用戶鎖

select s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type,s.terminal,s.machine,s.program,s.osuser from v$session s,v$lock l,dba_objects o where s.sid=l.sid and o.object_id=l.id1 and s.username is not null

如果有鎖等待或死鎖，可以通過以下語句發現

select lpad(' ',decode(l.xidusn,0,3,0))||l.oracle_username User_name ,o.owner,o.object_name,o.object_type from v$locked_object l,dba_objects o where l.object_id=o.object_id order by o.object_id desc

查詢、刪除執行中的SQL SESSION

select * from V$sql; select * from V$session; select a.sid, a.serial#, a.status, b.* from V$session a inner join v$sql b on a.sql_id = b.sql_id; --alter system kill session '142,317';

查詢所有的表格constraint

 select table_name, constraint_name, status, owner from all_constraints

使用Merge在一次執行中，同時對資料庫進行修改與新增
參考 http://www.ithome.com.tw/itadm/article.php?c=38332 

analyze table compute statistics; merge into test1 b using ( select idn,nvl(ebdte,' ') ebdte ,nvl(ename,' ') ename,min(paydte) paydte from test2 where paydte is not null group by idn,nvl(ebdte,' '),nvl(ename,' ') ) a on (b.idno = a.idn and (nvl(b.birthdat, ' ') = a.ebdte and nvl(b.name, ' ') = a.ename)) when matched then update set dateb=a.paydte where idn=a.idn and nvl(birthdat,' ')=a.ebdte and nvl(b.name,' ')=a.ename when not matched then insert (seqno,idno,birthdat,name,dateb) values( sequence1.nextval ,a.idn, trim(a.ebdte) ,trim(a.ename), a.paydte );

函式
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

使用臨時表with t1 as (select * from aaa where ubno = '02000010'), t2 as (select * from bbb where unitno = '02000010') select * from t1 inner join t2 on t1.ubno = t2.unitno

日期加減天數
sysdate  + 1  系統日加ㄧ天
sysdate  - 1  系統日減ㄧ天


參考網站
http://www.techonthenet.com/oracle/functions/index.php

請在此填寫程式碼

請在此填寫程式碼