Hi, My name is Jimmy Sam. If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.

test-netconnection or tnc

test-netconnection myhost_name -port 9999
tnc                myhost_name -port 9999

Powershell Version

$PSVersionTable
$PSVersionTable.PSVersion
Get-ChildItem Env:
$env:UserName
$env:UserDomain
$env:ComputerName
$env:PSModulePath
$env:Processor_Architecture


# vb
# Environment.GetEnvironmentVariables(tgt)
# Environment.SetEnvironmentVariable(myVarA, Nothing)
# Environment.SetEnvironmentVariable(myVarB, Nothing, EnvironmentVariableTarget.Process)
# Environment.SetEnvironmentVariable(myVarC, Nothing, EnvironmentVariableTarget.User)
# Environment.SetEnvironmentVariable(myVarD, Nothing, EnvironmentVariableTarget.Machine)

# [Environment]::SetEnvironmentVariable("TNS_ADMIN", "C:\oracle\product\10.2.0\client_1\network\ADMIN", "User")
# [Environment]::SetEnvironmentVariable("ORACLE_HOME", "C:\oracle\product\10.2.0\client_1", "User")

[Environment]::SetEnvironmentVariable("TNS_ADMIN", "C:\oracle\product\10.2.0\client_1\network\ADMIN")
[Environment]::SetEnvironmentVariable("ORACLE_HOME", "C:\oracle\product\10.2.0\client_1")

$mypath123498=[environment]::GetEnvironmentVariable("PATH","User")
[Environment]::SetEnvironmentVariable("PATH", "C:\oracle\product\10.2.0\client_1\bin;$mypath123498")

[Environment]::SetEnvironmentVariable("TNS_ADMIN",$null,"User")

[Environment]::GetEnvironmentVariables()
[Environment]::GetEnvironmentVariable("PATH","User")
[environment]::GetEnvironmentVariable("PATH","User")

# get all environment variables
Get-ChildItem Env:
Get-ChildItem Env:PATH
$env:os
$env:path

$mypath12983=$env:path
$env:path="C:\oracle\product\10.2.0\client_1;$mypath12983"

Powershell Comments

#
block comments: <#  #>

Alias, Help, And Command

get-help get-childitem --> alias gci ls dir

Get-Help <cmdlet name> 
Get-Help <cmdlet name> -Detailed
Get-Help <cmdlet name> -Examples
Get-Help <cmdlet name> -Full

Get-Command
Get-Command -Verb Remove
Get-Commend -Noun Service
Get-Command -ListImported

Get-Help Get-Command -full

Get-Alias gl
Get-Alias -definition Get-Location
new-alias ll get-childitem

$d = $a.ToLower()
$d = $a.ToUpper()
$e = $e.Substring(3)
string character escape `$

# parameterize a string output
$mytest=&{"I", "book", "SunDay" }
$mystring="{0} read {1} On {2}" -f $mytest
$mystring
-->I read book On SunDay

Modules

get-module -ListAvailable
get-command -module mymodulename
Import-Module c:\temp\my.psm1
Import-Module c:\temp\my.psm1 -Force
# Remove-Module my
$env:PSModulePath

TO HTML Format

convertto-html
eg: get-process|convertto-html

Execution Policy

get-help executionpolicy
Get-ExecutionPolicy
Set-ExecutionPolicy RemoteSigned
Set-ExecutionPolicy Restricted

execution policy 

Get-ExecutionPolicy

Get-ExecutionPolicy -List

Get-ExecutionPolicy -Scope CurrentUser

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

Set-ExecutionPolicy  -ExecutionPolicy RemoteSigned -Scope CurrentUser

REM in DOS or Windows Command Promopt
powershell.exe -noprofile -executionpolicy RemoteSigned -file c:\my_script01.ps1

Profile

help about_profiles
$profile
psedit $profile
test-path $profile
notepad $profile

Powershell Programming

projection and selection
REM DOS
winver

#Powershell
Get-WmiObject Aliases 
#gwmi

Get-WmiObject Win32_OperatingSystem
Get-WmiObject Win32_OperatingSystem|Select-Object *

Get-WmiObject win32_bios
Get-WmiObject win32_bios -computername 
Get-WmiObject win32_bios | get-member
Get-WmiObject win32_bios | Select-Object *
Get-WmiObject win32_bios | Select-Object -excludeproperty "_*"

Find Files: Powershell

find drive script:
Get-ChildItem -Path . -Filter drive*.sql -Recurse

powershell -Command "dir p:/2014*/*/*/*%1*"

Get current folder size: Powershell

Get-ChildItem . -recurse| Measure-Object -property length -sum 
Get-ChildItem . -recurse| Measure-Object -property length -sum| Select-Object sum|format-list
Get-ChildItem . -recurse| Measure-Object -property length -sum| Select-Object sum|format-wide

Get-ChildItem . -recurse| Measure-Object -property length -sum|select @{LABEL='sum(mb)';EXPRESSION={"{0:N2}" -f ($_.sum/1MB)}}|format-list

# -- -----------------------------------------------------------------------
# sub folders size
$mySubDir = (Get-ChildItem | Where-Object {$_.PSIsContainer -eq $True} | Sort-Object)
foreach ($i in $mySubDir)
{
$v = (Get-ChildItem $i -recurse| Measure-Object -property length -sum)
$i.FullName + "     " + "{0:N2}" -f ($v.sum / 1MB) + " MB"
} 

disk space: Powershell


# current disk drive space info
gwmi win32_volume | select driveletter, label, @{LABEL='GBfreespace';EXPRESSION={"{0:N2}" -f ($_.freespace/1GB)}}|sort-object -property driveletter

gwmi win32_volume -Filter 'drivetype = 3' | select driveletter, label, @{LABEL='GBfreespace';EXPRESSION={"{0:N2}" -f ($_.freespace/1GB)} }|sort-object -property driveletter

Test Email: Powershell

send-mailmessage -to "myemailname@abc.com" -from "oracle user <oracle@abc.com>" -subject "Test mail"  -smtpServer 'forwarder.subdomain.abc.com'

Last Boot Time: Powershell

Get-CimInstance -ClassName win32_operatingsystem | select csname, lastbootuptime
top

Users And Groups

rem Find A user's Windows Groups
rem In DOS
net user myusername /domain

# In Powershell
Get-ADUser myusername -Properties MemberOf | Select -ExpandProperty memberof|sort
Get-ADPrincipalGroupMembership myusername | select name
Get-ADPrincipalGroupMembership myusername | select name | sort name

rem List All Of the Users In A Windows Group
rem In DOS
net user localgroup Administrators

# powershell

$GroupName="Unix_DBA_GRP"
Get-ADGroupMember -Identity $GroupName -Recursive | `
Where {$_.ObjectClass -eq 'user'} | `
Get-ADUser -Properties Title,Department | `
Select SamAccountName,Name,Title,Department 

