In Oracle9i, you can certainly export tablespace contents. Check out the TABLESPACES option of exp
utility.
But then you must prebuild the objects with the extent sizes of your choice prior to importing
them in the new tablespace.
Unfortunately you can't avoid the mess!
Tom's solution is less messy :)
- Kirti
- "Mercadante, Thomas F" <thomas.mercadante_at_labor.state.ny.us> wrote:
> Have you looked at the "alter table table_name move tablespace new_tbspace
> command"?
>
> I would do the following:
>
> Create a new tablespace with the extent size you want.
> run the following, spooling the output to a file:
>
> set lines 120
> set pages 2000
> select 'alter table ' || table_name || ' move tablespace new_tablespace;'
> from user_tables;
>
> run the file produced.
>
> then:
>
> select 'alter index ' || index_name || ' rebuild tablespace
> indx_tablespace;'
> from user_indexes;
>
> run the file produced.
>
> You should be all set. This will take some time, but I like it better than
> export/import.
>
> Good Luck.
>
> Tom Mercadante
> Oracle Certified Professional
>
>
> -----Original Message-----
> From: mkline1_at_comcast.net [mailto:mkline1_at_comcast.net]
> Sent: Tuesday, January 27, 2004 8:47 AM
> To: oracle-l_at_freelists.org
> Subject: [oracle-l] Uniform extents
>
>
> I beleive I already know the answer, but is there any way to change the
> uniform extents on a tablespace?
>
> I'm trying to put a production database into "templates" that were built
> before my time.
>
> I'm running into production tablespaces built on 64k extents, and someone
> set up the test database with 100m uniform extents. Takes a lot of room to
> shove 100-1500 tables into there when I only need a few hundred meg
> normally.
>
> I'm probably sunk, but just wondering what "tricks" may be done to fix this.
> Can I convert it to dictionary, then back to local at the right setting
> which will be ignored except on new tables?
>
> Perhaps a trick to "export" a tablespace, then drop and recreate? I've also
> done some "moves", dropped and recreated, and put the tables back, but that
> too has been messy.
>
> Sure is messy.
>
> --
> 13308 Thornridge Ct
> Midlothian, VA 23112
> 804-744-1545
>
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
Received on Tue Jan 27 2004 - 08:16:51 CST
