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 | 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 |
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 ID | Headline |
---|---|
CSCvu55518 | Pruning of some database tables is not working |
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.
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:
If the databases are not pruned, the data continues to accumulate and might result in slower system response times at the user interface.
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.
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.
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:
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:
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:
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;
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:
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.
Unleash the Power of TAC's Virtual Assistance