Re: reallocating tablespaces

Depending on the size of your database and the free disk space for your re-organization, you may have different choices.

If you have small database,

1. export all user objects with data
2. recreate the database with suitable storage parameters
3. create the user objects skeleton with suitable storage parameters (no data)
4. import the user objects with data (with ignore=Y)

If you have large database and much disk free space,

1. create a (or some) new tablespace with desired parameters
2. if the object is index, recreate the index in new tablespace
3. if the object is table, export the data, pre-create the table structure on the new tablespace, import the data.
4. drop the old tablespace when there is no objects in the tablespace.

Pls remember that always backup all data before performing the above action.

For the performace, using EXPLAIN PLAN is one of the method to tune the sql statements running in Oracle.

Joe

Ed Stevens wrote:

> Well, I've just completed the Oracle Performance and Tuning Workshop
> and am ready to attack our large collection of databases. All of these
> databases were built by either myself or one other DBA, and we both
> started with zero Oracle experience. As you can imagine, there are
> lots of tuning opportunities to correct past sins.
>
> One of the first things I want to do is correct the storage allocations
> of our tablespaces, resizing the extents and resetting PCTINCREASE back
> to zero. Could someone point me in the direction to best accomplish
> this? I'm sure I can figure out *a* way of doing it, and I'm not
> looking for a ready-to-run solution, just a point in the right
> direction, to avoid excessive spinning of wheels.
>
> One other item: As those who have taken the class know, it covers all
> sorts of things that impact performance, how to measure them, how to
> adjust them. But they are all "internal" things. My boss has said the
> one measure he cares about is query execution time -- how long does it
> take to execute a query. I can do all kinds of things with buffer
> caches, log files, file distribution, etc. etc. etc. but in the end
> I've got to be able to measure and show improvement in application
> response time. That was the one measurement I don't recall covering in
> the class. Again -- any hints?
>
> --
> Ed Stevens
> (Opinions are not necessarily those of my employer)
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jan 06 2000 - 08:50:00 CST