Create Tablespace cdr_data_ts

Create a separate tablespace cdr_data_ts for the LOB storage. You must calculate an estimated size of the old implementation of the LOB basic file and allocate 25% more tablespace size for the tablespace cdr_data_ts. This tablespace will store the migrated secure file implementation.

1. Connect to Oracle DMW PDB as SYS or SYSTEM user.
2. Calculate the approximate size of the existing LOB by executing the following SQL statement.

   WITH lobs AS (
       SELECT /*+ materialize cardinality(2) */
           *
       FROM
           (
               SELECT
                   owner,
                   segment_name
               FROM
                   dba_lobs
               WHERE
                   table_name = 'CDR_JOB_LOG'
               UNION
               SELECT
                   owner,
                   index_name segment_name
               FROM
                   dba_lobs
               WHERE
                   table_name = 'CDR_JOB_LOG'
           )
   )
   SELECT
       round(SUM(bytes) / 1024 / 1024) AS size_mb,
       round(SUM(bytes) / 1024 / 1024/1024) AS size_gb,
       round(SUM(bytes) / 1024 / 1024 / 1024 / 1024, 2) AS size_tb
   FROM
       dba_segments
   WHERE
       ( owner,
         segment_name ) IN (
           SELECT
               *
           FROM
               lobs
       );

   The SQL provides output in MB, GB, and TB.
3. Calculate the number of datafiles.

   Number of required datafiles = Estimated size from step 1 (note the number from SIZE_GB) x 1.25 (25% over allocation) / OS max file size limit

   For example, if the estimated size from step 1 is 875GB and the max file size limit is 30GB, then:
   Number of required datafiles = 875 x 1.25 / 30 = 36.45
   Round this calculated decimal number to 37.
4. Make sure there is enough space available in the ASM disk group. In case you do not use the ASM disk group, make sure there is enough disk space available at the operating system directory where the datafiles will be created.
5. Create the tablespace cdr_data_ts by executing the following command:

   CREATE TABLESPACE cdr_data_ts DATAFILE
       'fully qualified datafile name' SIZE 500M
       AUTOEXTEND ON NEXT 100M MAXSIZE max size limit of the file, typically 30G on Linux OS
       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M SEGMENT SPACE MANAGEMENT AUTO;

   For example, assuming 30GB operating system file size limit using ASM storage "+DATA":

   CREATE TABLESPACE cdr_data_ts DATAFILE '+DATA' SIZE 500M
      AUTOEXTEND ON NEXT 100M MAXSIZE 30G
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M SEGMENT SPACE MANAGEMENT AUTO;

6. Add the datafiles, as required by executing the following command:
   ALTER TABLESPACE cdr_data_ts ADD DATAFILE 'fully qualified datafile name' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE max size limit of the file, typically 30G on Linux OS;
   For example, assuming 30GB operating system file size limit using ASM storage "+DATA":

   ALTER TABLESPACE cdr_data_ts ADD DATAFILE '+DATA' SIZE 500M
      AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

7. Assign a quota for the tablespace cdr_data_ts to the user CDR and optionally to APPS.
   ALTER USER CDR QUOTA UNLIMITED ON cdr_data_ts;
   ALTER USER APPS QUOTA UNLIMITED ON cdr_data_ts;