Development

Source Control for Oracle, a Customer Success Story

Gerger Consulting - Tue, 2021-09-28 02:20

Gitora is a version control tool for the Oracle Database. It helps DBA's, developers to manage their database code and objects using Git and enables them to use modern software development workflows.

Attend the webinar by Plexxis to learn how they are using Gitora to improve their software development process.

Click here to register.

Categories: Development

Another interesting troubleshooting case

XTended Oracle SQL - Fri, 2021-05-28 16:58

Got an interesting question today in RuOUG:

Some very simple PL/SQL procedures usually are completed within ~50ms, but sometimes sporadically longer than a second. For example, the easiest one from these procedures:

create or replace PROCEDURE XXXX (
  P_ORG_NUM IN number,
  p_result OUT varchar2, 
  p_seq OUT number 
) AS 
BEGIN
  p_seq := P_ORG_NUM; p_result:='';
END;

sql_trace shows that it was executed for 1.001sec and all the time was “ON CPU”:

Trace file /opt/oracle/diag/rdbms/rdb4/rdb/trace/rdb_ora_7100.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
Build label:    RDBMS_19.10.0.0.0DBRU_SOLARIS.SPARC64_210107
ORACLE_HOME:    /opt/oracle/product/19
System name:	SunOS
Node name:	node2.zzzzz.ru
Release:	5.11
Version:	11.3
Machine:	sun4v
Oracle process number: 137
Unix process pid: 7100, image: oracle@node2.zzzzz.ru

*** 2021-05-26T15:38:31.321550+03:00
*** SESSION ID:(68.47940) 2021-05-26T15:38:31.321609+03:00
*** CLIENT ID:() 2021-05-26T15:38:31.321633+03:00
*** SERVICE NAME:(SYS$USERS) 2021-05-26T15:38:31.321656+03:00
*** MODULE NAME:(JDBC Thin Client) 2021-05-26T15:38:31.321679+03:00
*** ACTION NAME:() 2021-05-26T15:38:31.321703+03:00
*** CLIENT DRIVER:(jdbcthin : 19.3.0.0.0) 2021-05-26T15:38:31.321728+03:00

=====================
PARSING IN CURSOR #18446744071368904384 len=53 dep=0 uid=51 oct=47 lid=51 tim=190436799743741 hv=804128640 ad='12345678' sqlid='aabbaabbaa123'
BEGIN AAAA.XXXX(:1 ,:2 ,:3 ); END;
END OF STMT
PARSE #18446744071368904384:c=28,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=190436799743741
BINDS #18446744071368904384:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=24 off=0
  kxsbbbfp=ffffffff747c6c98  bln=22  avl=02  flg=05
  value=6
 Bind#1
  oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1400010 frm=01 csi=873 siz=32767 off=0
  kxsbbbfp=ffffffff747b8020  bln=32767  avl=00  flg=05
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1400000 frm=01 csi=873 siz=24 off=0
  kxsbbbfp=ffffffff747c6c68  bln=22  avl=00  flg=05

*** 2021-05-26T15:38:33.172899+03:00
WAIT #18446744071368904384: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=190436800744970
EXEC #18446744071368904384:c=1104,e=1001110,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=190436800745125
XCTEND rlbk=0, rd_only=1, tim=190436800745192


