Still think the easiest way is
alter table X move tablespace Y;
alter index X rebuild tablespace Y;
that is available in V8.1.7. Just done it this afternoon in a production
database. Smooth, no problems.
The alter table move makes indexes on the table "unusable". Check for status
!='VALID' in user_indexes to see if there are still indexes that are not
rebuild.
It won't work for tables with a long column. Use export/import here.
For lob columns statement alter table X move lob (LOBCOLNAME) store as
LOBSEGMENTNAME (tablespace Y) is required (query user_lob to get this info).
You can add "(storage (initial ......) )" after each "tablespace Y" if
storage parameters needs to be adjusted too
Its very easy to generate sql with sql for this reorganisation.
Teresa <tpreto7_at_sapo.pt> schreef in berichtnieuws
6dabc692.0210031734.67187360_at_posting.google.com...
| Thanks Peter and Norman for you help, I will export the users out,
| this is Oracle 8170
| Teresa
|
| Pete Sharman <peter.sharman_at_oracle.com> wrote in message
news:<anhple028ji_at_drn.newsguy.com>...
| > In article <6dabc692.0210030335.31d3fec4_at_posting.google.com>,
tpreto7_at_sapo.pt
| > says...
| > >
| > >I just discovered that I have 2 users with a permanent tablespace
| > >index1 and index3, how can I move them to a tablespace of their own, I
| > >will create a tablespace for them then how to I do a import their
| > >contents into the new tablespaces ....
| > >Thanks
| > >Teresa
| >
| > While Norman has scripted the whole thing out for you, depending on your
version
| > there are a couple of other things you may want to look at. You could
simply
| > move the objects with the relevant ALTER commands, or you could (in 9i)
export
| > the tablespace in question. You could even use the DBMS_REDEFINITION
package
| > (again only in 9i) if the objects must remain available during the move.
| >
| > HTH. Additions and corrections welcome.
| >
| > Pete
| >
| > SELECT standard_disclaimer, witty_remark FROM company_requirements;
Received on Fri Oct 04 2002 - 12:22:35 CDT
