 |
|
Oracle Tips by Burleson |
Moving
OUTLINES from One DB to Another
A user may want to copy OUTLINEs from one database to another, for
example, to copy the outlines of an application from a test database
to a production database. This can easily be done utilizing Oracle’s
export and import routines.
Scenario
Once the
optimization for an application is achieved in a test database, you
can move the outlines created and stored for the application in a
production database. Instead of recreating these outlines in the
production database using the CREATE OUTLINE ... FOR CATEGORY
command for each optimized query of the application, export the
outlines for the specified category from the test database, and then
import these in the production database.
Technique
-
The outlines for all categories are inserted in the OL$ and
OL$HINTS tables owned by OUTLN schema.
-
The OUTLN schema, OL$ and OL$HINTS
tables are created during the database creation while sql.bsq is
executed:
create user
outln identified by outln
/
grant connect to outln
/
grant resource to outln
/
grant execute any procedure to outln
/
create table outln.ol$
(
ol_name varchar2(30), /* named is
potentially generated */
sql_text long, /* the SQL stmt
being outlined */
textlen number,
/* length of SQL stmt */
signature raw(16),
/* signature of sql_text */
hash_value number, /* KGL's
calculated hash value */
category varchar2(30), /*
category name */
version varchar2(64), /* db version @ outline
creation */
creator varchar2(30), /* user from whom outline
created */
timestamp date, /* time
of creation */
flags number, /* e.g. everUsed, bindVars,
dynSql */
hintcount number /* number of
hints on the outline */
)
/
create table outln.ol$hints
(
ol_name varchar2(30), /*
outline name */
hint# number, /* which hint
for a given outline */
category varchar2(30), /*
collection/grouping name */
hint_type
number, /* type of
hint */
hint_text varchar2(512), /* hint specific
information */
stage# number, /* stage of hint
generation/applic'n */
node#
number, /* QBC node id */
table_name varchar2(30), /* for
ORDERED hint */
table_tin number,
/* table instance number */
table_pos number
/* for ORDERED hint */
)
/
create unique index outln.ol$name on outln.ol$(ol_name)
/
create unique index outln.ol$signature on
outln.ol$(signature,category)
/
create unique index outln.ol$hnt_num on outln.ol$hints(ol_name,
hint#)
/
-
Export the data from the OUTLN.OL$ and OUTLN.OL$HINTS from the
test database, selecting the appropriate category for the
required application with the new 8i QUERY export parameter.
-
In the production database, it is recommended to store all
stored outlines in a separate tablespace. If this is not set
yet, create a new tablespace, and set this tablespace as the
default one for the OUTLN schema user. Drop the OL$ and
OL$HINTS tables so that the import recreates them in the
appropriate tablespace.
-
Import the OUTLN.OL$ and OUTLN.OL$HINTS tables and/or rows only
depending on the status of the previous step in the production
database.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
