EBS常用SQL脚本

[b]界面LOV的SQL获取[/b]
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;
[b]EBS信息[/b]
-----查询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;
[b]ORACLE信息[/b]
----查询一个对象所依赖的其它对象
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;
[b]LOCK[/b]
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
[b]分割字符串[/b]
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;
[b]查看表空间使用情况[/b]
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';