Oracle regular expressions examples (REGEXP)

  • Oracle 10 and above

Примеры использования регулярных выражений.

REGEXP_REPLACE
1. Замена всех пробелов в строке на двойной пробел.

with t(fname) as
(select 'Hello, dear   friend! Nice to  see   you!' from dual)
select regexp_replace(fname, '[ ]+', '  ')  from t;</pre>
<pre>fname
--------------------------------------------------
Hello,  dear  friend!  Nice  to  see  you!

2. Замена первых n-символов в строке на другие.

with t(fname) as
(select 'Regular expressions is very easy' from dual)
select regexp_replace(fname, lpad(fname, 7), 'Arithmetic')  from t;

FNAME                              
-----------------------------------
Arithmetic expressions is very easy

3. Замена n-ого символа(ов) в строке, начиная с позиции k

with t(fname) as
(select 1000340082145076 from dual)
select regexp_replace(fname, '..', '21', 5, 1) fname  from t;

FNAME           
----------------
1000210082145076

4. Получить часть строки после первой запятой

with t(fname) as
(select 'gfdgh  ввап1; 567, 23425' from dual)
select regexp_replace(fname,  '^[^,]*,') fname  from t;

FNAME
------
23425

5. Удаляем из строки html tags

with t(fname) as
(select '<span style="color: rgb(255, 255, 153);">Hello World!</span><br style="color: rgb(255, 255, 153);"><span style="color: rgb(255, 255, 153);">Listen to your heart!</span><br>' from dual)
select regexp_replace(fname, '<[^>]*>', ' ') fname  from t;

FNAME
---------------------------------------
Hello World!   Listen to your heart!

6. Вырезать значение из двойных ковычек

with t(fname) as
(select 'LTD  "GROUP  "BIG WORLD" Las Vegas' from dual)
select replace(regexp_replace(fname, '^([^"]*"(.*)"[^"]*|(.*))$', '\2\3'), '"') fname  from t;

FNAME
----------------
GROUP  BIG WORLD

REGEXP_SUBSTR
1. Вырезать часть строки по заданному шаблону

with t(fname) as
(select '(KERAMIN."user_role_auth"(p_username=>:USER_NAME, p_groupname=>'||chr(39)||'public'||chr(39)||', p_level=>5, p_app_id=>:APP_ID) or KERAMIN."user_role_auth"(p_username=>:USER_NAME, p_groupname=>'||chr(39)||'ADMINISTRATORS'||chr(39)||', p_level=>10, p_app_id=>:APP_ID) or KERAMIN."user_role_auth"(p_username=>:USER_NAME, p_groupname=>'||chr(39)||'users'||chr(39)||', p_level=>15, p_app_id=>:APP_ID))' fname from dual)
select regexp_substr(fname,'KERAMIN."user_role_auth"\(p_username=>:USER_NAME, p_groupname=>[^,]+, p_level=>10, p_app_id=>:APP_ID\)', 1, 1) fname  from t;

FNAME
--------------------------------------------------------------------------------
KERAMIN."user_role_auth"(p_username=>:USER_NAME, p_groupname=>'ADMINISTRATORS',
p_level=>10, p_app_id=>:APP_ID)

2. Делим одну строку на несколько строк с использованием разделителя (например, <,>)

with t as
(select 'O,r,a,c,l,e' fname from dual)
select regexp_substr(fname,'[^,]+', 1, level) fname from t
connect by regexp_substr(fname, '[^,]+', 1, level) is not null;

FNAME
-----------
O
r
a
c
l
e

REGEXP_COUNT
1. Подсчет количества цифр в строке

with t(fname) as
(select '12 mgf45, fhfh-565gh hgh56 66' fname from dual UNION ALL
select 'fd8g78df7g8ghorhegy g rgtueriogti' fname from dual UNION ALL
select '5768hf hgh565 g-+g 676 454ggf55fhgfh' fname from dual)
select regexp_count(fname, '\d') fname from t;

FNAME
----------
11
5
15

2. Подсчет количества символов в строке без учета регистра

with t(fname) as
(select 'A fox is not taken twice in the same snare' fname from dual)
select regexp_count(fname, 'a', 1, 'i') fname from t;

FNAME
----------
4
Реклама

How Does An Execution Plan Suddenly Change When The Statistics (And Everything Else) Remains The Same ? (In Limbo)

Richard Foote's Oracle Blog

I’ve slipped this post in as there have been a number of discussions recently on how execution plans have changed while nothing else appears to have changed in the database. How can an execution plan suddenly change when no one has made any changes to the database ?
 
By no changes, it means that there have been no alterations to any segments, no new indexes have been added, no changes associated  bind peeking (indeed, there may not even be any bind variables), no parameters changes, no new patches or upgrades, no new outlines or profiles, no new system stats and perhaps most prevalent of all, no changes to any CBO statistics.
 
The DBA hasn’t touched a thing and yet suddenly, for no apparent reason, execution plans suddenly change and (say) an inappropriate index is suddenly used and causes performance degradation.
 
How can this be possible ?
 
There are…

View original post ещё 1 271 слово

Clustering Factor: A Consideration in Concatenated Index Leading Column Decision (Sweet Thing)

Richard Foote's Oracle Blog

Short but sweet today.

I last discussed how high cardinality columns shouldn’t necessarily be in the leading column of a concatenated index as  they don’t provide the performance benefit as sometimes claimed.

If all things are equal and the columns in the concatenated index are all likely to be referenced, a simple consideration that is often forgotten when deciding which column to have as the leading index column is the Clustering Factor of the corresponding columns.

As previously discussed, the Clustering Factor  determines how well aligned or ordered the index entries are in relation to the rows in the parent table. So if the rows are ordered within the table on a particular column or columns (such as a sequential ID column, a monotonically increasing date or time-stamp, etc), then an index on these columns is likely to have a very good Clustering Factor. Consequently less IOs will be required to retrieve all the required rows…

View original post ещё 275 слов

Important !! Clustering Factor Calculation Improvement (Fix You)

Richard Foote's Oracle Blog

Believe me, this article is worth reading :)

I’m currently not allowed to discuss Oracle 12c Database goodies but I am allowed to discuss things perhaps initially intended for 12c that are currently available and already back-ported to 11g. This includes a wonderful improvement in the manageability of how the Clustering Factor (CF) of an index can now be calculated. Many thanks to Martin Decker for pointing this out to me.

As anyone who has attended my Index Seminars will know, the CF of an index is one of the most important statistics used by the Cost Based Optimizer (CBO) in determining the most efficient execution plan. As such, it has always been an issue for me that the manner in which the CF is calculated has been so flawed.

Basically, the CF is calculated by performing a Full Index Scan and looking at the rowid of each index entry. If the…

View original post ещё 1 108 слов

Reuse Of Empty Index Leaf Blocks (Free Four)

Richard Foote's Oracle Blog

A recent question by Stalin Subbiah has prompted me to write a quick post on the reuse of empty leaf blocks. In part, the question asked:

«Is there anyway I could monitor the effectiveness of empty blocks being reused from freelist of an index resulting from purge process that we are planning to start soon?»

I’ve previously discussed how Oracle can recycle index blocks that contain nothing but deleted index entries as such blocks are effectively added to the index freelist to be reused by subsequent index block splits. In my «Index Internals — Rebuilding The Truth» presentation, I mention a number of methods of how to see this reuse in operation, such as via block dumps, tree dumps and INDEX_STATS.

However, another simple method which I don’t think I’ve discussed here before is the use of the DBMS_SPACE package. So to help answer Stalin’s question, a simple…

View original post ещё 291 слово

ManageEngine ServieDesk : wrong DueBy Date for SLA

Если в созданной заявке неверно проставляется Срок исполнения (DueBy Date), нужно проверить параметры, от которых данный показатель:
1. Admin -> Operation Hours: правильно ли задано рабочее время.
2. Admin -> Service Catalog -> кнопка Manage: правильно ли задано значение времени в SLA.

Если в предыдущих пунктах все верно, то нужно обратить внимание на версию JRE time zone, под которой работает ServiceDesk.
Может быть она устарела и требует немедленного обновления!

