oracle 9i增加表空间,使用可传输表空间将Oracle 9i升级到10g

最新推荐文章于 2021-04-15 21:16:00 发布
最新推荐文章于 2021-04-15 21:16:00 发布
阅读量173 收藏
点赞数
文章标签： oracle 9i增加表空间

使用可传输表空间将Oracle 9i升级到10g

[日期：2012-09-01]

来源：Linux社区

作者：lsq_008

[字体：大 中 小]

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(*)

----------

      57860b1331709591d260c1c78e86d0c51c18.png

Kinetic AC
关注
Oracle 9i/10g/11g数据库升级路线图总览
小二
01-04 990
注意： Oracle 10.2.0.1 不能直接升级到11gR2，至少需要先升级到10.2.0.2 以后才可以升级到11gR2 原因： To find time zone file version on sourcedatabase (10g) run SQL> select * from v$timezone_file;        If time zone file ver
Oracle 9i创建用户和表空间并赋予权限
jkant的专栏
03-04 7291
/*create tablespace(后面指定其为users default tablespace)*/--DROP TABLESPACE HOT;/*tablespace HOT只能在此删除*/--删除时先切换用户，保证有足够权限DROP USER jkant CASCADE;create tablespace HOT datafile D:/MyOracleDB/HOT.dbf s
Oracle10g之传输表空间详解(原创)
czmmiao的专栏
11-18 243
可传输表空间概述 Oracle 的可传输表空间特性通过将 元数据和数据文件 简单地从一个数据库移动到另一个数据库，提供 在数据库之间有效移动大数据的一种简易方法。代替重新创建对象，可移植表空间可以让 毫不费力地移动大对象，而所花费的时间是你手动创建这些对象的时间。 可移植表空间包括将属于源数据库的所有数据文件拷贝到目标数据库，并将关于表空间 数据目录信息从...
oracle 9i增加表空间,Oracle9i的临时表空间
weixin_30950075的博客
04-04 183
在Oracle9i中，Oracle引入了真正意义上的临时文件，在创建临时表空间时Oracle增加了一个新的关键字tempfile , 在Oracle9i的创建数据库的脚本中，我们可以看到Oracle的这一改变：CREATEDATABASEeygleMAXINSTANCES1MAXLOGHISTORY1MAXLOGFILES5MAXLOGMEMBERS3MAXDATAFILES100D...
oracle9i创建表空间
navy_xue的专栏
01-22 1472
文章转自：http://www.cnblogs.com/netsql/articles/1745978.html 注意点： 1.如果在PL/SQL 等工具里打开的话，直接修改下面的代码中[斜体加粗部分]执行 2.确保路径存在，比如【D:\oracle\oradata\Oracle9i\】也就是你要保存文件的路径存在 /*分为四步 *//*第1步：创建临时表空间  */ creat
oracle9i表空间扩容,我的Oracle 9i学习日志（12）-- 表空间和数据文件
weixin_31478029的博客
04-04 275
表空间和数据文件•表空间：–某一时刻只能属于一个数据库–由一个或多个数据文件组成–可进一步划分为逻辑存储单元•数据文件：–只能属于一个表空间和一个数据库–是方案对象数据的资料档案库表空间类型:SYSTEM表空间：•随数据库创建•所有数据库均需要•包括数据字典(内含存储程序单元)•包含SYSTEM还原段•应不包括用户数据，尽管允许这样做非SYSTEM 表空间(包括永久表空间，还原表空间，临时表空间)...
Oracle 9i 11g历史库升级迁移数据至19c CDB.docx
12-25
本文档主要介绍了如何将Oracle 9i/11g的历史数据库升级并迁移数据至Oracle 19c的多租户环境(CDB)的过程。升级的目标之一是归档历史数据，同时保持原有的字符集。 #### 需求概述 - 升级的数据库版本包括9.2.0.7、...
oracle9i全备份导入到11g说明
03-01
当将Oracle 9i的数据全备份导入到Oracle 11g版本时，经常会遇到由于`db_block_size`参数不一致导致的导入失败问题。这种情况下，如果直接进行导入操作，很可能会出现如下的错误提示：“ORA-02236: invalid filename...
查询Oracle数据库表空间和数据文件方法
03-03
下面以oracle9i为例，详细介绍查询Oracle数据库表空间信息和数据文件信息的方法。一、查看Oracle数据库中表空间信息的方法1、查看Oracle数据库中表空间信息的工具方法：使用oracleenterprisemanagerconsole工具，这...
Oracle9i中MapInfo空间表整体结构分析.pdf
10-10
在将MapInfo空间数据从Oracle 8.1.6版本升级到9i版本时，由于9i引入了新的空间索引创建方法，可能会遇到问题。通过理解这个整体结构，可以解决因索引方式差异导致的问题。文章中提到的方法就是利用这个整体结构的...
oracle9i310.rar
02-26
由于其资源占用较小，因此特别适合这些环境下使用，同时也能连接到更高版本的Oracle数据库，如10g和11g。 Oracle 9i客户端主要包含以下组件和功能： 1. **Oracle Net Services**：这是客户端与服务器通信的核心...
Oracle9i补丁升级说明（For Windows）
枫的专栏
11-05 5824
此说明针对：oracle9i从9201到9208升级oracle安装文件和补丁汇总下载地址链接：http://space.itpub.net/7177886/viewspace-281077安装前的准备     1.在数据库服务器上以Administrator身份登录到Windows系统。     2.停止所有Oracle服务。oracle服务名称通常是以Oracle打头的。     3.
oracle9i新建数据库的用户有哪些,Oracle 9i数据库的用户创办以及权限分配
weixin_31613447的博客
04-03 119
当前位置:我的异常网» 数据库»Oracle 9i数据库的用户创办以及权限分配Oracle 9i数据库的用户创办以及权限分配www.myexceptions.net网友分享于：2013-07-05浏览：8次Oracle 9i数据库的用户创建以及权限分配http://www.360doc.com/content/10/0516/00/1441142_27808340.shtml1.cre...
oracle升到9i,ORACLE9I升级到10G
weixin_42520448的博客
04-15 172
ORACLE9I升级到10G的简要说明：Win下Oracle9208如何升级到Oracle10.2.0.11、 关闭原9208数据库，执行一个完全冷备。2、 安装ORACLE10ｇ，选择一个跟9208不同的Home，安装的时候选择不装数据库。3、 以SYSDBA身份登陆到SQLPLUS，执行：Sql>spool upgrade.infoSql>@ oracle10G_HOMErdbms...
升级oracle 9i到10g
weixin_34025051的博客
06-27 128
从9i升级到10g两个方案可供选择:   一是利用oracle提供的一个升级实用程序dbua(database upgrade assistant)直接将9i的数据库升级到10g。    再有就是新建一个10g的数据库,利用oracle的导入/导出(imp/exp)实用程序迁移数据。1.首先来讲第一种方案(用升级程序:dbua):适合:  这种方案适宜于数据量比较大,用导入导出工具来迁移可能要花...
Oracle9i从9201升级到9206步骤
rede
03-15 350
转 Oracle 9i 从9.2.0.1升级到 9.2.0.6 步骤https://blog.csdn.net/tianlesoftware/article/details/4843836 一. 安装前的准备 1.在数据库服务器上以Administrator身份登录到Windows 2.停止所有Oracle相关的服务。这些服务名称通常是以Oracle打头 3.停止Distributed Tran...
oracle升到9i,Oracle9i升级至Oracle10g数据库最快捷的方法
weixin_39975744的博客
04-15 146
1、在安装好Oracle 10g软件的目标服务器上创建和源数据库9i 数据文件，控制文件，redo文件相同的目录(两台数据库的数据文件的位1、在安装好Oracle 10g软件的目标服务器上创建和源数据库9i 数据文件，控制文件，redo文件相同的目录(两台数据库的数据文件的位置必须相同)2、由关闭源数据库9i，拷贝9i的数据文件，控制文件，redo文件，密码文件，，初始化参数文件，spfile 文...
oracle 9.2.0.1下载,Oracle 9i 从9.2.0.1升级到 9.2.0.6 步骤
weixin_35695652的博客
04-04 161
一. 安装前的准备1.在数据库服务器上以Administrator身份登录到Windows2.停止所有Oracle相关的服务。这些服务名称通常是以Oracle打头3.停止Distributed Transaction Coordinator服务4.备份与Oracle数据库相关的所有文件(包括Oracle数据库软件及数据文件)，把这些文件按原有的目录结构复制到安全的地方，并做好标记或记录二. 安装...
oracle一个创建用户、创建表空间、授权、建表的完整过程
sw3828的博客
11-09 1559
1.首先我们可以用scott用户以sysdba的身份登录oracle.  Sql代码   conn scott/tiger as sysdba   2.然后我就可以来创建用户了.  Sql代码   create user zzg identified by zzg123;   3.创建好用户我们接着就可以修改用户的密码. 
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值