Oracle BLOB export via SqlPlus

oracle

Is there an easy and fast way to quickly perform a select of a BLOB value from Oracle, copy the result in a notepad and run the corresponding insert in another Oracle to insert data? Data is much longer than 4 K.

I cannot use external tools or copy files because I've access only to the sqlplus tool (PlSql developer, to be clearer) and from what I've seen googling for it is not so easy, so maybe the accepted answer will be simple a "NO, there is no way".

Best Answer

Rather than using notepad as an intermediary, I expect you'd be much better served simply using the SQL*Plus COPY command, i.e.

COPY FROM source_table@source_tns_alias
       TO destination_table@destination_tns_alias
   CREATE dest_table_name ( column_list)
    USING SELECT column_list 
            FROM source_table_name

will copy the contents of SOURCE_TABLE at SOURCE_TNS_ALIAS to a newly created table DEST_TABLE_NAME at the DESTINATION_TNS_ALIAS. There are also options in the COPY command to load data into an existing table.

Related Solutions

Export/Import on different versions of Oracle

I believe export from 11g to 9i is not supported but will probably work. 11g to 10g is fine. Basic rules are:

Export data using the export utility supplied with the lowest of the two databases.
Import data using the import utility supplied with the target database.

Ideally you want to be using the latest patch release of each version.

You can find the full documentation including step-by-step operation of the export and import utilities here:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/exp_imp.htm

Oracle export failed: EXP-00006: internal inconsistency error

If you have Oracle Support, then tell them. It can be an internal problem with export itself and they will request additional information, tell you what to try and so on, until you resolve the problem. You will probably also get a patch/fix if there is.

I would suggest doing the export with datapump instead as export is long time deprecated. You need to define a directory where oracle should drop exported data into, then start datapump. It's easy, faster and more reliable and flexible than import/export.

Example:

SQL> CREATE OR REPLACE DIRECTORY expdump AS '/export/home/oracle/expdump';

SQL> GRANT READ, WRITE ON DIRECTORY expdump TO user_who_will_export;

Then run:

$ expdp user/pass schemas=schema1,schema2 directory=expdump dumpfile=myexport.dmp logfile=myexport.log

Addition: Looks like you have problems with consistency of the DB. Run logical and physical validation with RMAN to catch any other problems:

RMAN> connect target /

RMAN> run {
# set disk to be default device type
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
# number of concurrent sessions to spawn (if you can affor parallelism)
CONFIGURE DEVICE TYPE DISK PARALLELISM 10 BACKUP TYPE TO BACKUPSET;
# check datafiles for corruption 10 datafiles in each session (if you can afford paralellism)
BACKUP VALIDATE CHECK LOGICAL DATABASE FILESPERSET=10;
}

Code is generic, this snippet borrowed from http://oraclespin.wordpress.com/2008/06/11/how-to-check-physical-and-logical-data-corruption-using-rman/

Best Answer

Related Solutions

Export/Import on different versions of Oracle

Oracle export failed: EXP-00006: internal inconsistency error

Related Topic