You can always download latest version here: http://github.com/xtender/xt_scripts/blob/master/dynamic_sampling_used_for.sql
Current source code:
col owner for a30;
col tab_name for a30;
col top_sql_id for a13;
col temporary for a9;
col last_analyzed for a30;
col partitioned for a11;
col nested for a6;
col IOT_TYPE for a15;
with tabs as (
to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,1)) owner
,to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,2)) tab_name
,max(sql_id) keep(dense_rank first order by elapsed_time desc) top_sql_id
from v$sqlarea a
An interesting issue presented itself just recently with a logical standby database I manage. The database is used for generating reports and the client wanted to skip all DML activity for a given schema as it wasn’t necessary for reporting purposes. I had done this in version 10.2.0.x; it was a simple procedure on a low-traffic database:
alter database stop logical standby apply;
alter database start logical standby apply;
I was upgrading an Exadata test database from 18.104.22.168 to 22.214.171.124 and I came across a failure scenario I had not encountered before. I’ve upgraded a few databases to both 126.96.36.199 and 188.8.131.52 for test purposes, but this was the first one I’d done on Exadata. And the first time I’d encountered such a failure.
I started the upgrade after checking with the (more...)
As a huge friend of Oracles SQL trace I also see it's limitations as well. One of these limitations is visible in it's underlying infrastructure: the wait interface. It's only about waits
There is another source of information as well, you could call it the DO interface, but the common term is session and system statistics. there Oracle counts what it is doing.
Unfortunately I do not know a simple way to show both realities (more...)
Comparing SQL Execution Times From Different Systems
Suppose it's your job to identify SQL that may run slower in the about-to-be-upgrated Oracle Database. It's tricky because no two systems are alike. Just because the SQL run time is faster in the test environment doesn't mean the decision to upgrade is a good one. In fact, it could be disastrous.
For example; If a SQL statement runs 10 seconds in production and runs 20 seconds in (more...)
I tested this on an Oracle 11.1 database.
Oracle profiles control how certain database resources are allocated to a user session. They also define some security rules. When you create a user, it is assigned a profile and, if you do not specify it explicitly, the DEFAULT profile will be used:
SQL> grant create session to andrew
2 identified by reid
SQL> select profile (more...)
Good day for learning something new. After read Flume book, that something popped up in my head. Wanted to test "rsyslog" => Flume => HDFS. As we know, forwarding log to other systems. We can set rsyslog:
*.* @YOURSERVERADDRESS:YOURSERVERPORT ## for UDPFor rsyslog:
*.* @@YOURSERVERADDRESS:YOURSERVERPORT ## for TCP
[root@centos01 ~]# grep centos /etc/rsyslog.conf
Came back to Flume, I used Simple Example
for reference and changed a bit. Because (more...)
This is the first entry in a series of random articles about some useful internals-to-know of the awesome Oracle Database In-Memory column store. I intend to write about Oracle’s IM stuff that’s not already covered somewhere else and also about some general CPU topics (that are well covered elsewhere, but not always so well known in the Oracle DBA/developer world).
Before going into further details, you might want to review the Part 0 of this series and also our (more...)
It is common knowledge, or at least it should be, that Oracle has
released version 184.108.40.206 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...)
Well, the annual spectacle of enormous proportions has come to a conclusion again. And thats probably the first reason I’d recommend OpenWorld to anyone who works with Oracle who has never been to it. It’s a jaw dropping moment just to see the scale of the event, and how impressively its organised in terms of facilities, lunches, registration and the like.
But onto the technical elements, here’s my impressions of this years conference:
1) Big (more...)
Disclosure: I'm attending Openworld at the invitation of the OTN ACE Director program who are paying for my flights, hotel and conference fee. My employer has helpfully let me attend on work time, as well as sending other team mates because they recognise the educational value of attending. Despite that, all of the opinions expressed in these posts are, as usual, all my own.
After the very welcome tradition of breakfast at Lori's Diner, I (more...)
This year's Oracle Open World 2014 was a lot of learning and meeting old and new friends.
What a fun and great conference!
The big highlights for me were:
Oracle 12c In Memory Database- in sight and everywhere this was a key mantra at the event.
Big Data SQL- another new thing that intrigues me is using SQL to manage Big Data applications with Hadoop, NoSQL and other applications.
Engineered systems- Oracle demo (more...)
I used Oracle Enterprise Manager to look at the execution plan for some SQL in an Oracle 10.2.0.3 database. (The SQL shown is just an example done later for the purposes of this blog post. As usual, click on the image to enlarge it and bring it into focus if necessary.):
This produced the following ORA-00600 message several times in the alert log:
Wed Oct 1 18:13:21 2014
To extend an ACFS cluster filesystem dynamically, we need to use the acfsutil command:
node01:/u01/grid>/sbin/acfsutil size +10G /u02
acfsutil size: ACFS-03008: The volume could not be resized. The volume expansion limit has been reached.
acfsutil size: ACFS-03216: The ADVM compatibility attribute for the diskgroup was below the required
version (220.127.116.11.0) for unlimited volume expansions.
Oh dear, not 18.104.22.168, so you can only extend volumes dynamically a (more...)
What is Real Time Database Operation Monitoring ? Real Time Database Operation Monitoring will help you track the progress of a set of sql statements and let you create a report. Real Time Database Operation Monitoring acts as a superset of all monitoring components like : ASH, DBMS_MONITOR … You can generate Active Reports which are [...]
The post OCP 12C – Real-Time Database Operation Monitoring appeared first on Oracle DBA Scripts and Articles (Montreal).
Yes, direct path reads again :) No worries I’m already a bit bored from digging in this algorithm, so I think this is going to be my last post about it. Till they change it again of course. And yes, in 22.214.171.124 they did. For introduction to DPR, you can check my previous post, because […]
A big thanks to all those who braved attending my session on the final day of Oracle Open World 2014. I hope you enjoyed it and found it valuable.
You can download the slide deck as well as the scripts I mentioned here
As always, I would love to hear from you.
RMAN Enhancements New Privilege A new SYSBACKUP privilege is created in Oracle 12c, it allows the grantee to perform BACKUP and RECOVERY operations with RMAN SQL in RMAN You can now use SQL Statements in RMAN like you would do in SQL*PLUS : BEFORE : RMAN> SQL “alter system switch logfile”; NOW : RMAN> alter system switch logfile; [...]
The post OCP 12C – RMAN and Flashback Data Archive appeared first on Oracle DBA Scripts and Articles (more...)
User Task-Specific Administrative Privileges To continue with the objective of separating duties and the least privileges, Oracle 12c introduce new administratives privileges all destinated to accomplish specific duties: SYSBACKUP : Used for RMAN operations like BACKUP, RESTORE, RECOVER SYSDG : Used to administer DATAGUARD, In 12c when you use DGMGRL commandline interface your are automatically [...]
The post OCP 12C – Privileges appeared first on Oracle DBA Scripts and Articles (Montreal).