EBS系统管理常用SQL语句整理汇总(参考网上资料&其他人博客)

原创 2013年12月04日 23:07:56
--1查找系统用户基本信息 

SELECT user_id,
       
       user_name,
       
       description,
       
       employeE_id,
       
       person_party_id

  FROM fnd_user;

--3查找所有的interface表 

SELECT *

  FROM dba_objects db

 WHERE db.object_type = 'TABLE'
   AND db.object_name LIKE '%INTERFACE%';

--4查找对应模块的interface表  

SELECT *

  FROM dba_objects db

 WHERE db.object_type = 'TABLE'
      
   AND db.object_name LIKE '%INTERFACE%'
      
   AND owner LIKE 'PO';

--5查找用户当前的状态  

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查找用户的职责 

SELECT c.user_name AS login_name,
       
       d.full_name AS employee_name,
       
       f.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,
       
       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 = f.organization_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
          
          --7查找组织信息 
          
            SELECT organization_id   id,
                   organization_code 代码,
                   organization_name 名称,
                   
                   operating_unit 营运ouid
            
              FROM org_organization_definitions ood;




--11查询所有应用模块的ID,对应的职责ID,模块的简称代码 

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查询当前系统登录的用户数  

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查询系统当前物料单位列表 

SELECT muom.unit_of_measure,
       
       muom.uom_code,
       
       muom.description,
       
       muom.uom_class,
       
       muom.source_lang

  FROM mtl_units_of_measure muom;

--14OU 库存组织与子库存  

SELECT hou.organization_id ou_org_id,
       
       hou.name ou_name,
       
       ood.organization_id org_org_id,
       
       ood.organization_code org_org_code,
       
       msi.secondary_inventory_name,
       
       msi.description

  FROM hr_organization_information hoi,
       
       hr_organization_units hou,
       
       org_organization_definitions ood,
       mtl_secondary_inventories    msi

 WHERE hoi.org_information1 = 'OPERATING_UNIT'
      
   AND hoi.organization_id = hou.organization_id
      
   AND ood.operating_unit = hoi.organization_id
      
   AND ood.organization_id = msi.organization_id;

--15查询库存物料现有量  

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 查找死锁进程 

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 中断死锁进程 

ALTER system kill session 'sid,serial#';

--18 查找死锁进程2 

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.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';

--查询EBS用户密码sql语句 参考萌虫小换换
SELECT usr.user_name,
       
       get_pwd.decrypt
       
       ((SELECT (SELECT get_pwd.decrypt
                       
                       (fnd_web_sec.get_guest_username_pwd,
                        
                        usertable.encrypted_foundation_password
                        
                        )
                
                  FROM dual) AS apps_password
        
          FROM fnd_user usertable
        
         WHERE usertable.user_name =
              
               (SELECT substr
                       
                       (fnd_web_sec.get_guest_username_pwd,
                        
                        1,
                        
                        instr
                        
                        (fnd_web_sec.get_guest_username_pwd,
                         
                         '/'
                         
                         )
                        
                        - 1
                        
                        )
                
                  FROM dual))
        
        
        
        ) password,
   usr.encrypted_user_password
  FROM fnd_user usr
 WHERE usr.user_name = 'SYSADMIN';

相关文章推荐

ORACLE EBS常用表及查询语句(最终整理版)

建议去看参考二 参考一:                                                              ca...

EBS常用SQL

--1查找系统用户基本信息 author:dezai SELECT user_id, username, description, employedd_id, person_party_id F...

Oracle EBS R12文件系统结构(学习汇总网上资料)

Oracle EBS R12在服务器端文件结构如下: 顶层目录下面分为 1)inst ——–跟ebs整个实例(instance)相关的配置信息以及其他信息 2)  db   ——-主要存储D...

用户创建,删除and并发注册and系统登陆的API研究(学习汇总网上资料)

比如有一个外围支持系统,用户需要在外围系统登录之后点个link就可以登录到Oracle ERP系统中,那么我们需要先把外围系统的用户创建在Oracle ERP中,并且分配职责给他。 DECLARE a...

参考网上资料,整理的华为编程要求

在网上搜集了一些资料,整理了一下关于华为的编程要求,方便学习 一.   排版 1.  程序块要采用缩进风格编写,缩进的空格数为4个,但对齐只使用空格键,不使用TAB键。 2.  相对独立...

[MSSQL]系统管理常用语句

(1)@@S SELECT @@CONNECTIONS AS TotalConnections ,@@TIMETICKS AS TimeTicks ,@@CPU_BUSY AS TotalCPU...

关于MOS功率与选型,网上资料的整理

MOS管自身的功率 P = VDS * ID,VDS = ID * Rds,Rds是MOS管得导通电阻,可以通过查看芯片手册得知RDS。 MOS管的实际功耗是怎样计算的呢?各位大神,最近...

[网上资料整理]关于标准电阻阻值的说明(E6、E12、E24、E48、E96、E192)

关于电容电阻的使用,只知道有一些所谓的典型值,有助于选型,却不知道这些值是怎么来的。今天查了一下,才发现所谓的“E”规格。 “ E ”表示“指数间距”(Exponential Spacing) ...
  • hgzty
  • hgzty
  • 2011年06月16日 19:16
  • 7339

matlab下libjpeg工具箱(jpegtbx1.4)的安装与配置,包括IJG库的编译配置(综合整理网上资料)

最近做jpeg图像处理,需要读取JPEG的DCT系数和质量因子等信息,发现libjpeg库有比较好的解决方法,下面是在matlab下安装和配置libjpeg库的具体方法,包括一些错误解决方法:   配...

手机扫描条形码二维码原理和实现等网上资料整理

有志于要从事条形码和二维码开发的同学,和我一样想多了解这方面的东西。但是苦于网上资源甚少,我已经把现有资源整理如下。 原理、实现方案、代码资源、规则是否通用比如加密如何实现? ==========...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:EBS系统管理常用SQL语句整理汇总(参考网上资料&其他人博客)
举报原因:
原因补充:

(最多只允许输入30个字)