ORACLE: Required permissions for v$mystat

Long time: no post. Busy. Working. Travelling. Excuses. Excuses.

Too many net resources point you to GRANT SELECT ANY DICTIONARY TO {user/role}' but I like a more granulated approach.

If you want to give just enough permissions for a user to v$mystat;

C:\> "sqlplus sys/passwd@tnsname as sysdba"

SQL> GRANT SELECT ON v_$mystat TO {user/role};


[Listening to: Marilyn Manson - Apple of Sodom]

Finding memory leaks without buying a memory profiler

Another winner from Eran Sandler: Finding memory leaks without buying a memory profiler.

[Listening to: Guns N' Roses - Rocket Queen]

Advanced .NET Debugging with PerfMon

Fellow Blogspotter Eran Sandler gives a nice overview on some PerfMon performance counters, what they mean and how to use them to detect some common issues such as leaks, fragmentation and lousy (my word not his) allocation patterns. It's nice having this all in one spot.

[Listening to: Ted Nugent - Free for all]

ORACLE: Enabling function-based indices (indexes?)

I was happily using function based indices to provide case insensitivity like so:

CREATE UNIQUE INDEX MyIndexName ON MyTable (
    LOWER (SomeColumn) ASC
);

Worked like a charm. The uniques was case insensitive and the normal query like:

SELECT blah, foo, bar
FROM   MyTable
WHERE  LOWER(SomeColumn) = LOWER (:bindparam);

was happily using the index.

Until one day at (naturally) a client site, one of my fellow engineers found that the above query was performing a full table scan. And, despite what Tom Kite keeps saying, in this case, a full table scan is bad.

Only after some spelunking through Tom's Book Expert One-on-One Oracle did I find the magic. The database needs the following session/system variables in place to actually use the function-based indices:

QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED

I'll chock these up to the huge pile of stuff that "Oracle needs to have to work the way it should but is next to impossible to find unless you already know it."

Hopefully this saves someone else some time.

[Listening to: Tripping Daisy - Piranha]

Enabling the "Shut Down" option in Windows Server 2003

Once again I spent way too long trying to figure out how to allow my non-administrator user the ability to shutdown windows. Strangely enough Google was of little help. I guess the terms "shutdown", "enable", "user", "dialog" are too vague?

By default a normal user only has log off username in the list of options when you hit shutdown from the start menu. Just so I don't have to search for this again:

Run Administraive Tools/Local Security Settings
Navigate to:
Security Settings/
Local Policies/
User Rights Assignment/

Add the user/group in question to "Shut down the system".

Also, just in case others fall into the same trap, here's some food for the GoogleBots:
"shutdown windows" dialog "log off" enable
"shutdown windows" dialog "log off" allow
"shutdown windows" dialog "only log off"
"shutdown windows" dialog "shut down missing"
"shutdown windows" dialog missing
"shut down windows" dialog missing
"shutdown privilege" "windows 2003"
"shutdown privilege" "windows server 2003"
enable shutdown windows server 2003

Assert.IsEqualGraph -- Comparing object graphs

Keith Brown over at PluralSight has a useful method to Assert the equality of 2 object graphs: Assert.IsEqualGraph.

I have a couple other quasi-useful Assertion type methods that I'll post here (if I ever get around to using the stupid computer again that is.)

[Listening to: Ozzy Osbourne - Over the Mountain]

Dumping XML content while reading it from a stream

Oleg Tkachenko posts a nice nugget on Dumping XML content while reading it from a stream. Since I'm a logging junkie this strikes home.

[Listening to: Marilyn Manson - Apple of Sodom]

ORACLE: displaying initialization parameters

Pulled from here and here.

Thinking I was clever I created a little parms.sql which will pull out all matching initialization parameters from the v$parameter table. Of course I find out that there's a built in way of doing this:

C:\> sqlplus someuser/passwd@tnsname
[snip]
SQL> show parameter spfile

NAME         TYPE        VALUE
------------ ----------- ------------------------------
spfile       string      %ORACLE_HOME%\DATABASE\SPFILE%
                         ORACLE_SID%.ORA

And while we're at it, here's how you get the full set of all parameters in a nice human readable format:

SQL> create pfile='filename' from spfile;

Note₁: this creates the file on the server beside the original spfile (see VALUE above.)
Note₂: you need to run this as sys as sysdba

[Listening to: Barry White - My First, My Last, My Everything]

Of Performance and Peanut Butter Sandwiches

Rico Mariani (certainly in my top 5 blogs lists ever,) gives some tips on
Narrowing Down Performance Problems in Managed Code which border on instantly useful recommendations. Cool.

[Listening to: Fleetwood Mac - Silver Springs]

ORACLE: Permissions for SQL Analyze

To run SQL Analyze, the user requires the SELECT_CATALOG_ROLE:

C:\> sqlplus system/passwd@tnsname
[snip]
SQL> grant SELECT_CATALOG_ROLE to theuser;

Grant succeeded.

If you want to use the index recommendation feature you'll need more privileges. You could grant DBA but I hate doing that, so enable each one independently:

C:\> sqlplus "sys/passwd@tnsname as sysdba"
[snip]
SQL> grant SELECT ON SYS.CDEF$ to theuser;

Grant succeeded.

SQL> grant SELECT ON SYS.CON$ to theuser;

Grant succeeded.

SQL> grant SELECT ON SYS.IND$ to theuser;

Grant succeeded.

SQL> grant SELECT ON SYS.OBJ$ to theuser;

Grant succeeded.

SQL> grant SELECT ON SYS.SEG$ to theuser;

Grant succeeded.

SQL> grant SELECT ON SYS.TAB$ to theuser;

Grant succeeded.

SQL> grant SELECT ON SYS.TS$ to theuser;

Grant succeeded.

SQL> grant SELECT ON SYS.USER$ to theuser;

Grant succeeded.

[Listening to: Stone Temple Pilots - Where the River Goes]