Ensure Enough Space is Available in Tablespace APPS_TS_MEDIA

Calculate an estimated size of the old implementation of the LOB basic file and allocate 25% more tablespace size for the tablespace APPS_TS_MEDIA.

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:

   Note:

   Execute the following SQL statement for all of the following tables:

   • CDR_INSTALLATION_LOG
   • CDR_OUTPUT_BLOBS
   • CDR_OUTPUT_CLOBS
   • CDR_INSTALL_SCRIPTS
   • DME_DISC_CSV_FILES

   WITH lobs AS (
   		SELECT /*+ materialize cardinality(2) */
   		*
   		FROM
   		(
   		SELECT
   		owner,
   		segment_name
   		FROM
   		dba_lobs
   		WHERE
   		table_name = 'enter the table name'
   		UNION
   		SELECT
   		owner,
   		index_name segment_name
   		FROM
   		dba_lobs
   		WHERE
   		table_name = 'enter the table name'
   		)
   		)
   		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 additional tablespace size required for migration which is equal to the 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 additional space required for the tablespace = 100 * 1.25 = 125 GB.
4. Add the datafiles, as required by executing the following command to make sure enough space that was calculated at step 3 is avaiable in the tablespace:

   ALTER TABLESPACE APPS_TS_MEDIA 
   			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 30 GB of operating system file size limit using ASM storage "+DATA":

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

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

Note:

Make sure your SYSTEM tablespace has enough space which is managed locally and not managed by dictionary.