Hi all!
Today is a quick tip. Actually, just because more than once I was about to kill a session in a RDS session and it was simply not possible with my usual ALTER SYSTEM KILL SESSION scripts… 🙂
Know how to do it? Here it goes:
begin rdsadmin.rdsadmin_util.kill( sid => &sid, serial => &serial); end; /
Putted it in a script: @killrds.sql
Cheers!
Did you know the SIMPLE_INTEGER datatype is a subtype of the PLS_INTEGER datatype and can increase the speed of integer arithmetic in natively compiled code as well as in interpreted code?
Nice, right?
This is an 11g feature, so not that new… Have a look and use it!
Check below for datatype declaration example:
PLS_INTEGER:
var_old_fashioned PLS_INTEGER := 0;
SIMPLE_INTEGER:
var_new_way SIMPLE_INTEGER := 0;
Cheers!
Did you know we have this? Yes, and this is since 11g.
As you know, the process of tuning SQL statements often requires the testing of alternate indexing strategies to see the effect on execution plans.
Adding extra indexes to large tables can take a considerable amount of time and disk space besides possibly changing known execution plans and possibly affecting all the application behavior.
This can be problematic when you are trying to identify problems on a production system. Some of those problems can be solved by using invisible indexes, but they still would require creation and segment allocation, but also being updated by the application itself.
To solve that, virtual indexes have no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn’t affect the normal running of your system, similar to invisible indexes, but also don’t not even to be updated, as invisible indexes need to be.
To create the virtual index consists in simply add the NOSEGMENT clause to the CREATE INDEX statement, for example:
CREATE INDEX index_name ON table(column_name) NOSEGMENT;
However, to make the virtual index available we must set the _use_nosegment_indexes parameter. Another important mention is that the virtual indexes don’t appear in the USER_INDEXES view, but can be found in the USER_OBJECTS view.
Cheers!
Did you know that?
Since 11g, Oracle has something called “compound trigger”, that allows a single trigger to be used for combining actions for different timing points for a specific object.
The individual timing points share a single global declaration section, whose state is maintained for the lifetime of the statement. Once a statement ends, due to successful completion or an error, the trigger state is cleaned up. In previous releases this type of functionality was only possible by defining multiple triggers whose code and global variables were defined in a separate package.
The triggering actions are defined in the same way as any other DML trigger, with the addition of the COMPOUND TRIGGER clause. The main body of the trigger is made up of an optional global declaration section and one or more timing point sections, each of which may contain a local declaration section whose state is not maintained. For example:
CREATE OR REPLACE TRIGGER trigger_example
FOR action ON table_name COMPOUND TRIGGER
global_variable VARCHAR2(30);
BEFORE STATEMENT IS BEGIN NULL; END BEFORE STATEMENT;
BEFORE EACH ROW IS BEGIN NULL; END BEFORE EACH ROW;
AFTER EACH ROW IS BEGIN NULL; END AFTER EACH ROW;
AFTER STATEMENT IS BEGIN NULL; END AFTER STATEMENT;
END trigger_example;
/
A good summary with detailed information and examples can be found (as always) here: https://oracle-base.com/articles/11g/trigger-enhancements-11gr1
Cheers!
Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.
So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
Segment Creation on Demand or Deferred Segment Creation
I also talked about it in post EXP Missing Tables on 11.2.
Segment creation on demand, or deferred segment creation as it is also known, is a space saving feature of Oracle Database 11g Release 2. When non-partitioned tables are created, none of the associated segments (table, implicit index and LOB segments) are created until rows are inserted into the table. For systems with lots of empty tables, this can represent a large space saving. This functionality can be controlled by the DEFERRED_SEGMENT_CREATION initialization parameter, which is set to TRUE by default.
More“Segment Creation on Demand or Deferred Segment Creation”
Enjoyed last week post?
Cool, because looking further on the subject I found this pretty similar post, by Mohammad Nazmul Huda.
The additional script there is actually not working in my server, but the idea is great. So, I did just some small adjustments and it’s working pretty fine now:
# asm_report.sh (Adjusted by Matheus):
printf "\n%-15s %-14s %-11s %-7s\n" "ASM disk" "based on" "Minor,Major" "Size (MB)"
printf "%-15s %-14s %-11s %-7s\n" "===============" "=============" "===========" "========="
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome1
for i in `/usr/sbin/oracleasm listdisks`
do
v_asmdisk=`/usr/sbin/oracleasm querydisk -d $i | awk '{print $2}'| sed 's/\"//g'`
v_minor=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
v_major=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
v_device=`ls -la /dev | awk -v v_minor="$v_minor," -v v_major=$v_major '{if ( $5==v_minor ) { if ( $6==v_major ) { print $10}}}'`
v_size_bt=`blockdev --getsize64 /dev/${v_device}`
v_size=`expr $v_size_bt / 1024 / 1024`
Total_size=`expr $Total_size + $v_size`
Formated_size=`echo $v_size | sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta'`
printf "%-15s %-14s %-11s %-7s\n" $v_asmdisk "/dev/$v_device" "[$v_minor $v_major]" $Formated_size
done
Formated_Total_size=`echo $Total_size | sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta'`
printf "\nTotal (MB): %43s\n\n" $Formated_Total_size
Ok, and how it works?
[root@greporasrv ~]# sh asm_report.sh ASM disk based on Minor,Major Size (MB) =============== ============= =========== ========= DATA01 /dev/sdg1 [8 97] 255,999 DATA02 /dev/sdh1 [8 113] 255,999 DATA03 /dev/sdi1 [8 129] 255,999 DATA04 /dev/sdj1 [8 145] 255,999 FRA01 /dev/sdk1 [8 161] 307,199 Total (MB): 1,331,195
Even better, right?
Cheers!
Hello all!
So, recently I found this error in a CRS alert log from a client environment. Interesting error…
2018-03-26 16:33:53.277 [SRVM(9624)]CRS-10051: CVU found following errors with Clusterware setup : PRCW-1015 : Wallet greporadb does not exist. CLSW-9: The cluster wallet to be operated on does not exist. :[1015]PRCW-1015 : Wallet greporadb does not exist.
I found this also related to the error:
PRVG-1512 : Failed to retrieve current selection of public and private network classifications
So, it was mapped to known Bug 18234669, as per described in CRS-10051: CVU Found Following Errors With Clusterware Setup :PRCW-1015 : Wallet ora603ut does not exist (Doc ID 2008466.1).
Ok, but what to do?
1) In case you have the wallet, you can simply add it to the database:
crsctl add wallet -type CVUDB -name [dbname]
2) In case you haven’t, you can simply disable the resource ora.cvu, that is the one checking this:
> Checking Status
[root@grepora-srv ~]# crsctl stat res ora.cvu -p | grep CHECK_RESULT CHECK_RESULTS=PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet c4prod does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet c4prod does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet c4prod does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015]
> Disable CVU:
oracle:grepora-srv: srvctl disable cvu oracle:grepora-srv: oracle:grepora-srv: crsctl stat res ora.cvu -p | grep ENABLED ENABLED=0 oracle:grepora-srv: srvctl status cvu CVU is disabled
Hope it helps!
Hi all!
So, today is quickie one, just to make the links. Seems this message from OEM is not clear enough for some people, specially regarding non-specialists in Oracle: This means something is in lock in your database!
If this is the case, contact a DBA.
If you ARE a DBA, you may want to read this post about easy locating and solving locks: Solving Simple Locks Through @lock2s and @killlocker.
Also, if the session if from DBLink, is always useful to read this: Lock by DBLink – How to locate the remote session?
There is also some additional/specific material about some issues and bugs in this regard here: Tag: LOCK.
I hope it helps!
Cheers!
Hey all!
So, I had to map a couple ASM disks to physical devices. But it’s not direct, which causes some manual work.
To save me from this, I found this great post by Alejandro Vargas, with a very nice script to make this mapping easier.
I found however, it was done for RHEL/OEL 6 and older, and I’m in OEL7. So I did some small changes to adapt it.
Anyway, decided to share as this is a great script to have handy. 🙂
# Alejandro’s script (RHEL/OEL 6 and older):
#!/bin/ksh
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk '{print $2}'`
v_minor=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $1}'`
v_major=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor $v_major]"
done
# Adjustments by Matheus (RHEL/OEL7):
#!/bin/ksh
for i in `/usr/sbin/oracleasm listdisks`
do
v_asmdisk=`/usr/sbin/oracleasm querydisk -d $i | awk '{print $2}'`
v_minor=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
v_major=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor $v_major]"
done
# Example of execution:
[root@greporasrv]$ for i in `/usr/sbin/oracleasm listdisks`
> do
> v_asmdisk=`/usr/sbin/oracleasm querydisk -d $i | awk '{print $2}'`
> v_minor=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
> v_major=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
> v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
> echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor $v_major]"
> done
ASM disk "DATA01" based on /dev/sdg1 [8 97]
ASM disk "DATA02" based on /dev/sdh1 [8 113]
ASM disk "DATA03" based on /dev/sdi1 [8 129]
ASM disk "DATA04" based on /dev/sdj1 [8 145]
ASM disk "FRA01" based on /dev/sdk1 [8 161]
Hope you enjoy it like I did.
Cheers!
Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.
So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
Virtual Columns
Since 11g is possible to create columns based on functions, not physically stored on database. See the example below:
create table sales
(
sales_id number,
cust_id number,
sales_amt number,
sale_category varchar2(6)
generated always as
(
case
when sales_amt <= 10000 then 'LOW'
when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'
when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
else 'ULTRA'
end
) virtual
);
It’s also interesting to raise that starting on Release 2, virtual columns can be used as foreign keys. Should be used as FK, not sure… but can….
Cheers!