如何在不同的表空间中导入 oracle 转储

Question

提问by Michiel Overeem

I want to import an oracle dump into a different tablespace.

我想将 oracle 转储导入不同的表空间。

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

我有一个由用户 A 使用的表空间 A。我已经撤销了该用户的 DBA 并授予他连接和资源权限。然后我用命令倾倒了所有东西

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:

现在我想将转储导入用户 B 使用的表空间 B。所以我给了他连接和资源的授权（没有 DBA）。然后我执行了以下导入：

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

IMP-00017：以下语句失败，出现 ORACLE 错误 20001：“BEGIN DBMS_STATS.SET_TABLE_STATS IMP-00003：ORACLE 错误 20001 遇到 ORA-20001：无效或不一致的输入值

After that I've tried the same import command but with the option statistics=none. This resulted in the following errors:

之后，我尝试了相同的导入命令，但使用了选项 statistics=none。这导致了以下错误：

ORA-00959: tablespace 'A_TBLSPACE' does not exist

ORA-00959: 表空间“A_TBLSPACE”不存在

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.

注意：很多列都是 CLOB 类型。看起来问题与此有关。

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.

注 2：oracle 版本是 9.2、10.1 和 10.1 XE 的混合版本。但我认为这与版本无关。

Answer 1

采纳答案by Andrew

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.

首先，您使用的不同版本的 Oracle 是导致表统计错误的原因 - 当我们的一些 Oracle 10g 数据库升级到第 2 版时，我遇到了同样的问题，而有些仍然在第 1 版，我正在交换。它们之间的 DMP 文件。

The solution that worked for me was to use the same version of expand imptools 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.

对我来说有效的解决方案是使用相同版本exp和imp工具做出口和进口在不同的数据库实例。通过使用同一台 PC（或 Oracle 服务器）发出所有导出和导入命令，最容易做到这一点。

Secondly, I suspect you're getting the ORA-00959: tablespace 'A_TBLSPACE' does not existbecause 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 USERSfor you.

其次，我怀疑你得到的ORA-00959: tablespace 'A_TBLSPACE' does not exist，因为你想导入从一个完全成熟的Oracle数据库.dmp文件到10g快捷版（XE）数据库，该数据库，默认情况下，创建一个名为单一的预定义的表空间USERS的你。

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_username>/<password>@XE file=<filename.dmp> 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<space>' Replace: <nothing>
Find: '"<source_tablespace>"' 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_username>/<password>@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_username>/<password>@XE file=<filename.dmp> fromuser=<original_username> touser=<xe_username> ignore=y

使用您的 .DMP 文件，创建一个包含结构（表）的 SQL 文件：
imp <xe_username>/<password>@XE file=<filename.dmp> indexfile=index.sql full=y
在可以对整个文件进行查找和替换的文本编辑器中打开索引文件 (index.sql)，并按顺序发出以下查找和替换语句（忽略单引号.. '）：
Find: 'REM<space>' Replace: <nothing>
Find: '"<source_tablespace>"' Replace: '"USERS"'
Find: '...' Replace: 'REM ...'
Find: 'CONNECT' Replace: 'REM CONNECT'
保存索引文件，然后针对您的 Oracle Express Edition 帐户运行它（我发现最好创建一个新的空白 XE 用户帐户 - 或者如果我正在刷新，则删除并重新创建）：
sqlplus <xe_username>/<password>@XE @index.sql
最后运行与您使用相同帐户创建索引文件的相同 .DMP 文件以导入数据、存储过程、视图等：
imp <xe_username>/<password>@XE file=<filename.dmp> fromuser=<original_username> touser=<xe_username> 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.

当您尝试创建某些对象（例如数据库作业）时，您可能会遇到 Oracle 错误页面，因为 Oracle 将尝试使用相同的数据库标识符，这很可能会在您使用不同的数据库时失败。

Answer 2

回答by Andrew

If you're using Oracle 10g and datapump, you can use the REMAP_TABLESPACE clause. example:

如果您使用 Oracle 10g 和数据泵，则可以使用 REMAP_TABLESPACE 子句。例子：

REMAP_TABLESPACE=A_TBLSPACE:NEW_TABLESPACE_GOES_HERE

Answer 3

回答by Grebets Kostyantyn

For me this work ok (Oracle Database 10g Express Edition Release 10.2.0.1.0):

对我来说，这项工作正常（Oracle 数据库 10g 快捷版 10.2.0.1.0 版）：

impdp B/B full=Y dumpfile=DUMP.dmp REMAP_TABLESPACE=OLD_TABLESPACE:USERS

But for new restore you need new tablespace

但是对于新的还原，您需要新的表空间

P.S. Maybe useful http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

PS 也许有用http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

Answer 4

回答by Matthew Watson

What version of Oracle are you using? If its 10g or greater, you should look at using Data Pump instead of import/export anyway. I'm not 100% sure if it can handle this scenario, but I would expect it could.

