FPSADMIN.BLD_COPY_DASH_C_TABLES

>

> FPSADMIN.WIP_WAFER_HIST_LOOP

View FPSADMIN.BLD_COPY_DASH_C_TABLES

Here are instructions on how to copy Dashboard configuration from a database at a different site. This is normally used only for a new site that is the first FPS site at the client. If we are starting up a new site at an existing client then we almost always want to copy the configuration from an existing site of that client which we can usually do easily via a database link. However if the sites cannot be connected via a database link then these instructions will work. ----- PART 1: ON THE SOURCE (EXISTING) DATABASE 1) Run this script with F5 (not F9) in SQL Developer or Toad. Fill in OLDFAC with the name of the facility. If the site has multiple facilities, pick the facility that you want to use for DASH_C. The insert syntax only works for up to 5000 rows at a time so DASH_C_CATEGORY_TABLE_COLS is split into three queries that should be less than 5000 rows each. Since this is in a comment, we cannot actually includes slashes and asterisks so you have to find-replace the slashes: --These tables each have less than 5000 rows per facility select (slash)*insert*(slash) * from dash_c_columns order by sort_order; select (slash)*insert*(slash) * from dash_c_spotlights where facility = 'OLDFAC' order by web_version, category, sort_order; select (slash)*insert*(slash) * from dash_c_category_tables where facility = 'OLDFAC' order by web_version, category, table_id; --This table had 32k rows per facility as of 7.7.4 and we need to break it up into 5000 row chunks for (slash)*insert*(slash) so we do this 7 times plus one more just in case it grows select (slash)*insert*(slash) WEB_VERSION, FACILITY, CATEGORY, TABLE_ID, COLUMN_ID, COLUMN_WIDTH_PCT, COLUMN_ENTITY_MAX_NUM, OVR_SORT_ORDER, SHOULD_TOTAL, SHOULD_DISPLAY, SHOULD_AUTO_HIDE, SHOULD_EXPORT, SHOULD_DISPLAY_EXP, OVR_HEADER_DISPLAY from (select c.*, row_number() over (order by web_version, category, table_id, column_id) as rn from dash_c_category_table_cols c where facility = 'OLDFAC') where rn > 0 and rn <= 5000; select (slash)*insert*(slash) WEB_VERSION, FACILITY, CATEGORY, TABLE_ID, COLUMN_ID, COLUMN_WIDTH_PCT, COLUMN_ENTITY_MAX_NUM, OVR_SORT_ORDER, SHOULD_TOTAL, SHOULD_DISPLAY, SHOULD_AUTO_HIDE, SHOULD_EXPORT, SHOULD_DISPLAY_EXP, OVR_HEADER_DISPLAY from (select c.*, row_number() over (order by web_version, category, table_id, column_id) as rn from dash_c_category_table_cols c where facility = 'OLDFAC') where rn > 5000 and rn <= 10000; select (slash)*insert*(slash) WEB_VERSION, FACILITY, CATEGORY, TABLE_ID, COLUMN_ID, COLUMN_WIDTH_PCT, COLUMN_ENTITY_MAX_NUM, OVR_SORT_ORDER, SHOULD_TOTAL, SHOULD_DISPLAY, SHOULD_AUTO_HIDE, SHOULD_EXPORT, SHOULD_DISPLAY_EXP, OVR_HEADER_DISPLAY from (select c.*, row_number() over (order by web_version, category, table_id, column_id) as rn from dash_c_category_table_cols c where facility = 'OLDFAC') where rn > 10000 and rn <= 15000; select (slash)*insert*(slash) WEB_VERSION, FACILITY, CATEGORY, TABLE_ID, COLUMN_ID, COLUMN_WIDTH_PCT, COLUMN_ENTITY_MAX_NUM, OVR_SORT_ORDER, SHOULD_TOTAL, SHOULD_DISPLAY, SHOULD_AUTO_HIDE, SHOULD_EXPORT, SHOULD_DISPLAY_EXP, OVR_HEADER_DISPLAY from (select c.*, row_number() over (order by web_version, category, table_id, column_id) as rn from dash_c_category_table_cols c where facility = 'OLDFAC') where rn > 15000 and rn <= 20000; select (slash)*insert*(slash) WEB_VERSION, FACILITY, CATEGORY, TABLE_ID, COLUMN_ID, COLUMN_WIDTH_PCT, COLUMN_ENTITY_MAX_NUM, OVR_SORT_ORDER, SHOULD_TOTAL, SHOULD_DISPLAY, SHOULD_AUTO_HIDE, SHOULD_EXPORT, SHOULD_DISPLAY_EXP, OVR_HEADER_DISPLAY from (select c.*, row_number() over (order by web_version, category, table_id, column_id) as rn from dash_c_category_table_cols c where facility = 'OLDFAC') where rn > 20000 and rn <= 25000; select (slash)*insert*(slash) WEB_VERSION, FACILITY, CATEGORY, TABLE_ID, COLUMN_ID, COLUMN_WIDTH_PCT, COLUMN_ENTITY_MAX_NUM, OVR_SORT_ORDER, SHOULD_TOTAL, SHOULD_DISPLAY, SHOULD_AUTO_HIDE, SHOULD_EXPORT, SHOULD_DISPLAY_EXP, OVR_HEADER_DISPLAY from (select c.*, row_number() over (order by web_version, category, table_id, column_id) as rn from dash_c_category_table_cols c where facility = 'OLDFAC') where rn > 25000 and rn <= 30000; select (slash)*insert*(slash) WEB_VERSION, FACILITY, CATEGORY, TABLE_ID, COLUMN_ID, COLUMN_WIDTH_PCT, COLUMN_ENTITY_MAX_NUM, OVR_SORT_ORDER, SHOULD_TOTAL, SHOULD_DISPLAY, SHOULD_AUTO_HIDE, SHOULD_EXPORT, SHOULD_DISPLAY_EXP, OVR_HEADER_DISPLAY from (select c.*, row_number() over (order by web_version, category, table_id, column_id) as rn from dash_c_category_table_cols c where facility = 'OLDFAC') where rn > 30000 and rn <= 35000; select (slash)*insert*(slash) WEB_VERSION, FACILITY, CATEGORY, TABLE_ID, COLUMN_ID, COLUMN_WIDTH_PCT, COLUMN_ENTITY_MAX_NUM, OVR_SORT_ORDER, SHOULD_TOTAL, SHOULD_DISPLAY, SHOULD_AUTO_HIDE, SHOULD_EXPORT, SHOULD_DISPLAY_EXP, OVR_HEADER_DISPLAY from (select c.*, row_number() over (order by web_version, category, table_id, column_id) as rn from dash_c_category_table_cols c where facility = 'OLDFAC') where rn > 35000 and rn <= 40000; 2) Copy or save the output. If you will be able to connect to the new database with the same SQL Developer or Toad application then just copy/paste into a new tab. If you have to use Citrix or Remote Desktop then save to a file. 3) The output will have six sections. In between each section, replace these lines: 999 rows selected. REM INSERTING into DASH_C_XXX SET DEFINE OFF; With these lines: commit; end; (slash) begin At the top, start with just begin. At the bottom, end with just commit-end-slash. ----- PART 2: ON THE NEW DATABASE 1) Turn ETL off but ignore the message that tells you to wait for running jobs: exec adm_turn_etl_off; 2) Disable some constraints: exec adm_disable_cons('FPSAPP.DASH_C_CATEGORY_TABLES_FK_FAC'); exec adm_disable_cons('FPSAPP.DASH_C_CAT_TABLE_COLS_C_FK'); exec adm_disable_cons('FPSAPP.DASH_C_SPOTLIGHTS_FK_FAC'); exec adm_disable_cons('FPSAPP.DASH_C_CAT_TABLE_COLS_TABLE'); 3) Delete the existing DASH_C tables: delete from dash_c_spotlights; delete from dash_c_category_tables; delete from dash_c_category_table_cols; delete from dash_c_columns; commit; 4) Run the script from part 1 using F5. Hopefully it will not have any errors but fix if necessary: 5) Update the facility to your facility: update dash_c_spotlights set facility = 'NEWFAC' where facility = 'OLDFAC'; update dash_c_columns set facility = 'NEWFAC' where facility = 'OLDFAC'; update dash_c_category_tables set facility = 'NEWFAC' where facility = 'OLDFAC'; update dash_c_category_table_cols set facility = 'NEWFAC' where facility = 'OLDFAC'; 6) Enable the disabled constraints: exec adm_enable_cons('FPSAPP.DASH_C_CATEGORY_TABLES_FK_FAC'); exec adm_enable_cons('FPSAPP.DASH_C_CAT_TABLE_COLS_C_FK'); exec adm_enable_cons('FPSAPP.DASH_C_SPOTLIGHTS_FK_FAC'); exec adm_enable_cons('FPSAPP.DASH_C_CAT_TABLE_COLS_TABLE'); 7) If your site has multiple facilities then you can duplicate the configuration with the STARTUP_ADD_FACILITY procedure. 8) When finished, turn ETL on: exec adm_turn_etl_on('Y');