Feed aggregator

Why A Brand New Index Might Benefit From An Immediate Coalesce (One Slip)

Richard Foote's Blog - Mon, 07/06/2015 - 07:58
A recent question on the OTN Forums Reg: Index – Gathering Statistics vs. Rebuild got me thinking on a scenario not unlike the one raised in the question where a newly populated index might immediately benefit from a coalesce. I’ve previously discussed some of the pertinent concepts such as how index rebuilds can make indexes bigger, not smaller […]

Make even more of UKOUG Tech15: APEX 5.0 UI Training - Dec 10th in Birmingham

Dimitri Gielis Blog - Tue, 06/30/2015 - 06:48

APEX 5.0 has been released this spring. People who have already spent some time on this new version know this version is packed with new features aimed to make APEX developers even more productive, like the Page Designer.
Another striking new subset of features is aimed at creating better looking user interfaces for your APEX applications in an easy and maintainable way. The definition of user interface components in APEX 5.0 is very different to what we're used to. For example there is a new Universal Theme with Template Options and a Theme Roller. To get you up and running with this new toolset as quickly as possible, Dimitri Gielis of APEX R&D and Roel Hartman of APEX Consulting have joined forces and set up a one day course fully aimed at APEX 5.0 UI. So if you want to know not only how to use the new Theme, but also how to modify it to fit your needs, this is the event you should attend!
The training will be at the Jury’s Inn in Birmingham (UK) on Thursday Dec 10 - so conveniently immediately after the UKOUG Tech15 conference.More information and registration see www.apextraining.eu
If you are from another country and think this training should be available in your country as well, please contact us - then we'll see what we can do!

Quiz Time. Why Do Deletes Cause An Index To Grow ? (Solution)

Richard Foote's Blog - Mon, 06/29/2015 - 06:27
OK, time to reveal how a couple of simple deletes can cause an index to double in size. If we go back and look at the tree dump before the delete operation: —– begin tree dump branch: 0x180050b 25167115 (0: nrow: 19, level: 1) leaf: 0x180050c 25167116 (-1: row:540.540 avs:4) leaf: 0x180050d 25167117 (0: row:533.533 […]

Quiz Time. Why Do Deletes Cause An Index To Grow ? (Up The Hill Backwards)

Richard Foote's Blog - Thu, 06/25/2015 - 07:02
OK, time for a little quiz. One of the things I’ve seen at a number of sites is the almost fanatical drive to make indexes as small as possible because indexes that are larger than necessary both waste storage and hurt performance. Or so the theory goes …   :) In many cases, this drives DBAs to […]

Empty Leaf Blocks After Rollback Part II (Editions of You)

Richard Foote's Blog - Wed, 06/24/2015 - 07:35
In my last post, I discussed how both 1/2 empty and totally empty leaf blocks can be generated by rolling back a bulk update operation. An important point I made within the comments of the previous post is that almost the exact scenario would have taken place had the transaction committed rather than rolled back. A […]

Empty Leaf Blocks After Rollback Part I (Empty Spaces)

Richard Foote's Blog - Tue, 06/23/2015 - 06:09
There’s been an interesting recent discussion on the OTN Database forum regarding “Index blank blocks after large update that was rolled back“. Setting aside the odd scenario of updating a column that previously had 20 million distinct values to the same value on a 2 billion row table, the key questions raised are why the blank index leaf blocks […]

Index Tree Dumps in Oracle 12c Database (New Age)

Richard Foote's Blog - Mon, 06/22/2015 - 05:56
I’ve previously discussed Index Tree Dumps but I’ve recently found a nice little improvement that’s been introduced in Oracle Database 12c. Let’s begin by creating a little table and index: To generate an Index Tree Dump, we first need the OBJECT_ID of the index: And then use it to generate the Index Tree Dump: Previously, an […]

Indexing and Transparent Data Encryption Part III (You Can’t Do That)

Richard Foote's Blog - Tue, 06/16/2015 - 06:28
In Part II of this series, we looked at how we can create a B-Tree index on a encrypted column, providing we do not apply salt during encryption. However, this is not the only restriction with regard to indexing an encrypted column using column-based encryption. If we attempt to create an index that is not a […]

Fixing Super LOV in Universal Theme

Dimitri Gielis Blog - Thu, 05/21/2015 - 20:08
When you migrate to APEX 5.0 and the Universal Theme you might see that some plugins are not behaving correctly anymore. In this post I'll discuss the Enkitec Modal LOV plugin.

When I ran the plugin in my APEX 5.0 app with the Universal Theme it looked like this:


There's too much space in the search bar and the close button is not showing up with an icon.

Here're the steps I did to fix it. First you need to download the css file of the style you use and the js file from the plugin in Shared Components. I use the smoothness.css style most of the times, so I'll use that as an example.

