mkdir <directory name>

mkdir D:\datapump

sql> create directory EXP_DIR as '<path to operating system directory created previous step>';

sql> create directory EXP_DIR as 'D:\datapump';

sql> grant all on directory EXP_DIR to <user performing the export dump>;

sql> grant all on directory EXP_DIR to pdmadmin;

expdp userid=system/<password> directory=EXP_DIR
dumpfile=<dump_file_name>%u.dmp logfile=<log_file_name>.log
schemas=<staging db user> filesize=<size>M

read -p "Please, provide PDB_NAME: " PDB_NAME
export PDB_NAME
export LST_FILE_NAME=${PDB_NAME?}.lst
echo "
SET FEEDBACK off;
SET HEADING off;
SET TRIMSPOOL ON
WHENEVER SQLERROR EXIT SQL.SQLCODE;
ALTER SESSION SET container=${PDB_NAME?};
SPOOL ${LST_FILE_NAME?};
SELECT tablespace_name || '*' || ceil((USED_SPACE*8192*1.2)/(34359738368)) files_required
FROM dba_tablespace_usage_metrics
WHERE tablespace_name NOT IN ('TEMP','SYSTEM','SYSAUX','UNDOTBS1');
" | sqlplus -s / as sysdba
ls -l ${LST_FILE_NAME?}

SET verify OFF echo OFF feedback OFF heading OFF trimspool ON pagesize 0
WHENEVER SQLERROR EXIT SQL.SQLCODE;
ALTER SESSION SET container=&1;
SPOOL &1..mdt
SELECT 'DB:NAME:'||sys_context('USERENV','DB_NAME') FROM dual;
SELECT 'DB:VERSION:' || REGEXP_SUBSTR(version_full, '^\d+\.\d+') FROM v$instance;
SELECT 'DB:COMPATIBLE:' || value FROM database_compatible_level;
SELECT 'DB:TIMEZONE_VERSION:' || version FROM v$timezone_file;
SELECT 'DB:TS_TOTAL_SIZE_GB:' || (SELECT CEIL(SUM(bytes)/1024/1024/1024) FROM (SELECT tablespace_name, bytes, maxbytes FROM dba_data_files
UNION ALL
SELECT tablespace_name, bytes, maxbytes FROM dba_temp_files))
FROM dual;
SELECT 'NLS:CHARACTERSET:' || value FROM nls_database_parameters WHERE UPPER(parameter) = 'NLS_CHARACTERSET';
SELECT 'NLS:NCHAR_CHARACTERSET:' || value FROM nls_database_parameters WHERE UPPER(parameter) = 'NLS_NCHAR_CHARACTERSET';
SELECT 'SCHEMA:NAME:'||UPPER('&2') FROM dual;
SELECT 'SCHEMA:TOTAL_OBJECT_COUNT:' || (SELECT COUNT(*) FROM dba_objects WHERE UPPER(owner)=UPPER('&2')) FROM dual;
SELECT 'SCHEMA:DATA_SIZE_GB:' || (SELECT TO_CHAR(CEIL(SUM(bytes)/1024/1024/1024)) FROM dba_segments WHERE owner=UPPER('&2') AND segment_type NOT LIKE '%INDEX%') FROM dual;
SELECT 'SCHEMA:TOTAL_SIZE_GB:' || (SELECT TO_CHAR(CEIL(SUM(bytes)/1024/1024/1024)) FROM dba_segments WHERE owner=UPPER('&2')) FROM dual;
SELECT 'EXPORT:DB_REQUIRED_SIZE_GB:' || (SELECT TO_CHAR(CEIL(SUM(bytes)/1024/1024/1024)) FROM dba_segments WHERE UPPER(owner) = UPPER('&2')) FROM dual;
PROMPT # Tablespace Summary:
PROMPT # Format: TS:<TYPE>:<TABLESPACE_NAME>:<USED_GB>:<ALLOCATED_GB>:<FILE_COUNT>:<MAX_GB>
SELECT 'TS:' || CASE
WHEN ts.tablespace_name IN ('SYSTEM', 'SYSAUX') THEN ts.tablespace_name
WHEN ts.contents = 'UNDO' THEN 'UNDO'
WHEN ts.contents = 'TEMPORARY' THEN 'TEMPORARY'
ELSE 'USER'
END || ':' ||
ts.tablespace_name || ':' ||
CEIL(NVL(seg.segment_size_gb, 0)) || ':' ||
CEIL(df.total_size_gb) || ':' ||
df.file_count || ':' ||
CEIL(df.max_size_gb)
FROM (
SELECT tablespace_name,
COUNT(*) AS file_count,
CEIL(SUM(bytes)/1024/1024/1024) AS total_size_gb,
CEIL(SUM(DECODE(maxbytes, 0, bytes, maxbytes))/1024/1024/1024) AS max_size_gb
FROM (
SELECT tablespace_name, bytes, maxbytes FROM dba_data_files
UNION ALL
SELECT tablespace_name, bytes, maxbytes FROM dba_temp_files
)
GROUP BY tablespace_name
) df
LEFT JOIN (
SELECT tablespace_name,
CEIL(SUM(bytes)/1024/1024/1024) AS segment_size_gb
FROM dba_segments
GROUP BY tablespace_name
) seg
ON df.tablespace_name = seg.tablespace_name
JOIN dba_tablespaces ts
ON df.tablespace_name = ts.tablespace_name
ORDER BY 1
/
EXIT

read -p "Please, provide PDB_NAME: " PDB_NAME
read -p "Please, provide Staging DB User Name: " SCHEMA_NAME
export MDT_FILE_NAME=${PDB_NAME?}.mdt
sqlplus -s / as sysdba @db_metadata.sql "${PDB_NAME?}" "${SCHEMA_NAME?}"
ls -l ${MDT_FILE_NAME?}