Organize scripts with remote execution and HELP command SQL*PLUS

A few days ago I realized that I had too many scripts and I was already forgetting some good ones that I didn't use for some time, so I decide that I should document them, but also it should be fast and easy to check this documentation before I run my script, otherwise it would be helpless. Then as a DBA I start thinking in creating a table, putting all the information there, create some nice views, etc..., which would be handy, but not so handy as the HELP command on the SQL*PLUS :) And here comes my idea, instead of creating something new why not use what is already there, and works just fine. So to use the HELP command with your own scripts it is quite simple, there is one table called, (guess what?), HELP, and belongs to SYSTEM, which is created by the script: @$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql;
The HELP table structure:
SQL> desc system.help
Name Null? Type
--------- -------- ---------------------
TOPIC NOT NULL VARCHAR2(50)
SEQ NOT NULL NUMBER
INFO VARCHAR2(80)

TOPIC and SEQ, are the primary key.
Here is an example of my insert on this table:
The first step is just to create a separation between the usual commands already stored on the HELP table.

----------------------------------------------------------------------------------
INSERT INTO SYSTEM.HELP
SELECT 'TOPICS',MAX(SEQ)+1,'----------------------' FROM SYSTEM.HELP WHERE TOPIC='TOPICS';

INSERT INTO SYSTEM.HELP
SELECT 'TOPICS',MAX(SEQ)+1,'ADMINISTRATION SCRIPTS' FROM SYSTEM.HELP WHERE TOPIC='TOPICS';

INSERT INTO SYSTEM.HELP
SELECT 'TOPICS',MAX(SEQ)+1,'----------------------' FROM SYSTEM.HELP WHERE TOPIC='TOPICS';
COMMIT;


Now I create the new script(topic) and the info about it.

Set define off
-- SCRIPT MYSID.SQL
INSERT INTO SYSTEM.HELP
SELECT 'TOPICS',MAX(SEQ)+1,'mysid.sql - Show your SID' FROM SYSTEM.HELP WHERE TOPIC='TOPICS';

INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 2, ' mysid.sql');
INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 3, ' ---------');
INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 4, NULL);
INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 5, ' Show the SID for your session');
INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 6, NULL);
INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 7, ' @&s/mysid.sql, remote execution');
INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 8, ' &s stands for your url, eg.: def s=http://www.scriptsserver.com/scripts');
INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 9, NULL);
INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 10, ' @mysid.sql, local execution');
INSERT INTO SYSTEM.HELP VALUES ('mysid.sql', 11, NULL);
commit;
----------------------------------------------------------------------------------
Set define on

As you can see I’ve first created a new row where the INFO columns is 'mysid.sql - Show your SID' for the topic=’TOPIS’

After that I start inserting the topic itself, where we create a new set of rows, ordering the lines by the SEQ column.

Now when you use the HELP TOPICS or ? TOPICS command the sqlplus is using this query:

Select INFO from system.help where topic ='TOPICS' ORDER BY SEQ;

And you should see this information:

SQL> HELP TOPICS
* - I remove all the entries here
TOPICS
TTITLE
UNDEFINE
VARIABLE
WHENEVER OSERROR
WHENEVER SQLERROR
XQUERY
XTEST
----------------------
ADMINISTRATION SCRIPTS
----------------------
mysid.sql - Show your SID

And when you use HELP MYSID:

SQL> HELP MYSID

mysid.sql
---------

Will show the SID for your session

@&s/mysid.sql, remote execution
&s stands for your url, eg.: def s=http://www.scriptsserver.com/scripts

@mysid.sql, local execution


SQL>

Cool isn’t it? J

Of course there is some labour work to do in order to document the scripts, but I think it is a nice way of doing it.

Another subject that I would like to mention, is that SQL*PLUS is able to run scripts through HTTP or FTP, and that can be very handy as well, try this on your sqlplus:

Def s=http://www.dbabrain.com/scripts

@&s/mypid
Or

@&s/tablespace


If you have access on the internet from your database server you should be able to run my script, of course never use scripts through the internet on your production databases, imagine if someone just changed the script that checks the tablespace in something that drops them L, but the idea is that you should create your own webserver or ftpsever and consolidate your scripts in only one place, and to make things easier you can change the glogin.sql file, located at $ORACLE_HOME/sqlplus/admin, and add the entry:

Def s=http://yourwebserver.com/scripts

That way you can save time typing as demonstrated above.

So using the HELP command plus the remote script execution is a nice way to organize and run your scripts, hope you enjoy!!!




Rodrigo Righetti.

REAL APPLICATION TEST 10G OR 11G

Everybody knows that Oracle has created on 11g the RAT(Real Application Test), and it is very useful, but the only thing they've done for 10g is allow you to capture the workload and do the replay on a 11g instance, that is good but not so useful at this point, most of our databases are on 10g yet, if not 9i or 8i, but the point is it will take some time till we upgrade to 11g.

But I realize that was not so difficult to create some sort of RAT that would capture and replay on 10g databases, and that is what I did, I've create a package that capture the workload and replay on 10g databases, that way we can compare execution plans, cpu time, elapsed time, disk reads and buffer gets.

That can save a lot of time when we are dealing with server migrations, new releases, new statistics, etc …

Attached is the documentation and scripts, if you think this is a good idea, take a look, and see if it can help you on your dba activities.

Any errors, or any suggestions of improvement, please let me know.


http://docs.google.com/Doc?id=df2mxgvb_33f8xnjgcj
http://docs.google.com/Doc?id=df2mxgvb_34czhqcqhq
http://docs.google.com/Doc?id=df2mxgvb_35gwn6jwhf
http://docs.google.com/Doc?id=df2mxgvb_36hhcr4rhs

HOW TO RENAME A TABLESPACE WITH ORACLE 9 USING BBED

http://docs.google.com/Doc?id=df2mxgvb_32hjz79hqk

ORACLE 11g New Features Part3

http://docs.google.com/Doc?id=df2mxgvb_20cd5kg2

Standby and Backup
Not completed yet ....

ORACLE 11g New Features Part2

http://docs.google.com/Doc?id=df2mxgvb_16cvtxtd

How to install Oracle BBED tool on any release

http://docs.google.com/Doc?id=df2mxgvb_15ftv586

ORACLE 11g New Features Part1

http://docs.google.com/Doc?id=df2mxgvb_14dzkfcr