DATAPUMP Table

define owner_schema='BAAN' (char)
define directory_dump=' DATA_PUMP_DIR_OGG' (char)
define datapump_file='export.dmp' (char)
define datapump_log='export.log' (char)
set linesize 1000
set echo off 
set term off
set feed off
set trims on
set head off
set feedback off
set pagesize 1000
set verify off


spool datapump.sql
select 'DIRECTORY=&directory_dump' from dual;
select 'DUMPFILE=&datapump_file' from dual;
select 'LOGFILE=&datapump_log' from dual;


select 'TABLES=&owner_schema'||'.'||SEGMENT_NAME||from (SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB,SUM(DS.BYTES) AS TOTAL_BYTES FROM DBA_SEGMENTS DS WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES where owner like '&owner_schema' AND NUM_ROWS>0) GROUP BY DS.TABLESPACE_NAME,SEGMENT_NAME) where MB < 1000 order by MB desc;
spool off