Oracle Application Express Hands-On in Brasília!

Hello all!

I’m happy to announce here the next GUOB Meetup in Brasília focused on APEX .

The meeting will be conducted by

When?
Next October 9th!
2PM to 6PM

Where?
Ed. Corporate Financial Center (Oracle).
Setor Comercial Norte Q 2 – North Wing, Brasilia – Federal District

Cost? FREE
Just confirm you presence here.

IMPORTANT: bring your laptop.

Agenda:
2PM – Autonomous Database and Oracle Application Express – Monica Godoy
4PM – Coffee-break
4:30PM – Dynamic Actions in Action – Anderson Ferreira
5:30PM – Open Mic
6PM – Closure

NewScreenshot 2019-09-23 às 16.42.37

Oracle Application Express Hands-On in São Paulo!

Hello all!

I’m happy to announce here the next GUOB Meetup in São Paulo focused on APEX .

The meeting will be conducted by Monica Godoy, Product Manager do Oracle Application Express.

When?
Next October 7th!
6PM to 8PM

Where?
Rua Dr. José Áureo Bustamante, 455

Cost? FREE
Just confirm you presence here.

IMPORTANT: bring your laptop.

Agenda:
6PM – Autonomous Database and Oracle Application Express – Monica Godoy
7:30PM – Open Mic
8PM – Closure

NewScreenshot 2019-09-23 às 16.48.00

Oracle Container for Kubernetes Hands-On – Porto Alegre

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.

WhatsApp Image 2019-09-23 at 22.54.55.jpeg

guobguors

Getting Oracle version – new utility on 18c oraversion

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

Restore table statistics using dbms_stats

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

Oracle: Explicitly Lock a Table

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!

OEM Report: Last 6 month Database Space Usage and Growth

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:

Select_DB.png

2. Then you see the report:

Report3

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):

Growth

So, Mat, can you share the queries? Of course:

More“OEM Report: Last 6 month Database Space Usage and Growth”

Oracle Trigger Follows Clause: Simultaneous Ordered Triggers

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!

Regular Expressions with SUBEXPR

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!

Oracle: Create Disabled Triggers

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!