如何用exp导出表空间问题

如何将exp出来的数据IMP到不同表空间

前几天部门经理在IMP数据时想将数据导入到不同于原系统的表空间去，却发现怎么也不成功，数据还是导入到原来的表空间。

我看了一下，他在新系统上创建的这个用户授予了connect,RESOURCE,dba等角色，而且这个用户有UNLIMITED TABLESPACE这个系统权限。

总的来说，Oracle并没有提供什么参数让你将数据导入到指定的表空间中，数据默认还是导入到它原来所在的表空间。你可以用IMP 的SHOW参数可以看到其创建脚本中会指定TABLESPACE参数，而这个值是原系统该对象所在的表空间，跟你用户默认的表空间是没有关系的，如下：

"CREATE TABLE "EMPLOYEES" ("EMPLOYEE_ID" NUMBER(6, 0), "FIRST_NAME" VARCHAR2"

"(20), "LAST_NAME" VARCHAR2(25) NOT NULL enable, "email" VARCHAR2(25) NOT NU"

"LL enable, "PHONE_NUMBER" VARCHAR2(20), "HIRE_date" date NOT NULL enable, ""

"JOB_ID" VARCHAR2(10) NOT NULL enable, "SALARY" NUMBER(8, 2), "COMMISSION_PC"

"T" NUMBER(2, 2), "MANAGER_ID" NUMBER(6, 0), "DEPARTMENT_ID" NUMBER(4, 0)) "

"PCTfree 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 freeLIS"

"TS 1 freeLIST GROUPS 1) TABLESPACE "example" LOGGING NOCOMPRESS"

. . 正在跳过表 "EMPLOYEES"

"CREATE TABLE "JOBS" ("JOB_ID" VARCHAR2(10), "JOB_TITLE" VARCHAR2(35) NOT NU"

"LL enable, "MIN_SALARY" NUMBER(6, 0), "MAX_SALARY" NUMBER(6, 0)) PCTfree 1"

"0 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 freeLISTS 1 free"

"LIST GROUPS 1) TABLESPACE "example" LOGGING NOCOMPRESS"

. . 正在跳过表 "JOBS"

看其中的红色部分，如果在原系统中你创建EMPLOYEES指定的表空间是example，而此时该用户的默认表空间是USERS，那么exp的dmp文件里是example，而不是USERS，当然如果你建表时没特意指定表空间，那么默认的就会是用户的默认表空间。

所以对于这个问题的解决方法是：

1．如果目标系统中不存在跟原系统一样的表空间，这个一样，是指存储你要导入的数据的表空间

如：你要导入的数据在原系统中是存放在USERS表空间的，而在目标系统并不存在这个表空间，那么你在导入数据时数据就会导入到目标系统中该用户的默认表空间

2． 如果存在一样的表空间，则在目标系统中：

a) REVOKE UNLIMITED TABLESPACE FROM 该USER

b) 取消该用户在原系统导出数据所在表空间的配额：

SQL>alter USER XXX QUOTA 0 ON OLD_TABLESPACE

c) 将你要存储导入数据的表空间设为该用户默认的表空间

d) 添加该用户在其默认表空间中的配额：

SQL>alter USER XXX QUOTA UNLIMITED ON NEW_TABLESPACE

3． 其他方法：

a) 可以用IMP的SHOW=Y将创建脚本SPOOL出来，然后修改其创建脚本中的TABLESPACE，将其修改成你所需要的表空间。

b) 用第三方工具，比如TOAD，产生其创建脚本，然后修改TABLESPACE值，然后导入的时候加IGNORE=Y进行导入。

c) 可以先导入数据，然后用TOAD的Rebuild Multi Objects，进行数据转移。

1、Oracle11g默认对空表不分配segment，故使用exp导出Oracle11g数据库时，空表不会导出。

2、设置deferred_segment_creation

参数为FALSE后，无论是空表还是非空表，都分配segment。

在sqlplus中，执行如下命令：

SQL>alter

system

set

deferred_segment_creation=false

查看：

