Field Notice: FN - 70588 - Databases in Specific Versions of Cisco Field Network Director Might Not Be Pruned - Software Upgrade Recommended

Notice

THIS FIELD NOTICE IS PROVIDED ON AN "AS IS" BASIS AND DOES NOT IMPLY ANY KIND OF GUARANTEE OR WARRANTY, INCLUDING THE WARRANTY OF MERCHANTABILITY. YOUR USE OF THE INFORMATION ON THE FIELD NOTICE OR MATERIALS LINKED FROM THE FIELD NOTICE IS AT YOUR OWN RISK. CISCO RESERVES THE RIGHT TO CHANGE OR UPDATE THIS FIELD NOTICE AT ANY TIME.

Revision History

Revision	Publish Date	Comments
1.0	16-Dec-20	Initial Release
1.1	28-May-21	Updated the Additional Information Section
1.2	03-Jun-21	Update Affected Release Number section

Products Affected

Affected OS Type	Affected Software Product	Affected Release	Affected Release Number	Comments
NON-IOS	Field Network Director for Virtual Machine Installation	4	4.3.0, 4.3.1, 4.3.2, 4.4.0, 4.5.0, 4.5.1
NON-IOS	Field Network Director for Bare Metal Installation	4	4.1.0, 4.1.1, 4.1.2, 4.1.3, 4.2.0, 4.3.0, 4.3.2, 4.4.0, 4.4.1, 4.4.2, 4.4.3, 4.4.4, 4.5.0, 4.5.1, 4.6.0

Defect Information

Defect ID	Headline
CSCvu55518	Pruning of some database tables is not working

Problem Description

Databases that are deployed in certain versions of Cisco Field Network Director (FND) might not be pruned after the preset duration. This might affect the performance of FND.

Background

Cisco FND uses databases that must be pruned after a specific duration in order to avoid an overall system slowdown. Customers who use an affected Cisco FND release (see the Products Affected section) might observe that these databases are not pruned:

• Metrics History Hourly and Daily Aggregated Tables
  Note: The Metrics History table does not have this issue.
• Historical Data and Hourly and Daily Aggregated Tables
• Firmware Logs
• Issues
• Jobs
• SPdownrouters

If the databases are not pruned, the data continues to accumulate and might result in slower system response times at the user interface.

Problem Symptom

Database tables have specific retention periods that are set by the customer. When these retention periods are exceeded, it is an indicator that the database has not been pruned.

This image shows an example of a Data Retention table in which “Keep Closed Issues data for” is set to 45 days.

On the same system, when the filter is set to "All time", "All Closed Issues", and "Occur Time" is listed in ascending chronological order, issues that have been logged for more than 45 days are visible. This is an indicator that the database has not been pruned.

Workaround/Solution

There are two options to address the issue when databases are not pruned:

Option 1. The solution is to upgrade to Cisco FND Release 4.6.1 or later. After the upgrade process is complete and Cisco FND Release 4.6.1 restarts, it will begin to prune the database as part of the cleanup process.

In order to view the status of the job detail in the server log file, enable debug logging level for “Data Retention” in Admin > Logging > Log Level Settings.

Note: Depending on the size of the database, it is possible that the pruning activity could take a long time. Also, customers must ensure that sufficient disk space for Oracle logs is available.

Option 2. As a workaround, use the SQL commands that are highlighted in the Additional Information section in order to delete unwanted data. Contact the Technical Assistance Center (TAC) for assistance with the scripts or procedure.

Additional Information

Prune the Oracle Database Tables

A summary of the origin of Cisco bug ID CSCvu55518 (Pruning of some database tables is not working.) and the issues associated with the caveat are detailed in this section.

These tables are not being pruned:

• Metrics history hourly and daily aggregated tables
  Note: The Metrics History table does not have the issue.
• Historical data and hourly and daily aggregated tables
• Firmware logs
• Issues
• Jobs
• SPdownrouters

Given that pruning was not occurring, the performance of Cisco FND might be affected. A fix for Cisco bug ID CSCvu55518 has been incorporated into Cisco FND Release 4.6.1.

