oracle笔记

–首先允许行移动
alter table s_sale_printer enable row movement;
–闪回
flashback table s_sale_printer to timestamp to_timestamp(‘2017-08-30 09:30:00’,‘yyyy-mm-dd hh24:mi:ss’);
–随机取1或0
select sign(sign(dbms_random.random)+1) “Random” from dual;
–助记码
select f_st2zjm(‘国家金库河津市支库’) from dual;

–查看表的定义
select dbms_metadata.get_ddl(‘TABLE’,‘T1’) from dual；
–查看视图的定义语句
select dbms_metadata.get_ddl(‘VIEW’,‘V_WARE_SPBJ_HFT’) from dual
–查看存储过程代码
SELECT text
FROM user_source
WHERE NAME = upper(‘Procedure Name’)
ORDER BY line;
–查看触发器
select trigger_name from all_triggers WHERE table_name=upper(‘v_ware_spbj_hft’)
select dbms_metadata.get_ddl(‘TRIGGER’,‘TR_V_WARE_SPBJ_HFT’,‘H2’) from dual;

–查触发器
select * from dba_triggers t where t.owner=‘H2’ AND LOWER(t.table_name) = ‘t_dist_d’
–查字段
select * from dba_tab_columns where owner=‘H2’

添加字段的语法：alter table tablename add (column datatype [default value][null/not null],….);
修改字段的语法：alter table tablename modify (column datatype [default value][null/not null],….);
删除字段的语法：alter table tablename drop (column);

–判断数字
trim(translate(busnos,‘0123456789’,’ ‘)) IS NULL
f_get_orgname((CASE WHEN trim(translate(busnos,‘0123456789’,’ ')) IS NULL THEN to_number(busnos) ELSE 0 END)) AS orgname,

alter table test1
add (name varchar2(30) default ‘无名氏’ not null,
age integer default 22 not null,
has_money number(9,2)
);
–预览小票
select * FROM TABLE(f_sale_printer_pipe(‘101’, ‘1709251000000018’, 2));

SELECT txt,saleno,lineorder,duplicate,id,filed FROM temp_printtxt
WHERE saleno = ‘1807091001286216’

begin
delete temp_printtxt where saleno=‘1807091001286216’;
insert into temp_printtxt(txt,saleno,lineorder,duplicate,id,filed)
SELECT column_value,‘1807091001286216’,‘1’,‘0’,rownum,rownum FROM TABLE(f_sale_printer_pipe(‘1011’, ‘1807091001286216’, 1));
commit;
end;

–屏蔽触发器
begin
INSERT INTO tmp_disable_trigger (table_name)VALUES (‘t_reject_apply_h’);
update t_reject_apply_h set status = 2 where applyno = ‘1612060001’;
DELETE FROM tmp_disable_trigger WHERE table_name = ‘t_reject_apply_h’;
end;

—update
Oracle没有update from语法，可以通过两种写法实现同样的功能：
1：子查询UPDATE A SET A.NAME=(SELECT B.NAME FROM B WHERE B.ID=A.ID)，本查询要根据具体情况看看是否变通成如下
（1）单列
UPDATE A

SET A.NAME=(SELECT B.NAME FROM B WHERE B.ID=A.ID)

WHERE A.ID IN (SELECT ID FROM B)；

（2）多列
UPDATE order_rollup

SET(qty,price)=(SELECT SUM(qty),SUM(price) FROM order_lines WHERE customer_id=‘KOHL’ )

WHERE cust_id=‘KOHL’ AND order_period=TO_DATE(‘01-Oct-2000’)

–何处用了什么代码
select distinct name from all_source where lower(text) like ‘%t_item_in_out_remain%’

查看正在执行的作业
select * from user_scheduler_running_jobs
select sid, serial# from v$session where sid = 1065
杀进程
SELECT * FROM v_who;

–查看锁表进程SQL语句_李凤洋_杀login_time最早的一个sid：
with sess as
(select /*+ OPT_PARAM(’_optimizer_adaptive_plans’,‘false’) / /+ NO_MONITOR */
s.inst_id,
s.sid,
to_char(s.LOGON_TIME, ‘mm/dd hh24:mi:ss’) login_time,
decode(COMMAND, 3, ‘SEL’, 2, ‘INT’, 6, ‘UPD’, 7, ‘DEL’, ‘OTH’) OPN,
DECODE(s.PLSQL_SUBPROGRAM_ID, NULL, nvl(s.SQL_ID,prev_sql_id), NULL) SQL_ID,
substr(s.wait_class, 1, 4) wait_class,
substr(s.STATUS,1,6) status,
s.paddr,
s.STATE,
s.EVENT,
s.SECONDS_IN_WAIT,
substr(s.program, 1, 8) PROGRAM,
s.MACHINE,
s.PORT,
substr(s.USERNAME,1,12) username,
s.prev_sql_id,
blocking_instance,
blocking_session,
ROW_WAIT_OBJ#||’-’||ROW_WAIT_FILE#||’-’||ROW_WAIT_BLOCK#||’-’||ROW_WAIT_ROW# obj_file_blk_row
from gv