Querry to find the Last executed SQL statement from particular schema or table

SELECT CREATED, TIMESTAMP, last_ddl_time
FROM all_objects
WHERE OWNER = 'EXSCHEMA'
AND OBJECT_TYPE = 'TABLE'
AND OBJECT_NAME = 'EMPLOYEE_TABLE';

Database: Query to get current session ID, Process ID & Client Process ID

SELECT b.sid,
b.serial#,
a.spid processid,
b.process clientpid
FROM v$process a, v$session b
WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');

1) V$SESSION.SID AND V$SESSION.SERIAL# is database process id
2) V$PROCESS.SPID is shadow process id on this database server
3) V$SESSION.PROCESS is client PROCESS ID

Database: Query to find the Long running query

SELECT a.sid,
a.serial#,
b.username,
opname OPERATION,
target OBJECT,
TRUNC (elapsed_seconds, 5) "ET (s)",
TO_CHAR (start_time, 'HH24:MI:SS') start_time,
ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid
AND b.username NOT IN ('SYS', 'SYSTEM')
AND totalwork > 0
ORDER BY elapsed_seconds;

EBS: Query to find the CPU usage of the user

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.STATISTIC# = sn.STATISTIC#
AND NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
AND ss.status = 'ACTIVE'
AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

Short Names of Oracle E-Business Suite Modules

APPLICATION NAME	SHORT NAME	PRODUCT CODE	APP ID
ADS Development	ADS_DEV	ADS_DEV	20062
APAC Consulting Localizations	CLA	CLA	13017
Advanced Benefits	BEN	PER	805
Advanced Outbound Telephony	IEC	IEC	545
Advanced Pricing	QP	QP	661
Advanced Product Catalog	EGO	EGO	431
Advanced Supply Chain Planning	MSC	MSC	724
Alert	ALR	ALR	160
Application Implementation	AZ	AZ	190
Application Object Library	FND	FND	0
Application Report Generator	RG	GL	168
Application Utilities	AU	AU	3
Applications BIS	BIS	BIS	191
Applications DBA	AD	AD	50
Applications Demonstration Services	ADS	ADS	20041
Applications Shared Technology	SHT	SHT	60
Approvals Management	AME	AME	203
Asia/Pacific Localizations	JA	JA	7000
Asset Tracking	CSE	CSE	873
Assets	OFA	FA	140
Automotive	VEA	VEA	663
Banking	BANKING	BANKING	20064
Bills of Material	BOM	BOM	702
Budgeting and Planning	PBR	PBR	502
Build/syncronize Contracts Repository Text index	OKC_REP_TXT_INDEX_SYNC	OKC_REP_TXT_INDEX_SYNC	20160
CPG - CDOA	CPGC	CPGC	20090
CRM Foundation	JTF	JTF	690
CRM Gateway for Mobile Devices	ASG	ASG	689
Call Center	CSN	CSN	20180
Call Center Connectors	IET	IET	547
Capacity	CRP	CRP	705
Capital Resource Logistics - Assets	CUA	CUA	8731
Capital Resource Logistics - Projects	IPA	IPA	8721
Cash Management	CE	CE	260
Citizen Interaction Center	CUG	CUG	866
Clinical Transaction Base	CTB	CTB	3002
Collections	IEX	IEX	695
Common Modules-AK	AK	AK	601
Communications BRM	COMMS_BRM	COMMS_BRM	20068
Communications Intelligence	BIN	BIN	679
Complex Maintenance Repair and Overhaul	AHL	AHL	867
Configurator	CZ	CZ	708
Constraint Based Optimization	MSO	MSO	723
Content Manager	IBC	IBC	549
Contract Commitment	IGC	IGC	8407
Contracts Core	OKC	OKC	510
Contracts Integration	OKX	OKX	524
Core Banking	COREBANK	COREBANK	20065
Cost Management	CST	BOM	707
Culinary Application	CLR	CLR	20021
Custom Development	CUSTOM	CUSTOM	20061
Custom General Ledger	CUSTGL	CUSTGL	20067
Customer Care	CSC	CSC	511
Customers Online	IMC	IMC	879
Data Query	ODQ	ODQ	525
DateTrack	DT	PER	803
Demand Planning	MSD	MSD	722
Demand Signal Repository	DDR		9003
Demo Order Entry (AOL Class)	DEM	DEM	20001
Depot Repair	CSD	CSD	512
Development	DNA	DNA	429
Digital Asset Management	IAM	IAM	878
Document Managment and Collaboration	DOM	DOM	432
E-Business Tax	ZX	ZX	235
E-Records	EDR	EDR	709
EMEA Consulting Localizations	CLE	CLE	13014
Email Center	IEM	IEM	680
Engineering	ENG	ENG	703
Enterprise Asset Management	EAM	EAM	426
Environment Management System	EMS	EMS	40
European Localizations	JE	JE	7002
Exchange	POM	POM	298
FastFormula	FF	PER	802
Federal Financials	FV	FV	8901
Field Service	CSF	CSF	513
Field Service/Laptop	CSL	CSL	868
Field Service/Palm	CSM	CSM	883
Financials Common Modules	FUN	FUN	435
Flow Manufacturing	FLM	FLM	714
Genealogy Intelligence	GNI	GNI	747
General Ledger	SQLGL	GL	101
Global Accounting Engine	AX	AX	600
Grants Accounting	GMS	GMS	8402
Healthcare	HCA	HCA	3004
Healthcare Intelligence	HCP	HCP	3003
Healthcare Terminology Server	HCT	HCT	3005
Human Resources	PER	PER	800
IVR Integrator	IEV	IEV	546
Incentive Compensation	CN	CN	283
Installed Base	CSI	CSI	542
Institutional Banking	INSTITBANK	INSTITBANK	20066
Interaction Blending	IEB	IEB	699
Interaction Center Technology	IEO	IEO	539
Internet Procurement Enterprise Connector	ITG	ITG	230
Inventory	INV	INV	401
Inventory Optimization	MSR	MSR	726
Japan Consulting Localizations	CLJ	CLJ	13016
LAD Consulting Localizations	CLL	CLL	13015
Labor Distribution	PSP	PSP	8403
Latin America Localizations	JL	JL	7004
Learning Management	OTA	OTA	810
Lease and Finance Management	OKL	OKL	540
Legal Entity Configurator	XLE	XLE	204
Loans	LNS	LNS	206
Manufacturing	MFG	MFG	700
Manufacturing Execution System for Process Manufacturing	GMO		560
Manufacturing Mobile Applications	WMA	WMA	407
Manufacturing Scheduling	WPS	WPS	388
Marketing	AMS	AMS	530
Marketing Encyclopedia System	AMV	AMV	520
Mass Market Receivables for Comms	CUR	CUR	8725
Master Scheduling/MRP	MRP	MRP	704
Media Interactive	MIV	MIV	860
Mobile Application Foundation	JTM	JTM	874
Mobile Applications	MWA	MWA	405
Mobile Applications for Inventory Management	MIA	MIA	403
Mobile Quality Applications	MQA	MQA	425
Number Portability	XNP	XNP	534
Optimize Contracts Repository Text index	OKC_REP_TXT_INDEX_OPTIMIZE	OKC_REP_TXT_INDEX_OPTIMIZE	20161
Oracle Applications Manager	OAM	OAM	99
Oracle Clinical Data Repository	CDR	CDR	3006
Oracle Deal Management	QPR		667
Oracle E-Business Suite Diagnostics	IZU		278
Oracle Environmental Accounting and Reporting	GHG		9008
Oracle Imaging Process Management	IPM		207
Oracle In-Memory Cost Management	CMI		9010
Oracle Landed Cost Management	INL		9004
Oracle Manufacturing Operations Center	MTH		9001
Oracle Price Protection	DPP		9000
Oracle Sales for Handhelds	ASP	ASP	869
Oracle Telecommunications Billing Integrator	XNB	XNB	881
Oracle Web Analytics	IBW	IBW	666
Oracle Yard Management	YMS		9009
Oracle iProcurement	ICX	ICX	178
Order Capture	ASO	ASO	697
Order Entry	OE	OE	300
Order Management	ONT	ONT	660
Outsourced Manufacturing for Discrete Industries	JMF	JMF	778
Partner Management	PV	PV	691
Patch Tracking System	PTX	PTX	41
Payables	SQLAP	AP	200
Payments	IBY	IBY	673
Payroll	PAY	PER	801
Predictive	IEP	IEP	548
Process Manufacturing Financials	GMF	GMF	555
Process Manufacturing Inventory	GMI	GMI	551
Process Manufacturing Logistics	GML	GML	556
Process Manufacturing Portal	GMW	GMW	559
Process Manufacturing Process Execution	GME	GME	553
Process Manufacturing Process Planning	GMP	GMP	554
Process Manufacturing Product Development	GMD	GMD	552
Process Manufacturing Regulatory Management	GR	GR	557
Process Manufacturing Systems	GMA	GMA	550
Progress Custom	PRGC	PRGC	20063
Project Contracts	OKE	OKE	777
Project Manufacturing	PJM	PJM	712
Project Portfolio Analysis	FPA	FPA	440
Projects	PA	PA	275
Property Manager	PN	PN	240
Proposals	PRP	PRP	694
Provisioning	XDP	XDP	535
Public Sector Financials	PSA	PSA	8450
Public Sector Financials International	IGI	IGI	8400
Public Sector HR	PQH	PQH	8302
Public Sector Payroll	PQP	PQP	8303
Public Sector Receivables	PSR	PSR	20139
Purchasing	PO	PO	201
Quality	QA	QA	250
Quoting	QOT	QOT	880
Receivables	AR	AR	222
Regional Localizations	JG	JG	7003
Release Management	RLM	RLM	662
Report Manager	FRM	FRM	265
Retail Core	RRC	RRC	719
Revenue Accounting	CUC	CUC	8726
Risk Management	QRM	QRM	186
Risk Manager	RMG	RMG	509
Royalty Management	OKT	OKT	877
SSP	SSP	SSP	804
Sales	ASN	ASN	280
Sales Analysis	AN	AN	501
Sales Foundation	AS	AS	279
Sales Offline	ASL	ASL	544
Sales Online	ASF	ASF	522
Scheduler	CSR	CSR	698
Scripting	IES	IES	519
Service	CS	CS	170
Service Assurance for Communications	XNA	XNA	536
Service Contracts	OKS	OKS	515
Shipping Execution	WSH	WSH	665
Shop Floor Management	WSM	WSM	410
Site Management	RRS	RRS	718
Sourcing	PON	PON	396
Spares Management	CSP	CSP	523
Subledger Accounting	XLA	XLA	602
Supplier Scheduling	CHV	CHV	202
Supply Chain Trading Connector for RosettaNet	CLN	CLN	701
System Administration	SYSADMIN	SYSADMIN	1
Systems Intelligence	BIY	BIY	863
Team 01 Order Entry Demo	DEM01	DEM01	20200
TeleBusiness for Telecom/Utilities	XNT	XNT	537
TeleSales	AST	AST	521
Telephony Manager	CCT	CCT	172
Time and Labor	HXT	HXT	808
Time and Labor Engine	HXC	HXC	809
Trade Management	OZF	OZF	682
Transportation Execution	FTE	FTE	716
Treasury	XTR	XTR	185
US Federal Human Resources	GHR	PER	8301
Universal Work Queue	IEU	IEU	696
University Curriculum	OUC	OUC	20114
Utility Billing	BLC	BLC	436
Warehouse Management	WMS	WMS	385
Web Applications Desktop Integrator	BNE	BNE	231
Work in Process	WIP	WIP	706
XML Gateway	ECX	ECX	174
XML Publisher	XDO	XDO	603
e-Commerce Gateway	EC	EC	175
eCommerce Intelligence	BIE	BIE	678
iAssets	IA	IA	205
iAuction	IBT	IBT	675
iHCIntegrate	HCN	HCN	3001
iRecruitment	IRC	IRC	821
iSettlement	ISX	ISX	415
iStore	IBE	IBE	671
iSupplier Portal	POS	POS	177
iSupport	IBU	IBU	672