Import/Export - Yak Shaving Experience

Quick:

1. You cant export data from Synonym view [Exports only sql desc of Synonym]
2. You cant create table using 'create table as' [It throws column name zero errpr
3. Collect view's desc of Synonym view
4. Export Synonym view data into csv file (SQLDeveloper is handy)
5. Create the table in destination DB using modified desc (from step 2)
6. Import csv file using SQLDeveloper - Created table -> right click -> Import (has csv option by default in SQLDeveloper 4.0.3.16)

TL;DR:

Stage 0: Intro

As developer environment isolated from other environments, some of live feed tables were copied to local database (Oracle 11g) as table, instead of accessing them using Synonym (view) -> DB Links. 

Almost 100K records needed to be copied to the dev DB. 

Stage 1: Create the table using 'create table as'

  Ref: http://dba.stackexchange.com/questions/60175/export-synonyms-content-to-tables

 Create table as failed with  'zero-length columns are not allowed'. 
 create table HUGE_TABLE NOLOGGING PARALLEL 4 as select * from syn_table@dblink;

Stage 2: Describe table

 Using Desc of synonym view shows only the synonym info. After trying many options, 'DESC syn_table' gave table/view description only where the real view/table exists. 

 Searched 'dba_objects' [Alternative: all_objects, usr_objects] to verify view existence. Describe table info needed some modification to create the table!

 So table is created.

Stage 3: Export data - insert option

 The SQLDeveloper has an option to export the 'Query Result' window [Right click to see the options]. First trial, I used the Format option as 'Insert', which creates the SQL file with insert queries. This failed in Mac Os (10.10.2) with error 'File ** was not opened because it exceeds the maximum automatic open size'. So, I had to go pagination

Stage 4: Pagination with ROWNUM

Ref:http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

 Important: The following query wont return any results. Worth reading the article!! 

      select *   from t  where ROWNUM > 1; 

 Finally, after careful document reading, I found a query which could help with pagination.

select * from (select rec.*, rownum rnum from (select * from items order by items_id asc) rec where rownum <= 10) where rnum >= 5;

Stage 5: Text file limitation

 When I tried to export 10K per file, I realized that only 469 rows can be exported with record size for the given table. It is because, every insert line includes column names too.

Stage 6: CSV file

  Simple common sense thought told me that removing columns names may double the number of rows can be accommodated in single export. While exploring the format options, such 'loader', 'csv', only option works smoothly was 'csv'. Tried with 1000 records exported to csv. 

Stage 7: Import CSV

  In SQLDeveloper, tables list, right clicking on table gives import option and it accepts csv file. And, 1000 records worked smooth.

Stage 8: Working option

 Tried to export all 100K records into single csv file and it went smooth; Above all, SQLDeveloper has nice and informative tool to import csv file. It showed the import progress in Message log.