Note: Before you begin the actual upgrade to Cisco FND 4.6.1, follow this recommendation:

When you upgrade to Cisco FND 4.6.1, the pruning starts after the upgrade to Cisco FND Release 4.6.1. There might be a heavy load on the database, which causes the archive logs to increase, and might result in shortage of available disk space. Therefore, Cisco recommends that you prune the database directly by employing the SQLs described in this section during the “lean” times of the database load. Specifically, when no users are accessing the user interface and when no pruning is in progress. This approach allows the earlier software versions (pre-Cisco FND 4.6.1) to better handle functions such as backup, removal, and generation of the archive logs.

 

SQLs to Run During an Upgrade to FND Release 4.6.1 to Prune the Database

Here are some recommended and/or optional steps to take before you execute the mentioned SQLs:

1. Back up the database.
2. (Optional) Turn off the archive log to prevent running out of disk space.
3. Execute the mentioned SQLs. See the next section for more information on this step.
4. (Optional) Turn on the archive logs after steps 1-3 are completed.

 

SQLs To Execute

Note: Only the database administrator should execute the queries.

The queries need to be executed using SQL Developer or SQL Plus.

Run Oracle SQL developer locally. The Oracle SQL developed homepage is displayed.

 

Under Connections, right-click Connections.
The Connection menu appears.

Choose New Connection.
The New/Select Database Connection dialog box appears.

On the New/Select Database Connection dialog, update these fields:

• Connection Name - Enter the name for this connection.
• Username - Enter the database username – cgms_dev.
• Password - Enter the password – cgms123.
• Connection Type - From the drop-down list, choose Basic.
• Port - Enter 1522.
• Service name - Enter cgms.

Click Test.
Status: Success displays in the bottom left side of the New/Select Database Connection dialog box.

Click Connect.
An entry for the new connection appears under Connections.

Open the new connection.
If you have connected successfully, the tables and other objects display under the new connection.

Run this query in SQL Developer in order to determine if pruning is required.

SELECT Count(*)FROM cgms_dev.historical_named_data WHERE TIME < (trunc(SYSDATE-8) - DATE '1970-01-01')*24*60*60*1000;

If this query returns data, then pruning is required on these tables. Otherwise, pruning is not required.

 

Metrics History Hourly Aggregated Tables

METRICS_HISTORY_HR

CREATE TABLE "CGMS_DEV"."METRICS_HISTORY_HR_COPY" 

   ("ID" NUMBER(19,0) NOT NULL ENABLE,

            "NET_OBJECT_ID" NUMBER(19,0) DEFAULT '0' NOT NULL ENABLE,

            "METRIC_TYPE_ID" NUMBER(10,0) DEFAULT '0' NOT NULL ENABLE,

            "LASTUPDATE" TIMESTAMP (0) DEFAULT to_date('01-JAN-1970 00:00:00', 'dd-MON-yyyy hh24:mi:ss') NOT NULL ENABLE,

            "AVG_VALUE" FLOAT(126),

            "MIN_VALUE" FLOAT(126),

            "MAX_VALUE" FLOAT(126),

            "SUM_VALUE" FLOAT(126));

Alter table METRICS_HISTORY_HR_COPY add CONSTRAINT "METRICS_HR_PRIMARY_KEY_PK" PRIMARY KEY ("NET_OBJECT_ID", "METRIC_TYPE_ID", "LASTUPDATE");

CREATE INDEX "CGMS_DEV"."IDX_NETID_TIME_METRIC_MAX_HR_C" ON "CGMS_DEV"."METRICS_HISTORY_HR_COPY" ("NET_OBJECT_ID", "LASTUPDATE", "METRIC_TYPE_ID", "MAX_VALUE");

Alter table METRICS_HISTORY_HR nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.METRICS_HISTORY_HR_COPY Select * FROM  cgms_dev.metrics_history_hr WHERE  lastUpdate  < (SYSDATE-((select RETENTIONDAYS from Pruning_jobs where NAME='MetricHourlyAggrPruningJob')+1)) order by lastupdate;

 

Alter table METRICS_HISTORY_HR logging;

Delete the Table Data

Drop table cgms_dev.metrics_history_hr;

