exp/imp tables from one tablespace to another [message #569068] |
Fri, 19 October 2012 06:14 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/0d8d54604424af68f3c669fe2bc54c02?s=64&d=mm&r=g) |
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........
|
|
|
|
|