How to automate Oracle TableSpace report from SQL Server-Part II


By:   |   Updated: 2016-06-30   |   Comments   |   Related: More > Other Database Platforms

Problem

I saw your previous tip (How to Automate Oracle Table Space Report from SQL Server) and want to find out how to create a SQL Server Reporting Services (SSRS) report with a subscription to improve my visibility into Oracle tablespace data. Can you show me how to build this report?

Solution

In Part - I of this tip series, we saw how to create a SQL Server Linked Server to Oracle and pull tablespace information into a SQL Server database table through a SQL Server Agent Job.  In this tip we will create an SQL Server Reporting Services (SSRS) report for Oracle tablespace and schedule the report to run via an SSRS subscription to deliver the report to your inbox every 8 hours.

Create an SSRS report for Oracle Tablespace Information

1. Open BIDS (Business Intelligence Development Studio) and select New SSRS project.

2. Name the project Oracle_Health_check.

3. In Solution Explorer, right click on the Reports folder and select Add New Report. Name the report - TableSpaceReport.

4. In Solution Explorer, right click on the Shared Data Sources folder and select Add New Data Source, as shown below:

Add a new data source in Business Intelligence Development Studio

5. On the Shared Data Source Properties interface for the General tab, enter the name of the data source, the Type and in the Connection String box the Server Name and Database name (i.e. Initial Catalog) where you have tablespace information.

Shared Data Source Properties in SQL Server Reporting Services

6. Verify that the data source is created successfully.

7. Go to View and Select Report Data. Now you will see Report data window at the left side.

8. Click new at the top of the window and select New Data Source.

9: Data Source window: give some logical name like I gave oracle, select Use Shared Data source Reference. Here we are going to use the shared data source we created earlier. Select the shared data source from drop down and click ok.

10: Now you will see your data source in report data window. Right click your data source and select add Dataset then add the appropriate name.  I left it default as dataset. Select the data source from the drop down, select query type as text and in the query window enter the query to pull information for your instance from the Tablespace report as shown below:

Dataset Properties for SQL Server Reporting Services

11: Repeat the same steps for creating another dataset for the oracle_health table.

Query for the Oracle_Health data

12: Now your report data should look like this:

Report Data interface in BIDS

13: Now let's create a report to display the Oracle tablespace details and oracle health status. Go to the toolbox and drag-n-drop the table into the report, it should have 2 rows and 5 columns as shown below:

Blank Table in the SQL Server Reporting Services Report

14: Go to the report data and drag-n-drop fields from the dataset to the table as below:

Drag and drop fields for the Oracle Table Space Report

15: Now, let's do some conditional formatting to the table based on the free space percentage for a better understanding of the report.

16: Click on the PctFree column in the table and open properties, select BackgroundColor and choose Expression as below:

Select the Conditional Option for the PctFree Column

17: In the Expression window enter the code below, to highlight the report row if the free space percentage is below the threshold. I set it to 5% here, you can change this as per your need.

Expression Window with the Conditional Logic for the PctFree field color

18: From the Toolbox select a Text Box, and drag it to the top of table for the Instance Name, Open Mode, Log Mode. Next drag the values from Dataset2 against the respective text box.

19: After, do some cosmetic changes for a good looking report like background color and report header. Now your report should similar to the one below.

Updated Header for the SSRS Report

20: Go to preview and view your final report. It should be look like the sample below.

Oracle Table Space Report denoting rows exceeding the threshold

You can see Master_data_4m has free percentage below 5%, hence it has been highlighted so you can take appropriate action before it creates an issue.

Deploy the Oracle Tablespace SSRS report to the Report Server

Now, we have report ready for the Oracle Tablespace. Let's go ahead and deploy to your SQL Sever Report Server.

1: In Solution Explorer, right click the project and select properties.

BIDS Solution Explorer to Configure Properties

2: In the property window, update the parameters for the TargetReport folder under which you want to deploy report and enter the Target Server URL as shown below:

SSRS TargetReport folder under which you want to deploy report and enter the Target Server URL

3: Click Apply and Ok. Save the project.

4: Right click project in the Solution Explorer and click Deploy.

Deploy the SSRS via BIDS

5: In the output window, you will see the below message after the report is deployed successfully.

Deploy complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Setup SSRS report subscription to deliver report to every day

1: Connect to your report server. i.e. https://<your Report Server>/Reports/Pages/Folder.aspx

2: Click on the Oracle TableSpaceReport (or any report you want to schedule).

3: In reports header, click on Subscription and Create New Subscription.

SQL Server Reporting Services Subscriptions

4: Enter the details as shown below. This you can change as per your needs.

SSRS Parameters for the Oracle Table Space Report

5: Select the schedule as per your needs.

Interval for the SSRS Subscription to the Oracle Table Space Report

Now you have setup a subscription to the TableSpace report and you will start receiving email alerts in the form of Oracle Table space status. Now you can take proactive action to increase table space before free space percentage goes below threshold which should prevent any outages from occurring.

Next Steps


Last Updated: 2016-06-30


get scripts

next tip button



About the author
MSSQLTips author Atul Gaikwad Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

SQL Server and PostgreSQL Linked Server Configuration - Part 2

Creating a SQL Server 2014 Linked Server for an Oracle 11g Database

SQL Server and PostgreSQL Foreign Data Wrapper Configuration - Part 3

Transferring Data Between SQL Server 2014 and Oracle 11g Databases

Export Data from IBM DB2 iSeries to SQL Server





get free sql tips
agree to terms


Learn more about SQL Server tools