Submit a ticketCall us

Solarwinds & Cisco Live! Barcelona
Join us from the 29th of January to the 2nd of February at Cisco Live 2018 in Barcelona, where we will continue to show how monitoring the network with SolarWinds will keep you ahead of the game. At our booth (WEP 1A), we will demonstrate how SolarWinds network solutions can help. As a bonus, we are also hosting a pre-event webinar - Blame the Network, Hybrid IT Edition with our SolarWinds Head Geek™, Patrick Hubbard on January 24th - GMT (UTC+0): 10:00 a.m. to 11:00 a.m. There's still time to RSVP.

Home > Success Center > Archive > 2017December14 - Deletes > Data truncation errors in Oracle plan poll job on SQL Server repositories

Data truncation errors in Oracle plan poll job on SQL Server repositories

Table of contents


Early releases of DPA had insufficiently sized some numeric columns for the Repository tables that store explain plan text for monitored Oracle database instances (SQL Server Repositories only). The names of these tables are prefixed with 'CONPT_' and end with the database instance's ID (as specified in the COND table).

This issue may cause data truncation errors when numeric values retrieved from the explain plan exceed the space allotted in the corresponding column in the Repository table. The error that is seen in the log is:

    Monitor for database [DATABASE_NAME] failed in job [PlanPollJob] due to [Data truncation]

If this error is observed for a database instance, the plan table (CONPT_<databaseid>) will need to be resized. Due to the potential size of these tables and the restriction of SQL Server to only allow modification of a single column at a time, resizing the necessary columns is best done by recreating the table.


  • All versions of DPA


  1. Determine the database affected.
    • The database name is evident in the error message.
    • The database ID is in the SQL embedded in error message (ID is number following CONPT_)
  2. Stop the monitor for this database instance (??? stop monitor, disable plan poll ???).
  3. Rename the original CONPT table using sp_rename (inserting the correct database ID).
    • sp_rename 'CONPT_<database_id>','CONPT_<database_id>_OLD'</database_id></database_id>
  4. Create the new table using the following script  (inserting the correct database ID):

    CREATE TABLE CONPT_<databaseid> (</databaseid>

      [PLAN_HASH_VALUE]    Numeric(38,0) NOT NULL,

      [ID]                 Numeric(38,0) NOT NULL,

      [TIMESTAMP]          Datetime,

      [OPERATION]          Varchar(50),

      [OPTIONS]            Varchar(50),

      [OBJECT_NODE]        Varchar(50),

      [OBJECT#]            Numeric(38,0),

      [OBJECT_OWNER]       Varchar(50),

      [OBJECT_NAME]        Varchar(50),

      [OBJECT_ALIAS]       Varchar(100),

      [OBJECT_TYPE]        Varchar(50),

      [OPTIMIZER]          Varchar(50),

      [PARENT_ID]          Numeric(38,0),

      [DEPTH]              Numeric(38,0),

      [POSITION]           Numeric(38,0),

      [SEARCH_COLUMNS]     Numeric(38,0),

      [COST]               Numeric(38,0),

      [CARDINALITY]        Numeric(38,0),

      [BYTES]              Numeric(38,0),

      [OTHER_TAG]          Varchar(50),

      [PARTITION_START]    Varchar(64),

      [PARTITION_STOP]     Varchar(64),

      [PARTITION_ID]       Numeric(38,0),

      [OTHER]              Varchar(4000),

      [DISTRIBUTION]       Varchar(50),

      [CPU_COST]           Numeric(38,0),

      [IO_COST]            Numeric(38,0),

      [TEMP_SPACE]         Numeric(38,0),

      [ACCESS_PREDICATES]  Varchar(4000),

      [FILTER_PREDICATES]  Varchar(4000),

      [PROJECTION]         Varchar(4000),

      [TIME]               Numeric(38,0),

      [QBLOCK_NAME]        Varchar(50),

      [REMARKS]            Varchar(4000),



  5. Insert existing data from the original table into the new table using the following statement:
    • insert into CONPT_<database_id> select * from CONPT_<database_id>_OLD</database_id></database_id>
  6. Restart the monitor for the database instance.

    Once it is determined that the error has been resolved and plan text is being successfully collected (evidenced by new rows appearing in the new CONPT table), the old table can be dropped.

Last modified