Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Controlling Maximum Number of Connections to Oracle?

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
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.  â€¦
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

971 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