Tag: techtip

LOGGING and temporary space

We had an interesting question on AskTom this week.  The poster had been told by their DBA that the reason their large INSERT-AS_SELECT statement was consuming lots of temporary segment space, was because the database had been recently altered to enable FORCE LOGGING, presumably…

Quick tip on Function Based Indexes

For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes. SQL> create table blah ( x varchar2(30)); Table created. SQL> create index blah_ix on blah ( upper(x)); Index created. SQL> select…

Active and Inactive Sessions

Most people are aware of the STATUS column in V$SESSION. If it’s ‘ACTIVE’ then that connection is in the process of consuming database resources (running a SQL statement etc). However, a lesser known column which is probably even more useful is the LAST_CALL_ET column….

Dealing with URL’s

If you are creating or handling URL’s, there is a nice little package routine that assists with handling all those pesky special characters. I’ve added a little wrapper just to make the parameter handling easier SQL> create or replace function utl_url_escape(x varchar2) return varchar2…

Opatch quick tip

I was patching my 12.1.0.2 home installation to the latest patchset today, and got the following error: C:\oracle\stage\22581007>opatch apply Oracle Interim Patch Installer version 12.1.0.1.10 Copyright (c) 2016, Oracle Corporation. All rights reserved. Oracle Home : C:\oracle\product\1210~1.2 Central Inventory : C:\Program Files\Oracle\Inventory from :…

Median in SQL

Are you stuck with a database that does not offer analytic SQL facilities ? Never mind, you can use the following trivial query can determine the median salary from the EMP table SQL> select avg(sal) from 2 ( select x.sal 3 from scott.EMP x,…

Database dictionary corruption ? Maybe not.

At first glance, this looks like a major drama. Some sort of database dictionary corruption, or internal error. But we’ll see, it’s actually fine. Let’s do a simple describe on an existing table SQL> desc MY_TABLE Name Null? Type —————————– ——– ——————– EMPNO NUMBER(38)…