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.vendor_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
FROM 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 '%INTERFACE%';
--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$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_WAIT, 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.USERNAME;
--6查用户的职责 author:dezai
select c.user_name as login_name,
d.full_name as employee_name,
f.name as department_name,
a.user_id as user_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
ployee_id = d.employee_id
ployee_id = e.PERSON_ID
and e.ORGANIZATION_ID = f.organization_id
sponsibility_id = b.RESPONSIBILITY_ID
and sysdate > e.EFFECTIVE_START_DATE
and sysdate < 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_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 描述,
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查询当前系统登录的用户数 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 = b.process
AND d.user_id = a.user_id
AND (d.user_name = 'USER_NAME' or 1 = 1);
--13查询系统当前物料单位列表 author:dezai
Select muom.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,
msi.secondary_inventory_name,
msi.description
FROM hr_organization_information hoi,
hr_organization_units hou,
org_organization_definitions ood,
mtl_secondary_inventories msi
Where _information1 = 'OPERATING_UNIT'
anization_id = anization_id
AND ood.operating_unit = anization_id
anization_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 查死锁进程 author:dezai
Select vs.username,
lo.OBJECT_ID,
sob.name,
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 SESSION '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.machine,
vs.osuser,
vs.SID,
vp.pid,
vp.spid AS os_process,
vs.serial#,
vs.status,
vs.saddr,
vs.audsid,
vs.process
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 = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
--AND c.object_name LIKE '%' || UPPER('&tab_name_leaveblank4all') || '%'
AND NVL (vs.status, 'XX') != 'KILLED';
--1.已审批的请购单 author:dezai
quisition_header_id, quisition_line_id,prh.segment1
from PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL
quisition_header_id = quisition_header_id
/* quisition_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_HEADER_ID=ph.po_header_id
/* and ph.po_header_id = 22-1*/
and ph.authorization_status = 'APPROVED'
and NVL(ph.cancel_flag,'N')<>'Y'
ation_date>=trunc(sysdate)
--3查看用户的职责 author:dezai
select c.user_name as login_name,
d.full_name as employee_name,
f.name as department_name,
a.user_id as user_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
ployee_id = d.employee_id
ployee_id = e.PERSON_ID
and e.ORGANIZATION_ID = f.organization_id
sponsibility_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_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
基本的sql语句有哪些and pll.line_location_id = pd.line_location_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.NAME 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*/
_id = _id
/* AND asp.set_of_books_id = g_set_of_books_id*/
AND ac.check_id = aip.check_id
AND nvl(asp.when_to_account_pmt, 'ALWAYS') = 'ALWAYS'
_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, COUNT(*) 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, p.program oracle_process,
status session_status, s.terminal terminal, s.program program,
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 * 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_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", pro.program "Program",
sess.username, sess.osuser, sess.machine, inal, sess.program, pe
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 sql.sql_text, sess.logon_time, pro.pid "Oracle ProID", sess.sid "SessID",pro.spid "OS ProID", pro.username "Oracle UsrName", pro.program "Program",
sess.username, sess.osuser, sess.machine, inal, sess.program, pe
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 "SessID",pro.spid "OS ProID", pro.username "Oracle UsrName", pro.program "Program",
sess.username, sess.osuser, sess.machine, inal, sess.program, pe
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.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_COST, 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 a.process_status_acle_process_id,a.os_process_id,process_start_date FROM FND_CONCURRENT_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
from v$process p
, v$session s
,fnd_concurrent_requests cr
,fnd_user u,fnd_concurrent_programs cp
where
s.acle_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
quest_id = &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 from v$session
where status='ACTIVE'
AND SCHEMANAME<>'SYS'
AND UPPER(PROGRAM) LIKE 'AR60RUN%'
quest_id "Request ID",
s.sid "Session ID" ,
s.serial#
FROM applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_queues_tl qt,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_processes p,
v$session s
lling_urrent_process_id
AND q.application_id=p.queue_application_id
urrent_queue_
urrent_queue_id
AND qt.application_id=q.application_id
urrent_queue_urrent_queue_id
AND r.phase_code='R'
AND qt.language in ('ZHS')
AND p.session_id=s.audsid
---18根据request_id查是哪个trace file
quest_id, urrent_program_id, urrent_program_name,
TO_CHAR (fcr.actual_completion_date, 'DD-MON-YY HH24:MI:SS'),
actual_completion_date, acle_process_id,
par_udd.VALUE || '/*' || acle_process_id || '*.trc' TRACE
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
v$parameter par_udd
urrent_program_id = urrent_program_id
AND LOWER (par_udd.NAME) = 'user_dump_dest'
quest_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,'',' '||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,' ',
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(bytes,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,' ',
decode(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)
来源:fhtt0606.itpub/post/14578/476681
--20获取ORACLE SESSION客户端信息
select
SYS_CONTEXT('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('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') nls_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','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') se
发表评论