1. В командной строке (CMD) получим версию JRE time zone и текущего файла tzupdater.jar (если файла tzupdater.jar нет, то переходим к п.2.):

c:\ManageEngine\ServiceDesk — это директория, куда установлен ServiceDesk

CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -V -v

------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
 java.vendor: Sun Microsystems Inc.
 java.version: 1.6.0_45
 JRE time zone data version: tzdata2014e    //версия загруженная в java
 Embedded time zone data version: tzdata2014e    //версия файла tzupdater.jar
 Validating the time zone data
 Validation complete 

2. Теперь пройдем по этой ссылке http://www.oracle.com/technetwork/java/javase/tzdata-versions-138805.html к списку всхем имеющихся версий tzupdater.jar.

Например, после нашей имеющейся версии tzdata2014e уже было выпущено несколько новых, где добавлены и изменены часовые зоны
(в одном из которых, Since Belarus is not changing its clocks even though Moscow is, the time zone abbreviation in Europe/Minsk is changing from FET to its more-traditional value MSK on 2014-10-26 at 01:00).

3. Здесь можно скачать последнюю версию tzupdater.jar: http://www.oracle.com/technetwork/java/javase/downloads/index.html

4. Скачанный файл нужно скопировать в директорию c:\ManageEngine\ServiceDesk\jre\bin\. Если там уже имеется файл, то переименуйте его, например, tzupdater.jar.old.

5. В командной строке получим разницу версий JRE time zone и скаченного файла tzupdater.jar:

CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -t -v

-----------------------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_45
JRE time zone data version: tzdata2014e    //текущая версия загруженная в java
Embedded time zone data version: tzdata2014i    //версия файла tzupdater.jar недавно скачанного
Validating the time zone data
/data/tzdata2014i.test:4870: test failed: Asia/Kashgar
/data/tzdata2014i.test:4874: test failed: Asia/Urumqi
/data/tzdata2014i.test:5402: test failed: Pacific/Fiji
/data/tzdata2014i.test:5404: test failed: Pacific/Fiji
/data/tzdata2014i.test:5406: test failed: Pacific/Fiji
/data/tzdata2014i.test:5408: test failed: Pacific/Fiji
/data/tzdata2014i.test:5410: test failed: Pacific/Fiji
/data/tzdata2014i.test:5412: test failed: Pacific/Fiji
/data/tzdata2014i.test:5414: test failed: Pacific/Fiji
/data/tzdata2014i.test:5416: test failed: Pacific/Fiji
/data/tzdata2014i.test:5418: test failed: Pacific/Fiji
/data/tzdata2014i.test:5517: time zone not found: Pacific/Bougainville
/data/tzdata2014i.test:5518: time zone not found: Pacific/Bougainville
/data/tzdata2014i.test:5519: time zone not found: Pacific/Bougainville
/data/tzdata2014i.test:5522: test failed: Asia/Yakutsk
/data/tzdata2014i.test:5525: test failed: Europe/Kaliningrad
/data/tzdata2014i.test:5528: test failed: Europe/Volgograd
/data/tzdata2014i.test:5531: test failed: Asia/Magadan
/data/tzdata2014i.test:5532: time zone not found: Asia/Srednekolymsk
/data/tzdata2014i.test:5533: time zone not found: Asia/Srednekolymsk
/data/tzdata2014i.test:5534: time zone not found: Asia/Srednekolymsk
/data/tzdata2014i.test:5537: test failed: Asia/Khandyga
/data/tzdata2014i.test:5540: test failed: Asia/Yekaterinburg
/data/tzdata2014i.test:5543: test failed: Asia/Vladivostok
/data/tzdata2014i.test:5548: test failed: Europe/Simferopol
/data/tzdata2014i.test:5551: test failed: Asia/Ust-Nera
/data/tzdata2014i.test:5554: test failed: Asia/Omsk
/data/tzdata2014i.test:5557: test failed: Asia/Novosibirsk
/data/tzdata2014i.test:5560: test failed: Asia/Sakhalin
/data/tzdata2014i.test:5563: test failed: Asia/Krasnoyarsk
/data/tzdata2014i.test:5564: time zone not found: Asia/Chita
/data/tzdata2014i.test:5565: time zone not found: Asia/Chita
/data/tzdata2014i.test:5566: time zone not found: Asia/Chita
/data/tzdata2014i.test:5569: test failed: Asia/Irkutsk
/data/tzdata2014i.test:5572: test failed: Europe/Moscow
/data/tzdata2014i.test:5577: test failed: America/Grand_Turk
/data/tzdata2014i.test:5580: test failed: W-SU

