How to Setup Sqlplus for Most Efficient Use on Windows. How to Setup Sqlplus for Most Efficient Use on Windows. Sql Ed Wrote File Afiedt.Buf' title='Sql Ed Wrote File Afiedt.Buf' />Sqlplus command line or console version is actually more powerful and efficient than its Windows or GUI version. The feature that brings the greatest convenience is probably being able to press UP key to recall previous commands. With Oracle. 11g, the Windows version is no longer available, making the console version more than just appealing, but essential. This article summarizes some tips you may find useful in using the console version. I am trying to delete a lot of rows from a table. I want to try the approach of putting rows I want to delete into a cursor and then keep doing fetch, delete, commit. Is it possible to somehow do this WITH T1 AS SELECT 1 AS SEQ, NOTHING 1 AS SOMETYPE FROM DUAL UNION ALL SELECT 2 AS SEQ, NOTHING 2 AS SOMETYPE FROM DUAL. How to Setup Sqlplus for Most Efficient Use on Windows. Sqlplus command line or console version is actually more powerful and efficient than its Windows or GUI. Whilst you are here, check out some content from the AskTom team Optimizing the PLSQL Challenge V The Danger of Too Many Indexes. I assume your desktop OS runs Windows 7 unless otherwise noted. Create a shortcut in Start menu. In Windows Explorer, at C UsersuserApp. DataRoamingMicrosoftWindowsStart MenuPrograms where user is you or All Users you need to manually type App. Data in the path since its hidden, create a shortcut named for instance Sqlplus pointing to oraclehomebinsqlplus. I use oraclehome notation to refer to your oracle home folder. Also note Windows XP uses a different path, C Documents and SettingsuserStart Menu. So the shortcut target looks like this. Db2binsqlplus. Because of cmd k, if you exit sqlplus, sometimes accidentally by C, the console window remains open instead of closing itself. So you can still see the result on screen. T10h60sUNk/U2JO1AVdBZI/AAAAAAAAADg/kIOVBVCPrkI/s1600/PLSQL.png' alt='Sql Ed Wrote File Afiedt.Buf' title='Sql Ed Wrote File Afiedt.Buf' />If for any reason you need to set oraclehome and oraclesid just for running this sqlplus, i. Control Panel, the command will be cmd k set oraclehomed oracleproduct1. If you think too many environment variables should be set, put them in a batch file and call the file on this command instead. For instance, c 1. I normally set Start in folder to somewhere other than the default oraclehomebin, such as d temp, so a spooled file goes there by default. Once this shortcut is there, you can quickly launch Sqlplus by going to Start Sqlplus. Can the system tablespace be rebuilt with damage to the database offline. Ouch looks like someone let people create some really big things in system. Denver Pumpkin Patch Chatfield. If you want to launch it even faster, set a shortcut key for it. Adjust shortcut properties. Screen colors are probably the most important, ergonomically speaking. In Properties window for this shortcut, go to Colors tab, I strongly suggest you choose black for Screen Text and very light color or white for Screen Background. Trust me on this adviceDo an experiment with two console windows, one with white text in black background and the other with black text in white or light color background see below. Make sure text font is exactly the same in size and face. Sql Ed Wrote File Afiedt.Buf' title='Sql Ed Wrote File Afiedt.Buf' />Stand some distance away. Youll find that the text in the second window looks much clearer and you can read faster. I think the reason is that white text lines have more visible tiny black gaps than black text lines have white gaps. Eye strain test. c C WINDOWSSystem. C WINDOWSSystem. D od x D oracleproduct1. D od x D oracleproduct1. Dont be discouraged by only 1. You can adjust Red, Green and Blue to fairly high numbers to get your favorite light color, such as 2. Now go to Options tab. Check Quick. Edit mode and Insert mode checkboxes. It makes copy and paste of text easier. If you see somebody send an image of Sqlplus text, you know heshe is very computer un savvy. Under Font, choose a small font but not too small to your vision. I like Lucida Console, and not Bold version of it. Under Layout, set Width for both Screen buffer and Window sizes to the same number, I would say at least 1. On my 1. 9 inch monitor using 1. Lucida Console font, I can get 1. Set Height for Screen Buffer to 2. The reason you want the Sqlplus screen as wide as you feel comfortable is so that youll have less cluster in the query output. Sqlplus setting. Default pagesize 1. I prefer 1. 00 except in case of very wide tables such as some Oracle Applications AOL tables, where select repeats headers too often even with pagesize 1. Since we widened our sqlplus console window in the last step to, say, 1. But due to a problem with console window,note. The only time you want a smaller linesize is when you DESCribe a table or view, so that column names and types are not too far apart in DESC output. Interestingly, this DESCribe feature is not for DESCribing a PLSQL package or procedure. But typing these set commands every time you start Sqlplus would be tedious, even with abbreviations such as set pages 1. So add them to oraclehomesqlplusadminglogin. R2, see 2. 24. 10. If one or both of them are already in there, change or delete them. Other useful settings are note. With the above window width and Sqlplus linesize setting, query column values for the same row need to be vertically aligned with the column headers and dash lines, as in the case of select from awidetable. Because Sqlplus uses tabs for fast display, some column headings or even values may not align very well with dash lines under the headings. Set tab off to correct the problem. If you really prefer to see one data row shown on one row, the preferred tool is Toad or any one under Toad Alternatives. But Sqlplus is not completely hopeless you can set both window width and Sqlplus linesize to a very big number like 1. Environment variables. Environment variables on Windows are case insensitive and their values generally are so too. The following are commonly used environment variables that affect the behavior of Sqlplus, which can also be set in Windows registry. But if theyre set in both places, environment variables take precedence. If you need to find the current value of a variable regardless where its set, you can type thevariablename at SQL prompt to see it outputs the literal string you typed which means its not set or its value when its set try oraclehome and nosuchvar to see what I mean. Here the percent sign is literal type it as is. For a fairly complete list of the variables, see Radoslav Rusinovs article see pp. Google Translate. LOCAL Windows equivalent of UNIX TWOTASK environment variable. With this set, sqlplus userpass is translated to sqlplus userpassvalueofLOCAL. NLSDATEFORMAT I like to set it to yyyymmdd hh. DATE type columns are displayed in my favorite format. The console command is set nlsdateformatyyyymmdd hh. Yes, there is a space between dd and hh. NOT interpreted by set as two arguments Setting this environment variable is preferred over adding alter session set nlsdateformatyyyymmdd hh. Session altered feedback upon login, and as a bonus, if you use RMAN from this console window, timestamp in RMAN shows in this format too. So you may consider setting it in Control Panel as a system wide variable. See also NLSLANG below. NLSLANG If your PC uses code page 4. US8. PC4. 37, e. g. US8. PC4. 37 note the leading dot or americanamerica. Oracle Character Sets for Console Mode OEM Code Pages. If this is not set here, its value may be AMERICANAMERICA. WE8. MSWIN1. 25. 2 set in registry by the default installation, which is only good for GUI not console applications. You may temporarily set NLSLANG to a value not recommended by Oracle in case you wish to read command feedback or error message in a language you know, e. But the easier alter session set nlslanguageenglish would at least get the error message in English. Before running imp or exp but not expdp or impdp, this variable is also set, to the same as the source database character set to avoid character set conversion, even if the source and target databases use different character sets Ref.