|
281
|
1 ==============================
|
|
247
|
2 SQLPython's extra capabilities
|
|
|
3 ==============================
|
|
|
4
|
|
|
5 For the most part, SQLPython simply duplicates SQL\*Plus's capabilites.
|
|
|
6
|
|
279
|
7 Neatened output
|
|
|
8 ===============
|
|
|
9
|
|
|
10 When printing query results, sqlpython economizes on screen space by allocating
|
|
|
11 only the width each column actually needs.
|
|
|
12
|
|
|
13 Smart prompt
|
|
|
14 ============
|
|
|
15
|
|
|
16 sqlpython automatically uses `username`@`instance`> as its prompt, helping
|
|
|
17 avoid wrong-instance and wrong-user errors.
|
|
|
18
|
|
|
19 Tab completion
|
|
|
20 ==============
|
|
|
21
|
|
|
22 When typing SQL commands, hitting `<TAB>` after entering part of an object
|
|
|
23 or column name brings up a list of appropriate possibilities or, if there
|
|
|
24 is only one possibility, fills in the rest of the name. This feature is
|
|
|
25 not yet very reliable, but can save typing.
|
|
|
26
|
|
|
27 Scripting
|
|
|
28 =========
|
|
|
29
|
|
|
30 Like SQL\*Plus, sqlpython can run scripts (text files with series of SQL and
|
|
|
31 sqlpython commands) with `@/path/to/script.sql` or (for online scripts)
|
|
|
32 `@http://scripthost/scriptlibrary/script.sql`.
|
|
|
33
|
|
|
34 History
|
|
|
35 =======
|
|
|
36
|
|
|
37 The up- and down-arrow keys allow you to scroll through the lines entered so far
|
|
|
38 in your sqlpython session.
|
|
|
39
|
|
|
40 Commands are also entered into a command history.
|
|
|
41
|
|
|
42 `history` or `hi`
|
|
|
43 List entire command history
|
|
|
44
|
|
|
45 `list` or `li`
|
|
|
46 List only last command
|
|
|
47
|
|
|
48 `hi <N>`
|
|
|
49 List command number <N> from history.
|
|
|
50
|
|
|
51 `hi <N>-`, `hi -<N>`
|
|
|
52 List commands from <N> onward, or up to <N>
|
|
|
53
|
|
|
54 `hi <str>`
|
|
|
55 Lists commands that include the string <str>
|
|
|
56
|
|
|
57 `hi /<regex>/`
|
|
|
58 Lists commands that match the regular expression <regex>
|
|
|
59
|
|
|
60 `run`, `r`, or `\\g`
|
|
|
61 Run the most recent command again
|
|
|
62
|
|
|
63 `run <N>`
|
|
|
64 Run command <N>
|
|
|
65
|
|
|
66 `run <str>`, `run /<regex>/`
|
|
|
67 Run command matching <str> or <regex> (as for `history`) -
|
|
|
68 if multiple items would match, run most recent
|
|
|
69
|
|
281
|
70 Special I/O destinations
|
|
|
71 ========================
|
|
|
72
|
|
|
73 Much as in a UNIX shell, you can follow a command with a special output destination.
|
|
|
74
|
|
|
75 `> {filename}` sends the output to a file. This is more convenient than SQL\*Plus's
|
|
|
76 SPOOL {filename}... SPOOL OFF (though you can use those as well).
|
|
|
77
|
|
|
78 `>` alone (no filename) sends the output to the paste buffer.
|
|
|
79
|
|
|
80 `|` pipes the output to an operating-system command.
|
|
|
81
|
|
|
82 When `< {filename}` is included in your command, it is replaced with the contents of
|
|
|
83 {filename} before the command is run.
|
|
|
84
|
|
|
85 Examples::
|
|
|
86
|
|
|
87 Special output formats
|
|
|
88 ======================
|
|
|
89
|
|
|
90 By replacing the `;` that terminates a SELECT statement with a backslash-character
|
|
|
91 sequence, you can get output in a number of useful formats. The `terminators`
|
|
|
92 command lists them, for your convenience::
|
|
|
93
|
|
|
94 === ======================== ================================
|
|
|
95 Especially useful for
|
|
|
96 === ======================== ================================
|
|
|
97 ; standard Oracle format
|
|
|
98 \c CSV (with headings) sending to spreadsheets
|
|
|
99 \C CSV (no headings)
|
|
|
100 \g list wide output with linewraps
|
|
|
101 \G aligned list
|
|
|
102 \h HTML table web reports
|
|
|
103 \i INSERT statements copying to other instances
|
|
|
104 \j JSON
|
|
|
105 \s CSV (with headings)
|
|
|
106 \S CSV (no headings)
|
|
|
107 \t transposed "narrow" tables like v$database
|
|
|
108 \x XML
|
|
|
109 \l line plot, with markers
|
|
|
110 \L scatter plot (no lines)
|
|
|
111 \b bar graph
|
|
|
112 \p pie chart
|
|
|
113 === ======================== ================================
|
|
|
114
|
|
|
115 Most of these output formats are even more useful when combined with special output
|
|
|
116 destinations. For example, `SELECT * FROM party\h > /var/www/party_report.html`
|
|
|
117 could create an HTML report in the webserver's documents directory, ready to serve.
|
|
|
118
|
|
247
|
119 UNIX-like commands
|
|
|
120 ==================
|
|
|
121
|
|
279
|
122 Many sqlpython commands allow you to act as though the database objects
|
|
|
123 were files in a UNIX filesystem. Many of the commands also accept flags
|
|
|
124 to modify their behavior.
|
|
|
125
|
|
281
|
126 ls {object type/object name, with wildcards}
|
|
|
127 Lists objects from the data dictionaries, as though they were in a
|
|
|
128 *object_type*/*object_name* directory structure. Thus, `ls view/\*`
|
|
|
129 lists all the user's views. Calling with no argument is equivalent
|
|
|
130 to `ls *`.
|
|
|
131
|
|
|
132 Options::
|
|
|
133
|
|
|
134 -l, --long long descriptions
|
|
|
135 -a, --all all schemas' objects (otherwise, you only get your own)
|
|
|
136 -t, --timesort Sort by last_ddl_time
|
|
|
137 -r, --reverse Reverse order while sorting
|
|
247
|
138
|
|
281
|
139 `ls -lt *;10` lists the ten items with the most recent last_ddl_time;
|
|
|
140 this can be a good way to answer the question, "What was I working on?"
|
|
|
141
|
|
|
142 cat {remainder of query}
|
|
|
143 Shorthand for "SELECT * FROM". Can be combined with anything else
|
|
|
144 that fits into a SELECT statement (WHERE, ORDER BY, etc.)
|
|
|
145
|
|
|
146 grep {grep {target} {table} [{table2,...}]
|
|
|
147 Equivalent to SELECT * FROM {table} WHERE *any column* LIKE '%{target}%'
|
|
|
148 Useful when you don't know, don't remember, or don't care which column
|
|
|
149 a value may be found in.
|
|
|
150
|
|
|
151 Options::
|
|
|
152
|
|
|
153 -i, --ignore-case Case-insensitive search
|
|
247
|
154
|
|
281
|
155 find -c {target}, find -t {column}
|
|
|
156 Lists all tables or columns whose names contain {target}. More convenient than
|
|
|
157 querying user_tab_columns/all_tab_columns or user_tables/all_tables.
|
|
|
158 Options::
|
|
|
159 -a Find all objects (not just my own)
|
|
|
160
|
|
247
|
161 PostgreSQL-like shortcuts
|
|
|
162 =========================
|
|
|
163
|
|
|
164 ----- ------------------
|
|
|
165 z y
|
|
|
166 ----- ------------------
|
|
|
167 \\c connect
|
|
|
168 \\d desc
|
|
|
169 \\e edit
|
|
|
170 \\g run
|
|
|
171 \\h help
|
|
|
172 \\i load
|
|
|
173 \\o spool
|
|
|
174 \\p list
|
|
|
175 \\q quit
|
|
|
176 \\w save
|
|
|
177 \\db _dir_tablespaces
|
|
|
178 \\dd comments
|
|
|
179 \\dn _dir_schemas
|
|
|
180 \\dt _dir_tables
|
|
|
181 \\dv _dir_views
|
|
|
182 \\di _dir_indexes
|
|
|
183 \\? help psql
|
|
|
184 ----- ------------------
|
|
|
185
|
|
281
|
186 PL/SQL source code
|
|
|
187 ==================
|
|
|
188
|
|
|
189 pull {object_name}
|
|
|
190 Displays the PL/SQL source code for {object_name}.
|
|
|
191
|
|
|
192 Options:
|
|
|
193 -d, --dump dump results to files (object_type/object_name.sql)
|
|
|
194 -f, --full get dependent objects as well
|
|
|
195 -a, --all all schemas' objects
|
|
|
196
|
|
|
197 bzr, git, hg {object_name}
|
|
|
198 Dump source code to files, as `pull -f`, but also creates or commits to a
|
|
|
199 repository of the appropriate distributed version control system
|
|
|
200 (Bazaar, Git, or Mercurial, respectively).
|
|
|
201
|
|
|
202 find {target}
|
|
|
203 Lists all PL/SQL objects whose source code contains the {target} string.
|
|
|
204 Always case-insensitive.
|
|
|
205 Options::
|
|
|
206 -a Search all PL/SQL objects (not just my own)
|
|
|
207
|
|
|
208
|
|
|
209 Bind variables
|
|
|
210 ==============
|
|
|
211
|
|
|
212 Bind variables work in sqlpython as they do in SQL\*Plus, but they are set dynamically; there
|
|
|
213 is no need to declare them before use. The syntax for setting them is more permissive than
|
|
|
214 in SQL\*Plus; all these are recognized::
|
|
|
215
|
|
|
216 exec :mybind := 'value'
|
|
|
217 exec :mybind = 'value'
|
|
|
218 :mybind := 'value'
|
|
|
219 :mybind = 'value'
|
|
|
220
|
|
|
221 The current values of all bind variables can be viewed with the `print` command.
|
|
|
222
|
|
|
223 The `bind` command creates and populates bind variables for the final row of the most recent
|
|
|
224 SELECT statement executed; each column name is used as a bind variable, which is filled with
|
|
|
225 the value. `bind -r {rownumber}` does the same, but fills from row {rownumber} instead of
|
|
|
226 from the final row (row numbers begin at 0 for this command).
|
|
|
227
|
|
|
228 When the `autobind` sqlpython parameter is True, a `bind` statement is issued automatically
|
|
|
229 after every query that returns exactly one row.
|
|
|
230
|
|
|
231 Bind variables are available from within Python as a dictionary named `binds` (see Python).
|
|
|
232
|
|
|
233 Substitution variables
|
|
|
234 ======================
|
|
|
235
|
|
|
236 Substitution variables ("&" variables) work much as they do in SQL\*Plus. As in SQL\*Plus,
|
|
|
237 the `scan` parameter determines whether queries are scanned to replace substitution
|
|
|
238 variables. Unlike SQL\*Plus, sqlpython knows how annoying it is to hit a substitution
|
|
|
239 variable you didn't expect, so entering "SET SCAN OFF" when prompted for a substitution
|
|
|
240 variable actually aborts the substitution process.
|
|
|
241
|
|
247
|
242 Wild SQL
|
|
|
243 ========
|
|
|
244
|
|
|
245 Wild SQL is a nonstandard SQL feature that must be enabled with `set wildsql on`. When it is
|
|
|
246 enabled, column names in a SELECT statement do not need to be explicitly typed.
|
|
|
247
|
|
|
248 * % or \* as wildcards::
|
|
|
249
|
|
|
250 SELECT d* FROM v$database;
|
|
|
251
|
|
|
252 SELECT
|
|
|
253
|
|
|
254 Wild SQL can only be used in the primary column list of straightforward SELECT statements,
|
|
279
|
255 not in subqueries, `UNION`ed queries, etc.
|
|
281
|
256
|
|
|
257 Parameters
|
|
|
258 ==========
|
|
|
259
|
|
|
260 Several parameters control the behavior of sqlpython itself.
|
|
|
261
|
|
|
262 ===================== ================================================== ===============
|
|
|
263 default
|
|
|
264 ===================== ================================================== ===============
|
|
|
265 autobind When True, single-row queries automatically `bind` False
|
|
|
266 commit_on_exit Automatically commits work at end of session True
|
|
|
267 continuation_prompt Prompt for second line and onward of long statement >
|
|
|
268 default_file_name The file opened by `edit`, if not specified afiedt.buf
|
|
|
269 echo Echo command entered before executing False
|
|
|
270 editor Text editor invoked by `edit`. varies
|
|
|
271 heading Print column names True
|
|
|
272 maxfetch Maximum number of rows to return from any query 1000
|
|
|
273 maxtselctrows Maximum # of rows from a tselect or \\n query 10
|
|
|
274 prompt Probably unwise to change user@instance>
|
|
|
275 scan Interpret & as indicating substitution variables True
|
|
|
276 serveroutput Print DBMS_OUTPUT.PUT_LINE results True
|
|
|
277 sql_echo Print text of "behind-the-scenes" queries False
|
|
|
278 timeout In seconds 30
|
|
|
279 timing Print time for each command to execute False
|
|
|
280 wildsql Accept *, %, #, and ! in column names False
|
|
|
281 ===================== ================================================== ===============
|
|
|
282
|
|
|
283 The user can change these with the `set {paramname} {new-value}` statement.
|
|
|
284 The True/False parameters accept new
|
|
|
285 values permissively, recognizing "True", "False", "T", "F", "yes", "no", "on", "off"...
|
|
|
286
|
|
|
287 `set` and `show` both list the current values of the sqlpython parameters.
|
|
|
288 `show parameter {param}` shows current Oracle parameters (from v$parameter), as it does
|
|
|
289 in SQL\*Plus.
|
|
|
290
|
|
|
291 Tuning
|
|
|
292 ======
|
|
|
293
|
|
|
294 In sqlpython, `explain {SQL ID}` shows the execution plan for the SQL statement with the
|
|
|
295 given ID. If SQL ID is omitted, it defaults to the most recent SQL executed.
|
|
|
296 (This is not necessarily the last statement `EXPLAIN PLAN` was issued against.)
|
|
|
297
|
|
|
298 Other specialized sqlpython tuning commands include:
|
|
|
299
|
|
|
300 load
|
|
|
301 Displays OS load on cluster nodes (10gRAC)
|
|
|
302
|
|
|
303 longops
|
|
|
304 Displays long-running operations
|
|
|
305
|
|
|
306 sessinfo
|
|
|
307 Reports session info for the given sid, extended to RAC with gv$
|
|
|
308
|
|
|
309 top, top9i
|
|
|
310 Displays active sessions
|
|
|
311
|
|
|
312
|
|
|
313
|
|
|
314
|
|
|
315
|
