Online redef - is there a better way?
Ind-dba
We have a requirement to move some of the table from normal to
partitioned.
I tested the oracle provided online redef way by simulating select+dml
load on the table.
I tried using
exec DBMS_REDEFINITION.START_REDEF_TABLE
('OWNER1','SOURCE1','SOURCE1_TEMP$')
In this SOURCE1 is non partition table and SOURCE1_TEMP$ is hash
partition table on one of the keys.
This finished rather quickly - in 1 hr .. the no of rows in source1
table is 40 million.
After the above finished i created the necessary indexes/constraints.
-- this took 1 more hour. In parallel select and DML (upload) load was
running.
After index+constraints -- i used: exec
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('OWNER1','SOURCE1','SOURCE1_TEMP
$') -- this ran almost like for 8 hrs before i aborted this.
It seems online redef uses Mviews to refresh the data.
My question is - Is there a better way to move non-part tables to part
ways in a real OLTP environment or any extra precaution to make
DBMS_REDEFINITION work seamless?
-- Sachin
Mark D Powell
If you need to leave the table available them dbms_redefinition is
probably you best choice. Stopping the load jobs during the
redefinition process should greatly reduce the time it takes to apply
the DML changes that took place during the redefinition phase (the
sync) to the new version of the table so that it can become the table.
If you need to run the loads but do not need to be able to query the
data for a while then an alternate approach if you can get just a
couple minutes of no loading might be to rename the table, build the
new partition table, restart the loads, then run a task to insert the
existing data. Potentially the data movement could be done so that
only one partition is affected per data movement task. This would
allow moving data that you need access to now before dealing with data
that is only needed for month-end, quarterly, or year to date
processing that will not run for a while.
HTH -- Mark D Powell --
Ind-dba
1. We dont have cushion for going with 2nd option -- we cant stop
frontend users to not able to work. so availability of tables have to
be 100% (1% inconvenience is affordable).
2. Using DBMS_REDEFINITION.FINISH_REDEF_TABLE - is there a possibility
to get "ORA-00054 -- resource busy" at the time of swap of dictionary
names of 2 tables?
3. If yes -- any way to avoid it?
4. Is there a way to tune dbms_redefination way to work fast. I know
im being lazy in asking this. I should do this myself -- but asking if
anyone has done this in past?
Thx, Sachin
Jonathan Lewis
How many indexes do you have ?
You can resynch the table more than once in the
redefinition process. Especially if there is a lot of
extra activity, for example:
start redefinition (gets initial insert/select run)
resynch (with the log generated during the insert/select)
create first index nologging
resynch (with the log generated during index create)
create second index nologging
resynch,
repeat for each index in turn
each resynch will be slower as you have more indexes to maintain
finish redefinition.
The start redefinition and end redefinition both have to lock the
table during the data dictionary updates - so it is possible that
your end-user code gets an Oracle error 54 at either end of the
redefinition process: it all depends on what your normal code does
plus a little bit of luck.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Ind-dba" <oracl...@googlemail.com> wrote in message
news:d2463f1f-96cd-429e...@p2g2000prf.googlegroups.com...
Ind-dba
While doing FINISH redef -- i saw wait event "Wait for Table Lock" --
does that means that oracle will wait for table lock? And if we wait
-- we can avoid ORA-54? what do you say ?
1* select username,event,b.object_name from v$session, dba_objects b
where username='SYSTEM' and ROW_WAIT_OBJ#=b.object_id;
SQL> /
USERNAME
EVENT
OBJECT_NAME
------------------------------
----------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
SYSTEM Wait for Table
Lock TABLE_1
1 row selected.
-- Sachin
Jonathan Lewis
"Ind-dba" <oracl...@googlemail.com> wrote in message
news:13dfbc97-f396-4c3a...@n33g2000pri.googlegroups.com...
> Thanks Jonathan for your reply!
>
> While doing FINISH redef -- i saw wait event "Wait for Table Lock" --
> does that means that oracle will wait for table lock? And if we wait
> -- we can avoid ORA-54? what do you say ?
>
> 1* select username,event,b.object_name from v$session, dba_objects b
> where username='SYSTEM' and ROW_WAIT_OBJ#=b.object_id;
> SQL> /
>
You didn't say which session was waiting for a table lock,
the one doing the redefinition, or one of the sessions doing
inserts.
Why not try the experiment -
session 1 - insert rows into table, don't commit
session 2 - enable sql_trace, try to start redefinition -- does it get
stuck
session 3 - insert rows into table, don't commit -- does it get stuck
session 1 - commit -- is session 2 still stuck, or has the trace moved
on, does it get stuck again
session 1 - insert more rows - don't commit
and so on ...
If your "finishing" session is waiting for a lock, are other sessions
stuck behind it waiting, or do they fail. Does the "finishing"
session deliberately timeout and reacquire its lock so that any
sessions waiting behind it can clear before it tries again ?
I can't tell you whether your application is going to get unexpected
errors, because I don't know how your application is going to
respond to a blocking lock from "finishing" session.