blocks|key|1451704|text|是的，我每天优先使用SQL+Plus而不是Toad或SQL+Developer+(尽管我也使用SQL+Developer浏览数据库)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1451705|我的login.sql脚本(+SQL+Plus会自动运行)中包含以下内容：|1451706|1)将默认编辑器(记事本)替换为我选择的一个：|1451707|define+_editor+=+"C:\Program+Files\TextPad+5\TextPad.exe"|code-block|syntax|javascript|1451708|2)让SQL提示符显示数据库名称，这样我就知道自己在哪里(这要感谢Tom+Kyte+)：|1451709|COLUMN+global_name+new_value+gname
SET+TERMOUT+OFF
SELECT+LOWER(USER)+%7C%7C+'@'+%7C%7C+global_name%7C%7CCHR(10)%7C%7C'SQL>+'+AS+global_name
FROM+++global_name;
SET+SQLPROMPT+'&gname'
SET+TERMOUT+ON|1451710|..。加上与您的设置类似的其他设置。|1451711|我还发现Tom+Kyte的print_table过程非常有用。|offset|length|1451712|entityMap|0|LINK|mutability|MUTABLE|url|http://asktom.oracle.com/tkyte/print_table/^0|0|0|0|0|0|0|0|D|B|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|15|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|17|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|18|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|19|8|@]|9|@$S|1A|T|1B|1|1C]]|A|$]]|$1|U|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

Yes, I use SQL Plus every day in preference to Toad or SQL Developer (though I also use SQL Developer to browse the database).

I have the following in my login.sql script (which SQL Plus runs automatically):

1) Replace default editor (Notepad) with one of my choice:

<pre><code>define _editor = "C:\Program Files\TextPad 5\TextPad.exe"
</code></pre>

2) Make SQL prompt show database name so I know where I am (thanks to Tom Kyte for this):

<pre><code>COLUMN global_name new_value gname
SET TERMOUT OFF
SELECT LOWER(USER) || '@' || global_name||CHR(10)||'SQL&gt; ' AS global_name
FROM global_name;
SET SQLPROMPT '&amp;gname'
SET TERMOUT ON
</code></pre>

... plus other setting similar to yours.

I also find Tom Kyte's <a href="http://asktom.oracle.com/tkyte/print_table/" rel="noreferrer">print_table</a> procedure very useful.

blocks|key|1236974|text|我发现在指令中使用SQL*Plus列变量很方便-例如，我经常在一个会话中，想要假脱机到一个新的文件名，以避免覆盖可能已经存在的另一个日志，并执行此操作(通过@+file的前三个语句)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1236975|SQL>+column+spr+new_value+spoolref
SQL>+select+user%7C%7C'_'%7C%7Cabs(dbms_random.random)+spr+from+dual;
SQL>+spool+&spoolref
+++++++++...+do+work+here+...
SQL>+spool+off|code-block|syntax|javascript|1236976|然后，我将通过时间排序找到新的日志-如果您愿意，您可以始终使用随机数以外的其他策略。|1236977|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I find it is handy to use SQL*Plus column variables within directives - for example, I'm often in a session and want to spool to a new file name to avoid overwriting another log that may already exist and do this (first three statements through an @file):

<pre><code>SQL&gt; column spr new_value spoolref
SQL&gt; select user||'_'||abs(dbms_random.random) spr from dual;
SQL&gt; spool &amp;spoolref
 ... do work here ...
SQL&gt; spool off
</code></pre>

I'll then find the new log by sorting by time - you could always use some strategy other than the random number if you prefer.

blocks|key|1237036|text|请记住，我们可以将这些设置放在login.sql脚本中，该脚本将在我们启动SQL时自动运行。Find+out+more.|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1237037|巧妙的是，从10g开始，这个脚本在我们每次连接时运行，而不是在我们第一次启动SQL*Plus时运行。|1237038|SQL>+conn+apc
Enter+password:
Connected.
Running+login+script

Session+altered.

SQL>+conn+scott
Enter+password:
Connected.
Running+login+script

Session+altered.

SQL>|code-block|syntax|javascript|1237039|entityMap|0|LINK|mutability|MUTABLE|url|http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch2.htm#sthref414^0|1A|E|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

Remember that we can put these settings in the login.sql script which will be run automatically whenever we start SQL*Plus. <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch2.htm#sthref414" rel="noreferrer">Find out more.</a>

The neat thing about this is, that since 10g, this script is run every time we connect rather just the first time we fire up SQL*Plus...

<pre><code>SQL&gt; conn apc
Enter password:
Connected.
Running login script