Originally I asked for dtrace/flamegraph and got just this:

              unix`_resume_from_idle+0x280
              genunix`cv_timedwait_sig_hires+0x1c4
              genunix`cv_waituntil_sig+0x90
              genunix`nanosleep+0xd4
              unix`syscall_trap+0x228

              0xffffffff7c04dbcc
              0xffffffff7c0393ec
              0xffffffff7c02e084
              0xffffffff7c02f860
              0x1176ef724
              0x118376518
              0x118366b84
              0x118365d14
              0x118366078
              0x118314b60
              0x1183973cc
              0x1182c707c
              0x1102edef8
              0x10aa60064
              0x10c224038
              0x10aa50ca8
              0x115bbc370
              0x10aa196dc
              0x10aa2c59c
              0x10aa50ca8
  ns                                                
           value  ------------- Distribution ------------- count    
       268435456 |                                         0        
       536870912 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1        
      1073741824 |                                         0      

But truss showed much more interesting details:

 0.000037    write(11, "   B i n d # 2\n", 8)           = 8
 0.000045    write(12, " 2 0 m 7 } M X 8\n", 9)         = 9
 0.000022    lseek(11, 0, SEEK_CUR)                             = 4673
 0.000038    write(11, "     o a c d t y = 0 2  ".., 104)       = 104
 0.000040    write(12, " 2 0 m N } 5 Y e 1\n", 10)              = 10
 0.000020    lseek(11, 0, SEEK_CUR)                             = 4777
 0.000037    write(11, "     k x s b b b f p = f".., 52)        = 52
 0.000038    write(12, " 2 0 m 7 } j Y q\n", 9)         = 9
 0.000028    pathconf("/tmp/", _PC_NAME_MAX)                    = 255
 0.000029    open("/tmp/.SHMLPESLD_rdb4_26_000000047334dc80", O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0666) Err#17 EEXIST
 1.000029    nanosleep(0xFFFFFFFF7FFF5FF0, 0xFFFFFFFF7FFF5FE0) = 0
 0.000038    open("/tmp/.SHMLPESLD_rdb4_26_000000047334dc80", O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0666) = 13
 0.000023    close(13)                                  = 0
 0.000026    open("/tmp/.SHMDPESLD_rdb4_26_000000047334dc80", O_RDONLY|O_NOFOLLOW|O_CLOEXEC) = 13
 0.000028    unlink("/tmp/.SHMLPESLD_rdb4_26_000000047334dc80") = 0
 0.000052    mmap(0x00000000, 528, PROT_READ|PROT_EXEC, MAP_PRIVATE, 13, 0) = 0xFFFFFFFF74700000
 0.000020    close(13)                                  = 0
 0.000023    lseek(11, 0, SEEK_CUR)                             = 4829
 0.000044    write(11, "\n * * *   2 0 2 1 - 0 5".., 38)        = 38
 0.000040    write(12, " - T + 7 } d b k t . c *".., 16)        = 16
 0.000020    lseek(11, 0, SEEK_CUR)                             = 4867
 0.000037    write(11, " W A I T   # 1 8 4 4 6 7".., 130)       = 130
 0.000037    write(12, " 8 4 j U 1 y 5 $ o 7 2 2".., 18)        = 18
 0.000020    lseek(11, 0, SEEK_CUR)                             = 4997
 0.000037    write(11, " E X E C   # 1 8 4 4 6 7".., 105)       = 105
 0.000037    write(12, " 8 0 j d 0 y 5 $ k x s t".., 20)        = 20
 0.000022    lseek(11, 0, SEEK_CUR)                             = 5102

As you can see it calls nanosleep in the line #11 and sleeps 1 sec, but more interesting a line before and a few after nanosleep: oracle tries to open (and fails “err17”) a file "/tmp/.SHMLPESLD_rdb4_XXX” , which means that it tries to open a pl/sql procedure compiled in native mode (pesld – Plsql code Execution Shared object manager native code Loader). I’ve asked to check compilation parameters of the procedure, and it was really compiled in native mode:

PLSQL_OPTIMIZE_LEVEL 2
PLSQL_CODE_TYPE NATIVE
PLSQL_DEBUG FALSE

So obvious workaround to recompile those procedures in the INTERPRETED mode fixed the issue.

UPDATE: Forgot to show what exactly is going on here: Finally I got normal stack:

              unix`_resume_from_idle+0x280
              genunix`cv_timedwait_sig_hires+0x1c4
              genunix`cv_waituntil_sig+0x90
              genunix`nanosleep+0xd4
              unix`syscall_trap+0x228

              libc.so.1`__nanosleep+0x4
              libc.so.1`sleep+0x14
              libc.so.1`__pos4obj_lock+0x68
              libc.so.1`shm_open+0x50
              oracle`pesld02_Map_Object+0xbc4
              oracle`penglur+0x1d18
              oracle`pfrins+0x8e4
              oracle`pfri7_inst_body_common+0x214
              oracle`pfri3_inst_body+0x18
              oracle`pfrrun+0x9e0
              oracle`plsql_run+0x22c
              oracle`peicnt+0xfc
              oracle`kkxexe+0x2f8
              oracle`opiexe+0x5784
              oracle`kpoal8+0x918
              oracle`opiodr+0x448
              oracle`ttcpip+0x430
              oracle`opitsk+0x83c
              oracle`opiino+0x39c
              oracle`opiodr+0x448
  ns                                                
           value  ------------- Distribution ------------- count    
       268435456 |                                         0        
       536870912 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 3        
      1073741824 |                                         0    

As you can see pesld02_Map_Object is trying to load (map) a user-defined function (native compilation) and calls shm_open which calls __pos4obj_lock and if we look at it, we can find why and when it calls sleep(which calls nanosleep internally):

int
__pos4obj_lock(const char *name, const char *ltype)
{
 char *dfile;
 int fd;
 int limit = 64;

 if ((dfile = __pos4obj_name(name, ltype)) == NULL) {
  return (-1);
 }

 while (limit-- > 0) {
  if ((fd = __open_nc(dfile, O_RDWR | O_CREAT | O_EXCL, 0666))
      < 0) {
   if (errno != EEXIST)
    break;
   (void) sleep(1);
   continue;
  }

  (void) __close_nc(fd);
  free(dfile);
  return (1);
 }

 free(dfile);
 return (-1);
}
Categories: Development

SQL*Plus tips #9: Reading traces and incident files

XTended Oracle SQL - Thu, 2021-05-27 19:57

@tracefile_read_last_by_mask filemask [regexp] [ignore_regexp]
– finds last trace by filemask and filters rows by regexp and filters out rows by ignore_regexp:

@ tracefile_by_mask.sql [mask] – finds and shows last 10 trace files by mask

@tracefile_current – shows a file name of the your session’s trace file
@tracefile_read_current – reads a trace file of the current session

@tracefile_last [n] – shows last N trace files (by default 10)

@tracefile_read filename – reads a specified trace file

@tracefile_read_full.sql <filename> – reads a specified trace file and shows it with additional columns: component_name, operation_name, file_name, function_name

@tracefile_spool <tracefilename> <dest_file> – saves tracefilename to dest_file on local machine:

Incidents, problems and incident files

@diag/problems_last.sql [N] – shows last N problems, N is optional, by default shows last 10 problems

@diag/incidents_last.sql [N] – shows last N incidents (by default 10)

@diag/incidents_by_problem.sql <problem_id> [N] – shows last N incidents by problem_id

@diag/adr_home_create.sql – creates a directory object for reading incident files:

@diag/incident_files_by_mask.sql filemask – shows incident file names by file mask

SQL> @diag/incident_files_by_mask.sql %incident%387.%

INCIDENT_ID   OWNER_ID      FLAGS CON_ID BFILE
----------- ---------- ---------- ------ ------------------------------------------------------------
      26387          1          1      3 <ADR_HOME>/incident/incdir_26387/ORA19_ora_2942_i26387.trc#0

1 row selected.

@diag/incident_files_by_id.sql incident_id – shows incident files by incident_id

