Printable View
I have found many posts regarding the subject of "Importing a schema into a different tablespace" but I have not found one that shows complete instructions on how to do this from start to finish. Can someone point out to me or provide me exact instructions on how to export and import a schema from a certain tablespace into a different tablespace???
Please help...I've been trying to do this for some time now and I have not been able to do so...
pando has a really nice solution, playing with quotas ;)
Note:1012307.6 on Metalink has pretty good instructions.
Okay... I have tried all of your recommendations and I'm back to square one.
I followed very throughly the instructions on document 1012307.6:
1) Create new user (user-b)
2) ALTER USER user-b QUOTA 0 ON TABLESPACE tblspace-A
3) REVOKE UNLIMITED TABLESPACE from user-b
4) ALTER USER user-b DEFAULT TABLESPACE tblspace-B
QUOTA UNLIMITED ON tblspace-B
5) Tested create test table on tblspace-A (did not allow)
- received ORA-01536 instead of ORA-01950
6) Tested create test table on tblspace-B (okay)
7) Performed Import
Results: with RESOURCE priv and without doing step 4
it imported back to tbspace-A
Without RESOURCE and step 4, it loaded into
correct tblspace-B but failed with the following
errors:
After various combinations of removing privileges (RESOURCES) and removing tablespace allocation, I was able to load into the appropriate table space but the import failed first with "IMP-00017: following statement failed with ORACLE error 2270:..." and after reimporting with options ROWS=N, IGNORE=Y, it failed again with error "IMP-00017: following statement failed with ORACLE error 2264:"
Please help?
have u tried to import with fromuser=olduser touser=newuser?
Yes. These are the parameters I used for the first import:
USERID=...
FILE=...
LOG=...
FROMUSER=USER-A
TOUSER=USER-B
BUFFER=128000
ANALYZE=N
GRANTS=Y
COMMIT=Y
INDEXES=Y
IGNORE=Y
CONSTRAINTS=Y
ROWS=Y
2nd Import:
USERID=
FILE=...
LOG=...
FROMUSER=USER-A
TOUSER=USER-B
BUFFER=128000
ROWS=N
IGNORE=Y
Personally, I would:
1. export USER-A
2. imp indexfile=abc.sql
3. edit abc.sql
3a. change references from old TS to new TS
3b. uncomment create table statements
4. run abc.sql to pre-create objects from sqlplus as USER-B
5. imp fromuser=user-a touser=user-b ignore=y