I am trying to run a dba_hist_plan query to look for specific schema objects

Question

Here is my script minus the schema names.

col c1 heading ‘Owner’              format a13
col c2 heading ‘Object|Type’        format a15
col c3 heading ‘SQL|ID’             format a25

break on c1 skip 2

break on c2 skip 2

 select
  p.object_owner    c1,
  p.object_type     c2,
  p.SQL|ID'     c3,
from
  dba_hist_sql_plan p
where
        p.object_name is not null
    and
        p.object_owner <> '*'
group by
  p.object_owner,
  p.object_type,
  p.SQL|ID'
order by
  1,2,3 desc
 ;

I am getting this error here - SQL Error: ORA-00996: the concatenate operator is ||, not | 00996. 00000 - "the concatenate operator is ||, not |"

Answer 1

Below is a cleaned up version of the query:

select distinct
  object_owner,
  object_type,
  SQL_ID
from
  dba_hist_sql_plan
where
  object_name is not null
  and object_owner <> '*'
order by 1,2,3 desc;

I made these changes; some are fixes, and some are style suggestions:

1. Removed extra single quotes.
2. changed SQL|ID to SQL_ID.
3. Replaced GROUP BY with DISTINCT. They work the same here, but since you're not using any aggregate functions, DISTINCT is simpler.
4. Removed table alias. (Why create extra variables?)

But are you sure you want to do this with SQL*Plus? SQL*Plus is great for scripting installations and schema changes, but your task looks like an ad hoc data analysis of structured and unstructured data. An IDE worksheet is a better tool for this task for so many reasons. Download the free Oracle SQL Developer if you don't already have a GUI tool.