rem @echo off

for /F "tokens=2-4 delims=/ " %%i in ('date /t') do  set currentdate=%%k-%%i-%%j
for /F "tokens=1-3 delims=: " %%i in ('time /t') do set currenttime=%%i-%%j-%%k

set SET_PROMPT=y
set SET_SEMANTICS=n
set SET_RESTART=n
set _SCRIPT_DRIVE=%~d0
set APP_ROOT=%_SCRIPT_DRIVE%\ICOOP_ALL\ICORE

SET NLS_LANG=AMERICAN_AMERICA.TH8TISASCII
rem SET SET NLS_LANG=THAI_THAILAND.TH8TISASCII
rem SET NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

SET ORACLE_HOME=C:\app\Administrator\product\11.2.0\dbhome_1
SET ORA_ADM_USR=sys
SET ORA_ADM_PWD=admin
SET ORA_ADM_EXP_USR=system
SET ORA_ADM_EXP_PWD=admin
SET TEMP_DIR=%WINDIR%\TEMP

SET ORA_TARGET_DB_HOST=192.168.99.11
SET ORA_TARGET_DB_PORT=1521
SET ORA_TARGET_DB_SID=icoop
SET ORA_TARGET_DBSTR=%ORA_TARGET_DB_HOST%:%ORA_TARGET_DB_PORT%/%ORA_TARGET_DB_SID%
SET ORA_TARGET_USR=ISCOICOOPNMN
SET ORA_TARGET_PWD=iscoicoopnmn

SET ORA_VERSION=11.2

SET DUMP_FILE=%ORA_TARGET_USR%
SET DATAPUMP=DATAPUMP
SET DATAPUMP_DIR=C
SET DATAPUMP_PATH=%DATAPUMP_DIR%:\%DATAPUMP%
SET DATAPUMP_ZIP_FILE=ISCOICOOPNMN.127.0.0.1icoop.2016-03-01_11-37-PM
SET DATAPUMP_ZIP_PATH=%APP_ROOT%\ICOOP\DB
SET ZIPAPP=%APP_ROOT%\ICOOP\DB\7-ZipPortable\App\7-Zip64
SET PATH=%ORACLE_HOME%\bin;%PATH%

SET ORA_OS_USR=Administrator
SET ORA_OS_PWD=Admin123
SET ORA_OS_DATAPUMP_PATH=\\%ORA_TARGET_DB_HOST%\%DATAPUMP_DIR%$\%DATAPUMP%

:STEP0
net use "\\%ORA_TARGET_DB_HOST%\%DATAPUMP_DIR%$" %ORA_OS_PWD% /user:%ORA_OS_USR%
mkdir \\%ORA_TARGET_DB_HOST%\%DATAPUMP_DIR%$\%DATAPUMP%
echo y|CACLS \\%ORA_TARGET_DB_HOST%\%DATAPUMP_DIR%$\%DATAPUMP% /T /P Everyone:F
net use "%ORA_OS_DATAPUMP_PATH%" %ORA_OS_PWD% /user:%ORA_OS_USR%
rem mkdir %DATAPUMP_PATH%

echo quit|echo alter system set nls_length_semantics='CHAR' scope=both;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo CREATE OR REPLACE DIRECTORY %DATAPUMP% AS '%DATAPUMP_PATH%';|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo alter session set "_ORACLE_SCRIPT"=true;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo alter system set deferred_segment_creation = FALSE ;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant select on dba_jobs to sys;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant execute  on dbms_job to sys;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant select on dba_jobs to system;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant execute  on dbms_job to system;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA

IF "%SET_SEMANTICS%"=="n" GOTO :STEP2

:STEP1
echo quit|echo @Alter_Fix_Oracle_nls_length_semantics.sql;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA

:STEP2
IF "%SET_RESTART%"=="n" GOTO :STEP4

:STEP3
cd %ORACLE_HOME%\bin
echo quit|echo shutdown immediate|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD% AS SYSDBA
echo quit|echo startup|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD% AS SYSDBA

:STEP4

cd %TEMP_DIR%
del  /F /Q *.dmp
del  /F /Q *.log
%ZIPAPP%\7z e %DATAPUMP_ZIP_PATH%\%DATAPUMP_ZIP_FILE%.zip 
copy *.dmp %DUMP_FILE%.DMP
rem echo y|echo F|xcopy %DATAPUMP_ZIP_PATH%\%DUMP_FILE%.DMP %TEMP_DIR%\  /f /j /s /w /z /r /y
echo y|echo F|xcopy %TEMP_DIR%\%DUMP_FILE%.DMP %ORA_OS_DATAPUMP_PATH%\  /f /j /s /w /z /r /y
rem copy %DATAPUMP_ZIP_FILE%.zip %DATAPUMP_ZIP_PATH%\%DATAPUMP_ZIP_FILE%.zip

