Search

Top 60 Oracle Blogs

Recent comments

Uncategorized

Index compression–quick tip

If you’re appropriately licensed and want to use advanced index compression, you can take advantage of the setting a tablespace to automatically add compression as a default at a nominated level in the database.  From the docs:

Here is an example of that in action.   We’ll set our tablespace default accordingly


SQL> create tablespace demo
  2  datafile 'C:\ORACLE\ORADATA\DB122\DEMO.DBF'
  3  size 100M
  4  default index compress advanced high;

Tablespace created.

Now we’ll create a table and an index on it in that tablespace

OTN Yathra 2017

The amazing 6 city set of events is just around the corner, with all of the dates and details available at http://otnyathra.in/

image

Here are my thoughts on why you should be attending

Instrumentation … not just for debugging

Yeah I know.  You’re probably thinking “Here’s another blog post from someone telling us how important it is to instrument our code, so we can get better debugging, better performance, blah blah blah”.

The village idiot

If you are not familiar with the term Village Idiot, then Wikipedia provides a sufficient definition from which I can base this blog post.

https://en.wikipedia.org/wiki/Village_idiot

“The village idiot … is a person known for ignorance or stupidity”

Over the past couple of weeks I’ve been flying a bit.  First was OpenWorld and OracleCode in New Delhi in India, and from there, I was heading straight from there to Cleveland, Ohio for the GLOC users conference for the first time.  Being a fairly seasoned traveller, this should have been a relatively straightforward affair.

Well… things didn’t turn out that way. 

opt_estimate

The opt_estimate hint is one of many that shouldn’t be used in end-user code and isn’t officially documented. Nevertheless – like so many other hints – it’s a hint that is hard to ignore when you see it floating around the code generated by the Oracle software. This note is prompted by a twitter question from fellow Oak Table member Stefan Koehler asking the about working of the hint’s index_filter parameter. Checking my library I knew the answer was yes – so after a quick exchange on twitter I said I’d write up a short note about my example, and this is it.

Quick tip–identity columns

Lets say I’ve been reading about schema separation, and thus I am going to have a schema which owns all of my objects, which I’ll call APP_OWNER, which will have no connection privilege and a separate schema called APP_ADMIN which will take care of all of the DDL tasks.

Here’s my setup:

RTFM

Imagine you’re fairly new to Oracle and don’t have a lot of background information at your fingertips; then one day someone tells you to read the manual pages for the view dba_free_space. Look carefully at this sentence:

Note that if a data file (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information.

Can you spot the error ? Did you spot the error when you first read the sentence – or did you fill in the gap without noticing what you were doing ?

Let’s demonstrate the accuracy of the statement (simple cut-n-paste from an SQL*Plus session on 12.1.0.2 running in archivelog mode, and with a locally managed tablespace consisting of 4 (oracle managed) files on a filesystem):

DOAG Exaday 2017: a preview

I am quite excited to have been accepted to speak at the upcoming DOAG Exaday taking place June 20 in Frankfurt. It is the third time I have been lucky enough to present at the event, having spoken both in Frankfurt in Hamburg in previous years.

As you can probably imagine by looking at this weblog, I am a great supporter of the Oracle Engineered Systems community. My interest is not limited to Germany: I still remember the many great meetings in London in the context of UKOUG’s Exadays. I am also very lucky to work for @enkitec where we have been sharing real-world experience at E4 for years and will do so again in June this year.

Session killin’ time

As developers, sometimes we set something running that we wish we hadn’t Smile  And naturally, we’d like to be good IT citizens and clean up the mess as quick as we can.  (For most of us, this means – cover our tracks before the phone rings about smoke coming out of the server).  But of course, getting an administrator to hand over the trigger to let you have the ALTER SYSTEM KILL SESSION command is probably unlikely because…well… it’s just a bad bad idea.  So here’s a wrapper which might serve as a starting point for you.  It expose the kill system command to you, but in a restricted set of circumstances.

Sample Usage

By default, we report any session that has a status of active or killed. We’ll see the session details, whether it’s running or blocked, plus the SQL ID etc.

Transportable Tablespace–part 2

I did a little demo of sharing a tablespace between two databases a few days back – you can see the details here or by just scrolling down Smile if you’re on the home page.

To avoid clouding the demonstration I omitted something in the details, but I’ll share that now, because it could be critical depending on how you currently use transportable tablespaces.

Let me do the most basic of examples now, transporting a tablespace from one database to another:

First, we make our tablespace read only, and Datapump export out the metadata