I recently came across the book called “Oracle Database Transactions and Locking Revealed” by Tom Kyte and Darl Kuhn. Usually, any new book by Tom Kyte is a big deal and acquires a huge following very quickly. Strangely enough there was only a single review on the Amazon, before I added my own.
For the reasons unknown to me, this book is literally ignored. I haven’t seen posts on the oracle-l about it, on the (more...)
For those who don’t know, it is now possible to use hints on PgSQL. Here
is how things work:
- Download and install the extension from the home page:
If you are using Red Hat derivative, like me, it’s a simple RPM package. Check the content of the package, like this:
[mgogala@pg91 ~]$ rpm -qa|grep pg_hint
[mgogala@pg91 ~]$ rpm -ql pg_hint_plan93-1. (more...)
Oracle 12c came up with one nice new feature: Opatch, the tool known to all database administrators is now integrated into the database in the form of DBMS_QOPATCH package. If the database contains pluggable databases, the package must be executed from the root (CDB$ROOT) level, as user sys.
Also, a minor inconvenience is that there are no tabular results, results are returned as XML. It is a bit cumbersome to work with and I do (more...)
It is common knowledge, or at least it should be, that Oracle has
released version 220.127.116.11 in June 2014. which has a feature called
“Oracle In-Memory”. The name is a tad confusing, since many
features were called “in-memory” before, from “Very Large
Memory” to Times 10 product.
This “In Memory” option is a licensed product featuring
columnar storage, in addition to the normal buffer storage, almost
identical to IBM’s “Blu acceleration” (more...)
This is an excellent book with an encyclopedia-like approach to Oracle performance tuning, concentrated around the optimizer. It doesn’t cover architectural changes in the database in the newer releases, like the new redo mechanism with the private strands or the new implementation of the latches and pins. In my opinion, Oracle performance is more than just the optimizer.
Also, the book is very long and without the sections for new features in 12c, which makes (more...)
There is a serious problem with export/import utilities between versions.
The first command, executed on Oracle 18.104.22.168, Linux x86_64 was:
[oracle@oradb tmp]$ expdp system directory=tmp dumpfile=oe.dmp schemas=oe
Directory tmp was created as /tmp
Result was the following:
Export: Release 22.214.171.124.0 – Production on Mon Jul 28 22:57:03 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights
Connected to: Oracle Database 11g (more...)
I was looking into some new features of 12c and I didn’t need to dig deep, in order to figure out how does the “fetch first” feature work:
QL> set autotrace on
SQL> select ename from emp offset 5 rows fetch next 5 rows only;
Plan hash value: 3611411408
| Id | Operation | Name | Rows | Bytes | Cost (more...)
I am slightly disappointed to report that the new extended data types cannot be used in the clustered tables. CLOB columns have never been allowed, and are still not allowed in 12c, but I expected the new data types to pass seamlessly. That is not the case:
SQL> create cluster testclu(intkey number(30,0));
SQL> create index testclu_ind on cluster testclu;
Now, let’s create a normal table (more...)
The SYSBACKUP connectivity problems described in the previous post are caused by unpublished bug 15828768.
There is a way to connect, using SYSBACKUP privilege:
RMAN> connect target ‘system@local as sysbackup’
target database Password:
connected to target database: ORA12C (DBID=214280212, not open)
Please, note the quotes around the connection string. Without the quotes, this will not work. It works with both single and double quotes:
RMAN> connect target “system@local as sysbackup”
target database Password:
Oracle 12c has a great new privilege, tailor made for the paranoid oracle DBA guys like me. However, there are still some quirks: SYSBACKUP privilege doesn’t work over Oracle*Net. If the authentication method is SID based local authentication, all is well:
Oracle Database 12c Enterprise Edition Release 126.96.36.199.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shutdown immediate