From database creation to learn Oracle technology in depth: those years mkplug secretly executed Plugin operations

in manyOracle documentation, Maybe everyone note too-Oracle A tablespace used for testing, This tablespace has a series of preconfigured users and data, Can be used for database orBI of many test experiments。

This tablespace is optional when building a library using a template, and in this screen shown below, you can choose to include this example tablespace when building a library (it is unselected by default).

Figure 1 Inclusion of example scenarios

During the DBCA configuration process, if you choose to include the example scenario, the final cloneDBCreation.sql script will change from the standard model, adding the following statements (largely unchanged in newer versions such as 11g and 12c).

See here, again referencing the files in the template directory, the PlugIN operation is performed via the mkplug.sql script, and if we see Oracle 12c's Pluggable Database and feel a little strange, then in fact Plugin is an operation that Oracle has been performing for us for years at Ink.

C:>dir C:oracle10.2.0assistantsdbca emplatesex* 2005-09-07 13:02 983,040 example.dmp 2005-09-07 13:02 20,897,792 example01.dfb

Load this example tablespace with the mkplug.sql script and take a look at the main contents of this script.

Again, the most important thing is to recover the data files from the example01.dfb file via the dbms_backup_restore package:.

Once this recovery is complete, the next most important part is to import the EXAMPLE tablespace into the current database via the transfer tablespace technique.

Consider this scenario, when doing a cross-database migration, where data from one user tablespace needs to be migrated to another database, what method should be used?

The most conventional approach might be to export all the data via the EXP tool and then IMP import it on the target database, but this method can be slow. The EXP tool also offers another technique - transportable tablespace technology - that can be used to speed up this process.

In the help for exp -help, one can see a parameter such as

TRANSPORT_TABLESPACE Exporting transportable tablespace metadata (N)

With this option, we can export only metadata for a set of self-contained, read-only tablespaces, then copy the data files of these tablespaces to the target platform at the OS level and import the metadata into the data dictionary (a process called inserting, plugging), i.e., the migration is complete.


Transfer tablespace technology cannot be applied to the SYSTEM tablespace.

There is an important concept for transportable tablespaces. Self-Contained (Self-Contained)

In a tablespace transfer, the tablespace set is required to be self-contained; self-contained means that the internal tablespace set used for the transfer has no references to the external tablespace set. There are two types of self-containment: general self-contained tablespace sets and fully (strictly) self-contained tablespace sets.

The following are common violations of the principle of self-containment.

  • The index is in the internal tablespace set and the table is in the external tablespace set (conversely, the self-containment principle is not violated if the table is in the internal tablespace set and the index is in the external tablespace set).
  • Partitioned tables are partly in the internal tablespace set and partly in the external tablespace set (for partitioned tables, either all of them are included in the internal tablespace set or none of them are).
  • If a constraint is transferred along with the tablespace, the self-contained constraint is violated for referential integrity constraints where the table pointed to by the constraint is in an external tablespace set; if the constraint is not transferred, it is irrelevant to the constraint pointing.
  • A table in the internal tablespace set and lob columns in the external tablespace set violates the self-contained constraint.

It is usually possible to check whether a tablespace is self-contained via the system package DBMS_TTS, and validation can be performed in two ways: non-strictly and strictly.

The following is a simple validation process that assumes the existence of a table eygle in the eygle tablespace on which indexes exist stored in the USERS tablespace:,.

SQL> create table eygle as select rownum id ,username from dba_users; SQL> create index ind_id on eygle(id) tablespace users;

To perform a non-strict self-contained check (full_check=false) as SYS user.

Perform strict self-containment check (full_check=true).

In turn for the USERS tablespace, non-strict checks will not pass.

However, some of the self-contained problems can be solved by simultaneous transfers to multiple tablespaces.

Once the tablespace self-inclusion is confirmed, it is easy to perform a tablespace transfer, which generally consists of the following steps.

1.To set the tablespace to read-only.

alter tablespace users read only;

2.Exporting tablespaces. At the operating system prompt, execute.

exp username/passwd tablespaces=users transport_tablespace=y file=exp_users.dmp

The export file here contains only metadata, so the export file is small and the export will be fast.


Transfer the exported metadata file (in this case exp_users.dmp) and the data file of the transfer tablespace (in this case the data file user01.dbf of the users tablespace) to the target host (care should be taken to use the binary method if the transfer process uses the FTP method).


Insert the tablespace into the database at the target database to complete the tablespace transfer. Execute 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 understanding Oracle's transportable tablespace technology, take a look at the EXAMPLE tablespace insert, the following script is still from the mkplug.sql script.

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

1、What areas will capital be in play in 2017
2、Mysql Backup and Restore xtrabackup
3、EXCLUSIVEAli smart speaker unveils first natural language processing results on eve of launch
4、The straight path to customer acquisition sales for SaaSenterprise services
5、PowerDesigner generates Access databases

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送