Search Results for: "sqlplus is my second home"

Sqlplus is my second home: Part 7 – Downloading files via sqlplus :-)

January 24, 2013 by 5 Comments

Some years ago I wrote about how sqlplus allows you to run sqlplus scripts directly from HTTP and FTP locations instead of the local filesystem. By the way, I didn’t even notice – my blog is over 5 years old already! :)

I a recent email thread Marco Gralike just showed the simplest way I to open a HTTP URL and download + list its contents in a CLOB datatype. It’s the HTTPURITYPE and its getCLOB (and getBLOB) methods.

So, basically if you want to download and save a (text) file like a script without a browser for some reason – and assuming that your database server can make outgoing HTTP connections, you can use this trick:

SQL> SET LINES 1000 TRIMSPOOL ON TRIMOUT ON PAGESIZE 0 LONG 99999999 LONGCHUNKSIZE 99999999 FEEDBACK OFF HEAD OFF
SQL> SPOOL $HOME/snapper_download.sql
SQL> 
SQL> select httpuritype('http://blog.tanelpoder.com/files/scripts/snapper.sql').getCLOB() from dual;
--------------------------------------------------------------------------------
--
-- File name:   snapper.sql
-- Purpose:     An easy to use Oracle session-level performance measurement tool
--              which does NOT require any database changes nor creation of any
--              database objects!
--
--              This is very useful for ad-hoc performance diagnosis in environments
--              with restrictive change management processes, where creating

... lots of output snipped ...

SQL> SPOOL OFF

Now you have to open the spool file (snapper_download.sql) and remove any garbage (sqlplus commands) from the top of the spool file and there you go:

