The DBA can use additional features of the Export (EXP) and Import (IMP) utilities that a user can use to save and restore database objects.for backing up all user objects in the database or to copy a tablespace to another database. The tablespace copy feature, introduced in Oracle9i, is known as transportable tablespaces. It is a very convenient way to copy all objects in a tablespace to another database, without needing to specify individual objects in the tablespace.
transportable tablespace : A feature of Oracle’s Import and Export utilities that allows a tablespace to be copied to another database. All objects within the tablespace to be copied must be self-contained; in other words, a table in a tablespace to be copied must have its associated indexes in the same tablespace.
At Scott’s widget company, there are two primary databases:
- The OLTP database (ORD), which contains the online widget order system and the HR tables. It has the EMPLOYEES, DEPARTMENTS, and other tables.
- The data warehouse database (WH), which contains summaries of orders processed on the online system. Analysts use this summarized information to do “what-if” analyses to predict sales for the upcoming fiscal year.
On a weekly basis, Janice, the DBA, needs to copy the transactions from the online database to the data warehouse database. She decides that using transportable tablespaces is the most convenient and efficient way to move this data, as there are hundreds of tables in several different schemas that need to be merged into the data warehouse.
In the online database, Janice reviews the available tablespaces:
connect janice/janice@ord;
Connected.
select tablespace_name, status, contents from dba_ tablespaces;
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
12 rows selected.
The TO_DATAMART tablespace contains the tables that need to go to the data warehouse database. The first step in copying a tablespace to another database is to make it read-only:
|
1 |
alter tablespace to_datamart read only; |
|
1 |
Tablespace altered. |
Next, Janice uses Export (EXP) to save the characteristics of the tablespace to a dump file. Note that the contents of the tablespace are not saved to the dump file; only the information about the objects in the tablespace is saved. She will use the datafiles that make up the tablespace to copy the data. In the following EXP command, Janice creates the dump file for the TO_DATAMART tablespace:
|
1 |
E:\TEMP>exp transport_tablespace=y |
|
1 |
tablespaces=to_datamart file=exp_mart.dmp |
|
1 |
Export: Release 10.1.0.2.0 - Production on |
|
1 |
Mon Jun 21 22:57:53 2004 |
|
1 |
Copyright (c) 1982, 2004, Oracle. All rights reserved. |
|
1 |
Connected to: Oracle Database 10g Enterprise Edition |
|
1 |
Release 10.1.0.2.0 - Production |
|
1 |
With the Partitioning, OLAP and Data Mining options |
|
1 |
Export done in WE8MSWIN1252 character set |
|
1 |
and AL16UTF16 NCHAR character set |
|
1 |
server uses WE8ISO8859P1 character set |
|
1 |
(possible charset conversion) |
|
1 |
Note: table data (rows) will not be exported |
|
1 |
About to export transportable tablespace metadata... |
|
1 |
For tablespace TO_DATAMART ... |
|
1 |
. exporting cluster definitions |
|
1 |
. exporting table definitions |
|
1 |
. . exporting table INVENTORIES |
|
1 |
. . exporting table SALES001 |
|
1 |
. . exporting table SALES002 |
|
1 |
... |
|
1 |
. . exporting table SALES226 |
|
1 |
. . exporting table CUSTOMERS |
|
1 |
. exporting referential integrity constraints |
|
1 |
. exporting triggers |
|
1 |
. end transportable tablespace metadata export |
|
1 |
Export terminated successfully without warnings. |
|
1 |
E:\TEMP> |
In the next step, Janice copies the datafiles that compose the TO_DATAMART tablespace to the directory location where the rest of the data warehouse datafiles reside. Janice uses the data dictionary views V$TABLESPACE and V$DATAFILE to determine the operating system files that compose the TO_DATAMART tablespace:
|
1 |
select d.name "Filenames" |
|
1 |
from v$tablespace t, v$datafile d |
|
1 |
where t.ts# = d.ts# |
|
1 |
and t.name = 'TO_DATAMART'; |
|
1 |
Filenames |
|
1 |
--------------------------------------- |
|
1 |
D:\ORACLE\ORADATA\ORD\TO_DATAMART.ORA |
|
1 |
1 row selected. |
Janice uses a standard operating system copy command to make a copy of the tablespace in the new database: D:\> copy d:\oracle\oradata\ord\to_datamart.ora
d:\oracle\oradata\wh
|
1 |
1 file(s) copied. |
|
1 |
D:\> |
Back in the online database, Janice changes the source tablespace back to read-write: connect janice/janice@ord ;
Connected.
|
1 |
alter tablespace to_datamart read write; |
|
1 |
Tablespace altered. |
At this point, the source database is back to its original state, the information about the TO_DATAMART tablespace has been saved to a dump file, and a copy of the TO_DATAMART tablespace datafile is ready to attach to the data warehouse database. Janice will run Import (IMP) to attach the tablespace to the data warehouse database, using many of the same options she used with Export to create the tablespace dump file:
E:\TEMP>imp transport_tablespace=y file=exp_mart.dmp
datafiles=(‘d:\oracle\oradata\wh\to_datamart.ora’)
|
1 |
tablespaces=to_datamart |
|
1 |
Import: Release 10.1.0.2.0 - Production |
|
1 |
on Mon Jun 21 23:16:21 2004 |
|
1 |
Copyright (c) 1982, 2004, Oracle. |
|
1 |
All rights reserved. |
|
1 |
Username: janice as sysdba |
|
1 |
Password: |
|
1 |
Connected to: Oracle Database 10g Enterprise Edition |
|
1 |
Release 10.1.0.2.0 - Production |
|
1 |
With the Partitioning, OLAP and Data |
|
1 |
Mining options |
|
1 |
Export file created by EXPORT:V10.01.00 |
|
1 |
via conventional path |
|
1 |
About to import transportable tablespace(s) metadata... |
|
1 |
import done in WE8MSWIN1252 character set |
|
1 |
and AL16UTF16 NCHAR character set |
|
1 |
. importing SYS's objects into SYS |
|
1 |
. importing RJB's objects into RJB |
|
1 |
. . importing table "INVENTORIES" |
|
1 |
. . importing table "SALES001" |
|
1 |
. . importing table "SALES002" |
|
1 |
... |
|
1 |
. . importing table "SALES226" |
|
1 |
. . importing table "CUSTOMERS" |
|
1 |
Import terminated successfully without warnings. |
|
1 |
E:\TEMP> |
A copy of the TO_DATAMART tablespace is now attached to the data warehouse database and ready for use by the marketing analysts: connect janice/janice@wh;
Connected.
|
1 |
select tablespace_name, status, contents |
|
1 |
from dba_tablespaces |
|
1 |
where tablespace_name = 'TO_DATAMART'; |
| TABLESPACE_NAME | STATUS | CONTENTS |
| TO_DATAMART | READ ONLY | PERMANENT |
|
1 |
1 row selected. |
Before the tablespace can be imported again into the data warehouse database, it must be taken offline and dropped. It is assumed that any objects in the TO_DATAMART tablespace are copied to other tablespaces shortly after the TO_DATAMART tablespace is imported.
