Import puts objects in wrong tablespace

[slicendice]

Hi
I am importing into a database from an export and all of the objects being imported seem to be going into the USERS tablespace even though they were originally (so I am told) in different tablespaces (i.e. DATA, INDEXES, etc) when they were exported.

Does anyone know any reason why this might be happening, and if so how to get round it?
Is there any way of checking an export dump file to see what tablespaces objects were originally in?

Thanks

 

[sem]

Does the user you're importing to has quotas on the underlined tablespaces?

 

[slicendice]

I'm not too sure - how would I check that?

 

[alexhu]

use import with SHOW=Y as the only parameter, this will show you the SQL the import is going to use

Alex

 

[alexhu]

in above - not 'only' parameter - you obviously need file= and user/passwd !!

Alex

 

[ThomVF]

use the SHOW=Y option of import to see the contents of the file (make sure you spool to a file when you do this so you can scan it)

Search for a particular object you know was in a specific tablespace - as a verification.


From ORACLE:
By default, a table is imported into its original tablespace.
If the tablespace no longer exists, or the user does not have sufficient quota in the tablespace, the system uses the default tablespace for that user

To check your tablespace quota(s):
select * from user_ts_quotas;
"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[slicendice]

The user had no quota on the tablespaces!! Thanks very much for your help.

However, I now need to get all the objects back into their correct tablespaces as they are currently sat in the USERS tablespace! What is the best way of doing this??

Thanks again

 

[ThomVF]

Depending on the volume of data, it might make sense to drop and re-import the data, once you have quota.

Alternatively, you can move the tables to the new tablespace individually, manually:
ALTER TABLE <table> MOVE TABLESPACE <new tablespace>;

Note: You cannot move partitioned tables in theor entirety; you must move individual partitions - check for any before doing this.

&quot;ALTER TABLE&quot; syntax from ORACLE docs:

http://download-east.oracle.com/docs/cd/A87860_01/doc/index.htm

Do the same for indexes, using the REBUILD option.
Syntax for &quot;ALTER INDEX&quot; command from ORACLE docs

http://download-east.oracle.com/docs/cd/A87860_01/doc/index.htm

&quot;Helping others to help themselves...&quot;
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com