Can we use tnsping for Oracle Autonomous 26ai database

tnsping Not Working on macOS (zsh) – Connecting to Oracle 26ai Autonomous Database Without It

Recently, while working on Oracle Autonomous Database 26ai, I encountered the following error on macOS:

zsh: command not found: tnsping

At first, it looked like something was missing from my Oracle client installation. However, the actual answer is that you don’t need tnsping when using SQLcl to connect to Autonomous Database.

Instead, you can connect directly using the wallet configuration.

Step 1: Set the Wallet Location

Export the wallet directory so SQLcl knows where to find the connection configuration.

export TNS_ADMIN=/Users/karandodwal/git/code/oracle/wallet

Step 2: Required Wallet Files

Your wallet directory should contain files similar to the following:

(base) karandodwal@Karans-MacBook-Air wallet % ls -ltr
total 152
-rw-r--r--@ 1 karandodwal staff 3021 22 Mar 17:33 truststore.jks
-rw-r--r--@ 1 karandodwal staff 1245 22 Mar 17:33 tnsnames.ora
-rw-r--r--@ 1 karandodwal staff 114 22 Mar 17:33 sqlnet.ora
-rw-r--r--@ 1 karandodwal staff 3030 22 Mar 17:33 README
-rw-r--r--@ 1 karandodwal staff 691 22 Mar 17:33 ojdbc.properties
-rw-r--r--@ 1 karandodwal staff 3191 22 Mar 17:33 keystore.jks
-rw-r--r--@ 1 karandodwal staff 7097 22 Mar 17:33 ewallet.pem
-rw-r--r--@ 1 karandodwal staff 6312 22 Mar 17:33 ewallet.p12
-rw-r--r--@ 1 karandodwal staff 6357 22 Mar 17:33 cwallet.sso
-rw-r--r--@ 1 karandodwal staff 25332 22 Mar 23:20 Wallet_PYDB.zip

These files are downloaded when you download the Autonomous Database Wallet from Oracle Cloud.

Step 3: Connect Using SQLcl

Once the wallet path is configured, simply run your connection command. In my case, I use an alias called atp.

(base) karandodwal@Karans-MacBook-Air ~ % atp

SQLcl: Release 24.4 Production on Sun Mar 22 23:28:13 2026
Copyright (c) 1982, 2026, Oracle. All rights reserved.

Last Successful login time: Sun Mar 22 2026 23:27:33 +05:30

Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.1.2.0 - Production
Version 23.26.1.2.0

Step 4: Verify the Connection

You can verify the pluggable database using:

SQL> show pdbs

CON_ID   CON_NAME                    OPEN MODE   RESTRICTED
------   --------------------------  ----------  ----------
600      RE2D3BGG7YWWTP7_PYDB       READ WRITE  NO

Key Takeaway

If you see:

zsh: command not found: tnsping

Don’t worry.

When working with Autonomous Database and SQLcl, you typically don’t need tnsping. As long as:

• The wallet is downloaded
• TNS_ADMIN is set correctly
• SQLcl is installed

You can connect directly.

This approach works well on macOS using zsh and is a simple way to connect to Autonomous Database using the wallet.

Performance Tuning with Subquery Factoring and Inline Views

Speed Up Complex Queries: Using Subquery Factoring and Inline Views in Oracle SQL

Overview: Discuss how subquery factoring (WITH) and inline views help break down complex logic, reduce repeated computations, and improve readability and performance. Touch on materialization hints if needed. Example Query:

WITH top_performers AS (
    SELECT employee_id, salary, department_id
    FROM employees
    WHERE salary > 10000
)
SELECT 
    d.department_name,
    tp.employee_id,
    tp.salary
FROM 
    top_performers tp
JOIN 
    departments d ON tp.department_id = d.department_id;

Use Case: Optimize queries that reuse subsets of data (e.g., high-salary employees) and reduce redundancy.

Querying and Analyzing JSON Data in Oracle SQL

Modern SQL: Querying JSON Data in Oracle Like a Pro

