Thanks for the speed reply.
Albert Nelson A, March 06, 2007 - 2:17 pm UTC
Tom,
We have following concerns in doing dbms_redefinition:
1. We are using streams to replicate data to another database. When we tested online redefinition (using rowid) we could see apply errors on the destination table. Though only the interim table got those errors. We suspected this may be due to the additional column added when using rowid method.
2. The number of segments to be moved will be around 1000. Because of the high number of objects, creating necessary interim tables and redefinition scripts and running may be tedious.
Thanks for your suggestions.
Regards,
Albert Nelson A.
reorganize tablespace
rafael santiago, August 03, 2012 - 11:20 am UTC
Tom,
i want implement assm in tablespace DATA1. i use oracle 92 version. so, what is the best method to do this? tablespace belong like default tablespace to user raf.
so
export schema raf
drop tablespace DATA1
create tablespace DATA1 with assm
and import data from file dump
other method i can
create temporary tablespace DATA1_TEMP
alter table move, and lob segment move to tablespace DATA1_TEMP
and drop tablespacer DATA1 and create tablespace DATA1 with assm
but how export procedures, java class, triggers before drop tablespace DATA1 ?
dbms_metadata is ok but doesn't work with objects like java class...
Thank You for answer!
August 16, 2012 - 8:26 am UTC
You cannot implement ASSM in an existing tablespace, you'll create a new tablespace and move the segments into it and then drop it.
you don't need to touch java/plsql etc, they live in system.
Just create tablespace newts;
alter table t move tablespace newts;
alter index i rebuild tablespace newts;
for each table/index in the old tablespace, drop old tablespace - rename newts back to oldtablespacename;
reorganize tablespace
rafael santiago, August 20, 2012 - 8:23 am UTC
Thank You!
But in oracle 92 how rename tablespace?
it is only one method rename tablespace by drop old tablespace and create new tablespace with old tablespace's name ?
if i have tablespace oldts and i move all tables to new tablespace newts
alter table t move tablespace newts;
and i drop old tablespace oldts
and i create new tablespace oldts with assm and move all tables from tablespace newts to tablespace oldts ?
August 28, 2012 - 12:54 pm UTC
if you use software from the last century.....
Stick with the new tablespace name then, why isn't that an option? Until you get to 10g.
reorganize tablespace
rafael santiago, August 20, 2012 - 8:55 am UTC
Tom,
if i move all tables from one tablespace to another for example
alter table t move tablespace newts nologging;
it will be faster then :
alter table t move tablespace newts;
?
Thank You for help.
August 28, 2012 - 12:57 pm UTC
it would sort of depend on whether redo logging is a bottleneck. Which is typically would not be (it happens in the background, the redo is constantly being generated by you and written to disk in the background by lgwr).
so, the answer is "maybe".
You'd have to factor in the time to backup everything since you need to do that right after a nologging operation.