Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Controlling Maximum Number of Connections to Oracle?

Posted on 2011-03-17
2
Medium Priority
?
566 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 400 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 35

Accepted Solution

by:
sarabande earned 1600 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Windows programmers of the C/C++ variety, how many of you realise that since Window 9x Microsoft has been lying to you about what constitutes Unicode (http://en.wikipedia.org/wiki/Unicode)? They will have you believe that Unicode requires you to use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

721 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