SQL>show

parameter

deferred_segment_creation

该值设置后只对后面新增的表产生作用，对之前建立的空表不起作用。

3、可以使用手工为空表分配Extent的方式，来解决导出之前建立的空表的问题。说明如下：

3.1

使用ALLOCATE

EXTENT的说明

使用ALLOCATE

EXTENT可以为数据库对象分配Extent。其语法如下：

-----------

ALLOCATE

EXTENT

{

SIZE

integer

[K

|

M]

|

DATAFILE

'filename'

|

INSTANCE

integer

}

-----------

可以针对数据表、索引、物化视图等手工分配Extent。

ALLOCATE

EXTENT使用样例:

ALLOCATE

EXTENT

ALLOCATE

EXTENT(SIZE

integer

[K

|

M])

ALLOCATE

EXTENT(DATAFILE

'filename')

ALLOCATE

EXTENT(INSTANCE

integer)

ALLOCATE

EXTENT(SIZE

integer

[K

|

M]

DATAFILE

'filename')

ALLOCATE

EXTENT(SIZE

integer

[K

|

M]

INSTANCE

integer)

针对数据表 *** 作的完整语法如下：

-----------

ALTER

TABLE

[schema.]table_name

ALLOCATE

EXTENT

[({

SIZE

integer

[K

|

M]

|

DATAFILE

'filename'

|

INSTANCE

integer})]

-----------

故，需要构建如下样子简单的SQL命令：

-----------

alter

table

aTabelName

allocate

extent

-----------

3.2

构建对空表分配空间的SQL命令，

查询当前用户下的所有空表（一个用户最好对应一个默认表空间）。命令如下：

-----------

SQL>select

table_name

from

user_tables

where

NUM_ROWS=0

-----------

根据上述查询，可以构建针对空表分配空间的命令语句，如下：

-----------

SQL>Select

'alter

table

'||table_name||'

allocate

extent'

from

user_tables

where

num_rows=0

-----------

批量输出上述生成的SQL语句，建立C:\createsql.sql，其内容如下：

-----------

set

heading

off

set

echo

off

set

feedback

off

set

termout

on

spool

C:\allocate.sql

Select

'alter

table

'||table_name||'

allocate

extent'

from

user_tables

where

num_rows=0

spool

off

-----------

执行C:\createsql.sql，命令如下：

-----------

SQL>@

C:\createsql.sql

-----------

执行完毕后，得到C:\allocate.sql文件。

打开该文件会看到，已经得到对所有空表分配空间的命令SQL语句。

3.4

执行SQL命令，对空表分配空间：

执行C:\allocate.sql，命令如下：

-----------

SQL>@

C:\allocate.sql

-----------

执行完毕，表已更改。

3.4

此时执行exp命令，即可把包括空表在内的所有表，正常导出。

另外：Oracle11g中，对密码是大小写敏感的，即密码中的字母是区分大小写的。

在Oracle10g中及以前，密码中的字母大小写无所谓。

为保持数据文件的一致性，在导出自包含表空间数据字典信息之前，应首先将自包含表空间转变为只读状态，作用是使表空间信息不会发生变化。

SQL>alter

tablespace

local

read

only

SQL>alter

tablespace

local2

read

only

将表空间设置为只读关态时，会在表空间数据文件上发出检查点，并且其内容不会发生任何变化，此时就可以导出表空间信息了。

导出表空间仅仅是导出与其相关的数据字典信息，而不是导出表空间的任何数据，

使用如下 *** 作系统命令可导出表空间local和local2的信息。

D:\>EXP

transport_tablespace=y

tablespaces=local,local2

file=expdat.dmp

用户名：system/anchor@ORCL

as

sysdba

参数：transport_tablespace搬移表空间选项，Y表示导出表空间信息；

tablespaces用于指定要导出的表空间；file用于指定存放导出信息的文件。

导出表空间信息之后，使用 *** 作系统命令将表空间数据文件和导出文件(expdat.dmp)复制到目标数据库所在机器相应的目录中。

---