Configure Automated Workarea Tablespace Creation

Configuration required to enable the automated workarea tablespace creation.

1. Verify that all the following objects are created. To do so, execute the following:

   select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS, CREATED, LAST_DDL_TIME from dba_objects
   WHERE OBJECT_NAME IN ('CDR_WA_TBLSPC_NM_CNTR','CDR_WA_METADATA','CDR_WA_TLSPC_AUDT','CDR_WA_METADATA_U1', 'CDR_CREATE_WA_TABLESPACE');
   

2. Configure the metadata in the metadata table CDR_WA_METADATA. To do so, log in to the database as APPS and execute the following script:
   @$CDR_TOP/patch/115/sql/cdrwatblspcinsmtdt.sql
   The script prompts you for the different inputs as shown below:

   **********  DO YOU WANT TO ENABLE AUTO TABLESPACE CREATION WHEN THERE IS SPACE ISSUE (YES / NO) ***********
   AUTO TABLESPACE CREATION: (YES/NO) <ENTER REQUIRED VALUE>
   **********  ENTER DATAFILE DIRECTORY WHERE DATAFILES WILL BE CREATED ***********
   DATAFILE DIRECTORY <ENTER COMPLETE DATAFILE PATH WHERE NEW TABLESPACES WILL BE CREATED >
   **********  ENABLE SENDING EMAIL ALERT (YES / NO ) ***********
   SEND ALERT EMAIL (YES / NO) <ENTER REQUIRED VALUE>
   

   After the script is executed successfully, verify the content of the metadata table CDR_WA_METADATA.

3. To enable the automated workarea tablespace creation, perform the following steps:
   1. As APPS user, execute the following command:
      exec Cdr_Create_WA_Tablespace.P_WA_BIGFILE_TBLSPC(<NUMBER OF TABLESPACES>);
      Execute the following SQL to verify if the new tablespace has been created successfully:

      select *
               from dba_tablespaces
               where tablespace_name like 'CDR_WA_OBJ_BFL_TS_%';

      Execute the following SQL to verify if the new tablespace-related entry is present at metadata table CDR_WA_TBLSPC_NM_CNTR:

      select *
               from  CDR_WA_TBLSPC_NM_CNTR;

   2. Schedule the weekly monitor job. Execute the following command:

      Note:

      You can set the repeat_interval as per the requirement.

      BEGIN
      DBMS_SCHEDULER.create_job (
      job_name => 'WA_BFL_TBLSPC_GRP_MONITOR',
      job_type => 'PLSQL_BLOCK',
      job_action => 'begin Cdr_Create_WA_Tablespace.P_WA_TBLSPC_FREESPACE(); end;',
      start_date => SYSTIMESTAMP,
      repeat_interval => 'FREQ=WEEKLY; BYDAY=SAT;',
      enabled => TRUE);
      END;
      /