Here are the steps to pass along to a DBA to set up explain plan in SQL Navigator for Oracle:
1) Create a plan table under a specific schema owner:
CREATE TABLE sqln_explain_plan
(statement_id VARCHAR2(30),
timestamp DATE,
remarks VARCHAR2(80),
operation VARCHAR2(30),
options VARCHAR2(30),
object_node VARCHAR2(128),
object_owner VARCHAR2(30),
object_name VARCHAR2(30),
object_instance NUMBER(*,0),
object_type VARCHAR2(30),
optimizer VARCHAR2(255),
search_columns NUMBER(*,0),
id NUMBER(*,0),
parent_id NUMBER(*,0),
position NUMBER(*,0),
cost NUMBER(*,0),
cardinality NUMBER(*,0),
bytes NUMBER(*,0),
other_tag VARCHAR2(255),
partition_start VARCHAR2(255),
partition_stop VARCHAR2(255),
partition_id NUMBER(*,0),
other LONG,
distribution VARCHAR2(30))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE &Tablespace
/
-- Create synonym SQLN_EXPLAIN_PLAN
CREATE PUBLIC SYNONYM sqln_explain_plan
FOR sqln_explain_plan
/
-- Grants for Table
GRANT DELETE ON sqln_explain_plan TO public
/
GRANT INSERT ON sqln_explain_plan TO public
/
GRANT SELECT ON sqln_explain_plan TO public
/
GRANT UPDATE ON sqln_explain_plan TO public
/
2) Run "crtuser.sql" located in the <Install directory>/install
3) Run "crtsqltb.sql" located in the <Install directory>/install
When the explain plan is run, make sure the "Plan Table Owner" is changed to the right owner. It's default is the user's login. When browsing the saved plan, change the plan table owner as well.