SET TERMOUT ON
SET ECHO OFF
SET HEADING OFF
SET VERIFY OFF
set linesize 1000
set pagesize 50
 


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

connect &SYSUSER/&SYSPsw@&Server as sysdba

spool impdp_user_ALL.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 '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 'impdp.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') ||'_IMPDP.log ' || 'SCHEMAS=' ||
       t.USERNAME
  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;


spool off



SET TERMOUT ON
SET ECHO ON
EXIT