Rename the Copy Table to the Original Table

Rename metrics_history_hr_copy to metrics_history_hr;

 

Metrics History Daily Aggregated Tables

METRICS_HISTORY_DA

CREATE TABLE "CGMS_DEV"."METRICS_HISTORY_DA_COPY" 

   (        "ID" NUMBER(19,0) NOT NULL ENABLE,

            "NET_OBJECT_ID" NUMBER(19,0) DEFAULT '0' NOT NULL ENABLE,

            "METRIC_TYPE_ID" NUMBER(10,0) DEFAULT '0' NOT NULL ENABLE,

            "LASTUPDATE" TIMESTAMP (0) DEFAULT to_ date('01-JAN-1970 00:00:00', 'dd-MON-yyyy hh24:mi:ss') NOT NULL ENABLE, 

            "AVG_VALUE" FLOAT(126),

            "MIN_VALUE" FLOAT(126),

            "MAX_VALUE" FLOAT(126),

            "SUM_VALUE" FLOAT(126));

Alter table METRICS_HISTORY_DA_COPY add constraint metrics_history_router_pk primary key (lastUpdate,net_object_id,metric_type_id);

CREATE INDEX "CGMS_DEV"."IDX_NETID_TIME_METRIC_MAX_DA_T" ON "CGMS_DEV"."METRICS_HISTORY_DA_COPY" ("NET_OBJECT_ID", "LASTUPDATE", "METRIC_TYPE_ID", "MAX_VALUE") ;

 

Alter table METRICS_HISTORY_DA nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.METRICS_HISTORY_DA_COPY Select * FROM  cgms_dev.metrics_history_da WHERE  lastUpdate  < (SYSDATE-((select RETENTIONDAYS from Pruning_jobs where NAME='MetricDailyAggrPruningJob')+1));

 

Alter table METRICS_HISTORY_DA logging;

Delete the Table Data

Drop  table cgms_dev.metrics_history_da;

Rename the Copy Table to the Original Table

Rename metrics_history_hr_copy to metrics_history_da;

 

Historical Data Tables

CREATE TABLE "CGMS_DEV"."HISTORICAL_NAMED_DATA _COPY" 

   ("ID" NUMBER(19,0) NOT NULL ENABLE,

            "TIME" NUMBER(19,0) NOT NULL ENABLE,

            "SEARCHID" VARCHAR2(510 BYTE) NOT NULL ENABLE,

            "DEVICECOUNT" NUMBER(19,0) NOT NULL ENABLE,

            "DOMAIN_ID" NUMBER(19,0) DEFAULT 1 NOT NULL ENABLE,

             PRIMARY KEY ("ID"));

Alter table "CGMS_DEV"."HISTORICAL_NAMED_DATA _COPY" add CONSTRAINT HND_TIME_SRCH_DID_UDX_T UNIQUE (TIME, SEARCHID, DOMAIN_ID);

 

Alter table HISTORICAL_NAMED_DATA nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.HISTORICAL_NAMED_DATA_COPY Select * FROM  cgms_dev.historical_named_data WHERE time < ((SYSDATE-((select RETENTIONDAYS from Pruning_jobs where NAME='HistoricalDataPruningJob')+1))- DATE '1970-01-01')*24*60*60*1000;

 

Alter table HISTORICAL_NAMED_DATA logging;

Delete the Table Data

Drop table cgms_dev.historical_named_data;

Rename the Copy Table to the Original Table

Rename historical_named_data_copy to historical_named_data;

 

Historical Data Hourly Aggregated Tables

CREATE TABLE HISTORICAL_NAMED_DATA_HR_COPY

("ID" NUMBER(19,0) NOT NULL ENABLE,

"TIME" NUMBER(19,0) NOT NULL ENABLE,

"SEARCHID" VARCHAR2(510 BYTE) NOT NULL ENABLE,

"AVG_DEVICECOUNT" NUMBER(19,0) NOT NULL ENABLE,

"MIN_DEVICECOUNT" NUMBER(19,0) NOT NULL ENABLE,

"MAX_DEVICECOUNT" NUMBER(19,0) NOT NULL ENABLE,

"SUM_DEVICECOUNT" NUMBER(19,0) NOT NULL ENABLE,

PRIMARY KEY ("ID","TIME","SEARCHID") );

 

