Re: Moving long tables out of the SYSTEM tablespace. Yikes!

First of all, the best decision you made was to move user tables out of the system tablespace. Whether your table has long columns or not, you should never have tables like this in the SYSTEM tablespace. Now, to your problem, you can export the tables out of the system tablespace. Then recreate the tables in the user tablespace and import the data back (you may want to increase the buffer size while importing). Some tips :
1. Query on the USER_CONSTARINTS where table_name = something, disable them while dropping the tables in SYSTEM tablespace. 2. For recreating tables, you can extract the DDLs from the dump file, specify the tablespace name in the DDLs and recreate them. 3. If you have a lot of long columns, you may want to consider keeping the long columns alone in a separate table with foreign keys pointing the parent row. This will ease your overall maintenance and if all your queries are not accessing these long columns, you will see enormous performance gains.

Good luck,
CM

In article <35568626.61AC823B_at_texas.net>,   Dell BSM DBA <jsilvey_at_texas.net> wrote:
>
> All,
>
> Have Oracle 7.3 database (NT 4.0) with user data tables in the system
> tablespace. Some of these tables have long datatype columns. Would like
> to move them into another tablespace so as not to interfere with data
> dictionary.
>
> Cannot recreate tables elsewhere using "Create table x as (select from
> y)" since the long datatypes prevent this.
>
> Cannot insert into new tables for the same reason.
>
> Solution one: extract the data from these tables, recreate tables by
> hand, load data, recreate constraints.
>
> Solution two: draw data from the long tables into MS Access tables,
> recreate the tables by hand elsewhere, insert data, recreate
> constraints.
>
> Both of these options are unappealing, since these tables are at the
> heart of a 100+ table transaction system, and have a lot of data and a
> lot of constraints.
>
> Any help greatly appreciated.
>
> Regards,
>
> Jack
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue May 12 1998 - 00:00:00 CDT