Hello all!
I’m happy to announce here the next Meetup in Porto Alegre speaking over Oracle Container for Kubernetes and how Oracle works with Cloud Native.
The meeting will be conducted by Diogo Shibata, from Oracle.
When?
Next October 2nd!
Where?
ATTIVE Coworking – Rua Carlos Gardel nº 55 – Bela Vista – Porto Alegre
Cost? FREE
Just confirm you presence here.
IMPORTANT: bring your laptop.
While scripting on an environment with different Oracle versions, I often needed to get the Oracle version for the target database as usually the SQL to be executed is version depended.
I used different methods to grab the database version some you can see below:
SQL> select substr( substr( banner, instr(banner,' ',-(length(banner)-instr(banner,'.')),1)+1, instr(banner,' ',+instr(banner,'.'),1)-instr(banner,' ',-(length(banner)-instr(banner,'.')),1)-1 ), 1, instr( substr( banner, instr(banner,' ',-(length(banner)-instr(banner,'.')),1)+1, instr(banner,' ',+instr(banner,'.'),1)-instr(banner,' ',-(length(banner)-instr(banner,'.')),1)-1 ), '.' )-1 ) version from v$version where rownum = 1 ; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 VERSION -------------------------------------------------------------------------------- 18
Or like this
SQL> select substr(version,1,instr(version,'.')-1) version from product_component_version where product like 'Oracle%'; VERSION -------------------------------------------------------------------------------- 18
But on Oracle 18c there is a new utility called oraversion which you can get the same result as the above queries.
[oracle@server01 ~]$ oraversion This program prints release version information. These are its possible arguments: -compositeVersion: Print the full version number: a.b.c.d.e. -baseVersion: Print the base version number: a.0.0.0.0. -majorVersion: Print the major version number: a. -buildStamp: Print the date/time associated with the build. -buildDescription: Print a description of the build. -help: Print this message. [oracle@server1 ~]$ [oracle@server01 ~]$ oraversion -majorVersion 18 [oracle@server01 ~]$
This could be somewhat useful but I though it was worth sharing.
Until next time.
Elisson Almeida
Hi all, another quick as reference. Table statistics as everyone knows are very important when the CBO is creating the execution plan. When you have new statistics Oracle will invalid the current execution plans affected by them and create new execution plans based on the new statistics. Most of the time it gets right if that causes your execution plan to change for worse?
In this situations dba_tab_stats_history view and dbms_stats.restore_table_stats procedures are your friends.
To validate set and check and set the stats history:
select dbms_stats.get_stats_history_retention from dual; exec dbms_stats.alter_stats_history_retention(30);
On dba_tab_stats_history you can view the last statistics available:
select table_name, stats_update_time, from dba_tab_stats_history where table_name='TABLE1' and owner='USER' order by 2 desc;
Use the dbms_stats.restore_table_stats to restore the statistics:
exec dbms_stats.restore_table_stats(ownname=>'USER',tabname=>'TABLE1',AS_OF_TIMESTAMP=>'DATE from column STATS_UPDATE_TIME on dba_tab_stats_history');
Hope it helps!
Elisson Almeida
Hello all,
So I client had a process that kept need big chunks locks on a table. Problem is that due application use this lock occasionally failed (lock timeout) and the whole process fail. So how to guarantee this?
Using explicit locks for the table before starting the procedure, with command below:
LOCK TABLE GREPORA.GREP_TABLE IN SHARE MODE;
This lock is released as soon as the session commit or rollback transaction in the current session. Killing session means an implicit rollback, as usual.
The SHARE permits concurrent queries but prohibits updates to the locked table.
Nice right?
Hope it helps you too!
Hello All!
So I had worked in some very useful reports to have in OEM. In next weeks I’ll share some code you may like… 🙂
To create it? Enterprise -> Reports -> Information Reports. There are several nice default reports there you me like.
Now, let’s go to the first Report, as per title:
1. First you Select the Database:
2. Then you see the report:
That’s nice, right?
Here is a report for another database with actual 640GB average growth per month (its expected, once DBSize is over 60TB):
So, Mat, can you share the queries? Of course:
More“OEM Report: Last 6 month Database Space Usage and Growth”
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 here is something you may not be aware and is really cool:
Follows Clause
Oracle allows more than one trigger to be created for the same timing point, but up to version 11g is not possible to establish the execution order of execution. The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point.
The example below establish that second_trigger is executed after first_trigger before inserting each row in table_example.
CREATE OR REPLACE TRIGGER second_trigger BEFORE INSERT ON table_example FOR EACH ROW FOLLOWS first_trigger BEGIN NULL; END; /
Nice, han?
Cheers!
Hi All!
Did you know since 11g the REGEXP_INSTR and REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits the pattern match to a specific subexpression in the search pattern?
Also, a new function, REGEXP_COUNT, returns the number of times the search pattern appears in source string.
Below a simple example of each one:
SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) FROM dual;
REGEXP_INSTR('1234567890','(123)(4(56)(78))',1,1,0,'I',3)
---------------------------------------------------------
5
SQL> SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3) FROM dual;
RE
--
56
SQL> SELECT REGEXP_COUNT('123 123 123 123', '123', 1, 'i') FROM dual;
REGEXP_COUNT('123123123123','123',1,'I')
----------------------------------------
4
Cheers!
Hey,
I believe you may know about this already, but in case its something new to you: Yes, this is possible since 11g!
Prior to 11g, it was only possible to create triggers in the enabled state, and after creating to disable them. Since 11g they can be explicitly enabled or disabled at creation time using clause ENABLE or DISABLE. As per example:
CREATE OR REPLACE TRIGGER disabled_trigger BEFORE INSERT ON table_example FOR EACH ROW DISABLE BEGIN NULL; END; /
If didn’t know it yet. Np! You know it now! 😀
Cheers!
Hi All!
Following the line of last week post, let’s talk today about the JFT…
Oracle SQL Join Factorization Transformation (JFT)
The UNION operator is great for merging the results of multiple queries that return similar rowsets, essentially executing each query separately and merging the results together into a single result set. Oracle 11g release 2 come with improvements for SQL optimizer on how Oracle handles UNION ALL performance with the new join factorization transformation (JFT). The Join Factorization Transformation applies only to UNION ALL queries.
The following example show how the optimizer improves the performance of UNION ALL by dynamically re-writing a UNION ALL query into a more efficient form using an in-line view:
Original Query:
select t1.c1, t2.c2
from t1, t2, t3
where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2
union all
select t1.c1, t2.c2
from t1, t2, t4
where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;
Re-Written Query:
select t1.c1, VW_JF_1.item_2
from t1, (select t2.c1 item_1, t2.c2 item_2
from t2, t3
where t2.c2 = t3.c2 and t2.c2 = 2
union all
select t2.c1 item_1, t2.c2 item_2
from t2, t4
where t2.c3 = t4.c3) VW_JF_1
where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1;
Nice, right?
Cheers!
Have you ever heard:
“My session was killed. I’m trying again. There is no locks. But My SQL doesn’t run!”
By default, when a session is disconnected from Oracle, the SQLs uncommitted are undone (rollback). So, in case of a long routing there is a probably a rollback in place.
How Oracle rollback take that long? Well, it’s Oracle mechanism. The RDBMS basically assume you know what your are doing and start writing the new blocks so the commit will be very quick. Which, however, is bad in case of rollbacks.
How to check if this is your case?
select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta from x$ktuxe where ktuxecfl = 'DEAD' 4 / KTUXEUSN Time KTUXESIZ KTUXESTA ---------- -------------------- ---------- ---------------- 31 07-APR-2018 16:48:53 17705 ACTIVE 48 07-APR-2018 16:48:53 108856 ACTIVE
Hm.. What can I do? Not much really, we need to wait the rollback to complete.
One thing that can be done to increase the rollback is changing the fast_start_parallel_rollback parameter, as per:
SQL> show parameters roll NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fast_start_parallel_rollback string LOW rollback_segments string transactions_per_rollback_segment integer 5 SQL> alter system set fast_start_parallel_rollback=HIGH; System altered.
Hope it helps!
Cheers!