From time to time techical articles and reviews are submitted to industry publications such as Oracle Scene and IT Bulletin. It is the nature of such publications that the full text of the article cannot be printed due to space and formatting issues. Not everything gets accepted in any case!
An article entitled "Using V$SQL_PLAN to get accurate execution plans" was accepted for the winter 2006 edition of Oracle Scene. Full and extended text of this article can be viewed here as can the scripts the article refers too. Further scripts are available from the Scripts section of the site.
The same issue included a short tip on using the NTILE analytic function in Oracle.
The Spring 2007 edition of Oracle Scene contained a short article of tips for the Unix/Linux environment. This covers the rlwrap and screen utilities, both of which can make working on Unix/Linux very much easier. The former provides full command line recall and editing from within tools such as SQL*Plus, RMAN, LSNRCTL and ASMCMD. The latter allows you to create terminal sessions and then "walk away" from them, leaving them running - you can then re-attach to the detached sessions from any other terminal.
The Summer 2007 edition of Oracle Scene printed an article describing how to use SQL*Net wallets to access remote databases without the need to supply a username and password. This feature, officially called Secure External Password Store, is part of the standard Oracle10g client install and means you can write batch scripts and cron jobs (or MS Scheduled tasks) without having to embed usernames and passwords into your scripts. As such it addresses, and to great extend, resolves the age old security issue of how to connect to a database from within a script without making the user credentials potentially available to the unauthorised eye.
The Autumn 2007 edition of Oracle Scene included an article describing how to use the SQL*Plus COPY command to easily, and quickly copy large amounts of data between databases without the need for flat files, database links or exp/imp va named pipes. The same article also highlights a useful freeware utility that can be used to format SQL statements into a "standard" and/or a easily readable format.
The Winter 2007 edition of Oracle Scene had in it an article on how to save the environment settings within SQL*Plus - and then how to recall them later on to get back to a particular set of environment settings.
An article has been submitted for the Spring 2008 edition of Oracle Scene showing three separate methods of getting the name of the host server machine that the Oracle database is running, via:SYS_CONTEXTS, V$ tables and the little used and obscure UTL_INADDR package. It also includes a short hint on how, in Unix shell scripts, you can turn on spooled output using the exec command.
You must check all hints, tips and scripts before use - please read the disclaimer page.