Shulou(Shulou.com)06/01 Report--

Transmit tablespaces TABLESPACES, TRANSPORT_TABLESPACE, TTS_FULL_CHECK

Use exp/imp to transfer tablespace data_tbs from library a to library b? Here is the preparatory work.

Create tablespace data_tbs

Create tablespace idx_tbs

Create table t (x varchar2 (10)) tablespace data_tbs

Create index idx_t on t (x) tablespaceidx_tbs

Insert into t

(1) created under the sys user (Note: the export is incorrect)

C:\ Documents and Settings\ Administrator > set oracle_sid=orcl

C:\ Documents and Settings\ Administrator > sqlplus sys/ymh assysdba

SQL*Plus: Release 10.2.0.1.0-Production on Friday June 24 10:36:38 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connect to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

SQL > CREATE TABLESPACE data_tbs

2 DATAFILE 'data_tbs' SIZE 100m AUTOEXTEND ONNEXT 100m MAXSIZE UNLIMITED LOGGING

3 EXTENT MANAGEMENT LOCAL SEGMENT SPACEMANAGEMENT AUTO

The tablespace has been created.

SQL > CREATE TABLESPACE idx_tbs

2 DATAFILE 'idx_tbs' SIZE 10m AUTOEXTEND ONNEXT 10m MAXSIZE UNLIMITED LOGGING

3 EXTENT MANAGEMENT LOCAL SEGMENT SPACEMANAGEMENT AUTO

The tablespace has been created.

SQL > create table t (x varchar2 (10)) tablespace data_tbs

The table has been created.

SQL > create index idx_t on t (x) tablespace idx_tbs

The index has been created.

SQL > insert into t select object_id from dba_objects

50407 rows were created.

SQL > insert into t select * from t

50407 rows were created.

SQL > commit

SQL > alter tablespace data_tbs read only

The tablespace has changed.

SQL > alter tablespace idx_tbs read only

The tablespace has changed.

SQL > Executesys.dbms_tts.transport_set_check ('data_tbs',TRUE,TRUE)

The PL/SQL process completed successfully.

SQL > SELECT * FROMsys.transport_set_violations

VIOLATIONS

Index SYS.IDX_T intablespace IDX_TBS points to table SYS.T intablespace DATA_T

BS

Sys owned object T intablespace DATA_TBS not allowed in pluggable set

SQL > delete from sys.transport_set_violations

2 lines have been deleted.

SQL > Executesys.dbms_tts.transport_set_check ('data_tbs,idx_tbs',TRUE,TRUE)

The PL/SQL process completed successfully.

SQL > SELECT * FROM sys.transport_set_violations

VIOLATIONS

Sys owned object IDX_Tin tablespace IDX_TBS not allowed in pluggable set

Sys owned object T intablespace DATA_TBS not allowed in pluggable set

SQL > exit

From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options disconnected

C:\ Documents and Settings\ Administrator > exp\ "sys/ymhas sysdba\" file=d:/tbs.dmp tablespaces= (data_tbs,idx_tbs) transport_Tab

Export: Release 10.2.0.1.0-Production on Friday June 24 11:16:40 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

Exported ZHS16GBK character set and AL16UTF16 NCHAR character set

Note: table data (rows) will not be exported

Transferable tablespace metadata is about to be exported.

EXP-00008: encountered ORACLE error 29341

ORA-29341: transportable sets are not self-contained

ORA-06512: in "SYS.DBMS_PLUGTS", line 1387

ORA-06512: in line 1

EXP-00000: export termination failed

C:\ Documents and Settings\ Administrator >

Note: there is a problem with sys users. For more information, please see the summary below.

(2) use scott user operations

C:\ Documents and Settings\ Administrator > sqlplus sys/ymh assysdba

SQL*Plus: Release 10.2.0.1.0-Production on Friday June 24 13:24:34 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connect to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

SQL > drop table t

Table has been deleted.

SQL > alter tablespace data_tbs read write

The tablespace has changed.

SQL > alter tablespace idx_tbs read write

The tablespace has changed.

SQL > conn scott/tiger

Connected.

SQL > create table T2 (x varchar2 (10)) tablespace data_tbs

The table has been created.

SQL > create index idx_t on T2 (x) tablespace idx_tbs

The index has been created.

SQL > insert into T2 select object_id from all_objects

46677 rows were created.

SQL > insert into T2 select object_id from all_objects

46677 rows were created.

SQL > commit

The submission is complete.

SQL > conn sys/ymh as sysdba

Connected.

SQL > Executesys.dbms_tts.transport_set_check ('data_tbs',TRUE,TRUE); = > single tablespace self-inclusion check

The PL/SQL process completed successfully.

SQL > SELECT * FROM sys.transport_set_violations;= > has an association. Self-inclusion check fails.

VIOLATIONS