rem echo quit|echo SELECT  'SYS.DBMS_IJOB.BROKEN('||job||',TRUE); END;' FROM DBA_JOBS_RUNNING;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
rem echo quit|echo SELECT  'BEGIN dbms_ijob.remove('||job||'); END;' FROM DBA_JOBS_RUNNING;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA

:STEP5
echo quit|echo DROP USER "%ORA_TARGET_USR%" CASCADE;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo CREATE USER "%ORA_TARGET_USR%" PROFILE "DEFAULT" IDENTIFIED BY "%ORA_TARGET_PWD%" ACCOUNT UNLOCK ;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo GRANT UNLIMITED TABLESPACE TO "%ORA_TARGET_USR%" WITH ADMIN OPTION;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo GRANT "AQ_ADMINISTRATOR_ROLE" TO "%ORA_TARGET_USR%" WITH ADMIN OPTION;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo GRANT "CONNECT" TO "%ORA_TARGET_USR%" WITH ADMIN OPTION;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo GRANT "DBA" TO "%ORA_TARGET_USR%" WITH ADMIN OPTION;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant IMP_FULL_DATABASE to %ORA_TARGET_USR%;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo alter profile "DEFAULT" limit password_life_time UNLIMITED;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA

rem impdp %ORA_TARGET_USR%/%ORA_TARGET_PWD%@%ORA_TARGET_DBSTR% DIRECTORY=%DATAPUMP% remap_schema=%ORA_TARGET_USR%:%ORA_TARGET_USR% DUMPFILE=%DUMP_FILE%_METADATA_ONLY.DMP log=%DUMP_FILE%_METADATA_ONLY.DMP.log CONTENT=METADATA_ONLY SQLFILE=%DUMP_FILE%_METADATA.sql

rem 
rem ========== Start =============
rem sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
rem begin
 rem for i in (select ('ALTER TABLE '|| C.owner || '.' || C.table_name|| ' modify (' || C.column_name || ' '|| C.data_type || '(' || C.char_length|| ' CHAR))') as Q from all_tab_columns C, all_tables T where T.owner in ('%ORA_TARGET_USR%') and C.owner = T.owner and C.table_name = T.table_name and C.char_used = 'B' and C.table_name not in (select table_name from all_external_tables) and C.data_type in ('VARCHAR2', 'CHAR') ) LOOP
   rem BEGIN
	rem execute immediate i."Q"||' ';
   rem EXCEPTION 
        rem WHEN OTHERS 
        rem THEN dbms_output.put_line(SQLCODE||i."Q"||' ');
   rem END;	
 rem end loop;
rem end;
rem /
rem quit
rem ========== End =============

rem impdp %ORA_ADM_EXP_USR%/%ORA_ADM_EXP_PWD%@%ORA_TARGET_DBSTR% DIRECTORY=%DATAPUMP% remap_schema=%ORA_TARGET_USR%:%ORA_TARGET_USR% DUMPFILE=%DUMP_FILE%.DMP LOGFILE=%DUMP_FILE%_DATA_ONLY.DMP.log CONTENT=DATA_ONLY
rem impdp %ORA_ADM_EXP_USR%/%ORA_ADM_EXP_PWD%@%ORA_TARGET_DBSTR% DIRECTORY=%DATAPUMP% remap_schema=%ORA_TARGET_USR%:%ORA_TARGET_USR% DUMPFILE=%DUMP_FILE%.DMP LOGFILE=%DUMP_FILE%_IMPDP.log full=y 
impdp %ORA_ADM_EXP_USR%/%ORA_ADM_EXP_PWD%@%ORA_TARGET_DBSTR% DIRECTORY=%DATAPUMP% remap_schema=%ORA_TARGET_USR%:%ORA_TARGET_USR% DUMPFILE=%DUMP_FILE%.DMP LOGFILE=%DUMP_FILE%_IMPDP.log  VERSION=%ORA_VERSION% 

echo y|echo F|xcopy %ORA_OS_DATAPUMP_PATH%\%DUMP_FILE%_IMPDP.log %TEMP_DIR%\ /f /j /s /w /z /r /y
echo y|echo F|xcopy %ORA_OS_DATAPUMP_PATH%\%DUMP_FILE%_IMPDP.log %DATAPUMP_ZIP_PATH%\ /f /j /s /w /z /r /y
copy %DATAPUMP_ZIP_PATH%\%DUMP_FILE%_IMPDP.log %DATAPUMP_ZIP_PATH%\%DUMP_FILE%.log 

IF "%SET_PROMPT%"=="n" GOTO :STEP7
:STEP6
SET /P E=Pleasec enter to exit 

:STEP7