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

Tanel Poder

2007/08/30

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> @https://github.com/tanelpoder/tpt-oracle/blob/master/i.sql

Tanel's sqlplus http test...
https://github.com/tanelpoder/tpt-oracle/blob/master/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…

Probably everyone who has managed tens or hundreds of Oracle databases, knows this situation: You need to get something done fast, but for whatever environmental reasons you don’t have access to your normal work environment, PC, GUI tools and scripts on your hard-disk. And you just don’t have your scripts available on every one of those servers. Or you do some the scripts there, but they were copied there 4 years ago and are outdated.

This normally means that you will log on to the server, fire up sqlplus and spend most of your time on repeatedly typing in long commands for doing very simple operations. Finding list of active users or tablespace level free space breakdown can be done in a second using a script or mouseclick, however it may take long time if you need to manually type in a SQL statement for that (on a crappy terminal!)

So, one solution would be copying your scripts to the server you’re working on – if you have the time and ability to do that.

Another option would be keeping a corporate script repository on a webserver and access the required scripts using @http callouts from sqlplus.

There’s one technical issue though. Unlike regular directories, the http and ftp locations will not work in SQLPATH variable (which is the automatic sql search path variable). So you can’t just type @df and have sqlplus automatically look it up from your webpage. So you would manually need to type the full HTTP URL of your script every time you want to execute it.

Four years ago I proposed a solution for running the remote scripts through a wrapper @r script, in an Oracle-L post (time flies fast, huh?)

That solution was somewhat clumsy though as it required the wrapper script still to be downloaded and stored on local disk and the parameters to script executed needed to be embedded in double quotes etc..

There’s another much simpler solution though – just using a sqlplus variable for holding the remote path:

SQL> define remote=https://github.com/tanelpoder/tpt-oracle/blob/master
SQL>
SQL> @&remote/snapper out 1 1 "select sid from v$mystat where rownum = 1"

-- Session Snapper v1.03 by Tanel Poder (  )

--------------------------------------------------------------------------------------------------------------------------------------------
--        SID, SNAPSHOT START   , SECONDS  , TYPE, STATISTIC                               ,         DELTA,      D/SEC,     HDELTA,   HD/SEC
--------------------------------------------------------------------------------------------------------------------------------------------
DATA,      10, 20070830 23:02:36,         1, STAT, opened cursors cumulative               ,             5,          5,          5         5
DATA,      10, 20070830 23:02:36,         1, STAT, opened cursors current                  ,             1,          1,          1         1
DATA,      10, 20070830 23:02:36,         1, STAT, recursive calls                         ,           136,        136,        136       136
DATA,      10, 20070830 23:02:36,         1, STAT, recursive cpu usage                     ,             8,          8,          8         8
DATA,      10, 20070830 23:02:36,         1, STAT, session logical reads                   ,            71,         71,         71        71
DATA,      10, 20070830 23:02:36,         1, STAT, session uga memory                      ,         65464,      65464,     65.46k    65.46k
DATA,      10, 20070830 23:02:36,         1, STAT, session uga memory max                  ,        130928,     130928,    130.93k   130.93k
DATA,      10, 20070830 23:02:36,         1, STAT, session pga memory                      ,         16604,      16604,      16.6k     16.6k
DATA,      10, 20070830 23:02:36,         1, STAT, session pga memory max                  ,        278748,     278748,    278.75k   278.75k
DATA,      10, 20070830 23:02:36,         1, STAT, consistent gets                         ,            71,         71,         71        71
DATA,      10, 20070830 23:02:36,         1, STAT, physical reads                          ,             1,          1,          1         1
DATA,      10, 20070830 23:02:36,         1, STAT, free buffer requested                   ,             1,          1,          1         1
DATA,      10, 20070830 23:02:36,         1, STAT, consistent gets - examination           ,            27,         27,         27        27
DATA,      10, 20070830 23:02:36,         1, STAT, calls to get snapshot scn: kcmgss       ,            13,         13,         13        13
DATA,      10, 20070830 23:02:36,         1, STAT, no work - consistent read gets          ,            44,         44,         44        44
DATA,      10, 20070830 23:02:36,         1, STAT, table fetch by rowid                    ,            33,         33,         33        33
DATA,      10, 20070830 23:02:36,         1, STAT, rows fetched via callback               ,             7,          7,          7         7
DATA,      10, 20070830 23:02:36,         1, STAT, index fetch by key                      ,             7,          7,          7         7
DATA,      10, 20070830 23:02:36,         1, STAT, index scans kdiixs1                     ,            11,         11,         11        11
DATA,      10, 20070830 23:02:36,         1, STAT, cursor authentications                  ,             1,          1,          1         1
DATA,      10, 20070830 23:02:36,         1, STAT, buffer is pinned count                  ,            14,         14,         14        14
DATA,      10, 20070830 23:02:36,         1, STAT, buffer is not pinned count              ,            68,         68,         68        68
DATA,      10, 20070830 23:02:36,         1, STAT, workarea executions - optimal           ,            30,         30,         30        30
DATA,      10, 20070830 23:02:36,         1, STAT, parse count (total)                     ,            12,         12,         12        12
DATA,      10, 20070830 23:02:36,         1, STAT, execute count                           ,            13,         13,         13        13
DATA,      10, 20070830 23:02:36,         1, STAT, sorts (memory)                          ,            15,         15,         15        15
DATA,      10, 20070830 23:02:36,         1, STAT, sorts (rows)                            ,          2056,       2056,      2.06k     2.06k
DATA,      10, 20070830 23:02:36,         1, WAIT, db file sequential read                 ,         29628,      29628,    29.63ms   29.63ms
--  End of snap 1

PL/SQL procedure successfully completed.

SQL>

So, you define the remote location into a variable once in sqlplus session and can run your scripts with much less typing effort. Obviously you can make the remote variable name shorter to type even less:

SQL> def r=https://github.com/tanelpoder/tpt-oracle/blob/master
SQL> @&r/df

TABLESPACE_NAME                   TotalMB     UsedMB     FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
EXAMPLE                               121        121          0   100% YES |####################|
INDX                                   25          1         24     4% YES |#                   |
SYSTEM                                390        286        104    74% YES |###############     |
TEMP                                   41         41          0   100% YES |####################|
TOOLS                                  92         92          0   100% YES |####################|
UNDOTBS1                              200          5        195     3% YES |#                   |
USERS                                  25          1         24     4% YES |#                   |
X                                      30          1         29     4% YES |#                   |

8 rows selected.

NB! The other BIG concern with this approach is that you need to be VERY sure that noone can change those remote scripts without your knowing!

This essentially means that do not try to run any scripts directly from internet, including my site.

Comments powered by Talkyard.