Skip navigation.

Feed aggregator

Bitmap Counts

Jonathan Lewis - Mon, 2015-01-19 06:15

In an earlier (not very serious) post about count(*) I pointed out how the optimizer sometimes does a redundant “bitmap conversion to rowid” when counting. In the basic count(*) example I showed this wasn’t a realistic issue unless you had set cursor_sharing to “force” (or the now-deprecated “similar”). There are, however, some cases where the optimizer can do this in more realistic circumstances and this posting models a scenario I came across a few years ago. The exact execution path has changed over time (i.e. version) but the anomaly persists, even in

First we create a “fact” table and a dimension table, with a bitmap index on the fact table and a corresponding primary key on the dimension table:

create table area_sales (
	area		varchar2(10)	not null,
	dated		date		not null,
	category	number(3)	not null,
	quantity	number(8,0),
	value		number(9,2),
	constraint as_pk primary key (dated, area),
	constraint as_area_ck check (area in ('England','Ireland','Scotland','Wales'))

insert into area_sales
with generator as (
	select	--+ materialize
		rownum 	id
	from	all_objects
	where	rownum <= 3000
	sysdate + 0.0001 * rownum,
	rownum <= 1e6

create bitmap index as_bi on area_sales(category) pctfree 0;

create table dim (
	id	number(3) not null,
	padding	varchar2(40)

alter table dim add constraint dim_pk primary key(id);

insert into dim
	distinct category, lpad(category,40,category)
from	area_sales


		ownname		 => user,
		tabname		 =>'AREA_SALES',
		method_opt 	 => 'for all columns size 1',
		cascade		 => true

		ownname		 => user,
		tabname		 =>'DIM',
		method_opt 	 => 'for all columns size 1',
		cascade		 => true

Now we run few queries and show their execution plans with rowsource execution statistics. First a query to count the number of distinct categories used in the area_sales tables, then a query to list the IDs from the dim table that appear in the area_sales table, then the same query hinted to run efficiently.

set trimspool on
set linesize 156
set pagesize 60
set serveroutput off

alter session set statistics_level = all;

	distinct category

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

select  distinct category from  area_sales
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|   0 | SELECT STATEMENT             |       |      1 |        |    300 |00:00:00.01 |     306 |       |       |          |
|   1 |  HASH UNIQUE                 |       |      1 |    300 |    300 |00:00:00.01 |     306 |  2294K|  2294K| 1403K (0)|
|   2 |   BITMAP INDEX FAST FULL SCAN| AS_BI |      1 |   1000K|    600 |00:00:00.01 |     306 |       |       |          |

As you can see, Oracle is able to check the number of distinct categories very quickly by scanning the bitmap index and extracting ONLY the key values from each of the 600 index entries that make up the whole index (the E-rows figure effectively reports the number of rowids identified by the index, but Oracle doesn’t evaluate them to answer the query).

select  /*+   qb_name(main)  */  dim.* from dim where  id in (   select
   /*+     qb_name(subq)    */    distinct category   from
area_sales  )

| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|   0 | SELECT STATEMENT              |       |      1 |        |    300 |00:00:10.45 |     341 |       |       |          |
|*  1 |  HASH JOIN SEMI               |       |      1 |    300 |    300 |00:00:10.45 |     341 |  1040K|  1040K| 1260K (0)|
|   2 |   TABLE ACCESS FULL           | DIM   |      1 |    300 |    300 |00:00:00.01 |      23 |       |       |          |
|   3 |   BITMAP CONVERSION TO ROWIDS |       |      1 |   1000K|    996K|00:00:02.64 |     318 |       |       |          |
|   4 |    BITMAP INDEX FAST FULL SCAN| AS_BI |      1 |        |    599 |00:00:00.01 |     318 |       |       |          |

What we see here is that (unhinted) oracle has converted the IN subquery to an EXISTS subquery then to a semi-join which it has chosen to operate as a HASH semi-join. But in the process of generating the probe (sescond) table Oracle has converted the bitmap index entries into a set of rowids – all 1,000,000 of them in my case – introducing a lot of redundant work. In the original customer query (version 9 or 10, I forget which) the optimizer unnested the subquery and converted it into an inline view with a distinct – but still performed a redundant bitmap conversion to rowids. In the case of the client, with rather more than 1M rows, this wasted a lot of CPU.

select  /*+   qb_name(main)  */  dim.* from (  select   /*+
qb_name(inline)    no_merge    no_push_pred   */   distinct category
from   area_sales  ) sttv,  dim where = sttv.category

| Id  | Operation                      | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|   0 | SELECT STATEMENT               |       |      1 |        |    300 |00:00:00.02 |     341 |       |       |          |
|*  1 |  HASH JOIN                     |       |      1 |    300 |    300 |00:00:00.02 |     341 |  1969K|  1969K| 1521K (0)|
|   2 |   VIEW                         |       |      1 |    300 |    300 |00:00:00.01 |     306 |       |       |          |
|   3 |    HASH UNIQUE                 |       |      1 |    300 |    300 |00:00:00.01 |     306 |  2294K|  2294K| 2484K (0)|
|   4 |     BITMAP INDEX FAST FULL SCAN| AS_BI |      1 |   1000K|    600 |00:00:00.01 |     306 |       |       |          |
|   5 |   TABLE ACCESS FULL            | DIM   |      1 |    300 |    300 |00:00:00.01 |      35 |       |       |          |

By introducing a manual unnest in the original client code I avoided the bitmap conversion to rowid, and the query executed much more efficiently. As you can see the optimizer has predicted the 1M rowids in the inline view, but used only the key values from the 600 index entries. In the case of the client it really was a case of manually unnesting a subquery that the optimizer was automatically unnesting – but without introducing the redundant rowids.

In my recent 12c test I had to include the no_merge and no_push_pred hints. In the absence of the no_merge hint Oracle did a join then group by, doing the rowid expansion in the process; if I added the no_merge hint without the no_push_pred hint then Oracle did a very efficient nested loop semi-join into the inline view. Although this still did the rowid expansion (predicting 3,333 rowids per key) it “stops early” thanks to the “semi” nature of the join so ran very quickly:

select  /*+   qb_name(main)  */  dim.* from (  select   /*+
qb_name(inline)    no_merge   */   distinct category  from   area_sales
 ) sttv,  dim where = sttv.category

| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
|   0 | SELECT STATEMENT              |       |      1 |        |    300 |00:00:00.02 |     348 |
|   1 |  NESTED LOOPS SEMI            |       |      1 |    300 |    300 |00:00:00.02 |     348 |
|   2 |   TABLE ACCESS FULL           | DIM   |      1 |    300 |    300 |00:00:00.01 |      35 |
|   3 |   VIEW PUSHED PREDICATE       |       |    300 |   3333 |    300 |00:00:00.01 |     313 |
|   4 |    BITMAP CONVERSION TO ROWIDS|       |    300 |   3333 |    300 |00:00:00.01 |     313 |
|*  5 |     BITMAP INDEX SINGLE VALUE | AS_BI |    300 |        |    300 |00:00:00.01 |     313 |

Bottom line on all this – check your execution plans that use bitmap indexes – if you see a “bitmap conversion to rowids” in cases where you don’t then visit the table it may be a redundant conversion, and it may be expensive. If you suspect that this is happening then dbms_xplan.display_cursor() may confirm that you are doing a lot of CPU-intensive work to produce a very large number of rowids that you don’t need.

Where the innovation is

DBMS2 - Mon, 2015-01-19 02:27

I hoped to write a reasonable overview of current- to medium-term future IT innovation. Yeah, right. :) But if we abandon any hope that this post could be comprehensive, I can at least say:

1. Back in 2011, I ranted against the term Big Data, but expressed more fondness for the V words — Volume, Velocity, Variety and Variability. That said, when it comes to data management and movement, solutions to the V problems have generally been sketched out.

  • Volume has been solved. There are Hadoop installations with 100s of petabytes of data, analytic RDBMS with 10s of petabytes, general-purpose Exadata sites with petabytes, and 10s/100s of petabytes of analytic Accumulo at the NSA. Further examples abound.
  • Velocity is being solved. My recent post on Hadoop-based streaming suggests how. In other use cases, velocity is addressed via memory-centric RDBMS.
  • Variety and Variability have been solved. MongoDB, Cassandra and perhaps others are strong NoSQL choices. Schema-on-need is in earlier days, but may help too.

2. Even so, there’s much room for innovation around data movement and management. I’d start with:

  • Product maturity is a huge issue for all the above, and will remain one for years.
  • Hadoop and Spark show that application execution engines:
    • Have a lot of innovation ahead of them.
    • Are tightly entwined with data management, and with data movement as well.
  • Hadoop is due for another refactoring, focused on both in-memory and persistent storage.
  • There are many issues in storage that can affect data technologies as well, including but not limited to:
    • Solid-state (flash or post-flash) vs. spinning disk.
    • Networked vs. direct-attached.
    • Virtualized vs. identifiable-physical.
    • Object/file/block.
  • Graph analytics and data management are still confused.

3. As I suggested last year, data transformation is an important area for innovation. 

  • MapReduce was invented for data transformation, which is still a large part of what goes on in Hadoop.
  • The smart data preparation crowd is deservedly getting attention.
  • The more different data models — NoSQL and so on — that are used, the greater are the demands on data transformation.

4. There’s a lot going on in investigative analytics. Besides the “platform” technologies already mentioned, in areas such as fast-query, data preparation, and general execution engines, there’s also great innovation higher in the stack. Most recently I’ve written about multiple examples in predictive modeling, such as:

Beyond that:

  • Event-series analytics is another exciting area. (At least on the BI side, I frankly expected it to sweep through the relevant vertical markets more quickly than it has.)
  • I’ve long been disappointed in the progress in text analytics. But sentiment analysis is doing fairly well, many more languages are analyzed than before, and I occasionally hear rumblings of text analytic sophistication inching back towards that already available in the previous decade.
  • While I don’t write about it much, modern BI navigation is an impressive and wonderful thing.

5. Back in 2013, in what was perhaps my previous most comprehensive post on innovation, I drew a link between innovation and refactoring, where what was being refactored was “everything”. Even so, I’ve been ignoring a biggie. Security is a mess, and I don’t see how it can ever be solved unless systems are much more modular from the ground up. By that I mean:

  • “Fencing” processes and resources away from each other improves system quality, in that it defends against both deliberate attacks and inadvertent error.
  • Fencing is costly, both in terms of context-switching and general non-optimization. Nonetheless, I suspect that …
  • … the cost of such process isolation may need to be borne.
  • Object-oriented programming and its associated contracts are good things in this context. But it’s obvious they’re not getting the job done on their own.

More specifically,

  • It is cheap to give single-purpose intelligent devices more computing power than they know what to do with. There is really no excuse for allowing them to be insecure.
  • It is rare for a modern PC to go much above 25% CPU usage, simply because most PC programs are still single-core. This illustrates that — assuming some offsetting improvements in multi-core parallelism — desktop software could take a security performance hit without much pain to users’ wallets.
  • On servers, we may in many cases be talking about lightweight virtual machines.

And to be clear:

  • What I’m talking about would do little to help the authentication/authorization aspects of security, but …
  • … those will never be perfect in any case (because they depend upon fallible humans) …
  • … which is exactly why other forms of security will always be needed.

6. You’ve probably noticed the fuss around an open letter about artificial intelligence, with some press coverage suggesting that AI is a Terminator-level threat to humanity. Underlying all that is a fairly interesting paper summarizing some needs for future research and innovation in AI. In particular, reading the paper reminded me of the previous point about security.

7. Three areas of software innovation that, even though they’re pretty much in my wheelhouse, I have little to say about right now are:

  • Application development technology, languages, frameworks, etc.
  • The integration of analytics into old-style operational apps.
  • The never-ending attempts to make large-enterprise-class application functionality available to outfits with small-enterprise sophistication and budgets.

8. There is, of course, tremendous innovation in robots and other kinds of device. But this post is already long enough, so I’ll address those areas some other time.

Related link

In many cases, I think that innovations will prove more valuable — or at least much easier to monetize — when presented to particular vertical markets.

Categories: Other

Log Buffer #406, A Carnival of the Vanities for DBAs

Pakistan's First Oracle Blog - Mon, 2015-01-19 01:38
This Log Buffer Edition covers blog posts from various bloggers of Oracle, SQL Server and MySQL.


Sync tables: generate MERGE using Unique constraint.
What Hardware and Software Do YOU Want Oracle to Build?
There were a number of new features introduced in Ops Center 12.2.2. One of the shiny ones is an expansion of the backup and recovery capabilities to include Proxy Controllers.
Want to Be a Better Leader? Answer One Question.
Managing a remote Oracle Database instance with “Geographic Edition”.

SQL Server:

Learn how you can use SQLCop to prevent your developers from writing stored procedures that are named sp_ something.
Data Cleaning in SQL 2012 with Data Quality Services.
Stairway to PowerPivot and DAX - Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions.
Options to Improve SQL Server Bulk Load Performance.
Dynamically Create Tables Based on an Access Table


Stored Procedures: critiques and defences.
JSON UDF functions 0.3.3 have been released.
Business Scalability, Operational Efficiency and Competitive Edge with MariaDB MaxScale 1.0 GA.
MySQL 5.7 labs and the HTTP Plugin – inserting, updating and deleting records in MySQL via HTTP.
Hyper-threading – how does it double CPU throughput?

Published on Pythian Blog
Categories: DBA Blogs

Setting up your own local Oracle Development environment in less than 15 minutes

Dimitri Gielis - Sun, 2015-01-18 17:30
The fastest and easiest way to setup your own local Oracle Development environment is by using the "Oracle Technology Network Developer Day" Database Virtual Box Appliance.

It contains:

  • Oracle Linux 7
  • Oracle Database 12c ( EE (CDB/PDB)
  • Oracle Rest Data Services (ORDS)
  • Oracle Application Express (APEX)
  • Oracle SQL Developer and Oracle SQL Developer Data Modeler
Step 1: Download and install Oracle VM VirtualBox

Step 2: Download the VM image that contains all the pieces you need

Step 3: Import the VM: File > Import Appliance


You can now start using the VM.

Some extra steps I did was adding a Shared folder and setup the Network connection

Once you add the Shared Folder open up a Terminal in your VM and type: "mount -t vboxsf Downloads /mnt"  (Downloads is my OSX Downloads folder and /mnt is my mount directory in VirtualBox), that makes it easier to transfer files to the VM.

Here's a screenshot once you run the VM:

The nice thing with this VM is that everything is ready to be used and it includes labs, so you can do some exercises too and there's even a reset script to put everything back how it was.

Exercises are available for: Database 12c (including JSON, XML DB, ...), SQL Developer, APEX, REST Data Services and Cloud Services.

Who said installing Oracle was hard? :)

Categories: Development

A blog on Oracle Standard Edition

Hemant K Chitale - Sun, 2015-01-18 08:40
Here's a blog on Oracle Standard Edition by Ann Sjokvist.

Categories: DBA Blogs

How to download a plugin from

Denes Kubicek - Sun, 2015-01-18 05:18
I am not sure how many times it has happened to me that I can't use my login for this site. Today, it is not possible to login into that application - again. I was trying to reset my password several times for both accounts I have there. I received the emails with a token, entered that token and after changing the password, nothing happens. I didn't even receive a message that the login isn't possible or wasn't successful. Simply nothing shows up. Maybe I am just not getting it and there is a step I am missing...

It is o.k. to track who downloads what and use the accounts for advertising. However, you should make sure your login and the password reset works. That is so simple.
Categories: Development

About Inside Higher Ed Selling Majority Stake

Michael Feldstein - Sun, 2015-01-18 01:20

Update 1/21: See link and blurb at bottom of post from new Editor’s Note at Inside Higher Ed.

Last week the Huffington Post ran an article by David Halperin breaking the news that the private equity firm Quad Partners had acquired a controlling interest in Inside Higher Ed.

Quad Partners, a New York private equity firm that is invested heavily in the for-profit college industry, and whose founder has aggressively opposed regulation of that troubled industry, has acquired a controlling stake in the respected trade publication Inside Higher Ed (IHE), which often reports on for-profit colleges and the policy disputes surrounding them. There has been no public announcement, but the Quad Partners website now lists Inside Higher Ed as one of its investments, among a range of education-related companies, including for-profit trade schools Beckfield College, Blue Cliff College, Dorsey Schools, Pacific College of Oriental Medicine, and Marinello Schools of Beauty.

Doug Lederman, one of IHE’s two top editors, confirmed to me that Quad purchased a majority interest in IHE in November.

Quad Partner James Tieng is now an IHE board member. Quad also owns the influential college admissions management company Noel-Levitz and other education technology companies that contract with colleges and universities — another sector that IHE covers.

The rest of the article then goes full conspiracy theory, building off the for-profit connection of both Quad Partners and its founder. Halperin seems to believe mere indirect association with for-profits is evil and compromising in and of itself rather than finding any changes or compromises in IHE coverage.

The bigger issue in my mind was described by Keith Button at Education Dive.

While the list of potential conflicts of interest in such a sale is long, the fact that the deal wasn’t announced and the potential news coverage issues weren’t publicly addressed up-front raises more questions.

This issue of disclosure was partially addressed in the original article:

“I would expect people to be watching us” in light of this purchase, says Lederman. “Our credibility is hugely important to us, and ultimately it will rise or fall on the nature and tenor of our coverage.” He says IHE will go on as before: “The proof will be in what we publish.” If there are significant references in IHE to specific Quad-owned companies, the publication will disclose the relationship.

In my mind, IHE made a serious mistake by not publicizing the acquisition back in November and issuing a blanket disclosure. I don’t fault them for selling the controlling stake in the company, especially given the lack of a paywall. But I do fault them for not realizing how the lack of disclosure created the opportunity for a advocate to publicly challenge them. It’s actually ironic to see a full-fledged advocate (Halperin writes extensively attacking the for-profit sector as part of his funding and openly calls himself an advocate) require 100% pure financial independence for IHE.

There are two types of disclosure that are relevant – a blanket disclosure announcing a key event such as the sale of the majority of company shares, proactively distributed and available; and article-specific disclosures if IHE articles reference companies tied to their owners. IHE seems to be relying on the latter, but their credibility will take a hit by not doing the former.

IHE was caught off guard by the Huffington Post article, and they seem to have quickly put up an Ownership Statement on the same day the article ran (Jan 14th).

Inside Higher Ed is an independent journalism organization. The journalistic independence is critical in ensuring the fairness and thoroughness of our higher education coverage.

Inside Higher Ed Inc. is owned by its three founders, other individual investors, and Quad Partners, a private equity firm that invests in the education space. Quad purchased a controlling share of Inside Higher Ed in November 2014 from a group of venture capital firms that invested in the company originally a decade earlier.

Owners of Inside Higher Ed stock who are not editors play no role in the editorial policies of the company.

The problem is the following:

  • This statement comes across as a reaction to Halperin – you got us – leading to the appearance that IHE had something to hide; and
  • IHE has done little to actually disclose this ownership, as the statement is only linked on the About Us page and Doug Lederman’s page (no articles or prominent placement of significant news event).

I read and research quite a bit of higher ed news and it took me a while to find this statement, despite the fact that I was specifically looking for information. With the current placement, very few people would have seen it.

This news is relevant, more for the Quad Partners ownership of Noel-Levitz than for their ownership of Marinello Schools of Beauty. Higher ed enrollment in the US has been declining the past 2 years, and this change is shaping up to be one of the biggest drivers of change initiatives for institutions and associated markets. There might be no other organization with more influential on enrollment management than Noel-Levitz. In the past 12 months Inside Higher Ed has written eight articles where Noel-Levitz plays an important role, and this prominent Fortune article profiling the company states:

Noel-Levitz might be the most influential force in higher education pricing that you’ve never heard of, empowering what’s become a three-stage, market-distorting game for college administrators.

Readers should know about the ownership connection given the importance of enrollment management and college pricing, and readers should not have to find this if and only if they read an article with direct references.

Do I believe that Quad Partners has or will change IHE coverage, especially on enrollment management and pricing? No. In my experience, IHE’s leadership and the reporters I’ve dealt with have been very ethical and honest. Furthermore:

Lederman says that at the insistence of IHE, the purchase agreement includes a clause that precludes Quad Partners from any involvement in editorial operations. IHE was launched by Lederman and two co-founders in 2004, with a modest investment from three Washington DC-area venture funds, including the owners of the lead generation company Double Positive. Those three investors, who sold their shares to Quad in November, also had no role in editorial operations, says Lederman.

IHE does a great job covering important stories in higher ed, including a watch dog role of exposing problems that arise. We need them to be trusted, and they should quickly correct the mistake. My unsolicited advise:

  • Write an article disclosing the sale and linking to the Ownership Statement – don’t make this information hard to find;
  • Quote a portion of the purchase agreement clause in the article to clarify their statement of editorial independence; and
  • Create a separate page of editorial policies.

Update 1/19: In a separate Education Dive post from the weekend:

A top editor of Inside Higher Ed said Friday that, in hindsight, he wished there had been more transparency about the sale of the publication’s controlling interest to a private equity firm that has invested heavily in for-profit education.

“We were founded without any support, then we had one set of investors and we had never said anything about them,” Scott Jaschik, an Inside Higher Ed founder and editor, told Education Dive. “In hindsight, I wish we had, because clearly this is of interest to people.” [snip]

“I guess I would just say to anyone who has questions, read us and read our coverage and call me if you think we’re doing anything that we shouldn’t,” he said.

Excellent work by Education Dive, by the way. As for IHE, I still think they would benefit from a blanket disclosure.

Update 1/21: Inside Higher Ed has now posted a full blanket disclosure note. Good for them.

Some of you may have seen some recent blog posts and discussion on Twitter or elsewhere about Inside Higher Ed Inc.’s ownership status. We wanted you to have more information directly from us. [snip]

In November 2014, Quad Partners, a private equity firm that invests in numerous companies in the education space, including some small for-profit colleges, bought a controlling interest in our company by purchasing shares of Inside Higher Ed Inc.’s stock from our previous investors.

Quad intends to help Inside Higher Ed expand its staff, extend its reach, and improve its coverage and services. Its goal is to help Inside Higher Ed do what it does better. And yes, like all investors, it wants to make money.

Owners of Inside Higher Ed Inc. stock who are not editors play no role in the editorial policies of the company. Quad acknowledged explicitly in its agreement to invest in Inside Higher Ed Inc. that it would be precluded from any involvement in editorial operations.

The post About Inside Higher Ed Selling Majority Stake appeared first on e-Literate.

Generate nested JSON from SQL with ORDS and APEX 5

Dimitri Gielis - Sat, 2015-01-17 17:30
When creating web applications, at some point you will need to interact with JSON. Either you consume JSON or you need to generate it to be able to use that cool widget you found.
A few years ago when I wrote about interacting and customising charts, XML was the data format. Today JSON is more common to use as it works so well with JavaScript. I needed to pass some nested JSON - here's an example: multiple customers have multiple orders and an order consists out of multiple items: 
What is the easiest way to generate that JSON data?
Luckily we have Oracle REST Data Services (ORDS), it literally is just one SQL statement!Here you go: 

Save and presto you're done :) Hit the TEST button and copy/paste the generated JSON in for example JSONLint to validate the output and yep we have valid JSON.

But that is not all - it gets even better. APEX 5 comes with a new package apex_json which contains so many goodies, really a wonderful piece of code. The same SQL statement I call with the package and again I get the JSON I need. It's slightly different from what ORDS generated - ORDS has an items tag surrounding the data, but again it's valid JSON.Note that APEX 4.2 has some JSON support too (apex_util.json_from_sql), but it doesn't go as far as the package you find in APEX 5.0 - for example the cursor syntax is not working there, but for straight forward JSON it does it job.

So this shows how easy it is to generate JSON these days. It has been different where we had to use the PL/JSON package (which was good too, but build-in is what I prefer).

And finally with Oracle Database 12c, and the JSON support straight in the database, consuming and querying JSON has been made so much easier, but that is for another post. If you can't wait, the link will bring you to the official 12c Database JSON doc.
Categories: Development

Cloud to Cloud ETL

Dylan's BI Notes - Fri, 2015-01-16 20:24
I found that Cloud to Cloud ETL is different from the regular ETL running in the on premise env in several ways: 1. Direct Database Access is not available 2. Complex / Nested Objects 3. Paging 4. Volume Restriction 5. File base approach 6. Data Streaming 7. Data Security / Protection 8. Change data capture […]
Categories: BI & Warehousing

Understanding the APEX url - passing variables to items and (interactive) reports

Dimitri Gielis - Fri, 2015-01-16 17:30
Did you know you can recognise an Oracle APEX application through the url?

Here's the syntax:


Let me give some examples:


The first part: depends on your configuration. I'm using https, my domain name is and I'm using the Oracle REST Data Services as connection to the database. This is a configuration in your webserver.

The next part is f?p= f is a procedure in the database with as paramaters (p=). f comes from flow - once APEX was called "Project Flows".

209 is my application id, 12 is my page id and 12351378808570 is my session
The first part is the same, but now as request I've CSV, page 12 is an Interactive Report and the fastest way to see your IR data as CSV is to give CSV as the request in the url
I changed the request again to the name of a saved Interactive Report (Alternative) IR_REPORT_: so the page goes straight to that layout of the report.

Here I'm calling the page in DEBUG mode
In the clear cache section you have a couple of options, specifying the below will clear the cache for : - APP: whole application- SESSION: current user session- RIR: reset interactive report to primary report- CIR: reset to primary report but with custom columns- RP: reset pagination- 12: page 12- P12_ID: item P12_ID
Here I pass a variable with the url, we'll fill P12_ID with the value 1You can use comma separated list of items and values for example: P12_ID,P12_NAME:1,DIMITRI
If you want to filter an interactive report you can call IR??_:In the above case we set the customer last name = Dulles.There are many other parameters: IREQ_ (equal), LT_ (less), IRLTE_ (less or equal), IRGT_, IRGTE_ (greater or equal), IRLIKE_ (like), IRN_ (null), IRNN_ (not null), IRC_ (contains), IRNC_ (not contains).
In this case we run the page in Printer-friendly mode
This url will generate a trace file on the system for this request.
You find some more information in the APEX Documentation or you can try online at
One remark: if you enable Session State Protection (SSP) you might not be able to call the parameters like this as it requires a checksum at the end which you have to calculate for example by using apex_util.prepare_url().

And finally, if you want more readable urls, some people did some posts about that, here's one of Peter.
Categories: Development

Free Webinar "Oracle Exadata & In-Memory Real-World Performance"

Randolf Geist - Fri, 2015-01-16 16:38
It's webinar time again.

Join me on Wednesday, January 28th at for a session based on a real world customer experience.

The session starts at 3pm UK (16:00 Central European) time. The webinar is totally free and the recording will made available afterwards.

Here's the link to the official landing page where you can register and below is the official abstract:
AbstractAfter a short introduction into what the Oracle Exadata Database Machine is, in this one-hour webinar I will look at an analysis of different database query profiles that are based on a real-world customer case, how these different profiles influence the efficiency of Exadata’s “secret sauce” features, as well as the new Oracle In-Memory column store option. Based on the analysis different optimization strategies are presented along with lessons learned.

If you are in Cleveland don't miss our January 23 2015 NEOOUG meeting!

Grumpy old DBA - Fri, 2015-01-16 14:09
Please join us next friday for our 1st 2015 regular meeting. Mark Rabne our resident Oracle technical geek will be taking us through major 2014 Oracle technology and application announcements. Kind of a recap of Oracle Open World 2014 major items plus some additional ones after that.

It's the usual deal at the Rockside Road Oracle office so free lunch at noon and networking opportunities. Meeting starts at 1 pm.

Our March meeting will be Big Data ish related ( and we have a great announcement coming up on a workshop for our May GLOC 2015 conference ).
Here is the info on Jan 23 2015 meeting

I hope to see you there!
Categories: DBA Blogs

ERROR: The following required ports are in use: 6801 : WLS OAEA Application Port

Vikram Das - Fri, 2015-01-16 13:55
Anil pinged me today when his adop phase=fs_clone failed with this error message:

ERROR: The following required ports are in use:
6801 : WLS OAEA Application Port
Corrective Action: Free the listed ports and retry the adop operation.

Completed execution :

Inside _validateETCHosts()...

This is a bug mentioned in the appendix of article: Integrating Oracle E-Business Suite Release 12.2 with Oracle Access Manager 11gR2 (11.1.2) using Oracle E-Business Suite AccessGate (Doc ID 1576425.1)
Bug 19817016The following errors are encountered when running fs_clone after completing AccessGate and OAM integration and after completing a patch cycle:

Checking  WLS OAEA Application Port on aolesc11:  Port Value = 6801
RC-50204: Error: - WLS OAEA Application Port in use: Port Value = 6801

ERROR: The following required ports are in use:
6801 : WLS OAEA Application Port
Corrective Action: Free the listed ports and retry the adop operation.

Stop the oaea managed server on the run file system before performing the fs_clone operation, immediately after the accessgate deployment.

This issue will be addressed through Bug 19817016.
If you read the bug:
Bug 19817016 : RUNNING ADOP FS_CLONE FAILS DUE TO PORT CONFLICT BETWEEN RUN AND PATCH EDITIONClick to add to FavoritesEmail link to this documentPrintable PageTo BottomTo Bottom Bug Attributes TypeB - DefectFixed in Product VersionSeverity2 - Severe Loss of ServiceProduct Version12.2.4Status11 - Code/Hardware Bug (Response/Resolution)Platform226 - Linux x86-64Created14-Oct-2014Platform VersionORACLE LINUX 5Updated02-Dec-2014Base BugN/ADatabase Version11.2.0.3Affects PlatformsGenericProduct SourceOracleKnowledge, Patches and Bugs related to this bug Related Products LineOracle E-Business SuiteFamilyApplications TechnologyAreaTechnology ComponentsProduct1745 - Oracle Applications Technology Stack
Hdr: 19817016 FSOP 12.2.4 PRODID-1745 PORTID-226

*** 10/14/14 11:58 am ***
Service Request (SR) Number:

Problem Statement:
Running fs_clone after completing EBS and OAM integration and after
completing a patch cycle results in fs_clone failing with the following

Checking  WLS OAEA Application Port on aolesc11:  Port Value = 6801
RC-50204: Error: - WLS OAEA Application Port in use: Port Value = 6801

ERROR: The following required ports are in use:
6801 : WLS OAEA Application Port
Corrective Action: Free the listed ports and retry the adop operation.

Detailed Results of Problem Analysis:
The problem is due to the newly added managed server port being the same for
both the run and patch edition.  Going back to the sequence of steps and
tracking the port assignment, it showed the following:

- deploy accessgate on patch
Creates managed server - oaea_server1:6801
This is the default port and doing this to the patch edition...

fs2 - run -> 6801 port
fs1 - patch -> 6801 port

- complete OAM registration
- close patching cycle
- cutover
- after cutover, SSO is working

fs1 - run -> 6801 port
fs2 - patch -> 6801 port

- fs_clone -> fails due to both run(fs1) and patch(fs2) referencing the same
port 6801

Configuration and Version Details:
WG -
EAG - 1.2.3
WT -

EBS 12.2.4 w/ AD/TXK delta 5

Steps To Reproduce:
As part of the EBS integration w/ OAM, we add a managed server for use as the
EBS AccessGate (EAG) to the existing WLS in EBS.  There is an option to do
this to both run edition, as well as the patch edition during an active patch
cycle.  In this case the latter was done.  Here is a summary of the steps

1. Start patch cycle
2. Integrated OID and EBS
3. Cutover
4. Confirmed OID provisioning is working
5. Start patch cycle
6. Apply pre-req EBS patches for OAM
7. Proceed w/ OAM integration on patch file system
8. Cutover
9. Confirmed SSO/OAM is working
10. Run fs_clone -> this is where the issue appears

Additional Information:
The workaround here is to stop the oaea_server1 managed server operating in
the run edition on port 6801, and then re-running fs_clone.  Once this is
done, fs_clone completes and the patch edition now operates on port 6802 for
the same managed server.

For A Severity 1 Bug: Justification and 24x7 Contact Details:

*** 10/14/14 01:19 pm ***
*** 10/16/14 07:05 am ***
*** 10/16/14 07:05 am ***
*** 10/17/14 01:47 am ***
*** 10/17/14 01:49 am ***
*** 10/17/14 01:57 am ***
*** 10/17/14 08:47 am ***
*** 10/23/14 12:16 am ***
*** 10/23/14 12:17 am ***
*** 10/26/14 10:07 pm ***
*** 10/27/14 10:06 pm ***
*** 10/27/14 10:09 pm ***
*** 10/30/14 10:40 pm ***
*** 10/30/14 10:49 pm ***
*** 10/30/14 10:49 pm ***
*** 11/05/14 04:30 pm ***
*** 11/05/14 04:30 pm ***
*** 11/06/14 10:59 am ***
*** 11/17/14 09:20 pm ***
*** 12/02/14 12:36 am ***
*** 12/02/14 07:26 pm ***

Till a patch is made available, you need to shutdown the oaea managed server and restart fs_clone. So much for keeping all services online and the promise of no outage during fs_clone.

Categories: APPS Blogs

January 21: Swedbank HCM Cloud Reference Forum

Linda Fishman Hoyle - Fri, 2015-01-16 10:57

Join us for an Oracle HCM Cloud Customer Reference Forum on Wednesday, January 21, 2015, at 9:00 a.m. PT / 12:00 p.m. ET. You will hear Fredrik Rexhammar, Business Information Officer (BIO) in Group HR at Swedbank, discuss the company’s initiative to replace its in-house HR system with a cloud-based HR solution that included Core HR, Compensation, Talent Review and Performance & Goal Management. The goal was to better manage its people and support its complex compensation model.

Fredrik will talk about Swedbank’s selection process for new HR software, its implementation experience with Oracle HCM Cloud, and the expectations and benefits of its new modern HR system.

Swedbank is a modern bank firmly rooted in Swedish savings bank history. It is an inclusive bank with approximately 20,000 employees, 8 million private customers, and more than 600,000 corporate and organizational customers.

You can register now to attend the live Forum on Wednesday, January 21, 2015 at 9:00 a.m. PT / 12:00 p.m. ET and learn more from Swedbank directly.

The Database Protection Series– Common Threats and Vulnerabilities- Part 2

Chris Foot - Fri, 2015-01-16 10:35

This is the third article of a series that focuses on database security. In my introduction, I provide an overview of the database protection process and what is to be discussed in future installments. In last month’s article, we began with a review of the various database vulnerabilities and threat vectors we need to address. In this article, we’ll finish our discussion of the most common threats and vulnerabilities. In the next installment of this series, we’ll take a look at the database vulnerability analysis process. We’ll begin by learning how to perform an initial database vulnerability assessment. In addition, we’ll discuss the importance of performing assessments on a regular basis to ensure that no new security vulnerabilities are introduced into our environment.

Unsecured Non-Database Files

It’s fairly obvious that, as DBAs, our focus will be on securing our sensitive database data stores. However, during the course of normal processing, the database often interacts with flat files and other objects that may contain sensitive data that needs to be secured. For our review, we’ll classify the data as we have always done – input or output. Input data that the database ingests or output data that the database generates.

Databases can receive data from a host of different mechanisms:

  • The database can retrieve data directly from other databases or be sent data from those systems. Database links in Oracle and linked servers in Microsoft SQL Server are often implemented to share data. If your sensitive database can be accessed using these features, you will need to take the additional steps required to secure those access mechanisms. Both Oracle and Microsoft have made improvements to the security of external database links, but the level of protection depends on how they are implemented. There will be times when this will require you to secure multiple database targets. It will broaden the scope of the security activities you will be required to perform, but the sensitive database data store will be vulnerable until you do.
  • Input files that are used by the database product’s load or import utility. DBAs can be pretty creative about using the database’s inherent toolsets to ingest data into their databases or transfer it to other systems. You will need to identify the data they contain and secure these files accordingly.
  • ETL products that extract, transform and load data into other data stores. ETL products are able to access data from a variety of sources, transform it into a common format and move it to the target destination. Each ETL product uses different strategies to collect and process the data. Identify what work files are used, how the product is secured and the sensitivity of the data that is being accessed as well as sent to other systems.
  • Middleware products that transfer data between disparate systems. Like ETL products, you will identify the sensitivity of the input and output, work files produced and how the product is secured.

Databases also have the ability to produce various forms of output:

    • Application report files that are either stored on disk or sent directly to printers. An in-depth review of the application report output the database generates will need to be performed. If the data being reported on contains sensitive data elements, you will need to determine if the printers are in secure locations, the personnel that have access to them and if the reports are stored on disk, how the storage is secured.
    • Flat file output generated by the database. Besides application reports that we just discussed, there are numerous methods that technicians use to generate flat file output from the database data store. Oracle external tables, export files, custom coded output files generated by developers and DBAs during debugging sessions, and system trace execution all have the capability to expose data. Everything from the spool command in SQL*PLUS to the PL/SQL util_file needs to be evaluated. A best practice is to provide a secure set of folders or directories in the operating system running the database and to not allow non-secure destinations to be utilized.
    • Database product and third-party database backup files. All leading database products provide the functionality to encrypt database backup files as do most third-party offerings. An analysis is required to determine how the data is encrypted, at what point in the process is it encrypted and how is the encryption mechanism secured.
Unsecured Data Transmissions

One of the more challenging tasks will be to identify the mechanisms used to transmit database data throughout the organization. You need to determine what’s being transmitted over the network wire as well as the wireless. One of the constraints I have in this series is that I can’t get into the details that would allow you to secure your connections to the target database. That’s far beyond the scope and intent of this series of articles. The series’ intent is to be a general overview of database protection best practices. All major database manufacturers provide a wealth of documentation on how to secure the communication mechanisms, encrypt data transfers as well as secure the operating system the database runs on. If you are serious about protecting data transmissions, a thorough review of vendor documentation is essential. In addition, you’ll need to become quite good friends with your network engineers as their assistance and expertise will be required.

Access Tools

Databases can be accessed using a variety of tools. That’s the benefit of using a database; you can interact with it using everything from Excel to a sophisticated enterprise-wide program suite. You will need to work with end-users, application developers and your security team to determine what happens to that data after it is retrieved from the database. For example, if a business user accesses sensitive data using Excel, where do they store the spreadsheets? The solution is to inter-weave the proper security procedures, constraints and end-point permissions to safeguard the data.

Application Hacks – SQL Injection and Buffer Overflows

SQL injection occurs when an attacker sends commands to the database by attaching it to web form input. The intent is to grant themselves privileges or access the data directly. In the past, hackers were required to manually attach the malicious code to the statement. There are hacking toolkits available now that allow them to automate the process. SQL injection attempts to confuse the database so it is unable to distinguish between code and data.

Here’s a couple of very rudimentary examples of SQL injection (as processed by the database):

SELECT name, address, SSN FROM employees WHERE lastname=”FOOT” or “x=x”

The program wants to return names, addresses and social security numbers for a specific employee. The attached   or “X=X” returns as true and allows the hacker to return all employees’ information.

SELECT name, address FROM employees where lastname=”FOOT” ;SELECT * from employees;

Most databases allow the use of delimiters to string statements together. In this case, instead of selecting just the name and address, the SQL statement injected at the end dumps the entire contents of the table.

Statements that use parameters as input, as opposed to using dynamic statements that generate the input values during execution as well as the use of stored procedures containing SQL code, prevent hackers from attaching malicious code to the statements. For example, in the or “x=x” example used above in a SQL statement using parameters as input (lastname = @lname), the database would look for the “x=x”value literally and fail to successfully process the statement.

A buffer overflow, also called a buffer overrun, occurs when the data being input to the buffer overflows into adjacent memory. The volume of input exceeds buffer size. This is a fairly complex hack, requiring a strong knowledge of the programming language using the buffer. The ease of performing the buffer overflow attack is based on the application language used, how the software is protected and how the developers write the code used to process data. By carefully coding input to a web application, the attacker is able to execute the code contained in the overflow. The hacker issues the commands to overwrite the internal program structures and then executes the additional code. The most common strategies of this hack are to crash the program, corrupt the data or have the code stored in the overflow execute malicious code to access data or grant authorities.  You’ll quickly find a listing of languages on the web that are vulnerable to buffer overflows.  Some are far more vulnerable than others.

I’ll be devoting an article to ongoing database security strategies. One of the key steps of that process will be to educate developers, DBAs, network engineers and OS administrators on how security best practices can be utilized to harden the application ecosystem. Although DBAs may feel that preventing SQL injection, buffer overflows and other application attacks are the responsibility of the development teams, the DBA must take an active role in their protection.

Privilege Abuse

Privilege abuse can be broken down into the following two categories:

  • Intentional Abuse – An example of an intentional abuse of privileges would be a database administrator, senior level application developer or business user accessing data they shouldn’t.
  • Non-Intentional Abuse- The user, in error, accesses sensitive data. The data is exposed unintentionally. Data stored in an unsecure directory, on a laptop that is subsequently stolen or on a USB drive, for example. The list of potential vulnerabilities is pretty much endless.

Disgruntled employees, especially disgruntled ex-employees, and those with just a general criminal inclination are common offenders. To safeguard sensitive data stores, the organization can ensure that background and credit checks are performed on new employees, only the privileges necessary for the employee to perform their work are granted and security credentials are immediately revoked upon termination for any reason. Once again, we will focus more on this topic in upcoming articles of this series.

Audit Trails (or lack thereof)

Auditing is not an alerting mechanism. Auditing is activated, the data is collected and reports are generated that allow the various activities performed in the database to be analyzed for the collected time period.

Identifying a data breach after the fact is not database protection. It is database reporting. To protect databases we are tasked with safeguarding, the most optimal solution is to alert in real time or alert and stop the unwarranted data accesses from occurring. We’ll discuss the various real-time breach protection products during our discussion on security monitoring products.

You will need to be very scientific when selecting the level of auditing to perform. Too much will lead to an excessive use of finite system resources. Auditing can place a significant impact on the system and database. Too little will give you the potential of missing critical security events that have occurred. An in-depth analysis of who and what is to be audited is an absolute requirement.

Auditing just the objects containing sensitive data elements and users with high levels of privileges are good starting points. Leading database vendors like Oracle, Microsoft and IBM all have advanced auditing features that reduce auditing’s impact on the system by transferring it to other components. In addition, most vendors offer add-on products that improve auditing’s capabilities at an additional price.

Auditing plays a critical role in database security, especially to those organizations that don’t have a real-time breach protection solution. Properly populated audit trails allow administrators to identify fraudulent activities, and the audit reports are often requirements for the various industry regulations including SOX, HIPAA and PCI.

Poor Security Strategies, Controls and Education

The two critical components that play a significant role in the database protection process are education and awareness; the awareness that your systems are vulnerable to breaches and not putting your head in the sand thinking that your systems aren’t potential targets. Pay a quick visit to the various websites that record data breaches. Although you will see information stating that organizations storing massive numbers of credit cards, like large retailers, are the most popular targets, you will also find that no organization is immune. Breaches occur daily, and all organizations are targets.

According to the Symantec 2014 Breach Investigations Report, companies with less than 250 employees accounted for 31% of all reported attacks. Visa reports an even more alarming statistic: 85% of all Visa card breaches occur at the small to medium-sized business level. The National Cyber Security Alliance SMB report states that 60% of small businesses close their doors within 6 months of a data breach.

When sensitive data is breached for any reason, it can threaten the survivability of your organization. The financial impact of the breach is not the only issue that affects companies that are victims of unauthorized data access. Loss of customer goodwill, bad press and legal penalties (lawsuits, fines, etc.) must also be considered.

After you realize the importance of protecting your sensitive database data stores, you need to transfer that awareness to your entire organization. DBAs can’t protect their environments on their own. All IT groups must become actively involved. Management buy-in is crucial. Expenditures on products and personnel may need to be made to improve the level of protection required to safeguard sensitive data assets. The organization has to commit the resources necessary to generate a well thought out enterprise-wide security strategy that requires that the appropriate level of controls be in place and audited regularly. If you don’t, I’ll be reading about your shop in the next data breach newsletter.

Learning how to secure your environments is like learning anything else. You will need to commit time to learning various security best practices. At an enterprise level, industry regulatory requirements like SOX, HIPAA and PCI DSS provide a laundry list of protective controls. Download the compliance control objectives. It will give your organization an excellent starting point. In RDX’s case, we decided to become PCI DSS and HIPAA compliant. PCI DSS contains a little over 300 separate security objectives and information about how those objectives are to be audited to demonstrate proof of compliance.

In the next installment of this series, we’ll take a look at the database vulnerability analysis process.

Thanks for reading.

The post The Database Protection Series– Common Threats and Vulnerabilities- Part 2 appeared first on Remote DBA Experts.

Chick-fil-A joins the payment card breach club [VIDEO]

Chris Foot - Fri, 2015-01-16 09:26


Hi, welcome to RDX. Given the number of payment card breaches that have occurred over the past couple of years, it’s no surprise that a fast food joint recently joined the list of companies that have been affected.

According to eSecurity Planet, Chick-Fil-A recently noted that a few of its restaurants have experienced unusual credit and debit card activity. Additional reports suggest that Chick-Fil-A is the link to approximately 9,000 instances of payment card loss. It’s possible that the perpetrators managed to steal payment card numbers from Chick-Fil-A’s databases, but analysts are still investigating.

First, it may be appropriate for Chick-Fil-A as well as other retailers to use tokenization, which will prevent hackers from accessing payment data. In addition, setting up a database security monitoring solution will allow specialists to receive alerts the minute a server records suspicious activity.

Thanks for watching!

The post Chick-fil-A joins the payment card breach club [VIDEO] appeared first on Remote DBA Experts.

Did you forget to run

Laurent Schneider - Fri, 2015-01-16 09:08

Not easy to detect, and depending on the product (agent/database), it may have only limited side effects.

Like external jobs not running, operating systems statistics not collected.

But it is not always easy to diagnose.

For instance if you patch from OMS 12cR2 to 12cR3, and you run the only in 12cR2, they are very few statistics missing (one is the OS_STORAGE_ENTITY).

Running the doesn’t generate a log file or an entry in the inventory.

To check if it was executed, check what it is supposed to do. It is a bit different in each version. One think it always does is changing the ownership to root and set the sticky bit for a few binaries. For the database, this is done in sub-scripts called (10g) or (11g/12c).

eval ls -l $(find $ORACLE_HOME -name "rootadd*sh" -exec awk '$1="$CHOWN"&&$2=="root"{print $3}' {} \;|sort -u)

-rwsr-x--- root dba .../product/11.2.0/db_4/bin/extjob
-rwsr-x--- root dba .../product/11.2.0/db_4/bin/jssu
-rws--x--- root dba .../product/11.2.0/db_4/bin/nmb
-rws--x--- root dba .../product/11.2.0/db_4/bin/nmhs
-rws--x--- root dba .../product/11.2.0/db_4/bin/nmo
-rwsr-x--- root dba .../product/11.2.0/db_4/bin/oradism
-rw-r----- root dba ...11.2.0/db_4/rdbms/admin/externaljob.ora

If the ownership is root, you definitely did run the

On the 12c agent, there is a FULL_BINARY_LIST variable that point to list of root binaries in sbin

cd $AGENT_HOME/../../sbin

-rws--x--- root dba nmb
-rws--x--- root dba nmhs
-rws--x--- root dba nmo

If all files exist and belong root, it looks like you did run the

Spatial space

Jonathan Lewis - Fri, 2015-01-16 07:00

One thing you (ought to) learn very early on in an Oracle career is that there are always cases you haven’t previously considered. It’s a feature that is frequently the downfall of “I found it on the internet” SQL.  Here’s one (heavily paraphrased) example that appeared on the OTN database forum a few days ago:

select table_name,round((blocks*8),2)||’kb’ “size” from user_tables where table_name = ‘MYTABLE';

select table_name,round((num_rows*avg_row_len/1024),2)||’kb’ “size” from user_tables where table_name = ‘MYTABLE';

The result from the first query is 704 kb,  the result from the second is 25.4 kb … fragmentation, rebuild, CTAS etc. etc.

The two queries are perfectly reasonable approximations (for an 8KB block size, with pctfree of zero) for the allocated space and actual data size for a basic heap table – and since the two values here don’t come close to matching it’s perfectly reasonable to consider doing something like a rebuild or shrink space to reclaim space and (perhaps) to improve performance.

In this case it doesn’t look as if the space reclaimed is likely to be huge (less than 1MB), on the other hand it’s probably not going to take much time to rebuild such a tiny table; it doesn’t seem likely that the rebuild could make a significant difference to performance (though apparently it did), but the act of rebuilding might cause execution plans to change for the better because new statistics might appear as the rebuild took place. The figures came from a test system, though, so maybe the table on the production system was much larger and the impact would be greater.

Being cautious about wasting time and introducing risk, I made a few comments about the question –  and learned that one of the columns was of type SDO_GEOMETRY. This makes a big difference about what to do next, because dbms_stats.gather_table_stats() doesn’t process such columns correctly, which results in a massive under-estimate for the avg_row_len (which is basically the sum of avg_col_len for the table). Here’s an example (run on 12c, based on some code taken from the 10gR2 manuals):

drop table cola_markets purge;

CREATE TABLE cola_markets (
  mkt_id NUMBER,
  name VARCHAR2(32),
  shape SDO_GEOMETRY);

INSERT INTO cola_markets VALUES(
    2003,  -- two-dimensional polygon
    SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
    SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
          -- define rectangle (lower left and upper right) with
          -- Cartesian-coordinate data

insert into cola_markets select * from cola_markets;

execute dbms_stats.gather_table_stats(user,'cola_markets')
	avg_row_len, num_rows, blocks,
	round(avg_row_len * num_rows / 7200,0) expected_blocks
from user_tables where table_name = 'COLA_MARKETS';

analyze table cola_markets compute statistics;
	avg_row_len, num_rows, blocks,
	round(avg_row_len * num_rows / 7200,0) expected_blocks
from user_tables where table_name = 'COLA_MARKETS';

If you care to count the number of times I execute the “insert as select” it’s 10, so the table ends up with 2^10 = 1024 rows. The 7,200 in the calculated column converts bytes to approximate blocks on the assumption of 8KB blocks and pctfree = 10. Here are the results following the two different methods for generating object statistics:

PL/SQL procedure successfully completed.

----------- ---------- ---------- ---------------
         14       1024        124               2

Table analyzed.

----------- ---------- ---------- ---------------
        109       1024        124              16

Where does the difference in Expected_blocks come from ? (The Blocks figures is 124 because I’ve used 1MB uniform extents – 128 block – under ASSM (which means 4 space management blocks at the start of the first extent.)

Here are the column lengths after the call to dbms_stats: as you can see the avg_row_len is the sum of avg_col_len.

select column_name, data_type, avg_col_len
from   user_tab_cols
where  table_name = 'COLA_MARKETS'
order by

COLUMN_NAME          DATA_TYPE                AVG_COL_LEN
-------------------- ------------------------ -----------
MKT_ID               NUMBER                             3
NAME                 VARCHAR2                           7
SHAPE                SDO_GEOMETRY
SYS_NC00008$         NUMBER                             0
SYS_NC00004$         NUMBER                             4
SYS_NC00005$         NUMBER                             0
SYS_NC00006$         NUMBER                             0
SYS_NC00007$         NUMBER                             0

The figures from the analyze command are only slightly different, but fortunately the analyze command uses the row directory pointers to calculate the actual row allocation, so picks up information about the impact of inline varrays, LOBs, etc. that the dbms_stats call might not be able to handle.

COLUMN_NAME          DATA_TYPE                AVG_COL_LEN
-------------------- ------------------------ -----------
MKT_ID               NUMBER                             2
NAME                 VARCHAR2                           6
SHAPE                SDO_GEOMETRY
SYS_NC00008$         NUMBER                             1
SYS_NC00004$         NUMBER                             3
SYS_NC00005$         NUMBER                             1
SYS_NC00006$         NUMBER                             1
SYS_NC00007$         NUMBER                             1

As a basic reminder – whenever you do anything slightly non-trivial (e.g. something you couldn’t have done in v5, say) then remember that all those dinky little script things you find on the Internet might not actually cover your particular case.

Oracle Audit Vault and Compliance Reporting

The Oracle Audit Vault has seeded reports for the following compliance and legislative requirements – no additional license is required.

  • Payment Card Industry (PCI)
  • Sarbanes-Oxley Act (SOX)
  • Gramm-Leach-Bliley Act (GLBA)
  • Health Insurance Portability and Accountability Act (HIPAA)
  • United Kingdom Data Protection Act (DPA)

For each compliance statue, following table lists the included reports available –

Compliance Report


Activity Overview

Digest of all captured audit events for a specified period of time

All Activity

Details of all captured audit events for a specified period of time

Audit Settings Changes

Details of observed user activity targeting audit settings for a specified period of time

Created Stored Procedures

Stored procedures created within a specified period of time

Data Access

Details of audited read access to data for a specified period of time

Data Modification

Details of audited data modifications for a specified period of time

Database Schema Changes

Details of audited DDL activity for a specified period of time

Deleted Stored Procedures

Stored procedures deleted within a specified period of time

Entitlements Changes

Details of audited entitlement related activity for a specified period of time

Failed Logins

Details of audited failed user logins for a specified period of time

New Stored Procedures

Latest state of stored procedures created within a specified period of time

Secured Target Startup and Shutdown

Details of observed startup and shutdown events for a specified period of time

Stored Procedure Activity Overview

Digest of all audited operations on stored procedures for a specified period of time

Stored Procedure Modification History

Details of audited stored procedure modifications for a specified period of time

User Login and Logout

Details of audited successful user logins and logouts for a specified period of time

If you have questions, please contact us at

Reference Tags: AuditingComplianceSarbanes-Oxley (SOX)PCIHIPAAOracle Audit Vault
Categories: APPS Blogs, Security Blogs

The info in OTHER_XML of view DBA_HIST_SQL_PLAN

Marco Gralike - Fri, 2015-01-16 04:04
I had some time to spend, killing time, and thought about something that was “on…