Efficiently write SELECT queries for tables with lots of columns (not SELECT *)

We can use the ALL_TAB_COLS view along with LISTAGG to generate the queries that you need. This strategy should work as long as your tables don't go above 125 columns or so. If you have too many very long column names LISTAGG won't return the full value because the return value is limited to a VARCHAR2(4000). Below is one implementation:

SELECT 'SELECT ' || COL_LIST || ' FROM SCOTT.' || TABLE_NAME || ';' QUERY_TO_RUN
FROM
(
  SELECT DISTINCT TABLE_NAME
  ,  LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)  OVER (PARTITION BY TABLE_NAME) COL_LIST
  from ALL_TAB_COLS
  WHERE OWNER = 'SCOTT'
  -- AND TABLE_NAME IN (...)
  AND COLUMN_NAME <> 'SHAPE'
) t;

In that example I'm using the built-in SCOTT schema (not sure if this is present in your database). Here are the results for me:

SELECT ENAME, JOB, SAL, COMM FROM SCOTT.BONUS;
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM SCOTT.EMP;
SELECT GRADE, LOSAL, HISAL FROM SCOTT.SALGRADE;
SELECT DEPTNO, DNAME, LOC FROM SCOTT.DEPT;