Quick Tips for Oracle DBAs

PROBLEM #1 : Need to set the environment each time you login to SQLPLUS?

SOLUTION: Edit login.sql or gloging.sql and enter the setting you need.

This will be used each time you login. 

 vi  $ORACLE_HOME/sqlplus/admin/glogin.sql

SET FEEDBACK OFF

SET TERMOUT OFF

COLUMN X NEW_VALUE Y

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> " or  SELECT LOWER(USER || '@' || SYS_CONTEXT('userenv', 'instance_name')) X FROM dual;

SET SQLPROMPT '&Y> '

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';

SET TERMOUT ON

SET FEEDBACK ON

SET LINESIZE 100

SET TAB OFF

SET TRIM ON

SET TRIMSPOOL ON

PROBLEM #2: Finding the folder sizes in Linux/Solaris

SOLUTION:du -h -s *

2.2G   AUDRPRD

3.6M   AUDRPROD

  11M   Backup

  80G   E1PROD

120K   E1PROD_RESTORE

   0K   lost+found

232M   SOMEPROD

PROBLEM #3: Unix command out is in bytes and it is hard to covert in readable format, try using h as an attribute.

SOLUTION: ls –alt OR ls –alth

ls –alt

drwxr-xr-x   2 oracle   dba        22016 Jul  7 15:27 SOMEPROD

drwxr-xr-x   2 oracle   dba        18944 Jul  7 15:17 AUDRPROD

ls –alth

drwxr-xr-x   2 oracle   dba        22kb Jul  7 15:27 SOMEPROD

Satheesh Shanmugan, Database Administrator
Myriad IT

Filtering on SQL Server Reporting Services datasets

I’ve been building reports in SSRS for about five years and almost always do all filtering in the SQL WHERE clause.  Over the last week I’ve been refining a report and needed an additional filter that would have been challenging within the query and in the process of investigating an alternative approach I came across dataset filters in SSRS.

As mentioned, I normally do all filtering in the SQL WHERE clause.  My rationale is that it reduces the size of the amount of data returned and is therefore more efficient and quicker.  The report I am currently working on returns information about millions of invoice rows from four tables all via LEFT OUTER JOINS (to ensure I capture all invoices).  One of the required filters is on a field not in the left outer most table and needs logic to handle the prospective nulls returned.

Rather than rewriting the query to handle this, and risking the integrity of the dataset, I found Dataset filters in the SSRS report itself.  By right clicking on the dataset and selecting Properties there is a Filter tab:

 

This offers a very flexible way of controlling the returned data much like a SQL where clause.  Some advantages are:

·         It maintains the query’s integrity.  Sometimes I build a query and verify the data with the business.  Then I am then asked to add additional constraints and these additional WHERE clause conditions then break the integrity of the data returned.  Since the filter doesn't alter the underlying query, the integrity should remain in tact

·         The filter is extremely flexible.  Like a WHERE clause it can operate using a report parameter with all the usual SQL operators

·         SSRS expressions can offer additional flexibility similar to a HAVING clause and nested clauses

·         An expression returning a boolean TRUE / FALSE offers similar functionality to an EXISTS (and NOT EXISTS) clause

 

·         It can be implemented very quickly in comparison to rewriting underlying queries and then revalidating data.

I don’t think I’ll stop filtering within the SQL WHERE clause where I can, but it is a great feature and certainly offers flexibility in filtering data in a SSRS report.

 

Struan Hijner – Infrastructure Services Practice Manager
www.myriad-it.com