@diag/incident_files_spool_by_id.sql incident_id – saves an incident file on local machine:

Categories: Development

Oracle diagnostic events — Cheat sheet

XTended Oracle SQL - Wed, 2021-05-19 20:13

Oracle diagnostic events is a great feature, but unfortunately poorly documented and nonintuitive, so it’s difficult to remember all events/actions/parameters and even read its internal documentation using oradebug. So I decided to compile its internal doc as a more convenient html-version (http://orasql.org/files/events/) and make a cheat sheet of some unknown or little-known use cases.

alter system set events 
   'kg_event[1476]
        {occurence: start_after 1, end_after 3}
            trace("stack is: %\n", shortstack())
            errorstack(2)
    ';
  1. kg_event[errno] – Kernel Generic event in library Generic for error number events, which instructs to trace ORA-errno errors;
  2. {occurence: start_after X, end_after Y} – is a filter, which instructs to skip X event checks and trace just Y times;
  3. trace(format, str1, str2, …, str15) – is a function from ACTIONS for printing into a trace file;
  4. shortstack() – is a function from ACTIONS , which returns a short call stack as a string;
  5. errorstack(level) – is a function from ACTIONS, which prints extended info (level: 0 – errorstack only, 1 – errorstack + call stack, 2 – as level 1 + processtate, 3 – as level 2 + context area). You can get more details with  PROCESSSTATE or SYSTEMSTATE. If you need just a call stack, you can use CALLSTACK(level) , with function arguments in case of level>1.
alter system set events 
    'trace[SQL_Compiler.* | SQL_Execution.*]
        [SQL: ...]
            {process: ospid = ...}
            {occurence:end_after 3}
                controlc_signal()';
  1. trace[component] – is the main diagnostic event, which allows to specify Components to trace. This command shows how to specify all child components of SQL_Compiler and SQL_Execution. 
  2. SQL[SQL: sqlid ] – is the only SCOPE in library RDBMS, which allows to trace specific SQL_ID and all its recursive calls (or for example, if you specify sql_id of a PL/SQL call, then all its internal queries will be traced too);
  3. {process: …} – is a filter, which allows to filter processes;
  4. controlc_signal – is an ACTION, that raises “ORA-01013: user requested cancel of current operation”, ie it’s like this session interrupted own call.

I’ve used this command recently when I had to help developers to find where some suboptimal and unnecessary queries, discovered during performance load testing, come from their code (huge webapp with hibernate).

alter system set events 
    'sql_trace {process: ospid = ...} 
     trace("sqlid(%): %\n", sqlid(), evfunc())
     ';

sql_trace – is just a good old trace event #10046, and this function forces oracle to print a function name and sql_id for each sql_trace event. Simple example:

enabling the event сначала выполняем запрос с настройками по умолчанию, а затем с _rowsource_statistics_sampfreq=1executing a query twice: with default parameters and with _rowsource_statistics_sampfreq=1 Разница в трейсе заметнаThe difference is noticeable: 12 vs 26 traced events

As you can see we get 26 “occurrences” in case of “_rowsource_statistics_sampfreq”=1 instead just 12 by default. More details about this here: https://alexanderanokhin.com/2012/12/24/timing-rowsource-statistics-part-2-overhead-and-inconsistent-time/

alter system set events 
    'wait_event["enq: TM - contention"]
        {wait: minwait=1000} 
            errorstack(1)
            trace("event=[%] sqlid=%, ela=% p1=% p2=% p3=%\n", 
                   evargs(5), sqlid(), evargn(1), evargn(2), evargn(3), evargn(4))
    ';
  1. wait_event[name] – event to control wait event post-wakeup actions, first argument is a wait event name, all wait event names and their parameters (P1,P2,P3) you can find in v$event_name:
select 
   wait_class,name
  ,parameter1,parameter2,parameter3
  ,display_name 
from v$event_name;
  1. {wait: … } – is a filter that allows to specify both the minimum wait time(ms) and parameters P1, P2, P3. For example, for this “TM-contention” wait event, P2 is “object #”, and we can specify {wait: minwait=1000; p2=12345}, ie only table TM-lock wait events on the table with object_id=12345 and longer than 1 sec;
  2. evargX() – functions from ACTIONs, which return event check arguments values, where 1st argument is an elapsed time(ms), 2nd-4th – parameters p1-p3, and 5th – wait event name. kg_event has own related functions – errargX().

or another example, when you need to find out which session variables have been changed: Let’s imagine someone forgot to specify nls parameters in to_number() and didn’t specify “on conversion error” clause, and some session sporadically get “ORA-01722: invalid number”:

-- original parameters:
SQL> alter session set nls_numeric_characters='.,';

Session altered.

-- all works fine...
SQL> select to_number('1,200.3','999g999d999') + 10 from dual;

TO_NUMBER('1,200.3','999G999D999')+10
-------------------------------------
                               1210.3

-- until they've been changed at some point:
SQL> alter session set nls_numeric_characters=".'";

Session altered.

-- obviously we get errors:
SQL> select to_number('1,200.3','999g999d999') + 10 from dual;
select to_number('1,200.3','999g999d999') + 10 from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

There is no special view for session parameters which are not a part of v$ses_optimizer_env, but we can easily get them using  MODIFIED_PARAMETERS():

alter system set events 
    'kg_event[1722] 
        {process: ospid=27556}
        {occurence:end_after 1}
            MODIFIED_PARAMETERS()';

And since we have v$diag_alert_ext for alert.log and v$diag_trace_file_contents for trace files, we can get them using this simple query:

select c.payload
from v$diag_trace_file_contents c
where 1=1
  and c.session_id   = &sid -- session sid 
  and c.serial#      = &serial -- session serial#
  and c.section_name = 'Error Stack' -- they will be in "Error Stack"
--  and c.payload like '%nls_numeric%' -- we can filter only specific nls parameters
  and c.timestamp>systimestamp-interval'15'minute -- last 15 minutes
;

-- Results:
DYNAMICALLY MODIFIED PARAMETERS:
  nls_language             = "AMERICAN"
  nls_territory            = "AMERICA"
  nls_sort                 = "BINARY"
  nls_date_language        = "AMERICAN"
  nls_date_format          = "yyyy-mm-dd hh24:mi:ss"
  nls_currency             = "$"
  nls_numeric_characters   = ".'"
  nls_iso_currency         = "AMERICA"
  nls_calendar             = "GREGORIAN"
  nls_time_format          = "hh24:mi:ssxff"
  nls_timestamp_format     = "yyyy-mm-dd hh24:mi:ssxff"
  nls_time_tz_format       = "hh24:mi:ssxff TZR"
  nls_timestamp_tz_format  = "yyyy-mm-dd hh24:mi:ssxff TZR"
  nls_dual_currency        = "$"
  nls_comp                 = "BINARY"
  local_listener           = ""

Update: I’ve just got a question in RuOUG:

We use “Extensible Optimizer Interface“, i.e. Oracle CBO calls such code internally, which fails periodically with “ORA-06550 PL/SQL compile error”. Can we specify both “kg_event” and “sql_id” so we could get those errors with details in alert.log

Of course, simple example:

alter system set events 
    'kg_event[6550]
        [sql: 5cmnpq8t0g7pd]
            {occurence: start_after 1, end_after 3}
                errorstack(3)
                incident(plserrors)
      ';

As you can see we use incident(label) here to generate an incident file (which you can easily pack and send to Oracle support or consulters) and kg_event[6550] to trace  “ORA-06550: line %, column %” errors.

let’s generate an error we can see that an incident file was successfully generated

PS, More details in Tanel Poder’s articles:
1. The full power of Oracle’s diagnostic events, part 1: Syntax for KSD debug event handling
2. The full power of Oracle’s diagnostic events, part 2: ORADEBUG DOC and 11g improvements
3. What Caused This Wait Event: Using Oracle’s wait_event[] tracing
4. http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc

Ora C functions on Frits Hoogland’s site: http://orafun.info/

HTML version of the internal oradebug/diagnostic events doc: http://orasql.org/files/events

Categories: Development

v$blog #funny #friday

XTended Oracle SQL - Sat, 2021-04-24 18:22
select title, short_url 
from v$blog 
where pubDate>=systimestamp - interval '5' month;
with v$blog as (
    select  
        title
       ,to_timestamp_tz(pubDate,'DY, dd mon yyyy hh24:mi:ss TZR') pubDate
       ,short_url
       ,description
    from xmltable(
            '/rss/channel/item'
            passing
                xmltype(httpuritype('http://orasql.org/feed/').getclob())
            columns
                 title       varchar2(70)  path 'title'
                ,pubDate     varchar2(40)   path 'pubDate'
                ,url         varchar2(128)  path 'link'
                ,short_url   varchar2(50)   path 'guid'
                ,description varchar2(500)  path 'fn:substring(./description,1,500)'
    ) 
)
select title, short_url from v$blog where pubDate>=systimestamp - interval '5' month;
Categories: Development

Murder in the Age of Enlightenment

Greg Pavlik - Fri, 2021-04-09 17:51

I had a few days of downtime to deal with some medical issues and turned to some short story collections to fill the time. My companions for a bit were Ryūnosuke Akutagawa and Anton Chekhov. I was quite delighted with a new translation of Akutagawa from Pushkin Press, Murder in the Age of Enlightenment. What sparse but sharp imagery - taken from Japanese history, European literature, Mahayana Buddhism, Christianity, Chinese writings - it was a bit of a smorgasbord. Akutagawa can be dark: his preoccupation with suicide in his writing no doubt reflected in his own suicide at age 35; I found his piece Madonna in Black on a peculiarly evil Maria-Kannon to be troubling, not least because I have a kind of devotional fascination with Maria-Kannon as our Lady of Mercy. But still Akutagawa is deeply humanistic and wide-ranging. The Karetnyk translation can be digested in an afternoon, no doubt time well spent.

My Chekhov choice was the recent translation of fifty-two stories by the unsurpassable translator pair Richard Pevear and Larissa Volokhonsky. These two are artists in their own right... I can't say enough good things about their portfolio of translations. They are so good I've been forced to re-read a number of novels just to digest their interpretative readings over the years.

But back to Akutagawa. Here I post a translation done under Creative Commons license* of the story The Spider's Thread. I don't know if this is a re-telling of Dostoevsky's "Tale of the Onion" in Karamazov for sure, though the story line is so close that I find it impossible to believe otherwise: Lord Buddha Shakyamuni simply replacing the Guardian Angel. Get the Pushkin Press book to read it in a slightly more refined form, but I found this a wonderful read as well:


ONE


One day, the Buddha was strolling alone along the edge of a lotus pond in Paradise. The blooming lotus flowers in the pond were each pure white like jewels, and the place was filled with the indescribably wondrous fragrance continually emitted from each flower’s golden center. It was just morning in Paradise.

After a time, the Buddha paused at the edge of the pond and from between the lotus leaves that covered it saw a glimpse of the state of things below. Now this celestial pond just happened to lie directly over Hell, and peering through that crystal-clear water was like looking through a magnifying glass at the River of Death and the Mountain of Needles and such.

The Buddha saw there, in the depths of Hell, a single man writhing along with the other sinners. This man was named Kandata, and he had been a notorious thief who had performed murder and arson and other acts of evil. In his past, however, he had performed just one good deed: one day, when walking through the deep forest, he saw a spider crawling along the road. At first he raised his foot to crush it, but suddenly he changed his mind and stopped, saying, “No, small though it may be, a spider, too, has life. It would be a pity to meaninglessly end it,” and so did not kill it.

Looking down upon the captives in Hell the Buddha recalled this kind act that Kandata had performed, and thought to use his good deed as a way to save him from his fate. Looking aside, there on a jade-colored lotus leaf he saw a single spider, spinning out a web of silver thread. The Buddha carefully took the spider’s thread into his hand, and lowered it straight down between the jewel-like white lotuses into the depths of Hell.


TWO


Kandata was floating and sinking along with the other sinners in the Lake of Blood at the bottom of Hell. It was pitch black no matter which way he looked, and the occasional glimpse of light that he would see in the darkness would turn out to be just the glint of the terrible Mountain of Needles. How lonely he must have felt! All about him was the silence of the grave, the only occasional sound being a faint sigh from one of the damned. Those who were so evil as to be sent to this place were tired by its various torments, and left without even the strength to cry out. Even the great thief Kandata could only squirm like a dying frog as he choked in the Lake of Blood.

But one day, raising up his head and glancing at the sky above the lake, in the empty darkness Kandata saw a silver spider’s thread being lowered from the ceiling so far, far away. The thread seemed almost afraid to be seen, emitting a frail, constant light as it came down to just above Kandata’s head. Seeing this, Kandata couldn’t help but clap his hands in joy. If he were to cling to this thread and climb up it, he may be able to climb out of Hell! Perhaps he could even climb all the way to Paradise! Then he would never be chased up the Mountain of Needles, nor drowned in the Lake of Blood again.

Thinking so, he firmly grasped the spider’s thread with both hands and began to climb the thread, higher and higher. Having once been a great thief, he was used to tasks such as this. But the distance between Hell and Paradise is tens of thousands of miles, and so it would seem that no amount of effort would make this an easy journey. After climbing for some time Kandata tired, and couldn’t climb a bit higher. Having no other recourse, he hung there from the thread, resting, and while doing so looked down below.

He saw that he had made a good deal of progress. The Lake of Blood that he had been trapped in was now hidden in the dark below, and he had even climbed higher than the dimly glowing Mountain of Needles. If he could keep up this pace, perhaps he could escape from Hell after all. Kandata grasped the thread with both hands, and laughingly spoke in a voice that he hadn’t used in the many years since he had come here, “I’ve done it! I’ve done it!”

Looking down, however, what did he see but an endless queue of sinners, intently following him up the thread like a line of ants! Seeing this, surprise and fear kept Kandata hanging there for a time with mouth open and eyes blinking like a fool. How could this slender spider’s web, which should break even under just his weight, support the weight of all these other people? If the thread were to snap, all of his effort would be wasted and he would fall back into Hell with the others! That just would not do. But even as he thought these thoughts, hundreds more, thousands more of the damned came crawling up from the Lake of Blood, forming a line and scurrying up the thread. If he didn’t do something fast, surely the thread would snap in the middle and he would fall back down.

Kandata shouted out, “Hey! You sinners! This thread is mine! Who said you could climb up it? Get off! Get off!”

Though the thread had been fine until just then, with these words it snapped with a twang right where Kandata held it. Poor Kandata fell headfirst through the air, spinning like a top, right down through the darkness. The severed end of the silver thread hung there, suspended from heaven, shining with its pale light in that moonless, starless sky.


THREE


The Buddha stood in Paradise at the edge of the lotus pond, silently watching these events. After Kandata sank like a stone to the bottom of the Lake of Blood, he continued his stroll with a sad face. He must have been surprised that even after such severe punishment Kandata’s lack of compassion would lead him right back into Hell.

Yet the lotus blossoms in the lotus ponds of Paradise care nothing about such matters. Their jewel-like white flowers waved about the feet of the Buddha, and each flower’s golden center continuously filled the place with their indescribably wondrous fragrance. It was almost noon in Paradise.


(16 April 1918)

* Translation http://tonygonz.blogspot.com/2006/05/spiders-thread-akutagawa-ryunosuke.html

Silence in 4 Movements

Greg Pavlik - Wed, 2021-03-24 12:05

"What is the relation of [contemplation] to action? Simply this. He who attempts to act and do things for others or for the world without deepening his own self-understanding, freedom, integrity and capacity to love will not have anything to give others. He will communicate to them nothing but the contagion of his own obsessions, his aggressiveness, his ego-centered ambitions, his delusions about ends and means, his doctrinaire prejudices and ideas."
—Thomas Merton


"Those who know do not talk.

Those who talk do not know.

~

Stop talking,

block off your senses,

meditate in silence,

release your worries,

blunt your sharpness,

untie your knots,

soften your glare,

harmonise your inner light

and unite the world into one whole!

This is the primal union or secret embrace."

Tao Te Ching 56

"Make stillness your criterion for testing the value of everything, and choose always what contributes to it."

-Evagrius Ponticus




"Silence and Beauty - Eco" (Minerals and gesso on canvas, 2016) by contemporary Japanese American artist Fujimura Makoto (藤村真, born in Boston, Massachusetts in 1960). Abstract expressionist piece done with nihonga techniques. Picture found online.

I read Fujimura's book Silence and Beauty last year, which was inspired by the Endo Shusaku's 20th century novel Silence (itself adapted to film by the great Martin Scorsese). Fujimura reflects on his relationship with Japanese culture in the light of Shusaku's work, the Hiroshima bombing, and his own experience as a Japanese-American: most importantly how it has manifested in his work as an artist. Shusaku's work itself dwells on "silence" as absence. But I think this painting shows absence-as-presence: something is there, something beautiful, but its not clear what or even why - in fact that presence changes over time for the viewer, depending on vantage point or even focus.

Version Control for Oracle Webinar Video

Gerger Consulting - Wed, 2021-02-24 05:34

 

On February 16th, we hosted a webinar about Gitora 5. Many thanks to everyone who attended. The recording of the webinar is below.

There were a few questions we received several times during the webinar. We'd like to reiterate our answers below:

How does Gitora work with GitLab, GitHub or similar services...?
Gitora repos work with these services just like any other local Git repo does. From the point of GitLab, GitHub or similar services, Gitora repos are just local repos that you can push to these services.

How can I create a merge/pull request with Gitora?
Merge/Pull request is not a Git feature. It is a GitHub/GitLab feature. You can continue creating merge/pull requests on these services using the same workflow you use for any other local repo. To create a merge/pull request for a Gitora repo, first push your repo to GitHub/GitLab using the Gitora web app (or use Git directly). Next, go to your GitHub/GitLab account and create your merge/pull request.

How can I work on two different projects with different deadlines in the same development database and send only code related to one project to the test database?
You can't. Gitora cannot do magic. :-) There will never be a tool that will help you to do this because it is impossible. What Gitora empowers you to do is to have multiple databases per project. Then you can work on the same logical objects in different databases and use Gitora to merge code bases.
You can watch a full workflow and detailed explanation of this process at this link.

Categories: Development

Source Control Management for the Oracle Database

Gerger Consulting - Thu, 2021-02-11 03:28

Managing the source code of objects in the Oracle database and moving them between databases has been a problem for many years.

DBA's and database developers did not enjoy the benefits of prominent source control software such as Git and have been largely left behind.

Gitora is a unique tool that bridges the Oracle database and Git and enables DBA's and database developers to benefit from Git's capabilities in managing database objects.

On February 16th we are hosting a webinar to show you how you can use Gitora to manage your database objects.

In this webinar we'll cover the following topics:

- How to use Git for database object DDL statements
- How to use Gitora to move code between databases
- How to implement modern development workflows using Gitora
- How to use Pluggable Databases for a more productive development environment

You can sign up at this link.

Categories: Development

Gitora 5 Source Control for Oracle is Out

Gerger Consulting - Wed, 2021-01-27 05:14

We are very excited to announce that Gitora 5 is now available for download.

Gitora 5 is a huge step forward for version management of database objects.

Gitora 5 has many new features. Below are just six of our favorites:

  • A Brand New User Interface and Application
  • One Gitora Installation to Manage Any Number of Databases
  • Gitora Now Manages Tables
  • No-login development experience
  • A Comprehensive Developer Privileges Management Module
  • Full API To Completely Automate Your Workflow

Let’s go over these new features briefly:

A Brand New User Interface and Application

We redesigned every screen in Gitora from the ground up. The new design gives Gitora 5 a beautiful modern look with large components, easy to read, fresh colors and updated icons. 

We also rebuilt the application with Java. This reduced Gitora’s database footprint by more than 95% making it much easier to install and manage.

One Gitora Installation to Manage Any Number of Databases

This was probably one of the most requested features. Previously, if you wanted to manage a database with Gitora, you needed a new Gitora installation. This is no longer the case.

Thanks to Gitora 5’s new architecture, users can now manage any number of databases with Gitora 5 from a single application.

This makes moving code and switching between databases much easier. Maintenance and upgrade tasks are also much simpler because there is only one application to manage.

Moreover, we made installing Gitora much easier. No more running hundreds of database scripts or changing database settings. Once you create a database user for Gitora, the application will automatically install its database objects.

Gitora Now Manages Tables

Tables are now first class citizens in Gitora. You can manage every table’s DDL in Git repos along with its constraints and indexes, in its own file with its complete history, automatically.

No-login Development Experience

Previously, signing in to Gitora required an extra step for database developers. Every time they opened a database session, they had to execute one line of code to sign in to Gitora. Gitora 5 can be configured to recognize selected database users as valid Gitora users. This way, Gitora 5 becomes completely transparent during regular development activities.

A Comprehensive Developer Privileges Management Module

Gitora 5 Enterprise comes with a sophisticated authorization module with which you can manage who can edit which database object and perform which Git operation. You can define privileges for actions such as check out, commit, pull, push etc… at database level and at repo level. You can even restrict access to certain objects to specific developers.

Full API To Completely Automate Your Workflow

Last but not least, Gitora 5 Enterprise comes with a set of API’s so that you can fully automate your workflow. The API’s are easy to use and support the restrictions you specify in the authorization module.

These are our top six features but there are many more. Here is just a few of them:

  • A new and improved diff screen
  • A new way to track every DDL you execute in the database
  • Table DDL generation that is master-detail relationship aware
  • Support for sequences, materialized views

Gitora 5 comes with the features you are already familiar with:

  • Create Git repos with any of your database code objects such as Packages, Views, Types, Triggers etc…
  • Gitora doesn’t miss a change. Capture every change in these objects automatically and commit them to Git with a single click.
  • Perform Git operations such as reset, branch, merge, pull etc.. These operations will update your database objects automatically.
  • Push your repos to any cloud service such as GitHub, GitLab etc…

Gitora is a unique tool that integrates Git to the Oracle Database. It enables you to use Git just like a Java, JavaScript or C# developer.  

Gitora 5 is by far our biggest and most important release since releasing the first version of Gitora in 2015. Gitora 5 is an indispensable tool to improve developer productivity, implement modern development workflows and cut delivery times significantly.

Try Gitora 5 today.

Categories: Development

pySyncOracleStandby – Simple sync service for Oracle manual standby

XTended Oracle SQL - Thu, 2021-01-07 05:55

I created this simple service a couple of years ago. It’s pretty simple, small and intuitive Python app, so you can easily modify it to suit your own needs and run on any platform: https://github.com/xtender/pySync

Categories: Development

Format SQL or PL/SQL directly in Oracle database

XTended Oracle SQL - Tue, 2020-12-22 16:59

Obviously we can format/beautify SQL or PL/SQL code using external tools, but sometimes it would be more convenient to format it directly in database, for example if we want to see few different sql_text’s from v$sqlarea. And thanks to Oracle SqlCL and Oracle SQL Developer, we can easily use oracle.dbtools.app.Format function from their Java library dbtools-common.jar, so if you use SqlCL or SQL Developer, you can use the same formatting options.

1. load appropriate java library into Oracle

You may have already installed SQLDeveloper or SqlCL on your database server, just check $ORACLE_HOME/sqldeveloper or $ORACLE_HOME/sqcl directories. If – not, you need to download appropriate SqlCL version that matches your java version in Oracle. For 12.2 – 19.8 you can download latest SqlCL 20.3. In fact we need just dbtools-common.jar from lib directory. I put it into $ORACLE_HOME/sqlcl/lib directory on the server and load it using loadjava:

loadjava -u login/pass@pdb1 $ORACLE_HOME/sqlcl/lib/dbtools-common.jar

Just change your login credentials and dbname.

2. set java permissions

Then you need to grant required Java permissions:

exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' );
exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' );
exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );
3. create java functions

