PO常用SQL语言

1、Oracle EBS:常用SQL语句 /* Formatted on 2009-11-03 23:10:34 (QP5 v5.114.809.3010 */ -1查找系统用户基本信息 author:dezai Select USER_ID, USERNAME, DESCRIPTION, EMPLOYEDD_ID, PERSON_PARTY_ID FROM Fnd_User; -2查找供应商基本信息(供应商,供应商地点,联系人 author:dezai Select pv.vendor_id vendor_id, PVSA.VENDOR_SITE_ID vendor_site_id, pv.ve

2、ndor_name vendor_name, PVSA.VENDOR_SITE_CODE vendor_site_code, PVSA.ORG_ID org_id, pv.segment1 vendor_code, pvc.area_code | pvc.phone vendor_phone, PVC.FAX_AREA_CODE | pvc.fax vendor_fax, PVSA.TERMS_ID terms_id, PVSA.VAT_CODE vat_code, PVC.LAST_NAME | PVC.MIDDLE_NAME | PVC.FIRST_NAME contact_man FRO

3、M po_vendors pv, po_vendor_sites_all pvsa, po_vendor_contacts pvc Where pv.vendor_id = pvsa.vendor_id AND pvsa.vendor_site_id = pvc.vendor_site_id orDER BY org_Id DESC; -3查找所有的interface表 author:dezai Select * FROM dba_objects db Where db.object_type = 'TABLE' AND db.object_name LIKE '%IN

4、TERFACE%' -4查找对应模块的interface表 author:dezai Select * FROM dba_objects db Where db.object_type = 'TABLE' AND db.object_name LIKE '%INTERFACE%' AND owner LIKE 'PO' -5查找用户当前的状态 author:dezai Select SUBSTR (V$SESSION.USERNAME, 1, 8 USERNAME, V$SESSION.OSUSER OSUSER, - DECODE(V$

5、SESSION.SERVER,'DEDICATED','D','SHARED','S','O' SERVER, V$SQLAREA.DISK_READS DISK_READS, V$SQLAREA.BUFFER_GETS BUFFER_GETS, SUBSTR (V$SESSION.LOCKWAIT, 1, 10 LOCKWAIT, V$SESSION.PROCESS PID, V$SESSION_WAIT.EVENT EVENT, V$SQLAREA.SQL_TEXT SQL FROM V$SESSION_WAI

6、T, V$SQLAREA, V$SESSION Where V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND V$SESSION.SID = V$SESSION_WAIT.SID(+ AND V$SESSION.STATUS = 'ACTIVE' AND V$SESSION_WAIT.EVENT != 'client message' orDER BY V$SESSION.LOCKWAIT ASC, V$SESSION

7、.USERNAME; -6查找用户的职责 author:dezai select c.user_name as login_name, d.full_name as employee_name, as department_name, a.user_id as user_id, a.responsibility_id as responsibility_id, b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME from FND_USER_RESP_GROUPS a, FND_RESPONSIBILITY_VL b, fnd_user c,

8、hr_employees d, per_assignments_f e, hr_all_organization_units_tl f where a.user_id = c.user_id and c.employee_id = d.employee_id and c.employee_id = e.PERSON_ID and e.ORGANIZATION_ID = anization_id and a.responsibility_id = b.RESPONSIBILITY_ID and sysdate > e.EFFECTIVE_START_DATE and sysdat

9、e < e.EFFECTIVE_END_DATE order by c.description, c.user_name, a.responsi bility_id -7查找组织信息 author:dezai select organization_id ID,Organization_code 代码,Organization_name 名称, OPERATING_UNIT 营运OUID from org_organization_definitions ood; -8查找物料基本信息 author:dezai Select organization_id 组织ID, Inventory

10、_item_id 物料ID, Segment1 物料代码, Description 物料描述, Primary_uom_code 物料单位 FROM mtl_system_items_b msib; -9查找付款条件 author:dezai Select at.TERM_ID ID, at.NAME 名称, at.DESCRIPTION 说明 FROM ap_terms at; -10查找税码 author:dezai Select atca.Tax_Id ID, atca.Name 名称, atca.Set_Of_Books_Id 所属账套ID, atca.Description 描述,

11、atca.Org_Id 组织ID FROM Ap_Tax_Codes_All atca; -11查询所有应用模块的ID,对应的职责ID,模块的简称代码 author:dezai Select resp.application_id, resp.Responsibility_Id, resp.Responsibility_Key, appl.application_short_name FROM fnd_responsibility resp, fnd_application appl Where resp.application_id = appl.application_id; -12查询当

12、前系统登录的用户数 author:dezai Select COUNT (DISTINCT d.user_name FROM apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d Where b.paddr = c.addr AND a.pid = c.pid AND a.spid = cess AND d.user_id = a.user_id AND (d.user_name = 'USER_NAME' or 1 = 1; -13查询系统当前物料单位列表 author:dezai Select m

13、uom.UNIT_OF_MEASURE, muom.UOM_CODE, muom.DESCRIPTION, muom.UOM_CLASS, muom.SOURCE_LANG FROM mtl_units_of_measure muom; -14OU 库存组织与子库存 author:dezai Select anization_id ou_org_id, hou.NAME ou_name, anization_id org_org_id, anization_code org_org_code, msi.secondary_inventory_name,

14、 msi.description FROM hr_organization_information hoi, hr_organization_units hou, org_organization_definitions ood, mtl_secondary_inventories msi Where _information1 = 'OPERATING_UNIT' AND anization_id = anization_id AND ood.operating_unit = anization_id AND ood.o

15、rganization_id = anization_id; -15查询库存物料现有量 author:dezai Select ms.* FROM mtl_supply ms, po_headers_all ph Where ms.po_header_id = ph.po_header_id AND ph.segment1 = '2009001' -PO号 orDER BY ms.po_header_id, ms.po_release_id, ms.po_line_id, ms.po_line_location_id, ms.po_distribution_id;

16、 -16 查找死锁进程 author:dezai Select vs.username, lo.OBJECT_ID, , lo.SESSION_ID, vs.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_ NAME, lo.PROCESS FROM V$LOCKED_OBJECT lo, V$SESSION vs, sys.obj$ sob Where lo.SESSION_ID = vs.SID AND sob.obj# = lo.OBJECT_ID; -17 中断死锁进程 author:dezai Alter SYSTEM KILL SES

17、SION 'sid,serial#' -18 查找死锁进程2 author:dezai Select c.owner, c.object_name, c.object_type, fu.user_name locking_fnd_user_name, fl.start_time locking_fnd_user_login_time, vs.module, vs.machine, vs.osuser, vlocked.oracle_username, vs.SID, vp.pid, vp.spid AS os_process, vs.serial#, vs.status, vs

18、.saddr, vs.audsid, cess FROM fnd_logins fl, fnd_user fu, v$locked_object vlocked, v$process vp, v$session vs, dba_objects c Where vs.SID = vlocked.session_id AND vlocked.object_id = c.object_id AND vs.paddr = vp.addr AND vp.spid = cess_spid(+ AND vp.pid = fl.pid(+ AND fl.user_id = fu.use

19、r_id(+ -AND c.object_name LIKE '%' | UPPER('&tab_name_leaveblank4all' | '%' AND NVL (vs.status, 'XX' != 'KILLED' -1.已审批的请购单 author:dezai Select prh.requisition_header_id, prl.requisition_line_id,prh.segment1 from PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISIT

20、ION_LINES_ALL PRL where prh.requisition_header_id = prl.requisition_header_id /* and prh.requisition_header_id = 100 */ and prh.authorization_status = 'APPROVED' -2.已审批的采购单 author:dezai Select ph.po_header_id,pl.PO_LINE_ID,ph.segment1,ph.* from Po_Lines_all pl,Po_Headers_All ph where pl.PO_H

21、EADER_ID=ph.po_header_id /* and ph.po_header_id = 22-1*/ and ph.authorization_status = 'APPROVED' and NVL(ph.cancel_flag,'N'<>'Y' and ph.creation_date>=trunc(sysdate -3查看用户的职责 author:dezai select c.user_name as login_name, d.full_name as employee_name, as depa

22、rtment_name, a.user_id as user_id, a.responsibility_id as responsibility_id, b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME from FND_USER_RESP_GROUPS a, FND_RESPONSIBILITY_VL b, fnd_user c, hr_employees d, per_assignments_f e, hr_all_organization_units_tl f where a.user_id = c.user_id and c.employee_i

23、d = d.employee_id and c.employee_id = e.PERSON_ID and e.ORGANIZATION_ID = anization_id and a.responsibility_id = b.RESPONSIBILITY_ID and sysdate > e.EFFECTIVE_START_DATE and sysdate < e.EFFECTIVE_END_DATE order by c.description, c.user_name, a.responsibility_id -4AP发票 select ai.* from ap_

24、invoices_all ai ,ap_invoice_distributions_all id ,po_line_locations_all pll ,po_lines_all pl ,po_headers_all ph ,po_distributions_all pd where pl.po_header_id = ph.po_header_id /* and ph.segment1 = '2001' -采购订单编 号*/ and pll.po_line_id = pl.po_line_id and pll.line_location_id = pd.line_locati

25、on_id and id.po_distribution_id = pd.po_distribution_id and ai.invoice_id = id.invoice_id; -5未过帐付款行 author:dezai Select rownum seq_id, aip.invoice_payment_id, aip.accounting_event_id, aip.accounting_date, aip.check_id, aip.amount aip_amount, ac.check_number, ac.check_date, ac.amount ac_amount, hou.N

26、AME org_name FROM ap_invoice_payments_all aip, ap_checks_all ac, ap_system_parameters_all asp, hr_operating_units hou Where aip.posted_flag IN ('N', 'S' /* AND aip.accounting_date BETWEEN g_period_start AND g_period_end*/ AND _id = _id /* AND asp.set_of_books_id = g_set

27、_of_books_id*/ AND ac.check_id = aip.check_id AND nvl(asp.when_to_account_pmt, 'ALWAYS' = 'ALWAYS' AND _id = anization_id -6查询用户刚刚执行的SQL author:dezai Select sql_text from v$sql orDER BY first_load_time DESC; -7查看数据库对象 author:dezai Select owner, object_type, status, COUN

28、T(* count# FROM all_objects GROUP BY owner, object_type, status; -8查看哪些用户连接 author:dezai select s.osuser os_user_name, decode(sign(48 - command, 1, to_char(command, 'Action Code #' | to_char(command action, gram oracle_process, status session_status, s.terminal terminal, gram progr

29、am, s.username user_name, s.fixed_table_sequence activity_meter, '' query, 0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER' order by s.username, s.osuser - 9最耗费CPU资源的TOP 25 SQLs author:dezai select

30、 * from ( select rank( over(order by buffer_gets desc as rank_bufgets ,to_char(100 * ratio_to_report(buffer_gets over(, '999.99' pct_bufgets ,sql_text from v$sqlarea where rownum<26 ; -10查询所有的SQL Statment author:dezai Select sql_text FROM v$sqltext a Where a.hash_value = (Select sql_hash_

31、value FROM v$session b Where b.SID = '&sid' orDER BY piece ASC -11查询SESSION_ID author:dezai select sql.sql_text, sess.logon_time, pro.pid "Oracle ProID", sess.sid "SessID",sess.serial#, pro.spid "OS ProID", pro.username "Oracle UsrName", gra

32、m "Program", sess.module "Module", sess.username, sess.osuser, sess.machine, sess.terminal, gram, sess.type from v$process pro,v$session sess , v$sql sql where pro.addr=sess.paddr and sess.sql_address = sql.address(+ and sess.sid = &SESSION_ID -13查询ORACLE_ID select sq

33、l.sql_text, sess.logon_time, pro.pid "Oracle ProID", sess.sid "SessID",pro.spid "OS ProID", pro.username "Oracle UsrName", gram "Program", sess.module "Module", sess.username, sess.osuser, sess.machine, sess.terminal, gram, s

34、ess.type from v$process pro,v$session sess , v$sql sql where pro.ADDR=sess.paddr and sess.sql_address = sql.address (+ - sql.address = sess.sql_address(+ ? and pro.pid= &ORACLE_PROCESS_ID - 14OS_PROCESS_ID select sql.sql_text, sess.logon_time, pro.pid "Oracle ProID", sess.sid "Ses

35、sID",pro.spid "OS ProID", pro.username "Oracle UsrName", gram "Program", sess.module "Module", sess.username, sess.osuser, sess.machine, sess.terminal, gram, sess.type from v$process pro,v$session sess , v$sql sql where pro.ADDR=sess.paddr

36、and sess.sql_address = sql.address (+ - sql.address = sess.sql_address(+ ? and pro.spid = &OS_ID - 15SQL select sql.SQL_TEXT,sql.BUFFER_GETS , sql.CPU_TIME, sql.DISK_READS,sql.ELAPSED_TIME ,sql.RUNTIME_MEM,sql.EXECUTIONS ,sql.FETCHES,sql.PARSE_CALLS,sql.FIRST_LOAD_TIME,sql.MODULE,sql.OPTIMIZER_C

37、OST, sql.OPTIMIZER_MODE,sql.SORTS , sess.SID , sess.PROGRAM ,SESS.TYPE from v$sql sql , v$session sess where sess.SQL_ADDRESS(+=sql.ADDRESS and upper(sql_text like '%&sql%' - 16查找请求IDRequest_ID Select cess_status_code,a.oracle_process_id,a.os_process_id,process_start_date FROM FND_C

38、ONCURRENT_PROCESSES A , FND_CONCURRENT_REQUESTS B Where B.CONTROLLING_MANAGER=A.CONCURRENT_PROCESS_ID AND B.REQUEST_ID='&req_id' select 'CONC',p.pid,request_id , p.username OS_NAME , p.spid , s.sid , s.username , s.serial# , s.sql_address ,u.user_name ,CONCURRENT_PROGRAM_NAME fro

39、m v$process p , v$session s ,fnd_concurrent_requests cr ,fnd_user u,fnd_concurrent_programs cp where s.audsid=cr.oracle_session_id - 如果是oracle report, using the following sql and cr.REQUESTED_BY = u.user_id and s.paddr = p.addr and cp.CONCURRENT_PROGRAM_ID =cr.CONCURRENT_PROGRAM_ID and cr.request_id

40、 = &REQUESTID select request_id,STATUS_CODE,PHASE_CODE ,oracle_id,OS_PROCESS_ID, oracle_session_id,concurrent_program_id, printer from fnd_concurrent_requests where request_id = &REQUEST_ID -17查找后台正在运行的concurrent reporter SESSION select sid, serial#,osuser, process, machine, program,module f

41、rom v$session where status='ACTIVE' AND SCHEMANAME<>'SYS' AND UPPER(PROGRAM LIKE 'AR60RUN%' Select r.request_id "Request ID", s.sid "Session ID" , s.serial# FROM applsys.fnd_concurrent_requests r, applsys.fnd_concurrent_queues_tl qt, applsys.fnd_conc

42、urrent_queues q, applsys.fnd_concurrent_processes p, v$session s Where r.controlling_manager=p.concurrent_process_id AND q.application_id=p.queue_application_id AND q.concurrent_queue_ id=p.concurrent_queue_id AND qt.application_id=q.application_id AND qt.concurrent_queue_id=q.concurrent_queue_id AN

43、D r.phase_code='R' AND qt.language in ('ZHS' AND p.session_id=s.audsid -18根据request_id查找是哪个trace file Select fcr.request_id, fcr.concurrent_program_id, fcp.concurrent_program_name, TO_CHAR (fcr.actual_completion_date, 'DD-MON-YY HH24:MI:SS', actual_completion_date, fcr.oracle

44、_process_id, par_udd.VALUE | '/*' | fcr.oracle_process_id | '*.trc' TRACE FROM fnd_concurrent_requests fcr, fnd_concurrent_programs fcp, v$parameter par_udd Where fcr.concurrent_program_id = fcp.concurrent_program_id AND LOWER (par_udd.NAME = 'user_dump_dest' AND fcr.request_

45、id = 58798 -19根据HASH_VALUE生成执行计划 select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" from dual union all select '-' from dual union all select * from (select rpad('|'|substr(lpad(' ',1*(depth-1|operation| decode(options, null,&

46、#39;',' '|options, 1, 32, 33, ' '|'|'| rpad(decode(id, 0, '- '|to_char(hash_value|' -' , substr(decode(substr(object_name, 1, 7, 'SYS_LE_', null, object_name |' ',1, 20, 21, ' '|'|'| lpad(decode(cardinality,null,' ',

47、 decode(sign(cardinality-1000, -1, cardinality|' ', decode(sign(cardinality-1000000, -1, trunc(cardinality/1000|'K', decode(sign(cardinality-1000000000, -1, trunc(cardinality/1000000|'M', trunc(cardinality/1000000000|'G', 7, ' ' | '|' | lpad(decode(byt

48、es,null,' ', decode(sign(bytes-1024, -1, bytes|' ', decode(sign(bytes-1048576, -1, trunc(bytes/1024|'K', decode(sign(bytes-1073741824, -1, trunc(bytes/1048576|'M', trunc(bytes/1073741824|'G', 6, ' ' | '|' | lpad(decode(cost,null,' ', de

49、code(sign(cost-10000000, -1, cost|' ', decode(sign(cost-1000000000, -1, trunc(cost/1000000|'M', trunc(cost/1000000000|'G', 8, ' ' | '|' as "Explain plan" from v$sql_plan where hash_value = &hash_value 来源: -20获取ORACLE SESSION客户端信息 select SYS_CONTE

50、XT('USERENV','TERMINAL' terminal, SYS_CONTEXT('USERENV','LANGUAGE' language, SYS_CONTEXT('USERENV','SESSIONID' sessionid, SYS_CONTEXT('USERENV','INSTANCE' instance, SYS_CONTEXT('USERENV','ENTRYID' entryid, SYS_CONTEXT(&#

51、39;USERENV','ISDBA' isdba, SYS_CONTEXT('USERENV','NLS_TERRITORY' nls_territory, SYS_CONTEXT('USERENV','NLS_CURRENCY' nls_currency, SYS_CONTEXT('USERENV','NLS_CALENDAR' nls_calendar, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT' nl

52、s_date_format, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE' nls_date_language, SYS_CONTEXT('USERENV','NLS_SORT' nls_sort, SYS_CONTEXT('USERENV','CURRENT_USER' current_user, SYS_CONTEXT('USERENV','CURRENT_USERID' current_userid, SYS_CONTEXT('USERENV&