ORA-01017 on Test in the New/Select Database Connection dialog: SID vs service name and thin vs OCI for a credential-free XE check

Answer

For a local Oracle XE smoke test using SQL Developer’s Test button, start with the thin JDBC driver and the service name "XE". This works out‑of‑the‑box for a default XE installation because the listener registers that service name and no Oracle client is required.

When SID or OCI might be needed

• If the listener is configured only with a SID (e.g., "XE") and does not register a service name, specifying the service name will fail with an ORA‑12505 listener error before authentication. In that case use the SID in the connect descriptor with the thin driver; the Test will succeed.
• If you prefer or require the OCI (thick) driver—for example to use a wallet or advanced security features—you must have a matching Oracle Instant Client installed and a correct tnsnames.ora or easy‑connect string. Without the client the Test fails with ORA‑12154; with the client it can succeed using either SID or service name.

Interpreting ORA‑01017 from the Test button

The Test button returns ORA‑01017 only when the database rejects the username/password pair. Distinct errors are raised for case‑sensitivity problems (ORA‑28000) and for expired or locked accounts (ORA‑28001). Therefore a plain ORA‑01017 most reliably indicates an invalid credential rather than a case or status issue. To confirm, reset the password or try a known good pair; if the error changes to ORA‑28000/28001 you have identified the specific cause.

Minimal privilege check beyond Test

1. Create a throwaway schema in XE that mirrors the low‑privilege account you intend to use.
2. Grant exactly the privileges your integration requires (e.g., SELECT on specific tables, EXECUTE on procedures).
3. Open a SQL Worksheet, connect as that test user, and run the actual SQL/DML/DDL statements your integration will execute.
4. If the statements complete without ORA‑01031 (insufficient privileges), the privilege set is sufficient. Any ORA‑01031 indicates a missing grant.

Missing diagnostic detail

To decide definitively between service name and SID, verify whether your XE listener registers the service name "XE". Run lsnrctl status on the host and look for "Service \"XE\"" in the output. If the service name is present, stay with service name + thin driver; if only a SID appears, switch to SID + thin driver.