Feed aggregator

Tightening Security with SELECT ANY DICTIONARY in Oracle 12c

alt.oracle - Thu, 2013-09-12 08:37
Developers and users sometimes request the SELECT ANY DICTIONARY system privilege to enable them to view various data dictionary tables.  This may be fine for querying DBA_TABLES, etc, but the Oracle data dictionary contains a LOT of information.  Some of the views/tables are compromising from a security standpoint.  One of Oracle’s legendary gaffs in version 9i displayed cleartext passwords for database links in SYS.LINK$.  Yikes.  In version 12c, Oracle has locked down this type of access even further.  Here’s an example from version 11g.

SQL> create user altdotoracle identified by altdotoracle;

User created.

SQL> grant create session, select any dictionary to altdotoracle;

Grant succeeded.

SQL> conn altdotoracle/altdotoracle

SQL> select name, password from sys.user$ where password is not null;

NAME                           PASSWORD
------------------------------ ------------------------------
SYS                            AAJ125C9483Q017
SYSTEM                         W45825DFFFD37SE
OUTLN                          WW24Z1N6A8ED2E1
ALTDOTORACLE                   73NH15SG3Q2364W

Armed with these password hashes, password cracking tools can be used to compare these values to common dictionary passwords.  When a match is found on a non-complex password, your password is cracked.  (Don’t wear yourself out trying to crack the hashes above, I obfuscated them)  SELECT ANY DICTIONARY no longer allows access to dictionary objects like USER$, ENC$ and DEFAULT_PWD$. Let’s try it.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user altdotoracle identified by altdotoracle;

User created.

SQL> grant create session, select any dictionary to altdotoracle;

Grant succeeded.

SQL> conn altdotoracle/altdotoracle
Connected.

SQL> select name, password from sys.user$ where password is not null;
select name, password from sys.user$ where password is not null
                               *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> select * from sys.enc$;
select * from sys.enc$
                  *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select * from sys.default_pwd$;
select * from sys.default_pwd$
                  *
ERROR at line 1:
ORA-01031: insufficient privileges
Categories: DBA Blogs

Kscope13 Top Speaker Award

Tim Tow - Thu, 2013-09-12 07:36
I thought I would post a quick picture of the award I earned at Kscope13 as one of the Top 5 Speakers at Kscope13 for my presentation entitled "Introducing the Outline Extractor NG (Next Generation)".   The Next Generation Outline Extractor is a complete rewrite of the popular Outline Extractor.  The original extractor was written in VB which hasn't been supported by Microsoft for many years; the new extractor is written in Java.  You can download my slides, and the next generation outline extractor, from our website at http://www.appliedolap.com/downloads.

I was honored to earn the award which is based on some secret formula combining feedback scores from attendees and number of attendees.  I did have a couple of hundred or so attendees at my presentation which, in my opinion, speaks to the popularity of the outline extractor and not to the popularity of me having anything to say... <BigGrin>

Here is a picture of the nice award sent by ODTUG.


Categories: BI & Warehousing

ORA-02248 - brilliant

Frank van Bortel - Thu, 2013-09-12 06:09
Brilliant explanation, RTFM made polite. [oracle@local ~]$ oerr ora 2248 02248, 00000, "invalid option for ALTER SESSION" // *Cause: Obvious. // *Action: see SQL Language Manual for legal options. Frankhttp://www.blogger.com/profile/07830428804236732019noreply@blogger.com0

Oracle Open World 2013 - attending & presenting

Syed Jaffar - Thu, 2013-09-12 02:36
Most of us knew Oracle Open World 2013 event is fast approaching and the event will kick start in a few days time. I haven't talked much about the OOW13 event of late, therefore, I am sharing my plans over here.

  • attending Oracle ACE Directors briefing scheduled on 19-20 Sept 2013 at Oracle HQ
  • participating and speaking in the following two Expert panel sessions 
      • Session ID: UGF9774
        Session Title: Oracle RAC SIG Meeting: Expert Panel
        Venue / Room: Moscone West - 3004
        Date and Time: 9/22/13, 15:30 - 16:30
If you are coming to OOW13, you are most welcome and I will be glad to see you in my sessions.

I can't guarantee though I will try to bring all authors of 'Expert Oracle RAC 12c'  book together for an unofficial book singing event at the book shop.

Stay tuned for more updates!!

Good luck and look forward seeing you at OOW13.



Jaffar

Smart City

Anshu Sharma - Wed, 2013-09-11 19:42

Oracle has been one of the leading providers of framework and technology to make our cities smarter. This picture shows some of the thinking around this. It is interesting to note that Internet of Things has the potential to make our cities "smarter" as is evident from projects our partners are engaged in. If I look at major constituents described in this picture and pick where "connected devices" or "embedded sensors" are creating most value, I will pick Healthcare, Public Safety, Transportation, and Utilities. The value add is both in modernizing the city infrastructure and streamlining city operations. Let us look at the possibilities based on some successful implementations.

