But they're the only times I've ever known
And I believe there is a time for meditation
In cathedrals of our own
Now I have seen that sad surrender in my mother's eyes
I can only stand apart and sympathize
For we are always what our situations hand us
Its either sadness or euphoria
-- From Billy Joel's "Summer, Highland Falls"
In working with Oracle customers every day, I'm seeing a common thread running through many internal IT departments: Nephophobia. That's right, fear of clouds. In this case, I'm talking fear of clouds from a technology perspective (I'm admittedly having a bit of fun here and mean no offense to anyone with a true fear of clouds).
The fear shows up through either resistance or an avalanche of "what if" or "what about" questioning. I suspect that the cause of that fear is rooted in the fear of change, as in "what happens to my job"? So this post is for all those folks in all those internal IT departments faced with moving to the cloud, whether it be SaaS, PaaS, Hybrid, or whatever.
You are spot on in recognizing that your world is changing. All the things you've spent your time doing - patches, upgrades, general maintenance - they're all going away. The cloud vendor will be taking over that work as part of the service to which your institution will subscribe. But, as those tasks disappear, new opportunities arise. Some examples:
Network administration: because your users are interacting with off-location servers, the performance of your own internal network becomes even more critical in a move to the cloud.
Integration: as much as the major enterprise application vendors would like you to stick with one platform, odds are you won't. You'll probably mix two or more vendors plus some in-house applications. Getting all these apps to talk to each other is critical.
Development: one of the keys for enterprise application cloud vendors is that, in order to scale (and thus make money, because cloud services are a volume business), the business processes have to be pretty basic so they can be easily shared across multiple industries. If you work with an institution that has unique transactional and/or reporting needs (I see this frequently with public sector organizations), there will be some custom development involved. Extensions, bolt-on applications, unique reporting...all will live on to some extent, although probably not as much as you've seen in the past.
Mobile: everyone wants mobile and the cloud provides a great platform for delivering mobile applications. So all those things about network administration, integration and development? They apply here as well...maybe even more so.
All this discussion notwithstanding, let's get to the root of it: this type of change can threaten your job. It's scary. So what do you do? Update your skills to stay relevant. The key to making a living in IT over the long-term is to be continually learning new things. If you don't make the investment on your own, you'll find yourself on the outside looking in. So do it. Dig into this cloud thing. Learn the technical underpinnings. Figure out where you and your IT department fit...how can you add value? And feel the fear go away.
Everyone gets caught out some of the time with NOT IN.
This came up in a (fairly typical) question on OTN recently where someone had the task of “deleting 6M rows from a table of 18M”. A common, and perfectly reasonable, suggestion for dealing with a delete on this scale is to consider creating a replacement table holding the data you do want rather than deleting the data you don’t want. In this case, however, the query for deleting the data looked like this:
DELETE FROM EI.CASESTATUS WHERE CASEID NOT IN (SELECT CASEID FROM DO.STG_CASEHEADER);
The suggested code for creating the kept data was this:
CREATE TABLE newTable as SELECT * FROM EI.CASESTATUS WHERE CASEID IN (SELECT CASEID FROM DO.STG_CASEHEADER);
You might get the same result sets in the two tables after this process – but it depends on the CASEID in both tables never being NULL. (You might think that a column with ID in the name probably ought to be a primary key, or a foreign key with a NOT NULL declaration, but then again there’s that STG_ in the subquery table that might be indicative of a “staging” table, so who knows what might happen if the data’s allowed to start life as dirty data.) Here’s a quick demo to prove the point. First some simple data creation – with an optional insert so that you’ve got two tests in one script – followed by the two strategies for identifying data:
drop table t3; drop table t2; drop table t1; create table t1 (n1 number); create table t2 (n1 number); insert into t1 values(null); insert into t1 values(1); insert into t1 values(2); /* two tests available, with or without a null in t2 */ -- insert into t2 values(null); insert into t2 values(1); commit; -- gather stats here set null n/a delete from t1 where t1.n1 not in (select t2.n1 from t2); prompt Remainder after delete select * from t1; rollback; prompt Selected on create create table t3 as select * from t1 where t1.n1 in (select t2.n1 from t2); select * from t3;
Then the two sets of output from running the test, first with the the NULL insert into t2:
Remainder after delete N1 ---------- n/a 1 2 Selected on create N1 ---------- 1
We haven’t deleted ANY data from t1 when we were probably hoping that the 2 would disappear – after all, it’s not in t2; however since the equality comparison between a t1 row and every t2 row must evaluate to FALSE before a t1 row is deleted and the comparison of 2 and NULL evaluates to NULL the 2 row won’t be deleted (similarly the comparison for “t1.NULL = t2.anything” evaluates to NULL rather than FALSE, so the NULL isn’t deleted).
Still, perhaps the rewrite would have been okay for the data set where we don’t have a NULL in t2:
Remainder after delete N1 ---------- n/a 1 Selected on create N1 ---------- 1
Oops – still doesn’t produce matching results . This time the row with the 2 has disappeared from t1 in both cases – which might have been closer to what the original OTN poster had hoped but we still have the difference in the survival of the NULLs from t1 – for the reason given for the previous data set
In passing, the execution plan subsequently supplied by the OP showed a “MERGE JOIN ANTI NA” with stg_caseheader (the subquery table) as the second table. The significance of the NA (Null-aware) is that it tells us that the join column in stg_caseheader definitely doesn’t have a NOT NULL constraint on it. (We can’t draw any conclusion about the join column in casestatus.)
Make sure all the recommended patches are in place as a part of the bundle patch. Your EBS 12.2 ADOP cycle could go unstable with out the database patches.
Mostly I've had it fairly easy in my career. When I worked in the UK, I just had the one time zone to work with. The only time things got complicated was when I was working at one of the power generation companies, and we had to make provision for the 23-hour and 25-hour days that go with Daylight Savings.
And in Australia we only have a handful of timezones, and when I start and finish work, it is the same day for any part of Australia. I did work on one system where the database clock was set to UTC, but dates weren't important on that application.
Now it is different. I'm dealing with events that happen all over the world. Again the database clock is UTC, with the odd effect that TRUNC(SYSDATE) 'flips over' around lunchtime. Now when I want to look at 'recent' entries (eg a log table) I've got into the habit of asking WHERE LOG_DATE > SYSDATE - INTERVAL '9' HOUR
And we also have columns that are TIMESTAMP WITH TIMEZONE. So I'm getting into the habit of selecting COL_TS AT TIME ZONE DBTIMEZONE . I could use sessiontimezone, but then the time component of DATE columns would be inconsistent. This becomes just a little more confusing this time of year as various places slip in and out of Daylight Savings.
Now things are getting even more complicated for me.
Again, during my career, I've been lucky enough to be pretty oblivious to character set issues. Most things have squeezed in to my databases without any significant trouble. Occasionally I've had to look for some accented characters in people's names, but that's been it.
In the past few months, I've been working with some European data where the issues have been more pronounced. Aside from a few issues in emails, I've been coping quite well (with a lot of help from Google Translate).
Now I get to work with some Japanese data. And things get complicated.
"The modern Japanese writing system is a combination of two character types: logographic kanji, which are adopted Chinese characters, and syllabic kana. Kana itself consists of a pair of syllabaries: hiragana, used for native or naturalised Japanese words and grammatical elements, and katakana, used for foreign words and names, loanwords, onomatopoeia, scientific names, and sometimes for emphasis. Almost all Japanese sentences contain a mixture of kanji and kana. Because of this mixture of scripts, in addition to a large inventory of kanji characters, the Japanese writing system is often considered to be the most complicated in use anywhere in the world."Japanese writing system
Firstly I hit katakana. With some tables, I can get syllables corresponding to the characters and work out something that I can eyeball and match up to some English data. As an extra complication, there are also half-width characters which are semantically equivalent but occupy different codepoints in Unicode. That has parallels to upper/lower case in English, but is a modern development that came about from trying to fit the previously squarish forms into print, typewriters and computer screens.
Kanji is a different order of shock. Primary school children in Japan learn the first 1000 or so characters. Another thousand plus get taught in high school. The character set is significantly larger in total.
I will have to see if the next few months cause my head to explode. In the mean time, I can recommend reading this article about the politics involved in getting characters (glyphs ? letters ?) into Unicode. I Can Text You A Pile of Poo, But I Can’t Write My Name
Oh, and I'm still trying to find the most useful character/font set I can have on my PC and use practically in SQL Developer. My current choice shows the Japanese characters when I click in the field in the dataset, but only little rectangles when I'm not in the field. The only one I've found that does show up all the time is really UGLY.
You are running adoafmctl.sh version 120.6.12000000.3
Starting OPMN managed OAFM OC4J instance ...
adoafmctl.sh: exiting with status 152
adoafmctl.sh: check the logfile
$INST_TOP/logs/appl/admin/log/adoafmctl.txt for more information
--> Process (index=1,uid=349189076,pid=15039)
time out while waiting for a managed process to start
07/31/09-09:14:28 :: adoafmctl.sh: exiting with status 152
07/31/09-09:14:40 :: adoafmctl.sh version 120.6.12000000.3
07/31/09-09:14:40 :: adoafmctl.sh: Checking the status of OPMN managed OAFM OC4J instance
Processes in Instance: SID_machine.machine.domain
ias-component | process-type | pid | status
default_group | oafm | N/A | Down
1. Shutdown all Middle tier services and ensure no defunct processes exist running the following from the operating system:
# ps -ef | grep
If one finds any, kill these processes.2. Navigate to $INST_TOP/ora/10.1.3/opmn/logs/states directory. It contains hidden file .opmndat:
# ls -lrt .opmndat3. Delete this file .opmndat after making a backup of it:
# rm .opmndat4. Restart the services.
5. Re-test the issue.
This resolved the issue.
I have updated sample application from previous post, to include indicator for AMPA background service call status tracking. Updated sample application - MobileServiceBusApp_v8.zip.
AMPA provides application scope variable, which acts as a flag and indicates when background service call is executed. Based on this flag, we could conditionally display animated GIF image, this will help user to understand if background service call still runs:
When user is searching and request is being processed in background, he will see rotating status indicator in the top right corner:
Until data is being returned from background task, user could go to another screen and monitor when request is completed, to see the latest data:
Once background task completes, indicator disappears:
I think that the process of building a data mining scoring engine is similar to develop an application.
We have the requirement analysis, functional design, technical design, coding, testing, deployment, etc. phases.
5-Apr: WIT panel added, Alex removed, Gwen and Pete schedule shifted.
11-Apr: Gwen and Pete swapped sessions.
13-Apr: Jonathan off lightning talks.
Guess what? OakTable World at IOUG C15 is happening again! Last year, we had awesome sessions and wonderful attendees. The sessions were so successful, in fact, that we needed a bigger room this year (there were other reasons too, but hey we can fit more people now!).
What: OakTable World C15
When: Wednesday, April 15, 2015, 8:00am – 5:30pm
Where: Mandalay Ballroom K
I really hope that, if you are reading this, you are planning to attend COLLABORATE 15 – IOUG Forum at the Mandalay Bay Resort & Casino in Vegas from April 12-16. If you haven’t yet planned your trip, this might just help you make the call. You know you want to be there!
OakTable Network will be holding its highly anticipated OakTable World during COLLABORATE 15! As always, IOUG was able to provide a room for us to use for the whole day (and boy what a big room it is!). The agenda is determined by the OakTable speakers, who choose topics they are passionate about. And if history is any indicator, these are also the topics you really want to hear about.
For those of you who aren’t familiar with OakTable World, Mogens Nørgaard started it as an underground event during Oracle OpenWorld—somewhere between 2007 and 2009. After several successful years and increasing popularity, the event became known as OakTable World during OOW12 and OOW13. Last year, we hosted OTWC14 at IOUG COLLABORATE 14 in Vegas. Needless to say, it was a success. So…Vegas here we come again!
Thank you to all the great companies who have sponsored this event over the years—you know who you are. This year, the usual suspects have pitched in to make it happen again—Pythian, Enkitec and Delphix. Once again, we will be printing unique t-shirts with cool graphics and awesome sponsors’ logos. Be part of history!
The OTW sessions are (mostly) aligned with conference sessions, except we start a tad later (you will appreciate it) and we’ve shifted a few sessions by 15 minutes to pack in as many as possible. Don’t worry, though, we don’t run anything during lunch or afternoon nap. :)
The current schedule is below, but check back regularly as it may change due to random events.Time Presenter Title 8:10-8:15 someone authorized Opening Notes 8:15-9:00 Tim Gorman Augmenting SQL Monitor 9:15-10:15 guest session Women in Technology Panel 10:30-11:10 Pete Sharman Knowledge Sharing – Why Do It? 11:15-12:00 Gwen Shapira Kafka for DBAs – Because Inquiring Minds Need to Know 14:00-15:00 see below Lightning Talks! 15:15-16:15 Cary Millsap The Go/No-Go Matrix for Thinking Clearly About Testing 16:30-17:30 Jared Still Knowledge Builds Intuition
Lightning Talks are 10-minute presentations done in a rapid-fire fashion. They are always a huge success—you’ve got to be there! They may be technical, motivational, or inspirational, but regardless they are always cool speeches. The sequence of the talks may change, but everything will be presented within the hour.Presenter Lightning Talk Kyle Hailey What is DevOps Kellyn Pot’Vin-Gorman SQLT in AWR Warehouse Alex Gorbachev #100miles Pete Sharman SnapClones++ Jonah H. Harris Performing MongoDB-Compatible NoSQL on Top of Oracle SQL
The OakTable Network folks and other great people will be hanging around, so make sure you drop by! This is an awesome place to grow your network. Remember that the presenters determine the agenda. Our passion to share and educate is what drives us. Come join us.
Vegas, here we come!
This sounds like a simple request doesn’t it?
Simple if there are no roles which are granted DBA role and allow me to show what can go wrong.
Create ROLE “secret”, Grant DBA to “secret”, Grant “secret” to USER “michael”
ARROW:(SYS@hawklas):PRIMARY> create role secret; Role created. ARROW:(SYS@hawklas):PRIMARY> grant dba to secret; Grant succeeded. ARROW:(SYS@hawklas):PRIMARY> grant secret to michael identified by michael; Grant succeeded. ARROW:(SYS@hawklas):PRIMARY>
When a simple SQL is used, “secret” is a ROLE NOT USER.
ARROW:(SYS@hawklas):PRIMARY> r 1 SELECT grantee 2 FROM dba_role_privs 3 WHERE granted_role='DBA' 4 AND NOT regexp_like(grantee,'SYS|OUTLN|DBSNMP|ORACLE_OCM') 5* GRANTEE ------------------------------ MDINH HR GGADMIN TESTING SECRET ARROW:(SYS@hawklas):PRIMARY>
Easy enough, let’s join dba_role_privs and dba_users. Where’s “Michael” at?
ARROW:(SYS@hawklas):PRIMARY> r 1 SELECT r.grantee 2 FROM dba_role_privs r, dba_users u 3 WHERE r.grantee=u.username 4 AND r.granted_role='DBA' 5 AND NOT regexp_like(r.grantee,'SYS|OUTLN|DBSNMP|ORACLE_OCM') 6 ORDER BY 1 7* GRANTEE ------------------------------ GGADMIN HR MDINH TESTING ARROW:(SYS@hawklas):PRIMARY>
We found “Michael”
ARROW:(SYS@hawklas):PRIMARY> r 1 WITH u AS ( 2 SELECT username 3 FROM dba_users 4 WHERE NOT regexp_like(username,'SYS|OUTLN|DBSNMP|ORACLE_OCM') 5 ) 6 SELECT distinct username 7 FROM u, dba_role_privs d 8 WHERE username = d.grantee 9 OR (d.granted_role='DBA' AND d.granted_role in (SELECT role FROM role_role_privs WHERE granted_role='DBA')) 10 ORDER BY 1 11* USERNAME ------------------------------ GGADMIN HR MDINH MICHAEL TESTING ARROW:(SYS@hawklas):PRIMARY>
Came across a blog which has another solution.
ARROW:(SYS@hawklas):PRIMARY> r 1 select username, 1 level_deep from V$PWFILE_USERS 2 union 3 select grantee, max(level_deep) from ( 4 select distinct level level_deep, grantee, granted_role 5 from dba_role_privs 6 start with granted_role='DBA' 7 connect by prior grantee=granted_role 8 ) where grantee in (select username from dba_users) 9 group by grantee 10* order by 1 USERNAME LEVEL_DEEP ------------------------------ ---------- GGADMIN 1 HR 1 MDINH 1 MICHAEL 2 SYS 1 SYSTEM 1 TESTING 1 7 rows selected. ARROW:(SYS@hawklas):PRIMARY>
Note: regexp_like is available from 10g onward.
OpenWorld SF 2015 External Call for Proposal is now open, from Business Analytics - Proactive Support.
From Tanel Poder: Sqlplus is my second home, part 8: Embedding multiple sqlplus arguments into one variable
From DBA Kevlar: SQLTXPLAIN and the AWR Warehouse, Part I, and part 2 is already out.
Also from Oracle guru Tanel Poder comes this posting on Exadata: Oracle Exadata Performance: Latest Improvements and Less Known Features
This list of books available free from KDNuggets seems a pretty good deal. I went all the way through to a couple of PDFs without any dire warnings, registrations, pop ups offering cat videos, etc: More Free Data Mining, Data Science Books and Resources.
EPM Patch Set Updates - March 2015, from Business Analytics - Proactive Support.
Cumulative Feature Tool for Oracle EPM Versions, from BI & EPM Partner Community EMEA
Coherence, WebLogic and Java SE 8, from WebLogic Partner Community EMEA.
From The Java Source: Updates to the Java Troubleshooting Guide
From the Ops Centerblog: Ops Center's port usage
Several items from Fusion Applications Developer Relations of late:
Working With Lists Of Values and Tables in Expression Language
Resources Roadmap for Customizing Security
Groovy Introduction on Customer Connect And Other Session Recommendations
From Oracle University: Training for PeopleTools 8.54
From the Oracle E-Business Suite Technology blog:
Periodic Average Costing Data Collection Scripts and Process Flow
The EBS iProcurement Item Analyzer Has A Whole New Look and Feel!
Where to Find Product Documentation for General Ledger?
Balances Corruption Fix Script (GLBALFIX) Now Available for Parameters Including Special Characters
General Ledger Recommended Patch Collection Released Recently!
See the new Purchasing Document Open Interface (PDOI) improvements in 12.1.3!
What is SQLcl ? SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It is a lightweight tool (only 11MB) developed by the SQL Developer team fully compatible with Windows and Unix/Linux. You don’t need to install it so it is totally portable. The tool does not need … Continue reading SQLcl, a revolution for SQL*Plus users →
When you really need to run one script, at all cost, an annoying error is
ORA-00020: maximum number of processes (40) exceeded, which can even occurs as sysdba.
Test case (21 is a not something to do in real life):
SQL> alter system set processes=21 scope=spfile; System altered. SQL> startup force quiet ORACLE instance started. Database mounted. Database opened. SQL> quit
From now on, sqlplus as sysdba is impossible.
$ sqlplus -s -L / as sysdba ERROR: ORA-00020: maximum number of processes (40) exceeded SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
Okay, if you really really need to run one script, you could connect with sqlplus -prelim and restart the database.
if :|sqlplus / as sysdba|grep ORA-00020 then echo shu abort|sqlplus -prelim / as sysdba echo startup quiet|sqlplus / as sysdba fi
If ORA-20 is detected, then the database will be restarted.
Like many other Oracle professionals and speakers I will be attending IOUG Collaborate 2015 this year in Las Vegas. I’m not a big fan of Las Vegas, but hey cannot turn down an opportunity to speak, especially when IOUG asked me to do more than one session.
This year my schedule is going to keep me busy; yet full of good topics that cover both EM12c and GoldenGate. If you are going to be a Collaborate, come check out my sessions and many others.
My sessions this year:
09:00 am – 03:00 pm – RAC SIG Function (RAC Attack)
10:30 am – Writing to Lead Panel discussion
12:00 pm – Exadata Exachk and EM12c: Keeping up with Exadata
17:30 pm – IOUG Data Integration SIG Meeting
11:00 am – Enable Oracle GoldenGate Monitoring for the Masses with EM12c
08:00 am – Examine Oracle GoldenGate Trail Files: How and When to use Logdump Utility
10:45 am – Extreme Replication: Performance Tuning Oracle GoldenGate for the Real World
If you are going to be a Collaborate, I look forward to see you there and hopefully in one of my sessions.
Filed under: Database, Golden Gate, OEM