Solved

Controlling Maximum Number of Connections to Oracle?

Posted on 2011-03-17
2
559 Views
Last Modified: 2012-05-11
Dear experts,

We have written a program in C++ (in Linux) connecting to an Oracle 9 database and manipulating some sort of data.  The program has been written in multi-threading manner.

Sometimes DBA admins complains that the program opens too many connections. How can we control this?

Note: The some part of code attached below.

BR,
GOCCILoader::GOCCILoader(IniParser* iniObj) {
    inputData=iniObj;
    poolName=(char*)calloc(1,100);
        
}

int GOCCILoader::stringToInteger(string value) {
    int integerValue;
    istringstream conversionStringStream(value);
    conversionStringStream >> integerValue;
    return integerValue;
}

int GOCCILoader::getEventIDFromDataFileName(string dataFilePath) {
    return stringToInteger(dataFilePath.substr(dataFilePath.find_last_of('_', dataFilePath.length()) + 1, (dataFilePath.find_last_of('.', dataFilePath.length()) - dataFilePath.find_last_of('_', dataFilePath.length()) - 1)));
}

bool GOCCILoader::startToLoad(){
    if(!connect())
        return false;

    string oracleDirectoryObjectName="";
    Log::logInformation("Creating oracle directory object if does not exist.", -1);
    if(!createOracleDirectoryObject(oracleDirectoryObjectName, inputData->getInputDirectory()))
    {
        cout<<"directory createion failed"<<endl;
        exit(EXIT_FAILURE);
    }

    tp_init(inputData->getThreadsNum(),inputData->getTerminationTimeLimit());
    
    //cout<<"thread pool is created"<<endl;
    list<string> fileList = inputData->getFileListWillBeParsed();
    

    cout<<endl<<"input files are being loaded to database......";
    cout.flush();

    list<string>::iterator fileIterator;

    int i=0;
    for(fileIterator=fileList.begin();fileIterator!=fileList.end();fileIterator++){

        i++;
        string filePath = *fileIterator;
        LoaderJob * job = new LoaderJob(i,filePath,oracleDirectoryObjectName,inputData,envHandle,sessionPoolHandle,poolName);
        tp_run( job, NULL, true );

        if(getTimeOutStatus())
            break;
    }
    tp_done();

     if(getTimeOutStatus())
         cout<<"  successfully terminated!!"<<endl;
     else
         cout<<"  completed!!"<<endl<<endl;

    dropOracleDirectoryObject(oracleDirectoryObjectName);
object.", -1);

    disconnect();
    
    if(getTimeOutStatus())
        Log::logInformation("Program has been terminated due to timeout..",-1);
}

int GOCCILoader::ociCheckError(OCIError *errorHandle,sword oci_status) {
  char errText[MAX_ERRTEXT_LEN+1];
  sb4 err;

  if ( errorHandle==NULL ) {
    OCIErrorGet(envHandle,1,NULL,&err,(text *)errText,MAX_ERRTEXT_LEN,OCI_HTYPE_ERROR);
    printf("%s\n",errText);;
    return OCI_ERROR;
  }

  switch ( oci_status ) {
    case OCI_SUCCESS:
      return 0;
    case OCI_ERROR:
    case OCI_SUCCESS_WITH_INFO:
      OCIErrorGet(errorHandle,1,NULL,&err,(text *)errText,MAX_ERRTEXT_LEN,OCI_HTYPE_ERROR);
      printf("%s\n",errText);
      break;
    case OCI_NEED_DATA:
      printf("OCI_NEED_DATA\n");
      break;
    case OCI_NO_DATA:
      // This "error" is returned when you ask for N rows but receive
      // <N rows. It really means "You asked for N rows but <N rows
      // satisfied the query. When N=1 this error is true there is no data
      // because 0 is the only value <N=1.
      break;
    case OCI_INVALID_HANDLE:
      printf("OCI_INVALID_HANDLE\n");
      break;
    case OCI_STILL_EXECUTING:
      printf("OCI_STILL_EXECUTING\n");
      break;
    case OCI_CONTINUE:
      printf("OCI_CONTINUE\n");
      break;
    default:
      printf("Unknown Oracle error: Code %d\n",oci_status);
      break;
  }
  return oci_status;
}

