system tablespace

[bazza]

Ok, this is a situation you should never get into, but we were constrained by a 3rd party application.

10gR2 RAC database on 2 linux nodes.

We literally have over half a million objects, mostly small tables and views. As such our system tablespace is around 11Gb.

We will shortly be implementing an applocation upgrade to create one large partitioned table and migrate the data from the small tables to the large one. After which the small tables get dropped.

The problem will be is that we'll be stuck with huge system datafiles that potentially (probably) we will not be able to significantly reduce the size of.

Without resorting to an export import of the whole 500Gb database, does anyone have any ideas to reduce the size of the system tablespace?

Thanks for your time.

[davey23uk]

move the objects 'alter table xxx move' so that they are at the start, unlucky if you packages are at the end of the datafiles

[bazza]

Thanks, not even thought of that. It may help, fingers crossed!

[bazza]

Ok, just had a brainwave (well actually I was nosing around on AskTom) that uses transportable tablespaces...

- generate a transportable tablespace set ( set tablespaces to read only and perform a special export)
- shutdown database instances on both nodes
- move/rename the old system/sysaux/undo datafiles
- Create a new empty database with just the bare minimum i.e. system/sysaux/undo etc
- Import the tablespace set
- Set tablespaces in read/write

Might have to create small undo datafiles (due to mountpoint constraints) and resize them up after we drop the old ones, but at first glance I think it would work.

Anyone spot any flaws?

[tamilselvan]

Ok, just had a brainwave (well actually I was nosing around on AskTom) that uses transportable tablespaces...

- generate a transportable tablespace set ( set tablespaces to read only and perform a special export)
- shutdown database instances on both nodes
- move/rename the old system/sysaux/undo datafiles
- Create a new empty database with just the bare minimum i.e. system/sysaux/undo etc
- Import the tablespace set
- Set tablespaces in read/write

Might have to create small undo datafiles (due to mountpoint constraints) and resize them up after we drop the old ones, but at first glance I think it would work.

Anyone spot any flaws?

How does this help to move half million objects from system tablespace to another tablespace?

[Bore]

As far as I understand the objects are not into the system tablespace. They just are half milion and their metadata is about 11G and when the objects gets dropped, the pace will be released but the data dictionary tables will not release extents nor the system datafile will be resized.
First, I do not see such big problem in 11G system tablespace, but if you want to reduce the size, I would support the idea of the transportable tablespaces.
The idea is:
1) Change the schema ( partitions instead of many objects)
2)Create a new database at the same machine
3) Generate a transportable tablespace set at the original database
4) Attach it to the newly createtd database
5) Test what have happen
6) Drop the original database

That's just and idea, I have not tested that, but seems the only aproach that could work

Regards
Boris

[PAVB]

...partitions instead of many objects...

...last time I checked it out "partitions" were "objects" too.

[bazza]

yes sorry should have made it crystal clear, the objects are all in data tablespaces and its purely the metadata information for all those objects that is making the system tablespace so large.

Partitions are only part of a table of course but 60 odd partitions are better than 500000 objects.

I think we're going to try the move within the system tablespace first (including the new 10g shrink segment commands) and if we can't reduce it significantly we'll see if we can live with it performance wise, if not we'll test the transportable tablespace idea.

Thanks for your input peeps.

[slimdave]

When you move the system tables, you're aware that the indexes on them will then be unusable and that you might crash your database? If you move the segment table, which presumably itself has a lot of segments itself, and invalidate an index on it that then prevents it from being maintained then will your database even work?

I'd counsel you to check with Oracle support on this one -- you might not be able to do this without breaking something.