/**********************************************************************/ /* Document : Some DOS/Win9x/NT/200x/XP/Vista cmd batch and */ /* wsh examples */ /* Doc. Version : 15 */ /* File : cmdshell.txt */ /* Purpose : some hopefully usefull examples for the Oracle, */ /* SQL Server, and DB2 DBA */ /* for batch scripts */ /* Date : 31/08/2008 */ /* Compiled by : Albert van der Sel */ /**********************************************************************/ Remark: Bit of an old file. ############################################################################# ############################################################################# Part 1: Traditional old cmd/dos batch command examples DOS/Win9x/NT/200x/XP/Vista ############################################################################# ############################################################################# 1. Put day, month, year into variables: ======================================= @echo off for /f "tokens=2-4 delims=/ " %%a in ('date /t') do ( set mm=%%a set dd=%%b set yyyy=%%c) REM to show these variables echo %mm% echo %dd% echo %yyyy% Or put in a logfile: echo ============== >> c:\temp\report.log echo START RUNTIME: >> c:\temp\report.log echo ============== >> c:\temp\report.log date /T >> c:\temp\report.log time /T >> c:\temp\report.log 2. Some copy and xcopy command examples: ======================================== -- If you want to xcopy files from a certain date: xcopy *.* /D:01-13-2002 f:\backup xcopy *.* /D:%datum% f:\backup -- Some examples of copy commands using variables: copy %NTResKit%\perfmib.dll %systemroot%\system32\perfmib.dll copy %NTResKit%\perfmib.ini %systemroot%\system32\perfmib.ini If you want to use xcopy for backup purposes in Win2Kx / Vista / XP, please see Part 6. 3. The use of "FOR" example: ============================ Example: print all .txt files in 1 command ------------------------------------------ for %f in (*.doc *.txt) do type %f > prn in a batchfile, just use: %%f Example: register some dll's in 1 command ------------------------------------------ for %f in (*.dll) do regsrv32 %f Example: copy tekst into a file a number of times ------------------------------------------------- for /L %%f in (1,1,1000) do echo Albert >> c:\test\test.txt (1,1,1000) means (start,step,end) Example: -------- Or look at this example: FOR /L %variable IN (start,step,end) DO command [command-parameters] To see this in action, at a command prompt, type FOR /L %i in (1,1,5) do @echo %i and you should see: 1 2 3 4 5 Example: sort of unix cut functionality with the use of for: ------------------------------------------------------------ suppose you have the following file "myfile.txt": a,b,c d,e,f g,h,i FOR /F "tokens=2,3* delims=, " %i in (myfile.txt) do @echo %i %j >> myfile2.txt will create the following file "myfile2.txt": b,c e,f h,i Example: @ECHO OFF IF (%1)==() FOR %%v in (GOTO:END ECHO.(%%1):(%1)) do %%v ECHO Got a value :END ECHO The end 4. "If.. Then..Else" test and the use of Labels: ================================================ Example 1: ---------- @echo off setlocal if (%2)==() goto usage sqlplus %1/%2 @%ORACLE_HOME%\sqlplus\demo\demobld.sql goto exit :usage echo Usage: demobld userid passwd :exit endlocal Example 2: ---------- @echo off set test=q if %test%==%1 goto lab2 :lab1 echo not_equal goto end :lab2 echo equal goto end :end if exist c:\temp goto lab2 :lab1 echo bestaat niet goto end :lab2 echo bestaat wel goto end :end Example 3: ---------- Some loose statements: if '%1' == '' goto ERR0 if not exist %SYSTEMROOT%\SYSTEM32\SQRDB3.DLL goto ERR1 if errorlevel 1 set DRV=C: if errorlevel 2 set DRV=D: if %errorlevel% EQU 0 goto GO12 if %errorlevel% GTR 0 goto ERR6 find "not exist" c:\temp\report.log > nul if %errorlevel% EQU 0 goto ERRNAME if "%OS%" == "Windows_NT" goto NT_BIN if exist _runscr.log del _runscr.log > nul Example 4: ---------- if "%OS%" == "Windows_NT" goto NT_OS CALL other.bat EXIT :NT_OS CALL ntlogon.bat EXIT Example 5: ========== IF NOT EXIST TypeFinder\BUILDALL.BAT GOTO TYPEFINDEREND CD TypeFinder CALL BUILDALL.BAT %1 CD .. :TYPEFINDEREND IF NOT EXIST Wintalk\BUILDALL.BAT GOTO WINTALKEND CD Wintalk CALL BUILDALL.BAT %1 CD .. :WINTALKEND IF NOT EXIST WordCount\BUILDALL.BAT GOTO WORDCOUNTEND CD WordCount CALL BUILDALL.BAT %1 CD .. :WORDCOUNTEND Example 6: ========== @echo off csc /t:module CountDownSecondsLabel.cs /r:System.dll /r:System.Windows.Forms.dll /r:System.Drawing.dll rem if C++ is specified, create C++ DLL, otherwise create C# DLL if "C++"=="%1" goto CPP if "c++"=="%1" goto CPP if "%1"=="" goto CS goto ERROR :CS csc /t:module CountDownErrorLabel.cs /r:System.dll /r:System.Windows.Forms.dll /r:System.Drawing.dll goto Continue :CPP cl /clr /LD CountDownErrorLabel.cpp /link /OUT:CountDownErrorLabel.netmodule :Continue ilasm Counter.il /dll ilasm CountDownComponents.il /dll ilasm CountDown.il goto END :ERROR echo Invalid command line argument '%1' echo. :END 5. The use of "Choice": ======================= Choice is an external "cmd" or "DOS box" executable you for example can find in MS Resource kits of Win9x, NT, 2000. Use it as in the following example: echo Please enter the drive letter ( c/d/e/f/g ) choice /c:cdefg if errorlevel 1 set DRV=C: if errorlevel 2 set DRV=D: etc.. echo Is this correct (y/n) ? choice /c:yn /n > nul if errorlevel 2 goto 6. Pipelining examples: ======================= SET | FIND "windir" | IF errorlevel=1 ECHO Windows not running In order to see if Oracle services are running on this machine: net start | FIND "Ora" 7. Creating sub-routines in CMD files without creating new files: ================================================================= With NT/2000 CMD files it's possible to call sub-routines without creating a new CMD file. This gives you, the programmer/scripter, the possibility to keep your scripts in one file and maintain an overview of scripts in use. How does it work then? Well, for those who know the DOS BATCH files (.bat), will remember the LABELS and GOTO commands. Within NT, Microsoft made an addition to this functionality so that you can go to a label, and at the end of youre sub-routine, it will jump back to the point where you have called the label. Just look at the following example: @echo off ECHO Start of part 1 CALL :part2 ECHO End of part 1 goto end :part2 ECHO Start of part 2 ECHO (Some things you want to do) ECHO End of part 2 goto :EOF :end ECHO Finished script The EOF is a hidden label which jumps to the end of the "subroutine", and so returns to its previous caller. 8. Oracle backup scripts partial code: ====================================== Example 1: archivelog backups ----------------------------- @echo off for /f "tokens=2-4 delims=/ " %%a in ('date /t') do ( set mm=%%a set dd=%%b set yyyy=%%c) REM month/day/year mm/dd/yyyy echo %mm% echo %dd% echo %yyyy% set /A lastday=%dd%-1 echo %newday% set copydate=%mm%/%lastday%/%yyyy% echo %copydate% g: cd\archives xcopy *.* /D:%copydate% f:\backup Example 2: maintenance exportfiles ---------------------------------- move /Y d:\backups\pegacc\2dayago\*.Z d:\backups\pegacc\3dayago move /Y d:\backups\pegacc\1dayago\*.Z d:\backups\pegacc\2dayago move /Y d:\backups\pegacc\*.Z d:\backups\pegacc\1dayago move /Y d:\backups\pegtst\2dayago\*.Z d:\backups\pegtst\3dayago move /Y d:\backups\pegtst\1dayago\*.Z d:\backups\pegtst\2dayago move /Y d:\backups\pegtst\*.Z d:\backups\pegtst\1dayago 9. Append date and time to filename: ==================================== Q. How can I append the date and time to a file? A. You can use the batch file below which will rename a file to filename_YYYYMMDDHHMM. @Echo OFF TITLE DateName REM DateName.CMD REM takes a filename as %1 and renames as %1_YYMMDDHHMM REM REM ------------------------------------------------------------- IF %1.==. GoTo USAGE Set CURRDATE=%TEMP%\CURRDATE.TMP Set CURRTIME=%TEMP%\CURRTIME.TMP DATE /T > %CURRDATE% TIME /T > %CURRTIME% Set PARSEARG="eol=; tokens=1,2,3,4* delims=/, " For /F %PARSEARG% %%i in (%CURRDATE%) Do SET YYYYMMDD=%%l%%k%%j Set PARSEARG="eol=; tokens=1,2,3* delims=:, " For /F %PARSEARG% %%i in (%CURRTIME%) Do Set HHMM=%%i%%j%%k Echo RENAME %1 %1_%YYYYMMDD%%HHMM% RENAME %1 %1_%YYYYMMDD%%HHMM% GoTo END :USAGE Echo Usage: DateName filename Echo Renames filename to filename_YYYYMMDDHHMM GoTo END :END REM TITLE Command Prompt Example: D:\Exchange> datetype logfile.log RENAME logfile.log logfile.log_199809281630 10. Output of a program into an environment variable: ===================================================== Q. How can I force the output of a program into an environment variable? A. Some programs return values to the command line and it may be you want these in a variable so they can be viewed/queried by other processes. The easiest way to put the result into an environment variable is to trap it in a FOR statement. For /f "Tokens=*" %i in ('command') do set variable="%i" For example: C:\>For /f "Tokens=*" %i in ('ver') do set NTVersion="%i" C:\>set NTVersion="Windows NT Version 4.0 " C:\>echo %NTVersion% "Windows NT Version 4.0 " If you place the command in a batch file you require two % in front of i, e.g. For /f "Tokens=*" %%i in ('ver') do set NTVersion="%%i" Variables: ---------- @echo off set var=testing 1 2 3 echo The variable is "%var%" To unset or erase a previously set user variable, use the following command:e set var= @echo off for /f "tokens=2-4 delims=/ " %%a in ('date /t') do ( set mm=%%a set dd=%%b set yyyy=%%c) FOR /F "tokens=*" %%A IN ('systeminfo ^| FIND "Computermodel"') DO SET var1=%%A echo %var1% %A is for use on command lines only. In all examples and syntax lines shown %A should be substituted with %%A when used in batch files. tokens=2,4,6 will cause the second, fourth and sixth items on each line to be processed tokens=2-6 will cause the second, third, fourth, fifth and sixth items on each line to be processed tokens=* will cause all items on each line to be processed tokens=3* will cause the 3rd and all subsequent items on each line to be processed Each token specified will cause a corresponding parameter letter to be allocated. 11. Get m columns from n in a text file: ======================================== Use the unix port freeware program cut.exe. Suppose you have a file x.txt similar to a b c d e f g h i j k l etc.. Now you only want certain columns in a new file. type x.txt | cut 1 3 > y.txt y.txt: a b e f i j 12. SCHEDULING: =============== Example 1: ---------- How to use the "at" command, please see the help given by: C:\> at /? >>> Example of the use of the at command: at 23:00 /every:M,T,W,Th,F backup.cmd That commands schedules the backup.cmd script on your local Server, to be executed at 23:00h at Monday, Tuesday, Wednesday, Thursday and Friday. >>> Other example @echo off rem rem NAME rem setat.cmd - NT command script rem at %1 /every:M,T,W,Th,F,S,Su %COMSPEC% /c "r:\ifa\bin\ifa.cmd" See also Part 3, section 5 13. Delete all files without prompting: ======================================= >> Best solution on NT, 2Kx, XP: --------------------------------- Delete of files, silently, in subdirs, also readonly ones (This is like a "rm -rf" on UNIX) cd %1 del /F /Q /S *.* >> Alternatives on all WinOS: ----------------------------- One of the most Frequently Asked Questions (FAQs) about batches is how to suppress the "Are you sure (Y/N)?" confirmation requirement for del *.*. Use the following: echo y| del *.* If you wish to suppress the message too, use echo y| del *.* > nul There is also another alternative for doing this. It has the advantange of being MS-DOS language version independent. for %%f in (*.*) do del %%f If the directory is empty you can avoid the "File not found" message by applying if exist *.* echo y| del *.* > nul A better, obvious alternative by Rik D'haveloose: if exist *.* for %%f in (*.*) do del %%f 14. Is there an easy way to append a new directory to the path? =============================================================== This often needed trick is basically very simple. For example to add directory %1 to path use path=%path%;%1 Note that you can only use this trick in a batch. It will not work at the MS-DOS prompt because the environment variables are expanded (%path%) only within batches. It also is typical to need a fuller path only for the duration of executing some particular program, and to restore the original after that: @echo off set path_=%path% path=%path_%;f:\ftools :: call whatever :: path=%path_% set path_= 15. Start an installation, tool etc.. ===================================== Example 1: ---------- @echo off REM Oracle Migration Workbench startup script for Windows NT set PATH=E:\Program Files\Oracle\jre\1.1.7\bin\;E:\oracle\ora81\bin;E:\oracle\ora81\Omwb\olite;%PATH% SET JRE=jrew -nojit -mx128m SET NT_START=start REM Starting Oracle Migration Workbench on Windows NT %NT_START% %JRE% -classpath "E:\oracle\ora81\Omwb\olite\Oljdk11.jar;E:\oracle\ora81\Omwb\olite\Olite40.jar;E:\Program Files\Oracle\jre\1.1.7\lib\rt.jar;E:\Program Files\Oracle\jre\1.1.7\lib\i18n.jar;E:\oracle\ora81\Omwb\jlib;E:\oracle\ora81\Omwb\plugins\SQLServer6.jar;E:\oracle\ora81\Omwb\plugins\Sybase.jar;E:\oracle\ora81\Omwb\plugins\MSAccess.jar;E:\oracle\ora81\Omwb\plugins\SQLAnywhere.jar;E:\oracle\ora81\Omwb\plugins\SQLServer7.jar;E:\oracle\ora81\Omwb\jlib\omwb-1_3_0_0_0.jar;E:\oracle\ora81\jdbc\lib\classes111.zip;E:\oracle\ora81\lib\vbjorb.jar;E:\oracle\ora81\jlib\ewt-swingaccess-1_1_1.jar;E:\oracle\ora81\jlib\ewt-3_3_6.jar;E:\oracle\ora81\jlib\ewtcompat-opt-3_3_6.zip;E:\oracle\ora81\jlib\share-1_0_8.jar;E:\oracle\ora81\jlib\help-3_1_8.jar;E:\oracle\ora81\jlib\ice-4_06_6.jar;E:\oracle\ora81\jlib\kodiak-1_1_3.jar" -DORACLE_HOME=E:\oracle\ora81 oracle.mtg.migrationUI.MigrationApp oracle.mtg.migrationUI.MigrationApp Example 2: ---------- set OSQLPATH="c:\Program Files\Microsoft SQL Server\80\Tools\Binn set DBNAME=%2 %OSQLPATH%\osql.exe" -n -S%1 -d %DBNAME% -E -i%3.sql >> _runscr.log 16. Get rid of Carriage return ^M in files: =========================================== How do I eliminate carriage returns (^M) in my files? In unix its simple: ------------------- If you transfer text files from a DOS machine to a UNIX machine, you might see a ^M before the end of each line. This character corresponds to a carriage return. In DOS a newline is represented by the character sequence \r\n, where \r is the carriage return and \n is newline. In UNIX a newline is represented by \n. When text files created on a DOS system are viewed on UNIX, the \r is displayed as ^M. You can strip these carriage returns out by using the tr command as follows: tr -d '\r' < file > newfile or on some unixes: tr -d '\015' < file > newfile Here file is the name of the file that contains the carriage returns, and newfile is the name you want to give the file after the carriage returns have been deleted. Here you are using the octal representation \015 for carriage return, because the escape sequence \r will not be correctly interpreted by all versions of tr. Or you can use sed in the following way: move from unix to dos: $ sed -e 's/$/\r/' myunix.txt > mydos.txt move from dos to unix: $ sed -e 's/.$//' mydos.txt > myunix.txt So, install a unix shell on your PC, like Cygwin But now in dos/nt/2000/xp: -------------------------- (1) get for example Gygwin or other 'unix' emulator engine for nt/2000/xp where you can run tr and sed like commands. (2) with nt/2000/xp tools only: 17: start a file minimised window: ================================== Example: start /min notepad c:\core\cmdshell.txt 18: COMM ports in DOS (dos, NT, 2000, 2003, XP): ================================================ Examples to test a port: 1. echo AT&F>com1 2. C:\>mode com3 Status for device COM3: ----------------------- Baud: 115200 Parity: None Data Bits: 8 Stop Bits: 1 Timeout: OFF XON/XOFF: OFF CTS handshaking: OFF DSR handshaking: OFF DSR sensitivity: OFF DTR circuit: ON RTS circuit: OFF Examples to assign a port: voorbeelden Als u COM12 wilt toewijzen aan COM1, zodat deze kan worden gebruikt door een MS-DOS-toepassing, typt u: change port com12=com1 Met de volgende opdracht geeft u de huidige poorttoewijzingen weer: change port /query 19. Special File and Volume commands in XP: =========================================== fsutil: ------- Fsutil is a command-line utility that you can use to perform many FAT and NTFS file system related tasks, such as managing reparse points, managing sparse files, dismounting a volume, or extending a volume. Because fsutil is quite powerful, it should only be used by advanced users who have a thorough knowledge of Windows XP. In addition, you must be logged on as an administrator or a member of the Administrators group in order to use fsutil. Fsutil: dirty Queries --------------------- Use this to see whether a volume's dirty bit is set, or use it to sets a volume's dirty bit. When a volume's dirty bit is set, autochk automatically checks the volume for errors the next time the computer is restarted. Syntax fsutil dirty {query|set} PathName Parameters -query Queries the dirty bit. -set Sets a volume's dirty bit. -PathName Specifies the drive letter (followed by a colon), mount point, or volume name. Examples - To query the dirty bit on drive C, type: fsutil dirty query C: Sample output: Volume C: is dirty or Volume C: is not dirty - To set the dirty bit on drive C, type: fsutil dirty set C: Fsutil: volume -------------- Us this to manage a volume. Dismounts a volume or queries to see how much free space is available on a disk. Syntax fsutil volume [diskfree] drivename fsutil volume [dismount] VolumePathname Parameters -diskfree Queries the free space of a volume. -drivename Specifies the drive letter (followed by a colon). -dismount Dismounts a volume. -VolumePathname Specifies the drive letter (followed by a colon), mount point, or volume name. Examples - To dismount a volume on drive C, type: fsutil volume dismount C: - To query the free space of a volume on drive C, type: fsutil volume diskfree C: 20. Start a program like a DB sql prompt util and run a script: =============================================================== example.cmd ----------- cls echo off c:\oracle\ora92\bin\sqlplus /nolog @c:\logging\example.sql > z:\its\oc\databases\oracle_logging\example.log So the .cmd file calls a program sqlplus which will run a .sql script, while the output will be placed in a designated logfile. example.sql ----------- The .sql file might contain something like the following: connect system/arcturus81@ECM_172.17.203.162 REM Logon to DB alter system checkpoint REM true DB commands / SELECT * FROM v$sgastat WHERE name = 'free memory' / alter system flush shared_pool / SELECT * FROM v$sgastat WHERE name = 'free memory' / 21. Remote terminal Services: ============================= If your XP, or Server has the terminal services client, or RDP, installed, you can run it via C:\>mstsc A dialog box will show, where you can enter the name or IP of the target system. C:\>mstsc /? Will show all switches you can use. 22. Run a script, or program with elevated credentials: ======================================================= In XP, Vista, Win2Kx you can, as an ordinary user, run a script, or program, with elevated credentials, that is, using another account, using the "runas" utility. - From the prompt, use runas: Syntax RUNAS [/profile] [/env] [/netonly] /user:user Program Key /profile Option to load the user's profile (registry) /env Use current environment instead of user's. /netonly Use if the credentials specified are for RAS only. /user Username in form USER@DOMAIN or DOMAIN\USER (USER@DOMAIN is not compatible with /netonly) Program The command to execute Examples: runas /profile /user:mymachine\administrator CMD runas /profile /env /user:SCOT_DOMAIN\administrator NOTEPAD runas /env /user:jDoe@swest.ss64.com "NOTEPAD \"my file.txt\"" Enter the password when prompted. - From the Windows explorer GUI Select an executable file, Right-click and select Run As.. This option can be hidden by setting HKLM\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer Examples: C:\> runas /user:Administrator@afa.com "mycommand.exe" Where you run "mycommand.exe" as the Adminstrator from the Domain "afa". So runas works quite like the unix sudo tool. But the tool will ask for the password of the user listed in the command. If you need encryption of command files, and many other options, checkout the great "runasspc" tool. Just google on runasspc to find more on this usefull tool. 23. Show running services: ========================== For the NT like OS'ses and later, like XP, Win2Kx etc.. you can view all running daemons / services with: C:\> net start Shows all running services on your machine C:\> net start | find "Part of Service name" Shows all services with a name like "Part of Service name" To show all services related to Oracle: C:\> net start | find "Ora" Or use this to show services: C:> cmd /C SC Query>C:\temp\services.txt Lists all your running services to the file services.txt 24. Some systemtools for Windows: ================================= We are not going to differentiate between all possible Windows versions here (like XP,Vista, Win2K3 etc..) but there might be a few additional tools that can be of interest. Ofcourse, everybody knows regedit or regedt32, for viewing or editing the Registry. And, likewise, everybody knows that the Resource Kits deliver you many additional tools for you platform. Besides all that, most Windows versions also have: -- sysedit.exe: It shows you win.ini, system.ini, config.sys and autoexec.bat. The configfiles win.ini and system.ini might still be important for older win applications. -- systeminfo.exe: Its shows you many hardware and system related information. It might also present you a nice list of all the patches and hotfixes that were applied on your system. 25. Find "open" files in Windows; ================================= For the newer Windows versions like XP, Win2Kx you can view all open (locked) files with: C:> net file 26. SC command: =============== F:\core>cmd /C SC DESCRIPTION: SC is a command line program used for communicating with the NT Service Controller and services. USAGE: sc [command] [service name] ... The option has the form "\\ServerName" Further help on commands can be obtained by typing: "sc [command]" Commands: query-----------Queries the status for a service, or enumerates the status for types of services. queryex---------Queries the extended status for a service, or enumerates the status for types of services. start-----------Starts a service. pause-----------Sends a PAUSE control request to a service. interrogate-----Sends an INTERROGATE control request to a service. continue--------Sends a CONTINUE control request to a service. stop------------Sends a STOP request to a service. config----------Changes the configuration of a service (persistant). description-----Changes the description of a service. failure---------Changes the actions taken by a service upon failure. sidtype---------Changes the service SID type of a service. qc--------------Queries the configuration information for a service. qdescription----Queries the description for a service. qfailure--------Queries the actions taken by a service upon failure. qsidtype--------Queries the service SID type of a service. delete----------Deletes a service (from the registry). create----------Creates a service. (adds it to the registry). control---------Sends a control to a service. sdshow----------Displays a service's security descriptor. sdset-----------Sets a service's security descriptor. showsid---------Displays the service SID string corresponding to an ar bitrary name. GetDisplayName--Gets the DisplayName for a service. GetKeyName------Gets the ServiceKeyName for a service. EnumDepend------Enumerates Service Dependencies. The following commands don't require a service name: sc