1) Healthcare - Many patients who require constant health monitoring prefer the comfort of at-home health care monitoring to costly and inconvenient hospital care. This has huge potential to take out cost from our healthcare systems. The main difficulty with home health care is enabling patients to provide accurate and timely data to health care professionals. This is being changed through Java based Gateways like Digi/Freescale Home Health Hub (HHH) platform. The value Java Embedded adds here is to provide a platform to seamlessly connect with ever increasing healthcare devices like blood pressure devices, pulse oximeters etc. and APIs to create a display interface to monitor connected devices and see results from the gateway itself.

2) Public Safety - The successful use of surveillance cameras in the recent past to identify suspects after an incident bolsters the case of using these at many locations. Low priced sensors can be put at more locations. However the challenge remains to sift through overwhelming amount of data and identify “events” of interest. This becomes more difficult if this has to happen real time to prevent incidents rather than analyze afterwards. One successful use of Real Time Event processing which has been implemented is in Emergency Response systems. During national disasters or emergencies, EADS products play key roles from the opening moments through every stage of the response. EADS used Oracle Event Processing along with other technologies in their Emergency Response System to prevent resources from entering an exclusion zone to limit damage.

3) Transportation – Logistics companies like UPS have long realized the benefits of Telematics to optimally plan the route of their vehicles. They are now also using sensor data from vehicles to perform maintenance on each vehicle only when required, as opposed to on a set schedule. It is about time that our cash strapped Public Transportations departments start realizing some of the same benefits to make real time changes to schedules based on actual demand. This has started happening in many counties. Italian Public Transportation company COTRAL implemented a monitoring system to plan and make changes to routes of their buses based on real time information being gathered using a Telematic control Unit on the Buses. Oracle products were used to do spatial analysis and visualization of data coming in from the buses in context with other route planning data.

Another area where Transportation is impacted is Traffic Management. San Francisco Municipal Transportation Agency has an ambitious project to manage demand and supply of parking spots in city of San Francisco towards the goal of reducing unnecessary traffic to find parking spots. One important way to achieve this was to use parking sensor data to provide availability information to drivers on mobile application. The sensor data is also used for determining pricing based on real time demand.

4) Utilities – IoT can impact Utilities (Electricity, Gas, Water, Waste management) in two main areas - (i) Smart Metering that enables utilities to remotely collect customers meter data at frequent intervals (~15 mins) has the potential to become smarter. (ii) SCADA (Supervisory Control & Data Acquisition) Systems can increasingly automate regular use and repair of utilities operations network. Let’s examine this in more detail in a future post.

The Network becomes the Computer...

Andries Hanekom - Tue, 2013-09-10 05:21
The Network becomes the Computer...It's an idea that I have never really been able to express in such a simplistic and elegant way as what was achieved by the technical writer(s) of the Glassfish Open Message Queue Release 5.0 Technical Overview guide:

The easiest way to integrate heterogeneous components is not to recreate them as homogeneous elements but to provide a layer that allows them to communicate despite their differences. This layer, called middleware, allows software components (applications, enterprise java beans, servlets, and other components) that have been developed independently and that run on different networked platforms to interact with one another. It is when this interaction is possible that the network can become the computer.

The idea that the network and application services becomes the equivalent of the processors integrated circuit and processing units. Continuing this abstraction to it's epoch, the internet, the power of Service Orientated Design becomes self evident.

Reference: Message Queue Technical Overview

Inverted tables: an alternative to relational structures

OraFAQ Articles - Sun, 2013-09-08 03:52

The inverted table format can deliver fast and flexible query capabilities, but is not widely used. ADABAS is probably the most successful implementation, but how often do you see that nowadays? Following is a description of how to implement inverted structures within a relational database. All code run on Oracle Database 12c, release 12.1.0.1.

Consider this table and a few rows, that describe the contents of my larder:

create table food(id number,capacity varchar2(10),container varchar2(10),item varchar2(10));
insert into food values(1,'large','bag','potatoes');
insert into food values(2,'small','box','carrots');
insert into food values(3,'medium','tin','peas');
insert into food values(4,'large','box','potatoes');
insert into food values(5,'small','tin','carrots');
insert into food values(6,'medium','bag','peas');
insert into food values(7,'large','tin','potatoes');
insert into food values(8,'small','bag','carrots');
insert into food values(9,'medium','box','peas');

