OraFAQ Forum: Performance Tuning » ora-02287 sequnce not allowed here

Home » RDBMS Server » Performance Tuning » ora-02287 sequnce not allowed here

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ora-02287 sequnce not allowed here [message #110692]

Wed, 09 March 2005 12:34

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.

Report message to a moderator

Re: ora-02287 sequnce not allowed here [message #110713 is a reply to message #110692]

Wed, 09 March 2005 15:12

Frank Naude
Messages: 4579
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 07:57:55 CDT 2024