in Education by
I want to import an oracle dump into a different tablespace. I have a tablespace A used by User A. I've revoked DBA on this user and given him the grants connect and resource. Then I've dumped everything with the command exp a/*** owner=a file=oracledump.DMP log=log.log compress=y Now I want to import the dump into the tablespace B used by User B. So I've given him the grants on connect and resource (no DBA). Then I've executed the following import: imp b/*** file=oracledump.DMP log=import.log fromuser=a touser=b The result is a log with lots of errors: IMP-00017: following statement failed with ORACLE error 20001: "BEGIN DBMS_STATS.SET_TABLE_STATS IMP-00003: ORACLE error 20001 encountered ORA-20001: Invalid or inconsistent input values After that, I've tried the same import command but with the option statistics=none. This resulted in the following errors: ORA-00959: tablespace 'A_TBLSPACE' does not exist How should this be done? Note: a lot of columns are of type CLOB. It looks like the problems have something to do with that. Note2: The oracle versions are a mixture of 9.2, 10.1, and 10.1 XE. But I don't think it has to do with versions. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by
You've got a couple of issues here. Firstly, the different versions of Oracle you're using is the reason for the table statistics error - I had the same issue when some of our Oracle 10g Databases got upgraded to Release 2, and some were still on Release 1 and I was swapping .DMP files between them. The solution that worked for me was to use the same version of exp and imp tools to do the exporting and importing on the different Database instances. This was easiest to do by using the same PC (or Oracle Server) to issue all of the exporting and importing commands. Secondly, I suspect you're getting the ORA-00959: tablespace 'A_TBLSPACE' does not exist because you're trying to import a .DMP file from a full-blown Oracle Database into the 10g Express Edition (XE) Database, which, by default, creates a single, predefined tablespace called USERS for you. If that's the case, then you'll need to do the following.. With your .DMP file, create a SQL file containing the structure (Tables): imp /@XE file= indexfile=index.sql full=y Open the indexfile (index.sql) in a text editor that can do find and replace over an entire file, and issue the following find and replace statements IN ORDER (ignore the single quotes.. '): Find: 'REM' Replace: Find: '""' Replace: '"USERS"' Find: '...' Replace: 'REM ...' Find: 'CONNECT' Replace: 'REM CONNECT' Save the indexfile, then run it against your Oracle Express Edition account (I find it's best to create a new, blank XE user account - or drop and recreate if I'm refreshing): sqlplus /@XE @index.sql Finally run the same .DMP file you created the indexfile with against the same account to import the data, stored procedures, views etc: imp /@XE file= fromuser= touser= ignore=y You may get pages of Oracle errors when trying to create certain objects such as Database Jobs as Oracle will try to use the same Database Identifier, which will most likely fail as you're on a different Database.

Related questions

0 votes
    I want to import an oracle dump into a different tablespace. I have a tablespace A used by User A ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 29, 2022 in Education by JackTerrance
0 votes
    I have to insert data in a table. It has a colum whose values should negative numbers like -1,-2 ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 4, 2022 in Education by JackTerrance
0 votes
    Slightly unimportant but it's piqued my curiousity. I've just logged into an Oracle 10g database for the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 18, 2022 in Education by JackTerrance
0 votes
    Slightly unimportant but it's piqued my curiousity. I've just logged into an Oracle 10g database for the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 18, 2022 in Education by JackTerrance
0 votes
    Slightly unimportant but it's piqued my curiousity. I've just logged into an Oracle 10g database for the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 15, 2022 in Education by JackTerrance
0 votes
    I'm working with secure application roles to grant access to users. In its dumbest form, it looks like ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 13, 2022 in Education by JackTerrance
0 votes
    I have not used access before but I have to convert an access query into SQL so I can write a report in crystal ... like this in SQL? Select the correct answer from above options...
asked Feb 8, 2022 in Education by JackTerrance
0 votes
    I have not used access before but I have to convert an access query into SQL so I can write a report in crystal ... like this in SQL? Select the correct answer from above options...
asked Feb 5, 2022 in Education by JackTerrance
0 votes
    Each tablespace in an Oracle database consists of one or more files called (a) Files (b) name space ... , Database Interview Questions and Answers for Freshers and Experience...
asked Oct 11, 2021 in Education by JackTerrance
0 votes
    I'm interesting in reducing the size of .hg folder in my project. I have size of .hg folder near ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 28, 2022 in Education by JackTerrance
0 votes
    is it possible to call c# methods written in managed code (maybe in a class or a library) from a ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 25, 2022 in Education by JackTerrance
0 votes
    A dump of the database contents is also referred to as an _____________ dump. (a) Archival (b) Fuzzy ... with Nonvolatile Storage in portion Recovery System of Database Management...
asked Oct 10, 2021 in Education by JackTerrance
0 votes
    I am using oracle 10g. I have a database user TDM_DD which executes a procedure in which it creates ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 3, 2022 in Education by JackTerrance
0 votes
    A tablespace is further broken down into ________ (a) Tablespace (b) Segments (c) Extents (d) Blocks The ... Storage in section Storage and File Structures of Database Management...
asked Oct 10, 2021 in Education by JackTerrance
0 votes
    A tablespace is further broken down into ________ (a) Tablespace (b) Segments (c) Extents (d) Blocks This ... Indices topic in chapter Indexing and Hashing of Database Management...
asked Oct 10, 2021 in Education by JackTerrance
...