The queries I run against the table might be "how many large boxes have I?" or "give me all the potatoes, I don't care about how they are packed". The idea is that I do not know in advance what columns I will be using in my predicate: it could be any combination. This is a common issue in a data warehouse.
So how do I index the table to satisfy any possible query? Two obvious possibilities:
First, build an index on each column, and the optimizer can perform an index_combine operation on whatever columns happen to be listed in the predicate. But that means indexing every column - and the table might have hundreds of columns. No way can I do that.
Second, build a concatenated index across all the columns: in effect, use an IOT. That will give me range scan access if any of the predicated columns are in the leading edge of the index key followed by filtering on the rest of the predicate. Or if the predicate does not include the leading column(s), I can get skip scan access and filter. But this is pretty useless, too: there will be wildly divergent performance depending on the predicate.
The answer is to invert the table:
create table inverted(colname varchar2(10),colvalue varchar2(10),id number);
insert into inverted select 'capacity',capacity,id from food;
insert into inverted select 'container',container,id from food;
insert into inverted select 'item',item,id from food;

Now just one index on each table can satisfy all queries:
create index food_i on food(id);
create index inverted_i on inverted(colname,colvalue);

To retrieve all the large boxes:
orclz> set autotrace on explain
orclz> select * from food where id in
  2  (select id from inverted where colname='capacity' and colvalue='large'
  3  intersect
  4  select id from inverted where colname='container' and colvalue='box');

        ID CAPACITY   CONTAINER  ITEM
---------- ---------- ---------- ----------
         4 large      box        potatoes


Execution Plan
----------------------------------------------------------
Plan hash value: 1945359172

---------------------------------------------------------------------------------
| Id  | Operation                                | Name       | Rows  | Bytes | C
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |            |     3 |   141 |
|   1 |  MERGE JOIN                              |            |     3 |   141 |
|   2 |   TABLE ACCESS BY INDEX ROWID            | FOOD       |     9 |   306 |
|   3 |    INDEX FULL SCAN                       | FOOD_I     |     9 |       |
|*  4 |   SORT JOIN                              |            |     3 |    39 |
|   5 |    VIEW                                  | VW_NSO_1   |     3 |    39 |
|   6 |     INTERSECTION                         |            |       |       |
|   7 |      SORT UNIQUE                         |            |     3 |    81 |
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED   |     3 |    81 |
|*  9 |        INDEX RANGE SCAN                  | INVERTED_I |     3 |       |
|  10 |      SORT UNIQUE                         |            |     3 |    81 |
|  11 |       TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED   |     3 |    81 |
|* 12 |        INDEX RANGE SCAN                  | INVERTED_I |     3 |       |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ID"="ID")
       filter("ID"="ID")
   9 - access("COLNAME"='capacity' AND "COLVALUE"='large')
  12 - access("COLNAME"='container' AND "COLVALUE"='box')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

orclz>

Or all the potatoes:
orclz> select * from food where id in
  2  (select id from inverted where colname='item' and colvalue='potatoes');

        ID CAPACITY   CONTAINER  ITEM
---------- ---------- ---------- ----------
         1 large      bag        potatoes
         4 large      box        potatoes
         7 large      tin        potatoes


Execution Plan
----------------------------------------------------------
Plan hash value: 762525239