As you can see, in the code below you can specify own formatting options (function getFormat()). For example, formatting options from Trivadis: https://github.com/Trivadis/plsql-formatter-settings/blob/main/settings/sql_developer/trivadis_advanced_format.xml

You can copy the code below, but it would be better to take latest code from https://github.com/xtender/xt_scripts/tree/master/extra/SQLFormatter

CREATE or replace AND COMPILE JAVA SOURCE NAMED SQLFormatter AS
/* Imports */
import oracle.dbtools.app.Format;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import java.io.StringWriter;
import java.io.PrintWriter;


public class SQLFormatter {

    private static String getStackTrace(Exception e) {
       StringWriter writer = new StringWriter();
       PrintWriter printWriter = new PrintWriter( writer );
       e.printStackTrace( printWriter );
       printWriter.flush();

       return writer.toString();
    }

    public static Format getFormat() {
        oracle.dbtools.app.Format format = new oracle.dbtools.app.Format();
        
        format.options.put("singleLineComments", Format.InlineComments.CommentsUnchanged);
        format.options.put("kwCase", Format.Case.UPPER);
        format.options.put("idCase", Format.Case.NoCaseChange);                             // default: Format.Case.lower
        format.options.put("adjustCaseOnly", false);                                        // default: false (set true to skip formatting)
        format.options.put("formatThreshold", 1);                                           // default: 1 (disables deprecated post-processing logic)
        // Alignment
        format.options.put("alignTabColAliases", false);                                    // default: true
        format.options.put("alignTypeDecl", true);
        format.options.put("alignNamedArgs", true);
        format.options.put("alignEquality", false);
        format.options.put("alignAssignments", true);                                       // default: false
        format.options.put("alignRight", false);                                            // default: false
        // Indentation
        format.options.put("identSpaces", 3);                                               // default: 4
        format.options.put("useTab", false);
        // Line Breaks
        format.options.put("breaksComma", Format.Breaks.Before);                            // default: Format.Breaks.After
        format.options.put("breaksProcArgs", false);
        format.options.put("breaksConcat", Format.Breaks.Before);
        format.options.put("breaksAroundLogicalConjunctions", Format.Breaks.Before);
        format.options.put("breaksAfterSelect", true);                                      // default: true
        format.options.put("commasPerLine", 1);                                             // default: 5
        format.options.put("breakOnSubqueries", true);
        format.options.put("breakAnsiiJoin", true);                                         // default: false
        format.options.put("breakParenCondition", true);                                    // default: false
        format.options.put("maxCharLineSize", 120);                                         // default: 128
        format.options.put("forceLinebreaksBeforeComment", false);                          // default: false
        format.options.put("extraLinesAfterSignificantStatements", Format.BreaksX2.Keep);   // default: Format.BreaksX2.X2
        format.options.put("flowControl", Format.FlowControl.IndentedActions);
        // White Space
        format.options.put("spaceAroundOperators", true);
        format.options.put("spaceAfterCommas", true);
        format.options.put("spaceAroundBrackets", Format.Space.Default);
        //format.options.put("formatProgramURL", "default");
        
        return format;
    }
    
