匿名通过本文主要向大家介绍了使用,传输,空间,Oracle,升级,10g,Oracle,等相关知识,希望本文的分享对您有所帮助
1. 在Oracle 9i所在服务器上安装oracle 10g，并建库，创建用户scott，不创建用户表空间： SQL select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1
1. 在Oracle 9i所在服务器上安装oracle 10g，并建库，创建用户scott，不创建用户表空间：SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
------------------------------ -------------------------------------------------- ---------------
SYSTEM /home/db/Oracle/oradata/mbs/MBS/datafile/o1_mf_sys 410
tem_80bnvf2j_.dbf
UNDOTBS1 /home/db/Oracle/oradata/mbs/MBS/datafile/o1_mf_und 305
otbs1_80bnvm5g_.dbf
SYSAUX /home/db/Oracle/oradata/mbs/MBS/datafile/o1_mf_sys 130
aux_80bnvnr8_.dbf
SQL> create user scott identified by tiger;
User created.
SQL> grant dba to scott;
Grant succeeded.
2. 检查9i环境，确认需要传输的表空间是自包含的
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
------------------------------ -------------------------------------------------- ---------------
SYSTEM /home/db/Oracle/oradata/mbs/system01.dbf 325
UNDOTBS1 /home/db/Oracle/oradata/mbs/undotbs01.dbf 200
USERS /home/db/Oracle/oradata/mbs/users01.dbf 100
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='TEST';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS TEST USERS
SCOTT TEST USERS
SQL> EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('users',TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
------------------------------------------------------------------------------------------------------------------------------------
Sys owned object TEST in tablespace USERS not allowed in pluggable set
提示在users表空间中存在sys用户的表test，这不能包含在需要传输的表空间中。
将其删除：
SQL> drop table sys.test;
Table dropped.
SQL> EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('users',TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
no rows selected
没有返回，代表在表空间users与其他表空间不存在依赖关系
3.导出表空间中的元数据
--首先将表空间users设为read only
SQL> alter tablespace users read only;
Tablespace altered.
--导出表空间users中表的元数据
[Oracle@rhel ~]$ exp \'/ as sysdba\' TRANSPORT_TABLESPACE=y TABLESPACES=(users) TTS_FULL_CHECK=y
Export: Release 9.2.0.4.0 - Production on Wed Jul 18 02:15:28 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TEST
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
4. 导出数据库中其它对象，以full=y rows=n方式导出
[Oracle@rhel ~]$ exp SYSTEM/oracle FULL=y FILE=full.dmp ROWS=n statistics=none
Export: Release 9.2.0.4.0 - Production on Wed Jul 18 02:16:22 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table AQ$_INTERNET_AGENTS
. . exporting table AQ$_INTERNET_AGENT_PRIVS
. . exporting table DEF$_AQCALL
. . exporting table DEF$_AQERROR
. . exporting table DEF$_CALLDEST
. . exporting table DEF$_DEFAULTDEST
. . exporting table DEF$_DESTINATION
. . exporting table DEF$_ERROR
. . exporting table DEF$_LOB
. . exporting table DEF$_ORIGIN
. . exporting table DEF$_PROPAGATOR
. . exporting table DEF$_PUSHED_TRANSACTIONS
. . exporting table DEF$_TEMP$LOB
. . exporting table LOGSTDBY$APPLY_MILESTONE
. . exporting table LOGSTDBY$APPLY_PROGRESS
. . exporting table LOGSTDBY$EVENTS
. . exporting table LOGSTDBY$PARAMETERS
. . exporting table LOGSTDBY$PLSQL
. . exporting table LOGSTDBY$SCN
. . exporting table LOGSTDBY$SKIP
. . exporting table LOGSTDBY$SKIP_TRANSACTION
. . exporting table REPCAT$_AUDIT_ATTRIBUTE
. . exporting table REPCAT$_AUDIT_COLUMN
. . exporting table REPCAT$_COLUMN_GROUP
. .