Force index use in Oracle
You can use optimizer hints select /*+ INDEX(table_name index_name) */ from table etc… More on using optimizer hints
sqlplus
Suppress output of variables substitution in sqlplus
SET VERIFY OFF is the answer.
How to display table data more clearly in oracle sqlplus
I usually start with something like: set lines 256 set trimout on set tab off Have a look at help set if you have the help information installed. And then select name,address rather than select * if you really only want those two columns.
arrow keys are not functional in sqlplus
You can use a third party utility called rlwrap. rlwrap is a readline wrapper, a small utility that uses the GNU readline library to allow the editing of keyboard input for any other command. It maintains a separate input history for each command, and can TAB-expand words using all previously seen words and/or a user-specified … Read more
How to insert a string which contains an “&”
I keep on forgetting this and coming back to it again! I think the best answer is a combination of the responses provided so far. Firstly, & is the variable prefix in sqlplus/sqldeveloper, hence the problem – when it appears, it is expected to be part of a variable name. SET DEFINE OFF will stop … Read more
How to kill a running SELECT statement
As you keep getting pages of results I’m assuming you started the session in SQL*Plus. If so, the easy thing to do is to bash ctrl + break many, many times until it stops. The more complicated and the more generic way(s) I detail below in order of increasing ferocity / evil. The first one … Read more
How can I issue a single command from the command line through sql plus?
I’m able to run an SQL query by piping it to SQL*Plus: @echo select count(*) from table; | sqlplus username/password@database Give @echo execute some_procedure | sqlplus username/password@databasename a try.
Maximum length of command line argument that can be passed to SQL*Plus?
Try with: xargs –show-limits </dev/null Your environment variables take up 2446 bytes POSIX upper limit on argument length (this system): 2092658 POSIX smallest allowable upper limit on argument length (all systems): 4096 Maximum length of command we could actually use: 2090212 Size of command buffer we are actually using: 131072 There is no limit per … Read more
sqlplus statement from command line
Just be aware that on Unix/Linux your username/password can be seen by anyone that can run “ps -ef” command if you place it directly on the command line . Could be a big security issue (or turn into a big security issue). I usually recommend creating a file or using here document so you can … Read more
Favorite SQL*Plus tips and tricks [closed]
You can use rlwrap to add readline support to sqlplus. Run sqlplus like this: $ rlwrap -c sqlplus username@database Now up/down will scroll through command history. Use ctrl-r to search backwards through history, etc. This makes sqlplus bearable. Also, add this to your login.sql to set the linesize to whatever the width of your terminal … Read more