|
Import with different tablespace and user names Pre-requisites before going for import. -Tablespaces should be there already or create a new one. A.Login as system & Use the following sql stmt to create customised tablespace of your own choice create tablespace <data_tablespace_name> Datafile '<data_tablespace_path>\<dat_tablespace_name>.dbf' size <data_tablespace_size_in_mb> reuse autoextend on NEXT 1M MAXSIZE UNLIMITED; Note: Provide tablespace size in Megabyte structure e.g: 200M for 200Megabyte B.Use below query to create customised indexspace create tablespace <index_tablespace_name> Datafile '<index_tablespace_path>\<index_tablespace_name<.dbf' size <index_tablespace_size_in_mb> reuse autoextend on NEXT 1M MAXSIZE UNLIMITED; -User should be already existing with assigned tablespaces or create a new user and assign the tablespaces. -Use below query to crete user: create user <schema_user_name> identified by <schema_user_password> default tablespace <data_tablespace_name> temporary tablespace temp_tablespace_name; Note: For temp tablespace "temp" name can be used. -Give the newly created user DBA to let it import, as only a DBA can import a DBA created dump file. login as system user & use below command to give DBA permission: grant dba to <schema_user_name>; C. Run the import in command prompt using the INDEXFILE option. This will create a file called index.sql. imp <schema_user_name>/<schema_user_password>@<SID> file=Dump.dmp indexfile=index.sql fromuser=<from_user_name> touser=<to_user_name>; Note: here from_user_name is the user name of the previous schema from where dump file is taken and to_user_name is same as the schema_user_name to which the data will be imported. D. Edit the index.sql file, Remove all the "REM ", and replace all instances of old tablespace names and old indexspace names with newly created tablespace and indexspace names respectively. And also remove a sentence like "CONNECT <to_user_name>". This statement will come only once in index.sql file. E. Login as <to_user_name> into the DB using SQL*Plus and execute the index.sql file. This will create all the objects in the new schema. -Use below commands to login and run the index.sql file. sqlplus <to_user_name>/<to_user_password>@SID; sql>@index.sql; F. Run the import again with the IGNORE=Y option in command prompt. imp <to_user_name>/<to_user_password>@SID file=dump.dmp full=Y ignore=Y log=Dumplog.log G. This will import all the data to the new schema with different tablespace and indexspace name. Note: <I> Ignore errors during import which says table or objects already exists. This is a normal error as we have already created all the objects by running index.sql file in sqlplus. <II> Ignore compilation errors as it will all go once you compile all after importing is done. Labels: ORACLE
Post / Read Comments |