WAIT STATS

IF (SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') as varchar(128)))-1) as int)) >= 9

      BEGIN

            IF object_id('tempdb..#BufferUsage') IS NOT NULL

                        DROP TABLE #BufferUsage

                 

                  CREATE TABLE #BufferUsage(

                        [Database_ID] [int] NULL,

                        [Database_Name] [varchar] (512) NULL,

                        [object_id] [int] NULL,

                        [Object] [varchar] (512) NULL,

                        [Type] [varchar] (128) NULL,

                        [Index] [varchar](512) NULL,

                        [Index_Type] [varchar](128) NULL,

                        [Buffer_Pages] [bigint] NULL,

                        [Buffer_MB] [float] NULL

                  ) ON [PRIMARY]               

                 

                 

                  Declare @DataBase VarChar(512)

                  Set @Database = ''

                 

                  While (@Database IS NOT NULL)

                        BEGIN

                              SET @Database = (SELECT MIN(Name)

                                    FROM master.sys.databases

                                    Where Name > @Database

                                          AND state_desc = 'ONLINE'

                                          AND Name NOT IN ('LiteSpeedLocal', 'master', 'model', 'msdb', 'tempdb')

                                          )

                             

                                    /****** START LOOP ********/

                                    IF LEN(@Database) > 0

                                    BEGIN

                                          EXECUTE ('

                                                USE [' + @Database + ']; 

                                                WITH [Source]

                                                      AS(SELECT [Object] = [Objects].name

                                                            ,[Type] = [Objects].type_desc

                                                            ,[Index] = COALESCE([Indexes].name, '''')

                                                            ,[Index_Type] = [Indexes].type_desc

                                                            ,[Partitions].[object_id]

                                                            ,[Partitions].index_id

                                                            ,[Allocation_Units].allocation_unit_id  

                                                            FROM [' + @Database + '].sys.partitions AS [Partitions]

                                                INNER JOIN [' + @Database + '].sys.allocation_units AS [Allocation_Units]      

                                                ON [Partitions].hobt_id = [Allocation_Units].container_id  

                                                INNER JOIN [' + @Database + '].sys.objects AS [Objects]     

                                                ON [Partitions].[object_id] = [Objects].[object_id]  

                                                INNER JOIN [' + @Database + '].sys.indexes AS [Indexes]     

                                                ON [Objects].[object_id] = [Indexes].[object_id]      

                                                      AND [Partitions].index_id = [Indexes].index_id  

                                                WHERE [Allocation_Units].[type] IN (1,2,3)      

                                                      AND [Objects].is_ms_shipped = 0)

                                               

                                                INSERT INTO #BufferUsage ([Database_ID], [Database_Name], [object_id], [Object], [Type], [Index], [Index_Type], [Buffer_Pages], [Buffer_MB])

                                                SELECT MAX([Database_ID]) as [Database_ID], ''' + @Database + ''', [Source].[object_id], [Source].[Object], [Source].[Type],   [Source].[Index],   [Source].Index_Type,   buffer_pages = COUNT_BIG([Buffer_Descriptors].page_id),   buffer_mb = COUNT_BIG([Buffer_Descriptors].page_id) / 128

                                                FROM [Source]

                                                INNER JOIN   [' + @Database + '].sys.dm_os_buffer_descriptors AS [Buffer_Descriptors]  

                                                ON [Source].allocation_unit_id = [Buffer_Descriptors].allocation_unit_id

                                                WHERE   [Buffer_Descriptors].database_id = DB_ID()

                                                GROUP BY [Source].[object_id], [Source].[Object],[Source].[Type],[Source].[Index],[Source].Index_Type

                                                ORDER BY   buffer_pages DESC

                                                ')

                                    END

                                    /****** END LOOP **********/

                        END

           

                  SELECT * FROM #BufferUsage

                  DROP TABLE #BufferUsage

                                         

            END  -- SQL Version

POWERSHELL | QUERY ORACLE DATABASE

HERE IS A SCRIPT I WROTE TO QUERY AN ORACLE DATABASE INTO A DATASET AND THEN DISPLAY IN GRIDVIEW (OR OTHER OF YOUR CHOOSEING)

#Load Oracle Assembly

if ($Oracle_Loaded -eq $null) {$Oracle_Loaded = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")}

Function Execute_OracleCommand([string]$HostName, [string]$ServiceName, [int]$Port, [string]$SQLStatement, [string]$Login, [string]$Password, [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$OutputType="DataRow")

{

              $SQLStatement = $SQLStatement -replace "\`$",'$'   # DBA Views have $ in the name ex. v$Database

              $SQLStatement = $SQLStatement -replace "\`#",'#'   # Oracle likes to use # for primary foreign key relationships

              $ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) `

                                                (HOST=$HostName)(PORT=$Port)) `

                                                (CONNECT_DATA=(SERVICE_NAME=$ServiceName))) `

                                                ;User Id=$Login;Password=$Password;"

              $OracleConnection = new-object Oracle.DataAccess.Client.OracleConnection

        $OracleConnection.ConnectionString = $ConnectionString

        $OracleCommand = new-object Oracle.DataAccess.Client.OracleCommand($SQLStatement,$OracleConnection)

        $OracleCommand.CommandTimeout=0

        $DataSet = New-Object system.Data.DataSet

        $DataAdapter = New-Object Oracle.DataAccess.Client.OracleDataAdapter($OracleCommand)

        try

              {

                     $OracleConnection.Open()

                     $null = $DataAdapter.fill($DataSet)

                     $OracleConnection.close()

                     switch ($OutputType)

                  {

                      'DataSet'   { Write-Output ($DataSet) }

                      'DataTable' { Write-Output ($DataSet.Tables) }

                      'DataRow'   { Write-Output ($DataSet.Tables[0]) }

                  } 

              }

           catch

           {

                     $ex = $_.Exception

                     Write-Output $ex.Message

              }

              Finally

              {

                     #Close the connection even if it fails

                     $OracleConnection.Close()

              }

}

function Execute-NonQuery($SQLStatement)

{

        $OracleConnection = new-object Oracle.DataAccess.Client.OracleConnection

        $OracleConnection.ConnectionString = $ConnectionString

        $OracleCommand = new-object Oracle.DataAccess.Client.OracleCommand($SQLStatement,$OracleConnection)

        $OracleCommand.CommandTimeout=0

        try

              {

                     $OracleConnection.Open()

              $OracleCommand.ExecuteNonQuery()

                     $OracleConnection.Close()               

              }

           catch

           {

                     $ex = $_.Exception

                     Write-Output $ex.Message

              }

              Finally

              {

                     #Close the connection even if it fails

                     $OracleConnection.Close()

              }

}

Clear-Host

$HostName = "dss0s001.svr.us.jpmchase.net"

$ServiceName = "pistr.svr.us.jpmchase.net"

$Port = 1521

#You will get an arithmetic overflow error on integers (numeric) as they are out of bounds for .Net so cast them as Integers

$SQLStatement = 'SELECT

    CAST(Session_Key as int) as Session_Key

    ,CAST(Session_RecID as int) as Session_RecID

    ,CAST(Session_Stamp as int) as Session_Stamp

    ,Command_id

    ,Start_Time

    ,End_time

    ,CAST(Input_Bytes as int) as Input_Bytes

    ,CAST(Output_Bytes as int) as Output_Bytes

    ,CAST(Status_Weight as int) as Status_Weight

    ,CAST(Optimized_Weight as int) as Optimized_Weight

    ,CAST(OBJECT_TYPE_WEIGHT as int) as OBJECT_TYPE_WEIGHT

    ,OUTPUT_DEVICE_TYPE

    ,CAST(AUTOBACKUP_COUNT as int) as AUTOBACKUP_COUNT

    ,AUTOBACKUP_DONE

    ,STATUS

    ,INPUT_TYPE

    ,OPTIMIZED

    ,CAST(ELAPSED_SECONDS as int) as ELAPSED_SECONDS

    ,CAST(COMPRESSION_RATIO as int) as COMPRESSION_RATIO

    ,CAST(INPUT_BYTES_PER_SEC as int) as INPUT_BYTES_PER_SEC

    ,CAST(OUTPUT_BYTES_PER_SEC as int) as OUTPUT_BYTES_PER_SEC

    ,INPUT_BYTES_DISPLAY

    ,OUTPUT_BYTES_DISPLAY

    ,INPUT_BYTES_PER_SEC_DISPLAY

    ,OUTPUT_BYTES_PER_SEC_DISPLAY

    ,TIME_TAKEN_DISPLAY

FROM v$rman_backup_job_details;'

 

$Login = "oracle_user"

$Password = "mypassword"

Write-Host $SQLStatement

$Results = Execute_OracleCommand -HostName $HostName -ServiceName $ServiceName -Port $port -Login $Login -Password $Password -SQLStatement $SQLStatement -OutputType "DataRow"

$Results | Out-GridView