Excel Utility for DBAs
This Excel utility has been very useful to all my colleagues at different places as well to my other DBA friends. Based on popular demand I am pleased to share this utility with a broader community of DBA team by sharing this to everyone.
This utility is pretty self explanatory. Due to privacy and security reason I have included sample database names. To begin validating this utility, you need to replace sample database names with your database name(s), which you can do by running a macro(ctrl+shift+T) which will read a TNSNames.ora file you provide as input.
Here is the usage included in the Excel file in the "Version" tab.
Usage: This WorkBook is useful for collecting information
from multiple Oracle or SQL
Server
databases in
Excel WorkSheet. Initialization of macros
is very dependent on your current cursor (selected cell) position. Query you enter in C2 and other cell should NOT contain ";" at the end of the
query string. Enhancement
has been done to trim the trailing ";",
if present. You should always validate that query you are going to execute
against multiple database is syntactically correct.
There are couple macros in this WorkBook
, most of them have shortcuts too. (Ref: List of Macros)
For collectData Macro:
You can enter multiple queries , one
query in its own cell. Starting from C2, C3, C4, etc.
You can specify work sheet name for
the query results, if you omit it will
autogenerate one for you. WorkSheetName must be specified in B2, B3, B4, etc.
corresponding cells for the queries.
Queries must be followed by header line
and then followed by Sr.No and Database Aliases (from your local TNSNAMES.ora
file). Please refer to the "Sample" WorkSheet in this WorkBook.
Once ready with this setup press
"Ctl+Shift+C" and will display Login Window for the common user name
and password across all the databases. If you have separate username and/or
password, use column E and F to
specify username and password
respectively. Select environment type; N => for Non Production, P => For
production, A => For all environment. Once entered all this information this
macro executes all the listed queries in the current WorkSheet for all the
databases listed in the current WorkSheet only. All database must be
consecutoive without any Blank lies and/or Sr.No.
WorkSheet containing result contains
following additional information:
Date and Time in Cell A1. Row 2 contains header name and Column A
contains database name. Query executed is being stored as Comments for Cell A2.
Also, there will be text file generated
in the directory where this Excel file with Macro is being stored, (e.g.
DB2Excel4DP.xlsm.txt) which can be used
to watch the progress of the execution. It updates this file once it completes execution for the databse
(success/failure).
| List of Macros: | |||||||
| MacroName | ShortCut | Description | |||||
| collectData | Ctl+Shift+C | Execute queries and stores result in WorkSheets | |||||
| compareResults | Ctl+Shift+R | Execute queries and stores result SideBySide | |||||
| DeleteCNValidWrkSheet | Ctl+Shift+D | Delete CN Valid Worksheets (Autogenerated after each execution) | |||||
| readTNSNamesAndList | Ctl+Shift+T | Reads TNSNames.ora file from the specified location and list all aliases in a new WorkSheet. | |||||
In order to get this file, as of now please feel free to send me email (patel.RiDham at GMail.com) I will share this file with you at the earliest feasible time.
Here is the permanent link to this Excel utility.
If you like this utility please feel free to leave your comments here as well as feel free to leave any suggestions/enhancements.