top

service and process, property, select, where

get-process
get-procee|ft
get-procee|fl
get-procee|Format-List
Get-Process|Get-Member
get-process|gm

get-Process oracle,tnslsnr
get-Process ora*,tns*

get-Process oracle|gm
Get-Process ora*,tns*| Select-Object name,fileversion,productversion,company

Get-Process | Where-Object {$_.handles -ge 200}
Get-Process | Where-Object {$_.handles -gt 200 -and $_.name -eq "svchost"}

-- (Get-Process MyProcess).StartTime.ToString('yyyyMMdd')
get-Process|findstr /i oracle
get-Process oracle
get-Process oracle|gm
(Get-Process oracle).StartTime.ToString('yyyyMMdd')
(Get-Process oracle).StartTime.ToString('yyyyMMdd HHmmss')

get-Process tnslsnr
(Get-Process oracle.exe).StartTime.ToString('yyyyMMdd')

get-process|where-object { $_.processname -eq "TNSLSNR" }|select -property *

get-process|where-object {$_.ProcessName -eq 'TNSLSNR'}
get-process|where-object { $_.processname -eq "TNSLSNR" } # big bracket { not small (
get-process|select-object -property name
get-process|select-object -property *
get-process|select-object -property name, Extendedproperties
-- ----------
dir|gm
DIR | select-object -property name, Extendedproperties

dir|select-object -property name
dir|select-object -property name,Extendedproperties

-- ---------------------------------
get-service
get-service|fl
get-service|gm

#Name                : OracleOraDb11g_home1TNSListener
#DisplayName         : OracleOraDb11g_home1TNSListener
#Status              : Running
#DependentServices   : {}
#ServicesDependedOn  : {}
#CanPauseAndContinue : False
#CanShutdown         : False
#CanStop             : True
#ServiceType         : Win32OwnProcess

#Name                : OracleServiceFIDELIO
#DisplayName         : OracleServiceFIDELIO
#Status              : Running
#DependentServices   : {}
#ServicesDependedOn  : {}
#CanPauseAndContinue : True
#CanShutdown         : True
#CanStop             : True
#ServiceType         : Win32OwnProcess

Get-WmiObject -Class Win32_Service
Get-WmiObject -Class Win32_Service -Property name|findstr /i /B Name|findstr /i oracle
Get-WmiObject -Class Win32_Service -Property name|findstr /i /B Name

get-wmiobject -Class Win32_Process|findstr /i oracle
get-wmiobject -Class Win32_Process|findstr /i name
get-wmiobject -Class Win32_Process|findstr /i processname

get-wmiobject -Class Win32_Process -Filter "Name='oracle.exe'" -ea 0
get-wmiobject -Class Win32_Process -Filter "Name='oracle.exe'" -ea 0 -Property CreationDate

top

call operator &: sqlplus

call operator &

&tnsping sead

& 'path\sqlplus.exe' 'system/password@dbase as sysdba'
& 'path\sqlplus.exe' 'system/password@dbase as sysdba' '@my_script.sql'

--
$cmd = "cmd.exe"
$args = ("/c sqlplus {0}/{1}@{2}:{3}/{4} @{5} {6}" -f $userName, $password, $tnsAlias, $port, $dbInstance, $sqlScript, $outputFileName)
&$cmd $args 
-- 

Reference

msdn Powershell