How to Move USER_SDO_GEOM_METADATA like a Grandpa

Sometimes moving a small amount of data in Oracle database requires more work than the actual task you need to complete. DBAs always have their tools (PL/SQL Developer Text Importer is my favorite one) to move a small amount of data between databases.
But when it comes to nested tables this might be a bit challenging. USER_SDO_GEOM_METADATA table (for those of you who are not familiar with it, it is the catalog information of spatial layers in an Oracle database) has no exception to that. Here how it is easy to handle it
The structure of USER_SDO_GEOM_METADATA table has the following structure

SQL> desc user_sdo_geom_metadata
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME				   NOT NULL VARCHAR2(32)
 COLUMN_NAME				   NOT NULL VARCHAR2(1024)
 DIMINFO					    MDSYS.SDO_DIM_ARRAY
 SRID						    NUMBER

Obviously DIMINFO column is the problematic part since it is in SDO_DIM_ARRAY type. Now create a temporary table by unfolding this nested table at the source database site.

CREATE TABLE temp_metadata AS 
  SELECT t.table_name, 
         t.column_name, 
         d.*, 
         t.srid 
  FROM   user_sdo_geom_metadata t, 
         TABLE(t.diminfo) d; 

Once you are done check the structure of temp_metadata table which only contains primitive SQL types for the columns

SQL> desc temp_metadata
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME					    VARCHAR2(32)
 COLUMN_NAME					    VARCHAR2(1024)
 SDO_DIMNAME					    VARCHAR2(64)
 SDO_LB 					    NUMBER
 SDO_UB 					    NUMBER
 SDO_TOLERANCE					    NUMBER
 SRID						    NUMBER

Choose your favorite tool to move this table to the target database since it contains just a few rows. Then as the final step import this data into USER_SDO_GEOM_METADATA table at the target database (Ensure that there is no other record with the same table_name.column_name key on the target USER_SDO_GEOM_METADATA table otherwise trigger on it will warn you)

insert into user_sdo_geom_metadata
SELECT m.table_name, m.column_name,
   CAST(MULTISET(SELECT sdo_dimname,sdo_lb,SDO_UB,sdo_tolerance
                 FROM temp_metadata n
                 WHERE n.table_name = m.table_name and n.column_name = m.column_name)
   AS SDO_DIM_ARRAY) as diminfo,
   m.srid
FROM (select distinct table_name,column_name,srid from temp_metadata) m;
commit;

You are done! Check everything works fine at the application site.