You Asked
I am trying to move objects from one tablespace to another tablespace using export and import.
Is there any way to avoid the errors below?
ORA-01950: no privileges on tablespace 'USERS'
Thank you for your time.
C:\>exp userid=imptest/imp file=exp.dmp
Export: Release 9.2.0.7.0 - Production on Mon Jun 26 14:09:39 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses US7ASCII character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user IMPTEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user IMPTEST
About to export IMPTEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export IMPTEST's tables via Conventional Path ...
. . exporting table DEMO_IOT 6091 rows exported
. . exporting table MYDBAOBJECTS 0 rows exported
. . exporting table NEWS_TEST 0 rows exported
. . exporting table T 100 rows exported
. . exporting table TESTUNDO 0 rows exported
. . exporting table TT 0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
EXP-00091: Exporting questionable statistics.
. 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 with warnings.
C:\>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.7.0 - Production on Mon Jun 26 14:09:55 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> drop user imptest cascade;
drop user imptest cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
SQL> drop user imptest cascade;
User dropped.
SQL> create user imptest identified by imp default tablespace moveto temporary tablespace temp;
User created.
SQL> grant connect, resource to imptest;
Grant succeeded.
SQL> revoke unlimited tablespace from imptest;
Revoke succeeded.
SQL> alter user imptest quota unlimited on moveto;
User altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
C:\>imp userid=imptest/imp file=exp.dmp ignore=y full=y
Import: Release 9.2.0.7.0 - Production on Mon Jun 26 14:10:37 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses US7ASCII character set (possible charset conversion)
. importing IMPTEST's objects into IMPTEST
. . importing table "DEMO_IOT" 6091 rows imported
. . importing table "MYDBAOBJECTS" 0 rows imported
IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "NEWS_TEST" ("ID" NUMBER, "BODY" CLOB) PCTFREE 10 PCTUSED 40 "
"INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1"
") TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("BODY") STORE AS (TABLESPACE "
""USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STO"
"RAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'
IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "T" ("X" NUMBER(*,0), "Y" CLOB) PCTFREE 10 PCTUSED 40 INITRAN"
"S 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLE"
"SPACE "USERS" LOGGING NOCOMPRESS LOB ("Y") STORE AS (TABLESPACE "USERS" EN"
"ABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITI"
"AL 65536 FREELISTS 1 FREELIST GROUPS 1))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'
. . importing table "TESTUNDO" 0 rows imported
. . importing table "TT" 0 rows imported
Import terminated successfully with warnings.
C:\>
and Tom said...
IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "NEWS_TEST" ("ID" NUMBER, "BODY" CLOB) PCTFREE 10 PCTUSED 40 "
"INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1"
") TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("BODY") STORE AS (TABLESPACE "
""USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STO"
"RAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1))"
that is one of those "multi-segment" objects.
Here is a quote from expert one on one Oracle regarding this:
<quote>
Multi-Tablespace CREATES
In the beginning, CREATE TABLE statements were relatively simple. Over the years, they have gotten progressively more and more complex. The 'train tracks' or 'wire diagram' for the simple CREATE TABLE now spans eight pages. One of the newer features of tables is the ability for bits and pieces of them to exist in various tablespaces. For example, a table with a CLOB column will have a table segment, CLOB index segment, and CLOB data segment. We can specify the location of the table and the locations of the CLOB data. An index-organized table (IOT) can have the index segment, and an overflow segment. Partitioned tables of course may have many partitions, each in a separately specified tablespace.
With this complexity comes some confusion for EXP/IMP. It used to be that if you tried to import an object and it failed due to the tablespace either not existing, or because you exceeded your quota on that tablespace, IMP would rewrite the SQL for you to create the object in your DEFAULT tablespace. IMP will not do this with multi-tablespace objects like it will with single tablespace object, even if all of the tablespaces specified in the CREATE are the same. An example will demonstrate the problem, and then I'll describe how we can work around the situation.
First, we'll start with a schema that has a couple of multi-tablespace objects and a simple single tablespace table in a tablespace:
tkyte@TKYTE816> create tablespace exp_test
2 datafile 'c:\oracle\oradata\tkyte816\exp_test.dbf'
3 size 1m
4 extent management local
5 uniform size 64k
6 /
Tablespace created.
tkyte@TKYTE816> alter user tkyte default tablespace exp_test
2 /
User altered.
tkyte@TKYTE816> create table t1
2 ( x int primary key, y varchar2(25) )
3 organization index
4 overflow tablespace exp_test
5 /
Table created.
tkyte@TKYTE816> create table t2
2 ( x int, y clob )
3 /
Table created.
tkyte@TKYTE816> create table t3
2 ( x int,
3 a int default to_char(sysdate,'d')
4 )
5 PARTITION BY RANGE (a)
6 (
7 PARTITION part_1 VALUES LESS THAN(2),
8 PARTITION part_2 VALUES LESS THAN(3),
9 PARTITION part_3 VALUES LESS THAN(4),
10 PARTITION part_4 VALUES LESS THAN(5),
11 PARTITION part_5 VALUES LESS THAN(6),
12 PARTITION part_6 VALUES LESS THAN(7),
13 PARTITION part_7 VALUES LESS THAN(8)
14 )
15 /
Table created.
tkyte@TKYTE816> create table t4 ( x int )
2 /
Table created.
So, we started by creating a tablespace and making this our DEFAULT tablespace. We then created an IOT with two segments the index and overflow. We created a table with a CLOB that has three segments. Then we have a partitioned table with seven segments. Lastly, we have the normal, simple 'table'. We export this schema:
tkyte@TKYTE816> host exp userid=tkyte/tkyte owner=tkyte
and proceed to drop that tablespace:
tkyte@TKYTE816> drop tablespace exp_test including contents;
Tablespace dropped.
tkyte@TKYTE816> alter user tkyte default tablespace data;
User altered.
When we import the schema we discover most of the tables won't come back in:
tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y
Import: Release 8.1.6.0.0 - Production on Tue Mar 20 19:03:18 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
Export file created by EXPORT:V08.01.06 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing TKYTE's objects into TKYTE
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "T2" ("X" NUMBER(*,0), "Y" CLOB) PCTFREE 10 PCTUSED 40 INITRA"
"NS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOB "
"("Y") STORE AS (TABLESPACE "EXP_TEST" ENABLE STORAGE IN ROW CHUNK 8192 PCT"
"VERSION 10 NOCACHE STORAGE(INITIAL 65536))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EXP_TEST' does not exist
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "T3" ("X" NUMBER(*,0), "A" NUMBER(*,0)) PCTFREE 10 PCTUSED 40"
" INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "EXP_TEST" PARTITION BY RANGE ("
""A" ) (PARTITION "PART_1" VALUES LESS THAN (2) PCTFREE 10 PCTUSED 40 INIT"
"RANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, P"
"ARTITION "PART_2" VALUES LESS THAN (3) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "
""PART_3" VALUES LESS THAN (4) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25"
"5 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_4" "
"VALUES LESS THAN (5) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE"
"(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_5" VALUES LE"
"SS THAN (6) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL "
"65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_6" VALUES LESS THAN ("
"7) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TA"
"BLESPACE "EXP_TEST" LOGGING, PARTITION "PART_7" VALUES LESS THAN (8) PCTFR"
"EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "
""EXP_TEST" LOGGING )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EXP_TEST' does not exist
. . importing table "T4" 0 rows imported
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "T1" ("X" NUMBER(*,0), "Y" VARCHAR2(25), PRIMARY KEY ("X") EN"
"ABLE) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOG"
"GING STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" PCTTHRESHOLD 50 OVERFLOW "
"PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 6553"
"6) TABLESPACE "EXP_TEST""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EXP_TEST' does not exist
Import terminated successfully with warnings.
Specifically, the only table that came back in without an error was the simple 'normal' table. For this table, IMP rewrote the SQL, blanking out the first TABLESPACE EXP_TEST that it came across and retried the CREATE. This rewritten CREATE succeeded. The other CREATES, when likewise rewritten, did not succeed. The only solution to this is to create the tables beforehand and then import with IGNORE=Y. If you do not have the DDL for the CREATE TABLES, you can retrieve it of course, from the DMP file with INDEXFILE=Y to modify it. In this case, since I had the DDL readily handy, I just created the three tables with new tablespaces specified where necessary:
tkyte@TKYTE816> create table t1
2 ( x int primary key, y varchar2(25) )
3 organization index
4 overflow tablespace data
5 /
Table created.
tkyte@TKYTE816> create table t2
2 ( x int, y clob )
3 /
Table created.
tkyte@TKYTE816> create table t3
2 ( x int,
3 a int default to_char(sysdate,'d')
4 )
5 PARTITION BY RANGE (a)
6 (
7 PARTITION part_1 VALUES LESS THAN(2),
8 PARTITION part_2 VALUES LESS THAN(3),
9 PARTITION part_3 VALUES LESS THAN(4),
10 PARTITION part_4 VALUES LESS THAN(5),
11 PARTITION part_5 VALUES LESS THAN(6),
12 PARTITION part_6 VALUES LESS THAN(7),
13 PARTITION part_7 VALUES LESS THAN(8)
14 )
15 /
Table created.
and was able to import cleanly:
tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y ignore=y
Import: Release 8.1.6.0.0 - Production on Tue Mar 20 19:03:20 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
Export file created by EXPORT:V08.01.06 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing TKYTE's objects into TKYTE
. . importing table "T2" 0 rows imported
. . importing partition "T3":"PART_1" 0 rows imported
. . importing partition "T3":"PART_2" 0 rows imported
. . importing partition "T3":"PART_3" 0 rows imported
. . importing partition "T3":"PART_4" 0 rows imported
. . importing partition "T3":"PART_5" 0 rows imported
. . importing partition "T3":"PART_6" 0 rows imported
. . importing partition "T3":"PART_7" 0 rows imported
. . importing table "T4" 0 rows imported
. . importing table "T1" 0 rows imported
Import terminated successfully without warnings.
</quote>
Rating
(11 ratings)
Is this answer out of date? If it is, please let us know via a Comment