---------------------------------------------------------------------------------
| Id  | Operation                              | Name       | Rows  | Bytes | Cos
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |            |     3 |   183 |
|   1 |  NESTED LOOPS                          |            |       |       |
|   2 |   NESTED LOOPS                         |            |     3 |   183 |
|   3 |    SORT UNIQUE                         |            |     3 |    81 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED   |     3 |    81 |
|*  5 |      INDEX RANGE SCAN                  | INVERTED_I |     3 |       |
|*  6 |    INDEX RANGE SCAN                    | FOOD_I     |     1 |       |
|   7 |   TABLE ACCESS BY INDEX ROWID          | FOOD       |     1 |    34 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("COLNAME"='item' AND "COLVALUE"='potatoes')
   6 - access("ID"="ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan

orclz>

Of course, consideration needs to be given to handling more complex boolean expressions; maintaining the inversion is going to take resources; and a query generator has to construct the inversion code and re-write the queries. But In principle, this structure can deliver indexed access for unpredictable predicates of any number of any columns, with no separate filtering operation. Can you do that with a normalized star schema? I don't think so.
I hope this little thought experiment has stimulated the little grey cells, and made the point that relational structures are not always optimal for all problems.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

articles: 

Oracle Open World - coming up

Hans Forbrich - Wed, 2013-09-04 14:39
Just in case you didn't know ;-)

Oracle Open World, and Oracle Java One are just around the corner.  For more information, look at http://www.oracle.com/index.html

I'll be available at the RAC Attack on both Tuesday and Wednesday.  Come on over, with your own laptop (or use one of ours) and install a complete, operational, RAC sandbox.  http://en.wikibooks.org/wiki/RAC_Attack_-_Oracle_Cluster_Database_at_Home
Categories: DBA Blogs

OWB - 11.2.0.4 Documentation Updates for 12c database

Antonio Romero - Wed, 2013-09-04 11:01
The OWB 11.2.0.4 documentation is updated on OTN with information on operating with 12c databases including install/upgrade and pluggable databases. It's worth starting with the release notes for further information. You will need either 11.2.0.3 plus CP2 (CP3 advised) or 11.2.0.4 of OWB to operate with 12c database.

OWB - 11.2.0.4 Documentation Updates for 12c database

Antonio Romero - Wed, 2013-09-04 11:01
The OWB 11.2.0.4 documentation is updated on OTN with information on operating with 12c databases including install/upgrade and pluggable databases. It's worth starting with the release notes for further information. You will need either 11.2.0.3 plus CP2 (CP3 advised) or 11.2.0.4 of OWB to operate with 12c database.

How to disable Firefox mixed content blocking (in one simple graphic)

Mike Moore - Tue, 2013-09-03 14:57
Pertains to Firefox 23.0.1 and maybe later.
I was surprised this information was so hard to find, so hopefully this will make it easier for you.


At the company I work for we have an HTTP iframe inside an HTTPS web page. The new version of Firefox objects to this mixed content and forces the user to respond by clicking on a little shield icon in order to allow the mixed content. However Firefox does not "remember" this override, nor does it allow a "white list" of domains where you don't care about mixed content. You can however disable the checking entirely by changing these options shown in the graphic above. PLEASE do not do this unless you completely understand the security implications.

If this is what you were looking for, please leave a comment, just say Hi or something.

Exceptions, Business as Usual?

Jan Kettenis - Mon, 2013-09-02 14:47
In this article I describe some aspects considering using BPMN exceptions to handle business exceptions. The conclusion is that you should carefully consider if doing so is appropriate, or that using a combination of a gateway and End event would be a better option.

Bruce Silver writes in his book BPM Method & Style that he used to use BPMN Exception end events only for technical exceptions. For business exceptions he used to use a combination of a gateway and an end state test.

When I first read that I found that to be a peculiar remark, as - coming from a Oracle BPM 10g direction - I used them for business exceptions all the time! Moreover, as most technical exceptions could be caught and managed in BPL (the 10g, Java-like scripting language) I even adviced people to to use Exceptions for technical exceptions only if unavoidable with the argument that the business audience is not interested in technical exceptions, and that therefore they should be left out of the model if possible. After all, wouldn't you agree that the first model (in which exceptions are used for business exceptions) looks simpler that the second one (which is more the gateway/end-state type of solution)?


Using BPMN Error end event
Using gateways
The model presented is inspired by a business process model with a similar complexity, but then bigger. From a functional point of view both models do the same. Examples like this may explain why later on Bruce Silver changed his mind based on feedback from his students.

With Oracle BPM 10g it was easy to write some generic (technical) business exception handling process with a retry option (using the BACK action). With this retry you could simply return to the happy path even when an Exception end event had occurred.

The first time I started to have second thoughts about using Exceptions for business exceptions was when I found that with 11g this back functionality is no longer possible (although it is supposed to come back in 12c in some way or another).

The second second thoughts came when I realized that throwing an Exception and catch that in an Event Sub-process, also has some other peculiarities, as I will explain using the model below:



In this sample model there is an Event Sub-process with a non-interrupting Get Status Message start event, and a Return Status Message end event. This exposes a getStatus services operation that can be called by some 3rd party to find out where the process is, and for that returns a status that is set by the Set Rejected Status and Set Reconsidering Status Script activities. When the Rejected Error event is thrown this is caught by the Event Sub-process with the Rejection Error start event. An Event Sub-process that starts with an Error start event, is interrupting by definition.

Patterns like this (where some operation or service is exposed to interact with a running instance) are very common in my practice. As a matter of fact, as far as I recall more than half of the models I created have a similar Event Sub-process, either to get or to set some process data on the run.

The issue that I found is that when the order is rejected without the option to reconsider - meaning that the Rejected Error event is thrown, the normal flow of the process is aborted (because of the interrupting nature of Error events). As a result, when the process is in the Confirm activity, and the getStatus operation is called, it won't react because that operation it is tied to the normal flow, which is no longer active. In contrast, when the process is in the Reconsider this is not a problem.

It is unclear to me what the BPMN specifications say about this. I can imagine that this behavior is in line with the specifications, or that the specifications are not explicit about what that behavior should be. In any case, this is how it works with 11g, which made me realize that throwing Error end events in case of business exceptions has some drawbacks that might make that modeling by using a gateway plus end state is not so peculiar after all.

Oracle Linux 6 UEK3 beta

Wim Coekaerts - Mon, 2013-09-02 13:14
Last week we published UEK3 beta on http://public-yum.oracle.com.

It is very easy to get started with this and play around with the new features. Just takes a few steps :

  • Install Oracle Linux 6 (preferrably the latest update) on a system or in a VM
  • Add the beta repository file in /etc/yum.repos.d
  • Enable the beta channel
  • Reboot into the new kernel
  • Add updated packages like lxc tools and dtrace
  • Oracle Linux is freely downloadable from http://edelivery.oracle.com/linux. Oracle Linux is free to use on as many systems as you want, is freely re-distributable without changing the CD/ISO content (so including our cute penguin), provides free security errata and bugfix errata updates. You only need to pay for a support subscription for those systems that you want/need support for, not for other systems. This allows our customers/users to run the exact same software on test and dev systems as well as production systems without having to maintain potentially two kinds of repositories. All systems can run the exact same software all the time.

    The free yum repository for security and bugfix errata is at http://public-yum.oracle.com. This site also contains a few other repositories :

  • Playground channel (a yum repository where we publish the latest kernels as released on kernel.org. We take the mainline tree and build it into RPMs that can easily be installed on Oracle Linux (Oracle Linux 6 and x86_64 specifically).
  • Beta channel (a yum repository where we publish new early versions of UEK along with corresponding packages that need to be updated along with it.
  • Now, back to UEK3 beta. Just a few steps are needed to get started.

    I will assume you have already installed Oracle Linux 6 (update 4) on a system and it is configured to use public-yum as the repository.

    First download and enable the beta repository.

    # cd /etc/yum.repos.d/
    
    # wget http://public-yum.oracle.com/beta/public-yum-ol6-beta.repo
    
    # sed -i s/enabled=0/enabled=1/g public-yum-ol6-beta.repo 
    

    You don't have to do sed you can just edit (vi/emacs) the repo file and manually set it to 1 (enable). Now you can just run yum update

    # yum update
    

    This will install UEK3 (3.8.13-13) and it will update any relevant packages that are required to be on a later version as well. At this point you should reboot into UEK3.

    New features introduced in UEK3 are listed in our release notes. There are tons of detailed improvements in the kernel since UEK2 (3.0 based). Kernelnewbies is an awesome site that keeps a nice list of changes for each version. We will add more detail to our release notes over time but for those that want to browse through all the changes, check it out.

  • http://kernelnewbies.org/Linux_3.1
  • http://kernelnewbies.org/Linux_3.2
  • http://kernelnewbies.org/Linux_3.3
  • http://kernelnewbies.org/Linux_3.4
  • http://kernelnewbies.org/Linux_3.5
  • http://kernelnewbies.org/Linux_3.6
  • http://kernelnewbies.org/Linux_3.7
  • http://kernelnewbies.org/Linux_3.8
  • To try out dtrace, you need to install the dtrace packages. We introduced USDT in UEK3's version of dtrace, there is some information in the release notes about the changes.

    # yum install dtrace-utils
    

    To try out lxc, you need to install the lxc packages. lxc is capable of using Oracle VM Oracle Linux templates as a base image to create a container.

    # yum install lxc
    

    Enjoy.

    Three impossibilities with partitioned indexes

    OraFAQ Articles - Sun, 2013-09-01 11:22

    There are three restrictions on indexing and partitioning: a unique index cannot be local non-prefixed; a global non-prefixed index is not possible; a bitmap index cannot be global. Why these limitations? I suspect that they are there to prevent us from doing something idiotic.

    This is the table used for all examples that follow:

    CREATE TABLE EMP
          (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
           ENAME VARCHAR2(10),
           JOB VARCHAR2(9),
           MGR NUMBER(4),
           HIREDATE DATE,
           SAL NUMBER(7,2),
           COMM NUMBER(7,2),
           DEPTNO NUMBER(2) )
    PARTITION BY HASH (EMPNO) PARTITIONS 4;

    the usual EMP table, with a partitioning clause appended. It is of course a contrived example. Perhaps I am recruiting so many employees concurrently that a non-partitioned table has problems with buffer contention that can be solved only with hash partitioning.

    Why can't I have a local non-prefixed unique index?
    A local non-unique index is no problem, but unique is not possible:

    orclz> create index enamei on emp(ename) local;
    
    Index created.
    
    orclz> drop index enamei;
    
    Index dropped.
    
    orclz> create unique index enamei on emp(ename) local;
    create unique index enamei on emp(ename) local
                                  *
    ERROR at line 1:
    ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

    You cannot get a around the problem by separating the index from the constraint (which is always good practice):

    orclz> create index enamei on emp(ename) local;
    
    Index created.
    
    orclz> alter table emp add constraint euk unique (ename);
    alter table emp add constraint euk unique (ename)
    *
    ERROR at line 1:
    ORA-01408: such column list already indexed
    
    
    orclz>

    So what is the issue? Clearly it is not a technical limitation. But if it were possible, consder the implications for performance. When inserting a row, a unique index (or a non-unique index enforcing a unique constraint) must be searched to see if the key value already exists. For my little four partition table, that would mean four index searches: one of each local index partition. Well, OK. But what if the table were range partitioned into a thousand partitions? Then every insert would have to make a thousand index lookups. This would be unbelievably slow. By restricting unique indexes to global or local prefixed, Uncle Oracle is ensuring that we cannot create such an awful situation.

    Why can't I have a global non-prefixed index?
    Well, why would you want one? In my example, perhaps you want a global index on deptno, partitioned by mgr. But you can't do it:

    orclz> create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4;
    create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4
                                                                    *
    ERROR at line 1:
    ORA-14038: GLOBAL partitioned index must be prefixed
    
    
    orclz>
    This index, if it were possible, might assist a query with an equality predicate on mgr and a range predicate on deptno: prune off all the non-relevant mgr partitions, then a range scan. But exactly the same effect would be achieved by using global nonpartitioned concatenated index on mgr and deptno. If the query had only deptno in the predicate, it woud have to search each partition of the putative global partitioned index, a process which would be just about identical to a skip scan of the nonpartitioned index. And of course the concatenated index could be globally partitioned - on mgr. So there you have it: a global non-prefixed index would give you nothing that is not available in other ways.

    Why can't I have a global partitioned bitmap index?
    This came up on the Oracle forums recently, https://forums.oracle.com/thread/2575623
    Global indexes must be prefixed. Bearing that in mind, the question needs to be re-phrased: why would anyone ever want a prefixed partitioned bitmap index? Something like this:

    orclz>
    orclz> create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4;
    create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4
                                           *
    ERROR at line 1:
    ORA-25113: GLOBAL may not be used with a bitmap index
    
    orclz>

    If this were possible, what would it give you? Nothing. You would not get the usual benefit of reducing contention for concurrent inserts, because of the need to lock entire blocks of a bitmap index (and therefore ranges of rows) when doing DML. Range partitioning a bitmap index would be ludicrous, because of the need to use equality predicates to get real value from bitmaps. Even with hash partitions, you would not get any benefit from partition pruning, because using equality predicates on a bitmap index in effect prunes the index already: that is what a bitmap index is for. So it seems to me that a globally partitioned bitmap index would deliver no benefit, while adding complexity and problems of index maintenance. So I suspect that, once again, Uncle Oracle is protecting us from ourselves.

    Is there a technology limitation?
    I am of course open to correction, but I cannot see a technology limitation that enforces any of these three impossibilities. I'm sure they are all technically possible. But Oracle has decided that, for our own good, they will never be implemented.
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com

    articles: 

    Redundancies should come with a pay rise

    Robert Baillie - Sun, 2013-09-01 02:21
    As far as I can see, there is only one reason why a company should ever make redundancies.Due to some unforseen circumstances the business has become larger than the market conditions can support and it needs to shrink in order to bring it back in line.Every other reason is simply a minor variation or a consequence of that underlying reason.Therefore, if the motivation is clear, and the matter dealt with successfully, then once the redundancies are over the business should be "right sized" (we've all heard that term before), and it should be able to carry on operating with the same values, practices and approach that it did prior to the redundancies.If the business can't, then I would suggest is that it is not the right size for the market conditions and therefore the job isn't complete.OK, there may be some caveats to that, but to my mind this reasoning is sound.In detail:When you reduce the headcount of the business you look for the essential positions in the company, keep those,...

    Redundancies should come with a pay rise

    Rob Baillie - Sat, 2013-08-31 10:46

    As far as I can see, there is only one reason why a company should ever make redundancies.

    Due to some unforseen circumstances the business has become larger than the market conditions can support and it needs to shrink in order to bring it back in line.

    Every other reason is simply a minor variation or a consequence of that underlying reason.

    Therefore, if the motivation is clear, and the matter dealt with successfully, then once the redundancies are over the business should be "right sized" (we've all heard that term before), and it should be able to carry on operating with the same values, practices and approach that it did prior to the redundancies.

    If the business can't, then I would suggest is that it is not the right size for the market conditions and therefore the job isn't complete.

    OK, there may be some caveats to that, but to my mind this reasoning is sound.

    In detail:

    When you reduce the headcount of the business you look for the essential positions in the company, keep those, and get rid of the rest.

    Once the redundancies are finished you should be left with only the positions you need to keep in order to operate successfully.

    It's tempting to think that you should have a recruitment freeze and not back-fill positions when people leave, but if someone leaves and you don't need to replace them, then that means you didn't need that position, in which case you should have made it redundant.

    Not back-filling positions is effectively the same as allowing your employees to choose who goes based on their personal motives rather than force the business heads to choose based on the business motives.  This doesn't make business sense.

    So, you need to be decisive and cut as far as you can go without limiting your ability to operate within the current market conditions.

    To add to that, recruitment is expensive.  If you're in a highly skilled market then you'll likely use an agency. They can easily charge 20% of a salary for a perm head.  On top of that you have the cost of bringing someone up to speed, at a time when you're running at the minimum size your market will allow.  Plus there's the cost of inefficiency during the onboarding period as well as the increased chance of the remaining overstretched employees leaving as well.

    The upshot is that you really can't afford to have people leave, it's so expensive that it jeopardises the extremely hard work you did when you made the redundancies.

    There's a theory I often hear that you can't have contractors working when the perm heads are being marched out.  That's a perfectly valid argument if the perm head would be of long term value to you and can do the job that the contract head can do.  But if you need the contractor to do a job that only lasts another 3 months and that person is by far the best or only person you have for the job, then the argument just doesn't stand up.  Get rid of the perm position now and use the contractor, it'll be cheaper and more beneficial to the business in the long run.

    OK, that's maybe not the most sentimental of arguments, but why would you worry about hurting the feelings of people who no longer work for you, at the expense of those that still do?

    It may even be worse than that - you could be jeopardising the jobs of others that remain by not operating in the most efficient and effective way possible.

    Another prime example is maternity cover.  If you need the person on maternity to come back to work then you almost certainly need the person covering them. If it's early in the maternity leave then you'll have a long period with limited staff, if it's late in the leave then you only need the temporary cover for a short period more. Either way you're overstretching the perm staff left to cover them and risking having them leave.

    Finally, there's the motivation to ensure that the business that remains is running as lean as possible. That costs are as low as they could be. The temptation is to cut the training and entertainments budget to minimum and pull back on the benefits package.
    As soon as you do this you fundamentally change the character of the business.  If you always prided yourself on being at the forefront of training then you attracted and kept staff who valued that. If you always had an open tab on a Friday night at the local bar, then you attracted people who valued that.  Whatever it is that you are cutting back on, you are saying to people who valued it that "we no longer want to be as attractive to you as we once were; we do not value you quite as much as we did". This might not be your intention, but it is the message your staff will hear.

    I put it to you that the cheapest way to reduce costs after redundancies is to be completely honest to the staff you keep. Say it was difficult, say that you're running at minimum and that a lot will be expected of whoever's left. But tell them that they're still here because they're the best of the company and they are vital to the company's success.  Let them know that the contractors you've kept are there because they're the best people for those positions to ensure that the company succeeds.  Tell them that the contractors will be gone the moment they're not generating value or when a perm head would be more appropriate.  Make it clear that the company is now at the right size and the last thing you want is for people to leave, because you value them and that if they left it would damage your ability to do business.

    Then give them a pay rise and a party to prove it.

    OWB - 11.2.0.4 standalone client released

    Antonio Romero - Fri, 2013-08-30 16:29

    The 11.2.0.4 release of OWB containing the 32 bit and 64 bit clients is released today. Big thanks to Anil for spearheading that, another milestone on the Data Integration roadmap.

    Below are the patch numbers;

    • 17389934 - OWB 11.2.0.4 STANDALONE CLIENT FOR LINUX X86 64 BIT
    • 17389949 - OWB 11.2.0.4 STANDALONE CLIENT FOR LINUX X86 32 BIT

    The windows releases will come in due course. This is the terminal release of OWB and customer bugs will be resolved on top of this release.

    Sure and Stedfast has been a steady motto through my life, it came from way back in my old Boys Brigade days back in Scotland. Working in Oracle I have always reflected back on that over the years, can still hear 'Will your anchor hold in the storms of life' ringing in my ear. The ride through different development organizations from Oracle Tools, through Oracle Database and Oracle Middleware groups, from buildings 200, to 400 to 100, 7th floor, 9th floor, 5th floor, countless acquisitions and integrations. Constant change in some aspects, but zeroes and ones remain zeroes and ones, for our group the direction and goals were well understood. Whilst its been quiet on the OWB blogging front, the data integration development organization has been busy, very busy releasing versions of OWB and ODI over the past few years and building the 12c release.

    So to 12c... our data integration product roadmap has been a strong focal point in our development over the past few years and that's what we have been using to focus our energy and and our direction. Like personal life we need a goal, a vision and a roadmap for getting there. There have been plenty of challenges along the way; technical, political and personal - its been a tough and challenging few years on all of those fronts, its when you are faced with momentous personal challenges that the technical ones look trivial. The most gratifying aspect is when you see light at the end of the tunnel. It's that light at the end of the tunnel that gives you added strength to finish the job at hand. Onwards and upwards!

    OWB - 11.2.0.4 standalone client released

    Antonio Romero - Fri, 2013-08-30 16:29

    The 11.2.0.4 release of OWB containing the 32 bit and 64 bit clients is released today. Big thanks to Anil for spearheading that, another milestone on the Data Integration roadmap.

    Below are the patch numbers;

    • 17389934 - OWB 11.2.0.4 STANDALONE CLIENT FOR LINUX X86 64 BIT
    • 17389949 - OWB 11.2.0.4 STANDALONE CLIENT FOR LINUX X86 32 BIT

    The windows releases will come in due course. This is the terminal release of OWB and customer bugs will be resolved on top of this release.

    Sure and Stedfast has been a steady motto through my life, it came from way back in my old Boys Brigade days back in Scotland. Working in Oracle I have always reflected back on that over the years, can still hear 'Will your anchor hold in the storms of life' ringing in my ear. The ride through different development organizations from Oracle Tools, through Oracle Database and Oracle Middleware groups, from buildings 200, to 400 to 100, 7th floor, 9th floor, 5th floor, countless acquisitions and integrations. Constant change in some aspects, but zeroes and ones remain zeroes and ones, for our group the direction and goals were well understood. Whilst its been quiet on the OWB blogging front, the data integration development organization has been busy, very busy releasing versions of OWB and ODI over the past few years and building the 12c release.

    So to 12c... our data integration product roadmap has been a strong focal point in our development over the past few years and that's what we have been using to focus our energy and and our direction. Like personal life we need a goal, a vision and a roadmap for getting there. There have been plenty of challenges along the way; technical, political and personal - its been a tough and challenging few years on all of those fronts, its when you are faced with momentous personal challenges that the technical ones look trivial. The most gratifying aspect is when you see light at the end of the tunnel. It's that light at the end of the tunnel that gives you added strength to finish the job at hand. Onwards and upwards!

    I'm not available

    Catherine Devlin - Thu, 2013-08-29 13:23

    I'm happy to say that I'll shortly be starting a new position as a PostgreSQL DBA and Python developer for Zoro Tools!

    We software types seem to have hardware envy sometimes. We have "builds" and "engines" and "forges" and "factory functions". But as it turns out, the "Tools" in "Zoro Tools" isn't a metaphor for cleverly arranged bytes. That's right - they're talking about the physical objects in your garage! Imagine! Lucky for me the interviewers didn't ask to review my junior high shop project.

    So disregard my earlier post about being available. Thanks for all your well-wishes!

    Depending on how you reckon it, my job search arguably only took forty minutes, though it took a while for gears to grind and finalize everything. Years of building relationships at PyCon made this the best job search ever; the only unpleasant part was having to choose from among the opportunities to work with my favorite technologies and people. I'm very glad I made the investment in PyCon over the years... and if you're thinking "that's easy for you to say, I can't afford it", don't forget PyCon's financial aid program.

    And speaking of conferences, I'll be at Postgres Open next month (my first one!) - hope to see some of you there!

    The Difference Between Access Manager 10g and 11g Webgates

    Mark Wilcox - Thu, 2013-08-29 11:00

    A common question we get is what is the difference between Access Manager 10g and Access Manager 11g webgates.

    My colleague Yagnesh who covers webgates put together a simple list:

    Here is 11g features:

    • Oracle Universal Installer for platform. Generic for all platforms
    • Host-based cookie
    • Individual WebGate OAMAuthnCookie_ making it more secure
    • A per agent key, and server key, are used. Agent key is stored in wallet file and Server key is stored in Credential store
    • One per-agent secret key shared between 11g WebGate and OAM Server One OAM Server key
    • OAM 11g supports cross-network-domain single sign-on out of the box. Oracle recommends you use Oracle Identity Federation for this situation.
    • Capability to act as a detached credential collector
    • Webgate Authorization Caching
    • Diagnostic page to tune parameters
    • Has separate install and configuration option. Hence, single install and multiple instance configuration is supported.

    And 10g:

    • InstallShield and One installer per platform
    • Domain-based cookie
    • ObSSOCookie (one for all 10g Webgates)
    • Global shared secret stored in the directory server only (not accessible to WebGate)
    • There is just one global shared secret key per OAM deployment which is used by all the WebGates
    • OAM 10g provides a proprietary multiple network domain SSO capability that predates Oracle Identity Federation. Complex configuration is required.
    • One Web server configuration supported per WebGate. Need to have multiple WebGates for multiple instances.

    Pages

    Subscribe to Oracle FAQ aggregator