OBIEE: Call SAPurgeAllCache()

Just a note to myself on how to clear the entire BI Server cache.

Mr. Minkjan has a great article here and Gerard Nico has one here.

Caching has been enabled (via <BI_SERVER_HOME>\server\Config\NQSConfig.ini)

For a list of ODBC Function extensions, read here.

First up, I run a simple report and then verify that there is an entry in the cache



I then create a small SQL script and put it in my c:\ folder:



(OK, just ignore what it says in the Title bar, I moved it)

Open up a cmd prompt and issue the following:

nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s purgeallcache.sql

Hit enter and you should see the following:

-------------------------------------------------------------------------------
          Oracle BI Server
          Copyright (c) 1997-2009 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------

CALL SAPurgeAllCache()

CALL SAPurgeAllCache()

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------
RESULT_CODE  RESULT_MESSAGE

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------
1            [59118] Operation SAPurgeAllCache succeeded!

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------
Row count: 1
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------

Processed: 1 queries

Check the Cache Manager and voila!



For future reference, here are the options available for the nqcmd utility:

Command: nqcmd - a command line client which can issue SQL statements
                 against either Oracle BI server or a variety
                 of ODBC compliant backend databases.
SYNOPSIS
         nqcmd [OPTION]...
DESCRIPTION
         -d
         -u
         -p
         -s
         -o

         -D
         -C<# number of fetched rows by column-wise binding>
         -R<# number of fetched rows by row-wise binding>
         -a (a flag to enable async processing)
         -f (a flag to enable to flush output file for each write)
         -H (a flag to enable to open/close a request handle for each query)
         -z (a flag to enable UTF8 instead of ACP)
         -utf16 (a flag to enable UTF16 instead of ACP)
         -q (a flag to turn off row output)
         -NoFetch (a flag to disable data fetch with query execution)
         -NotForwardCursor (a flag to disable forwardonly cursor)
         -SessionVar =

SOUG: SQL Developer Unit Testing

This week the Suncoast Oracle Users Group will be hosting Syme Kutz, Senior Architect for Database Tools at Oracle.

Update
I received Syme's bio (from Syme of course). It's pretty impressive...

I started working at Oracle in October of 1995 in the Systems Performance Group of Consulting under Cary Millsap.

I then spent 8 years tuning the Database and Oracle Applications. My Experience tuning application lead me to work with Max Schierson fixing and Tuning iStore. I left consulting and moved into Applications It working for Max at Headquarters were our focus was building custom applications to better facilitate oracle policies and programs. I built custom applications until a position opened up on APEX the development team. After rebuilding the Database management side of APEX, Kris Rice and I began the Sql Developer project. When the group split and Sql Developer became a product I followed. I have been a developer on Sql Developer since then building various functionality, such as reports and Unti Testing.

We "found" Syme through Kris Rice who unceremoniously offered his services up on Twitter.



After much wrangling and negotiating, we finally managed to talk Syme into coming down from Orlando.

According to Kris, Syme was heavily involved with the new Unit Testing functionality of SQL Developer. We all know how much testing us database folks do, so it makes perfect sense right?

Anyway, if you're in town and Thursday, please come by and check out Syme's presentation, it should be very interesting.

Database Application Security - A Visualization

Security, in regards to the database, is pretty broad. There are a multitude of ways to secure your database. From physical or logical access to SQL Injection.

This is a (not so) pretty picture of how I visualize security from an application point of view.



The base of any application are the tables. Access to those tables should be limited to database views and PL/SQL packages (API) that are owned by the application (schema).

SELECT privileges on those views should be given to only users who need it (users also includes other applications). Views present the data in the format that is required by the application. VPD (column or row) can be used to further restrict access to sensitive data.

EXECUTE privileges on the APIs (PL/SQL) should only be given to those applications that need it. Under no circumstances, should direct INSERT/UPDATE/DELETE privileges be given to another user.

Much of my thoughts on this matter are directly traceable to reading AskTom for so many years. I can't point to any one specific article, I think it's the accumulation/internalization of reading for so long.

It makes sense though.

Who better to decide the functions (INSERT/UPDATE/DELETE) that can be performed against a given set of tables than the person(s) who created them? If said person has left, make someone else responsible for them. I've fought hard over the years to implement this "pattern" and have been fairly unsuccessful. It is difficult to go against years of doing it one way (full access to the underlying tables) to forcing someone or something to use the API.

The idea to visualize it came to me recently and I need to put it down.

What do you think? Am I full of it? Is this reasonable? Anything I left out?

Random Things: Volume #14

Travel
I'm flying now up to Greenville every week. No more driving.

My Sunday flight is at 9 am, which is nice because I'm too tired to think of stuffing myself in a plane. Today was...annoying. Two older gentlemen (word used very looslely) who obviously were out partying last night seemed to believe they were the only 2 people around and talked to each other accordingly.

It started at the ticket counter. They were standing no where near each other but felt the need to discuss last night's escapades. You know, not saying things outright, just hinting at some of the things that gone on. It was amusing for about 2 seconds.

Of course they're little game didn't stop when they got on the plane. Inside voice anyone?

Look, I get obnoxious...I am obnoxious. I try not to be so obnoxious around people I don't know though, just a simple courtesy. They finally shut up once the plane took off to the delight of most of the passengers.

OOW
It's that time of year again. An email came through last week announcing that the call for papers would begin in March. It seems like it just ended doesn't it?

Find more information here. I may submit my How to Use Social Media to Increase Productivity paper abstract. Still debating that. Maybe I can talk Jake into co-presenting with me. I got turned down at ODTUG but OOW but be more amenable to it as the scope of the event is much broader.

I'm also contemplating one on OBIEE maybe targeted to the beginners out there. Who knows?

I had some other stuff to write, but it seems my brain has gone blank.