Reuse Oracle Spatial Styles and themes

After you create Your styles and themes on a server and you want to use them on another server, follow the following procedure

Log-in to your user

•  run the following scripts depending on you user 

         create table my_user_sdo_geom_metadata as select * from user_sdo_geom_metadata;
         create table my_user_sdo_styles as select * from user_sdo_styles;
         create table my_user_sdo_themes as select * from user_sdo_themes ;
         create table my_user_sdo_maps as select * from user_sdo_maps;
         create table my_user_sdo_cached_maps as select * from user_sdo_cached_maps;
        create table my_user_sdo_geor_sysdata as select * from user_sdo_geor_sysdata;

Now Log-in as sysdba and do the following

• To pick a directory from all directories in your DB use the following scrip

select * from all_directories;

• Grant the user to access the selected directory and also grant it to use remap 

GRANT READ, WRITE ON DIRECTORY EXP_DIR TO GIS_B
grant imp_full_database to gis_v1;

Now open The command prompt (cmd) as Admin and do the following

• Run the following script and don't type ";" at the end

expdp gis_coding/gis_coding@ora11g tables=my_user_sdo_cached_maps,my_user_sdo_geom_metadata,my_user_sdo_geor_sysdata, my_user_sdo_maps,my_user_sdo_styles,my_user_sdo_themes directory=EXP_DIR dumpfile=stylesAndThemes.dmp logfile=stylesAndThemes.log

Now you have dump file contains your styles and themes, copy it to a database directory on the destination Server

•  Repeat the granting step -look at the top of this document- to the new schem; which you want to move the data to it

Now open command prompt

• Run the following script , and don't type ";" at the end

impdp gis_coding/gis_coding@ora11g  directory=EXP_DIR dumpfile=stylesAndThemes.dmp logfile=stylesAndThemesIMP.log REMAP_SCHEMA=gis_coding:gis_coding

Now log-in to your new schema which you imported the data in it

•  Run the insert statement to insert all or specific data into "user_sdo_styles" or "user_sdo_themes" 

Enjoy