-
I am trying to import tables from user A to another user B on the same database. Both the users have different default tablespaces. But when I try to import into user B , it puts all the tables in user A's tablespace . I want the tables to be imported in user B's tablespace.
Here what I did but I still get problem
1. REVOKE UNLIMITED TABLESPACE FROM UBER_B
2. ALTER USER USERNAME QUOTA 0 ON TABLESPACE_B
I don't know what I did wrong but it's not wokring. The question I have is on item 2:USERNAME, is it USER_A or USER_B. The second question I have is TABLESPACE, is it tablespace in tablespace_A or tablespace_B ??? What if I have index on separate tablespaces with data files. Please give me the detail instructions b/c I tried couple times and it's not working right.
What is solutions and please give me a detial step of how to make this work.
Thanks
-
Try To exports
Try doing two exports. On one of the exports use the parameter rows=N. Edit this export and do a find/replace for the tablespace name.
You can do this for the exports where rows = Y but if it is a large export it will take a while. save the file and import the file with the db structure only first. Disable RI, Triggers and drop sequences in that schema and import the second export. Then reenable RI and Triggers. The sequences will be imported. Then check for invalid objects. You can use the same procedure for changing initial extents sizes on tables.
-
Does anyone know how to change the dedault tablespace without edit the DDL file???
Please let me know
-
IF you want those tables to be created without editing the default tablespace, then you can go ahead and create those tables under user B and then do an import with ignore=Y. But the first suggestion would be the better way.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Originally posted by mike73
1. REVOKE UNLIMITED TABLESPACE FROM UBER_B
2. ALTER USER USERNAME QUOTA 0 ON TABLESPACE_B
I don't know what I did wrong but it's not wokring. The question I have is on item 2:USERNAME, is it USER_A or USER_B. The second question I have is TABLESPACE, is it tablespace in tablespace_A or tablespace_B ???
It is:
1. REVOKE UNLIMITED TABLESPACE FROM UBER_B
2. ALTER USER user_B QUOTA 0 ON tablespace_A;
Here, user_B is the user that will ow the imported tables and tablespace_A is the tablespace, where tables resided when you exported them (user_A's tablespace).
All tables and indexes that were originaly in tablespace_A will be imported now be imported into user_B's default tablespace.
If any of the original indexes (or tables) were originaly in tablespace_C, they will still be imported ito the same tablespace_C if you don't set user_A's qouta on that tablespace to 0.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Jmodic,
Thanks for your reply, but I have some problem following exactly for your prcedures.
I create an user TEST and defautl tablespace is TESTD and I got
ORA-01950: no privileges on tablespace 'TESTD'
Please let me know why TEST had no privileges on TESTD even I specified its default tablespace is TESTD. how to fix this problem
Thanks so much
-
Code:
alter user test quota unlimited on testd
/
-
thanks so much for all of your response and I still have one more error:
What if I have seperate index tablespace say for user A and user B, it can find the index tablespace for B even I have this command:
ALTER USER TEST QUOTA UNLIMITED TESTX
my indexes are created but none of the primary keys are created in TEST schema. Please tell me how to fix this problem
Thanks
-
Code:
spool rebuildidx.sql
select 'alter index '||index_name||' rebuild tablespace testx;'
from user_indexes
/
spool off
@rebuildidx.sql
-
Pando thank your for your reply and sorry I still have some problem
Here is what happened: From USER A, I have INDEX_A for index and primary key, when I imp to USER B, it automatically create all of the primary key from USER A into index of the USER B. Here is the short snapshot of my imp log.
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. . importing table "AFIS_ACCT_IFACE" 53006 rows imported
IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "AFIS_ACCT_IFACE" ADD CONSTRAINT "AFIS_ACCT_IFACE_PRIM" PRIMAR"
"Y KEY ("IFACE_KEY") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE("
"INITIAL 1064960 NEXT 516096 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 0 FREEL"
"ISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "HOUSTONX" ENABLE "
. . importing table "AFIS_ACCT_IFACE_HOLD" 848 rows imported
. . importing table "AFIS_ACCT_OVERRIDES" 0 rows imported
Please let me know how to fix it
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|