SQL> @snapper_download all 5 1 3
Sampling SID 3 with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v3.62 by Tanel Poder ( http://blog.tanelpoder.com )

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SID, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      3, SYS       , STAT, opened cursors cumulative                                 ,            43,        8.6,         ,             ,          ,           ,
      3, SYS       , STAT, recursive calls                                           ,            44,        8.8,         ,             ,          ,           ,
      3, SYS       , STAT, recursive cpu usage                                       ,             9,        1.8,         ,             ,          ,           ,
      3, SYS       , STAT, CPU used by this session                                  ,             8,        1.6,         ,             ,          ,           ,
      3, SYS       , STAT, in call idle wait time                                    ,           412,       82.4,         ,             ,          ,           ,
      3, SYS       , STAT, session uga memory                                        ,         65512,      13.1k,         ,             ,          ,           ,
      3, SYS       , STAT, session pga memory                                        ,        327680,     65.54k,         ,             ,          ,           ,
      3, SYS       , STAT, calls to get snapshot scn: kcmgss                         ,            43,        8.6,         ,             ,          ,           ,
      3, SYS       , STAT, session cursor cache hits                                 ,            43,        8.6,         ,             ,          ,           ,
      3, SYS       , STAT, session cursor cache count                                ,             1,         .2,         ,             ,          ,           ,
      3, SYS       , STAT, workarea executions - optimal                             ,            48,        9.6,         ,             ,          ,           ,
      3, SYS       , STAT, execute count                                             ,            43,        8.6,         ,             ,          ,           ,
      3, SYS       , STAT, sorts (memory)                                            ,            45,          9,         ,             ,          ,           ,
      3, SYS       , STAT, sorts (rows)                                              ,          1867,      373.4,         ,             ,          ,           ,
      3, SYS       , TIME, PL/SQL execution elapsed time                             ,          5398,     1.08ms,      .1%, [          ],          ,           ,
      3, SYS       , TIME, DB CPU                                                    ,        435933,    87.19ms,     8.7%, [@         ],          ,           ,
      3, SYS       , TIME, sql execute elapsed time                                  ,        129913,    25.98ms,     2.6%, [#         ],          ,           ,
      3, SYS       , TIME, DB time                                                   ,        449166,    89.83ms,     9.0%, [#         ],          ,           ,
      3, SYS       , WAIT, PL/SQL lock timer                                         ,       4127427,   825.49ms,    82.5%, [WWWWWWWWW ],        41,        8.2,   100.67ms

--  End of Stats snap 1, end=2013-01-24 22:45:53, seconds=5

    

--  End of ASH snap 1, end=2013-01-24 22:45:53, seconds=5, samples_taken=41

Of course this technique is more for fun and is not reliable for binary files (unless you use something like UTL_ENCODE.BASE64_ENCODE or UUENCODE first). But still pretty fun :) I was happy to see that accessing a HTTP resource within the database has become so simple that it’s just a one-liner (as opposed to all the UTL_HTTP code lines needed for HTTP access).

Have fun (and thanks Marco for the tip :-)

Filed Under: Cool stuff, Oracle

Sqlplus is my second home: Part 6 – Colorizing sqlplus and terminal output

September 19, 2012 by 5 Comments

Jagjeet Singh mentioned that he has built a V2 of his cool SQL Dashboard sqlplus-based monitoring script, which now also shows a nice colored ASH graph output. Yes, right inside sqlplus!

This reminded me that I demoed color output in sqlplus when I delivered the Hotsos Symposium training day a few years ago, when introducing the MOATS tool the first time. I never got to colorize it as I had hoped to though… as MOATS was more of a fun project I worked on with Adrian Billington just to prove that it is possible to build a monitoring tool purely based on SQL and PL/SQL with a TOP-like self-refreshing screen output.

Anyway, Jagjeet has done this cool work now, but if you want to colorize some of your own scripts, then on XTerm-capable terminal emulators its simply done with just outputting some escape codes before the text you want to color. Most (or even all) widely used XTerm clients support 8 colors for text and 8 for background – but some terminal emulators (like putty, iTerm, Terminator) even support 256 colors!

Here’s a simple example script I demoed at the Hotsos training day too:

Just run it in sqlplus and see what happens! Note that you have to use the sqlplus in an XTerm terminal emulator (maybe ANSI terminals would work too) – this won’t work in Windows Command prompt!

Anyway, have fun and keep those cool & colorful sqlplus scripts coming! :-)

 

Filed Under: Cool stuff, Oracle

Sqlplus is my second home, part 5: Reading the name of currently executing script

December 26, 2007 by 9 Comments

If you need to run and manage loads of sqlplus scripts which call other scripts, which call other scripts etc, then you are probably interested in the sqlplus APPINFO parameter shown below.

When you issue SET APPINFO ON in sqlplus, this makes sqlplus to automatically call DBMS_APPLICATION_INFO.SET_MODULE and set the MODULE value to sql script name which is currently being executed.

This allows you to easily pass the current script name info to Oracle, without the need to have a manual call to SET_MODULE in beginning and end of every script (along with some mechanism for storing the previous module).

A simple example is below. I used two scripts blah.sql and blah2.sql for my test:

C:\tmp>type c:\tmp\blah.sql

select sys_context('USERENV', 'MODULE') from dual;

@@blah2

select sys_context('USERENV', 'MODULE') from dual;

 C:\tmp>type c:\tmp\blah2.sql

select sys_context('USERENV', 'MODULE') from dual;

So, blah.sql reports the current module, then calls blah2.sql which reports current module and then returns back to blah.sql which returns the current module again.

SQL> set appinfo on
SQL>
SQL> @blah

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
01@ blah.sql

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
02@ blah2.sql

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
01@ blah.sql

SQL>

Looks cool!

From output above we can see the following things:

  [Read more...]

Filed Under: Cool stuff, Oracle Tagged With: ,

Sqlplus is my second home, part 4: Getting sqlplus parameter value into a variable

November 7, 2007 by 4 Comments

I’m having some busy times, so can’t blog anything more serious than another sqlplus trick (which likely has value only to some hardcore sqlplus geeks though).

Ever wanted to load a sqlplus parameter (like linesize, pagesize or arraysize) into a sqlplus define variable?

This can sometimes be helpful for customizing your everyday DBA scripts to output (or not output) some columns based on linesize. Or you may want to use the SQL error code somewhere in your script.

We’ll it’s doable with the getplusparm.sql script:

SQL> def myvar
SP2-0135: symbol myvar is UNDEFINED
SQL>
SQL> @getplusparm linesize myvar
SQL>
SQL> def myvar
DEFINE MYVAR           = "80" (CHAR)
SQL>

The above example reads sqlplus parameter “linesize” to a define “myvar”.

The example below uses the sqlcode sqlplus parameter to capture the error code of last command sent to database ( 0 if success ):

SQL> drop table xyz;
drop table xyz
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> @getplusparm sqlcode err
SQL>
SQL> prompt Error code &err
Error code 942
SQL>

Note that the script creates few temporary files into local directory, you may want to modify the file locations from default for your use (and include an instance name or some variable to avoid race conditions when running multiple sqlplus’es concurrently).

And this is the script text… if you find a way for doing it simpler in sqlplus, let me know :)

