Find OMS details

http://www.aodba.com/find-omsoracle-management-server-database-repository-details/

Find Oracle Temp Usage

column sum_max_mb format 999,999,999;
column temporary_tablespace format A20
WITH
pivot1 AS
(
SELECT
trunc(ash.sample_time,'MI') sample_time,
ash.SESSION_ID,
ash.SESSION_SERIAL#,
ash.SQL_ID,
ash.sql_exec_id,
U.temporary_tablespace,
max(temp_space_allocated)/(1024*1024) max_temp_mb
FROM  GV$ACTIVE_SESSION_HISTORY ash, dba_users U
WHERE
ash.user_id = U.user_id
and ash.session_type = 'FOREGROUND'
and ash.temp_space_allocated > 0
GROUP BY
trunc(ash.sample_time,'MI'),
ash.SESSION_ID,
ash.SESSION_SERIAL#,
ash.SQL_ID,
ash.sql_exec_id,
U.temporary_tablespace
)
SELECT  temporary_tablespace, sample_time, sum(max_temp_mb) sum_max_mb
from pivot1
GROUP BY sample_time, temporary_tablespace
ORDER BY temporary_tablespace, sample_time
DESC;


SELECT * FROM DBA_TEMP_FREE_SPACE;

SQL Server list connection details between dates

SELECT 
    c.session_id, c.net_transport, c.encrypt_option, 
    c.auth_scheme, s.host_name, s.program_name, 
    s.client_interface_name, s.login_name, s.nt_domain, 
    s.nt_user_name, s.original_login_name, c.connect_time, 
    s.login_time 
FROM sys.dm_exec_connections AS c 
JOIN sys.dm_exec_sessions AS s 
    ON c.session_id = s.session_id
where last_request_start_time
between  '2019-07-29' and '2019-08-01'

How to change java heap size EBS r12

http://madhanappsdba.blogspot.com/2016/01/how-to-change-java-heap-size-in-r12-to.html

SQL SERVER WAIT TYPES

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-2017#WaitTypes

How to create SQL SERVER email notification

EXEC msdb.dbo.sp_add_alert

@name=N'AlwaysOn - Role Change',

@message_id=1480,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=0,

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

--AlwaysOn - Data Movement Suspended

EXEC msdb.dbo.sp_add_alert

@name=N'AlwaysOn - Data Movement Suspended',

@message_id=35264,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=0,

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

--AlwaysOn - Data Movement Resumed

EXEC msdb.dbo.sp_add_alert

@name=N'AlwaysOn - Data Movement Resumed',

@message_id=35265,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=0,

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

Then create operator

SSMS> object explorer>sql server agent>operators

Find longest running query in SQL SERVER

CREATE TABLE T1(Col1 int, Col2 char(3));
GO
BEGIN TRAN
INSERT INTO T1 VALUES (101, 'abc');
GO
DBCC OPENTRAN;
ROLLBACK TRAN;
GO
DROP TABLE T1;
GO