Index SCOTT.IDX_T in tablespace IDX_TBS points to tableSCOTT.T2 in tablespace DATA_TBS

SQL > delete from sys.transport_set_violations

1 line has been deleted.

SQL > commit

The submission is complete.

SQL > Executesys.dbms_tts.transport_set_check ('data_tbs,idx_tbs',TRUE,TRUE); = > two associated table space self-inclusion checks

The PL/SQL process completed successfully.

SQL > SELECT * FROM sys.transport_set_violations;= > two are checked together.

No rows selected

SQL > alter tablespace data_tbs read only

The tablespace has changed.

SQL > alter tablespace idx_tbs read only

The tablespace has changed.

SQL > exit

From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options disconnected

C:\ Documents and Settings\ Administrator > exp\ "sys/ymhas sysdba\" file=d:/tbs.dmp tablespaces= (data_tbs,idx_tbs) transport_tablespace=y

Tts_full_check=y

Export two tablespaces together without data (tablespaces= (data_tbs,idx_tbs): the tablespace to be exported; transport_tablespace=y: export only metadata; tts_full_check=y for full self-inclusion check)

Export: Release 10.2.0.1.0-Production on Friday June 24 14:21:22 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

Exported ZHS16GBK character set and AL16UTF16 NCHAR character set

Note: table data (rows) will not be exported

Transferable tablespace metadata is about to be exported.

For tablespace DATA_TBS...

. Exporting cluster definitions

. Exporting table definition

. . Exporting table T2 = è No data export

For tablespace IDX_TBS...

. Exporting cluster definitions

. Exporting table definition

. Exporting referential integrity constraints

. Exporting triggers

. End the export of transferable table space metadata

The export was terminated successfully without warning.

(3) copy files

C:\ Documents and Settings\ Administrator > sqlplus sys/ymh assysdba

SQL*Plus: Release 10.2.0.1.0-Production on Friday June 24 13:44:37 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connect to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

SQL > select name from v$datafile where name like'% _ TBS'

NAME

D:\ ORACLE\ PRODUCT\ 10.2.0\ DB_1\ DATABASE\ DATA_TBS

D:\ ORACLE\ PRODUCT\ 10.2.0\ DB_1\ DATABASE\ IDX_TBS

SQL > exit

From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options disconnected

C:\ Documents and Settings\ Administrator > copyD:\ ORACLE\ PRODUCT\ 10.2.0\ DB_1\ DATABASE\ IDX_TBSD:\ oracle\ product\ 10.2.0\ oradata\ test

1 file has been copied.

C:\ Documents and Settings\ Administrator > copyD:\ ORACLE\ PRODUCT\ 10.2.0\ DB_1\ DATABASE\ DATA_TBSD:\ oracle\ product\ 10.2.0\ oradata\ test

1 file has been copied.

C:\ Documents and Settings\ Administrator > cd.. /..

C:\ > d:

D:\ > cd D:\ oracle\ product\ 10.2.0\ oradata\ test

D:\ oracle\ product\ 10.2.0\ oradata\ test > dir * _ tbs

The volume in drive D is installed

The serial number of the volume is 3451-8864

D:\ oracle\ product\ 10.2.0\ oradata\ test directory

2011-06-24 13:28 104865792 DATA_TBS

2011-06-24 13:28 10493952 IDX_TBS

2 files 115359744 bytes

0 directories 40376631296 available bytes

D:\ oracle\ product\ 10.2.0\ oradata\ test >

(4) Import another library

C:\ Documents and Settings\ Administrator > set oracle_sid=test

C:\ Documents and Settings\ Administrator > echo% oracle_sid%

Test

C:\ Documents and Settings\ Administrator > sqlplus sys/ymh assysdba

SQL*Plus: Release 10.2.0.1.0-Production on Friday June 24 13:54:59 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connect to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

SQL > show parameter service

NAME TYPE VALUE

Service_names string test

C:\ Documents and Settings\ Administrator > imp sys/ymh@test assysdba' file='d:/tbs.dmp' tablespaces= (data_tbs,idx_tbs) transport_tablespace=y

Datafiles='D:\ oracle\ product\ 10.2.0\ oradata\ test\ DATA_TBS,D:\ oracle\ product\ 10.2.0\ oradata\ test\ IDX_TBS

Import: Release 10.2.0.1.0-Production on Friday June 24 14:45:00 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

An export file created by EXPORT:V10.02.01 via a regular path

Transferable tablespace metadata is about to be imported.

The import of ZHS16GBK character set and AL16UTF16 NCHAR character set has been completed

. Importing objects from SYS into SYS

. Importing objects from SYS into SYS

. Importing objects from SCOTT into SCOTT

. . Importing table "T2"

. Importing objects from SYS into SYS

The import was terminated successfully without warning.

(5) check the data

C:\ Documents and Settings\ Administrator > sqlplusscott/tiger@test

