SET TERMOUT ON
SET ECHO OFF
SET HEADING OFF
set LINESIZE 1000
set pagesize 0



ACCEPT Server PROMPT 'Enter a server alias/connection string (DEFAULT - MAGICASH): ' DEFAULT MAGICASH 
ACCEPT pump_directory PROMPT 'Enter Datapump_directory (DEFAULT - E:\pump_directory): ' DEFAULT E:\pump_directory
ACCEPT SYSPsw PROMPT 'Enter SYS PASSWORD: ' DEFAULT NCTSYS
DEFINE SYSUSER = SYS;

connect &SYSUSER/&SYSPsw@&Server as sysdba

spool ExpDPump_scheme.bat

Prompt SET NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251

Prompt 
Prompt 
Prompt 
Prompt 

prompt Set v_sid=&Server
prompt Set v_username=&SYSUSER
prompt Set v_pass=&SYSPsw
prompt Set dp_direct=&pump_directory

Prompt 
Prompt 
Prompt 

create or replace directory pump as '&pump_directory';
GRANT READ, WRITE ON DIRECTORY PUMP TO sys;

SELECT  'md '||'&pump_directory'  FROM dual;
SELECT 'cacls '||'"&pump_directory"'||' /t /e /c /p %username%:f' FROM dual;
SELECT 'cacls '||'"&pump_directory"'||' /t /e /c /p System:f' FROM dual;
SELECT 'cacls '||'"&pump_directory"'||' /t /e /c /p Βρε:f' FROM dual; 


SELECT 'expdp.exe userid=' || '''%v_username%/%v_pass%@%v_sid% as sysdba''' ||
       ' DIRECTORY=pump' || ' DUMPFILE=' || t.USERNAME||'_' || to_char(sysdate, 'dd_mm_yyyy') ||'_EXPDP.dmp ' ||
       ' LOGFILE=' || t.USERNAME ||'_' || to_char(sysdate, 'dd_mm_yyyy') || '_EXPDP.log ' || 'SCHEMAS=' ||
       t.USERNAME || ' STATUS=5'
  FROM DBA_USERS t,
       (SELECT owner,
               TO_CHAR(ROUND(SUM(BYTES) / 1024 / 1024, 1)) || ' Mb' sch_size
          FROM dba_segments t1
         GROUP BY owner) objsize
 WHERE objsize.owner = t.username
   and ACCOUNT_STATUS = 'OPEN'
   and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX')
 order by t.USERNAME asc;


prompt del ExpDPump_scheme.bat 



SET TERMOUT ON
SET ECHO ON
EXIT  
 