Session altered.

SQL&gt; conn scott
Enter password:
Connected.
Running login script

Session altered.

SQL&gt;
</code></pre>

blocks|key|1237084|text|我喜欢在脱机状态下使用sqlplus。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1237085|sqlplus+-S+user/password+@query.sql>+file.txt|code-block|syntax|javascript|1237086|query.sql在哪里|1237087|set+feedback+off+verify+off+heading+off+pagesize+0
...here+goes+a+query...
quit;
/|1237088|这样我就可以从windows或unix的bat/script文件中的数据库中获取信息。|1237089|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I like to use sqlplus in off-line.

<pre><code>sqlplus -S user/password @query.sql&gt; file.txt
</code></pre>

where query.sql is

<pre><code>set feedback off verify off heading off pagesize 0
...here goes a query...
quit;
/
</code></pre>

So i can get info from the database in my bat/script files in windows or unix.

blocks|key|1451935|text|我专门使用SQL*Plus来处理Oracle。其他答案已经提供了一些非常方便的login.sql内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1451936|这是我的login.sql。我抄袭了Tom+Kyte和William+Robertson的一些建议。也许你也会发现一些你想用的东西。|offset|length|style|CODE|1451937|set+termout+off
set+serveroutput+on+size+unlimited
set+pagesize+50000
set+linesize+135
set+long+50000
set+trimspool+on
set+tab+off
def+_editor+=+"C:\Progra~1\Notepad%2B%2B\Notepad%2B%2B.exe"

define+gname=idle
column+global_name+new_value+gname
select+lower(user)+%7C%7C+'@'+%7C%7C
+++++++substr(global_name,1,decode(dot,0,length(global_name),dot-1))+global_name
from+(select+global_name,instr(global_name,'.')+dot+from+global_name);
set+sqlprompt+'&gname>+'

alter+session+set+nls_date_format+=+'dd-mm-yyyy+hh24:mi:ss'
/
var+sid+number
var+serial#+number
var+tracefile+VARCHAR2(200)

DECLARE
++++v_audsid+v$session.audsid%25TYPE;
BEGIN
++++SELECT+sid,+serial#,+audsid
++++INTO+++:sid,+:serial#,+v_audsid
++++FROM+++v$session
++++WHERE++audsid+=+SYS_CONTEXT('USERENV','SESSIONID');

++++SELECT+par.value+%7C%7C
+++++++++++CASE+WHEN+par.value+LIKE+'%25/%25'+THEN+'/'+ELSE+'\'+END+%7C%7C
+++++++++++LOWER(th.instance)+%7C%7C
+++++++++++'_ora_'+%7C%7C+LTRIM(TO_CHAR(pro.spid,'fm99999'))+%7C%7C+'.trc'+AS+filename
++++INTO+++:tracefile
++++FROM+++v$process+++pro
+++++++++,+v$session+++se
+++++++++,+v$parameter+par
+++++++++,+v$thread++++th
++++WHERE++se.audsid+=+v_audsid
++++AND++++pro.addr+=+se.paddr
++++AND++++par.NAME+=+'user_dump_dest';
END;
/

BEGIN
++++IF+:sid+IS+NULL+THEN
++++++++SELECT+sid
++++++++INTO+++:sid
++++++++FROM+++v$mystat
++++++++WHERE++rownum+=+1;
++++END+IF;
END;
/

