Home » RDBMS Server » Server Utilities » exp/imp tables from one tablespace to another
exp/imp tables from one tablespace to another [message #569068] Fri, 19 October 2012 06:14 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello experts,


C:\Users\Neetesh>expdp system/*****@orcl2 dumpfile=temporary.dmp tables=testuser.test,testuser.test2

Export: Release 11.2.0.1.0 - Production on Fri Oct 19 16:39:06 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/*****@orcl2 dumpfile=temporary.dmp tables=testuser.test,testuser.test2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5.312 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "testuser"."test"                        3.554 MB   11585 rows
. . exported "testuser"."test2"                     272.1 KB     916 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  C:\APP\ADMINISTRATOR\ADMIN\orcl2\DPDUMP\TEMPORARY.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 16:39:01



then i import it in new user


SQL> create user temp identified by temp;

User created.

SQL> grant create session ,create table to temp;

Grant succeeded.

SQL> alter user temp quota 10 m on users;

User altered.

SQL> exit;

C:\Users\Neetesh>impdp system/Testdb@testdb remap_schema=testuser:temp dumpfile=temporary.dmp

Import: Release 11.2.0.1.0 - Production on Fri Oct 19 16:43:39 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_02":  system/********@testdb remap_schema=testuser:temp dumpfile=temporary.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"TEMP"."test" failed to create with error:
ORA-01950: no privileges on tablespace 'new_tbl'
Failing sql is:
CREATE TABLE "TEMP"."test" ("LINE_NUM" VARCHAR2(250 BYTE) NOT NULL ENABLE
, "LINE_NAME" VARCHAR2(250 BYTE) NOT NULL ENABLE, "COMP_ID" NUMBER, "R_W_WORK_OR
D_NO" VARCHAR2(50 BYTE), "R_W_PID" VARCHAR2(50 BYTE), "R_W_ABM" VARCHAR2(50 BYTE
), "R_W_AC" VARCHAR2(50 BYTE), "R_W_WIDTH_ID" NUMBER, "STATE_INCORP_ID" NUMBER,
"CONSTRUCTION_WORK_ORD_NO" VARCHAR2(50 BYTE)
ORA-39083: Object type TABLE:"TEMP"."test2" failed to create with error:
ORA-01950: no privileges on tablespace 'new_tbl'
Failing sql is:
CREATE TABLE "TEMP"."test2" ("ID" NUMBER NOT NULL ENABLE, "LINE_NUM" VARCHAR2
(50 BYTE), "GRANTOR" VARCHAR2(4000 BYTE), "PART_NUM" VARCHAR2(50 BYTE), "EASEMEN
T_NUM" VARCHAR2(50 BYTE), "STATE_INCORP" NUMBER, "test2_STATE" NUMBER, "SECTI
ON_NUM" VARCHAR2(50 BYTE), "IB_M" VARCHAR2(50 BYTE), "TWP_NUM" VARCHAR2(50 BYTE)
, "RANGE_NUM" VARCHAR2(50 BYTE), "QUARTER_SECTI
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 2 error(s) at 16:43:45





it gives 2 errors both are same as -
ORA-01950: no privileges on tablespace 'new_tbl'

exported table are exist in this 'new_tbl' tablespace but this is not exist in importing database.

then is there any way to import these tables in 'users' tablespace or other tablespace other than 'new_tbl'?

thanks........

Re: exp/imp tables from one tablespace to another [message #569071 is a reply to message #569068] Fri, 19 October 2012 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there any way to import these tables in 'users' tablespace or other tablespace other than 'new_tbl'?


remap_tablespace?

A very helpful command:
impdp help=y


Regards
Michel
Re: exp/imp tables from one tablespace to another [message #569074 is a reply to message #569071] Fri, 19 October 2012 07:09 Go to previous message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks sir.......

it is done by using 'remap_tablespace' clause.


thanks again.
Previous Topic: trace DDL SQL
Next Topic: Loading the date value into DATE column
Goto Forum:
  


Current Time: Tue Jul 22 22:49:52 CDT 2014

Total time taken to generate the page: 0.55941 seconds