RedHat AS3U8 ORACLE92080
1.在一个数据库上建立表空间(yzhq)、用户(yzhq)和表(test1,test2,test3)，并设置表空间(yzhq)为只读
SQL> create tablespace yzhq datafile
2 '/opt/app/oracle/oradata/eall188/yzhq01.dbf' size 20m,
3 '/opt/app/oracle/oradata/eall188/yzhq02.dbf' size 20m;
表空间已创建。
SQL> create user yzhq identified by yzhq default tablespace yzhq;
用户已创建
SQL> grant connect,resource,dba to yzhq;
授权成功。
SQL> conn yzhq/yzhq
已连接。
SQL> create table test1 as select * from dba_tablespaces;
表已创建。
SQL> create table test2 as select * from dba_users;
表已创建。
SQL> create table test3 as select * from dba_objects;
表已创建。
SQL> conn /as sysdba
已连接。
SQL> alter tablespace yzhq read only;
表空间已更改。
2.做导出操作，并拷贝出表空间(yzhq)的数据文件（yzhq01.dbf，yzhq02.dbf）。
注意：只是导出了表的信息，后边没有行数的，导出的文件很小，因为数据都在数据文件里。
参数：tablespaces=yzhq transport_Tablespace=y
[oracle@eall188 oracle]$ exp 'system/yzhqpwd as sysdba' file=yzhq.dmp tablespaces=yzhq transport_Tablespace=y
Export: Release 9.2.0.8.0 - Production on 星期五 11月 9 12:04:19 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
已导出 ZHS16CGB231280 字符集和 AL16UTF16 NCHAR 字符集
注: 将不会导出表数据（行）
关于导出可传输的表空间元数据...
用于表空间 YZHQ...
. 正在导出群集定义
. 正在导出表定义
. . 正在导出表 TEST1
. . 正在导出表 TEST2
. . 正在导出表 TEST3
. 正在导出引用完整性约束条件
. 正在导出触发器
. 结束导出可传输的表空间元数据
在没有警告的情况下成功终止导出。
[oracle@eall188 oracle]$
3.在另一个数据库上创建用户(yzhq),将数据文件（yzhq01.dbf，yzhq02.dbf）放到指定位置，yzhq.dmp放到oracle主目录，然后做导入操作。
注意参数：TABLESPACES TRANSPORT_TABLESPACE DATAFILES
[oracle@ealldb2 oracle]$ sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on 星期五 11月 9 13:40:52 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> create user yzhq identified by yzhq;
[oracle@ealldb2 oracle]$ imp 'system/yzhqpwd as sysdba ' file=yzhq.dmp TABLESPACES=yzhq TRANSPORT_TABLESPACE=y DATAFILES=/opt/app/oracle/oradata/rheall/yzhq01.dbf, /opt/app/oracle/oradata/rheall/yzhq02.dbf
Import: Release 9.2.0.8.0 - Production on 星期五 11月 9 13:41:27 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
经由常规路径导出由EXPORT:V09.02.00创建的文件
关于导入可传输表空间元数据...
已经完成ZHS16CGB231280字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将SYS的对象导入到 SYS
. 正在将YZHQ的对象导入到 YZHQ
. . 正在导入表 "TEST1"
. . 正在导入表 "TEST2"
. . 正在导入表 "TEST3"
成功终止导入，但出现警告。
[oracle@ealldb2 oracle]$
4.查看成功后的信息
SQL> conn /as sysdba
已连接。
SQL> grant connect,resource,dba to yzhq;
授权成功。
SQL> conn yzhq/yzhq
已连接。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
TEST2 TABLE
TEST3 TABLE
SQL> select count(*) from test1;
COUNT(*)
----------
11
SQL>
从dba_tablespaces中查看plugged_in的状态信息，如果表空间是插入到数据库中的，那就为YES
**************************************************************************************
SQL> select tablespace_name, status, plugged_in from dba_tablespaces;
TABLESPACE_NAME STATUS PLU
------------------------------ --------- ---
SYSTEM ONLINE NO
UNDOTBS1 ONLINE NO
TEMP ONLINE NO
CWMLITE ONLINE NO
DRSYS ONLINE NO
EXAMPLE ONLINE NO
INDX ONLINE NO
ODM ONLINE NO
TOOLS ONLINE NO
USERS ONLINE NO
XDB ONLINE NO
TABLESPACE_NAME STATUS PLU
------------------------------ --------- ---
MY_TS ONLINE NO
YZHQ READ ONLY YES
已选择13行。
从v$datafile中的plugged_in中也可以看出，只不过这是看得是数据文件而已。
**********************************************************************
SQL> set linesize 180
SQL> col name format a50
SQL> select name, status, plugged_in from v$datafile;
NAME STATUS PLUGGED_IN
-------------------------------------------------- ------- ----------
/opt/app/oracle/oradata/rheall/system01.dbf SYSTEM 0
/opt/app/oracle/oradata/rheall/undotbs01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/cwmlite01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/drsys01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/example01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/indx01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/odm01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/tools01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/users01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/xdb01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/my_ts01.dbf ONLINE 0
NAME STATUS PLUGGED_IN
-------------------------------------------------- ------- ----------
/opt/app/oracle/oradata/rheall/yzhq02.dbf ONLINE 1
/opt/app/oracle/oradata/rheall/yzhq01.dbf ONLINE 1
已选择13行。
5.将表空间(yzhq)设置为可读写
SQL> alter tablespace yzhq read write;
表空间已更改。
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='TEST3';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
YZHQ TEST3 YZHQ
SQL> insert into test3 select * from test3;
已创建30030行。
SQL> commit;
SQL> create table kkk (id number) tablespace yzhq;
表已创建。
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='KKK';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
YZHQ KKK YZHQ
SQL> select tablespace_name, status, plugged_in from dba_tablespaces;
TABLESPACE_NAME STATUS PLU
------------------------------ --------- ---
SYSTEM ONLINE NO
UNDOTBS1 ONLINE NO
TEMP ONLINE NO
CWMLITE ONLINE NO
DRSYS ONLINE NO
EXAMPLE ONLINE NO
INDX ONLINE NO
ODM ONLINE NO
TOOLS ONLINE NO
USERS ONLINE NO
XDB ONLINE NO
TABLESPACE_NAME STATUS PLU
------------------------------ --------- ---
MY_TS ONLINE NO
YZHQ ONLINE YES
已选择13行。
可以看到这时的v$datafile中的plugged_in状态信息变了
===================================================
SQL> select name, status, plugged_in from v$datafile;
NAME STATUS PLUGGED_IN
-------------------------------------------------- ------- ----------
/opt/app/oracle/oradata/rheall/system01.dbf SYSTEM 0
/opt/app/oracle/oradata/rheall/undotbs01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/cwmlite01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/drsys01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/example01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/indx01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/odm01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/tools01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/users01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/xdb01.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/my_ts01.dbf ONLINE 0
NAME STATUS PLUGGED_IN
-------------------------------------------------- ------- ----------
/opt/app/oracle/oradata/rheall/yzhq02.dbf ONLINE 0
/opt/app/oracle/oradata/rheall/yzhq01.dbf ONLINE 0
已选择13行。
######################################################################################################
报错：
1。AS SYSDBA
=============
[oracle@eall188 oracle]$ exp system/yzhqpwd file=yzhq.dmp tablespaces=yzhq transport_Tablespace=y
Export: Release 9.2.0.8.0 - Production on 星期五 11月 9 11:44:03 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
EXP-00044: 必须用 'AS SYSDBA' 连接来进行时间点恢复或可传输的表空间导入
EXP-00000: 导出终止失败
解决：
[oracle@eall188 oracle]$ exp 'system/yzhqpwd as sysdba' file=yzhq.dmp tablespaces=yzhq transport_Tablespace=y
2. DBMS_PLUGTS.NEWTABLESPACE
=============================
英文：
[oracle@ealldb2 oracle]$ imp system/yzhqpwd file=yzhq.dmp TABLESPACES=yzhq TRANSPORT_TABLESPACE=y DATAFILES=/opt/app/oracle/oradata/rheall/yzhq01.dbf, /opt/app/oracle/oradata/rheall/yzhq02.dbf
Import: Release 9.2.0.8.0 - Production on Fri Nov 9 11:20:57 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
Warning: the objects were exported by SYS, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16CGB231280 character set (possible charset conversion)
export client uses ZHS16CGB231280 character set (possible charset conversion)
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_PLUGTS.NEWTABLESPACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
IMP-00000: Import terminated unsuccessfully
[oracle@ealldb2 oracle]$
中文：
[oracle@ealldb2 oracle]$ imp system/yzhqpwd file=yzhq.dmp TABLESPACES=yzhq TRANSPORT_TABLESPACE=y DATAFILES=/opt/app/oracle/oradata/rheall/yzhq01.dbf, /opt/app/oracle/oradata/rheall/yzhq02.dbf
Import: Release 9.2.0.8.0 - Production on 星期五 11月 9 12:58:18 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
经由常规路径导出由EXPORT:V09.02.00创建的文件
关于导入可传输表空间元数据...
警告: 此对象由 SYS 导出, 而不是当前用户
已经完成ZHS16CGB231280字符集和AL16UTF16 NCHAR 字符集中的导入
IMP-00003: 遇到 ORACLE 错误 6550
ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必须说明标识符 'DBMS_PLUGTS.NEWTABLESPACE'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
IMP-00000: 未成功终止导入
[oracle@ealldb2 oracle]$
解决：
[oracle@ealldb2 oracle]$ imp 'system/yzhqpwd as sysdba ' file=yzhq.dmp TABLESPACES=yzhq TRANSPORT_TABLESPACE=y DATAFILES=/opt/app/oracle/oradata/rheall/yzhq01.dbf, /opt/app/oracle/oradata/rheall/yzhq02.dbf
或者：
If you are importing the tablespace as a user other than 'SYS', create a
public synonym for SYS.DBMS_PLUGTS as DBMS_PLUGTS else you would get the
errors similar to the following
SQL> conn /as sysdba
已连接。
SQL> create public synonym DBMS_PLUGTS for SYS.DBMS_PLUGTS
2 ;
同义词已创建。
SQL> grant execute on DBMS_PLUGTS to becvx;
授权成功。
[oracle@ealldb2 oracle]$ imp becvx/becvx file=yzhq.dmp TABLESPACES=yzhq TRANSPORT_TABLESPACE=y DATAFILES=/opt/app/oracle/oradata/rheall/yzhq01.dbf, /opt/app/oracle/oradata/rheall/yzhq02.dbf
Import: Release 9.2.0.8.0 - Production on 星期五 11月 9 16:06:46 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
经由常规路径导出由EXPORT:V09.02.00创建的文件
关于导入可传输表空间元数据...
警告: 此对象由 SYS 导出, 而不是当前用户
已经完成ZHS16CGB231280字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将SYS的对象导入到 BECVX
. 正在将YZHQ的对象导入到 YZHQ
. . 正在导入表 "TEST1"
. . 正在导入表 "TEST2"
. . 正在导入表 "TEST3"
成功终止导入，但出现警告。
[oracle@ealldb2 oracle]$
3.sys.dbms_plugts.checkUser('YZHQ')
===================================
英文：
[oracle@ealldb2 oracle]$ imp 'system/yzhqpwd as sysdba ' file=yzhq.dmp TABLESPACES=yzhq TRANSPORT_TABLESPACE=y DATAFILES=/opt/app/oracle/oradata/rheall/yzhq01.dbf, /opt/app/oracle/oradata/rheall/yzhq02.dbf
Import: Release 9.2.0.8.0 - Production on Fri Nov 9 13:09:43 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16CGB231280 character set (possible charset conversion)
export client uses ZHS16CGB231280 character set (possible charset conversion)
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 29342:
"BEGIN sys.dbms_plugts.checkUser('YZHQ'); END;"
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user YZHQ does not exist in the database
ORA-06512: at "SYS.DBMS_PLUGTS", line 1594
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
[oracle@ealldb2 oracle]$
中文：
[oracle@ealldb2 oracle]$ imp 'system/yzhqpwd as sysdba ' file=yzhq.dmp TABLESPACES=yzhq TRANSPORT_TABLESPACE=y DATAFILES=/opt/app/oracle/oradata/rheall/yzhq01.dbf, /opt/app/oracle/oradata/rheall/yzhq02.dbf
Import: Release 9.2.0.8.0 - Production on 星期五 11月 9 13:01:53 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
经由常规路径导出由EXPORT:V09.02.00创建的文件
关于导入可传输表空间元数据...
已经完成ZHS16CGB231280字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将SYS的对象导入到 SYS
IMP-00017: 由于 ORACLE 的 29342 错误，以下的语句失败
"BEGIN sys.dbms_plugts.checkUser('YZHQ'); END;"
IMP-00003: 遇到 ORACLE 错误 29342
ORA-29342: 数据库中不存在用户 YZHQ
ORA-06512: 在"SYS.DBMS_PLUGTS", line 1594
ORA-06512: 在line 1
IMP-00000: 未成功终止导入
[oracle@ealldb2 oracle]$ vi .bash_profile
解决：
SQL> create user yzhq identified by yzhq;
Solution Description:
=====================
Users with tables in the exported tablespace should exist in the target
database prior to initiating the import.
Create the user reported by the error message. If there is more than one user
whose table have been exported from the source database, all of them need to
be created in the target database.
Explanation:
============
The metadata exported from the target database does not contain enough
information to create the user in the target database.
The reason is that, if the metadata contained the user details, it might
ovewrite the privileges of an existing user in the target database, ie, if
the user by the same name already exists in the target database. By not
maintaining the user details, we preserve the security of the database.
4.SETUP
=======
The required scripts for transportable tablespaces are run by catproc.sql
itself. Check that DBMS_PLUGTS and DBMS_TTS are valid. If not, run the
following scripts
$ORACLE_HOME/rdbms/admin/catplug.sql
$ORACLE_HOME/rdbms/admin/dbmsplts.sql
$ORACLE_HOME/rdbms/admin/prvtplts.plb
as 'SYS' user.
[@more@]
可传输表空间(转并验证)
-
xlfsy
2010-10-12 13:37:21 -
数据库开发技术
- 原创