EBS常用SQL脚本

23decxf

界面LOV的SQL获取
DECLARE
  CURSOR cur_lov_sql(p_pre_sql_addr VARCHAR2) IS
    SELECT t.sql_text FROM v$sqltext_with_newlines t WHERE t.address = p_pre_sql_addr ORDER BY t.piece;
  --
  l_lov_sql       VARCHAR2(2400);
  l_prev_sql_addr VARCHAR2(200);
BEGIN
  BEGIN
    SELECT v.prev_sql_addr INTO l_prev_sql_addr FROM v$session v WHERE v.sid = &sid;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('get prev sql addr error,' || SQLCODE || ':' || SQLERRM);
  END;
  --
  FOR rec IN cur_lov_sql(l_prev_sql_addr) LOOP
    l_lov_sql := nvl(l_lov_sql, '') || rec.sql_text;
  END LOOP;
  --
  dbms_output.put_line('lov查询sql为:');
  dbms_output.put_line(l_lov_sql);

END;
EBS信息
-----查询APPLICATIONID所对应的应用产品
select * from fnd_application_vl where application_id=101;

-----查询EBS系统的版本信息
select release_name from apps.fnd_product_groups;

-----查看EBS安装了哪些模块，以及这些模块的Level
select fpi.application_id,
       fav.application_name,
       fpi.status,
       fpi.patch_level  
from fnd_product_installations fpi,
     fnd_application_vl fav
where fpi.application_id=fav.APPLICATION_ID;
ORACLE信息
----查询一个对象所依赖的其它对象
select * from all_dependencies t where t.name='HZ_PARTIES';

----查询一个对象的创建信息(如表的创建者等信息)
select * from all_objects where object_name= 'XXWIP_5993_ASC_MATERIAL_VIEW';
GRANT EXECUTE ON apps.AP_INVOICE_LINES_UTILITY_PKG to xxap;

----查询(设置)当前环境的语言
SELECT USERENV('LANG') FROM DUAL;
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';

--查看当前用户拥有什么角色
select * from user_role_privs;

--查看当前用户拥有权限
select * from session_privs;

--查看当前用户拥有的系统权限
select * from user_sys_privs;

--查看当前用户拥有的表
select * from user_tables;

select * from user_synonyms t where t.synonym_name='RCV_TRANSACTIONS_BACK';

--查看当前用户已经使用多大的空间，允许使用的最大空间是多少
select tablespace_name,bytes,max_bytes from user_ts_quotas;

--查看都把哪些表什么权限赋予了其他用户
select * from user_tab_privs_made

--查询触发器
select * from dba_triggers t where t.trigger_name like '%CUX%';

--查询到具有sysdba权限的用户
select * from V_$PWFILE_USERS;
LOCK
SELECT p.spid,
       decode(locked_mode,
              0,
              'None',
              1,
              'Null',
              2,
              'Row share',
              3,
              'Row Exclusive',
              4,
              'Share',
              5,
              'Share Row Exclusive',
              6,
              'Exclusive') lock_type,
       a.client_identifier,
       c.object_name,
       b.session_id,
       b.oracle_username,
       b.os_user_name,
       b.locked_mode,
       a.sid,
       a.serial#,
       a.machine,
       a.action,
       to_char(a.logon_time, 'yyyy/mm/dd hh24:mi'),
       'ALTER system kill session ''' || a.sid || ',' || a.serial# || ''''
  FROM v$process p, v$session a, v$locked_object b, all_objects c
WHERE p.addr = a.paddr
   AND a.process = b.process
   AND c.object_id = b.object_id;

---Kill session
SELECT sid, serial# FROM v$session WHERE sid = &sid;

ALTER system kill session 'sid,serial#';

--ALTER system kill session '104,12418';

--锁package
SELECT s.sid, s.serial#, s.machine, 'ALTER system kill session ''' || s.sid || ',' || s.serial# || '''', t.*
  FROM v$access t, v$session s

WHERE s.sid = t.sid
   AND t.object = 'CUX_PO_CHECK_DETAIL_PKG';

SELECT b.*
  FROM v$session a, v$sqlarea b
WHERE a.sql_hash_value = b.hash_value
   AND a.sid = 9945;

SELECT b.sql_text, b.*
  FROM v$session a, v$sql b
WHERE a.sql_address = b.address
   AND a.sid = 9945
分割字符串
DECLARE
  l_text VARCHAR2(2000) := '0000,42015,19095,19098';
  TYPE bill_seq_table_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
  l_length        NUMBER;
  l_start         NUMBER := 1;
  l_index         NUMBER := 1;
  l_count         NUMBER := 1;
  l_sub_text      VARCHAR2(100);
  l_bill_seq_type bill_seq_table_type;
BEGIN
  l_length := length(l_text);

  WHILE (l_start < l_length) LOOP
  
    l_index := instr(l_text, ',', l_start);
  
    IF l_index = 0 THEN
      l_sub_text := substr(l_text, l_start, l_length - 1);
      l_start    := l_length;
    ELSE
      l_sub_text := substr(l_text, l_start, l_index - l_start);
      l_start    := l_index + 1;
    END IF;
  
    l_bill_seq_type(l_count) := l_sub_text;
    l_count := l_count + 1;
  END LOOP;

  FOR i IN l_bill_seq_type.first .. l_bill_seq_type.last LOOP
    dbms_output.put_line('l_sub_text' || i || ':' || l_bill_seq_type(i));
  END LOOP;

END;
查看表空间使用情况
SELECT dbf.tablespace_name,
       dbf.totalspace "总量(M)",
       dbf.totalblocks AS 总块数,
       dfs.freespace "剩余总量(M)",
       dfs.freeblocks "剩余块数",
       (dfs.freespace / dbf.totalspace) * 100 "空闲比例"
  FROM (SELECT t.tablespace_name, SUM(t.bytes) / 1024 / 1024 totalspace, SUM(t.blocks) totalblocks
          FROM dba_data_files t
         GROUP BY t.tablespace_name) dbf,
       (SELECT tt.tablespace_name, SUM(tt.bytes) / 1024 / 1024 freespace, SUM(tt.blocks) freeblocks
          FROM dba_free_space tt
         GROUP BY tt.tablespace_name) dfs
WHERE TRIM(dbf.tablespace_name) = TRIM(dfs.tablespace_name)
   AND dbf.tablespace_name='CUXDATA';

SELECT * FROM dba_tablespace_usage_metrics t WHERE t.tablespace_name = 'CUXDATA';