Video : AVG, MEDIAN, MIN and MAX : Problem Solving using Analytic Functions

In today’s video we take a quick look at the AVG, MEDIAN, MIN and MAX aggregate and analytic functions.

You can get more information on these and more analytic functions here.

The star of today’s video is Sten Vesterli, in one of the rare moments at OpenWorld when he’s not in a wetsuit. 🙂

Cheers

(more...)

(In his best Arnie voice) I’M BACK!!!

Yes, after just under 7 months leave without pay due to the chronic pain issues you may recall I was suffering from last year, I am finally pain-free (relatively) and starting back at work in a graduated fashion. Last week, I only worked two days (most of which was deleting email that accumulated while I was gone – around 95,400 of them!). Today, I hope to work three days (if my body holds up (more...)

How Delete Statement Works

  • When Oracle receives sql/Delete query, it requires to run some pre-tasks before actually being able to really run the query.
  • During parsing, Database validate the syntax of the statement whether the query is valid or not.
  • Database validate the semantic of the statement. It checks whether a statement is meaningful or not.
  • If syntax/Semantic check pass, then server process will continue execution of the query.The server process will go to the library cache.In (more...)

How Update Statement Works

  • When Oracle receives sql/update query, it requires to run some pre-tasks before actually being able to really run the query.
  • During parsing, Database validate the syntax of the statement whether the query is valid or not.
  • Database validate the semantic of the statement. It checks whether a statement is meaningful or not.
  • If syntax/Semantic check pass, then server process will continue execution of the query.The server process will go to the library cache.In (more...)

Oracle database 19c step by step installation guide on oracle linux 7.6

Oracle database 19c installation document

Oracle Database 19c step by step installation guide on Oracle Linux 7.6

How to upload alert in r12

FNDLOAD Upload

FNDLOAD apps/password 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_Probation_period_alert15days.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXACE Alert_name=”Probation Period Alert 15 Days”

FNDLOAD Download

FNDLOAD apps/password 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_Probation_period_alert15days.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXACE Alert_name=”Probation Period Alert 15 Days”

How to calculate archivelog growth per day

archivelog size each day

select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc(COMPLETION_TIME) order by 1;

archivelog size each hour

alter session set nls_date_format = ‘YYYY-MM-DD HH24′;

select trunc(COMPLETION_TIME,’HH24′) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,’HH24’) order by 1;

Drop all synonym from owner

Drop all synonym from owner

set head off
set newpage none
set pagesize 9999

spool drop_synonym.sql

select ‘drop synonym ‘|| OWNER ||’.’ ||OBJECT_NAME || ‘;’ from dba_objects where OWNER =’OWNER’ and OBJECT_TYPE =’SYNONYM’;

spool off

Databaselink extract

Database link extract

 

SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,a.db_link,a.owner) FROM dba_db_links a;

 

 

compile jsp in r12

Stop application service

cd $FND_TOP
[appltrng@ebsdev bin]$ perl ojspCompile.pl –compile –flush -p 2

we need to check compiled successfully

start application service

NumPy in a Nutshell

Hello and welcome back. I have started a new category in my blog about Python. The purpose of this post is to go through NumPy library. I will be using Jupyter for the demo but will provide the py file if you prefer to run it in PyCharm for example. NumPy is a core Python Linear Algebra library for Data Science used for faster array processing than the native Python lists with a bunch of (more...)

VMware free self paced e-learning course

https://mylearn.vmware.com/mgrReg/plan.cfm?plan=33611&src=so_5a314d05ddb83&cid=70134000001SkJd

Getting rid of annoying, repetitive messages in /var/log/messages

The primary source of information regarding any change or issue on a linux is the /var/log/messages file. I am often annoyed when a linux system is setup in such a way that certain messages are written to syslog with a high frequency swamping the messages file with information that is not important. The reason for my annoyance is that this makes it very hard to actually spot important information because you have to skip through (more...)

DB links

A recent thread on the ODC SQL & PL/SQL forum raised the question of how data moves across a database link – is it row by row or array based (or other)? It’s a good question as it’s fairly common knowledge that distributed queries can be very much slower than the same query when executed on an equivalent set of local tables, so it’s worth having a little poke around to see if there’s anything (more...)

Same dog, different leash – functions in SQL

Let’s start with this somewhat odd looking result. I have an inline function that returns a random number between 0 and 20, and I call that for each row in ALL_OBJECTS and then I extract just those rows for which the generated random number is 10. Seems simple enough….but why do I get results for which the value of the second column is most certainly not 10?


SQL> with
  2    function
  3      getrand(pval in  (more...)

Power BI Reporting with Oracle Essbase

This last week, I presented a few sessions at ODTUG’s KSCOPE 2019 conference in Seattle.  One of these sessions was with my wonderful co-presenter and Oracle Application Cloud expert, Opal Alapat.  I connected with her when the call for papers opened and asked her if there would be interest in doing a session with Power BI connect to the Essbase in the Oracle Applications cloud, (OAC).  There was no desire to do a bake-off or (more...)

KScope19 – It’s a Wrap

This year I attended my second KScope ever. My first one was 2 years ago in San Antonio (I wrote about it here) and I missed last year’s one. In 2019, ODTUG decided to have the conference in Seattle, which is so cool for me as it’s just across the border. I actually ended up … Continue reading "KScope19 – It’s a Wrap"

Enterprise Manager Cloud Control 13.3 Silent Upgrade

A few days ago I put out a post called Enterprise Manager Cloud Control 13.3 Vagrant Build. In a comment on that post Dinesh said,

“Would like to see the silent upgrade from oem12c to oem13c upgrade post from you”

I normally try to keep on top of upgrades, so I’ve never done a jump bigger than one version, but I was checking through the documentation, and assuming it’s a supported start version, there (more...)

opt_estimate 4

In the previous article in this series on the opt_estimate() hint I mentioned the “query_block” option for the hint. If you can identify a specify query block that becomes an “outline_leaf” in an execution plan (perhaps because you’ve deliberately given an query block name to an inline subquery and applied the no_merge() hint to it) then you can use the opt_estimate() hint to tell the optimizer how many rows will be produced by that query (more...)

A change of career after 29 years working with Oracle databases

I have worked with Oracle since the early 90’s. I did work on V6 but I do not remember that much about it. V7.3 was were I had a big learning curve. This book was the mainstay of my life at that time.

In recent years I have diversified into managing teams across a number of areas including SQLServer, Middleware (ODI, SOA, JMS, Weblogic), System Management and others. I have been quite involved in (more...)