EBS常用SQL脚本
Mikasa 2013-10-14
界面LOV的SQL获取
DECLARE
CURSORcur_lov_sql(p_pre_sql_addrVARCHAR2)IS
SELECTt.sql_textFROMv$sqltext_with_newlinestWHEREt.address=p_pre_sql_addrORDERBYt.piece;
--
l_lov_sqlVARCHAR2(2400);
l_prev_sql_addrVARCHAR2(200);
BEGIN
BEGIN
SELECTv.prev_sql_addrINTOl_prev_sql_addrFROMv$sessionvWHEREv.sid=&sid;
EXCEPTION
WHENOTHERSTHEN
dbms_output.put_line('getprevsqladdrerror,'||SQLCODE||':'||SQLERRM);
END;
--
FORrecINcur_lov_sql(l_prev_sql_addr)LOOP
l_lov_sql:=nvl(l_lov_sql,'')||rec.sql_text;
ENDLOOP;
--
dbms_output.put_line('lov查询sql为:');
dbms_output.put_line(l_lov_sql);
END;
EBS信息
-----查询APPLICATIONID所对应的应用产品
select*fromfnd_application_vlwhereapplication_id=101;
-----查询EBS系统的版本信息
selectrelease_namefromapps.fnd_product_groups;
-----查看EBS安装了哪些模块，以及这些模块的Level
selectfpi.application_id,
fav.application_name,
fpi.status,
fpi.patch_level
fromfnd_product_installationsfpi,
fnd_application_vlfav
wherefpi.application_id=fav.APPLICATION_ID;
ORACLE信息
----查询一个对象所依赖的其它对象
select*fromall_dependenciestwheret.name='HZ_PARTIES';
----查询一个对象的创建信息(如表的创建者等信息)
select*fromall_objectswhereobject_name='XXWIP_5993_ASC_MATERIAL_VIEW';
GRANTEXECUTEONapps.AP_INVOICE_LINES_UTILITY_PKGtoxxap;
----查询(设置)当前环境的语言
SELECTUSERENV('LANG')FROMDUAL;
ALTERSESSIONSETNLS_LANGUAGE='AMERICAN';
ALTERSESSIONSETNLS_LANGUAGE='SIMPLIFIEDCHINESE';
--查看当前用户拥有什么角色
select*fromuser_role_privs;
--查看当前用户拥有权限
select*fromsession_privs;
--查看当前用户拥有的系统权限
select*fromuser_sys_privs;
--查看当前用户拥有的表
select*fromuser_tables;
select*fromuser_synonymstwheret.synonym_name='RCV_TRANSACTIONS_BACK';
--查看当前用户已经使用多大的空间，允许使用的最大空间是多少
selecttablespace_name,bytes,max_bytesfromuser_ts_quotas;
--查看都把哪些表什么权限赋予了其他用户
select*fromuser_tab_privs_made
--查询触发器
select*fromdba_triggerstwheret.trigger_namelike'%CUX%';
--查询到具有sysdba权限的用户
select*fromV_$PWFILE_USERS;
LOCK
SELECTp.spid,
decode(locked_mode,
0,
'None',
1,
'Null',
2,
'Rowshare',
3,
'RowExclusive',
4,
'Share',
5,
'ShareRowExclusive',
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/ddhh24:mi'),
'ALTERsystemkillsession'''||a.sid||','||a.serial#||''''
FROMv$processp,v$sessiona,v$locked_objectb,all_objectsc
WHEREp.addr=a.paddr
ANDa.process=b.process
ANDc.object_id=b.object_id;
---Killsession
SELECTsid,serial#FROMv$sessionWHEREsid=&sid;
ALTERsystemkillsession'sid,serial#';
--ALTERsystemkillsession'104,12418';
--锁package
SELECTs.sid,s.serial#,s.machine,'ALTERsystemkillsession'''||s.sid||','||s.serial#||'''',t.*
FROMv$accesst,v$sessions
WHEREs.sid=t.sid
ANDt.object='CUX_PO_CHECK_DETAIL_PKG';
SELECTb.*
FROMv$sessiona,v$sqlareab
WHEREa.sql_hash_value=b.hash_value
ANDa.sid=9945;
SELECTb.sql_text,b.*
FROMv$sessiona,v$sqlb
WHEREa.sql_address=b.address
ANDa.sid=9945
分割字符串
DECLARE
l_textVARCHAR2(2000):='0000,42015,19095,19098';
TYPEbill_seq_table_typeISTABLEOFVARCHAR2(100)INDEXBYBINARY_INTEGER;
l_lengthNUMBER;
l_startNUMBER:=1;
l_indexNUMBER:=1;
l_countNUMBER:=1;
l_sub_textVARCHAR2(100);
l_bill_seq_typebill_seq_table_type;
BEGIN
l_length:=length(l_text);
WHILE(l_start<l_length)LOOP
l_index:=instr(l_text,',',l_start);
IFl_index=0THEN
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;
ENDIF;
l_bill_seq_type(l_count):=l_sub_text;
l_count:=l_count+1;
ENDLOOP;
FORiINl_bill_seq_type.first..l_bill_seq_type.lastLOOP
dbms_output.put_line('l_sub_text'||i||':'||l_bill_seq_type(i));
ENDLOOP;
END;
查看表空间使用情况
SELECTdbf.tablespace_name,
dbf.totalspace"总量(M)",
dbf.totalblocksAS总块数,
dfs.freespace"剩余总量(M)",
dfs.freeblocks"剩余块数",
(dfs.freespace/dbf.totalspace)*100"空闲比例"
FROM(SELECTt.tablespace_name,SUM(t.bytes)/1024/1024totalspace,SUM(t.blocks)totalblocks
FROMdba_data_filest
GROUPBYt.tablespace_name)dbf,
(SELECTtt.tablespace_name,SUM(tt.bytes)/1024/1024freespace,SUM(tt.blocks)freeblocks
FROMdba_free_spacett
GROUPBYtt.tablespace_name)dfs
WHERETRIM(dbf.tablespace_name)=TRIM(dfs.tablespace_name)
ANDdbf.tablespace_name='CUXDATA';
SELECT*FROMdba_tablespace_usage_metricstWHEREt.tablespace_name='CUXDATA';
