oracle表空间可以转移吗,Oracle表空间的迁移

最新推荐文章于 2024-07-13 08:30:00 发布

亦雨初

最新推荐文章于 2024-07-13 08:30:00 发布

阅读量330

点赞数

文章标签： oracle表空间可以转移吗

表空间的迁移

传输表空间的迁移方式快速，便捷，不仅可以在同版本同平台中使用，而且还是在不同版本不同平台中使用，从而到达升级的目的。不过TTS有如下的一些限制：

The source and target database must use the same character set and national character set.

You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.

Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.

Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes, but you must use the IMP and EXP utilities, not Data Pump. When using EXP, ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).

SQL> select file_name from dba_data_files where tablespace_name = 'VSAT';

FILE_NAME

--------------------------------------------------------------------------------

/eqldisk01/oradata/vsat01.dbf

/eqldisk01/oradata/vsat02.dbf

/eqldisk01/oradata/vsat03.dbf

/eqldisk01/oradata/vsat04.dbf

/eqldisk01/oradata/vsat05.dbf

/eqldisk01/oradata/vsat06.dbf

/eqldisk01/oradata/vsat07.dbf

/eqldisk01/oradata/vsat08.dbf

/eqldisk01/oradata/vsat09.dbf

/eqldisk01/oradata/vsat10.dbf

/eqldisk01/oradata/vsat11.dbf

/eqldisk01/oradata/vsat12.dbf

/eqldisk01/oradata/vsat13.dbf

/eqldisk01/oradata/vsat14.dbf

/eqldisk01/oradata/vsat15.dbf

/eqldisk01/oradata/vsat16.dbf

/eqldisk01/oradata/vsat17.dbf

/eqldisk01/oradata/vsat18.dbf

-- 检查SOE表空间是否自包含

SQL> exec dbms_tts.TRANSPORT_SET_CHECK('SOE', true);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

SQL> show parameter compatible

compatible string 10.2.0.4.0 必须确保导出oracle的版本比要导入的版本高(要不会导入失败，已经验证)

-- 在传输表空间之前需要将表空间设置为只读

SQL> alter tablespace soe read only;

[oracle@oracle114 ~]$exp \"/ as sysdba\" file=vsat.dmp TRANSPORT_TABLESPACE=y TABLESPACES=vsat log=vsat.log

在导出是会出现： EXP-00091: Exporting questionable statistics

解决：

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

LINUX>export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

把/eqldisk01/oradata/vsat*.dbf文件和导出的dmp文件copy到新的主机上

最后：

-- 创建相应的用户并授予权限，我这里为了简单就直接给了dba权限

SQL>create user vsat identified by vsat;

SQL> grant connect,resource,dba to vsat;

把dmp文件导入：

imp '/ as sysdba' file=/data/vsat.dmp TRANSPORT_TABLESPACE=y TABLESPACES=vsat DATAFILES=('/data/oradata/vsat01.dbf','/data/oradata/vsat02.dbf','/data/oradata/vsat03.dbf','/data/oradata/vsat04.dbf','/data/oradata/vsat05.dbf','/data/oradata/vsat06.dbf','/data/oradata/vsat07.dbf',................'/data/oradata/vsat18.dbf')

用imp时出现：

ORA-00721: changes by release 10.2.0.4.0 cannot be used by release 10.2.0.1.0

解决：

改用impdp导入或把compatible改成10.2.0.4.0 ,主要是改init$ORACLE_SID.ora中的compatible，spfile是二进制文件不能直接修改。

若要彻底删除表，则使用语句：drop table purge;

清除回收站里的信息

清除指定表：purge table ;

清除当前用户的回收站：purge recyclebin;

清除所有用户的回收站：purge dba_recyclebin;

不放入回收站，直接删除则是：drop table xx purge;

出现的问题：

ORA-25254: time-out in LISTEN while waiting for a message