Alter table HISTORICAL_NAMED_DATA_HR nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.HISTORICAL_NAMED_DATA_HR_COPY Select * FROM  cgms_dev.historical_named_data_hr WHERE  time < ((SYSDATE-((select RETENTIONDAYS from Pruning_jobs where NAME='HistoricalHourlyAggrPruningJob')+1))- DATE '1970-01-01')*24*60*60*1000;

 

Alter table HISTORICAL_NAMED_DATA_HR logging;

Delete the Table Data

Drop table cgms_dev.historical_named_data_hr;

Rename the Copy Table to the Original Table

Rename historical_named_data_hr_copy to historical_named_data_hr;

 

Historical Data Daily Aggregated Tables

CREATE TABLE HISTORICAL_NAMED_DATA_DA_COPY

(

"ID" NUMBER(19,0) NOT NULL ENABLE,

"TIME" NUMBER(19,0) NOT NULL ENABLE,

"SEARCHID"    VARCHAR2(510 BYTE) NOT NULL ENABLE,

"AVG_DEVICECOUNT" NUMBER(19,0) NOT NULL ENABLE,

"MIN_DEVICECOUNT" NUMBER(19,0) NOT NULL ENABLE,

"MAX_DEVICECOUNT" NUMBER(19,0) NOT NULL ENABLE,>

"SUM_DEVICECOUNT" NUMBER(19,0) NOT NULL ENABLE,

PRIMARY KEY ("ID","TIME","SEARCHID") );

 

Alter table HISTORICAL_NAMED_DATA_DA nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.HISTORICAL_NAMED_DATA_DA_COPY Select * FROM  cgms_dev.HISTORICAL_NAMED_DATA_DA WHERE  time < ((SYSDATE-((select RETENTIONDAYS from Pruning_jobs where NAME='HistoricalDailyAggrPruningJob')+1))- DATE '1970-01-01')*24*60*60*1000;

 

Alter table HISTORICAL_NAMED_DATA_DA logging;

Delete the Table Data

Drop table cgms_dev.HISTORICAL_NAMED_DATA_DA;

Rename the Copy Table to the Original Table

Rename HISTORICAL_NAMED_DATA_DA_COPY to HISTORICAL_NAMED_DATA_DA;

 

Firmware Logs Table

CREATE TABLE "CGMS_DEV"."FIRMWARE_LOGS_COPY" 

   (        "ID" NUMBER(19,0) NOT NULL ENABLE, 

            "LASTUPDATE" TIMESTAMP (0) DEFAULT to_date('01-JAN-1970 00:00:00', 'dd-MON-yyyy hh24:mi:ss') NOT NULL ENABLE, 

            "FIRMWARE_GROUP_ID" NUMBER(10,0) NOT NULL ENABLE,

            "ADDRESS" VARCHAR2(255 BYTE) DEFAULT NULL,

            "MULTICAST" NUMBER(1,0) DEFAULT NULL,

            "EVENTTYPE" NUMBER(3,0) NOT NULL ENABLE,

            "MESSAGE" VARCHAR2(4000 BYTE) NOT NULL ENABLE,

            "MSGTYPE" NUMBER(3,0) NOT NULL ENABLE,

            "PREFIX" VARCHAR2(255 BYTE),

             PRIMARY KEY ("ID"));

CREATE INDEX "CGMS_DEV"."FIRMWARE_LOGS_PREFIX_C" ON "CGMS_DEV"."FIRMWARE_LOGS_COPY" (NLSSORT("PREFIX",'nls_sort=''BINARY_CI'''));

CREATE INDEX "CGMS_DEV"."FL_ADDRESS_IDX_C" ON "CGMS_DEV"."FIRMWARE_LOGS_COPY" ("ADDRESS");

CREATE INDEX "CGMS_DEV"."FL_LU_IDX_C" ON "CGMS_DEV"."FIRMWARE_LOGS_COPY" ("LASTUPDATE");

 

