Skip to content
Tutorials Center
My learning

Topics

Lesson 2 of 38 ยท Getting Started

Oracle Tools and Setup

Install Oracle Database Free, connect with SQL*Plus, SQLcl or SQL Developer, create your first user, and learn the commands every Oracle session needs.

To follow the Oracle-only parts of this course on your own machine, you need two things: a database to talk to and a client to talk with. Both are free. This lesson shows the common choices, how to connect, and a handful of client commands that you will use in every session.

The code on this page is read-only because it talks to your operating system and your database, not to our code runner. The last two examples are runnable stand-ins that mimic what a tool such as DESCRIBE shows.

Choosing a database

Option Best for Notes
Oracle Database Free (container) Learning on your PC Full 23ai engine, free limits (2 CPU threads, 2 GB RAM, 12 GB of user data)
Oracle Database Free (installer) Windows or Linux without Docker RPM or Windows installer from Oracle
Oracle Live SQL Quick experiments in the browser Needs a free Oracle account
Autonomous Database Always Free Cloud practice, APEX Free tier on Oracle Cloud

The container is the quickest route. With Docker installed, one command starts a database:

Terminal
docker run -d --name oracle-free -p 1521:1521 \
  -e ORACLE_PASSWORD=choose-a-password \
  gvenzl/oracle-free:23

The first start takes a minute or two while the database is created. Watch the log with docker logs -f oracle-free and wait for the line DATABASE IS READY TO USE!. Oracle also publishes an official image at container-registry.oracle.com/database/free.

Choosing a client

Oracle databases listen on port 1521. A client connects with a user name, a password and a connect string such as localhost:1521/FREEPDB1. This form is called Easy Connect.

  • SQL*Plus is the classic command-line client. It is small, scripted easily and installed with the database.
  • SQLcl is a modern command-line client with history, tab completion and formatting. It runs on Java.
  • SQL Developer is a free graphical tool with a schema browser, query editor and PL/SQL debugger.
  • VS Code with the Oracle SQL Developer extension gives you the same inside an editor.

Connect from a terminal:

Terminal
sqlplus system/choose-a-password@localhost:1521/FREEPDB1

You should see the banner Connected to: Oracle Database 23ai Free followed by the prompt SQL>.

Create your own user

Never practice as SYSTEM or SYS. Create a normal user for yourself, which also creates a schema:

SQL
CREATE USER learner IDENTIFIED BY "ChangeMe_123"
  DEFAULT TABLESPACE users
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW,
      CREATE SEQUENCE, CREATE PROCEDURE, CREATE TRIGGER TO learner;

Or use the shortcut role DB_DEVELOPER_ROLE, added in 23ai, which bundles the common developer privileges:

SQL
GRANT DB_DEVELOPER_ROLE TO learner;

Reconnect as the new user: sqlplus learner/ChangeMe_123@localhost:1521/FREEPDB1. Everything you create from now on lives in the LEARNER schema.

Client commands you will use every day

Some commands belong to the client, not to the SQL language. They are not sent to the server and need no semicolon.

SQL
SET SERVEROUTPUT ON      -- show DBMS_OUTPUT.PUT_LINE text
SET LINESIZE 120         -- wider lines
SET PAGESIZE 50          -- header every 50 rows
COLUMN name FORMAT A20   -- column width for text
DESCRIBE employees       -- list the columns of a table
SHOW USER                -- who am I?
SHOW ERRORS              -- compile errors for the last PL/SQL object

A SQL statement ends with ;. A PL/SQL block ends with / on its own line, because the semicolon inside a block belongs to the block's statements. We come back to this in PL/SQL blocks and variables.

Useful SQL queries about yourself and your objects use the data dictionary, a set of read-only views:

SQL
SELECT table_name FROM user_tables;                -- tables you own
SELECT column_name, data_type FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';                    -- columns of one table
SELECT object_name, object_type, status FROM user_objects;

Views starting with USER_ show what you own, ALL_ shows what you can access, and DBA_ shows everything (for administrators). Oracle stores unquoted names in upper case, which is why the table name above is written 'EMPLOYEES'.

The sample HR schema

Oracle ships a small practice schema called HR with employees, departments, jobs and locations tables. You can install it from Oracle's db-sample-schemas repository. This course uses its own, simpler tables so that every portable query can run in the browser, but the same ideas apply to HR.

Stand-ins you can run

DESCRIBE employees lists the columns of a table. SQLite has no DESCRIBE, but a table-valued pragma gives the same information:

SQL Stand-in for DESCRIBE ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

And SELECT table_name FROM user_tables becomes a query on SQLite's catalog:

SQL Stand-in for USER_TABLES ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Connection troubleshooting

  • ORA-12541: TNS:no listener means the database is not running or the port is wrong. Check docker ps.
  • ORA-01017: invalid username/password is a typo, or the password is case-sensitive and was typed differently.
  • ORA-12514 means the service name (FREEPDB1) is wrong. Connect to the pluggable database, not to the container root.
  • ORA-28000: the account is locked after too many failed logins: ask an administrator to run ALTER USER learner ACCOUNT UNLOCK;.

Error codes always read ORA- followed by five digits. Searching for the code, for example "ORA-12541", is the fastest way to find the cause.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which port does an Oracle listener use by default?

    Oracle's listener uses 1521 by default. 3306 is MySQL, 5432 is PostgreSQL and 1433 is SQL Server.
  2. 2. Why should you create your own user instead of practicing as SYSTEM?

    Administrative accounts have wide privileges. A normal user limits the damage of a mistake and gives you a clean schema.
  3. 3. What does SET SERVEROUTPUT ON do?

    It is a client command. Without it, PL/SQL output is produced but not shown.
  4. 4. How do you find the tables owned by the current user?

    USER_TABLES is a data dictionary view listing tables you own. SHOW TABLES is MySQL syntax.

Summary

  • Oracle Database Free in a container is the easiest way to get a real 23ai database.
  • Clients: SQL*Plus and SQLcl (command line), SQL Developer (graphical).
  • Connect with user/password@host:1521/FREEPDB1 and practice in your own schema.
  • Client commands such as SET SERVEROUTPUT ON and DESCRIBE run in the tool; SQL ends with ;, PL/SQL blocks end with /.
  • The USER_, ALL_ and DBA_ dictionary views describe your database; look up any ORA- code when something fails.

Next: Oracle Data Types