Table space that can be transferred

Table space that can be transferred
Oracle2271

There is an important concept for the table space that can be transferred: Self-Contained ).
During tablespace transmission, the tablespace set must be self-contained. The self-contained indicates that the internal tablespace set used for transmission does not reference the external tablespace set. There are two types of self-contained table space: generally self-contained table space set and completely (strictly) Self-contained table space set.
The following are common violations of the self-contained principle:
The tablespace index is in the internal tablespace set, while the table is in the external tablespace set (conversely, if the table is in the internal tablespace set and the index is in the external tablespace set, the self-contained principle is not violated ).
A partitioned table is a part of the internal tablespace set and a part of the external tablespace set. (For a partitioned table, it is either completely included or not included in the internal tablespace set ).
If the constraint is transmitted when the tablespace is transmitted at the same time, the reference Integrity Constraint is applied. If the constraint points to the table in the external tablespace set, the self-contained constraint is violated. If the constraint is not transmitted, it has nothing to do with the constraints.
The tablespace is in the internal tablespace set, while the lob is in the external tablespace set, which violates the self-contained constraints.
Generally, you can use the system package DBMS_TTS to check whether the space is self-contained. verification can be performed in two ways: Non-strict mode and strict mode.
The following is a simple verification process. Assume that an eygle table exists in the eygle tablespace, and its indexes are stored in the USERS tablespace:

SQL> create table eygle as select rownum id, username from dba_users;
Table created.

SQL> create index ind_id on eygle (id) tablespace users;
Index created.

Perform a non-strict self-contained check (full_check = false) as the SYS User ):

SQL> connect/as sysdba
Connected.
SQL> exec dbms_tts.transport_set_check ('eygl', TRUE );
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
No rows selected

Perform a strict self-contained check (full_check = true ):

SQL> exec dbms_tts.transport_set_check ('eygl', TRUE, True );
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
Bytes --------------------------------------------------------------------------------------
Index EYGLE. IND_ID in tablespace USERS points to table EYGLE. EYGLE in tablespace EYGLE

In turn, for the USERS tablespace, non-strict checks cannot pass:

SQL> exec dbms_tts.transport_set_check ('users', TRUE );
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
Bytes ----------------------------------------------------------------------------------------
Index EYGLE. IND_ID in tablespace USERS points to table EYGLE. EYGLE in tablespace EYGLE

However, if multiple tablespaces can be transmitted at the same time, some self-contained issues can be solved:

SQL> exec dbms_tts.transport_set_check ('users, eygl', TRUE, True );
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
No rows selected

The official explanation is as follows:

There may be logical or physical dependencies between objects in the transportable set and those outside of the set. you can only transport a set of tablespaces that is self-contained. in this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. some examples of self contained tablespace violations are:

• An index inside the set of tablespaces is for a table outside of the set of tablespaces.

  Note:

  It is not a violation if a corresponding index for a table is outside of the set of tablespaces.

• A partitioned table is partially contained in the set of tablespaces.

  The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. if you want to transport a subset of a partition table, you must exchange the partitions into tables.

• A referential integrity constraint points to a table except ss a set boundary.

  When transporting a set of tablespaces, you can choose to include referential integrity constraints. however, doing so can affect whether or not a set of tablespaces is self-contained. if you decide not to transport constraints, then the constraints are not considered as pointers.

• A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of tablespaces.

• An xml db schema (*. xsd) that was registered by user A imports a global schema that was registered by user B, and the following is true: the default tablespace for user A is tablespace, the default tablespace for user B is tablespace B, and only tablespace A is wrongly ded in the set of tablespaces.

To determine whether a set of tablespaces is self-contained, you can invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. you must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS) to execute this procedure.

When you invoke the DBMS_TTS package, you specify the list of tablespaces in the transportable set to be checked for self containment. you can optionally specify if constraints must be supported ded. for strict or full containment, you must additionally set the TTS_FULL_CHECK parameter to TRUE.

The strict or full containment check is for cases that require capturing not only references going outside the transportable set, but also those coming into the set. tablespace Point-in-Time Recovery (TSPITR) is one such case where dependent objects must be fully contained or fully outside the transportable set.

For example, it is a violation to perform. TSPITR on a tablespace containing a table t but not its index I because the index and data will be inconsistent after the transport. A full containment check ensures that there are no dependencies going outside or coming into the transportable set. see the example for TSPITR in the Oracle Database Backup and Recovery User's Guide.

Incl_constraints

TRUE if you want to count in referential integrity constraints when examining if the set of tablespaces is self-contained. (Theincl_constraints parameter is a default so that TRANSPORT_SET_CHECK will work if it is called with only the ts_list argument .)

