Last week, I suddenly had to work with an Oracle database again. I normally use Intellij’s DataGrip to connect to databases. I tried it this time, and I found I could not connect to the schema I wanted: the schema just turned up empty. Of course, everybody will recommend you use Oracle’s SQL Developer with any Oracle database you have to touch. So, after trying
brew search sqldeveloper (yes, I’m on a Mac at work), coming up empty, reading this cask request and feeling the anticipation of endless frustration grow inside me, I went to Oracle’s web site to see if I could download the program. I can, except that they want me to turn in a DNA sample first:
Of course, faking those kind of details is not impossible, but the hassle of going through something like that just for a lousy program so I can just run a couple of lousy queries puts me off.
Luckily, I had a Docker container with Oracle XE lying around. It includes SQL*Plus, the “venerable” command-line tool provided by Oracle to query and administer Oracle databases. Being a Arch Linux user with a predilection for anything with a command-line interface, it was not too bad, but getting the output formatted so that I could actually make sense of it required more effort than most users are willing to put up with.
So how do you find your way around when you have SQL*Plus and no clue how it works? How do you find the schema’s you want, the tables you need to query? How can you check your privileges?
This post aims to be a cheatsheet so you can find your way around. Keep in mind that the results you’re getting depend on the privileges your user has. Getting an empty result does not mean that the database object does not exist. Rather, it means you’re a lesser god.
Getting Help With
sqlplus does not have a man page, but provides help when you pass
$ sqlplus -h
Connecting to an Oracle Database Using SQL*Plus
The basic syntax to connect as user
alice with password
qwerty to a database
FOO which is located on
db.domain.tld and listens on port
1521 (default port) is:
$ sqlplus email@example.com:1521/FOO
Show the Connected User
SHOW command lets you look at the current state of your SQLPlus environment:
SHOW ALL shows all settings.
Show All Schema’s
SELECT username FROM dba_users u;
Return only non-empty schema’s (excluding most users who never created any object in the database):
SELECT username FROM dba_users u WHERE EXISTS (-- filter users without database objects SELECT 1 FROM dba_objects o WHERE o.owner = u.username);
Excluding Oracle’s built-in schema’s:
SELECT username FROM dba_users WHERE default_tablespace NOT IN ('SYSTEM', 'SYSAUX');
Show All Tables/Views in a Particular Schema
SELECT table_name FROM all_tables WHERE owner = 'MYSCHEMA';
Related: find all views:
SELECT view_name, text FROM all_views WHERE owner = 'MYSCHEMA';
Describe a Table
Show DDL of a Table
SELECT dbms_metadata.get_ddl('VIEW', 'USERS', 'MYSCHEMA') FROM dual;
where the first argument is the type of object (e.g.
'TRIGGER'), the second is the name of the object, and the third the schema where the object is defined.
Show the Privileges of Your User
SELECT * FROM USER_SYS_PRIVS; SELECT * FROM USER_TAB_PRIVS; SELECT * FROM USER_ROLE_PRIVS;
Get More Decent Output From a Command
If you want SQL*Plus to truncate the value of a column:
SET WRAP OFF;
otherwise it will allow the column to wrap to the next line (default
Suppress all headings, page breaks, titles, the initial blank line and other formatting information:
SET PAGESIZE 0;
Now, if only Oracle would support
\G for vertical output…
Get and Alter Database Timezones
SELECT dbtimezone FROM dual; ALTER DATABASE SET dbtimezone = 'Europe/Amsterdam';
SELECT sessiontimezone FROM dual; ALTER SESSION SET sessiontimezone = 'Europe/Amsterdam';
DATE Rows in the Last Hour
t has a column
c_date of type
SELECT * FROM table t WHERE c_date > sysdate - 1/24;
This works because you can subtract a fraction from a
DATE type where the fraction is interpreted as a fraction of a day (
1 is an entire day,
0.5 is 12 hours, etc.).
Working Around Oracle Not Having a
Yes, Oracle does not have a
LIMIT keyword, so this idiom will quickly become ingrained in your muscle memory:
SELECT * FROM (-- actual query SELECT firstName, lastName FROM users) WHERE ROWNUM <= 10;
For each row returned by a query, the
ROWNUMpseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a
1, the second has
2, and so on.
You could do without the subquery, plainly writing:
SELECT firstName, lastName FROM users WHERE ROWNUM <= 10;
but if you add a
ORDER BY clause, Oracle will first select the first ten rows, and only then apply the
ORDER BY clause, which might not be what you want. So that’s why it’s best to always use the first idiom above.