Create Tablespace CDR_BLOB_DATA_TS

Note:

Create the tablespace before installing the 3.4.1 patch.

Create a separate tablespace CDR_BLOB_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_BLOB_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_OUTPUT_BLOBS'
   		UNION
   		SELECT
   		owner,
   		index_name segment_name
   		FROM
   		dba_lobs
   		WHERE
   		table_name = 'CDR_OUTPUT_BLOBS'
   		)
   		)
   		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 the output in MB, GB, and TB.
3. Calculate the tablespace size.
   Size of Tablespace = Estimated Size from step 2 (note the number from SIZE_GB / SIZE_TB) * 1.25 (25% over allocation)
   For example, if the estimated size from step 1 is 100 GB, then Size of Tablespace = 100 * 1.25 = 125 GB.
4. Make sure that there is enough space available in the ASM disk group. In case you do not use the ASM disk group, make sure that there is enough disk space available at the operating system directory where the data files are created.
5. Create the tablespace CDR_BLOB_DATA_TS by executing the following command:

   CREATE BIGFILE  TABLESPACE CDR_BLOB_DATA_TS DATAFILE 
   		'<fully qualified datafile name>' SIZE  <Size Calculated in Step 3>
   		AUTOEXTEND ON NEXT 25G
   		EXTENT MANAGEMENT LOCAL 
   		SEGMENT SPACE MANAGEMENT AUTO;

6. Assign a quota for the tablespace CDR_BLOB_DATA_TS to the users CDR , APPS, and APPLSYS.
   ALTER USER CDR QUOTA UNLIMITED ON CDR_BLOB_DATA_TS;
   ALTER USER APPS QUOTA UNLIMITED ON CDR_BLOB_DATA_TS;
   ALTER USER APPLSYS QUOTA UNLIMITED ON CDR_BLOB_DATA_TS;