SQL to duplicate table structure with / without data rows

Just a note to myself.

How to duplicate a table structure with / without data. Drop the where 1=0 to copy over the data as well.

As an example the Oracle HR scheme with the employees table is used and replicated to employees2 (see bold SQL line below).

Note, that not null constraints are replicated but primary keys, foreign key, unique constraints and triggers aren’t.

 

SQL> describe employees;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID NOT NULL NUMBER(6)
 FIRST_NAME VARCHAR2(20)
 LAST_NAME NOT NULL VARCHAR2(25)
 EMAIL NOT NULL VARCHAR2(25)
 PHONE_NUMBER VARCHAR2(20)
 HIRE_DATE NOT NULL DATE
 JOB_ID NOT NULL VARCHAR2(10)
 SALARY NUMBER(8,2)
 COMMISSION_PCT NUMBER(2,2)
 MANAGER_ID NUMBER(6)
 DEPARTMENT_ID NUMBER(4)

SQL> 
SQL> create table employees2 as select * from employees where 1=0;

Table created.

SQL> describe employees2;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID NUMBER(6)
 FIRST_NAME VARCHAR2(20)
 LAST_NAME NOT NULL VARCHAR2(25)
 EMAIL NOT NULL VARCHAR2(25)
 PHONE_NUMBER VARCHAR2(20)
 HIRE_DATE NOT NULL DATE
 JOB_ID NOT NULL VARCHAR2(10)
 SALARY NUMBER(8,2)
 COMMISSION_PCT NUMBER(2,2)
 MANAGER_ID NUMBER(6)
 DEPARTMENT_ID NUMBER(4)

DevOps tools: http REST client httpie

Doing unplanned things adds fun to our lives. Last year at Open World when heading back to the Hilton I sneaked into a REST session at Java One that I never planned to attend. Honestly, I went there to finish my very late lunch. There I learned about a REST client tool that you should add to your toolbox: http (pronounced “HTTP Pie”, with the brave abbreviation: http).

In short http is more convenient version of curl, for the non-color blind folks. It includes heaps of features, like storing session data that can referenced for following requests, easy specification of JSON values, etc.

Here is an example of its usage with the the new WebLogic 12.2.1 REST interface for administration:

http -a weblogic:welcome1 \ "localhost:7001/management/weblogic/latest/domainRuntime/serverLifeCycleRuntimes/AdminServer?links=none&fields=name,state"

 

Screen Shot 2016-04-15 at 09.34.54

You read it here first. But also check out my other DevOps tools postings like lsof, high CPU threads, tunnel with ngrok.

 

DevOps tools: Easily tunnel your localhost (WebLogic or some other) server with ngrok to the world

Here is another addition to my DevOps tools series that is worth to know if you work with any kind of servers such as WebLogic, nginx or Apache httpd etc.

ngrok is a fun and very easy tool I use from time to time in demos or when running a training.
It opens a public tunnel (that of course can be protected) to connect to your local server. Really nice if you are in a different network than your audience, or hidden between a DSL router etc.

In the webcast below I show how to access WebLogic 12.2.1 running on localhost:7001

You read it here first 🙂

also check out the webcasts of the DevOps tools series about how to detect high CPU threads, or the usage of lsof.

 

Oracle Service Bus 12c (12.2.1) Native REST: Bug in HTTP GET in Pipeline?

While working on an Oracle tech presentation to be posted here, I discovered the following issue with native REST and Oracle Service Bus.

Version

SOA Quickstart / 12.2.1 OSB, running on RedHat derivative with Oracle JDK 8.

Description

OSB 12.2.1 Native REST pipelines don’t work with GET parameters.

Creating a native REST business services works fine, also adding a REST proxy service.
Once you insert a pipeline HTTP stops working correctly (since the URL parameters aren’t forwarded, a list will be returned by the backend service according to REST semantics), since URL parameters are not forwarded.

Steps to reproduce:

  • Use REST tech adapter to create a REST business service for an existing REST backend.
  • Define GET and POST methods. WADL will be created.
  • Test business service from WADL: both GET and POST will work.
  • Create proxy service from REST business service
  • Test proxy service, again GET and POST methods will work from OSB test client.
  • In JDev, drop a pipeline on the line connecting proxy and business service.
  • In JDev verify the pipeline configuration. it will display correct values.
  • Test the proxy service again, only POST will work. GET does not work anymore (now returns a whole list), since the URL parameters are not forwarded. Even an empty OSB pipeline will swallow the GET URL parameters.

Severity

IMHO this is a bug. I am currently in discussion with Oracle about it.

What is next?

I will let you know about updates / fixes or workarounds. Note, you could set the URL parameters as routing options to circumnavigate the issue for a particular use case.

Oracle Service Bus 12.2.1 JVM Settings: PermSize, Heap, Non-Heap, and ResourceManagement

Oracle Service Bus comes with JVM settings that cause questions to some customers. This posting provides answers to the most common questions I discussed in workshops or received so far.

Warning about PermSize Option

Question 1: “I see the following warning:

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=512m; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=1024m; support was removed in 8.0

Does that mean that Oracle generates the startup scripts with wrong JVM flags?”

Answer: With Oracle JVM 8 the permanent space was removed. Not having a perm space was a JRockit “feature” that has been ported over to the Oracle JVM. The warnings of course are harmless. Startup scripts for WebLogic only domains are generated correctly for WebLogic 12.2.1. So Oracle needs to change this for OSB domains and they know about it.

Heap Size

Question 2: “How big is Oracle Service Bus now? I used to be able to create and run a cluster on my laptop with earlier versions but now I run into resource problems.”

Answer: Default startup parameters are: -Xms1024m -Xmx2048m, i.e. minimum heap size is 1 GB, maximum heap size is 2 GB. Hence you should expect your process size to be larger than 1 GB right from the start.

Screen Shot 2016-02-17 at 13.27.49 Screen Shot 2016-02-17 at 13.29.38

Roughly speaking after starting up a single OSB instance (everything hosted on the admin server), you should expect more than 400 Mb of heap used. Have a look at the screenshot above. The last drop in the first screenshot was caused by an external garbage collection request (I did it manually). In addition there is more than 500 Mb of non-heap used (100 Mb code cache and 400 Mb meta space, GC of course does not affect this area). This shows that 1GB as a minimum setting makes sense.

JVM ResourceManagement Flag

Question 3: “I see the following warning

<Feb 17, 2016 9:45:56 AM CET> <Info> <RCM> <BEA-2165021> <"ResourceManagement" is not enabled in this JVM. Enable "ResourceManagement" to use the WebLogic Server "Resource Consumption Management" feature. To enable "ResourceManagement", you must specify the following JVM options in the WebLogic Server instance in which the JVM runs: -XX:+UnlockCommercialFeatures -XX:+ResourceManagement.> 
Should I enable -XX:+ResourceManagement? Will it help to improve OSB 12c performance?"

Answer: You probably have read announcements that emphasize that OSB 12.2.1 is running on top of WebLogic 12.2.1 and WebLogic 12.2.1 supports a number of exciting new features. Nothing wrong with that, kind of marketing logic though.

It is important to understand that Oracle Service Bus 12.2.1 (and other up stack 12c products such as Oracle SOA Suite, Oracle BPM etc.) doesn’t use yet some really cool WebLogic 12.2.1 features such as domain partitioning or elastic cluster.

In short: Oracle JVM 8 resource management is used as a commercial feature together with G1 garbage collector to track JVM resource usage on the JVM level per partition. Based on the collected data about memory, file and thread usage WebLogic can then react and ensure that a WebLogic partition within one domain doesn’t steal too many resources from another partition. It’s important to understand that the magic (the reaction) happens in WebLogic and it’s based on the data provided by the JVM.

So will the -XX:+ResourceManagement setting improve OSB 12.2.1 performance? I’d say no. It will only have benefits when used with partions which are so far not supported by OSB 12.2.1.