SQL*Plus: Release 10.2.0.1.0-Production on Friday June 24 14:46:07 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connect to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

SQL > conn sys/ymh@test as sysdba

Connected.

SQL > show parameter service

NAME TYPE VALUE

Service_names string test

SQL > conn scott/tiger

Connected.

SQL > select count (1) from T2

COUNT (1)

-

93354

SQL >

Summary error: (1) ORA-29335: tablespace 'DATA_TBS' is not read-only

The exported tablespace must be read-only, or the following error occurs

EXP-00008: encountered ORACLE error 29335

ORA-29335: tablespace 'DATA_TBS' is not read-only

ORA-06512: in "SYS.DBMS_PLUGTS", line 507

ORA-06512: in line 1

EXP-00000: export termination failed

(2) ORA-29341: transportable sets are not self-contained

There are two ways to deal with transportable sets that are not self-contained:

1. Pass all the dependent tablespaces together. For example, in this question, there will be a self-test error in one table space, but not in two table spaces at the same time.

SQL > Executesys.dbms_tts.transport_set_check ('data_tbs',TRUE,TRUE)

SQL > SELECT * FROM sys.transport_set_violations

VIOLATIONS

Index SCOTT.IDX_T in tablespace IDX_TBS points to tableSCOTT.T2 in tablespace DATA_TBS

SQL > Executesys.dbms_tts.transport_set_check ('data_tbs,idx_tbs',TRUE,TRUE)

SQL > SELECT * FROM sys.transport_set_violations

No rows selected

SQL >

2. Treat tablespaces as self-contained: for example, delete the index, rebuild the index after importing to another database, or rebuild the index to the tablespace of the data file, and then export a tablespace

Note: when creating tables under sys or system users, the transportable set is not self-contained and cannot be transferred at the same time, so it is best to export the table spaces used by sys and system to build tables, whether it is the built-in system or the new table space.

EXP-00008: encountered ORACLE error 29341

ORA-29341: transportable sets are not self-contained

ORA-06512: in "SYS.DBMS_PLUGTS", line 1387

ORA-06512: in line 1

EXP-00000: export termination failed

(3) IMP-00053: import mode is not compatible with export dump file

1. It may be caused by different storage formats across platforms (not tested)

2. On the same platform, pay attention to the exported parameters and imported parameters, because you forgot to add transport_tablespace=y when exporting, and changed the data to export, and added this parameter when importing, resulting in an error:

IMP-00053: import mode is not compatible with export dump file

IMP-00000: import was not terminated successfully

(4) ORA-27041: unable to open file O/S-Error: (OS2) the system cannot find the specified file.

A common import error

IMP-00003: encountered ORACLE error 1565

ORA-01565: error identifying file'D:\ oracle\ product\ 10.2.0\ oradata\ test\ DATA_TBS'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

ORA-06512: in "SYS.DBMS_PLUGTS", line 1801

ORA-06512: in line 1

IMP-00000: import was not terminated successfully

(5) ORA-19722: incorrect version of data file D:\ oracle\ oradata\ DATA_TBS

The tablespace is set to read-only to ensure data consistency, so the read-write state of the tablespace can be restored in the source database only after the copy of the data file of the tablespace has been completed.

Otherwise, the following error occurs when the target database is imported:

IMP-00003: encountered ORACLE error 19722

ORA-19722: wrong version of data file D:\ oracle\ oradata\ DATA_TBS

ORA-06512: in "SYS.DBMS_PLUGTS", line 2065

ORA-06512: in line 1

IMP-00000: import was not terminated successfully

(6) PLS-00201: the identifier 'DBMS_PLUGTS.NEWTABLESPACE' must be declared

If the following error occurs when using ordinary users or system users to import, you can use 'sys/ymh as sysdba' to import and add the parameter TTS_OWNERS=scott (TTS_OWNERS has not been tested in detail)

The import of ZHS16GBK character set and AL16UTF16 NCHAR character set has been completed

IMP-00003: encountered ORACLE error 6550

ORA-06550: line 1, column 7:

PLS-00201: the identifier 'DBMS_PLUGTS.NEWTABLESPACE' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

IMP-00000: import was not terminated successfully

(7) OSD-04002: unable to open file O/S-Error: (OS123) incorrect syntax for file name, directory name, or volume label.

If there are multiple data files, datafile=XXX,XXX, separated by commas, do not summarize multiple data files with a "" sign, otherwise the following error will occur

IMP-00003: encountered ORACLE error 1565

ORA-01565: error identifying file'D:\ oracle\ oradata\ DATA_TBS,D:\ oracle\ oradata\ IDX_TBS'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 123) incorrect syntax for file name, directory name, or volume label.

ORA-06512: in "SYS.DBMS_PLUGTS", line 1801

ORA-06512: in line 1

IMP-00000: import was not terminated successfully

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.