annotate docs/source/comparison.rst @ 445:b34ea206c7d1

debug bind var dependence on arg.parsed.expanded
author catherine@Drou
date Tue, 09 Feb 2010 13:48:24 -0500
parents ed02c8b1a9b6
children 80a1976decf2
rev   line source
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
1 This paper is a comparative guide to sqlpython and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
2 three other open-source SQL\*Plus alternatives (gqlplus, Senora, YASQL).
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
3
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
4 ******************************************************
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
5 Long Live the Command Line: SQL\*Plus and Alternatives
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
6 ******************************************************
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
7
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
8 `COLLABORATE 09 <http://www.ioug.org/collaborate09/>`_
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
9
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
10 Talk #405 - Database track
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
11
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
12 Thursday, May 7, 2009, 11:00 AM - 12:00 PM
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
13
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
14 Orange County Convention Center West
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
15
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
16 Orlando, FL
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
17
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
18 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
19 Introduction
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
20 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
21
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
22 Common desktop productivity applications, like spreadsheets, word processors, and the Microsoft Access database, make no clear separation between the program's handling of its underlying data and the interface it provides for user access. Coming from this world, newcomers to relational databases like Oracle are often confused by the distinction between the database itself and the variety of programs that may be used to access it. For my first several months of working with Oracle, "Oracle" and "SQL\*Plus" were basically synonyms in my mind.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
23
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
24 In reality, there is a great variety of options for accessing data. End users generally use tools that channel their interaction and insulate them from SQL - tools like Oracle Forms, Reports, and Discoverer; homegrown GUI applications; and web-based interfaces, sometimes built on platforms like Oracle Application Express.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
25
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
26 Database administrators and developers need more general-purpose tools that let them see and manipulate data and metadata freely and issue ad-hoc SQL statements, exercising any DML and DDL privileges their accounts have been granted. GUI tools have gained much attention since the introduction of Oracle SQL Developer, though Quest Software's TOAD remains extremely popular due to its rich feature set and vigorous development. Other GUI tools include PL/SQL Developer, TORA, and open-source tools like Squirrel. Oracle Enterprise Manager and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
27 JDeveloper can also be used for ad-hoc access to Oracle tables and data.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
28
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
29 Nonetheless, command-line interaction through SQL\*Plus remains a cornerstone of most Oracle professionals' work. SQL\*Plus is ubiquitous and familiar, running on virtually any system.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
30 Typing a familiar SQL or SQL\*Plus command is often faster than navigating a GUI's menus.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
31 SQL\*Plus gives experienced users a sense of full control of their system. Finally, professionals trying to avoid repetitive motion injuries may find frequent use of a mouse uncomfortable, preferring the ergonomics of a good keyboard.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
32
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
33 SQL\*Plus still appears much the same as it did years ago, and it's easy to assume that command-line clients are inherently limited, bare-bones approaches. There have been improvements, however, which many users are unaware of; by educating yourself about SQL\*Plus' newer features, you may find that SQL\*Plus is a more powerful tool than you'd realized.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
34
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
35 Furthermore, SQL\*Plus is not the only command-line client for interacting with Oracle. Over the years, Oracle professionals craving greater capabilities have produced several alternate command-line clients that introduce extra features they found useful, and you may as well. In fact, since all these tools are open-source, you are welcome to add new features to meet your own needs, and to contribute your improvements for incorporation into the tools. All the SQL\*Plus alternative tools are free of charge. (SQL\*Plus is also available free of charge, since it is packaged with free Oracle products like the Oracle XE client.)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
36
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
37 This paper will begin with a short introduction to each of the tools reviewed. It will then present enhanced capabilities, most of which appear in more than one of the tools. Next, it will list the most SQL\*Plus capabilities that some or all of the alternative products lack. Next, it will cover installing and extending each tool, and finally, briefly discuss cross-RDBMS SQL client tools.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
38
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
39 For purposes of this paper, "UNIX" implies UNIX, Linux, and similar POSIX-based operating
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
40 systems - essentially anything but Windows. Curly braces (`{}`) denote non-literal values,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
41 as in `{username}/{password}`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
42
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
43 =====
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
44 Tools
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
45 =====
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
46
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
47 SQL\*Plus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
48 ---------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
49
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
50 Oracle's command-line SQL interface, and possibly the most familiar face of Oracle to the world. We will take the well-known features of SQL\*Plus as our baseline, assuming that readers are familiar with them. Some of its lesser-known features, however, will be included in our review of enhanced features.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
51
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
52 gqlplus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
53 -------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
54
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
55 This tool is essentially a thin wrapper around SQL\*Plus, passing all input
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
56 directly to SQL\*Plus. It preserves virtually all SQL\*Plus' capabilities
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
57 and feel. Gqlplus was written in C by Ljubomir J. Buturovic and available under the GNU General
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
58 Public License. Version 1.12 was released in December 2006. It
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
59 is available from its SourceForge page at http://gqlplus.sourceforge.net/.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
60
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
61 senora
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
62 ------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
63
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
64 Senora - an acronym for Shell ENvironment for ORAcle - is a Perl-based tool was created by
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
65 Martin Drautzburg in 2003. As its name suggests, its focus is on adding features of the UNIX shell to a SQL\*Plus-like environment. Version 0.5.1 was released under the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
66 Artistic License in April 2003. A few of the capabilities
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
67 described here apply only to the 0.6 release, planned for early 2009; they are labelled as such in the text.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
68 Senora is available from its SourceForge page at http://senora.sourceforge.net/.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
69
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
70 Senora uses a plugin architecture; some of its commands are not available
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
71 in any given senora session until the plugin is activated with the `register` command. For example, senora's
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
72 tuning-related commands will not be available until `register Tuning` is executed in the session.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
73 A startup file, `login.sra`, can be used to automatically register all desired plugins on startup.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
74
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
75 YASQL
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
76 -----
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
77
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
78 Another Perl program, this one by Nathan Shafer and Balint Kozman. Many of its advanced features center around producing query output in more versatile and convenient forms. Version 1.83 was released under the GNU General Public License in May 2005. It is available from its SourceForge page at http://sourceforge.net/projects/yasql/.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
79
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
80 sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
81 ---------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
82
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
83 A Python program by Luca Canali and Catherine Devlin (this author). It imitates the features of senora and YASQL as well as introducing many unique features. Active development is ongoing, and version 1.6.1
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
84 was released in March 2009. Some defects as of this paper's writing (March 2009) are likely to be fixed by the time you read it. It is available from the Python Package Index at http://pypi.python.org/pypi/sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
85
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
86 ====================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
87 Windows Availability
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
88 ====================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
89
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
90 ========= ===
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
91 gqlplus no
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
92 senora yes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
93 YASQL no
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
94 sqlpython yes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
95 ========= ===
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
96
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
97 Since gqlplus and YASQL were written in C and Perl, respectively, it should be possible,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
98 in principle, to compile and run them on Windows. No precompiled versions of them are
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
99 available, however, and no Windows installation procedures have been documented.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
100
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
101 All the tools are available for all UNIX environments.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
102
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
103 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
104 Enhancements
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
105 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
106
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
107 Smart prompt
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
108 ------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
109
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
110 *SQL\*Plus (10g and higer), senora, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
111
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
112 `SQL>`, the standard SQL\*Plus prompt, provides no useful information. The prompt can be changed with SQL\*Plus' `SET SQLPROMPT` command.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
113
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
114 As any DBA who has mistakenly issued a command in what they *believed* was the development instance can tell you, the most useful prompt is one that identifies what username and database is
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
115 being used, like `me@production>`. To get this prompt in SQL\*Plus, issue `SET SQLPROMPT "_user'@'_connect_identifier> "`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
116
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
117 It's important that the prompt be kept up-to-date; if a `CONNECT` statement is used to connect to a different instance, the prompt must be updated or it will become deceptive. In SQL\*Plus, `SET SQLPROMPT` should be included in `$ORACLE_HOME/sqlplus/admin/glogin.sql`. SQL\*Plus 10g and higher execute the commands in `glogin.sql` each time a CONNECT statement changes this information.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
118
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
119 SQL\*Plus 9i and earlier run `glogin.sql` only when a new session is started, as does gqlpython. In these environments, the "smart prompt" cannot be trusted, since a `CONNECT` statement may have changed the reality.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
120
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
121 Senora, YASQL, and sqlpython provide this type of smart prompt out of the box.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
122
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
123 Tab completion
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
124 --------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
125
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
126 *gqlplus, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
127
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
128 Tab completion saves typing and avoids spelling errors by filling in the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
129 remainder of a command or object name when
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
130 the first few letters are entered and the TAB key is hit. If there are multiple valid
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
131 endings to an incomplete word, each potential ending.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
132
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
133 Editor choice
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
134 -------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
135
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
136 *all*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
137
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
138 It's important to remember to set the `$EDITOR` environment variable before starting any
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
139 command-line tool under UNIX. All the tools allow textfile editing, but if $EDITOR is
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
140 not set, they will use the system default editor - generally UNIX `ed`, which can be traumatizing.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
141
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
142 sqlpython does not give up so easily if `$EDITOR` is not set, searching for more advanced
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
143 text editors and starting them preferentially.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
144
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
145 Scripting
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
146 ---------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
147
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
148 *all*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
149
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
150 All the tools support running scripts with `@path/to/script.sql`. Establishing a personal
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
151 library of script files is an important part of building your productivity. SQL\*Plus,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
152 gqlplus, and sqlpython also support running scripts from urls, like
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
153 `@http://host.com/scriptlibrary/myscript.sql`, allowing you to keep your script library
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
154 on the web.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
155
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
156 The convenience of the script library is crucial. If your scripts are hidden away in
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
157 a directory that is difficult to remember and type, you will be tempted to rewrite
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
158 queries instead of using your established scripts. It's good practice to always start
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
159 your SQL tool in the directory that contains your script library; that way, you can
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
160 access your scripts without having to type path names. An alias or shortcut at the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
161 operating-system level can be useful; for instance, put `alias sql='cd ~/myscriptlibrary; sqlplus'` in your `.bashrc` file.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
162
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
163 Choose your script names carefully, so that you will find them easily in the future.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
164 To view your script library from within your SQL tool, type `host dir` or `host ls`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
165
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
166 Command history
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
167 ---------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
168
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
169 *SQL\*Plus (Windows, or with rlwrap), gqlplus, senora, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
170
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
171 On Windows, the up- and down-arrow keys can be used to scroll through
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
172 the history of SQL\*Plus commands issued during the session. SQL\*Plus on UNIX does not provide this feature. You can restore the feature, however, by installing a free GNU tool called `rlwrap`, then invoking SQL*\Plus under it: `rlwrap sqlplus me@instance`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
173 `rlwrap` can also provide cursor-key history to senora, and in fact to any command-line program.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
174
304
98975ce340ac more docs changes
catherine@dellzilla
parents: 288
diff changeset
175 Gqlplus and sqlpytyhon provide cursor-key command history out of the box; Senora and YASQL do also, provided
98975ce340ac more docs changes
catherine@dellzilla
parents: 288
diff changeset
176 that the Term::ReadLine::Perl module has been installed (see Installation).
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
177
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
178 In addition, senora and sqlpython have a `history` or `hi` command that gives a numbered list of all commands issued in the session.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
179
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
180 Senora history commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
181 ~~~~~~~~~~~~~~~~~~~~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
182
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
183 ================== ===============================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
184 hi List all commands issued in this session
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
185 hi {search string} List all commands containing {search string}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
186 ! rerun the last command
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
187 !!{N} rerun command number N
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
188 !{search string} rerun last command containing {search string}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
189 ================== ===============================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
190
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
191 sqlpython history commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
192 ~~~~~~~~~~~~~~~~~~~~~~~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
193
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
194 ========================= ==========================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
195 hi List all commands issued in this session
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
196 hi {N} List command number {N}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
197 hi -{N} List all commands up to command number {N}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
198 hi {N}- List all commands from command number {N} onward
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
199 hi {search string} List all commands containing {search string}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
200 hi /{regex}/ List all commands matching regular expression /{regex}/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
201 r or \\g rerun the last command
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
202 r {N} rerun command number {N}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
203 r {search string} rerun last command containing {search string}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
204 r /{regex}/ rerun last command matching {regex}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
205 ========================= ==========================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
206
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
207 Neatened output
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
208 ---------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
209
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
210 *senora, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
211
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
212 Senora, YASL, and sqlpython economize on column space when returning query results. This
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
213 can make output much neater, more compact, and easier to read.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
214
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
215 SQL\*Plus::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
216
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
217 SQL> select * from party where name = 'Gimli';
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
218
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
219 NAME STR INT WIS DEX CON CHA
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
220 ---------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
221 Gimli 17 12 10 11 17 11
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
222
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
223 YASQL::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
224
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
225 jrrt@orcl> select * from party where name = 'Gimli';
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
226
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
227
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
228 NAME STR INT WIS DEX CON CHA
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
229 ----- ---- ---- ---- ---- ---- ----
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
230 Gimli 17 12 10 11 17 11
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
231
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
232 1 row selected (0.03 seconds)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
233
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
234 UNIX-inspired commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
235 ----------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
236
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
237 *senora, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
238
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
239 "Senora" is an acronym for Shell ENvironment for ORAcle, and many of its special features
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
240 are inspired directly by UNIX shell commands. `sqlpython` duplicates most of these. Also, as
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
241 in Unix, these commands can be altered with flags.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
242
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
243 ls
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
244 `ls` is used to list Oracle objects from the data dictionary. These are reported as though
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
245 they existed in an {object type}/{object name} directory structure::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
246
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
247 0:jrrt@orcl> ls
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
248 Table/Party Index/Xpk_Party
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
249
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
250 0:jrrt@orcl> ls -h
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
251 NAME
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
252 ls - list all objects matching pattern
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
253
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
254 SYNOPSIS
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
255 ls <type/name>
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
256 -a List all objects, even with a dollar
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
257 -l List validity of objects too
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
258 -C List constraints etc too
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
259 -I List indexes etc too
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
260 -i List invalid objects only
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
261
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
262 FILES
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
263 DataDictionary.pm
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
264 0:jrrt@orcl> ls -l Table/*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
265 VALID 27-FEB-09 Table/Party
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
266
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
267
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
268 cat
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
269 A shortcut for `SELECT * FROM`. In fact, you can
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
270 attach `WHERE` clauses, `ORDER BY`, or other SQL just as if you had typed `SELECT * FROM`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
271
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
272 head [-n N]
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
273 Displays the first N rows (default 10) of a table or view.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
274
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
275 grep *target_text* *table to search* [*table 2 to search*, ...]
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
276 Searches entire tables (all columns) for the desired text::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
277
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
278 0:jrrt@orcl> grep 17 party
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
279
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
280 Name |str|int|wis|dex|con|cha|
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
281 --------------------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
282 Gimli | 17| 12| 10| 11| 17| 11|
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
283 Legolas| 13| 15| 14| 18| 15| 17|
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
284
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
285 2 rows selected.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
287 Also like UNIX commands, the operation of many senora and sqlpython commands can be altered by
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
288 using flags, as in `grep -i {target text} {table name}` , where `-i` makes the search case-insensitive.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
289 See `{command} -h` for help on each command, including a list of flags.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
290
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
291 Data dictionary access
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
292 ----------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
293
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
294 *senora, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
295
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
296 Inspecting Oracle objects using the data dictionary requires plenty of typing and an
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
297 excellent memory. The alternative tools provide convenient shortcuts.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
298
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
299 This table shows some approximately equivalent ways to extract object
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
300 information from the data dictionary; actually, the SQL queries
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
301 given require considerable interpretation and usually additional joins to get truly
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
302 useful information, whereas the YASQL/senora/sqlpython commands provide information
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
303 ready-to-use. There are too many possibilities to list, but a sample will give the idea.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
304
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
305 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
306 |SQL\*Plus |YASQL |senora |sqlpython |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
307 +========================+=================+=================+======================+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
308 |SELECT table_name |show tables |ls Table/\* |ls table |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
309 |FROM tabs; | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
310 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
311 |SELECT object_name, | | |ls -l \*a\* |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
312 |status, last_ddl_time | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
313 |FROM all_objects | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
314 |WHERE object_name | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
315 |LIKE 'A%'; | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
316 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
317 |SELECT * |show constraints |refs {table name}|refs {table name} |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
318 |FROM all_constraints |on {table name} | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
319 |WHERE table_name = | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
320 |'{table name}' | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
321 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
322 |SELECT * |show indexes | |\\di {table name} |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
323 |FROM user_indexes |on {table name} | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
324 |WHERE table_name = | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
325 |'{table name}' | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
326 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
327 |SELECT * | show triggers | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
328 |FROM all_triggers | on {table name} | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
329 |WHERE table_name = | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
330 |'{table name}' | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
331 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
332 |DBMS_METADATA.GET_DDL( | |pull |pull |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
333 |'{object_type}', | |{object_name} |{object_name} |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
334 |'{object_name}') | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
335 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
336 |SELECT * FROM | |desc -l | comments {table name}|
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
337 |all_tab_comments | |{table_name} | or \\dd {table name} |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
338 |WHERE table_name = | |(available in | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
339 |'{table name}'; | |v0.6) | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
340 |SELECT * FROM | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
341 |all_col_comments | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
342 |WHERE table_name = | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
343 |'{table name}'; | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
344 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
345 |SELECT * | |find {target} | find -a {target} |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
346 |FROM all_source | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
347 |WHERE text LIKE | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
348 |'%{target}%'; | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
349 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
350 |SELECT * | | |find -ac {column name}|
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
351 |FROM all_tab_columns | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
352 |WHERE column_name LIKE | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
353 |'%{column name}%' | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
354 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
355
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
356 In sqlpython, the command `ls -tl;10` will list the most recent ten objects by their
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
357 last DDL time - which can be a handy way to answer the question, "What was I working
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
358 on here?" (`ls -tl` is similarly useful in the Unix shell.)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
359
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
360 convenient viewing of explain plans
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
361 -----------------------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
362
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
363 *senora, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
364
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
365 In senora, `xplain {text of query to explain}` is the equivalent of issuing
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
366 `EXPLAIN PLAN FOR {query}` in SQL\*Plus, then querying the PLAN table with
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
367 spacing inserted for neat formatting.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
368
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
369 In YASQL, `show plan` displays the last PLAN table entry, neatly formatted.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
370 (Issuing `EXPLAIN PLAN` first is up to you.)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
371
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
372 In sqlpython, `explain {SQL ID}` shows the execution plan for the SQL statement with the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
373 given ID. If SQL ID is omitted, it defaults to the most recent SQL executed.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
374 (This is not necessarily the last statement `EXPLAIN PLAN` was issued against.)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
375
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
376 Special I/O destinations
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
377 ------------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
378
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
379 *YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
380
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
381 You can use the UNIX pipe (`|`) to send query output to a UNIX shell command; this will work much like the pipe in the UNIX shell would. An example in sqlpython::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
382
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
383 jrrt@orcl> select name from party; | sed 's/Legolas/Elfie-poo/'
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
384
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
385 4 rows selected.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
386
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
387 NAME
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
388 -------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
389 Frodo
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
390 Gimli
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
391 Elfie-poo
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
392 Sam
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
393
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
394 You can use `> {filename}` to route output into a file.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
395
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
396 In sqlpython, if the filename is omitted after a `>`, the output is simply redirected into the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
397 paste buffer, and can then be pasted into a document, email, etc. An external program called
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
398 `xclip` is necessary to make this work under Unix, and can be installed from your package
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
399 repository.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
400
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
401 In YASQL, `<` can be used to import data directly from a CSV file into a table::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
402
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
403 jrrt@orcl> insert into party values (:1, :2, :3, :4, :5, :6, :7); < newmembers.csv
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
404
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
405 In sqlpython, `< {filename}` simply replaces `< {filename}` with the content of {filename},
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
406 then executes the resulting command.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
407
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
408 Special output formats
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
409 ----------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
410
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
411 *SQL\*Plus, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
412
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
413 SQL\*Plus can produce HTML tables instead of plain text using the `SET MARKUP ON` command.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
414 Using this, a query could be published as a webpage entirely from within a SQL\*Plus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
415 session::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
416
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
417 > set markup html on
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
418 > spool /var/www/myreport.html
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
419 > select * from mytable;
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
420 > spool off
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
421 > set markup html off
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
422
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
423 YASQL and sqlpython have much more extensive, and convenient, output formatting options.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
424 By simply replacing the ";" terminating a query with a special backslash sequence, you can
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
425 get output in a variety of alternate formats.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
426
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
427 === ===================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
428 \\b bar graph (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
429 \\c CSV (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
430 \\c CSV (no column names) (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
431 \\G list (aligned)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
432 \\g list (not aligned)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
433 \\h HTML table (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
434 \\i as SQL `insert` statements
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
435 \\j JSON (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
436 \\l line plot, with markers (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
437 \\l line plot, no markers (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
438 \\p pie chart (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
439 \\s CSV
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
440 \\S CSV (no column names)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
441 \\t transposed (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
442 \\x XML (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
443 === ===================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
444
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
445 Thus, the sqlpython way to produce and web-publish a report fits on a single line::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
446
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
447 > select * from party\h > /var/www/party.html
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
448
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
449 List (`\\g`) output can be especially useful in reading tables with many columns without confusing
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
450 line wrapping. Transposed (`\\t`) output is ideal for reading many-columns/few-rows tables
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
451 like V$DATABASE. Generating plots directly from queries is just fun.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
452
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
453 Row output limits
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
454 -----------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
455
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
456 *YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
457
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
458 Follow the terminator in a query (usually ";") with a positive integer, and the query will only
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
459 output up to that number of rows::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
460
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
461 jrrt@orcl> select * from party;2
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
462
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
463 NAME STR INT WIS DEX CON CHA
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
464 ----- --- --- --- --- --- ---
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
465 Frodo 8 14 16 15 14 16
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
466 Gimli 17 12 10 11 17 11
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
467
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
468 2 rows selected.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
469
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
470 Multiple sessions
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
471 -----------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
472
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
473 *senora, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
474
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
475 When `connect -a {username@instance}` is used to create a new connection, senora does not close the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
476 old connection. It keeps both connections alive, and switching between them is far more convenient
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
477 than creating new connections::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
478
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
479 Enter sample here
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
480
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
481 psql-like shortcuts
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
482 -------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
483
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
484 *YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
485
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
486 Several backslash-character command shortcuts have been copied from psql, the command-line tool
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
487 for the PostgreSQL open-source database, into YASQL and sqlpython.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
488
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
489 ==== =====================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
490 \\c connect (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
491 \\c clear buffer (YASQL)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
492 \\d describe (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
493 \\e edit
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
494 \\g run (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
495 \\h help (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
496 \\i load (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
497 \\l list (YASQL)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
498 \\o spool (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
499 \\p list
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
500 \\q quit
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
501 \\w save
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
502 \\db directory of tablespaces (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
503 \\dd comments (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
504 \\dn directory of schemas (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
505 \\dt directory of tables
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
506 \\dv directory of views
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
507 \\di directory of indexes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
508 ==== =====================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
509
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
510 Enhanced bind variables
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
511 -----------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
512
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
513 *sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
514
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
515 Senora and sqlpython support SQL\*Plus-style bind variables. sqlpython also has an optional, easygoing
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
516 syntax for defining bind variables and permits them to be dynamically defined (no declaration required)::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
517
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
518 jrrt@orcl> :name = 'Legolas'
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
519 jrrt@orcl> print
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
520 :name = Legolas
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
521
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
522 sqlpython's `bind` command creates and populates bind variables for each
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
523 column of the row most recently returned from a query. The optional `autobind`
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
524 setting automatically does this after every query that returns a single row.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
525
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
526 ::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
527
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
528 jrrt@orcl> select name, str, int from party where name = :name;
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
529
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
530 NAME STR INT
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
531 ------- --- ---
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
532 Legolas 13 15
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
533
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
534 1 row selected.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
535
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
536 jrrt@orcl> print
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
537 :1 = Legolas
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
538 :2 = 13
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
539 :3 = 15
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
540 :int = 15
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
541 :name = Legolas
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
542 :str = 13
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
543
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
544 Tuning
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
545 ------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
546
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
547 *senora, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
548
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
549 Each program provides an assortment of commands to provide performance-related data.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
550 There are too many commands for detailed description, but a partial list will supply
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
551 some idea for the capabilities.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
552
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
553 Senora tuning commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
554 ~~~~~~~~~~~~~~~~~~~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
555
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
556 ============== ====== ==========================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
557 command abbrev effect
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
558 ============== ====== ==========================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
559 analyzeObject ana analyze tables or indexes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
560 cacheStats cst Display memory statisticts
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
561 compareStat coms compare statistics of two schemas
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
562 cstatement cs lists current SQL statements, by session
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
563 cstatement -S cs -S lists current SQL statements with their execution stats
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
564 FkeyIndexes fki analyze the existance of foreign key indexes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
565 hwm get high watermark info on analyzed tables (experimental)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
566 jobs print job information
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
567 kept show kept (pinned) code
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
568 kill kill a session
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
569 locks show sessions and the objects the are waiting for
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
570 logs print redo log activity
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
571 profile estimate current execution times
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
572 ps print session information
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
573 resize resize datafiles
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
574 rollSegs print rollback info
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
575 show parameter sp show init.ora parameter
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
576 space show tablespace and file stats
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
577 stat print session statistics
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
578 validate vi xvalidate structure an index
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
579 waits show what sessions are waiting for
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
580 xqueries xq show most expensive quieries
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
581 ============== ====== ==========================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
582
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
583 Before senora tuning commands can be run, `register Tuning` must be executed in the session.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
584
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
585 sqlython tuning commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
586 ~~~~~~~~~~~~~~~~~~~~~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
587
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
588 ======== =====================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
589 load prints the OS load on all cluster nodes (10g RAC)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
590 longops prints from gv$session_longops
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
591 sessinfo prints session information. Parameter: sid
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
592 sql prints the sql text from the cache. Parameter: sql_id
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
593 top List top active sessions
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
594 top9i 9i (and single instance) version of top
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
595 ======== =====================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
596
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
597 Wildcards in column names
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
598 -------------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
599
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
600 *YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
601
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
602 YASQL supports `\*` wildcards in column names, if there is a ~/.yasqlrc or /etc/yasql.conf file containing
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
603 `column_wildcards=1`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
604
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
605 sqlpython's wildcards are more powerful. When the option wildsql is set to ON,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
606 sqlpython will accept these in the column list of a SELECT statement:
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
607
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
608 * Wildcards (`\*` or `%`, and `?`)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
609
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
610 * Column numbers (`#1, #2`)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
611
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
612 * !, meaning NOT. `!str` means "all columns except STR".
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
613
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
614 These can be combined. `SELECT !#2, !c% FROM party` means 'select all columns except column #2 and any column beginning with 'C'", and is translated by sqlpython into `SELECT name, int, wis, dex FROM party;`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
615
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
616 ====================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
617 Drawbacks and quirks
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
618 ====================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
619
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
620 No tool reproduces all the features of SQL\*Plus (though gqlplus comes very, very close). These are the drawbacks most likely to be noticed.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
621
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
622 Smart prompt
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
623 ------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
624
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
625 Like SQL\*Plus, gqlplus invokes glogin.sql when it is started. Unfortunately, gqlplus does not re-invoke it when a new CONNECT statement is issued during a session. Thus, as for SQL\*Plus versions xxxx and before, the "smart prompt" can become dangerously deceptive in gqlplus.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
626
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
627 PL/SQL
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
628 ------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
629
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
630 Gqlplus, Senora, and sqlpython can run all PL/SQL as SQL\*Plus does.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
631
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
632 YASQL can run single lines of PL/SQL with `exec`, and automatically echoes DBMS_OUTPUT.PUT_LINE output, but it does not recognize anonymous SQL BEGIN..END blocks. sqlpython requires that PL/SQL blocks be bracketed
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
633 in `REMARK BEGIN` and `REMARK END` statements
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
634
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
635 Executing shell commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
636 ------------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
637
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
638 In SQL\*Plus, `host {command}` or `!{command}` run {command} on the operating system.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
639 These work in all the alternative tools as well, except that senora recognizes only
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
640 `host` (since, in senora, `!` is used for re-running commands from the history instead.)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
641
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
642 Error messages
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
643 --------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
644
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
645 Error messages in some programs are less friendly than in SQL*\Plus. For instance, senora
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
646 responds to most errors with "what ?"::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
647
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
648 0:jrrt@orcl> grep;
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
649 what ? "grep;" ?
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
650
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
651 sqlpython passes Python or Oracle errors it encounters up to the user, but it cannot
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
652 highlight the place in a SQL command where a syntax error took place.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
653
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
654 When compiling PL/SQL objects (functions, packages, procedures) that contain errors,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
655 the tools vary in the quality of information they return.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
656
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
657 * Senora reports that a compilation error has occured, and `show errors` works as expected.
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
658
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
659 * YASQL reports that a compilation error has occured. `show errors` in YASQL lists the compilation errors for *all* invalid PL/SQL objects in the schema.
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
660
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
661 * sqlpython reports errors immediately upon compilation.
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
662
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
663 Note that, in senora and YASQL, abbreviated forms of `show errors`, like `sho err`, are not recognized.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
664
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
665 Startup
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
666 -------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
667
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
668 Only SQL\*Plus can be used to login to a closed database. gqlplus hangs during the attempt.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
669 The other tools simply report that the database is closed and cannot connect.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
670
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
671 Text file editing
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
672 -----------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
673
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
674 In senora and YASQL, the `ed` and `\\e` commands (respectively) can be used to edit
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
675 text files using the environment's default editor. Unlike SQL\*Plus, however, they
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
676 do not edit the *most recently issued command* (the buffer), and the resulting file
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
677 is not automatically run. sqlpython's `ed`, without an argument, edits the SQL buffer
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
678 as SQL\*Plus does, and runs the result when the editor is closed. You may also
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
679 `ed {filename}` to edit a specific text file, `ed {N}` to load command number {N}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
680 from the command history into the buffer and edit it. Finally, if your EDITOR
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
681 environment is not set, sqlpython tries to find a more user-friendly editing program
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
682 on your machine than UNIX ed.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
683
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
684 Hints
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
685 -----
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
686
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
687 sqlpython removes all comments from commands at an early stage of parsing, and thus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
688 will not honor execution hints embedded within SQL statements.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
689
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
690 Variables
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
691 ---------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
692
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
693 YASQL does not support bind variables (`:` variables) or substitution variables (`&` variables).
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
694 The other tools support them, and sqlpython has enhanced bind variables (see above).
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
695
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
696 Parsing speed
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
697 -------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
698
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
699 sqlpython parses each line of a SQL command as it is entered to determine whether the command
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
700 is finished yet. This produces noticable waits as a query approaches ten lines of SQL, and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
701 downright annoying as it grows beyond that. To avoid it, you can prefix long queries with
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
702 `REMARK BEGIN` and end them with `REMARK END`, promising sqlpython that you will only enclose a
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
703 single query and freeing it from the need to parse until `REMARK END` is reached.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
704
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
705 Maturity
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
706 --------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
707
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
708 All the alternate tools are produced by individuals or very small groups, so it is not
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
709 uncommon to encounter bugs. If you encounter one, you can e-mail the author;
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
710 for sqlpython in particular, error reports filed in the project's bug tracker at
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
711 http://trac-hg.assembla.com/sqlpython are appreciated. Since the projects are
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
712 open-source, and most of them are written in the easy-to-use languages Perl and Python,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
713 you can always fix bugs yourself and improve the products for everyone.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
714
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
715 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
716 Installation
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
717 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
718
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
719 First, make sure you have Oracle client software on your machine! gqlplus simply
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
720 wraps SQL\*Plus, and wenora, YASQL, and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
721 sqlpython depend on
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
722 Perl or Python modules that access the Oracle OCI, so none of them will work in the absence of
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
723 an Oracle client.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
724
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
725 You will also need Perl (for senora or YASQL) or Python (for sqlpython). Virtually all UNIX
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
726 systems will have these languages installed, and frankly, every computer *should*. Free installers for
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
727 all common platforms are available from the languages' websites at http://www.perl.org/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
728 and http://python.org/.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
729
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
730 gqlplus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
731 -------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
732
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
733 Gqlplus is distributed as a gzipped tarball, downloadable from http://gqlplus.sourceforge.net/,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
734 containing the C source code and a precompiled binary
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
735 for Linux. To use on Linux, simply expand the tarball and run Linux/gqlplus; you may want to place
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
736 the file or a link to it somewhere in your PATH. On other systems, you will a C compiler such
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
737 as `gcc`; run `./configure` and `make` as directed in the project's `README` file.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
738
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
739 Senora
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
740 ------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
741
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
742 Unix
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
743 ~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
744
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
745 Senora is distributed as a gzipped tarball, downloadable from http://senora.sourceforge.net/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
746
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
747 Senora depends on the DBI and DBD::Oracle packages. Perl's `cpan` tool is a fairly convenient way
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
748 to install them::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
749
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
750 $ cpan
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
751
304
98975ce340ac more docs changes
catherine@dellzilla
parents: 288
diff changeset
752 cpan> install DBI Term::ReadLine::Perl
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
753 cpan> force install DBD::Oracle
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
754
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
755 If this is your first time running `cpan`, it will ask you many configuration questions
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
756 on startup. Accepting the default answers is generally correct.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
757 Running under `sudo`, `cpan` may not find your $ORACLE_HOME; running as `root` avoids this problem.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
758
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
759 After installation is complete, download and unzip Senora-0.5.1.tgz from http://senora.sourceforge.net/, cd into senora/core, and run `perl Senora.pm`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
760
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
761 Windows
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
762 ~~~~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
763
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
764 http://senora.sourceforge.net/ also has a "Senora for Windows" download. Download it, unzip, and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
765 run `SenoraForWindows/senora.exe`. You may wish to create a shortcut for your convenience.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
766
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
767 YASQL
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
768 -----
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
769
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
770 Like senora, YASQL depends on the DBI and DBD::Oracle packages. Several other Perl packages
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
771 are recommended and can also be installed through `cpan`::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
772
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
773 cpan> install DBI
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
774 cpan> force install DBD::Oracle
304
98975ce340ac more docs changes
catherine@dellzilla
parents: 288
diff changeset
775 cpan> install Term::ReadKey Text::CSV_XS Time::HiRes Term::ReadLine::Perl
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
776
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
777 Download yasql-1.83.tar.gz from http://sourceforge.net/projects/yasql/, unzip and untar it, and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
778 cd into yasql-1.83. Run `./configure`, `make`, and (as root) `make install`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
779 This will place the yasql executable into /usr/local/bin, so that you can run it from anywhere
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
780 by simply typing `yasql`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
781
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
782 sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
783 ---------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
784
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
785 Some UNIX machines come shipped with Python but without python-dev, which you should install
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
786 from your distribution's package repository if you don't have it already. Downloads from http://python.org
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
787 (including the Windows installer) include python-dev out of the box.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
788
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
789 The easiest way to install sqlpython is with `easy_install`, a popular Python package installation
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
790 tool. You can get easy_install by installing python-setuptools from your Linux distribution's repository,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
791 or by downloading directly from http://peak.telecommunity.com/DevCenter/EasyInstall.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
792
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
793 Once you have easy_install, simply type (as root, or using `sudo`)::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
794
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
795 $ easy_install -UZ sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
796
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
797 The -UZ flags, though optional, will update your sqlpython installation, if necessary, and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
798 unzip the code so that you can modify it.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
799
305
9c727d6afdc0 docs changes
catherine@dellzilla
parents: 304
diff changeset
800 To generate graphs using sqlpython's `\\b`, `\\g`, and `\\l` terminators, you will need to install `pylab` (or `matplotlib`)::
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
801
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
802 $ easy_install pylab
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
803
305
9c727d6afdc0 docs changes
catherine@dellzilla
parents: 304
diff changeset
804 $ apt-get install python-matplotlib
9c727d6afdc0 docs changes
catherine@dellzilla
parents: 304
diff changeset
805
304
98975ce340ac more docs changes
catherine@dellzilla
parents: 288
diff changeset
806 Alternately, a Windows installer is available at http://pypi.python.org/pypi/sqlpython, though easy_install
98975ce340ac more docs changes
catherine@dellzilla
parents: 288
diff changeset
807 works on Windows as well.
98975ce340ac more docs changes
catherine@dellzilla
parents: 288
diff changeset
808
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
809 ===================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
810 Extending the tools
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
811 ===================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
812
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
813 All the alternative tools are open-source, so you have the right to view the source
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
814 code, make your own modifications, contribute your modifications back to the original
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
815 project, and even distribute your own modified version. YASQL, senora, and sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
816 are especially easy to modify since they are written in dynamic languages, which are
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
817 easy to program in and require no compilation step.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
818
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
819 When you are ready to begin customizing or improving your favorite tool, imitation is
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
820 the key; look in the source code files of the tool for the code corresponding to a
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
821 simple command, copy and rename it, then work on small modifications until the new
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
822 functionality meets your needs.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
823
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
824 As a specific example, we will add new commands to sqlpython.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
825
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
826 `easy_install -UZ sqlpython` places sqlpython's source code files into your Python site-packages
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
827 directory, probably someplace like `/usr/lib/python2.5/site-packages/`. You can edit the files there
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
828 (you may need to change their ownership from `root` to yourself first). Any method in
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
829 `mysqlpy` whose name begins with `do_` will be registered as a sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
830 command the next time sqlpython is started. The simplest possible new command could
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
831 be written as::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
832
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
833 def do_hello(self, arg):
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
834 print 'Hello, World!'
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
835
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
836 Now, typing `hello [arguments]` in a sqlpython session calls `do_hello`. The
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
837 optional arguments will be assigned to `arg`, but in this case they are not used.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
838 Let's see a more useful function, one that will
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
839
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
840 * Make use of the argument string
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
841
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
842 * Provide online documentation
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
843
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
844 * Use a flag to optionally modify the command's behavior
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
845
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
846 * Send output to file, paste buffer, or pipe when `>` or `|` is used.
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
847
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
848 ::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
849
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
850 @options([make_option('-u', '--uppercase', action='store_true',
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
851 help='use ALL CAPS')])
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
852 def do_greet(self, arg, opts):
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
853 'Provides a personalized greeting.'
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
854 result = 'Hello %s!\n' % arg
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
855 if opts.uppercase:
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
856 result = result.upper()
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
857 self.stdout.write(result)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
858
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
859 Now we run sqlpython and try the new command::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
860
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
861 jrrt@orcl> greet
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
862 Hello !
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
863 jrrt@orcl> greet Larry E.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
864 Hello Larry E.!
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
865 jrrt@orcl> help greet
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
866 Provides a personalized greeting.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
867 Usage: greet [options] arg
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
868
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
869 Options:
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
870 -h, --help show this help message and exit
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
871 -u, --uppercase use ALL CAPS
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
872
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
873 jrrt@orcl> greet -u World
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
874 HELLO WORLD!
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
875
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
876 Senora v0.6 includes a command, `lregister`, that will automatically create a new senora plugin from
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
877 SQL commands in a file. This will make an incredibly easy way to make your own senora
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
878 commands for your favorite tasks.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
879
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
880 =================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
881 Cross-RDBMS tools
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
882 =================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
883
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
884 All the tools reviewed so far have been designed purely for Oracle. If you work with
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
885 multiple RDBMS platforms, however, you may be interested in a SQL tool that is compatible
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
886 with all of them, which opens up a whole new set of possible programs.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
887
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
888 One such possibility is sqlcmd (http://www.clapper.org/software/python/sqlcmd),
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
889 an open-source Python program available without charge on all platforms. It
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
890 operates seamlessly across Oracle, MySQL, PostreSQL, SQLite, and MS SQL Server.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
891
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
892 Its functionality is rather basic compared to the Oracle-specific tools, and there
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
893 are conventions that will be unfamiliar to those immersed in an Oracle world. For
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
894 example, use of stored procedures is not supported at all, column and table names
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
895 are case-sensitive, and database connection details are specified with an unfamiliar
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
896 format (`sqlcmd -d orcl,oracle,localhost,scott,tiger` in place of `sqlplus scott/tiger@orcl`). Nonetheless, it and similar
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
897 tools are viable options when cross-RDBMS compatibility is a key requirement.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
898
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
899 =======
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
900 Summary
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
901 =======
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
902
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
903 All the tools offer capabilities that will make command-line interaction with Oracle easier and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
904 more powerful, and you should experiment to find out which one(s) suit you best. If you want as
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
905 little change as possible, gqlplus provides a few extra features while being almost completely
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
906 transparent and SQL\*Plus-compatible. Sqlpython has the broadest set of features and is
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
907 being developed most actively. YASQL and senora may be good alternatives if you want some
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
908 of the features unique to those programs, such as senora's rich set of tuning commands,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
909 or if you prefer Perl for writing your own improvements.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
910
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
911 There will always be a place for SQL\*Plus, but alternative tools
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
912 can replace and improve upon some of the work you have been doing both with SQL\*Plus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
913 and with GUI tools. No matter which programs you use, you will get more powerful,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
914 enjoyable commmand-line experiences and gain new appreciation for what open-source
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
915 development can bring to an Oracle environment.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
916
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
917 =====
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
918 Links
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
919 =====
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
920
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
921 * http://gqlplus.sourceforge.net/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
922
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
923 * http://senora.sourceforge.net/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
924
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
925 * http://sourceforge.net/projects/yasql/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
926
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
927 * http://pypi.python.org/pypi/sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
928
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
929 * http://www.clapper.org/software/python/sqlcmd
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
930
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
931 * http://catherinedevlin.blogspot.com/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
932
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
933 ================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
934 Acknowledgements
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
935 ================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
936
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
937 Thanks to all the authors of the open-source tools reviewed here, but especially to Martin Drautzburg,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
938 author of senora, for important corrections and additions.