set termout off

def _tmpfile=getplusparm

spool &_tmpfile..tmp
show &1
spool off

spool &_tmpfile..chg
prompt c/&1/def &2/
prompt c/&2 /&2=/
spool off

get &_tmpfile..tmp nolist
@&_tmpfile..chg
save file &_tmpfile..set replace

@&_tmpfile..set

set termout on

While this is probably my most useless post written during my short blogging career, I was satisfied just to find out that such thing is doable. So, all sqlplus geeks out there, enjoy! ;)

Filed Under: Cool stuff, Oracle Tagged With:

Sqlplus is my second home, part 3: Colored selections in Windows XP command prompt

September 1, 2007 by 5 Comments

Whenever delivering some Oracle training or running a demo at a conference, I’ve always liked to use the Windows command prompt version of sqlplus.

One reason of course is its easy command line history navigation capability ( press F7 in cmd.exe after entering few commands to see why ).

Another reason is that whenever I want to highlight some part of sqlplus output, I can just drag a selection rectangle around that text. In other words I can “mark” the text – drawing the attention there. Of course as the selection rectangle is really meant for copy & paste operations only, it has several limitations. It’s not persistent, whenever I continue typing, the text “marking” will disappear.

Windows XP has introduced a really cool feature to cmd.exe, which anyone doing presentations involving some command line tool output will appreciate!

Basically XP allows you to persistently select and color command prompt output!

An example of what I’m talking about is here:

[Read more...]

Filed Under: Cool stuff Tagged With: ,

Sqlplus is my second home, part 2: Running SQL scripts from remote locations using HTTP

August 31, 2007 by 15 Comments

As you probably already know, the Session Snapper has been designed to be a very easy-to-use performance tool. It is especially useful in database environments where there are no decent performance tools pre-installed and available.

Snapper doesn’t require any setup, all you need is to log on to the database using sqlplus and download snapper.sql script to your computer.

Well, actually the second part is not required, as Oracle sqlplus allows you to run scripts from http and ftp locations!

C:>sqlplus "sys/oracle@ora92 as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Aug 30 23:00:10 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> @http://blog.tanelpoder.com/files/scripts/i.sql

Tanel's sqlplus http test...

http://blog.tanelpoder.com/files/scripts/i.sql

USER                           SYSDATE
------------------------------ ---------
SYS                            30-AUG-07

NAME
---------
ORA92

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
ora92            WINDOWS01

You should be *very* sure that noone can change the scripts on the server without your knowing!!!

SQL>

Cool stuff or what? :)

Let’s see how this relates to everyday DBA life…

[Read more...]

Filed Under: Cool stuff, Oracle Tagged With: ,

Sqlplus is my second home, Part 1: HTMLizing your sqlplus output

August 7, 2007 by 60 Comments

I have not managed to post anything for a while, but I intend to make it up by starting this series of posts made specially for Oracle enthusiasts, geeks and maniacs among us. Here I plan to post the coolest Oracle stuff I’ve just found out and some of it may actually be useful to you!

Lets start. This post is about removing the last major problem with sqlplus in everyday database and application administration work.

I would say the commnd line sqlplus, combined with its script execution and Windows cmd.exe’s command history navigation capabilities, is a very powerful and fast tool for database administration and troubleshooting. This is of course if you use a set of database administration scripts, either downloaded from some reliable source or accumulated over the years of working with Oracle ( you do have such scripts, right? ;-)

Now to the main weakness of sqlplus: I may have scripts carefully formatted for my screen size, however when adding more columns, I run out of screen width. When working on an application data quality troubleshooting task, I need to run some quick ad-hoc queries. Or run a query which just returns lots of data.

What usually happens in such cases is illustrated very well with the output of following query:

[Read more...]

Filed Under: Cool stuff Tagged With: