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

Oracle: get the tablespace structure

The easiest method to get the tablespace structure is to use the dbms_metadata package:


set linesize 200 long 50000 pagesize 5000

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name )
from dba_tablespaces
where tablespace_name in ( '&your_tablespace' );

Additionally, exp/imp (or expdp/impdp) utilities could be used. They could generate the DDL statements for all objects in the database, which could be very useful.

exp/imp


exp userid=" '/ as sysdba' " rows=n file=my.dmp full=y
imp userid=" '/ as sysdba' " file=my.dmp full=y show=y > full_structure.sql 2>&1

Warning! exp could not get the contents of the encrypted tablespaces.

expdp/impdp

expdp "'/ as sysdba'" dumpfile=mydp.dmp directory=DATA_PUMP_DIR content=metadata_only full=y
impdp "'/ as sysdba'" dumpfile=mydp.dmp directory=DATA_PUMP_DIR sqlfile=full_structure.sql

Outlook 2007: set default font in Outlook

I think, the “Options” window in Outlook is one of the most tangled things in IT. The programmers were so smart to create real “spaghetti” window! Did they ever hear about “user friendly” interfaces? They could at least add the “Search” field somewhere – otherwise, it’s not possible to find anything.

Here is the way to change the default font:

  • Tools -> Options
  • Click “Mail Format” tab
  • Click “Stationery and Fonts…” button
  • Select “Personal Stationery” tab
  • Click “Font” button to change the font

It’s nice, isn’t it?

Here there are even more paths for different options.

Perl: SOAP problem with dateTime

The following error message was received by Perl application:
Unrecognized type ‘{http://www.w3.org/1999/XMLSchema}dateTime

The problem is caused by “dateTime” type: it was not supported in the 1999 XML Specification.
The solution is to switch to 2001 schema:

[cc lang=”Perl”]
my $soap = SOAP::Lite->uri( $NAMESPACE )->proxy( $PROXY_URL )->xmlschema (‘2001’);
[/cc]