Find and Kill Scraping sessions

Firstly find out the offending schemas:

select username, count(*) from gv$session
group by username 
order by 2 desc

Then, if applicable, find the offending machine\terminal

select username, machine, terminal, count(*) from gv$session
where username = 'SCHEMA' 
group by username, machine, terminal
order by 4 desc;

If the sessions are not dying off buy stopping the application\process, generate Kill statements for each session:

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT --s.inst_id,
       --s.sid,
       --s.serial#,
       --p.spid,
       --s.username,
       --s.program,
    'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;'
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' and s.username='SCHEMA';

Full details on how to Kill oracle sessions here

Run CHECKDB against all databases

Set Nocount on
Declare @dbname varchar(100) 
Declare @servername varchar(100) 

Declare db Cursor For
        Select name from master.dbo.sysdatabases

Declare @osql varchar(1000)

select @servername = @@servername

Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
    Begin
        Set @osql='DBCC Checkdb (['+@dbname+']) WITH PHYSICAL_ONLY'
        EXEC (@osql) --Execute the osql statement
        Fetch Next from db into @dbname    
    End
Close db

Deallocate db

GO

Find SQL Server Start Time Quickly

DECLARE @WorkingVariable datetime

SELECT @WorkingVariable = login_time
FROM master..sysprocesses
WHERE cmd='LAZY WRITER'

print @WorkingVariable

The above will simply return the date when the LAZY Writer process started.

SELECT create_date FROM sys.databases WHERE NAME='tempdb'

The above simply checks when Tempdb started

SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
  SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
  SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
  PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))
    +'" has been Online for the past '+@hr+' hours & '+@min+' minutes.'
    + 'Instance start time is ' + CONVERT(varchar(23),@crdate, 113)

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses 
                            WHERE program_name like N'SQLAgent90%')
 BEGIN
   PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
 END
 ELSE BEGIN
   PRINT 'SQL Server and SQL Server Agent are both running'
 END

The above produces user friendly information on how many hours the Server has been up for, and other useful info.

image

Investigation SQL for Mview and Mlog growth

--Find oldest snaptime for a mlog - i.e. time the mlog has been building up since 
select * from sys.slog$
where mowner = 'SCHEMA'
and Master = 'TABLE'
ORDER BY SNAPTIME DESC;
--row growth every time the mlog updates
select count(*)
from ATD_UK_FP.MLOG$_FP_INET_OPTIONS
group by SNAPTIME$$;
-- SNapshot sites for a particular mview
select owner, name ,snapshot_site, snapshot_id, refresh_method 
from dba_registered_snapshots rs
WHERE rs.OWNER = 'SCHEMA'
and rs.Name = 'TABLE'
order by rs.owner, rs.name;
--Find which snapshot_site is holding up the mlog
select sl.*, rs.snapshot_site, rs.owner, rs.name, rs.refresh_method 
from sys.slog$ sl
left join dba_registered_snapshots rs on rs.SNAPSHOT_ID = sl.SNAPID
where mowner = 'SCHEMA'
and Master = 'TABLE'
ORDER BY SNAPTIME DESC;
--Find unregistered snaps and snaps older than certain number of days 
--not being refreshed
select mowner, master, snapid,  nvl(r.snapsite, 'not registered') snapsite,snaptime
from   sys.slog$ s, sys.reg_snap$ r where  s.snapid=r.snapshot_id(+) and
s.snaptime < (sysdate-0)
and mowner = 'SCHEMA';

Useful Oracle Metalink Docs

236233.1 – Materialized View Refresh : Log Population and Purge

727632.1 – What to do when a Materialized View Log is not cleared automatically after a Fast Refresh

258634.1 – Materialized View registration at Master Site

Backgrounding a linux task

Problem:  Want to make a Oracle mview refresh run in the background so it doesnt timeout after 2 hours of running (our company’s network timeout)

On the Oracle Server

  • Create the file with the SQL code you want to run. Mine is called mv_fp.sql
  • BEGIN
      DBMS_SNAPSHOT.REFRESH(
        LIST => 'SCHEMA.MV_MVIEW'
       ,METHOD => 'C'
       ,PUSH_DEFERRED_RPC => TRUE
       ,REFRESH_AFTER_ERRORS => FALSE
       ,PURGE_OPTION => 1
       ,PARALLELISM => 4
       ,ATOMIC_REFRESH => TRUE
       ,NESTED => FALSE);
    END;
    /
  • Create the file with the shell code you want to run. Mine is called mv_fp.sh
  • export ORACLE_SID=MYDB
    sqlplus schema/password << eof
    @mv_fp.sql
    exit
    eof
    • Grant execute access on the .sh file

    chmod 744 mv_fp.sh
    • Run the following to background the task

    nohup ./mv_fp.sh > nohup_mv_fp.out 2>&1 &

Trust Locations in Access

How to prevent the following warning propping up for users every time they open your access application:

image

When you open the packaged application, you receive the following message:
A potential security concern has been identified.

Warning: It is not possible to determine that this content came from a 
trustworthy source. You should leave this content disabled unless the 
content provides critical functionality and you trust its source.

File path: <path>

This file might contain unsafe content that could harm your computer. 
Do you want to open this file or cancel the operation?

You can add folders to trusted locations from Office Button | Access Options | Trust Center | Trust Center Settings…| Trusted Locations

The entries will be stored in the registry under:

Access 2007

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations]

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location(n)]

Access 2010

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security\Trusted Locations]

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security\Trusted Locations\Location(n)]

Path: C:\PathtoDB\

AllowSubFolders: 1

Date: DD/MM/YYYY HH:MM

If packaging up an access solution, then place the registry keys in the Package solution wizard.

Package Access Runtime Application with ODBC

I build a little application in access 2007 which connected to a SQL Server backend via a SQL authenticated Login.

I packaged the solution up as an installer, but didn’t want to manually go round pushing out ODBC connection separate to the install process. I wanted to be able to install the ODBC as part of the main installer, and then be able to remove the ODBC connection when the app was uninstalled.

The error in question:

image

The Access package solution wizard is useful for inserting registry keys into a machine as part of an install which would resolve the above error.

To enter an ODBC connection in the registry, i added the following keys (Enterprise Director is the name of my ODBC connection)

Root: Current User
Key: Software\ODBC\ODBC.INI\Enterprise Director
Name: Driver
Value: C:\Windows\system32\SQLSRV32.dll

Root: Current User
Key: Software\ODBC\ODBC.INI\Enterprise Director
Name: Server
Value: ServerName

Root: Current User
Key: Software\ODBC\ODBC.INI\Enterprise Director
Name: Database
Value: DatabaseName

LastUser is the SQL authenticated login to access the database
Root: Current User
Key: Software\ODBC\ODBC.INI\Enterprise Director
Name: LastUser
Value: CMEntDir

image

The overall effect of having these keys is that the ODBC is installed automatically without any additional action.

When opening the App, i know get the following prompt:

image

and the registry key is installed:

image

image