There’s a nice little touch to the “set feedback” command in SQL PLus in 12.2. There is a new “only” clause in the SET FEEDBACK command, so you can run queries but the resulting rows are not shown in the display. They are still queried, fetched and “returned”, but just rendered to the screen. Here’s an example
$ sqlplus hr/hr SQL*Plus: Release 220.127.116.11.0 Production on Tue Mar 14 22:59:15 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sat Mar 11 2017 01:59:20 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 - 64bit Production SQL> select * from regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa
So that’s normal behaviour. Let’s now use the new ONLY option.
If you use a login.sql script to set the SQL*Plus environment from your current working directory, you will see that it will not run anymore in 12.2. This is a security feature, and a good occasion to explain how sqlplus finds the scritps to run, on Linux.
For my test I have login.sql, LOGIN.SQL and script.sql in the following directories
$ tree /tmp/mytest/
│ ├── login.sql
│ ├── LOGIN.SQL
│ └── script.sqlL
│ ├── login.sql
│ ├── LOGIN.SQL
│ └── script.sql
I’m going to the parent directory
The scripts display their name:
+ head login.sql LOGIN.SQL script.sql
==> login.sql LOGIN.SQL script.sql <==
prompt Hello from /tmp/mytest/script.sql
One of the developers asked me to copy a small table from Live to Dev. In situations like this, my first thought is to use the SQL*Plus COPY command. By the way, this command is also available in SQLcl.
Oracle DBAs who are so old that they remember the days before Oracle 11.2 probably remember the tuning efforts for latches. I can still recall the latch number for cache buffers chains from the top of my head: number 98. In the older days this was another number, 157.
But it seems latches have become less of a problem in the modern days of Oracle 11.2 and higher. Still, when I generate heavy concurrency I can see some latch waits. (I am talking about you and SLOB mister Closson).
I decided to look into latches on Oracle 22.214.171.124 instance on Oracle Linux 7. This might also be a good time to go through how you think they work for yourself, it might be different than you think or have been taught.
I finally managed to work around My Most Annoying Problem (TM) with SQLPlus in my career. SQLPlus will mess up my output when I am copying/posting something in wordpress or elsewhere. Here’s an example to show you what I mean. The output in SQLPlus in my terminal window is all nice and pretty (it’s a print screen):
Many thanks to Jonathan Lewis and his presentation about generating test data by the way!
The above output looks nice at first glance, so I’m copying and pasting this into a document, only to see this (again a print screen; this time from TextWrangler):
Recently I am involved in a project which requires a lot of data to be extracted from Oracle. The size of the data was so huge that the filesystems filled up. Compressing the output (using tar j (bzip2) or z (gzip)) is an obvious solution, but this can only be done after the files are created. This is why I proposed compressing the output without ever existing in uncompressed form.
This solution works with a so called ‘named pipe’, which is something for which I know for sure it can be done on Linux and unix. A named pipe has the ability to let two processes transfer data between each other. This solution will look familiar to “older” Oracle DBA’s: this was how exports where compressed from the “original” export utility (exp).
I’ve created a small script which calls sqlplus embedded in it, and executes sqlplus commands using a “here command”:
This post is nothing new, and I created it after a little discussion on twitter about how to use readline support in SQL*Plus. The idea is not new, and I have compiled and used rlwrap for quite some time.
At the time, Frits Hoogland asked me why I didn’t use the EPEL package-and I had to admit to myself that I didn’t know the Extra Package for Enterprise Linux repository at all. But there is more to rlwrap and Linux I didn’t know, but first things first.
Installing rlwrap from EPEL
This is really simple-you can either add the EPEL repository to your /etc/yum.repos.d/ directory or simply download the rlwrap package and install it via RPM. A simple wget on your host does the trick. You can set environment variables when you’d like to use a proxy as shown here: