[oracle-l] Re: Uniform extents
- From: "Tanel Poder" <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Jan 2004 17:55:53 +0200
Note that if you have LONG or LONG RAW datatypes you can't use alter table
move for reorganizing tables.
Tanel.
----- Original Message -----
From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Cc: <mkline1@xxxxxxxxxxx>
Sent: Tuesday, January 27, 2004 3:55 PM
Subject: [oracle-l] Re: Uniform extents
> 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@xxxxxxxxxxx [mailto:mkline1@xxxxxxxxxxx]
> Sent: Tuesday, January 27, 2004 8:47 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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
> -------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> -------------------------------------------------------------
> -------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> -------------------------------------------------------------
>
-------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
-------------------------------------------------------------
Other related posts:
