Retrieve Oracle SPID from unix OS PID

The Unix/Linux PID that you see in ‘top’ is visible as SPID in V$PROCESS. Then, you’d have to join V$PROCESS to V$SESSION to get the SID. (Note : PID in V$PROCESS is the Oracle PID, not the OS PID).

--Replace bind variable with OS PID List
select s.sid, s.serial#, s.username,
       to_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,
       p.pid oraclepid, p.spid "ServerPID", s.process "ClientPID",
       s.program clientprogram, s.module, s.machine, s.osuser,
       s.status, s.last_call_et
from  gv$session s, gv$process p
where p.spid=nvl('&unix_process',' ')
and s.paddr=p.addr
order by s.sidÔ…

Unregister Mview Snapshot Cleanly

Useful SQL to dynamically create unregister mview script for all mviews for a certain site or schema or both!

--Dynamic SQL - Unregister Snapshot info for a particular schema and database
select OWNER, NAME, SNAPSHOT_SITE, 
'execute dbms_snapshot.unregister_snapshot(''' || OWNER || ''',''' || NAME || ''',''' || SNAPSHOT_SITE || ''');' 
from dba_registered_snapshots rs
WHERE rs.OWNER = 'MY_SCHEMA'
and SNAPSHOT_SITE = 'MY_SNAPSHOT_SITE';

This links to my previous posts on this topic:

Find Site holding up mlog & Register Orphaned Mviews

Investigation SQL for Mview and Mlog growth

Also see metalink note “How to Manually Register/Unregister Snapshots” [ID 67371.1]

Investigating high number of sessions/scraping

Some queries to help investigate scraping or max session errors on the database:

--Total Session counts on database per schema
select username, Service_name, count(*) from gv$session
group by username, Service_name
HAVING count(*) > 100
order by 3 desc;ì*

--Total Session counts split by specific instance 
-- Use if only certain instances recieving max session error
select username, inst_id, Service_name, count(*) from gv$session
where inst_id in (1,2)
group by username, inst_id, Service_name
HAVING count(*) > 100
order by 4 desc;

Out machine names have Data Centre references so we are able to further split:

--Split Sessions by Data Centre
SELECT USERNAME, 
CASE MACHINE 
    WHEN '%dc1%' THEN 'DC1'
    ELSE 'DC2'
END AS DC
, count(*)
FROM gv$session
where SERVICE_NAME = 'MYSERVICE_1' and username = 'MYSCHEMA_2'
GROUP BY USERNAME, 
CASE MACHINE 
    WHEN '%dc1%' THEN 'DC1'
    ELSE 'DC2'
END 
ORDER BY 3

Trace Session for Schema on Login

The following trigger starts a session trace whenever a session for a particular schema logs into the database:

CREATE OR REPLACE TRIGGER ON_MY_SCHEMA_LOGIN
AFTER LOGON ON DATABASE
WHEN ( USER = 'MySCHEMA' )
BEGIN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

Level definitions:

For methods that require tracing levels, the following are valid values:

• 0 – No trace. Like switching sql_trace off.

• 2 – The equivalent of regular sql_trace.

• 4 – The same as 2, but with the addition of bind variable values.

• 8 – The same as 2, but with the addition of wait events.

• 12 – The same as 2, but with both bind variable values and wait events.

To turn off session level traces that are running:

ALTER SESSION SET EVENTS '10046 trace name context off';

Refreshing the Intellisense Cache in SSMS 2008+

Intellisense is a new feature that became available in SSMS 2008 onwards. Sometimes i find that intellisense becomes stale and you start getting the squiggly red lines when writing code..even though you are certain the object exists and is correctly spelt.

Refreshing the cache is easy but not necessarily obvious. There are two ways to refresh the cache:

1) Go to Edit -> IntelliSense -> Refresh Local Cache

2) Ctrl+Shift+R Shortcut

More details on Intellisense can be found here – http://msdn.microsoft.com/en-us/library/hh245114.aspx

Intellisense shortcuts

History of SQL Server 1989 – 2012

Saw this on another blog and thought it was a pretty cool, concise history lesson for SQL Server versions.

(http://robbagley.wordpress.com/2012/03/19/the-history-of-sql-server/)

Timeline:

SQL Server Release History
Version	Year	Release Name	Codename
1	1989	SQL Server 1.0	-
(OS/2)		(16bit)
1.1	1991	SQL Server 1.1	-
(OS/2)		(16bit)
4.21	1993	SQL Server 4.21	SQLNT
(WinNT)
6	1995	SQL Server 6.0	SQL95
6.5	1996	SQL Server 6.5	Hydra
7	1998	SQL Server 7.0	Sphinx
-	1999	SQL Server 7.0	Palato mania
		OLAP Tools
8	2000	SQL Server 2000	Shiloh
8	2003	SQL Server 2000	Liberty
		64-bit Edition
9	2005	SQL Server 2005	Yukon
10	2008	SQL Server 2008	Katmai
10.25	2010	SQL Azure DB	CloudDatabase
10.5	2010	SQL Server 2008 R2	Kilimanjaro (aka KJ)
11	2012	SQL Server 2012	Denali

EXCEPT & INTERSECT in SQL Server – Same as MINUS & UNION in Oracle

Need to run a query to find the distinct different rows in an oracle table compared to a SQL server table. These tables are comparable because the source SQL Server table is replicated to the Oracle Schema.

Note: This example shows the use of Except in a more complex scenario but it can be used in its simplest form to return results from 2 SQL server tables (with the proviso that they are comparable tables)

MSDN – EXCEPT and INTERSECT

This SQL requires a link server to be in place linking to the oracle schema with select permissions.

SELECT Person_ID, Misc_Data_ID
FROM OPENQUERY(ORA_TEMP, 'select * from MYSchema.misc_data')
EXCEPT
SELECT Person_ID, Misc_Data_ID 
FROM misc_data
  WHERE (Person_ID IN 
    (SELECT Person_ID FROM Person 
    WHERE (Customer_ID IN 
        (SELECT Customer_ID FROM Customer WHERE Business_Subset_ID = 123456))))

In the above SQL, EXCEPT finds distinct rows which are in the oracle schema which are NOT in the SQL table. To make sure that we are returning exact rows, i use the primary key on the oracle table for the except comparison.

INTERSECT works in the same way, but finds all common rows between the 2 tables.

SELECT Person_ID, Misc_Data_ID
FROM OPENQUERY(ORA_TEMP, 'select * from MYSchema.misc_data')
INTERSECT
SELECT Person_ID, Misc_Data_ID 
FROM misc_data
  WHERE (Person_ID IN 
    (SELECT Person_ID FROM Person 
    WHERE (Customer_ID IN 
        (SELECT Customer_ID FROM Customer WHERE Business_Subset_ID = 123456))))

EXCEPT is doing the same functionality as the MINUS operation in Oracle, whereas INTERSECT is the same as the UNION operation.