Automate Oracle Table Space Report using SQL Server

By: Atul Gaikwad | Comments | Related: > SQL Server and Oracle Comparison

Problem

We work in an environment with both SQL Server and Oracle where we are responsible for monitoring all of the servers. Due to limitations we don't have third party tools to monitor the Oracle environment. In such cases we need to find alternatives to monitor the Oracle environment, such as table space growth details in order to be able to expand the databases accordingly. How can we automate the monitoring of the Oracle environment table space using some of our SQL Server tools?

Solution

As a SQL DBA I like to automate anything and everything that I can to minimize manual monitoring as much as possible. In our environment, we have a couple of Oracle servers where we faced table space growth issues and were notified by the users or when the queries failed due to table space issues. To prevent this issue, this first tip of a multi-part series will demonstrate how to automate a table space report. Please follow the below step by step instructions to setup an automated daily table space report for Oracle using SQL Server tools.

Install Oracle Client

Install the Oracle client on your SQL Server monitoring server instance. You need to install the Oracle client on your monitoring server in order to create an Oracle Linked Server which will be used to collect the data. For instructions on how to install the Oracle client, please refer to this Installing Oracle Database Client.

Create SQL Server Linked Server to Oracle

1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server monitoring instance.

2. Go to Server Object > Linked Server and right click select "New Linked Server...".

3. Enter the name of the server and select server type as "Other data source".

4. In the Provider drop down list select "Oracle Provider for OLEDB" from the drop down list.

5. Enter the Product Name, Data source and Provider string. You can find these details from the tnsname.ora file which is typically on the Oracle server in a directory similar to /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora. The file path may be different on your server. Here is what it looked like on my server.

6. On the Security tab, enter the credentials you use to connect to the Oracle server. I recommend you create a separate account for this report with only the required Oracle permissions.

Setup Security for the New SQL Server Linked Server

7. Once you are done entering the required details, click the OK button. Once the Oracle Linked Server is created, please test the linked server by right clicking on the Linked Server and select the "Test Connection" option to ensure its working and you are able to connect successfully as shown below.

Note: Add other linked servers for other Oracle instances if required.

Scripts for Objects to Store Historical Data

SQL Server Tables

Below are the scripts to create tables on the SQL Server monitoring server to store the Oracle table space information, health details and an archive table to store historical information.

CREATE TABLE [dbo].[TableSpace_Oracle]([INSTANCE] [char](20) NULL,
[TABLESPACE_NAME] [varchar](30) NULL,
[TotalMb] [nvarchar](384) NULL,
[TotalUsed] [nvarchar](384) NULL,
[TotalFree] [nvarchar](384) NULL,
[PctFree] [nvarchar](384) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TableSpace_Oracle_archive](
[INSTANCE] [char](20) NULL,
[TABLESPACE_NAME] [varchar](30) NULL,
[Total Mb] [nvarchar](384) NULL,
[Total Used] [nvarchar](384) NULL,
[Total Free] [nvarchar](384) NULL,
[Pct Free] [nvarchar](384) NULL,
[Logdate] [datetime] NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[oracle_health](
[NAME] [varchar](9) NULL,
[OPEN_MODE] [varchar](10) NULL,
[LOG_MODE] [varchar](12) NULL
) ON [PRIMARY]
GO

SQL Server Stored Procedure

Create a stored procedure on the SQL Server monitoring server to capture the Oracle table space information.

CREATE procedure [dbo].[Populate_Oracle_Tablespace_Data]
as

declare @Startdate varchar(25)
declare @Enddate varchar(25)
declare @date varchar(12)
select @date=convert(varchar(10),getdate()-1,121)
set @startdate = @date + ' 00:00:00'
set @Enddate = @date + ' 23:59:59'

-- Code to backup the old table space data
insert into TableSpace_oracle_archive
select [INSTANCE]
,[TABLESPACE_NAME]
,[TotalMb]
,[TotalUsed]
,[TotalFree]
,[PctFree]
, getdate() 
FROM [DBA].[dbo].[TableSpace_Oracle]

--Code to cleanup space before populating current data
Truncate table TableSpace_Oracle
Truncate table oracle_health

-- Populate health for ORAINSTANCE instance
insert into oracle_health
select * FROM OPENQUERY(ORALinkServer, 'select name, open_mode, log_mode from v$database')

--- Populating Tablespace data for MSS instance
insert into TableSpace_Oracle
select * FROM OPENQUERY(ORALinkServer, 'Select ''ORA_INST1'', B.Tablespace_Name, 
B.Total/1048576 "Total Mb",
round(Nvl((B.Total-A.Total_Free)/1048576, 100),2) "Total Used",
round(Nvl(A.Total_Free/1048576, 0),2) "Total Free",
round(Nvl((A.Total_Free/B.Total) * 100, 0),2) "Pct Free"
From (
Select Tablespace_Name, Sum(Bytes) Total_Free
From Sys.Dba_Free_Space
Group By Tablespace_Name ) A
, ( 
Select Tablespace_Name, Sum(Bytes) Total
From Sys.Dba_Data_Files
Group By Tablespace_Name ) B
Where A.Tablespace_Name(+) = B.Tablespace_Name')

--Duplicate the above code if you have multiple instances

GO

The oracle_health table is just used to check status. This helps with troubleshooting if there is an issue connecting.

I also hardcoded the instance name that I am checking "ORA_INST1". You would need to update this to the name of the Oracle instance you are checking.

If you want to check additional Oracle instances, you would need to duplicate the code above. You could also alter this stored procedure to make it more dynamic to accommodate multiple instances.

SQL Server Agent Job to Pull Table Space Information from Oracle

Use SSMS to Create SQL Server Agent Job

1. In SSMS navigate to SQL Server Agent > Jobs then right click on the Jobs folder and select "New Job...".

2. Name the job "Daily Oracle Table Space Report" as shown below:

New SQL Server Agent Job to Capture the Oracle Table Space

3. Go to the Steps page and click the "New..." button on the bottom left of the screen and name the step "Populate Data".

4. In the command box type Exec Populate_Oracle_TableSpace_Data as shown below and click the OK button to save the configuration.

SQL Server Agent Job Step for Data Collection

5. Go to the Schedules tab and create a new schedule to run daily every 8 hours as shown below:

T-SQL Script to Create SQL Server Agent Job

Alternatively, you can run the following script to build the SQL Server Agent Job.

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' 
AND category_class=1)
BEGIN
   EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', 
   @name=N'[Uncategorized (Local)]'



   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Daily Oracle Table Space Report', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'No description available.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Populate Data', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec Populate_Oracle_Tablespace_Data', 
@database_name=N'Monitoring', --Your Monitoring Database Name where you have created SP.
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=8, 
@freq_subday_interval=8, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20130906, 
@active_end_date=99991231, 
@active_start_time=10000, 
@active_end_time=235959, 
@schedule_uid=N'70e2a605-c71b-4786-9d32-30e2d77893f0'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Oracle Table Space Collection from SQL Server

Now that you have an automated table space collection populated every 8 hours you are able to query the data and see results like the following.

Next Steps

If you have needs to monitor both Oracle and SQL Server, consider some of the ideas from this tip.
In the next tip I will demonstrate how we can create an SSRS table space report that is delivered to your inbox every 8 hours.

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips