SQL Developer Hints to Format Output

SQL Developer may not be the perfect IDE, but it doesn't mean that it has not got any tricks at all. One of those features I'd like mention about can ease data transfer. SQL Developer excepts hints to format output. Here are the outputs for some of those hints; csv, loader, fixed and insert. As names implie; CSV gives rows with columns separated by commas, loader gives data formatted for SQL Loader, fixed returns columns separated by fixed spaces and insert creates insert commands.

select /*csv*/ * from scott.dept;
"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

select /*loader*/ * from scott.dept;
10|"ACCOUNTING"|"NEW YORK"|
20|"RESEARCH"|"DALLAS"|
30|"SALES"|"CHICAGO"|
40|"OPERATIONS"|"BOSTON"|

select /*fixed*/ * from scott.dept;
"DEPTNO"    "DNAME"           "LOC"                        
"10"        "ACCOUNTING"      "NEW YORK"                   
"20"        "RESEARCH"        "DALLAS"                     
"30"        "SALES"           "CHICAGO"                    
"40"        "OPERATIONS"      "BOSTON"                      

select /*insert*/ * from scott.dept;
REM INSERTING into scott.dept
SET DEFINE OFF;
Insert into scott.dept (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into scott.dept (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into scott.dept (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into scott.dept (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');

It's also possible to get output in html or xml format which can really be handy for a quick report. As you can see below, html format includes even a search box to make searches in content.


Please note that hints are used in lower case and no space left between the hint and *s.

BEA-337 Incidents on OMS 12c

Lately I noticed that free disk space on my 12c Cloud Control server was reducing for some amount. When I investigated, I've figured it out that there were daily incidents of BEA-337 errors with around 40 MB of log  files in /u01/app/Oracle/Middleware/oms12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/adr/diag /ofm/GCDomain/EMGC_OMS1/incident directory.

Problem Key: BEA-337 [WebLogicServer]
Error Message Id: BEA-337

Description
-----------
Incident detected using watch rule "StuckThread":
Watch ServerName:       EMGC_OMS1
Watch RuleType:         Log
Watch Rule:             (SEVERITY = 'Error') AND ((MSGID = 'WL-000337') OR (MSGID = 'BEA-000337'))
Watch DomainName:       GCDomain
Watch Data:
   SERVER : EMGC_OMS1
   MESSAGE : [STUCK] ExecuteThread: '7' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "611" seconds working on the request "weblogic.servlet.internal.ServletRequestImpl@70733a9e[
POST /em/websvcs/emws/ConsoleJobStepExecutorService HTTP/1.1

As you can see, incidents was caused due to a stuck thread watch rule. It was triggered because of "Refresh From My Oracle Support" job which is running more than 600 seconds. First thing I tried was rescheduling the job. From OEM, I navigated to jobs and found the job through advanced search by selecting ”Refresh From My Oracle Support” from the job type drop down and rescheduling it to some less busy time.

However it didn't work out the problem. So I decided to increase the parameter controlling stuck thread watch rule which is StuckThreadMaxTime. It's default value is 600 (seconds) and my job takes 13.5 minutes to complete. 

To alter the parameter:

1. Log on to WebLogic administration console through EMGC_OMS1 target on OMS or directly navigating to https://<host>:7200/console 

2. Click on EMGC_OMS1 under GCDomain->Environment -> Servers. Find "Stuck Thread Max Time" setting on Configuration/Tuning tab. I've increased it to 1200 seconds to cover job duration.


3. Save the configuration and then restart EMGC_OMS1 server from WebLogic administration console or from server console.

This workaround did the job.

Is Database 12c Supported on Exadata?

Answer is yes, even you can see 12c is listed under supported versions in document [888828.1].

However, there is a couple of buts (you should see this coming). Since latest  Exadata Storage Software (version 11.2.3.2.1) does not have new offload libraries, smart scan offload filtering and storage indexes become disabled with 12c. Smart scans are initiated but nodes get back blocks instead of rows and columns in the projection list. Also IORM plans are not enforced and inter-database plans are disabled on cells. And finally, cell metrics display 12c databases under OTHER_DATABASE.

So, it seems better to wait until next Exadata update and probably 12c patch set update to upgrade your Exadata. At least, to be able upgrade to a fully functional state. Meanwhile, you'd better upgrade to 11.2.0.3 if you haven't yet because upgrading from 11.2.0.1 is not supported.