Partitions with different attributes?



This past week the topic of archiving came up - the thing that everyone talks about but no-one does....

One of the topics that came up was that old partitions could be exchanged out of the main table and stored away from the main table to help in certain performance cases - we would still like access to them and the ability to switch it back in if required - we do not want it (more...)

WordPress 4.4.2

WordPress 4.4.2 has been released.

You can see the list of fixes here. Three of the five installations I maintain had already updated by the time I got to them, so by the time you read this you will probably already have it too. :)

Happy blogging.

Cheers

Tim…


WordPress 4.4.2 was first posted on February 2, 2016 at 8:30 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is (more...)

Partitioned Bitmap Join

If you don’t want to read the story, the summary for this article is:

If you create bitmap join indexes on a partitioned table and you use partition exchanges to load data into the table then make sure you create the bitmap join indexes on the loading tables in exactly the same order as you created them on the partitioned table or the exchange will fail with the (truthful not quite complete) error: ORA-14098: index (more...)

Auto-backups of PLSQL source

I saw this on an ideas forum today

image

and whilst most people would take care of this with a source code control system, its also true that people might do several compilations / tests with their PLSQL source before checking it in officially to their source repository. 

So the idea has some merit… and maybe we can roll our own without too much fuss. I make no claims that this is a ‘complete’ solution, (more...)

Log Buffer #458: A Carnival of the Vanities for DBAs

This Log Buffer Edition covers various useful tips and tricks from blogs for Oracle, SQL Server and MySQL.

Oracle:

  • pstack(or thread stack) for Windows to diagnose Firefox high CPU usage
  • With the ever-changing browser landscape, we needed to make some tough decisions as to which browsers and versions are going to be deemed “supported” for Oracle Application Express.  There isn’t enough time and money to support all browsers and all versions, each with (more...)

Lex de Haan

Today marks the 10th anniversary of Lex de Haan passing away. Although 10 years is a long time, I think about my dear friend Lex at least once a week. Lex assisted me adapting to new teaching skills when I progressed to blindness. It was Lex his idea to use colored magnets on my classroom […]

Nested loop internals. Part 2: decision making

In the previous part of this mini-series we looked at differences in multiblock read behavior for different nested loop optimization mechanisms depending on degree of ordering of the data. In this post I’ll continue to explore the subject, but this time we’ll focus on decision-making process: what factors (other than the obvious ones — like optimizer hints and/or parameters) affect the specific choice of a mechanism?

Clustering factor

Previously, we saw that in all nested (more...)

Forever is a long time

It’s interesting that we still see lots of code out “in the wild” that leans itself to taking forever. Now I’m not talking here about poorly performing SQL etc…because (at least theoretically) no matter how bad the SQL is, presumably it will finish one day, even if that day is years into the future. What I’m referring to here is the default mechanism for locking in Oracle.

If you try access a row that is (more...)

PROCESSED Messages not clearing from Oracle Queue

I was contacted by Dev team to look into Development database where Oracle queue size kept  on increasing. As per them messages were not getting cleared.They were running count on Queue table and the count kept increasing. To find out exact cause I included the msg_state in the query and found out that there were…

OT: YesSQL Summit 2016 Picture Diary

YesSQL Summit 2016 sponsored by O'Reilly and Axxana was held by the Northern California Oracle Users Group on January 26–28 at the Oracle conference center in Redwood City, California in conjunction with BIWA Summit 2016 and Spatial Summit 2016. The grand raffle prize sponsored by O'Reilly was a full pass to Strata + Hadoop World on March 28–31 in San Jose, California. Save 20% on Strata + Hadoop World conference passes with discount code UGNOCOUG. (more...)

Implement VM Virtual Box for OEM 13c

Oracle has released a nicely packaged, preconfigured OEM 13c environment for us to play with inside Oracle Virtual Box.  The VM comes with an installed repository database, a management server, and a local agent.  Great starting place.

Download the zip files from http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/oem-templates-2767917.html.

OVB_13c_01.png

Lets walk through items c-f since the instructions aren’t super clear.

Item c:  Uncheck ‘Programs’ when edelivery brings up this page

OVB_13c_03.png

Item d:  Start typing Oracle VM (more...)

Video: Database as a Service (DBaaS) on Oracle Cloud

The latest video on my YouTube Channel is a run through of using the Database as a Service (DBaaS) offering on Oracle Cloud.

There have been a few minor changes in the interface since I last ran through capturing images, so the related article has been brought up to date.

I used my dad for the cameo in this video. Hopefully this will help (more...)

Not Another Standby Monitoring Script

I know what you are thinking. Not another standby monitoring script, right?

There’s plenty of them out there already and what makes this one any different?

You are probably correct and there’s probably nothing different about this.

Having to work with environments that are inconsistent such as manual standby, dataguard without broker, dataguard with broker,
I have been obsessed to create standby SQL which can accommodate any environment and can be run from primary or (more...)

Friday Philosophy – If Only I Was As Good a Programmer As I Thought I Was Aged 22

I saw a tweet that made me smile a few days ago:

programmer quote

Like many of you, I started out my working life in IT as a computer programmer. Like some of you, I still am a computer programmer from time to time. I don’t think I’ve had a year of my working life when I did not do either some application development programming or some database infrastructure programming. I am constantly writing small (or not (more...)

Table Scans

It’s amazing how easy it is to interpret a number incorrectly until the point comes where you have to look at it closely – and then you realise that there was a lot more to the number than your initial casual assumption, and you would have realised it all along if you’d ever needed to think about it before.

Here’s a little case in point. I have a simple (i.e. non-partitioned) heap table t1 (more...)

Primarys Keys and their supporting indexes

Sometimes things just happen which makes you realise that stuff you thought all experienced DBA’s knew about isn’t common knowledge. I suppose it’s a side effect of working with the same evolving product for over 25 years at dozens of clients, when the colleague you are talking to has a mere decade of experience at fewer companies (and therefore less diversity of challenge).

Today I noticed that a release was creating a table, then an (more...)

Common GATHER_PLAN_STATISTIC confusion

Most people already know about the very cool GATHER_PLAN_STATISTICS hint.  If not, you can see an example here

But here’s a common cause of confusion when using it in SQL Plus:


SQL> select /*+ gather_plan_statistics*/ count(p) from t where x > sysdate - 30;

  COUNT(P)
----------
         0

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
--------------------
SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID:  (more...)

Enterprise Manager Processes, EM12c vs. EM13c

To assist users as they plan out their upgrades and new Enterprise Manager environments, I wanted to go over a few subtle, but important changes from EM12c, 12.1.0.5 to the newest release, the much anticipated EM13c, 13.1.0.0.

em13c_splash

EM Processes

One of the things you’ll notice when starting an EM12c from the command line is WHAT is started.

$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 5
 (more...)

Bad to crosscheck archivelog all

Typically, it’s not a good idea to have “crosscheck archivelog all;” in backup scripts and

even worse to have “delete expired archivelog all;” since any evidence will be eradicated.

oracle@arrow:hawklas:/tmp
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 28 14:17:35 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3130551611)

RMAN> list archivelog all;

using target database control file  (more...)

Sending notifications from Oracle Enterprise Manager to VictorOps

We use VictorOps for our paging/notification system, and we're pretty happy with it so far. On the DBA team, we've just been using a simple email gateway to send notifications from Oracle Enterprise Manager (EM) to VictorOps. Even then, we can only send the initial notification and not really send an automated recovery without more hacking than its worth. Not a big deal, but would be nice to have some more functionality.

So yesterday I (more...)