Overview: With more systems storing data in JSON format, Oracle's support for JSON functions is a game-changer. Introduce JSON_VALUE, JSON_TABLE, JSON_EXISTS, and how to use them in real scenarios. Example Query:

SQL> ed
  1  SELECT
  2      empno,
  3      ename,
  4      json_value(extra_info, '$.linkedin') AS linkedin_profile
  5  FROM
  6      emp
  7  WHERE
  8*     json_exists(extra_info, '$.linkedin');
SQL> /

Use Case: Extract social media profiles or preferences stored in a JSON column (e.g., extra_info) for analytics or personalization.

CTEs and WITH Clause for Clean, Reusable SQL

Write Cleaner SQL: Harnessing the WITH Clause (CTE) in Oracle

Overview: Benefits of CTEs: readability, reusability, complex nesting. Example Query:

SQL> ed
  1  WITH department_avg AS (
  2      SELECT deptno, AVG(sal) AS avg_salary
  3      FROM emp
  4      GROUP BY deptno
  5  )
  6  SELECT e.empno, e.sal, d.avg_salary
  7  FROM emp e
  8  JOIN department_avg d ON e.deptno = d.deptno
  9* WHERE e.sal > d.avg_salary;
SQL> /

   EMPNO     SAL                                   AVG_SALARY
________ _______ ____________________________________________
    7566    2975    2258.333333333333333333333333333333333333
    7698    2850                                         1800
    7788    3000    2258.333333333333333333333333333333333333
    7839    5000    2916.666666666666666666666666666666666667

Use Case: Identifying above-average performers.

Advanced Data Transformation Using PIVOT in Oracle SQL

From Rows to Columns: Using Oracle SQL PIVOT for Smarter Reports

Overview: Explain the PIVOT clause with aggregation. Example Query:

SQL> ed
  1  SELECT *
  2  FROM (
  3      SELECT deptno, job, sal
  4      FROM emp
  5  )
  6  PIVOT (
  7      SUM(sal)
  8      FOR job IN ('SALESMAN' AS SALES_REP, 'MANAGER' AS MGR)
  9* );
SQL> /

   DEPTNO    SALES_REP     MGR
_________ ____________ _______
       30         4350    2850
       10                 2450
       20                 2975

Use Case: Creating executive summary tables.

Unlocking the Power of Oracle Window Functions

Title: Ranking, Running Totals, and More: A Deep Dive into Oracle Window Functions

Overview: Cover RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD. Example Query:

SQL> ed
  1  SELECT
  2      deptno,
  3      empno,
  4      sal,
  5      RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS salary_rank
  6  FROM
  7*     emp
SQL> /

   DEPTNO    EMPNO     SAL    SALARY_RANK
_________ ________ _______ ______________
       10     7839    5000              1
       10     7782    2450              2
       10     7934    1300              3
       20     7788    3000              1
       20     7566    2975              2
       20     7369     800              3
       30     7698    2850              1
       30     7499    1600              2
       30     7844    1500              3
       30     7521    1250              4

10 rows selected.

Use Case: Finding top earners in each department.

Mastering Hierarchical Queries in Oracle SQL

Unraveling Tree Structures with Hierarchical Queries in Oracle SQL

Overview: Introduction to hierarchical data (e.g., employee-manager). Explain CONNECT BY, PRIOR, and LEVEL.

SQL> ed
  1  SELECT
  2      empno,
  3      ename,
  4      mgr,
  5      LEVEL AS hierarchy_level
  6  FROM
  7      emp
  8  START WITH
  9      mgr IS NULL
 10  CONNECT BY
 11*     PRIOR empno = mgr
SQL> /

   EMPNO ENAME         MGR    HIERARCHY_LEVEL
________ _________ _______ __________________
    7839 KING                               1
    7566 JONES        7839                  2
    7788 SCOTT        7566                  3
    7698 BLAKE        7839                  2
    7499 ALLEN        7698                  3
    7521 WARD         7698                  3
    7844 TURNER       7698                  3
    7782 CLARK        7839                  2
    7934 MILLER       7782                  3

9 rows selected.

Use Case: Visualizing an organizational chart or category tree.