|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 8i / 9i
OS Details :
Sun Solaris9
Often
we are faced with the challenge of having to transfer large volumes of
schema or table data across databases. Some of the viable options in
achieving fast data transfers would be (a) Transportable tablespaces
(b) Using CTAS (Create Table As Select) and (c) Using the COPY
command. In this article, we will take a look at each of these
methods.
(1) Transportable Tablespaces
Transportable
Tablespaces give us the option to transfer data between databases at
the tablespace level. With 9i, the OS type and version need to match
between the databases but with 10G, tablespaces can be transported
across OS environments. Please be aware that tablespaces can be
transported from a lower database version to a higher database version
and not the other way around (Oracle does not support it even though
you might be able to transport from higher to lower database version).
Included below is a simple example on how to transport tablespaces
across databases. This example assumes that the database and OS
versions match between the source and destination.
(Step a): Check to
see if the tablespaces to be transported are self-contained.
SQL> exec
dbms_tts.transport_set_check('TEST_TBS1,TEST_TBS2')
-- Query the
transport_set_violations view to find any violations
SQL> select * from
transport_set_violations;
-- Fix any violation
before continuing
(Step b): Get a
listing of all datafiles for the tablespaces that are being
transported
SQL> select name
from dba_data_files where tablespace_name in
('TEST_TBS1','TEST_TBS2');
(Step c): Make the
tablespaces to be transported READ-ONLY
SQL> alter
tablespace TEST_TBS1 read only;
SQL> alter
tablespace TEST_TBS2 read only;
(Step d): Export the
tablespaces to be transported:
exp
\'sys/<password> as sysdba\' file=tbs_transport tablespaces=test_tbs1,test_tbs2
transport_tablespace=y
(Step e): Copy the
datafiles listed in step b above and the export dump file to the
destination machine location using the appropriate OS commands.
(Step f): Put the
tablespace back to read-write within the source database
(Step g): On the
destination machine, do an import to load the tablespace details in to
the dictionary:
imp
\'sys/<password> as sysdba\' file=tbs_transport transport_tablespace=y
datafiles=<destination_directory_structure>/test_tbs1_data01.dbf, <destination_directory_structure>/test_tbs2_data01.dbf
At this point you are pretty
much done. Make sure that the sequences for the tables that came along
with the tablespaces match between the source and the destination
databases. Also, reset any lookup data that these tables might have
dependencies on.
(2) Create Table As Select (CTAS):
The CTAS method of transferring data between databases is one of the faster methods due to the
following reasons:
(i) With the use of NOLOGGING option, very minimal redo logs for dictionary operations are generated
(ii) Very minimal rollback for dictionary operations are generated.
(iii) Table blocks are written directly to the disk
An example usage :
SQL> create table test_table
nologging
tablespace user_tbs
<optional storage clause>
as select * from source_Table@dblink;
The CTAS creates a data segment in the target tablespace and marks this segment as TEMPORARY in
dictionary. On completion of the CTAS the dictionary type is changed from TEMPORARY to TABLE.
A few things to watch out for when using the CTAS method:
(i) If data transfer's done between databases then the query used by CTAS will have to use a
database link. There is a chance for the execution plan for the query to change when it is submitted
remotely. Make sure you check the execution plan for the statement with the database link included
in the SELECT statement.
(ii) Oracle will ignore the FIRST_ROWS hint when used within the query that's part of CTAS
(iii) After the tables have been successfully created using the NOLOGGING option with CTAS, shutdown
the database and take an offline backup since these tables cannot be recovered in the event of a
database crash.
(3) COPY Command:
This is an oracle provided SQL*PLUS utility that provides faster means of transferring data between
databases. It is particularly useful when copying data in to oracle from a non-oracle database or if
copying data for tables with LONG datatypes. An advantage to using the COPY method for data
transfer is the ability to transfer data and insert in bulks. SQL*PLUS settings, arraysize and copycommit
can be changed to allow bulk data transfer. Here's an example:
-- The two settings defined below will ensure that COPY will commit after every (5000 * 2) 10,000 rows
SQL> set arraysize 5000
SQL> set copycommit 2
SQL> copy from from source_user/source_user@sourcedb insert tablecopy using select * from -
source_table
A few things to watch out for when using this method:
(i) Columns may lose precision when you copy between databases.
(ii) SQLPLUS has restrictions on how long the SELECT statement can be when used with COPY. If it
complains about the statement being too long then create a view for that statement use the view with
the COPY.
|
