mysql: rownum functionality

Here are some examples how to use “rownum” functionality (similar to Oracle) in mysql


update mytable
set col1 = 'somevalue'
order by col2
limit 300

rownum analog:


select @rownum:=@rownum+1 rownum, mytable.*
from (select @rownum:=0) r, mytable;

WMI: list of the methods and properties


strComputer = "."
Set objWMIService=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

For Each objclass in objWMIService.SubclassesOf()
intCounter=0
If Left(objClass.Path_.Class,5) = "Win32" Then
For Each Qualifier in objClass.Qualifiers_
If UCase(Trim(Qualifier.Name)) = "ASSOCIATION" Then
intCounter = 1
End If
Next
If x = 0 Then
strComputer = "."
Set objWMIService = GetObject _
("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set strClass = objWMIService.Get(objClass.Path_.Class)
Wscript.Echo "PROPERTIES:"
For each strItem in strClass.properties_
Wscript.Echo objClass.Path_.Class & vbTab & strItem.name
Next
Wscript.Echo "METHODS:"
For Each strItem in strClass.methods_
Wscript.Echo objClass.Path_.Class & vbTab & strItem.name
Next
End If
End If
Next

Other scripts could be found here

Oracle: flashback usage


-- To save the flashback information for the last 30 minutes:
ALTER SYSTEM SET UNDO_RETENTION = 1800;

--
SELECT * FROM some_table AS OF TIMESTAMP ('2008-10-08 06:31:58', 'YYYY-MM-DD HH24:MI:SSS');

SELECT * FROM some_table AS OF SCN 12345678;

-- dbms_flashback could be also used
EXECUTE dbms_flashback.enable_at_time ('18-JAN-08 11:00:00');

WMI: get the information about the system


Option Explicit
On Error Resume Next

Dim strComputer, objWMIService
Dim colItems, objItem

strComputer = "."

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
strComputer & "\root\cimv2")

Set colItems = objWMIService.ExecQuery ("Select * from Win32_OperatingSystem")

For Each objItem in colItems
WScript.Echo "Machine Name: " & objItem.CSName & VbCr & _
"===================================" & vbCr & _
"Description: " & objItem.Description & VbCr & _
"Manufacturer: " & objItem.Manufacturer & VbCr & _
"Operating System: " & objItem.Caption & VbCr & _
"Version: " & objItem.Version & VbCr & _
"Service Pack: " & objItem.CSDVersion & VbCr & _
"CodeSet: " & objItem.CodeSet & VbCr & _
"CountryCode: " & objItem.CountryCode & VbCr & _
"OS Language: " & objItem.OSLanguage & VbCr & _
"CurrentTimeZone: " & objItem.CurrentTimeZone & VbCr & _
"Locale: " & objItem.Locale & VbCr & _
"SerialNumber: " & objItem.SerialNumber & VbCr & _
"SystemDrive: " & objItem.SystemDrive & VbCr & _
"WindowsDirectory: " & objItem.WindowsDirectory & VbCr & _
""
Next

Oracle: file needs recovery (offline mode)


select d.file# f#, d.name, d.status, h.status
from v$datafile d, v$datafile_header h
where d.file# = h.file#
and (d.status not in ('SYSTEM','ONLINE') or h.status != 'ONLINE' );

If there are such files, the recovery is necessary:

  1. restore the file from the backup
  2. recover datafile ‘&the_file_name’ ;
  3. alter database datafile ‘&the_file_name’ online;

Another possibility (if there are a lot of files):

  1. restore the files from the backup
  2. recover tablespace ‘&tbs_name’ ;
  3. alter tablespace ‘&tbs_name’ online;