您使用的是哪个版本的 Oracle？如果它是 10g 或更大，你应该考虑使用数据泵而不是导入/导出。我不是 100% 确定它是否可以处理这种情况，但我希望它可以。

Data Pumpis the replacement for exp/imp for 10g and above. It works very similar to exp/imp, except its (supposedly, I don't use it since I'm stuck in 9i land) better.

Data Pump是 10g 及以上版本 exp/imp 的替代品。它的工作原理与 exp/imp 非常相似，除了它（据说，我不使用它，因为我被困在 9i 土地上）更好。

Here is the Data Pump docs

这是数据泵文档

Answer 5

回答by Michiel Overeem

The problem has to do with the CLOB columns. It seems that the imp tool cannot rewrite the create statement to use another tablespace.

问题与 CLOB 列有关。似乎imp工具无法重写create语句以使用另一个表空间。

Source: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:66890284723848

来源：http: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 66890284723848

The solution is: Create the schema by hand in the correct tablespace. If you do not have a script to create the schema, you can create it by using the indexfile= of the imp tool.

解决方案是：在正确的表空间中手动创建模式。如果您没有创建模式的脚本，则可以使用 imp 工具的 indexfile= 创建它。

You do have to disable all constraints your self, the oracle imp tool will not disable them.

您必须自己禁用所有约束，oracle imp 工具不会禁用它们。

After that you can import the data with the following command:

之后，您可以使用以下命令导入数据：