Full_check

Indicates whether a full or partial dependency check is required. if TRUE, treats all IN and OUT pointers (dependencies) and captures them as violations if they are not self-contained in the transportable set. the parameter shocould be set to TRUE for TSPITR or if a strict version of transportable is desired. by default the parameter is set to false. it will only consider OUT pointers as violations.

1.3.5 convertible tablespace

In many Oracle documents, you may have noticed a tablespace used for testing in Oracle. This tablespace contains a series of Preset users and data that can be used for many test experiments in databases or BI.
This tablespace can be selected when you use the template to create a database. On the interface 1-22, you can select the tablespace that contains this example when creating a database (this is not selected by default ).

If the inclusion example scheme is selected, the cloneDBCreation. SQL script is changed, and the following statements are added:

Connect "SYS"/"& sysPassword" as SYSDBA
@ C: \ oracle \ 10.2.0 \ demo \ schema \ mkplug. SQL & sysPassword change_on_install
Change_on_install C: \ oracle \ 10.2.0 \ assistants \ dbca \ templates \ example. dmp C: \ oracle \ 10.2.0 \ assistants \ dbca \ templates \ example01.dfb C: \ oracle \ oradata \ eygle \ example01.dbf C: \ oracle \ admin \ eygle \ scripts \ "'sys/& sysPassword as sysdb '";

Here, the files in the template directory are referenced again:

C: \> dir C: \ oracle \ 10.2.0 \ assistants \ dbca \ templates \ ex *
The volume in drive C is SYSTEM
The serial number of the volume is 8C88-D1B4.

C: \ oracle \ 10.2.0 \ assistants \ dbca \ templates directory

983,040 example. dmp
20,897,792 example01.dfb
2 files in 21,880,832 bytes
0 directories, 915,578,880 available bytes

Use the mkplug. SQL script to load the example tablespace. Let's take a look at the main content of this script.
Similarly, the most important thing is to restore the data file from the example01.dfb file through the dbms_backup_restore package:

SELECT TO_CHAR (systimestamp, 'yyyymmdd HH: MI: ss') FROM dual;
Variable new_datafile varchar2 (512)
Declare
Done boolean;
V_db_create_file_dest VARCHAR2 (512 );
Devicename varchar2 (255 );
Data_file_id number;
Rec_id number;
Stamp number;
Resetlogs_change number;
Creation_change number;
Checkpoint_change number;
Blksize number;
Omfname varchar2 (512 );
Real _file_name varchar2 (512 );

Begin
Dbms_output.put_line ('');
Dbms_output.put_line ('allocating device ....');
Dbms_output.put_line ('specifying datafiles ...');
Devicename: = dbms_backup_restore.deviceAllocate;
Dbms_output.put_line ('specifing datafiles ...');
Select max (file_id) + 1 INTO data_file_id FROM dba_data_files;
SELECT value INTO v_db_create_file_dest FROM v $ parameter WHERE name = 'db _ create_file_dest ';
IF v_db_create_file_dest IS NOT NULL
THEN
Dbms_backup_restore.restoreSetDataFile;
Dbms_backup_restore.getOMFFileName ('example ', omfname );
Dbms_backup_restore.restoreDataFileTo (data_file_id, omfname, 0, 'example ');
ELSE
Dbms_backup_restore.restoreSetDataFile;
Dbms_backup_restore.restoreDataFileTo (data_file_id, '& data_file_name ');
End if;
Dbms_output.put_line ('restoring ...');
Dbms_backup_restore.restoreBackupPiece ('& data_file_backup', done );
SELECT max (recid) INTO rec_id FROM v $ datafile_copy;

-- Now get the real file name. It cocould be also OMF filename
SELECT name, stamp, resetlogs_change #, creation_change #, checkpoint_change #, block_size
INTO real_file_name, stamp, resetlogs_change, creation_change, checkpoint_change, blksize
From v $ DATAFILE_COPY
WHERE recid = rec_id and file # = data_file_id;

-- Uncatalog the file from V $ DATAFILE_COPY. This important.
Dbms_backup_restore.deleteDataFileCopy (recid => rec_id,
Stamp => stamp,
Fname => real_file_name,
Dfnumber => data_file_id,
Resetlogs_change => resetlogs_change,
Creation_change => creation_change,
Checkpoint_change => checkpoint_change,
Blksize => blksize,
No_delete => 1,
Force => 1 );
-- Set the bindvariable to the real filename
: New_datafile: = real_file_name;

If done then
Dbms_output.put_line ('Restore done .');
Else
Dbms_output.put_line ('ORA-XXXX: Restore failed ');
End if;
End;
/

