Home » RDBMS Server » Server Utilities » Importing into a separted tablespace.didnot find complete solution
Importing into a separted tablespace.didnot find complete solution [message #197744] Thu, 12 October 2006 09:01 Go to next message
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 Embarassed 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 Go to previous message
Mahesh Rajendran
Messages: 10708
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.

INDEXFILE option is used to get the DDL of tables and indexes.
Post what you have done. I mean, the exact commands and errors.
Literally 'explaining' what you have done will help us little.

Regards.
Previous Topic: Converting Sybase database to Oracle database ...
Next Topic: export and import
Goto Forum:
  


Current Time: Sun Dec 01 10:49:08 CST 2024