Export and Import for DBAs

 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;

TABLESPACE_NAME

STATUS

CONTENTS

SYSTEM

ONLINE

PERMANENT

UNDOTBS1

ONLINE

UNDO

TEMP

ONLINE

TEMPORARY

CWMLITE

ONLINE

PERMANENT

DRSYS

ONLINE

PERMANENT

EXAMPLE

ONLINE

PERMANENT

INDX

ONLINE

PERMANENT

ODM

ONLINE

PERMANENT

TOOLS

ONLINE

PERMANENT

USERS

ONLINE

PERMANENT

XDB

ONLINE

PERMANENT

TO_DATAMART

ONLINE

PERMANENT

 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: 

     alter tablespace to_datamart read only;

     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: 

     E:\TEMP>exp transport_tablespace=y

    tablespaces=to_datamart file=exp_mart.dmp

    Export: Release 10.1.0.2.0 - Production on

            Mon Jun 21 22:57:53 2004

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

 Connected to: Oracle Database 10g Enterprise Edition

            Release 10.1.0.2.0 - Production

   With the Partitioning, OLAP and Data Mining options

     Export done in WE8MSWIN1252 character set

           and AL16UTF16 NCHAR character set

     server uses WE8ISO8859P1 character set

           (possible charset conversion)

     Note: table data (rows) will not be exported

     About to export transportable tablespace metadata...

     For tablespace TO_DATAMART ...

     . exporting cluster definitions

     . exporting table definitions

     . . exporting table                    INVENTORIES

     . . exporting table                    SALES001

     . . exporting table                    SALES002

     ...

     . . exporting table                    SALES226

     . . exporting table                    CUSTOMERS

     . exporting referential integrity constraints

     . exporting triggers

     . end transportable tablespace metadata export

     Export terminated successfully without warnings.

   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: 

select d.name "Filenames"

from v$tablespace t, v$datafile d

where t.ts# = d.ts#

and t.name = 'TO_DATAMART';

 Filenames

---------------------------------------

 D:\ORACLE\ORADATA\ORD\TO_DATAMART.ORA

 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 file(s) copied.

   D:\>

Back in the online database, Janice changes the source tablespace back to read-write:  connect  janice/janice@ord ; 

  Connected. 

alter tablespace to_datamart read write;

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’)

        tablespaces=to_datamart

   Import: Release 10.1.0.2.0 - Production

          on Mon Jun 21 23:16:21 2004

   Copyright (c) 1982, 2004, Oracle.

          All rights reserved.

   Username: janice as sysdba

   Password:

 Connected to: Oracle Database 10g Enterprise Edition

           Release 10.1.0.2.0 - Production

 With the Partitioning, OLAP and Data

           Mining options

 Export file created by EXPORT:V10.01.00

      via conventional path

 About to import transportable tablespace(s) metadata...

     import done in WE8MSWIN1252 character set

        and AL16UTF16 NCHAR character set

     . importing SYS's objects into SYS

     . importing RJB's objects into RJB

     . . importing table                  "INVENTORIES"

     . . importing table                  "SALES001"

     . . importing table                  "SALES002"

     ...

     . . importing table                  "SALES226"

     . . importing table                  "CUSTOMERS"

 Import terminated successfully without warnings.

 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.

 select tablespace_name, status, contents

          from dba_tablespaces

          where tablespace_name = 'TO_DATAMART';

     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.