>when there is already 3 tables available in source tablespace(SOUTHERN_TB),is it still possible to have the same tables in KAMESH tablespace through impdp function?
Objects are unique & can only exist once.
In you case the import will fail since the objects already exist.
The SYS schema/user should NEVER be used for daily use.
SYS schema should only be used for database patches & version upgrades.
establish your own schema to use & experiment with.
Few more questions,
1) So the remap_tablespace is used to move the objects from one tablespace to other tablespace in different database?
2) If the source tablespace(SOUTHERN_TB) is deleted then there wont be any objects.In this case can i use remap_tablespace to move the objects to newly created tablespace in same database?
1 person found this helpful
>1) So the remap_tablespace is used to move the objects from one tablespace to other tablespace in different database?
>2) If the source tablespace(SOUTHERN_TB) is deleted then there wont be any objects.In this case can i use remap_tablespace to move the objects to newly created tablespace in same database?
you can not DROP TABLESPACE when any object still resides in the tablespace
1) Let us assume if i accidentally deleted my SOUTHERN_TB tablespace which has 3 tables namely southern1,southern2 and southern3.
Before deleting the tablespace i took bthe export backup of the tablespace southern_tb.
Do you still say it is not possible to restore the 3 tables using remap_tablespace to different tablespace in same database?
Message was edited by: Kamesh
1 person found this helpful
You can restore the tablespace or you can remap it. If the objects that you are importing already exist (anyplace in the database) (same tablespace or different tablespace) Data Pump's default behavior is to skip these objects. You can use
to drop an existing table and then import the table from the dumpfile. If you have a remap_tablespace on this import job, then your existing table in tablespace southern_tb will be dropped and a new table will be created in tablespace kamesh.
Hope this helps.
i was trying to do the import asnd i am getting the below error. Do you know what i am missing here ?
C:\Users\Pranav>impdp directory=Datapump_testing dumpfile=SOUTHERN_TB.DMP schema
s=southern remap_schema=southern:kamesh REMAP_TABLESPACE=SOU
Import: Release 220.127.116.11.0 - Production on Tue Sep 3 19:27:17 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
ORA-39039: Schema expression "IN (SELECT object_name FROM "SYS"."EXPORT_ORA" WHE
RE process_order = -56 AND duplicate BETWEEN 1 AND 1)" contains no valid schemas
Master table "SYS"."EXPORT_ORA" successfully loaded/unloaded
Starting "SYS"."EXPORT_ORA": sys/******** AS SYSDBA directory=Datapump_testing
dumpfile=SOUTHERN_TB.DMP schemas=southern job_name=export_ora remap_schema=south
Job "SYS"."EXPORT_ORA" successfully completed at 19:27:25
command should be like remove schemas clause
impdp directory=Datapump_testing dumpfile=SOUTHERN_TB.DMP remap_schema=southern:kamesh REMAP_TABLESPACE=SOUTHERN_TB:KAMESH
Thanks sb92075 , DeanGagne and DK2010. You guys made my day.