Re: exp /imp

> 1) how can i exp/imp the data w/o the association to a tablespace ?

When you export, the object's tablespace is "recorded" in the export. If you want to move an object to another tablespace using exp/imp, then you have to go through some minor hoops. It's pretty simple though.

1. Export the object (schema, or whatever).
2. Drop the object you want to import. You can drop the schema if you want to and all of those objects will be dropped.
3. If you dropped the schema, then recreate it with CREATE USER
4. ALTER USER username DEFAULT TABLESPACE new_ts; where new_ts is the new tablespace that you want the object to go into.
5. REVOKE UNLIMITED TABLESPACE FROM username; make sure that the owner doesn't have this.
6. ALTER USER username QUOTA 0 ON old_ts; means that the import cannot write to the old_ts
7. ALTER USER username QUOTA unlimited ON new_ts; import can now write to the new tablespace
8. Import

The idea is to export the object(s), make sure that the owner of the objects can't write to the old tablespace, and make sure that the objects can write to the new tablespace. When import tries to recreate the table, it will try to recreate it in the old tablespace first. But it can't because the owner of the object can't write to that tablespace anymore. Import needs to know where to put the object so it uses the new default tablespace, which it can write to.

> 2) sometimes when importing large amounts of data i get rollback segments
> errors (cannot extend).. so, i created a rollback segment w/ maxextents
> unlimited. this only makes the error occur later in the import. i found
> that adding commit=y to the imp line makes the errors go away but the imp
> takes longer. is there a way to tell oracle to keep extending so that the
> rollback errors do not occur (even w/o the commit=y) ? all of the
> tablespaces that i created have autoextend on.

Just because you have set rollback segments maxextents to unlimited does not mean that they can extend forever. They can only extend as long as there is room in the rollback segment tablespace. Increase the size of this tablespace and you should be fine. It's hard to say how big the tablespace should be. But I've seen them gigabytes in size just for rollback segments before.  

> personally i feel that our DBAs dropped the ball in organizing our
> production environment. (all users are in the 'users' tablespace.. among
> other setup/schema problems).. right now, i need to isolate as much data as
> possible in our development environment.

They could have dropped the ball, or they could have had valid reasons. Many times I've run into situations where I couldn't understand in my wildest dreams why someone designed it the way that they did. But when you talk to them (if they are around), they might just have valid points. And with those points, the design would make sense. I'm not saying that your DBAs are right or wrong. But they may have had reasons. The DBAs might turn around and say that the development team didn't give them appropriate information to make the correct decision. Now the ball is in your court.

> my problem is that our DBAs create everything in the 'users' tablespace.
> because of table conflicts in our development environment i need to be able
> to isolate the tables/users in their own tablespace. i have the
> users/tablespaces setup. but, when i exp/imp, the imp goes into the 'users'

You can only have table conflicts if a user tries to create two tables with the same name. If two users have tables with the same name you do not have a conflict because the tables are uniquely identified by preceeding it with the owner (owner.tablename). Putting tables in different tablespaces will not help resolve any of these conflicts. So I'm not quite sure what you meant about the above paragraph.

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================