  public static String format(String str) 
  {
    String res;
    try {
       //res = new Format().format(str);
       Format f = SQLFormatter.getFormat();
       res = f.format(str);
       }
    catch (Exception e){
       res = "Error: " + e.getMessage() + " [ " + SQLFormatter.getStackTrace(e) + " ]";
    }
    return res;
  }

  public static CLOB formatClob(oracle.sql.CLOB clob) 
  throws SQLException
  {
    String str = clob.getSubString(1, (int) clob.length());
    String res = SQLFormatter.format(str);
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
    CLOB resClob = CLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
    resClob.setString(1L, res);
    
    return resClob;
  }
}
/
4. Create PL/SQL package for it
create or replace package SQLFormatter as

  FUNCTION Format(str in varchar2) RETURN VARCHAR2
  AS LANGUAGE JAVA NAME 'SQLFormatter.format(java.lang.String) return java.lang.String';

  FUNCTION FormatClob(str in clob) RETURN CLOB
  AS LANGUAGE JAVA NAME 'SQLFormatter.formatClob(oracle.sql.CLOB) return oracle.sql.CLOB';
  
end;
/

Now we can test it:

SQL> select SQLFormatter.format('select 1 a,2 /*123 */ b,3 c, d from dual, dual d2') qtext from dual;