Validation tests failed. 

6. Теперь обновим JRE Time zone:
CMD>

CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -u -v

----------------------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_45
JRE time zone data version: tzdata2014e
Embedded time zone data version: tzdata2014i
Extracting files... done.
Renaming directories... done.
Validating the new time zone data... done.
Time zone data update is complete. 

7. Проверим версию JRE time zone:
CMD>

</pre>
CMD>
cd c:\ManageEngine\ServiceDesk\jre\bin\
java -jar tzupdater.jar -t -v

----------------------------------------------------------
java.home: c:\ManageEngine\ServiceDesk\jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_45
JRE time zone data version: tzdata2014i        //версия загруженная в java
Embedded time zone data version: tzdata2014i    //версия файла tzupdater.jar
Validating the time zone data
Validation complete 

Значения JRE time zone data version и Embedded time zone data version должны быть равны!

8. Перезапустим машину, на которой установлен сервер ManageEngine SevriceDesk, чтобы все измнения вступили в силу.

9. Если после выполнения п.1-п.8 все равно проблема осталась, то откройте файл c:\ManageEngine\Servicedesk\bin\startout.log и найдите в нем значение параметра user.timezone (временная зона). Если значение не равно временной зоне, что установлена в системе (например, если user.timezone = America/Caracas, а в системе установлено Europe/Minsk), значит Java запускается с какой-то своей временной зоной по умолчанию.

10. Чтобы исправить эту ситуацию, нужно остановить службу SD, открыть файл c:\ManageEngine\Servicedesk\server\default\conf\wrapper.conf на редактирование. Найти в нем раздел # Java Additional Parameters, где перечислены параметры запуска Java, и добавить туда новый параметр (-Duser.timezone):
В моем случае в этом разделе:

# Java Additional Parameters (added new parameter #24)
wrapper.java.additional.1=-server
wrapper.java.additional.2=-Dprogram.name=run.sh
wrapper.java.additional.3=-Djboss.server.type=com.adventnet.j2ee.deployment.system.AdventNetServerImpl
wrapper.java.additional.4=-Dorg.jboss.logging.Log4jService.catchSystemOut=false
wrapper.java.additional.5=-Dorg.jboss.logging.Log4jService.catchSystemErr=false
wrapper.java.additional.6=-Djava.util.logging.manager=com.adventnet.logging.LogManager
wrapper.java.additional.7=-Djava.util.logging.config.file=../server/default/conf/logging.xml
wrapper.java.additional.8=-Djava.util.logging.config.class=com.adventnet.logging.LoggingScanner
wrapper.java.additional.9=-Dlog.dir=../server/default
wrapper.java.additional.10=-Dtier-type=BE
wrapper.java.additional.11=-Dtier-id=BE1
wrapper.java.additional.12=-Ddb.home=../pgsql
wrapper.java.additional.13=-Dproduct.home=../
wrapper.java.additional.14=-Dfile.encoding=UTF-8
wrapper.java.additional.15=-Dsdp.java.gc=false
wrapper.java.additional.16=-Dserver.dir=../
wrapper.java.additional.17=-Duser.language=en
wrapper.java.additional.18=-Duser.region=US
wrapper.java.additional.19=-XX:PermSize=64m
wrapper.java.additional.20=-XX:MaxPermSize=256m
wrapper.java.additional.21=-Djboss.shutdown.forceHalt=false
wrapper.java.additional.100=-DlogDir=../server/default/log
wrapper.java.additional.22=-Dproduct.home=../
wrapper.java.additional.23=-Dorg.tanukisoftware.wrapper.WrapperManager.mbean=false
wrapper.java.additional.24=-Duser.timezone=Europe/Minsk # добавил нужную мне timezone

