web analytics

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;

mySQL: size of the database

SELECT table_schema "Database",  SUM( data_length + index_length ) / 1024 / 1024 "Size (MB)",  SUM( data_free )/ 1024 / 1024 "Free (MB)" FROM information_schema.tables GROUP BY table_schema ;

This will work in mySQL 5.0.2 and newer. Use SHOW TABLE STATUS command for other versions.

Office: check if PowerPoint is running

Dim objAPP, sMsg On Error Resume Next Set objAPP = GetObject(, "PowerPoint.Application") If TypeName(objAPP) = "Empty" Then     sMsg = "not started" Else     sMsg = "started" End If MsgBox "PowerPoint is " & sMsg, vbInformation, "PowerPoint" if sMsg = "started" then objAPP.Visible = true

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 = […]

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: " & […]

Oracle: information about ASM

Get the information about ASM disks:

SELECT GROUP_NUMBER, DISK_NUMBER, TOTAL_MB/1024 GB, NAME FROM v$asm_disk;

Get the information about ASM diskgroups (including used space and the free space):

SELECT GROUP_NUMBER, NAME, TOTAL_MB/1024 TOTAL_GB, FREE_MB/1024 FREE_GB FROM v$asm_diskgroup;

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:

restore the file from the backup recover datafile ‘&the_file_name’ ; alter database datafile ‘&the_file_name’ online;

Another possibility (if there are a […]