QTEXT
----------------------------------------------------------------------------------------------------
SELECT
   1 a
 , 2 /*123 */ b
 , 3 c
 , d
FROM
   dual
 , dual d2


SQL> select sql_id,SQLFormatter.format(sql_text) qtext from v$sqlarea where rownum<3 and sql_text is not null;

SQL_ID        QTEXT
------------- -------------------------------------
fbbm59qban13m DELETE FROM idl_sb4$
              WHERE
                    obj# = :1
                 AND part = :2
                 AND version <> :3

1gfaj4z5hn1kf DELETE FROM dependency$
              WHERE
                 d_obj# = :1

Categories: Development

Funny friday Oracle SQL quiz: query running N seconds

XTended Oracle SQL - Fri, 2020-12-11 06:37

Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.

My solution

with v(start_hsecs, delta, flag) as (
  select
    hsecs as start_hsecs, 
    0 as delta,
    1 as flag
  from v$timer
  union all
  select
    v.start_hsecs,
    (t.hsecs-v.start_hsecs)/100 as delta,
    case when (t.hsecs-v.start_hsecs)/100 &gt; :N /* seconds */ then v.flag*-1 else v.flag+1 end as flag
  from v, v$timer t
  where v.flag&gt;0
    and t.hsecs&gt;=v.start_hsecs
)
select delta
from v
where flag+rownum&lt;=0;

