Re: Moving TABLES to another TABLESPACE

From: Dale Cooper <cooper_at_seismo.CSS.GOV>
Date: 2 Feb 1995 14:59:45 GMT
Message-ID: <3gqs11$dle_at_seismo.CSS.GOV>


In article <D30yqC.D9F_at_acsu.buffalo.edu> oispeggy_at_ubvms.cc.buffalo.edu (Peggy Brown) writes:
>In article <3fpl05$8af$1_at_mhadf.production.compuserve.com>, Paul MJ Stone <100255.305_at_CompuServe.COM> writes...

->Basically you have to export the tables, drop them, take the 
->tablespace offline, redefine the users default tablespace to be 
->the target and then import the tables. 
-> 
->Alternatively export the tables, use IMPORT user/pwd 
->INDEXFILE=filename.SQL to generate a set of CREATE TABLE 
->statements in filename.SQL and then edit the files' TABLESPACE 
->clauses and rebuild the tables before importing the data.

>Wait a minute. Do you really have to do all that?

Well, no. But if your tables have a bunch of indices, grants, synonyms, etc. your simple CREATE statement just won't cut it. The beauty of the IMP/EXP solution is that it will do all of this stuff for you! Mr. Stone's response is actually the best solution. If you do it any other way, it's up to you to track down the indices, grants and synonyms. If you're anything like me, basically lazy ;) , you do everything in your power to make the system do as much work for you as possible. Why risk the embarrassment of forgetting to reset a simple permission?

The IMP option to create the INDEXFILE is extremely helpful when it comes to moving databases, reinstalling systems, etc. Whoever at Oracle developed that should get a serious pat on the back.

>How about making the new tablespace. Then assign a user to the new
>userspace (a user with access to the tables in question). Log in as
>this user.
 

>Then say: CREATE TABLE NEW_TABLE AS SELECT * FROM OLD_TABLE;
 

>I did this, after being told what you said above, and it worked for me
>and was a lot easier.

Yes, but did it recreate your indices? Did it create the new table with

the same storage definitions as the original table?  Did it recreate all
of the synonyms associated with the original table?  Did it recreate all
of the access permissions to the appropriate users?  Probably not.

While your technique may indeed be fine for your application, it just doesn't do all of the things listed above. For a complex system, it may even fail!

Consider this. If the INITIAL size of the original table is 100MB and the default storage definitions of the new tablespace are 100K INITIAL and 100K NEXT, and PCTINCREASE 0, and you didn't include a storage clause in your CREATE statement, guess what.

Your CREATE statement could fail. Why? Because the new table would be created with an INITIAL size of 100K, it will grow in 100K increments (again, assuming PCTINCREASE 0) until you hit the operating system limit for the number of extents (121 on most Unix boxes). Yes, this could be minimized by setting your PCTINCREASE to some non-zero value, but you would still end up with a fragmented table...to some degree.

Not only that, if your original table had and indices, grants or synonyms associated with it, you have to recreate them yourself. Yuck. Remember the "basically lazy" comment. Make the system work for you, not you for it.

Have fun.

Dale Cooper, DBA
Center for Monitoring Research
Arlington, VA

I don't speak for my company, why should I? They don't buy me beer. Received on Thu Feb 02 1995 - 15:59:45 CET

Original text of this message