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
. . exporting table repcat$_conflict
. . exporting table repcat$_ddl
. . exporting table repcat$_exceptions
. . exporting table repcat$_extension
. . exporting table repcat$_flavors
. . exporting table repcat$_flavor_objects
. . exporting table repcat$_generated
. . exporting table repcat$_grouped_column
. . exporting table repcat$_instantiation_ddl
. . exporting table repcat$_key_columns
. . exporting table repcat$_object_parms
. . exporting table repcat$_object_types
. . exporting table repcat$_parameter_column
. . exporting table repcat$_priority
. . exporting table repcat$_priority_group
. . exporting table repcat$_refresh_templates
. . exporting table repcat$_repcat
. . exporting table repcat$_repcatlog
. . exporting table repcat$_repcolumn
. . exporting table repcat$_repgroup_privs
. . exporting table repcat$_repobject
. . exporting table repcat$_repprop
. . exporting table repcat$_repschema
. . exporting table repcat$_resolution
. . exporting table repcat$_resolution_method
. . exporting table repcat$_resolution_statistics
. . exporting table repcat$_resol_stats_control
. . exporting table repcat$_runtime_parms
. . exporting table repcat$_sites_new
. . exporting table repcat$_site_objects
. . exporting table repcat$_snapgroup
. . exporting table repcat$_template_objects
. . exporting table repcat$_template_parms
. . exporting table repcat$_template_refgroups
. . exporting table repcat$_template_sites
. . exporting table repcat$_template_status
. . exporting table repcat$_template_targets
. . exporting table repcat$_template_types
. . exporting table repcat$_user_authorizations
. . exporting table repcat$_user_parm_values
. . exporting table sqlplus_product_profile
. about to export outln's tables via conventional path ...
. . exporting table ol$
. . exporting table ol$hints
. . exporting table ol$nodes
. about to export dbsnmp's tables via conventional path ...
. about to export scott's tables via conventional path ...
. . exporting table test
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
export terminated successfully without warnings.
5.将表空间及数据库其它对象导入
[oracle@rhel ~]$imp \'/ as sysdba\' transport_tablespace=y file=expdat.dmp datafiles=('/home/db/oracle/oradata/mbs/users01.dbf')
[oracle@rhel ~]$ imp \'/ as sysdba\' file=full.dmp ignore=y full=y
import: release 10.2.0.1.0 - production on wed jul 18 02:23:19 2012
copyright (c) 1982, 2005, oracle. all rights reserved.
connected to: oracle database 10g enterprise edition release 10.2.0.1.0 - production
with the partitioning, olap and data mining options
export file created by export:v09.02.00 via conventional path
warning: the objects were exported by system, not by you
import done in us7ascii character set and al16utf16 nchar character set
import server uses zhs16gbk character set (possible charset conversion)
. importing system's objects into system
imp-00017: following statement failed with oracle error 1119:
create temporary tablespace tempts1 blocksize 8192 tempfile '/home/db/or
acle/oradata/mbs/temp01.dbf' size 20971520 extent management local u
niform. size 1048576
imp-00003: oracle error 1119 encountered
ora-01119: error in creating database file '/home/db/oracle/oradata/mbs/temp01.dbf'
ora-27038: created file already exists
additional information: 1
imp-00017: following statement failed with oracle error 959:
alter user sys identified by values '8a8f025737a9097a' temporary tablespa
ce tempts1
imp-00003: oracle error 959 encountered
ora-00959: tablespace 'tempts1' does not exist
imp-00017: following statement failed with oracle error 959:
alter user system identified by values '2d594e86f93b17a1' temporary table
space tempts1
imp-00003: oracle error 959 encountered
ora-00959: tablespace 'tempts1' does not exist
imp-00061: warning: object type system.repcat$_object_null_vector already exists with a different identifier
create type repcat$_object_null_vector timestamp '2012-07-18:01:24:33' oi
d 'c509b28c83dba46fe040007f0100762c' as object
(
-- type owner, name, hashcode for the type represented by null_vector
type_owner varchar2(30),
type_name varchar2(30),
type_hashcode raw(17),
-- null_vector for a particular object instance
-- robj revisit: should only contain the null image, and not version#
null_vector raw(2000)
)
. importing sys's objects into sys
. importing system's objects into system
. importing sys's objects into sys
. importing system's objects into system
. importing outln's objects into outln
. importing dbsnmp's objects into dbsnmp
. importing scott's objects into scott
. importing system's objects into system
. importing sys's objects into sys
. importing system's objects into system
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_audit_attribute add constraint repcat$_audit_attribu
te_c1 check ((data_type_id in (2, 4, 5, 6, 7) and
data_length is not null)
or (data_type_id not in (2, 4, 5, 6, 7) and
data_length is null)
) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_conflict add constraint repcat$_conflict_c1 check (
conflict_type_id in (1, 2, 3)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_extension add constraint repcat$_extension_code che
ck (extension_code in (0)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_extension add constraint repcat$_extension_exportreq
check (export_required in ('y', 'n')) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_extension add constraint repcat$_extension_status c
heck (extension_status in (0, 1, 2, 3, 4)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_extension add constraint repcat$_extension_push_to_m
def check (push_to_mdef in ('y', 'n')) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_extension add constraint repcat$_extension_push_to_n
ew check (push_to_new in ('y', 'n')) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_flavors add constraint repcat$_flavors_c2 check (pu
blished is null or (published in ('y','n','o'))) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_flavor_objects add constraint repcat$_flavor_objects
_version check (version# >= 0 and version#
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_generated add constraint repcat$_generated_obj chec
k (reason in (0, 1, 2, 3, 4, 5, 6, 7, 9, 10)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_priority_group add constraint repcat$_priority_group
_c1 check (data_type_id in (1, 2, 3, 4, 5, 6, 7)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_priority_group add constraint repcat$_priority_group
_c2 check ((data_type_id in (4, 7) and
fixed_data_length is not null)
or (data_type_id in (1, 2, 3, 5, 6) and
fixed_data_length is null)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_refresh_templates add constraint refresh_templates_c
1 check ((public_template in ('y','n'))
or public_template is null) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repcat add constraint repcat$_repcat_status check (
status in (0, 1, 2)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repcatlog add constraint repcat$_repcatlog_request
check (request in (-1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17,
18, 19, 20, 21, 22, 23, 24, 25)) ena
ble novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repcatlog add constraint repcat$_repcatlog_type che
ck (type in (-1, 0, 1, 2, 4, 5, 7, 8, 9, 11, 12, -3,
13, 14, 32, 33)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repcatlog add constraint repcat$_repcatlog_status c
heck (status in (0, 1, 2, 3, 4)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repcolumn add constraint repcat$_repcolumn_version
check (version# >= 0 and version#
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repobject add constraint repcat$_repobject_type che
ck (type in (-1, 1, 2, 4, 5, 7, 8, 9, 11, 12, -3,
-4, 13, 14, 32, 33)) enable novalidat
e
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repobject add constraint repcat$_repobject_version
check (version# >= 0 and version#
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repobject add constraint repcat$_repobject_status c
heck (status in (0, 1, 2, 3, 4, 5, 6)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repobject add constraint repcat$_repobject_genpackag
e check (genpackage in (0, 1, 2)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repobject add constraint repcat$_repobject_gentrigge
r check (gentrigger in (0, 1, 2)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_repprop add constraint repcat$_repprop_how check (h
ow in (0, 1, 2, 3)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_sites_new add constraint repcat$_sites_new_full_inst
check (full_instantiation in ('y', 'n')) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_template_objects add constraint repcat$_template_obj
ects_ver check (object_version# >= 0 and object_version#
ovalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_template_parms add constraint repcat$_template_parms
_c1 check (user_override in ('y','n')) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_template_sites add constraint repcat$_template_sites
_c1 check (status in (-100,-1,0,1)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
imp-00017: following statement failed with oracle error 2264:
alter table repcat$_template_sites add constraint repcat$_template_sites
_c2 check ((site_name is not null and repapi_site_id is null) or
(site_name is null and repapi_site_id is not null)) enable novalidate
imp-00003: oracle error 2264 encountered
ora-02264: name already used by an existing constraint
. importing outln's objects into outln
. importing scott's objects into scott
. importing system's objects into system
. importing sys's objects into sys
. importing system's objects into system
. importing sys's objects into sys
. importing system's objects into system
. importing sys's objects into sys
. importing system's objects into system
. importing sys's objects into sys
. importing system's objects into system
. importing sys's objects into sys
. importing system's objects into system
. importing sys's objects into sys
. importing system's objects into system
. importing sys's objects into sys
. importing system's objects into system
. importing outln's objects into outln
. importing system's objects into system
. importing outln's objects into outln
. importing system's objects into system
. importing outln's objects into outln
. importing system's objects into system
. importing sys's objects into sys
. importing system's objects into system
about to enable constraints...
import terminated successfully with warnings.
6.编译失效对象
sql> @?/rdbms/admin/utlrp.sql
sql> select owner,object_name,object_type,status from dba_objects where status 'valid';
no rows selected
7.确认数据已经被导入到10g
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 140
aux_80bnvnr8_.dbf
users /home/db/oracle/oradata/mbs/users01.dbf 100
sql> select count(*) from scott.test;
count(*)
----------
5786