Alter table FIRMWARE_LOGS nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.FIRMWARE_LOGS_COPY Select * FROM  cgms_dev.FIRMWARE_LOGS WHERE  lastUpdate < (SYSDATE-((select RETENTIONDAYS from Pruning_jobs where NAME='FirmwarePruningJob')+1));

 

Alter table FIRMWARE_LOGS logging;

Delete the Table Data

Drop table cgms_dev.FIRMWARE_LOGS;

Rename the Copy Table to the Original Table

Rename FIRMWARE_LOGS_COPY to FIRMWARE_LOGS;

 

Issue Notes Table

CREATE TABLE "CGMS_DEV"."ISSUE_NOTES_COPY"

   ("ID" NUMBER(19,0) NOT NULL ENABLE,

            "ISSUE_ID" NUMBER(19,0) NOT NULL ENABLE,

            "USERNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,

            "NOTETIME" TIMESTAMP (0) NOT NULL ENABLE,

            "NOTE" VARCHAR2(4000 BYTE), 

             PRIMARY KEY ("ID"));



CREATE INDEX "CGMS_DEV"."ISSUEID_C" ON "CGMS_DEV"."ISSUE_NOTES_COPY" ("ISSUE_ID");

 

Alter table ISSUE_NOTES nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.ISSUE_NOTES_COPY select * from cgms_dev.issue_notes where issue_id in (select id from cgms_dev.issues where issueLastUpdateTime <  (sysdate-((select RETENTIONDAYS from Pruning_jobs where NAME='IssuePruningJob')+1)) and issueStatus='CLOSED');

 

Alter table ISSUE_NOTES logging;

 

Issues Table

CREATE TABLE "CGMS_DEV"."ISSUES_COPY" 

 (        "ID" NUMBER(19,0) NOT NULL ENABLE,

          "NET_ELEMENT_ID" NUMBER(19,0) NOT NULL ENABLE,

          "ISSUEOCCURTIME" TIMESTAMP (0) NOT NULL ENABLE,

          "ISSUELASTUPDATETIME" TIMESTAMP (0),

          "ISSUE_TYPE_ID" NUMBER(19,0) NOT NULL ENABLE,

          "ISSUEDISPLAYNAME" VARCHAR2(255 CHAR) NOT NULL ENABLE,

          "ISSUEMESSAGE" VARCHAR2(4000 BYTE),

          "LAT" FLOAT(126) DEFAULT '0' NOT NULL ENABLE,

          "LNG" FLOAT(126) DEFAULT '0' NOT NULL ENABLE,

          "GEOHASH" VARCHAR2(255 CHAR),

          "EID" VARCHAR2(255 CHAR) NOT NULL ENABLE,

          "ISSUESTATUS" VARCHAR2(255 CHAR) DEFAULT 'OPEN',

          "MODULEID" VARCHAR2(255 CHAR) DEFAULT null,

          "DOMAIN_ID" NUMBER(19,0) DEFAULT 1 NOT NULL ENABLE,

           CONSTRAINT "PRIMARY_ISSUE_C" PRIMARY KEY ("LNG", "LAT", "ID"));

CREATE INDEX "CGMS_DEV"."EID_ISSUE_C" ON "CGMS_DEV"."ISSUES_COPY" (NLSSORT("EID",'nls_sort=''BINARY_CI'''));

CREATE INDEX "CGMS_DEV"."EID_ISSUESTATUS_C" ON "CGMS_DEV"."ISSUES_COPY" (NLSSORT("EID",'nls_sort=''BINARY_CI'''), "ISSUESTATUS");

CREATE INDEX "CGMS_DEV"."GEOHASH_ISSUE_C" ON "CGMS_DEV"."ISSUES_COPY" ("GEOHASH");

CREATE INDEX "CGMS_DEV"."ISSUE_C" ON "CGMS_DEV"."ISSUES_COPY" ("ID") ;

CREATE INDEX "CGMS_DEV"."ISSUELASTUPDATETIME_C" ON "CGMS_DEV"."ISSUES_COPY" ("ISSUELASTUPDATETIME");

