LOBs from afar

This has always been a nuisance.  There you are – getting all the bells and whistles with LOBs…until a database link enters the room Smile


--
-- Database: DB11
--
SQL> create table t ( id int, c clob );

Table created.

SQL> insert into t values (1,rpad('x',32000,'x'));

1 row created.

SQL> select * 
  2  from   t;

        ID C
---------- --------------------------------------------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

--
-- Database: anywhere except DB11
--
SQL> select * 
  2  from    (more...)

SCOTT/TIGER for SAP Hana

SAP Hana is becoming increasingly popular these days. It comes with its own demo schema, STUDENT. However, being an old Oracle hack, I ported the SCOTT schema from Oracle to SAP Hana. Every Oracle DBA has a bunch of queries against EMP and DEPT tables which he or she may test on the SAP Hana database. In case you’re interested, the export file is available here:

http://mgogala.byethost5.com/scott_hana.tgz

 


Arizona Oracle User Group meeting Thursday

The Arizona Oracle User Group (AZORA) is meeting this Thursday, November 16th, 2017 from 12:30 to 3:30 pm Arizona time.

Here is the link to the Meetup:

So good … it will scare your socks off

One of the big releases of 2017 is on our doorstep.  Watch the trailer here


That’s How We’ve Always Done It Before

Thanksgiving is coming up and to this day, I still remembered anecdote from former manager.

Daughter is helping mother prepare for Thanksgiving dinner which they do together for many years.

This year, daughter decided to ask, “Why do we cut off the turkey legs and wings before putting it the oven?”

Mom responded, “That’s how we’ve always done it before. Let’s call grandma and find out.”

On the phone with grandma and grandma’s response (more...)

Finding the trace files

In Oracle versions before 12c, it was necessary to resort to complex magic to find out the location of the process trace file. One of the most famous recipes is probably the one invented by René Nyffenegger and available here:

http://www.adp-gmbh.ch/ora/misc/find_trace_file.html

Another trick included utilizing ORADEBUG command “tracefile_name” which would return trace file name.

However, in Oracle 12c the things have changed. Trace file is now available as a column in the V$PROCESS (more...)

Little things worth knowing: scheduler jobs and Instance Caging

While updating material for a training class about Database Resource Management I briefly wondered if Instance Caging should apply for scheduler jobs as well. The obvious answer is “yes”, but I wanted to find proof. I hope I did in the end, and wanted to share how I got there.

The test environment

My system is based on the shiny new AMD Ryzen 7 1700X Eight-Core Processor, and it shows as 1s8c16t. I really like (more...)

Buffer cache hit ratio–blast from the past

I was perusing some old content during a hard drive “spring clean” the other day, and I found an old gem from way back in 2001.  A time when the database community were trying to dispel the myth that all database performance issues could be tracked back to,  and solved via, the database buffer cache hit ratio.  Thankfully, much of that folklore has now passed into the realm of fiction, but I remember (more...)

SQL Profile to fix slow inserts

I was on call Sunday and got paged about a job that normally runs for 10 to 15 minutes but had run for 5 hours already. I used the coe_xfr_sql_profile.sql script from SQLT to speed up an insert statement that was part of the job. We restarted the job and it completed in a few minutes.

I have written a number of posts about the use of coe_xfr_sql_profile.sql. Sunday’s issue was most like the post (more...)

UKOUG is coming

Yes it is just a few more weeks until the UKOUG conference swings by.

This has been one of my favourite conferences for years – dating back to my first one in 2002 !!.  You can see from the picture at the tail of this post – whilst times have changed in those 15 years, the basic tenets of community, networking and technical content remain unchanged to this day.

The AskTOM team will all be (more...)

GoldenGate Naming Convention P03

GoldenGate Naming Convention P01
GoldenGate Naming Convention P02

Here I provide an example for how I would implement 3-way replication.
I used capitalization for some clarity in trail – not tested yet.

N-way Replication calculations:
Primary Extract for each silo: 1
Pump Extract for each silo: N-1 
Replicats for each silo: N-1 
Total proceses for each silo: 2N-1

++++++++++

3-way Replication:
Primary Extract for each silo: 1
Pump Extract for each silo: 3-1=2
Replicats for  (more...)

IP CIDR rules and address ranges

I always forget IP address range coverage rules and forget where to look.

It’s the wiki!

and for good reference here is the table:

https://en.wikipedia.org/wiki/Classless_Inter-Domain_Routing

 

Screen Shot 2017-11-03 at 11.28.31 AM

ORA-15040 ORA-15042 with EXTERNAL redundancy Diskgroup

A colleague was working on an ASM issue (Standalone one, Version 11.2.0.3 on AIX) at one of the customer sites. Later on, I also joined him. The issue was that the customer added few news disks to an existing diskgroup. Everything went well and the rebalance kicked in. After some time, something happened and all of a sudden the diskgroup was dismounted. While trying the mount the diskgroup again, it was giving

ORA-15032:  (more...)

Two Talks Accepted for RMOUG Training Days

I got two talks accepted for RMOUG Training Days in February. I mentioned these two titles in a earlier post:

  • Python for the Oracle DBA
  • Toastmasters for the Oracle DBA

These two talks are about topics that interest me so I am glad that RMOUG thinks that they are valuable to the conference attendees.

I plan to do the two talks for my DBA coworkers and shorter versions at Toastmasters so I should get some constructive (more...)

Parsing freeform data in flat files

SQL loader is a very cool utility that has existed for a long time within Oracle to load flat files into the database. However sometimes people find the control file syntax quite cryptic, and when it comes to passing very complicated structures, this can mean control files which are hard to maintain. For me the best solution here is to use an external table. That way we can combine the power of the SQL Loader (more...)

“Oh…another language is too hard”

We had a request on AskTOM a few days ago asking for an implementation of the XIRR function in PL/SQL.

I didn’t really know much about what that function was, or what it did, but a quick web search yielded plenty of examples in Excel, where it is a pre-delivered function, as described here:

“Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.”

That explains (more...)

Submitted two talks to Collaborate 18

I submitted my two talks to Collaborate 18 through IOUG. These are the same two that I submitted to RMOUG Training Days. Hopefully one of the talks will get accepted at one of the two conferences but I don’t know. The nice thing is that they are both professional development talks and a user group meeting might be more open to that sort of talk than a vendor (i.e. Oracle OpenWorld) conference. But, there (more...)

Why being wrong can be awesome

OK, Now that I’ve started the post with a nice click-bait heading, let’s get down to the business of being wrong. Smile

I did a lot of conference presentations last year, and the great thing about that for me was that I got to meet a lot of new people in the Oracle community in the Developer and DBA space. One of the questions that came up over and over again was about putting one’s knowledge (more...)

Python sortedcontainers has me thinking

I was looking at the Python sortedcontainers package and it got me thinking. It is a long convoluted story and I am not sure that I can explain it clearly in a short blog post. I tried to explain all this to my wife in the last few minutes as we were driving up to a friend’s house last night and I’m sure it was confusing the way I explained it. But, I’m hoping that (more...)

Best method for tuning sub-optimal execution plans

How do you determine if the Oracle SQL optimizer has created a sub-optimal execution plan? re-run statistics and see what happens? wait for Oracle to fin other execution plans? What if neither method is helping? Do you read the execution plan? What do you look at? Differences in actual vs estimated? How successful is that? Look for full table scans?  Do you look at the 10053 trace? How much time and effort does that take?  (more...)