A Toad for Oracle Alternative
That Runs on Mac, Windows and Linux

A reproducible Oracle workflow: connect, query, plan, session, debug

Every step below uses the same Oracle Database Free 23ai container, service name and grants that Jam SQL Studio’s own Oracle end-to-end tests run against. The SQL is inline so you can repeat it. The seed script writes to a database — one table, 1,000 generated rows and one procedure — so run it against a throwaway container or a dedicated sample schema, never a production database. A cleanup script is at the end.

1. Start an Oracle you can throw away

Oracle Database Free needs no Oracle account. First start takes two to five minutes while the database initialises.

docker run -d --name oracle-free -p 1521:1521 \
  -e ORACLE_PASSWORD=Oracle123 \
  -e APP_USER=jamsql -e APP_USER_PASSWORD=JamSql123 \
  gvenzl/oracle-free:latest

# watch until the log prints DATABASE IS READY TO USE
docker logs -f oracle-free

That container exposes two service names: FREEPDB1 is the pluggable database your data lives in, and FREE is the container database for admin work. Use FREEPDB1.

2. Grant the privileges each step needs

Oracle gates plans, session monitoring and debugging behind separate privileges, and none of them are implied by CONNECT. Run this as SYSTEM against FREEPDB1. These statements change privileges on the target database.

-- metadata for the Object Explorer and IntelliSense
GRANT SELECT ANY DICTIONARY TO jamsql;

-- execution plans (estimated and actual)
GRANT SELECT ON SYS.V_$SQL                        TO jamsql;
GRANT SELECT ON SYS.V_$SQL_PLAN_STATISTICS_ALL    TO jamsql;
GRANT EXECUTE ON SYS.DBMS_XPLAN                   TO jamsql;

-- Session Browser
GRANT SELECT ON SYS.V_$SESSION                    TO jamsql;

-- PL/SQL debugger
GRANT DEBUG CONNECT SESSION                       TO jamsql;
GRANT DEBUG ANY PROCEDURE                         TO jamsql;
GRANT EXECUTE ON SYS.DBMS_DEBUG                   TO jamsql;

If a debug privilege is missing, Jam SQL Studio checks for it when the debug session starts and puts the exact GRANT statements in the error message rather than failing with an ORA number.

3. Connect

Click New Connection, pick the Oracle tile, and fill in server localhost, port 1521, service name FREEPDB1, login jamsql, password JamSql123. If you are not sure of the service name on a real server, click Discover and pick from the names the listener actually answers to. Already have your databases in a tnsnames.ora? Import every alias in one pass instead of retyping them — connection import also reads DBeaver, DataGrip, SSMS, TablePlus and pgAdmin.

4. Seed a table and read an execution plan

Run this as jamsql. It creates one table and 1,000 rows.

CREATE TABLE demo_orders (
  order_id  NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer  VARCHAR2(60)  NOT NULL,
  amount    NUMBER(10,2)  NOT NULL,
  placed_at DATE DEFAULT SYSDATE NOT NULL
);

INSERT INTO demo_orders (customer, amount, placed_at)
SELECT 'CUST-' || TO_CHAR(MOD(LEVEL, 50), 'FM000'),
       ROUND(DBMS_RANDOM.VALUE(5, 500), 2),
       SYSDATE - MOD(LEVEL, 90)
FROM   dual
CONNECT BY LEVEL <= 1000;

COMMIT;

Then run the query you actually want to understand:

SELECT customer, COUNT(*) AS orders, SUM(amount) AS total
FROM   demo_orders
WHERE  placed_at >= SYSDATE - 30
GROUP  BY customer
ORDER  BY total DESC
FETCH FIRST 10 ROWS ONLY;

Estimated plan: Plan → Explain plan in the query editor toolbar runs EXPLAIN PLAN FOR, reads the rows back from PLAN_TABLE (operation, options, object, cost, cardinality, bytes, CPU and I/O cost), then deletes its own statement id. Nothing executes.

Actual plan: executing with the actual plan prepends /*+ GATHER_PLAN_STATISTICS */ and reads runtime figures from V$SQL_PLAN_STATISTICS_ALL — Starts, E-Rows against A-Rows, A-Time, Buffers. This is deliberately limited to read-only statements: Jam SQL Studio will not run a DML statement just to collect an actual plan. Both render in the same tree and graph views used for SQL Server, PostgreSQL and MySQL, and both export to a file (.oracle.plan.json / .oracle.plan-actual.json) so you can reopen or compare a plan later.

5. Watch a session while it runs

Open a second query tab and start something slow enough to catch:

BEGIN
  DBMS_SESSION.SLEEP(60);
END;

Now open More → DBA → Session Browser from the main toolbar and pick the connection. On Oracle the grid is built from V$SESSION: one row per session with SID, user, status, CPU, reads and writes, current command and wait event. Click a row for the full SQL text, the locks it holds with the blocked/blocking chain, its wait events, and — Oracle only — its open cursors. Kill Session issues ALTER SYSTEM KILL SESSION behind a confirmation dialog that shows you the session id, user, database and the SQL you are about to stop.

6. Step through PL/SQL

Create something worth stepping into:

CREATE OR REPLACE PROCEDURE demo_month_total (
  p_customer IN  VARCHAR2,
  p_total    OUT NUMBER
) AS
  v_count NUMBER := 0;
BEGIN
  SELECT COUNT(*), NVL(SUM(amount), 0)
  INTO   v_count, p_total
  FROM   demo_orders
  WHERE  customer = p_customer
  AND    placed_at >= SYSDATE - 30;

  IF v_count = 0 THEN
    p_total := 0;
  END IF;
END;

Right-click the procedure in the Object Explorer and choose Compile for Debug (that runs ALTER … COMPILE DEBUG; without it Oracle has no line map and breakpoints never bind). Choose Open to load the source, click the gutter next to the SELECT to set a breakpoint, then click Start Debug. A dialog appears pre-filled with a call block — BEGIN JAMSQL.DEMO_MONTH_TOTAL(); END; — edit the parameters and start.

Execution pauses on the highlighted line. The Variables panel splits into parameters, locals and package variables with their current values and types; Call Stack lists OWNER.PROGRAM line N frames and clicking one loads that frame's source and variables; Watch evaluates arbitrary expressions (v_count * 2, LENGTH(p_customer), record fields, collection elements) and re-evaluates after every step; Exceptions breaks on all exceptions or on named ones such as NO_DATA_FOUND. Anything written with DBMS_OUTPUT.PUT_LINE is captured and shown in Messages when the run ends.

To debug ad-hoc instead, write an anonymous block in the Query Editor and use the bug icon — it appears when Oracle is connected and the text starts with BEGIN or DECLARE. It does not appear for a CREATE… statement, and that is a real Oracle constraint rather than a gap: the debugger runs an anonymous block, and Oracle rejects a CREATE nested inside one. Run the CREATE OR REPLACE first, then step into the compiled routine.

DECLARE
  v_total NUMBER;
BEGIN
  demo_month_total('CUST-007', v_total);
  DBMS_OUTPUT.PUT_LINE('total = ' || v_total);
END;

Clean up

DROP PROCEDURE demo_month_total;
DROP TABLE demo_orders PURGE;

docker rm -f oracle-free

Why Developers Switch from Toad

Toad for Oracle is a long-established tool with far more Oracle-specific depth. These are the reasons an Oracle developer might pick Jam SQL Studio instead.