CREATE INDEX "CGMS_DEV"."ISSUESTATUS_LASTUPDATETIME_C" ON "CGMS_DEV"."ISSUES_COPY" ("ISSUELASTUPDATETIME", "ISSUESTATUS");

CREATE INDEX "CGMS_DEV"."ISSUE_STATUS_C" ON "CGMS_DEV"."ISSUES_COPY" (NLSSORT("ISSUESTATUS",'nls_sort=''BINARY_CI'''));

CREATE INDEX "CGMS_DEV"."ISSUE_STATUS_TYPE_C" ON "CGMS_DEV"."ISSUES_COPY" ("ISSUESTATUS", "ISSUE_TYPE_ID");

 

Alter table ISSUES nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.ISSUES_COPY Select * FROM  cgms_dev.ISSUES WHERE  lastUpdate < (SYSDATE-((select RETENTIONDAYS from Pruning_jobs where NAME='IssuePruningJob')+1));

 

Alter table ISSUES logging;

Delete the Table Data

drop table cgms_dev.ISSUE_NOTES_COPY;

drop table cgms_dev.ISSUES;

Rename the Copy Table to the Original Table

Rename ISSUE_NOTES_COPY to ISSUE_NOTES;

Rename ISSUES_COPY to ISSUES;

 

Job Items Table

CREATE TABLE "CGMS_DEV"."JOB_ITEMS_COPY"

   ("ID" NUMBER(19,0) NOT NULL ENABLE,

            "JOB_ID" NUMBER(19,0) NOT NULL ENABLE,

            "SUBMITTEDAT" TIMESTAMP (0),

            "PROCESSEDAT" TIMESTAMP (0),

            "COMPLETEDAT" TIMESTAMP (0),

            "STATUS" NUMBER(3,0) DEFAULT '0' NOT NULL ENABLE,

            "DETAILS" CLOB,

            "ITEMVALUE" VARCHAR2(255 CHAR) NOT NULL ENABLE,

            "ITEMUID" VARCHAR2(255 CHAR) NOT NULL ENABLE,

            "NODE" VARCHAR2(255 CHAR),

            "LASTUPDATED" TIMESTAMP (0), CONSTRAINT "PRIMARY_JOB_ITEMS1" PRIMARY KEY ("ID"));

CREATE UNIQUE INDEX "CGMS_DEV"."JOB_ITEMS_ITEMUID_C" ON "CGMS_DEV"."JOB_ITEMS_COPY" (NLSSORT("ITEMUID",'nls_sort=''BINARY_CI''')) ;

CREATE INDEX "CGMS_DEV"."JOB_ITEMS_JOB_ID_C" ON "CGMS_DEV"."JOB_ITEMS_COPY" ("JOB_ID") ;   CREATE INDEX "CGMS_DEV"."JOB_ITEMS_STATUS_C" ON "CGMS_DEV"."JOB_ITEMS_COPY" ("STATUS") ;

 

Alter table JOB_ITEMS nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.JOB_ITEMS_COPY Select * FROM  cgms_dev.job_items where job_id in (select id from cgms_dev.jobs where completedAt <= trunc(sysdate-((select RETENTIONDAYS from Pruning_jobs where NAME='JobEnginePruningJob')+1)));

 

Alter table JOB_ITEMS logging;

 

Jobs Table

CREATE TABLE "CGMS_DEV"."JOBS_COPY"

   ("ID" NUMBER(19,0) NOT NULL ENABLE,

            "NAME" VARCHAR2(255 CHAR), 
	
            "USERNAME" VARCHAR2(255 CHAR), 

            "TOTALCOUNT" NUMBER(10,0),

            "SUCCESSCOUNT" NUMBER(10,0),

            "FAILURECOUNT" NUMBER(10,0),

            "STATUS" VARCHAR2(255 CHAR),

            "SUBMITTEDAT" TIMESTAMP (0),

            "UPDATEDAT" TIMESTAMP (0),

            "FILENAME" VARCHAR2(255 CHAR),

            "TYPE" NUMBER(10,0),

            "PROCESSEDAT" TIMESTAMP (0),

            "COMPLETEDAT" TIMESTAMP (0),

            "OPUID" VARCHAR2(255 CHAR),

            "DETAILS" CLOB,

            "SCHEDULEDFOR" TIMESTAMP (0),

            "ITEMDATACLASSNAME" VARCHAR2(255 CHAR),

            "DOMAIN_ID" NUMBER(19,0) DEFAULT 1 NOT NULL ENABLE,

             CONSTRAINT "PRIMARY_37_C" PRIMARY KEY ("ID"));

