Solved

Controlling Maximum Number of Connections to Oracle?

Posted on 2011-03-17
2
550 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
Comment Utility
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 32

Accepted Solution

by:
sarabande earned 400 total points
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now