If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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?

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

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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.

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.