7 Replies Latest reply on Feb 22, 2011 8:28 PM by Bill Robinson

    TEMP tablespace overflow during ETL script execution.

      Hello everyone,

       

      I've got the following error after running ETL script. I think almost for sure the problem is on the execution of that SQL sentence which during its execution is overflowing the TEMP tablespace and I'dont know why.The TEMP tablespace size is 4GB. Is there something I must do to avoid this error, like clean the DB before running ETL script or something like that? ¿How this SQL sentence execution can consume 4GBytes? ¿Do you think there is something wrong on my envioroment?. Any help will be appreciated.

       

       

      Thanks in advance and my best regards.

       

      #########################################################################################################################

      Session   Task

       

       

       

      Session   ID:

      1959001

      Session   Step:

      F_JOB_RUN.

      Order   Number:

      3

      No. of   Executions:

      1

      Task   Name:

      Integration

      Object:

      F_JOB_RUN

      Name:

      Update IS_LATEST_RUN

      Start   Date:

      2011-01-11 07:03:09.0

      End   Date:

      2011-01-11 07:12:57.0

      Duration   (s):

      588

      Status:

       

      Return   Code:

      1652

      Message:

      1652 : 72000 : java.sql.SQLException: ORA-01652:   unable to extend temp segment by 128 in tablespace TEMP

      java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in   tablespace TEMP

          at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
          at   oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
          at   oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
          at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
          at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)
          at   oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633)
          at   oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1086)
          at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)
          at   oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3057)
          at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)
          at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java)
          at   com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
          at   com.sunopsis.dwg.dbobj.SnpSessTaskSqlI.treatTaskTrt(SnpSessTaskSqlI.java)
          at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
          at   com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
          at   com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
          at   com.sunopsis.dwg.cmd.DwgCommandScenario.treatCommand(DwgCommandScenario.java)
          at   com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
          at com.sunopsis.dwg.cmd.e.i(e.java)
          at com.sunopsis.dwg.cmd.g.E(g.java)
          at com.sunopsis.dwg.dbobj.SnpScen.a(SnpScen.java)
          at com.sunopsis.dwg.dbobj.SnpScen.localExecuteSync(SnpScen.java)
          at   com.sunopsis.dwg.tools.StartScen.actionExecute(StartScen.java)
          at   com.sunopsis.dwg.function.SnpsFunctionBaseRepositoryConnected.execute(SnpsFunctionBaseRepositoryConnected.java)
          at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java)
          at   com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
          at   com.sunopsis.dwg.dbobj.SnpSessTaskSqlS.treatTaskTrt(SnpSessTaskSqlS.java)
          at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
          at   com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
          at   com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
          at   com.sunopsis.dwg.cmd.DwgCommandScenario.treatCommand(DwgCommandScenario.java)
          at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
          at com.sunopsis.dwg.cmd.e.i(e.java)
          at com.sunopsis.dwg.cmd.g.E(g.java)
          at com.sunopsis.dwg.dbobj.SnpScen.a(SnpScen.java)
          at   com.sunopsis.dwg.dbobj.SnpScen.localExecuteSync(SnpScen.java)
          at   com.sunopsis.dwg.tools.StartScen.actionExecute(StartScen.java)
          at   com.sunopsis.dwg.function.SnpsFunctionBaseRepositoryConnected.execute(SnpsFunctionBaseRepositoryConnected.java)
          at   com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java)
          at   com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
          at   com.sunopsis.dwg.dbobj.SnpSessTaskSqlS.treatTaskTrt(SnpSessTaskSqlS.java)
          at   com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
          at   com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
          at   com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
          at   com.sunopsis.dwg.cmd.DwgCommandScenario.treatCommand(DwgCommandScenario.java)
          at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
          at com.sunopsis.dwg.cmd.e.i(e.java)
          at com.sunopsis.dwg.cmd.e.y(e.java)
          at com.sunopsis.dwg.DwgJv.treatCmd(DwgJv.java)
          at com.sunopsis.dwg.DwgJv.main(DwgJv.java)
          at oracle.odi.Agent.main(Agent.java)

      Log   Indicator:

       

      No. of   Inserts:

       

      No. of   Deletes:

       

      No. of   Updates:

       

      No. of   Errors:

       

      No. of   Rows:

       

      Error   Tolerated?:

       

      Target   Server Name:

      phy_dw_svr_site1

      Dest   Logical Schema:

      BLREPORTS_LOGICAL

      Target   Context:

      CTX_SITE1

      Target   Transaction ID:

      1

      Commit   Target:

      0

      Target   Isolation Level:

       

      Destination   Command:

      update BSARA_DW.F_JOB_RUN
      set is_latest_run = 1
      where 1=1
      and run_start_time = (
      select max(run_start_time) from f_job_run fjr2
      where fjr2.job_id = f_job_run.job_id
      and fjr2.bl_site_id = f_job_run.bl_site_id and fjr2.is_deleted = 0)
      and job_run_id = (
      select max(job_run_id) from f_job_run fjr3
      where fjr3.job_id = f_job_run.job_id
      and fjr3.bl_site_id = f_job_run.bl_site_id
      and fjr3.run_start_time >= f_job_run.run_start_time and fjr3.is_deleted =   0)
      and bl_site_id=#BLREPORTS.var_bl_site

      Source   Server Name:

      phy_dw_svr_site1

      Source   Logical Schema:

      BLREPORTS_LOGICAL

      Source   Context:

      CTX_SITE1

      Source   Transaction ID:

       

      Source   Commit:

       

      Source   Isolation Level:

       

      Source   Command:

       

      ########################################################################################################################

       

        • 1. Re: TEMP tablespace overflow during ETL script execution.
          Bill Robinson

          What version and hotfix of bdssa are you using?

           

          There are some fixes in the latest hf for bdssa 2.0 but you may still need to open a support case on this.

          1 of 1 people found this helpful
          • 3. TEMP tablespace overflow during ETL script execution.
            Paul Seager-Smith

            In my experience 4GB for the TEMP DB seems low. We have seen cases when a number of things change - particularly with RBAC, where the TEMP DB grows quite a lot during the run_etl. This is with SQL-Server, so not directly comparable. However, I would recommend looking at more like 10GB for TEMPDB.

             

            This seems to be happening when copying JOB_RUN data, so have you been running more jobs than usual here?

             

            As Bill says, support will be able to help you track down what is consuming the TEMP space.

             

            Regards,

             

            Paul

            1 of 1 people found this helpful
            • 4. Re: TEMP tablespace overflow during ETL script execution.

              Hello everyone again,

               

              I have to say that finally I reported this issue to BMC. And as they said this situation is completly usual due to the amount of dat we hace in our BL Core Database.They also answered me asking me to run a script, they provided it to me, in order to estimate what size of TEMP Tablespace BDSSA we need to populate our BDSSA Data Warehouse.

               

              Here is attached that script,

               

              ##################################################################################

              /* Create table for sizing informaiton */

              drop table sizing_info;

              drop table sizing_master;

              create table sizing_info

              ( sr_no number,

                bsara_table_name char(70),

                om_table_name char(70),

                tab_date date,

                cnt  number

              );

               

              --Master data creation

              create table sizing_master

              ( sr_no number,

                bsara_table_name char(70),

                cnt  number

              );

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(1,'F_job_run',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(2,'F_agent_log',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(3,'F_server_usage',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(4,'F_audit_detail',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(5,'F_audit_fact_cnt',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(6,'F_audit_object',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(7,'F_audit_object_part',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(8,'F_audit_object_part_count',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(9,'F_audit_summary',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(10,'F_audit_trail',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(11,'F_compliance_rule_result',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(12,'F_component_exception',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(13,'F_job_pm_device',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(14,'F_job_run_event',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(15,'F_job_run_server',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(16,'F_job_depot_object',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(17,'F_bl_acl_auth',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(18,'F_object_auth_role',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(19,'F_bl_patch_analysis_res_data',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(20,'F_snapshot_part',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(21,'F_blfile_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(22,'F_com_object_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(23,'F_config_file_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(24,'F_acct_settings_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(25,'F_conf_file_content_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(26,'F_unix_daemon_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(27,'F_unix_sys_conf_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(28,'F_uptime_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(29,'F_os_user_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(30,'F_win_share_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(31,'F_sys_info_object_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(32,'F_cpu_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(33,'F_filesystem_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(34,'F_memory_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(35,'F_net_adapt_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(36,'F_net_conf_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(37,'F_os_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(38,'F_dot_net_assembly_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(39,'F_event_log_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(40,'F_generic_asset_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(41,'F_gpo_setting_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(42,'F_metabase_object_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(43,'F_os_group_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(44,'F_process_info_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(45,'F_registry_object_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(46,'F_service_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(47,'F_software_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(48,'F_vmware_object_snapshot',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(49,'F_compliance_ratio',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(50,'F_productivity_ratio',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(51,'F_audit_ratio',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(52,'F_maturity_ratio',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(53,'stg_compliance_rule_result',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(54,'stg_for_inventory_fact',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(55,'stg_for_obj_auth_role',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(56,'stg_for_audit_fact',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(57,'Object_overflow_detail ',0);

              insert into sizing_master(sr_no,bsara_table_name,cnt) values(58,'asset_path ',0);

              commit;

               

              -- Job run

              Prompt Foundation Domain

               

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 1,'f_job_run','f_job_run',start_time,count(job_run_id) from job_run group by start_time;

               

              -- Audit object

              Prompt Audit Domain

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 6,'f_audit_object','f_audit_object',jrun.start_time, count(ao.result_id) cnt

              from job_run jrun, job_result jr, audit_object ao

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = ao.result_id

              group by jrun.start_time

              order by 1;

               

              -- Audit object part

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 7,'f_audit_object_part','f_audit_object_part',jrun.start_time, count(aop.result_id) cnt

              from job_run jrun, job_result jr, audit_object_part aop

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = aop.result_id

              group by jrun.start_time

              order by 1;

               

              /* Audit detail tables started */

              Prompt Audit detail related tables --> Started

              -- Audit audit_blfile

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_blfile',jrun.start_time, count(af.result_id) cnt

              from job_run jrun, job_result jr, audit_blfile af

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = af.result_id

              group by jrun.start_time

              order by 1;

               

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_com_object',jrun.start_time, count(aco.result_id) cnt

              from job_run jrun, job_result jr, audit_com_object aco

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = aco.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_config_file_object',jrun.start_time, count(cfo.result_id) cnt

              from job_run jrun, job_result jr, audit_config_file_object cfo

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = cfo.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_dot_net_assembly',jrun.start_time, count(dna.result_id) cnt

              from job_run jrun, job_result jr, audit_dot_net_assembly dna

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = dna.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_event_log',jrun.start_time, count(ael.result_id) cnt

              from job_run jrun, job_result jr, audit_event_log ael

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = ael.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_gpo_setting',jrun.start_time, count(agp.result_id) cnt

              from job_run jrun, job_result jr, audit_gpo_setting agp

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = agp.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_metabase_object',jrun.start_time, count(amo.result_id) cnt

              from job_run jrun, job_result jr, audit_metabase_object amo

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = amo.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_os_group',jrun.start_time, count(aog.result_id) cnt

              from job_run jrun, job_result jr, audit_os_group aog

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = aog.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_os_user',jrun.start_time, count(aou.result_id) cnt

              from job_run jrun, job_result jr, audit_os_user aou

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = aou.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_registry_object',jrun.start_time, count(aro.result_id) cnt

              from job_run jrun, job_result jr, audit_registry_object aro

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = aro.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_service',jrun.start_time, count(asr.result_id) cnt

              from job_run jrun, job_result jr, audit_service asr

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = asr.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_software',jrun.start_time, count(aso.result_id) cnt

              from job_run jrun, job_result jr, audit_software aso

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = aso.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_system_info_object',jrun.start_time, count(asio.result_id) cnt

              from job_run jrun, job_result jr, audit_system_info_object asio

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = asio.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_vmware_object',jrun.start_time, count(avo.result_id) cnt

              from job_run jrun, job_result jr, audit_vmware_object avo

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = avo.result_id

              group by jrun.start_time

              order by 1;

              --

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 4,'f_audit_detail','audit_generic_asset',jrun.start_time, count(aga.result_id) cnt

              from job_run jrun, job_result jr, audit_generic_asset aga

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = aga.result_id

              group by jrun.start_time

              order by 1;

               

              Prompt Audit detail related tables --> Ended

               

              /* Audit detail table ended */

               

              -- Audit trail

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 10,'audit_trail','audit_trail',log_date,count(AUDIT_TRAIL_LOG_ID) from audit_trail group by log_date order by 1;

               

              Prompt Compliance Domain

               

              -- Compliance rule result

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 11,'f_compliance_rule_result','f_compliance_rule_result',result_date,count(COMPLIANCE_RULE_RESULT_ID) from compliance_rule_result group by result_date;

               

              Prompt Job activity Domain

              -- Job run event

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 14,'f_job_run_event','f_job_run_event',jrun.start_time, count(jre.result_id) cnt

              from job_run jrun, job_result jr, job_run_event jre

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = jre.result_id

              group by jrun.start_time

              order by 1;

               

              -- Job depot object

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 16,'f_job_depot_object','f_job_depot_object',sysdate, count(1) cnt

              from job_depot_object

              order by 1;

               

              Prompt Patch Domain

              -- Patch analysis

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 19,'f_bl_patch_analysis_res_data','f_bl_patch_analysis_res_data',jrun.start_time, count(pard.result_id) cnt

              from job_run jrun, job_result jr, bl_patch_analysis_res_data pard

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = pard.result_id

              group by jrun.start_time

              order by 1;

               

              Prompt Inventory Domain

              -- snapshot part

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 20,'f_snapshot_part','f_snapshot_part',jrun.start_time, count(sp.result_id) cnt

              from job_run jrun, job_result jr, snapshot_part sp

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = sp.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot blfile

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 21,'f_blfile_snapshot','f_blfile_snapshot',jrun.start_time, count(sb.result_id) cnt

              from job_run jrun, job_result jr, snapshot_blfile sb

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = sb.result_id

              group by jrun.start_time

              order by 1;

               

              -- Snapshot Com

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 22,'f_com_object_snapshot','f_com_object_snapshot',jrun.start_time, count(scb.result_id) cnt

              from job_run jrun, job_result jr, snapshot_com_object scb

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = scb.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot config file

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 23,'f_config_file_snapshot','f_config_file_object_snapshot',jrun.start_time, count(cfo.result_id) cnt

              from job_run jrun, job_result jr, snapshot_config_file_object cfo

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = cfo.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot dot net assembly

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 38,'f_dot_net_assembly_snapshot','f_dot_net_assembly_snapshot',jrun.start_time, count(dna.result_id) cnt

              from job_run jrun, job_result jr, snapshot_dot_net_assembly dna

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = dna.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot os user

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 29,'f_os_user_snapshot','f_os_user_snapshot',jrun.start_time, count(ou.result_id) cnt

              from job_run jrun, job_result jr, snapshot_os_user ou

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = ou.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot service

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 46,'f_service_snapshot','f_service_snapshot',jrun.start_time, count(ser.result_id) cnt

              from job_run jrun, job_result jr, snapshot_service ser

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = ser.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot software

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 47,'f_software_snapshot','f_software_snapshot',jrun.start_time, count(soft.result_id) cnt

              from job_run jrun, job_result jr, snapshot_software soft

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = soft.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot OS group

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 43,'f_os_group_snapshot','f_os_group_snapshot',jrun.start_time, count(og.result_id) cnt

              from job_run jrun, job_result jr, snapshot_os_group og

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = og.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot GPO setting

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 41,'f_gpo_setting_snapshot','f_gpo_setting_snapshot',jrun.start_time, count(gs.result_id) cnt

              from job_run jrun, job_result jr, snapshot_gpo_setting gs

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = gs.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot vmware object

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 48,'f_vmware_object_snapshot','f_vmware_object_snapshot',jrun.start_time, count(vob.result_id) cnt

              from job_run jrun, job_result jr, snapshot_vmware_object vob

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = vob.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot registry object

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 45,'f_registry_object_snapshot','f_registry_object_snapshot',jrun.start_time, count(rob.result_id) cnt

              from job_run jrun, job_result jr, snapshot_registry_object rob

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = rob.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot metabase object

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 42,'f_metabase_object_snapshot','f_metabase_object_snapshot',jrun.start_time, count(mob.result_id) cnt

              from job_run jrun, job_result jr, snapshot_metabase_object mob

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = mob.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot event log

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 39,'f_event_log_snapshot','f_event_log_snapshot',jrun.start_time, count(elog.result_id) cnt

              from job_run jrun, job_result jr, snapshot_event_log elog

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = elog.result_id

              group by jrun.start_time

              order by 1;

               

              -- snapshot system info object

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 31,'f_sys_info_object_snapshot','f_sys_info_object_snapshot',jrun.start_time, count(sio.result_id) cnt

              from job_run jrun, job_result jr, snapshot_system_info_object sio

              where jrun.job_run_id=jr.job_run_id

              and  jr.result_id = sio.result_id

              group by jrun.start_time

              order by 1;

               

              --object overflow details

              insert into sizing_info(sr_no,bsara_table_name,om_table_name, tab_date, cnt)

              select 57,'Object_overflow_detail','Object_overflow_detail','04-JUN-09',count(1) from Object_overflow_detail;

               

              -- Asset path calculation

              insert into sizing_info(sr_no,bsara_table_name,om_table_name,tab_date, cnt)

              select 58,'asset_path','asset_path',sysdate,sum(cnt) from (

              select count(1) as cnt from blfile

              union

              select count(1) as cnt from com_object

              union

              select count(1) as cnt from config_file_object

              union

              select count(1) as cnt from dot_net_assembly

              union

              select count(1) as cnt from event_log

              union

              select count(1) as cnt from generic_asset

              union

              select count(1) as cnt from gpo_setting

              union

              select count(1) as cnt from metabase_object

              union

              select count(1) as cnt from os_group

              union

              select count(1) as cnt from os_user

              union

              select count(1) as cnt from registry_object

              union

              select count(1) as cnt from service

              union

              select count(1) as cnt from software

              union

              select count(1) as cnt from system_info_object

              union

              select count(1) as cnt from vmware_object);

               

              commit;

               

              Spool get_om_count_for_sizing.txt;

              set head off;

              set linesize 150;

               

              select b.sr_no,rtrim(b.bsara_table_name)||','||nvl(sum(per_day_row),0) from (

              select sr_no,bsara_table_name,om_table_name,round(sum(cnt)/round((max(tab_date) - min(tab_date)+1),0),0) per_day_row

              from sizing_info

              group by sr_no,bsara_table_name,om_table_name

              order by sr_no) a, sizing_master b

              where a.sr_no(+) = b.sr_no

              group by b.sr_no,b.bsara_table_name

              order by 1;

              select 'Om_size',sum(bytes)/1024/1024/1024 from user_segments;

               

              spool off;

              ##################################################################################

               

              It must be executed over BL Core Database.As you will see in the results show what size of TEMP Tablespace you'll need with data your BL Core Database actually has.

               

              By other hand now I am trying to know how can I determine what information in our BL Core Database will use more space in our BDSSA during BDSSA Data Warehouse populating proccess in order to determine what information of all It could be sensitive to be removed allowing us to avoid to increase our TEMP Tablespace size.

               

              If anyone of you have any idea I would be aprreciated.

               

              Thanks in avanced.

              • 5. Re: TEMP tablespace overflow during ETL script execution.

                Hello again,

                 

                After walking a lot around this issue, I have to say there are many factors to take into consideration about this issue.

                 

                The first one is, the dimensioning process before installation it was wrong. The consultants that made this dimesion didn't it well. So this is the primary factor to keep in mind.

                 

                The second one but no the last is, the script I provided before just take in consideration the information you currently have in your BL Core Database but not the information you already have in you BSARA Datawarehouse database.So the estimations that BMC support team could made about this is not accureatly enough becasue they are not based on information you have in BL Core Database and information you already have in you BSARA Datawarehouse database together.

                 

                And finally, BSARA doesn't provide any kind of utility to mantain that datawarehouse database, purging obsolete information in it that you are not going to use anymore, as BL for Server Automation provides.

                 

                Due to this if you not have a huge TEMP tablespace to process this enormous ammount of information your TEMP tablespace sooner or later will be overflowed.

                 

                And that's all so far.

                 

                Thank you to everyone.

                • 6. TEMP tablespace overflow during ETL script execution.
                  Vinnie Lima

                  May not solve your issue, but make sure in the Cognos configuration utility you specify a directory/disk drive that can house large temporary files whenever reports are being run. 

                   

                  In previous engagement, we found that when reports were being run it was consuming all disk space as Cognos creates a series of temporary files, all of them of substantial size.

                   

                  Make sure that the Temp location has plenty of room for these dynamic files.

                  • 7. Re: TEMP tablespace overflow during ETL script execution.
                    Bill Robinson

                    Jose is talking TEMP db tablespace, not TEMP files for the application, but the TEMP file config is useful to have too.