11. Запустить службу SD. Попробовать создать новую заявку, Срок исполнения должен ставиться верный, исходя из выбранного SLA.

Real-Time segments statistics

bdt's oracle blog

The v$segment_statistics and v$segstat views are a goldmine to extract statistics that are associated with the Oracle segments.

You can see how useful it could be in those posts :

Kevin Closson’s post

Jonathan Lewis’s post or this one

Arup Nanda’s post

But those views are cumulatives, so not so helpful to report real-time information on the segments (Imagine your database is generating a lot of I/O right now and you would like to know wich segments are generating those I/O).

To report real-time statistics on the segments I wrote the segments stats.pl script (click on the link and then on the view source button to copy/paste the source code) that basically takes a snapshot based on the v$segstat view each second (default interval) and computes the differences with the previous snapshot.

Let’s see an example:

./segments_stats.pl Connecting to the Instance... 07:10:45 INST_NAME OWNER OBJECT_NAME STAT_NAME VALUE 07:10:45 BDT1 BDT BDTTAB…

View original post ещё 304 слова

Powershell and MS Exchange 2013

Управление рапсределенными группами и членами групп MS Exchange 2013 с помощью Powershell.
(Managing distribution groups and members of groups of MS Exchange 2013 by Powershell)

#################################################
#------------ Подготовка PowerShell ------------#
#################################################
# Allow powershell access to remote objects
Get-ExecutionPolicy
Set-ExecutionPolicy RemoteSigned

###############################################
#------------ Проверочные функции ------------#
###############################################
#F: Function to check the existence of distribution group: 0 - not exists, 1 - exists
function ExistsGroup
{param($group)
if (Get-DistributionGroup $group -ErrorAction 'SilentlyContinue' | select Name) {$res = 1}
else {$res = 0}
Return $res
}

#F: Function to check the existence of user: 0 - not exists, 1 - exists, 2 - exists, but account offline
function ExistsUser
{param($user)

$attr=Get-User $user -ErrorAction 'SilentlyContinue'
if ($attr){
$attr | Foreach-Object{
$attr = $_

$uaccount=$attr.UserAccountControl
if ($uaccount -eq "NormalAccount"){$res = 1} else{$res = 2}
}
} else {$res = 0}

Return $res
}

#F: Function to check the existence of membership in distribution group: 0 - not member, 1 - member
function ExistsGroupMember
{param($group, $alias)

$members=Get-DistributionGroupMember $group -ErrorAction 'SilentlyContinue'
$members | Foreach-Object{
$members = $_
$memberlist=$members.Alias
foreach($member in $memberlist){
if ($member -eq $alias){$r = 1
Write-Host $member " = " $alias
}
}
}
if ($r -eq 1) {$res = 1}
else {$res = 0}

Return $res
}

#F: Function to check the existence of mail user: 0 - not exists, 1 - exists
function ExistsEmail
{param($user)
if (Get-user $user -ErrorAction 'SilentlyContinue' | Select WindowsEmailAddress){$res = 1} else {$res = 0}
Return $res
}

#F: Function to send mail
function SendEmail
{param($body)
$pw = convertto-securestring -AsPlainText -Force -String test
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist "domain\robot",$pw
$rus = [Text.Encoding]::Unicode

send-mailmessage -from "User01 <robot@domain.int>" -to "User02 <souluran@domain.int>", "User03 <it@domain.int>" -subject "TEST" -body $body -Encoding $rus -smtpServer mail.domain.int -credential $cred

}

##################################################################
#------------ Configurating connection to MS Exchane ------------#
##################################################################
$pw = convertto-securestring -AsPlainText -Force -String password # set password
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist "domain\user",$pw #your credentials
$session = new-pssession -ConfigurationName Microsoft.Exchange -ConnectionUri https://server_exchange.domain.int/powershell/ -Credential $cred #connecting string to MS Exchange 2013

