Solved

Controlling Maximum Number of Connections to Oracle?

Posted on 2011-03-17
2
560 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle sqlplus query delimiter 8 53
Setting variables in a stored procedure 5 79
maximize the sum of fractions 33 75
how to solve "recursive" database tables 2 58
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
The goal of this video is to provide viewers with basic examples to understand how to create, access, and change arrays in the C programming language.
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…

739 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