CREATE INDEX "CGMS_DEV"."FILENAME_C" ON "CGMS_DEV"."JOBS_COPY" ("FILENAME");

CREATE INDEX "CGMS_DEV"."JOBS_OPUID_C" ON "CGMS_DEV"."JOBS_COPY" (NLSSORT("OPUID",'nls_sort=''BINARY_CI'''));

CREATE UNIQUE INDEX "CGMS_DEV"."JOB_NAME_UDX_C" ON "CGMS_DEV"."JOBS_COPY" (NLSSORT("NAME",'nls_sort=''BINARY_CI'''));

CREATE INDEX "CGMS_DEV"."SUBMITTEDAT_C" ON "CGMS_DEV"."JOBS_COPY" ("SUBMITTEDAT");

CREATE INDEX "CGMS_DEV"."UPDATEDAT_1_C" ON "CGMS_DEV"."JOBS_COPY" ("UPDATEDAT");

CREATE INDEX "CGMS_DEV"."USERNAME_1_C" ON "CGMS_DEV"."JOBS_COPY" ("USERNAME");

 

Alter table JOBS_COPY nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.JOBS_COPY Select * FROM  cgms_dev.JOBS WHERE  lastUpdate < (SYSDATE-((select RETENTIONDAYS from Pruning_jobs where NAME='JobEnginePruningJob')+1));

 

Alter table JOBS_COPY nologging;

Delete the Table Data

Drop table cgms_dev.JOB_ITEMS;

Drop table cgms_dev.JOBS;

Rename the Copy Table to the Original Table

RENAME JOB_ITEMS_COPY to JOB_ITEMS;

RENAME JOBS_COPY to JOBS;

 

SPdownrouters Table

CREATE TABLE "CGMS_DEV"."SPDOWNROUTERS_COPY"

   ("ID" NUMBER(19,0) NOT NULL ENABLE,

            "DOWNROUTERS" NUMBER(10,0) DEFAULT 0 NOT NULL ENABLE,

            "LASTUPDATE" TIMESTAMP (6) DEFAULT to_date('01-JAN-1970 00:00:00', 'dd-MON-yyyy hh24:mi:ss') NOT NULL ENABLE,

            "NAME" VARCHAR2(255 CHAR) NOT NULL ENABLE,

            "CELLID" VARCHAR2(255 CHAR) DEFAULT '',

            "MODEMID" NUMBER(10,0) DEFAULT 1,

             PRIMARY KEY ("ID"));

CREATE UNIQUE INDEX "CGMS_DEV"."NAME_CELLID_LASTUPDATE_MID_IDX_C" ON "CGMS_DEV"."SPDOWNROUTERS_COPY" ("NAME", "CELLID", "LASTUPDATE", "MODEMID");

 

Alter table SPDOWNROUTERS nologging;

Copy the Data to a New Table

Insert INTO cgms_dev.SPDOWNROUTERS_COPY Select * FROM  cgms_dev.SPDOWNROUTERS WHERE  lastUpdate < (SYSDATE-((select RETENTIONDAYS from Pruning_jobs where NAME='SPDownRoutersPruningJob')+1));

 

Alter table SPDOWNROUTERS logging;

Delete the Table Data

Drop table cgms_dev.SPDOWNROUTERS;

Rename the Copy Table to the Original Table

RENAME SPDOWNROUTERS_COPY to SPDOWNROUTERS;

For More Information

If you require further assistance, or if you have any further questions regarding this field notice, please contact the Cisco Systems Technical Assistance Center (TAC) by one of the following methods:

• Open a service request on Cisco.com
• By email or telephone

Receive Email Notification For New Field Notices

My Notifications—Set up a profile to receive email updates about reliability, safety, network security, and end-of-sale issues for the Cisco products you specify.