On Feb 23, 3:14 pm, Unknown <m.ley..._at_chello.nl> wrote:
> Rdbms version: 9.2.0.5
> Database configuration: Data-warehouse / OLAP (noarchivelog)
> O.S. AIX 5L Version 5.1
>
> Hi,
>
> I am preparing for a change. 3 tablespaces where created using a
> too large uniform size of 1M. This needs to be rectified. The particular
> tablespaces need to be recreated with a smaller uniform size because the
> uniform size is too large for the size of objects the tablespace is
> housing causing inefficient data storage.
>
> The tablespaces in question contain only objects belonging to one
> user/schema and the user/schema only owns objects that are created in the
> tablespaces in question.
>
> What in globally needs to be done.
> - save create user & tablespace ddl from OEM.
> - lock user.
> - set tablespaces in question read only, to be certain no data is
> entered during change and lost during the coming deletion of the
> tablespaces?
> - export data belonging to the user or tablespaces in question.
> - set tablespaces read/write before drop?
> - drop tablespaces is question containing data, then drop user cascade
> - or-
> - drop user cascade without dropping the tablespaces first.
> - recreate tablespaces in question with correct uniform size using
> modified ddl scripts.
> - recreate user using ddl script.
> - import user objects.
>
> I have been advised by the customer to drop the user with cascade option,
> considering the user owns 100GB of data, I am afraid of performance
> issues, generation of large amounts of undo redo.
>
> If possible I would rather drop the tablespaces belonging to the user
> first and then drop the user with cascade to remove all other objects
> belonging to the user that are saved in the data dictionary.
>
> Please advise what the best strategy is, any other related tips are more
> the welcome.
>
> Regards Mick
> (Junior DBA).
Can you not just move the segments to a new tablespace (i.e. ALTER
TABLE ... MOVE, ALTER INDEX ... REBUILD, etc.)? That sounds a lot
easier to me.
Received on Fri Feb 23 2007 - 14:32:58 CST