set+termout+on
set+feedback+off
exec+DBMS_OUTPUT.PUT_LINE('Sessie:+'+%7C%7C+:sid+%7C%7C+CASE+WHEN+:serial#+IS+NULL+THEN+'+(no+access+to+V$+tables)'+ELSE+','+%7C%7C+:serial#+END)
exec+IF+:tracefile+IS+NOT+NULL+THEN+DBMS_OUTPUT.PUT_LINE('Eventueel+trace-bestand:+'+%7C%7C+:tracefile);+END+IF
prompt
set+feedback+on|code-block|syntax|javascript|1451938|entityMap^0|0|4|9|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I use SQL*Plus exclusively to work with Oracle. Other answers already give some very handy login.sql contents.

This is my <code>login.sql</code>. I copied some suggestions from Tom Kyte and William Robertson in there. Maybe you find some things you want to use as well.

<pre><code>set termout off
set serveroutput on size unlimited
set pagesize 50000
set linesize 135
set long 50000
set trimspool on
set tab off
def _editor = "C:\Progra~1\Notepad++\Notepad++.exe"

define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
 substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name
from (select global_name,instr(global_name,'.') dot from global_name);
set sqlprompt '&amp;gname&gt; '

alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss'
/
var sid number
var serial# number
var tracefile VARCHAR2(200)

DECLARE
 v_audsid v$session.audsid%TYPE;
BEGIN
 SELECT sid, serial#, audsid
 INTO :sid, :serial#, v_audsid
 FROM v$session
 WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID');

 SELECT par.value ||
 CASE WHEN par.value LIKE '%/%' THEN '/' ELSE '\' END ||
 LOWER(th.instance) ||
 '_ora_' || LTRIM(TO_CHAR(pro.spid,'fm99999')) || '.trc' AS filename
 INTO :tracefile
 FROM v$process pro
 , v$session se
 , v$parameter par
 , v$thread th
 WHERE se.audsid = v_audsid
 AND pro.addr = se.paddr
 AND par.NAME = 'user_dump_dest';
END;
/

BEGIN
 IF :sid IS NULL THEN
 SELECT sid
 INTO :sid
 FROM v$mystat
 WHERE rownum = 1;
 END IF;
END;
/

set termout on
set feedback off
exec DBMS_OUTPUT.PUT_LINE('Sessie: ' || :sid || CASE WHEN :serial# IS NULL THEN ' (no access to V$ tables)' ELSE ',' || :serial# END)
exec IF :tracefile IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('Eventueel trace-bestand: ' || :tracefile); END IF
prompt
set feedback on
</code></pre>

blocks|key|1237178|text|您可以使用rlwrap向sqlplus添加readline支持。像这样运行sqlplus：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1237179|$+rlwrap+-c+sqlplus+username@database|code-block|syntax|javascript|1237180|现在，向上/向下滚动查看命令历史记录。使用ctrl-r向后搜索历史，等等。这使得sqlplus可以接受。|1237181|此外，将此代码添加到您的login.sql中，以将线条大小设置为您的终端的宽度：|1237182|HOST+echo+"set+linesize"+$(stty+-a%7Chead+-n1%7Ccut+-f7+-d'+'%7Ccut+-f1+-d';')+>+.tmp.sql
@.tmp.sql
HOST+rm+-f+.tmp.sql|1237183|这两个技巧只在unix上有效。|1237184|entityMap|0|LINK|mutability|MUTABLE|url|http://freshmeat.net/projects/rlwrap/^0|5|6|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|C|$]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|C|$]]|$1|M|3|N|5|F|7|15|8|@]|9|@]|C|$G|H]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

You can use <a href="http://freshmeat.net/projects/rlwrap/" rel="noreferrer">rlwrap</a> to add readline support to sqlplus. Run sqlplus like this:

<pre><code>$ rlwrap -c sqlplus username@database
</code></pre>

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 is:

<pre><code>HOST echo "set linesize" $(stty -a|head -n1|cut -f7 -d' '|cut -f1 -d';') &gt; .tmp.sql
@.tmp.sql
HOST rm -f .tmp.sql
</code></pre>

Both of these tips only work on unix.

So many times I just need a quick connection to an Oracle DB, where SQLPLUS handles the job.

I imagine when people start using Oracle, the first thing they are told to do is to install Toad or SQLDeveloper. Even so, sometimes you don't want to wait for those tools to load, if you are performing some simple queries.

I have a script that I run when I start my shell so I get a better experience:

<pre><code>SET pagesize 2000
SET LONG 10000
SET linesize 1000
COLUMN last_name format a20
COLUMN total format 999,999,999
SET feedback ON
alter session set nls_date_format = 'yyyy-mm-dd hh:mi:ssPM';
</code></pre>

I trimmed my "COLUMN" settings for this example, but basically, that helps the data fit on the screen.

Setting the date format really simplifies dealing with dates.

When the command window opens in Windows, I set the window layout properties so I can scroll, have a wider window, etc. and save the settings for future windows.

Does anybody else use SQL*Plus daily? Any tips?

Favorite SQL*Plus tips and tricks

数据库

Windows

很多时候，我只需要快速连接到Oracle DB，SQLPLUS就可以处理这项工作。我想，当人们开始使用甲骨文时，他们被告知要做的第一件事就是安装Toad或SQLDeveloper。即使这样，如果您正在执行一些简单的查询，有时您也不想等待这些工具加载。我有一个在启动shell时运行的脚本，这样我就可以获得更好的体验：SE...

问最受欢迎的SQL*Plus提示和技巧
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问最受欢迎的SQL*Plus提示和技巧EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问最受欢迎的SQL*Plus提示和技巧
EN