EBS常用SQL脚本

界面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';