Perl: how to get the position of the match

Let’s search for the word “brave” in the string “Hello, new brave world!”.
There are several methods to get the position of the match in Perl.

#1 – use POS function
Function pos returns the position, where search was finished.


$txt = "Hello, brave new world!" ;
$txt =~ m/(brave)/g;
my $pos = pos($txt) - length $1;

#2 – use $PREMATCH variable
$PREMATCH varialbe (short name $`) contains the string, preceeding the match.
This method is easy, however it could significantly affect performance of other regular expressions (see RegExp book for the detailed explanation). Therefor, it’s not the best solution.


use English ;
$txt = "Hello, brave new world!" ;
$txt =~ m/(brave)/g;

###my $pos = length $`
my $pos = length $PREMATCH;

#3 – use @LAST_MATCH_START variable
This method was introduced in Perl 5.6.0 and it does not make any bad impact on performance – so it’s recommended solution.


use English qw( -no_match_vars );
$txt = "Hello, brave new world!" ;
$txt =~ m/(brave)/g;

### $pos = $-[0];
$pos = $LAST_MATCH_START[0];

Oracle: who is connected to the server

Here is the simple query, showing who is connected to the server.


col username for a15
col program for a25 trun

select sid, serial#, username, program, substr(module,1,10) module, logon_time
from v$session
order by 1,2;

This is almost the same query, limiting the output to the active users:

select sid, serial#, username, program, substr(module,1,10) module, logon_time
from v$session
where status = 'ACTIVE'
order by 1,2;

It’s important to set “MODULE” field for the running sessions. It makes the output more clear.
The setting could be done with the following command:


exec DBMS_APPLICATION_INFO.SET_MODULE( module_name=>'&module',action_name=>'&action' );

Oracle: kill session

The command to kill Oracle is simple:


ALTER SYSTEM KILL SESSION '&sid,&serial';

sid – session ID
serial – session serial number

 

If the version 11g and this is RAC, additional syntax is possible:

ALTER SYSTEM KILL SESSION '&sid,&serial,@&inst_id';

inst_id – instance id number (1, 2, 3 …)

 

Script to find the TOAD session older than 1 day and kill them:

select
'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' immediate;' CMD
from v$session
where
program like '%TOAD%'
and logon_time and status != 'KILLED'
;

 

If the session is killed on the Oracle level and still stays in the list for very long time with status ‘KILLED’, it could be killed on the OS level.
Warning! This is potentially dangerous operation! Use it only as the last resort.


select sess.sid, sess.serial#,
'OS:' || proc.spid SPID,
'App:' || sess.process process,
substr(sess.username,1,15) username ,
sess.program,
logon_time ,
sess.status
from v$session sess, v$process proc
where sess.paddr = proc.addr
and sess.status = 'KILLED'
order by 1,2;

Check if “App:” process still exist – maybe, it’s worth to stop the application first.
Otherwise, kill the “OS:” process.
 

mySQL: How to reset root password

If the password for the mysql “root” user is lost, it’s still possible reset it to some other value.
The restart of the mySQL server will be necessary, of course.

The following should be done:

Stop the server
It could be done normally:

/etc/init.d/mysql stop

or effectively:

ps -ef | grep mysql
...
mysql 25079 1 0 Jan 31 ? 0:00 /usr/bin/mysqld
...
kill -9 25079

Please ensure that the proper process is killed!

Start MySQL server without security checking

mysqld_safe --skip-grant-tables &

Connect to mySQL without password

mysql -u root

Reset mySQL root password

use mysql;
update user set password=PASSWORD("NEW_PASSWORD") where user='root';
flush privileges;

Use the real new password instead of the string “NEW_PASSWORD”.

Restart mySQL server

/etc/init.d/mysql stop
/etc/init.d/mysql start

Check the connection

mysql -u root -p

VBA: how to read text file

This is just example, how to read text file in Visual Basic for Applications (for example, in Excel macro).

It’s just a stub: there is no error processing here, the source file is really text file, no problems with permissions etc.


Sub read_from_text_file()

txtFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If txtFile = False Then
MsgBox "No file to process"
Exit Sub
End If

Open txtFile For Input As #1
Input #1, myString
MsgBox myString
Close #1

End Sub