Re: how to compress database and move indexes to different tablespace

In article <1992Sep2.100626.1_at_bbs.mdcbbs.com> suskind_at_bbs.mdcbbs.com writes:

> I am trying to compress my database. While I am at it I am moving stuff
> into various tablespaces to improve performance and layout. My original
> database has only ONE tablespace "system". My new database will have
> "system", "user", "temp", and "index". I can see how to easily move
> a user's tables into the "user" tablespace by only giving him resource
> in that tablespace and altering his user account so "user" is his default
> tablespace.

As you did not mention version you are using, I suppose RDBMS v. 6.0. You might already know this, but have you checked that none of your users have general resource privileges (that is, have number 1 in column RESOURCE_PRIV in table DBA_USERS). If they have, they have resource on every tablespace and granting (or revoking) resources in any one tablespace does not mean a thing and imports always goes to the original tablespace. So do not use GRANT RESOURCE TO <user> command (I think it is quite useless), use always GRANT RESOURCE ON <tablespace> TO <user> command.

> However, how to I move the corrisponding indexes? I have tried using the
> "show" option of IMP to generate a file, but this file is in such a poor
> format I cannot use it without EXTENSIVE editing. Does anybody have a
> utility that will take the file generated by the "show" option and make it
> usable? The format I get is ONLY 80 column records and EVERY line is
> enclosed in double quotes.

Try

IMPORT username/password INDEXFILE=<filename>

It gives you a file <filename>.SQL with CREATE INDEX commands. It works on RDBMS 6.0.34 (may work on earlier versions, but I cannot say for sure), but it is not yet in the manuals, only in the readme-file.

Hope this helps, I had to learn it by try-and-error.

Mikko Lahti                            Mikko.Lahti_at_fmi.fi