[collapse]
SQL&gt; var N number
SQL&gt; exec :N := 3 /* seconds */;

PL/SQL procedure successfully completed.

SQL&gt; select...

     DELTA
----------
      3.01

1 row selected.

Elapsed: 00:00:03.01

Another my solution using sys.standard.current_timestamp, so some internal pl/sql…:

select count(*) from dual 
connect by sys.standard.current_timestamp - current_timestamp <= interval'3'second;
Categories: Development

Pretzel Logic

Greg Pavlik - Sat, 2020-10-17 18:12

 

Getting Additional Values from a Select-Single Component

Shay Shmeltzer - Wed, 2020-09-30 18:11

With newer versions of Oracle JET we are recommending using the new select-single instead of the old (and deprecated) select-one component. In a past blog entry about tips for using lists in Visual Builder, I showed how to get additional values from the record you selected in a select single. I figured I owe you an update that shows how to achieve the same thing with a select-single. 

When you bind a select-single to a set of options, beyond selecting the value and the label, you are also able to select additional fields that will construct the row of data. In the demo below for example I use id for the value, name for the label, and then add salary as the additional field. The select-single expose an event for value-item change. The payload for this event include not just the key selected, but also "data" - which has all the fields in the record you chose.

You can then simply get their value in the action chain using something like $action.variables.data.fieldName - so in the video below I'm using the $variables.data.salary to get the value of the salary for the selected employee and then assign it to another variable on the page.