bool GOCCILoader::connect(){

//
// Allocate OCI server
//  if ( ociCheckError(errorHandle,OCIHandleAlloc(envHandle,(dvoid **)&serverHandle,OCI_HTYPE_SERVER,0,NULL))<0 )
//    return false;
//
//  // Allocate OCI service
//  if ( ociCheckError(errorHandle,OCIHandleAlloc(envHandle,(dvoid **)&serviceHandle,OCI_HTYPE_SVCCTX,0,NULL))<0 )
//    return false;
//
//  // Attach to the server specified by sid
//  if ( ociCheckError(errorHandle,OCIServerAttach(serverHandle,errorHandle,(text *)inputData->getDBName().c_str(),strlen(inputData->getDBName().c_str()),0))<0 )
//    return false;
//
//  // Set the server attribute for the service
//  if ( ociCheckError(errorHandle,OCIAttrSet(serviceHandle,OCI_HTYPE_SVCCTX,serverHandle,0,OCI_ATTR_SERVER,errorHandle))<0 )
//    return false;

  // Allocate OCI Session
//  if ( ociCheckError(errorHandle,OCIHandleAlloc(envHandle,(dvoid **)&sessionHandle,OCI_HTYPE_SESSION,0,NULL))<0 )
//    return false;

//  // Set the username attibute for the session
//  if ( ociCheckError(errorHandle,OCIAttrSet(sessionHandle,OCI_HTYPE_SESSION,(text*)inputData->getDBUserName().c_str(),strlen(inputData->getDBUserName().c_str()),OCI_ATTR_USERNAME,errorHandle))<0 )
//    return false;
//
//  // Set the password attibute for the session
//  if ( ociCheckError(errorHandle,OCIAttrSet(sessionHandle,OCI_HTYPE_SESSION,(text*)inputData->getDBUserPassword().c_str(),strlen(inputData->getDBUserPassword().c_str()),OCI_ATTR_PASSWORD,errorHandle))<0 )
//    return false;
//
//  // Start the session
//  if ( ociCheckError(errorHandle,OCISessionBegin(serviceHandle,errorHandle,sessionHandle,OCI_CRED_RDBMS,OCI_DEFAULT))<0 )
//    return false;
//
//  // Set session attribute for the service
//  if ( ociCheckError(errorHandle,OCIAttrSet(serviceHandle,OCI_HTYPE_SVCCTX,sessionHandle,0,OCI_ATTR_SESSION,errorHandle))<0 )
//      return false;

  // Allocate OCI authentication handler


    // Initialise OCI

  OCIInitialize(OCI_THREADED,NULL,NULL,NULL,NULL);

  // Initialise OCI environment
  OCIEnvInit(&envHandle,OCI_DEFAULT,0,NULL);

 continues on ...

Open in new window

0
Comment
Question by:GurcanK
2 Comments
 
LVL 19

Assisted Solution

by:Thommy
Thommy earned 100 total points
ID: 35156253
You can limit the sessions of an Oracle user.

Ask your dba to create a new profile with session limit and set this new profile for your user...

Example: Limit sessions to 5

CREATE PROFILE NEW_PROFILE LIMIT SESSIONS_PER_USER 5

ALTER USER dbuser PROFILE NEW_PROFILE


In your application you have to handle the Oracle error if your app requires more than 5 connections...
0
 
LVL 34

Accepted Solution

by:
sarabande earned 400 total points
ID: 35157289
if all connections happen with GOCCILoader::connect and all disconnections with GOCCILoader::disconnect you could count the number of open connections easily by using a counter as member of class GOCCILoader. make the member volatile do that evey thread recognizes changes.

Sara
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question