To fix the close icon, add !important to the png:

.ek-ml .ui-state-default .ui-icon {
background-image: url(ui-icons_888888_256x240.png) !important;
}

Note: you can do that for all those png on line 467 till 489.

To fix the height, add following css to smoothness.css:

.superlov-button-container {
  height:50px;
}

And finally in enkitec_modal_lov.min.js change the height of the searchContainer from a dynamic height (r) to 0px:
$searchContainer.css("height","0px")
Next upload those files again to the plugin.
When you run the plugin it should give you this result:

Now the bigger question is; do we still need that plugin? In APEX 5.0 there're native Modal Pages, so you could create an Interactive Report and set the page as a Modal Page. Next you can hook that up to a button or link and you've just build your own Modal LOV.
I still like to use the plugin at the moment (as it's just one item on the page), but it could use a refresh to make it look nicer and more inline with Universal Theme.
Wonder what you think - would you build your own Modal LOV in APEX 5.0 or would you still prefer to use a plugin? 

Indexing and Transparent Data Encryption Part II (Hide Away)

Richard Foote's Blog - Wed, 05/20/2015 - 08:03
In Part I, I quickly ran through how to setup an encrypted tablespace using Transparent Data Encryption and to take care creating indexes outside of these tablespaces. Another method of encrypting data in the Oracle database is to just encrypt selected columns. Although the advantage here is that we can just encrypt sensitive columns of interest (and that the […]

2 minute Tech Tip: Working with JSON in APEX

Dimitri Gielis Blog - Tue, 05/19/2015 - 22:30
On Monday Bob Rhubart did a video call with me in his series of 2MTT (2 Minute Tech Tip) on YouTube. You find my 2MMT here.

I talked about using JSON and APEX and gave two examples were we use it.
In previous blog posts I gave more details on those techniques. Here's a quick overview:

Change first day of week in APEX 5.0 Calendar

Dimitri Gielis Blog - Tue, 05/19/2015 - 06:52
APEX 5.0 comes with a new calendar region, which is way nicer than the previous calendar in APEX. It has more features, looks better and is also responsive. Behind the scenes you'll see the calendar region is based on Full Calendar.


In Belgium we use Monday as first day of the week, whereas in the US they seem to use Sunday as start of the week in the calendar overview. I've been integrating Full Calendar before, so I knew that library had an option to set the first day of the week. You could either specify an option called firstDay and set that to 1, or you could change the language, and depending the language it would adjust the start day of the week.

In APEX 5.0 I looked for that option, but there's not a specific attribute to set the first day of the week, instead it's based on the language of your application. If you go to Shared Components > Globalization Attributes by default it's set to en, which has Sunday as start of the week. If you set it to en-gb it will have Monday as start of the week.


I searched some time to find how to do it, so hopefully this post will help others to find it more easily. Thanks to Patrick for sharing the way it was implemented.

Indexing and Transparent Data Encryption Part I (The Secret Life of Arabia)

Richard Foote's Blog - Tue, 05/19/2015 - 05:42
Database security has been a really hot topic recently so I thought I might write a few posts in relation to indexing and Transparent Data Encryption (TDE) which is available as part of the Oracle Advanced Security option. To protect the database from unauthorized “backed-door” accesses, the data within the actual database files can be encrypted. […]

Singapore Maths Question Solution and Very Interesting Observation (The Trickster)

Richard Foote's Blog - Thu, 04/23/2015 - 08:22
OK, time to reveal the solution to the somewhat tricky Singapore maths exam question I blogged previously. Remember, there were 10 dates: May 13   May 15   May 19 June 13   June 14 July 16   July 18 August 14   August 15   August 16 Bowie only knew the month of my birthday, Ziggy only knew the day. Bowie […]

Enable Real Application Security (RAS) in APEX 5.0

Dimitri Gielis Blog - Mon, 04/20/2015 - 21:25
Oracle Database 12c introduced Oracle Real Application Security (RAS), the next generation Oracle Virtual Private Database (VPD). In APEX 5.0 RAS is declaratively build-in. Follow the below steps to enable it:

Login to the INTERNAL workspace and go to Manage Instance > Security:

In the Real Application Security section set Allow Real Application Security to Yes.

Next login to the Workspace your Application is build in and go to your Authentication Scheme.
You'll see a new section in there called Real Application Security.



The dropdown has following possibilities:

  • Disabled: Real Application Security does not get used in the application. 
  • Internal Users: APEX creates a RAS session and assumes that all users are internal and passes false via the is_external parameter to dbms_xs_sessions.assign_user. 
  • External Users: RAS session created and true gets passed via the is_external parameter to dbms_xs_sessions.assign_user. 

The last two options enable RAS Mode and make the Dynamic Roles and Namespaces shuttle available. (from the help in APEX) Make sure that the users get privileges to access the application's schema objects. For External Users, you can for example grant database privileges to a RAS Dynamic Application Role and configure it in this authentication scheme as a Dynamic Role. You can also enable roles via a call to apex_authorization.enable_dynamic_groups, e.g. in a Post-Authentication procedure.

You can read more about Oracle Real Application Security and view an entire example how to set up RAS at the database side. I'm still learning about all the RAS features myself too, but thought to already share the above. I plan to include a chapter in my e-book about RAS and APEX 5.0 with a real-case example, as I see a big benefit for using it in a highly regulatory, secure and audited environment.


JSON for APEX Developers (part 3): Querying JSON in Oracle DB 12c

Dimitri Gielis Blog - Fri, 04/17/2015 - 15:34
In previous blog posts I talked about JSON for APEX Developers:
In this post I want to show how you store and query JSON data straight in the database.

To start, create a table to store the JSON object:

CREATE TABLE ssn_json
   (id          NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL,
    date_loaded DATE,
    json_document CLOB
    CONSTRAINT ensure_json CHECK (json_document IS JSON));

I inserted a couple of records in the table:


Here's in more detail the JSON in the json_document column:

Now to query the JSON data I can do :


You basically say j (table) . json_document (column) . analyses (first entry in JSON) . ... (other fields of the hierarchy you want to navigate to)
 
You see the data I get back is actually two records as I actually get back the JSON array.

To go into the array and see for example the first record, I can use json_value:


Note that the array start with 0.

And finally to see both records that are in the JSON array I can make use of json_table like this:


The above is just a grasp of what you can do with JSON directly in the database (12c) by using SQL.
If you're interested to read more about how to manipulate JSON in the database, have a look at the documentation, it contains a wealth on information with great examples.

As you can basically query the JSON with SQL you can use this SQL in your APEX reports... in the coming days I'll show you a real case where I used the above techniques to do some interesting searching in data.

Oracle APEX 5.0 released today

Dimitri Gielis Blog - Wed, 04/15/2015 - 21:36
After 2.5 years of development, today is the day APEX 5.0 is publicly released and ready to be downloaded to install on your own environment.

In my view it's the best release ever. Not so much of the new Page Designer - although that is definitely a piece of art and it increased productivity even further - but because it's the first time whole of APEX got refreshed and every piece was put under a radar to see how it could be improved. All the small changes and the new UI, together with the Page Designer makes it a whole new development tool, without losing it's strengths from before.

Also note that APEX 5.0 enables many new features build on top of the Oracle Database 12c features, so if you're on that database, you'll see even more nice features.

If you wonder if you should wait with upgrading to APEX 5.0 because you're afraid that your current APEX applications break, I can only share that I upgraded many of my applications as part of EA/beta and most of my apps kept running without issues. As always you have to try your applications yourself, but the APEX development team spend a lot of time trying to keep things backwards compatible. But make sure to have a look at the APEX 5.0 release notes and known issues as they contain important information about changes, expected behaviour and workarounds.

You can develop online on apex.oracle.com or you can download APEX 5.0 and install into your own environment.

Good Singapore Maths Students Would Likely Make Good Oracle DBAs (Problems)

Richard Foote's Blog - Wed, 04/15/2015 - 01:46
An interesting mathematics based question from a Singapore high school exam has been doing the internet rounds in the past few days. Considering it’s aimed at high school students, it’s a tricky one and obviously designed to filter out the better students, in a country with a very good reputation for churning out mathematically gifted […]

Oracle Database In-Memory Test Drive Workshop: Canberra 28 April 2015

Richard Foote's Blog - Mon, 03/30/2015 - 03:17
I’ll be running a free Oracle Database In-Memory Test Drive Workshop locally here in Canberra on Tuesday, 28th April 2015. Just bring a laptop with at least 8G of RAM and I’ll supply a VirtualBox image with the Oracle Database 12c In-Memory environment. Together we’ll go through a number of hands-on labs that cover: Configuring the Product Easily […]

Thoughts from a developing CIO

Rick's Blog - Thu, 03/19/2015 - 16:41
Thoughts, thought prompts (questions), small musings, golden ideas, tips, hacks, and some ironies to guide the open mind on the path toward truth. My motivation is enable high customer and shareholder satisfaction, dependable and innovative business, and peaceful and flawless execution of strategy. Living after the manner of happiness for all. Nothing I know is absolute. Some of my writings are designed to motivate and move the reader and myself to chose a better path and not settle for aimless repetition of yesterdays work. Dare to live your life on purpose.

Pages

Subscribe to Better Logic LLC aggregator