Home » RDBMS Server » Performance Tuning » ora-02287 sequnce not allowed here
ora-02287 sequnce not allowed here [message #110692] Wed, 09 March 2005 12:34 Go to next message
avdba_22
Messages: 5
Registered: March 2005
Junior Member
insert into top_sql(topsql_id,username,sql_text,disk_reads,buffer_gets,executions,diskreads_per_exec,buffergets_per_exec,
rows_processed,buffergets_per_row,buffercache_hit_ratio,shareable_memory,sorts,parse_calls,parse_per_exec,loads,cpu_time,
elapsed_time,tune_date)
(select seq_top_id.nextval from dual),
select b.username "User",
a.sql_text "Sql_text",
a.disk_reads "Disk reads",
a.buffer_gets "Buffer gets",
a.executions "Executions",
trunc(a.disk_reads/greatest(a.executions,1)) "Disk reads per execution",
trunc(a.buffer_gets/greatest(a.executions,1)) "Buffer gets per execution",
a.rows_processed " Rows processed",
trunc(a.buffer_gets/greatest(a.rows_processed,1)) "Buffer gets per row",
trunc(a.disk_reads/greatest(buffer_gets,1)*100 )"Buffer cache hit ratio",
a.sharable_mem "Shareable memory",
a.sorts "sorts",
a.Parse_calls "Parse Calls",
trunc(a.parse_calls/greatest(a.executions,1)) "Parse calls per execution",
a.Loads "Loads",
a.Cpu_time "Cpu_time",
a.elapsed_time "Elasped_time",
sysdate
from sys.v_$sql a,
sys.all_users b
where a.parsing_user_id=b.user_id and
b.username not in ('SYS','SYSTEM')
and rownum < 26
order by cpu_time desc


I am trying to get the top 25 sqls with the highest cpu_time and insert this into a table, but when I add the order by clause I get ORA_02287 error. I prefer to do this in the above manner, (not with a cursor).

Thank you.
Re: ora-02287 sequnce not allowed here [message #110713 is a reply to message #110692] Wed, 09 March 2005 15:12 Go to previous message
Frank Naude
Messages: 4566
Registered: April 1998
Senior Member
Probably just a syntax error. Try:

insert into top_sql(topsql_id,username,sql_text,disk_reads,buffer_gets,executions,diskreads_per_exec,buffergets_per_exec,
 rows_processed,buffergets_per_row,buffercache_hit_ratio,shareable_memory,sorts,parse_calls,parse_per_exec,loads,cpu_time,
 elapsed_time,tune_date)
 select seq_top_id.nextval, b.username "User",
...


Best regards.

Frank
Previous Topic: URGENT:-Performance Tuning a query
Next Topic: High Consistent gets
Goto Forum:
  


Current Time: Tue Jan 19 22:45:12 CST 2021