Ensure Enough Space is Available in Tablespace
APPS_TS_MEDIA
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.
Connect to Oracle DMW PDB as SYS or SYSTEM user.
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.
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.
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;
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;