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

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…

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=http://blog.tanelpoder.com/files/scripts
SQL>
SQL> @&remote/snapper out 1 1 "select sid from v$mystat where rownum = 1"

-- Session Snapper v1.03 by Tanel Poder ( http://www.tanelpoder.com )

--------------------------------------------------------------------------------------------------------------------------------------------
--        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=http://blog.tanelpoder.com/files/scripts
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.

This entry was posted in Cool stuff, Oracle and tagged , . Bookmark the permalink.

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

  1. Mark Bobak says:

    Hi Tanel,

    Cool idea!

    I just tested, and, setting SQLPATH environment variable doesn’t work….bummer….

    -Mark

  2. Mark Bobak says:

    Actually, upon a closer look, here’s the problem:
    access(“whoami.sql”, 0) Err#2 ENOENT
    access(“http/whoami.sql”, 0) Err#2 ENOENT
    access(“//www.tanelpoder.com/files/scripts/whoami.sql”, 0) Err#2 ENOENT

    It sees the ‘:’ in the URL and assumes that it’s a separator between multiple paths. I tried double quote, single quote, and escaping the ‘:’, and nothing worked….

    -Mark

  3. Mark Bobak says:

    Also tried encoding, as such:
    export SQLPATH=http%3A//www.tanelpoder.com/files/scripts/

    But that failed as well…

    -Mark

  4. tanelp says:

    Nice research, Mark… I think one reason for current limitation may be that fetching the script over http or ftp requires opening a TCP socket instead of a regular file.

  5. Jared says:

    Nice trick Tanel.

    I use remote scripts regularly. The r variable is going into glogin.sql today.

  6. malcolm says:

    Cool – I didn’t know that. Thanks!

    This makes a _lot_ of great things possible here, where our Subversion code repository is accessible over HTTP…

  7. tanelp says:

    Hmm.. good idea! I never thought about this feature in database schema release context.

    Thanks!

  8. Leslie says:

    I am teaching myself Oracle and I found your webpage. When I tried to log a script, I am getting error messages:
    SQL> @START http://www.dbdomain.com/sqlscripts/10g/course002.sql;
    SP2-0310: unable to open file “START.sql”
    SQL> @http://www.dbdomain.com/sqlscripts/10g/course002.sql;
    SQL> ————————————————————————
    SQL> — COPYRIGHT
    SQL> — This script is the property of 4SKWare Technologies, Inc. (c) 2005
    SQL> — http://www.animatedlearning.com, http://www.dbdomain.com
    SQL> –
    SQL>
    SQL> SET ECHO OFF;
    SP2-0640: Not connected

    You can ignore errors related to DROP commands when
    this is the first time you are executing this script.

    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0306: Invalid option.
    Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
    where ::= [/][@] | /
    SP2-0640: Not connected

    Could you help me understand what I am doing wrong?

    thanks,
    Les…

  9. tanelp says:

    Hi Leslie,

    You are not connected to the database instance as the error message says.

    Before running the script, you need to say: connect username/password@dbname

    (of course replace the username, password and dbname with your user/database details :)

  10. Mette says:

    Brilliant – do you know how to do it from TOAD og SQL Dev?

  11. Tanel Poder says:

    Hi Mette,

    I don’t know how to do it from Toad. The SQL Developer is fairly compatible with sqlplus commands so *maybe* it allows running scripts from http/ftp locations with @http://file.sql syntax.. I haven’t tested it though

  12. Mette says:

    Hi – I have tried, but it says it is unable to open the file – both in TOAD and SQL Dev. I normally use TOAD, where the @ works fine, running the SQL file as a script.

    But … very nice feature otherwise – thanx for the informative posts :-)

    Mette

  13. Mubarak says:

    tanelp :Hi Leslie,
    You are not connected to the database instance as the error message says.
    Before running the script, you need to say: connect username/password@dbname
    (of course replace the username, password and dbname with your user/database details :)

    @tanelp

    Thanks Tanel, that works, I had this connection problem for long time.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>