imp b/*** file=oracledump.dmp log=import.log fromuser=a touser=b statistics=none ignore=y

Note: I still needed the statistics=none due to other errors.

注意：由于其他错误，我仍然需要 statistics=none。

extra info about the data pump

关于数据泵的额外信息

As of Oracle 10 the import/export is improved: the data pump tool ([http://www.oracle-base.com/articles/10g/OracleDataPump10g.php][1])

从 Oracle 10 开始，导入/导出得到改进：数据泵工具 ([ http://www.oracle-base.com/articles/10g/OracleDataPump10g.php][1])

Using this to re-import the data into a new tablespace:

使用它来将数据重新导入新的表空间：

First create a directory for the temporary dump:
CREATE OR REPLACE DIRECTORY tempdump AS '/temp/tempdump/';
GRANT READ, WRITE ON DIRECTORY tempdump TO a;
Export:
expdp a/* schemas=a directory=tempdump dumpfile=adump.dmp logfile=adump.log
Import:
impdp b/* directory=tempdump dumpfile=adump.dmp logfile=bdump.log REMAP_SCHEMA=a:b

首先为临时转储创建一个目录：
创建或替换目录 tempdump 为 '/temp/tempdump/';
授予读取，写入目录 tempdump 到 a;
出口：
expdp a/* schemas=a directory=tempdump dumpfile=adump.dmp logfile=adump.log
进口：
impdp b/* directory=tempdump dumpfile=adump.dmp logfile=bdump.log REMAP_SCHEMA=a:b

Note: the dump files are stored and read from the server disk, not from the local (client) disk

注意：转储文件是从服务器磁盘存储和读取的，而不是从本地（客户端）磁盘

Answer 6

回答by Dmitry

my solution is to use GSAR utility to replace tablespace name in the DUMP file. When you do replce, make sure that the size of the dump file unchanged by adding spaces. E.g.

我的解决方案是使用 GSAR 实用程序替换 DUMP 文件中的表空间名称。执行replce 时，通过添加空格确保转储文件的大小不变。例如

gsar -f -s"TSDAT_OV101" -r"USERS      " rm_schema.dump rm_schema.n.dump
gsar -f -s"TABLESPACE """USERS      """ ENABLE STORAGE IN ROW CHUNK 8192 RETENTION" -r"                                                                   " rm_schema.n1.dump rm_schema.n.dump
gsar -f -s"TABLESPACE """USERS      """ LOGGING" -r"                                  " rm_schema.n1.dump rm_schema.n.dump
gsar -f -s"TABLESPACE """USERS      """ " -r"                             " rm_schema.n.dump rm_schema.n1.dump

Answer 7

回答by peter

I wanna improve for two users both in different tablespaces on different servers (databases)

我想为不同服务器（数据库）上不同表空间的两个用户改进

1. First create a directories for the temporary dump for both servers (databases):

1. 首先为两个服务器（数据库）的临时转储创建一个目录：

server #1:

服务器#1：

CREATE OR REPLACE DIRECTORY tempdump AS '/temp/old_datapump/';
GRANT READ, WRITE ON DIRECTORY tempdump TO old_user;

server #2:

服务器#2：

CREATE OR REPLACE DIRECTORY tempdump AS '/temp/new_datapump/';
GRANT READ, WRITE ON DIRECTORY tempdump TO new_user;

2. Export (server #1):

2. 导出（服务器 #1）：

expdp tables=old_user.table directory=tempdump dumpfile=adump.dmp logfile=adump.log

3. Import (server #2):

3. 导入（服务器 #2）：

impdp directory=tempdump dumpfile=adump_table.dmp logfile=bdump_table.log
REMAP_TABLESPACE=old_tablespace:new_tablespace REMAP_SCHEMA=old_user:new_user

Answer 8

回答by Michiel Overeem

The answer is difficult, but doable:

答案很难，但可行：

Situation is: user Aand tablespace X

情况是：用户A和表空间X

import your dump file into a different database (this is only necessary if you need to keep a copy of the original one)
rename tablespace
alter tablespace Xrename to Y
create a directory for the expdp command en grant rights
create a dump with expdp
remove the old user and old tablespace (Y)
create the new tablespace (Y)
create the new user (with a new name) - in this case B- and grant rights (also to the directory created with step 3)
import the dump with impdp
impdp B/B directory=DIR dumpfile=DUMPFILE.dmp logfile=LOGFILE.log REMAP_SCHEMA=A:B

将您的转储文件导入不同的数据库（仅当您需要保留原始文件的副本时才需要这样做）
重命名表空间
将表空间X重命名为Y
为 expdp 命令创建一个目录并授予权限
使用 expdp 创建转储
删除旧用户和旧表空间（Y）
创建新表空间（Y）
创建新用户（使用新名称） - 在本例中为B- 并授予权限（也授予使用第 3 步创建的目录）
使用impdp导入转储
impdp B/B 目录=DIR dumpfile=DUMPFILE.dmp logfile=LOGFILE.log REMAP_SCHEMA= A: B

and that's it...

就是这样......

Answer 9

回答by masterxilo

Because I wanted to import (to Oracle 12.1|2) a dump that was exported from a local development database (18c xe), and I knew that all my target databases will have an accessible tablespace called DATABASE_TABLESPACE, I just created my schema/user to use a new tablespace of that name instead of the default USERS(to which I have no access on the target databases):

因为我想对imp（到 Oracle 12.1|2）exp从本地开发数据库 (18c xe ) 进行转储，并且我知道我的所有目标数据库都有一个名为的可访问表空间DATABASE_TABLESPACE，所以我刚刚创建了我的架构/用户使用该名称的新表空间而不是默认值USERS（我无法访问目标数据库）：

-- don't care about the details
CREATE TABLESPACE DATABASE_TABLESPACE
  DATAFILE 'DATABASE_TABLESPACE.dat' 
    SIZE 10M
    REUSE
    AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

ALTER DATABASE DEFAULT TABLESPACE DATABASE_TABLESPACE;

CREATE USER username
  IDENTIFIED BY userpassword
  CONTAINER=all;

GRANT create session TO username;
GRANT create table TO username;
GRANT create view TO username;
GRANT create any trigger TO username;
GRANT create any procedure TO username;
GRANT create sequence TO username;
GRANT create synonym TO username;
GRANT create synonym TO username;
GRANT UNLIMITED TABLESPACE TO username;

An expcreated from this makes imphappy on my target.

exp由此创建的一个让imp我对我的目标感到满意。

Answer 10

回答by Ahmad Shakil

---Create new tablespace:

---创建新表空间：

CREATE TABLESPACE TABLESPACENAME DATAFILE 'D:\ORACL\ORADATA\XE\TABLESPACEFILENAME.DBF' SIZE 350M AUTOEXTEND ON NEXT 2500M MAXSIZE UNLIMITED LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT MANUAL FLASHBACK ON;

CREATE TABLESPACE TABLESPACENAME DATAFILE 'D:\ORACL\ORADATA\XE\TABLESPACEFILENAME.DBF' SIZE 350M AUTOEXTEND ON NEXT 2500M MAXSIZE UNLIMITED LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE ONFLAGEMANS

---and then import with below command

---然后使用以下命令导入

CREATE USER BVUSER IDENTIFIED BY VALUES 'bvuser' DEFAULT TABLESPACE TABLESPACENAME

-- where D:\ORACL is path of oracle installation

-- 其中 D:\ORACL 是 oracle 安装路径

如何在不同的表空间中导入 oracle 转储

提问by Michiel Overeem

采纳答案by Andrew

回答by Andrew

回答by Grebets Kostyantyn

回答by Matthew Watson

回答by Michiel Overeem

回答by Dmitry

回答by peter

回答by Michiel Overeem

回答by masterxilo

回答by Ahmad Shakil

相关推荐

最近更新

标签

如何在不同的表空间中导入 oracle 转储

提问by Michiel Overeem

采纳答案by Andrew

回答by Andrew

回答by Grebets Kostyantyn

回答by Matthew Watson

回答by Michiel Overeem

回答by Dmitry

回答by peter

回答by Michiel Overeem

回答by masterxilo

回答by Ahmad Shakil

相关推荐

multithreading 如何在此线程 TCPServer 中的线程之间共享数据？

multithreading JavaFX 使用线程和 GUI

multithreading 计时器和 javafx

multithreading 如何正确销毁pthread互斥锁

相关推荐

最近更新

标签