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

Learn more about Oracle technology from database creation: Plugin operations secretly performed by mkplug in those years

In many Oracle documents, you may have noticed a tablespace that Oracle uses for testing, which has a series of preset users and data that can be used in many testing experiments in databases or BI.

This tablespace is optional when building a library using templates, and in the interface shown in the following figure, you can choose to include this sample tablespace when creating the library (unselected by default).

Does figure 1 contain a sample scheme?

During the configuration of DBCA, if you choose to include the example scenario, the resulting cloneDBCreation.sql script will change from the standard mode by adding the following statements (basically unchanged in 11g, 12c and other new versions):

See here, once again refer to the files in the template directory, and perform the PlugIN operation through the mkplug.sql script. If we find it a bit strange to see the Pluggable Database of Oracle 12c, then in fact, Oracle has been silently helping us to implement this operation for many years:

C:\ > dir C:\ oracle\ 10.2.0\ assistants\ dbca\ templates\ ex*

2005-09-07 13:02 983040 example.dmp

2005-09-07 13:02 20897792 example01.dfb

Take a look at the main content of the script by loading the sample tablespace through the mkplug.sql script.

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

After this recovery is complete, the next most important part is to import example tablespaces into the current database through the transport tablespace technology.

Consider this situation. When migrating across databases, you need to migrate data from one user tablespace to another database. What method should be used?

The most common approach may be to export all the data through the EXP tool and then IMP import on the target database, but this approach may be slow. The EXP tool also provides another technology, transportable tablespace technology, which can be used to speed up the process.

In the help of exp-help, you can see this parameter:

TRANSPORT_TABLESPACE exports table space metadata that can be transferred (N)

With this option, we can export only metadata for a set of self-contained, read-only tablespaces, then copy the data files for those tablespaces to the target platform at the operating system layer, and import the metadata into the data dictionary (a process called insert, plugging), which completes the migration.

Be careful

Transport tablespace technology cannot be applied to SYSTEM tablespaces.

There is an important concept of transportable tablespaces: Self-Contained.

In tablespace transfer, the tablespace set is required to be self-contained, which means that the internal tablespace set used for transfer has no reference to the external tablespace set. There are two types of self-inclusion: general self-contained tablespace sets and completely (strictly) self-contained tablespace sets.

The following common situations violate the principle of self-inclusion:

§the 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, it does not violate the self-inclusion principle).

§partitioned tables are partitioned in the internal tablespace set and partly in the external tablespace set (for partitioned tables, they are either all in the internal tablespace set or none).

§if the constraint is transferred while transferring the tablespace, for referential integrity constraints, the table pointed to by the constraint is in an external tablespace set, which violates the self-contained constraint; if the constraint is not transferred, it has nothing to do with the constraint pointing.

§the table is in the internal tablespace set, while the lob is listed in the external tablespace set, which violates the self-inclusion constraint.

You can usually check whether the tablespace is self-contained through the system package DBMS_TTS, and validation can be performed in two ways: non-strict and strict.

The following is a simple validation process, assuming that there is a table eygle in the eygle tablespace on which indexes are 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

Perform a non-strict self-inclusion check (full_check=false) as a SYS user:

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

Conversely, for USERS tablespaces, lax checks fail:

But if multiple tablespaces can be transferred at the same time, some self-contained problems can be solved:

After the tablespace self-inclusion confirmation, the tablespace transfer is very convenient, which generally includes the following steps.

1. Set the tablespace to read-only:

Alter tablespace users read only

two。 Export tablespaces. Execute at the operating system prompt:

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 speed is fast.

3. transfer.

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

4. Transmission.

Insert the tablespace into the database in 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 learning about Oracle's transportable tablespace technology, take a look at example tablespace insertion. The following script is still from the mkplug.sql script:

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

Enmo College, a subsidiary of Yunhe Enmo (Beijing) Information Technology Co., Ltd., is committed to providing professional and high-level oracle database and big data training services, mining and training big data and database talents. Enmo College provides a full range of big data and database technology training, including individual practical skills training, personal certification training, and enterprise internal training. ACE-level super teachers, equipped with professional laboratories, immersion learning and training, professional laboratories, equipped with professional teaching assistants to guide training. Can quickly integrate into the circle of experts, rich in resources in the industry, and quickly accumulate workplace contacts. Oracle database courses include: Oracle DBA practical class, Oracle OCM examination, Oracle OCP examination and so on.

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.