Load BLOBs using SQL Loader + Transportable Tablespace

1. Copy images to myunixsource server into /pics directory
   
   
2. Locate /pics directory and run the following:
   
   

cd /pics ls -l *.dat | awk '{print $9 ",/pics/" $9}' > /load/bloblist.txt  sed 's/.dat,/,/g' /load/bloblist.txt > /load/bloblist.dat

3. Create a new tablespace and a new table on EMULE database:
   
   
   

sys@EMULE> CREATE TABLESPACE MI_CTZN_BLOB_TEMP_TBS   2  DATAFILE '/database_emule/mi_ctzn_blob_temp_tbs1.dbf' SIZE 100M; Tablespace created. sys@EMULE> sys@EMULE>ALTER USER PICTURE QUOTA UNLIMITED ON MI_CTZN_BLOB_TEMP_TBS; sys@EMULE> conn picture@emule Enter password: ****** Connected. sys@EMULE> @pr picture@EMULE> CREATE TABLE MI_CTZN_BLOB_TEMP   2  ( ID NUMBER,   3    PIC BLOB ) TABLESPACE MI_CTZN_BLOB_TEMP_TBS; Table created.

4. Create myblob.ctl file in /load directory as per following format:
   
   
   

LOAD DATA   INFILE '/load/bloblist.dat'      INTO TABLE MI_CTZN_BLOB_TEMP      FIELDS TERMINATED BY ','      (id,     ext_file    FILLER,     pic     LOBFILE(ext_file) TERMINATED BY EOF)

5. Run sqlldr to load the table:
   
   sqlldr picture/*****@emule control=myblob.ctl rows=500
   
   
   
   
6. Check whether all the tablespaces for transport can be transported.
   
   

DECLARE   TS_LIST CLOB;   INCL_CONSTRAINTS BOOLEAN;   FULL_CHECK BOOLEAN; BEGIN   INCL_CONSTRAINTS := NULL;   FULL_CHECK := NULL;   SYS.DBMS_TTS.TRANSPORT_SET_CHECK ('MI_CTZN_BLOB_TEMP_TBS', INCL_CONSTRAINTS, TRUE );   COMMIT; END;

Then check sys.transport_set_violations. If there are no rows, then do the export. If rows are returned, there are violations.

SQL> select count(*) from sys.transport_set_violations

7. Make MI_CTZN_BLOB_TEMP_TBS tablespace READ ONLY:
   
   sys@EMULE> ALTER TABLESPACE MI_CTZN_BLOB_TEMP_TBS READ ONLY;

Tablespace altered.

8. Define environment for the database and run export:
   
   setenv ORACLE_SID EMULE

exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=MI_CTZN_BLOB_TEMP_TBS file=/load/tts.dmp log=/load/tts.log

9. Copy all the related data files and export file to destination server:

oracle@myunixsource:/load>scp /database_emule/mi_ctzn_blob_temp_tbs1.dbf oracle@myunixdest:/database_mav

oracle@myunixsource:/load>scp tts.dmp oracle@myunixdest:/load

10. On destination server run import:

imp userid=\'/ as sysdba\' transport_tablespace=y datafiles=/database_mav/mi_ctzn_blob_temp_tbs1.dbf file=/load/tts.dmp log=/load/tts.log