#######################################################
#------------ Open connect to MS Exchange ------------#
#######################################################
Import-PSSession $session -AllowClobber -DisableNameChecking

########################################################
#------------ Managing distribution groups ------------#
########################################################
#E: Example 1 - Создание группы ITGROUP (Security Group - Universal)
$NAME = "ITGROUP"
$DisplayName = "Группа IT"
$OrganizationalUnit = "keramin.int/special/kadrgroupe"
# Проверим, существует ли уже такое имя группы
$ch=ExistsGroup($NAME)
if ($ch -eq 0) {
# Если не существует, то создаем группу ITGROUP
Write-Host -ForegroundColor Magenta "    CREATE group: " $NAME
New-DistributionGroup -Name $NAME -DisplayName $DisplayName -OrganizationalUnit $OrganizationalUnit -SamAccountName $NAME -Type "Security" -Confirm:$False  -ErrorAction 'SilentlyContinue'
}
else {
Write-Host -ForegroundColor Red "    WARNING! Group " $NAME " is already exists in LDAP!"
}

#E: Example 2 - Переименование группы ITGROUP в ITGROUP2
$NAME2 = "ITGROUP2"
$DisplayName2 = "Группа IT 2"
# Обновялем аттрибуты группы
Write-Host -ForegroundColor Magenta "    RENAME group: " $NAME " --> " $NAME2
Set-DistributionGroup -Identity $NAME -Name $NAME2 -DisplayName $DisplayName2 -SamAccountName $NAME2 -Confirm:$False -ErrorAction 'SilentlyContinue'

#E: Example 3 - Удаление группы ITGROUP2
#   Write-Host -ForegroundColor Magenta "    DELETE group: " $NAME2
#   Remove-DistributionGroup -Identity $NAME2 -Confirm:$False -ErrorAction 'SilentlyContinue'

####################################################################
#------------ Managing memebers of distribution groups ------------#
####################################################################

#E: Example 1 - Добавление пользователя в члены группы
$UPN="user1@domain.int"  #User principal name
$FIO="Тестовый пользователь"
$ALIAS="user1"
$NAME="FINGROUP"

# Проверяем существование пользователя
$ru=ExistsUser($UPN)
if ($ru -eq 0) {Write-Host -ForegroundColor Red "    User " $UPN " is not exists in Active Directory!"
}
elseif ($ru -eq 2) {Write-Host -ForegroundColor Red "    User " $UPN " exists, but is Disabled in LDAP!"}
else {

# Проверяем является ли членом группы FINGROUP пользователь user1
$rm=ExistsGroupMember $NAME $ALIAS
if ($rm -eq 0) {

# Проверяем имеется ли email у пользователя user1. Email должен существовать, иначе рассылка по этому пользователю не будет работать.
$re=ExistsEmail($UPN)
if ($re -eq 1) {
# Добавляем пользователя user1 в группу FINGROUP
Write-Host -ForegroundColor Magenta "    ADD user: " $UPN " to " $NAME
Add-DistributionGroupMember -Identity $NAME -Member $UPN -Confirm:$False -ErrorAction 'SilentlyContinue'

}
}
else {
Write-Host -ForegroundColor Magenta "    User: " $UPN " already exists in " $NAME
}
}

#E: Example 2 - Перевод пользователя user1 из группы FINGROUP в группу BUHGROUP
$NAME2="BUHGROUP"
Write-Host -ForegroundColor Magenta "    ADD user: " $FIO " into " $NAME2
# Удаляем пользователя из группы FINGROUP
Remove-DistributionGroupMember -Identity $NAME -Member $FIO -Confirm:$False -ErrorAction 'SilentlyContinue'
# Добавляем пользователя в группу BUHGROUP
Add-DistributionGroupMember -Identity $NAME2 -Member $FIO -Confirm:$False -ErrorAction 'SilentlyContinue'

##############################################
#------------ Отправка сообщения ------------#
##############################################

$bodymess = "ALL OK!"
SendEmail($bodymess)

########################################################
#------------ Close connect to MS Exchange ------------#
########################################################
Remove-PSSession $session