Categories: Development

Multi-row Selection with JET 9 in Visual Builder

Shay Shmeltzer - Thu, 2020-09-24 21:29

With the new support for Oracle JET 9 in Oracle Visual Builder, it's much simpler to create a multi-row selection enabled table that with checkboxes for row selection. Below is a quick video showing how this is done. This is much simpler compared to the approach you had to take before.

The table automatically adds a selection checkbox column when you set the selection-mode.row="multiple" attribute. There is an on-selected-changed event on the table - and you can attach an action chain to it. The event gets an array of the selected row keys as an input parameter. In the video, I'm simply looping over this array and show a notification with the row key referring to them with the expression - [[ $variables.keys[$current.index] ]]. You can of course use this array for any follow up steps you want to do on the rows.

Note that this will only work if your app is using the Redwood theme (the default for new apps in VB). If your app is still using an Alta based theme you won't see the checkboxes, you'll need to switch your app to Redwood to get them.

Categories: Development

The Island

Greg Pavlik - Tue, 2020-09-15 23:19

What is guilt? Who is guilty? Is redemption possible? What is sanity? Do persons have a telos, a destiny, both or neither? Ostrov (The Island) asks and answers all these questions and more.

A film that improbably remains one of the best of this century: "reads" like a 19th century Russian novel; the bleakly stunning visual setting is worth the time to watch alone.



Simple function returning Parallel slave info

XTended Oracle SQL - Tue, 2020-09-15 07:38

You can add also any information from v$rtsm_sql_plan_monitor if needed

create or replace function px_session_info return varchar2 parallel_enable as
   vSID int;
   res varchar2(30);
begin
   vSID:=userenv('sid');
   select 
           to_char(s.server_group,'fm000')
    ||'-'||to_char(s.server_set,'fm0000')
    ||'-'||to_char(s.server#,'fm0000')
    ||'('||s.sid||','||s.degree||'/'||s.req_degree||')'
    into res
   from v$px_session s 
   where s.sid=vSID;
   return res;
exception when no_data_found then
   return 'no_parallel';
end;
/

Simple example:

select--+ parallel
  px_session_info, count(*)
from sys.obj$
group by px_session_info
/
PX_SESSION_INFO           COUNT(*)
------------------------  --------
001-0002-0001(630,2/2)     38298
001-0002-0002(743,2/2)     34706
Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development