Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Question: Is is safe to move a 'plan table' to a temp tablespace???
"BD" <bobby_dread_at_hotmail.com> wrote in message
news:1149195265.097581.181410_at_g10g2000cwb.googlegroups.com...
> Hey, all.
>
> I've got a non-managed standby database, which I am hoping to release
> as a reporting database.
>
> I'm trying to determine what I can and can't do with this db. Clearly,
> as it can only be opened in read only mode, I can't modify anything.
>
> I am trying an explain plan on a SQL statement (something I'm sure the
> developers would like for testing) and get an error:
>
> RA-01552: cannot use system rollback segment for non-system tablespace
>
> My current impression as to why this is happening is that explain plan
> writes to the plan_table, which is stored in a permanent tablespace.
>
> Can I not run explain plans in a read-only standby, then? Or is there
> some mechanism of moving the plan_table to a temp tablespace? Sounds
> like a bad idea, but otherwise, I see no way of using explain plan for
> tuning in a read-only db...
>
> Thanks for all comments,
>
> BD.
>
Interesting question.
In 10g, the default install of the plan_table
table is a global temporary table (called
plan_table$) in the SYS schema with a
public synonym and public access - and
I've the same sort of thing in 8i and 9i
but using the system schema for the last
few years.
But explain plan executes
SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL
which leads to an update of the seq$ table, which
means there is another reason why explain plan would
probably not work in a read-only database.
-- Regards Jonathan Lewis http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.htmlReceived on Thu Jun 01 2006 - 16:33:28 CDT