Relentless Coding

A Developer’s Blog

Oracle Sqlplus Cheat Sheet

In this post, I’m going to aggregate all those Oracle commands that I can never remember but are very useful to have somewhere written down.

Table of Contents

Intro

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:

Oracle’s create-new-account page

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 cheat sheet 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

sqlplus does not have a man page, but provides help when you pass -h/-help:

$ 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 alice/qwerty@db.domain.tld:1521/FOO

Show the Connected User

SHOW USER;

The SHOW command lets you look at the current state of your SQL*Plus 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');

Source

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

DESCRIBE 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. 'TABLE', 'VIEW', '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;

Source

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 ON). Suppress all headings, page breaks, titles, the initial blank line and other formatting information:

SET PAGESIZE 0;

Source 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';

Select DATE Rows in the Last Hour

Table t has a column c_date of type DATE:

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 LIMIT

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 ROWNUM pseudocolumn 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 ROWNUM of 1, the second has 2, and so on. — Oracle documentation

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.

Setting New Password for User

ALTER USER user_name IDENTIFIED BY new_password;

Show Output from Script

When writing a script, you may want to output some diagnostic data:

BEGIN
    EXECUTE IMMEDIATE 'GRANT INSERT ON my.table TO role';
EXCEPTION
    WHEN OTHERS
        THEN
            IF SQLCODE = -942
            THEN
                DBMS_OUTPUT.PUT_LINE('Table does not exist, buddy');
            ELSE
                RAISE;
            ENDIF;
END;
/

You think you’re good to go, but when you execute your script in an environment where my.table does not exist, you don’t see the diagnostic message. What gives? SQL*Plus’s default behavior is to suppress output by default. You have to SET SERVEROUTPUT ON first.