After the recovery is completed, the most important part is to import the example tablespace to the current database through the transfer tablespace technology.
In this case, when performing cross-database migration, what methods should we use to migrate the data in one user tablespace to another database? The most common practice may be to use the EXP tool to export all data and then import IMP to the target database. However, this method may be slow. The EXP tool also provides another technology-the table space transfer technology, which can be used to speed up this process.
In the help of exp-help, you can see this parameter:
TRANSPORT_TABLESPACE export the table space metadata that can be transferred (N)

With this option, we can export only metadata for a set of self-contained and read-only tablespaces, and then copy the data files of these tablespaces to the target platform at the operating system layer, import the metadata into the data dictionary (this process is called insert and plugging) to complete the migration.
There is an important concept for the table space that can be transferred: Self-Contained ).
During tablespace transmission, the tablespace set must be self-contained. The self-contained indicates that the internal tablespace set used for transmission does not reference the external tablespace set. There are two types of self-contained table space: generally self-contained table space set and completely (strictly) Self-contained table space set.
The following are common violations of the self-contained principle:
The tablespace index is in the internal tablespace set, while the table is in the external tablespace set (conversely, if the table is in the internal tablespace set and the index is in the external tablespace set, the self-contained principle is not violated ).
A partitioned table is a part of the internal tablespace set and a part of the external tablespace set. (For a partitioned table, it is either completely included or not included in the internal tablespace set ).
If the constraint is transmitted when the tablespace is transmitted at the same time, the reference Integrity Constraint is applied. If the constraint points to the table in the external tablespace set, the self-contained constraint is violated. If the constraint is not transmitted, it has nothing to do with the constraints.
The tablespace is in the internal tablespace set, while the lob is in the external tablespace set, which violates the self-contained constraints.
Generally, you can use the system package DBMS_TTS to check whether the space is self-contained. verification can be performed in two ways: Non-strict mode and strict mode.
The following is a simple verification process. Assume that an eygle table exists in the eygle tablespace, and its indexes are stored in the USERS tablespace:

SQL> create table eygle as select rownum id, username from dba_users;
Table created.

SQL> create index ind_id on eygle (id) tablespace users;
Index created.

Perform a non-strict self-contained check (full_check = false) as the SYS User ):

SQL> connect/as sysdba
Connected.
SQL> exec dbms_tts.transport_set_check ('eygl', TRUE );
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
No rows selected

Perform a strict self-contained check (full_check = true ):

SQL> exec dbms_tts.transport_set_check ('eygl', TRUE, True );
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
Bytes --------------------------------------------------------------------------------------
Index EYGLE. IND_ID in tablespace USERS points to table EYGLE. EYGLE in tablespace EYGLE

In turn, for the USERS tablespace, non-strict checks cannot pass:

SQL> exec dbms_tts.transport_set_check ('users', TRUE );
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
Bytes ----------------------------------------------------------------------------------------
Index EYGLE. IND_ID in tablespace USERS points to table EYGLE. EYGLE in tablespace EYGLE

However, if multiple tablespaces can be transmitted at the same time, some self-contained issues can be solved:

SQL> exec dbms_tts.transport_set_check ('users, eygl', TRUE, True );
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
No rows selected

After the tablespace self-contained is confirmed, it is easy to transmit the tablespace. The following steps are generally taken.
(1) set the tablespace to read-only:
Alter tablespace users read only;
(2) Export the tablespace. Run the following command at the operating system prompt:
Exp username/passwd tablespaces = users transport_tablespace = y file = exp_users.dmp
The exported file contains only metadata, so the exported file is very small and the export speed will be very fast.
(3) transfer. Transfers the exported metadata file (exp_users.dmp) and the data file (user01.dbf) of the tablespace to the target host (if FTP is used during the transfer process, note that the binary mode is used ).
(4) transmission. Insert the tablespace into the database in the target database to transfer the tablespace. Run the following statement at the operating system command prompt:
Imp username/passwd tablespaces = users transport_tablespace = y file = exp_users.dmp datafiles = 'users01. dbf'

After learning about Oracle's portable tablespace technology, let's take a look at the insertion of the example tablespace. The following script is still from the mkplug. SQL script:

--
-- Importing the metadata and plugging in the tablespace at the same
-- Time, using the restored database file
--
DEFINE imp_logfile = & log_path.tts_example_imp.log

-- When importing use filename got after restore is finished
Host imp "'sys/& password_sys as sysdba '" transport_tablespace = y file = & imp_file log = & imp_logfile datafiles =' & datafile 'tablespaces = EXAMPLE tts_owners = hr, oe, pm, ix, sh

After plugging is completed, the tablespace is included in the newly created database.