- Ensure that the new user cannot create objects in the old tablespace:
REVOKE UNLIMITED TABLESPACE FROM new_user; ALTER USER new_user QUOTA 0 ON old_tablespace;
- Ensure that the new user can create objects in the new tablespace:
ALTER USER new_user QUOTA unlimited ON new_tablespace;
- Make the new tablespace the new user's default tablespace:
ALTER USER new_user DEFAULT TABLESPACE new_tablespace;
- Import the objects from the dump file.
imp userid=system file=mydump.dmp fromuser=old_user touser=new_userThis step works because any CREATE TABLE or CREATE INDEX statements on import will not be able to create in the old tablespace. Those objects will then be created in the user's default tablespace.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading