Oracle Export BLOB,CLOB Error problem

2.3 How to solve the problem of exporting CLOB and BLOB type data error

When a table field contains CLOB and BLOB type data, using PL/SQL developer to export an error that reports the stream read error, the export operation terminates, stating that the PL/SQL developer mode Export does not support this type, Oracle Export mode can support this type. Due to the large number of tables that need to be exported, PL/SQL will typically take more than 10 minutes to parse the tables that need to be exported before exporting, but the operation time will be wasted due to the fact that a table exists with CLOB and BLOB type data, which would be wasteful, and the exported table must be marked to export all tables , only tables that do not have CLOB and BLOB type data are exported. Our usual practice is to use CTRL or SHIFT keys to manually select when exporting a table, and then manually reverse the tables to be exported by exporting the tables that cannot be exported. But it takes time and effort to know which tables cannot be exported by repeating the operation. Here's how to quickly export table data that does not contain CLOB and BLOBs using the object selection functionality provided by Oracle System view all_tab_columns and PL/SQL when exporting tables.

1. Use the following SQL statement to spell out the file contents of object selection.

--table with no CLOB and BLOBs select DISTINCT (' Table ' | | A.owner | | ' "." ' | | a.table_name| | ' "') From Sys.all_tab_columns a WHERE A.owner = ' icdpub ' and A.table_name not in (select T.table_name from Sys.all_tab_columns t where T.owner = ' icdpub ' and T.data_type in (' CLOB ', ' BLOB '))

Note: The icdpub in the SQL statement above is the user name

2. Generate object selection file (suffix. osf) According to the execution result of the above statement, the contents of the file are as follows:

PL/SQL Developer Object Selection File 1

TABLE "Icdpub". " Active_alarms "

TABLE "Icdpub". " Allappoint "

TABLE "Icdpub". " Alloptionview "

TABLE "Icdpub". " Allotconfig "

TABLE "Icdpub". " Allpaperauthview "

TABLE "Icdpub". " Allpaperview "

TABLE "Icdpub". " Allquestionview "

3. In the table selection box of the Export table function, right-click to select "Load Object Selection", select the file you made in the previous step and complete the table selection.

4, select the appropriate parameters for export operations.

5. Export the data that contains CLOB and blobs with the Oracle exporting method.

Oracle Export BLOB,CLOB Error problem