| Importing into a separted tablespace.didnot find complete solution [message #197744] |
Thu, 12 October 2006 09:01  |
asherisfine
Messages: 63
Registered: June 2006
Location: India
|
Member |
|
|
Hi
I have a case where data is to be imported from a schema say OLD into a schema called NEW such that Objects in OLD are created in the default schema of NEW.
i got some help from orafaq itself. here is the information i got.
[B]Can one import tables to a different tablespace?[/B]
Oracle offers no parameter to specify a different tablespace to import data into. Objects will be re-created in the tablespace they were originally exported from. [B]One can alter this behaviour by following one of these procedures: [/B]
Pre-create the table(s) in the correct tablespace:
option 1.Import the dump file using the INDEXFILE= option
Edit the indexfile. Remove remarks and specify the correct tablespaces.
Run this indexfile against your database, this will create the required tables in the appropriate tablespaces
Import the table(s) with the IGNORE=Y option.
Change the default tablespace for the user:
option 2.Revoke the "UNLIMITED TABLESPACE" privilege from the user
Revoke the user's quota from the tablespace from where the object was exported. This forces the import utility to create tables in the user's default tablespace.
Make the tablespace to which you want to import the default tablespace for the user
Import the table
I tried out option 2 first, i was able to get data into the default tablespace of schema NEW.
I had 232 tables in schema OLD.When i imported using option 2, i could get only 198 tables into the schema "NEW".
I also tried option 1 (i.e. Indexfile=file1).I got a script file called "file1".interestingly all tables i could not get through option2, i got the script for them while following option1.
i haven't run the script in file "file1"
Following are my doubts.
1.The "file1" only has create table script for those tables which i could not generate i mean for the Remaining 34 tables(232-198=34).
2.There are no create index script in the "file1"
3.when i execute
SQL> select distinct table_name from user_indexes;
i get 55 rows in OLD schema.
and for same query i get 177 rows in NEW schema.
I am totally confused  about what iam seeing i mean indexfile creates a file that has no create index command,
user_indexes have indexes( which ofcourse are from my import using option1) but the count is different in the two schema's.
Please guide me understand the scenario.If you need any clarification on the scenario i explained, i will explain again.
|
|
|
|
| Re: Importing into a separted tablespace.didnot find complete solution [message #197754 is a reply to message #197744] |
Thu, 12 October 2006 09:41  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
It all depends on the version you are using.
In 10g, things are much simplified and easy to understand. use remap_schema option.
Neverthless in 9i and lower , it is still possible to do.
Inspite of your lengthy post, i am sorry to say, you have provided no useful information.
Your requirement seems to be:
>>where data is to be imported from a schema say OLD into a schema called NEW
But the note you are talking about from orafaq is to transport table to a different TABLESPACE and not SCHEMA.
All you need to do is,
export the source (OLD).
create the target (NEW).
Create tablespaces for target (new_tablespace).
Revoke RESOURCE from new (if granted).
Grant a quota on new_tablespace for NEW.
Import with fromuser/touser option.
scott@9i > create user new identified by new default tablespace tools;
User created.
scott@9i > grant connect to new;
Grant succeeded.
scott@9i > alter user new quota unlimited on tools;
User altered.
scott@9i > select table_name,tablespace_name from dba_tables where owner in ('NEW','OLD');
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
EMP USERS
PLAN_TABLE USERS
scott@9i > !exp scott/tiger owner=old file=old.dmp compress=n
Export: Release 9.2.0.7.0 - Production on Thu Oct 12 10:40:32 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OLD
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user OLD
About to export OLD's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export OLD's tables via Conventional Path ...
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table PLAN_TABLE 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
scott@9i > !imp scott/tiger fromuser=old touser=new file=old.dmp
Import: Release 9.2.0.7.0 - Production on Thu Oct 12 10:41:29 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing OLD's objects into NEW
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "PLAN_TABLE" 0 rows imported
Import terminated successfully without warnings.
scott@9i > select table_name,tablespace_name from dba_tables where owner in ('NEW','OLD');
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
EMP USERS
PLAN_TABLE USERS
DEPT TOOLS
EMP TOOLS
PLAN_TABLE TOOLS
6 rows selected.
Post what you have done. I mean, the exact commands and errors.
Literally 'explaining' what you have done will help us little.
Regards.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
