Archive for 2009

Oracle 11g Release 2 documentation

The long-awaited 11gR2 is out, at least for Linux x86 and x86_64 now.

Marketing will show you all the features, but the most important link is to the documentations:

Use it well!

By Martin Klier in Oracle  .::. (Add your comment)

Oracle: Executing an OS command from PL/SQL – ICMP ping from database

Executing OS commands from SQL or PL/SQL is easy as long as you are in SQL*PLUS – just do a host command. But I must confess, that this is a really useless hint. :) Much more interesting is to execute an OS command with a DBMS_SCHEDULER job, but this is not possible in every case, as it wasn’t in mine: I had to execute an ICMP ping and wait for the result to proceed, all that within a periodic DBMS_SCHEDULER job. No way to launch another job and wait until it’s started.

It took not much time to find out, that PL/SQL provides no native method to execute something on OS level. But the web was full of more or less useful hints what to do, the most useful one I found was from Ask Tom, a question about problems calling a in-database-java method for this case. The guy there used rt.exec() out of the database – nice, but Java and me …

Read more…

By Martin Klier in Oracle  .::. Read Comment (1)

Oracle: Tracing of another session

I simply love 10046 level 12 traces. I described session tracing based on a logon trigger quite earlier, but some situations in real DBA life need this trace event switched on for a session other than my own AND for a short peroid in time (with no need for the connected user to log out). The package/function dbms_system.set_ev is cool for that – the syntax and the handling is very simple, and I like it much more than ORADEBUG. So it’s syntax is like:

Read more…

By Martin Klier in Oracle  .::. Read Comment (1)

Oracle: Audit a failed logon attempt without auditing

Oracle has a disadvantage: It allows no trigger BEFORE LOGON ON DTATBASE! :) For obvious reasons, this would be nonsense, but there’s a need for it! Auditing failed logon attempts, for example. Of course, there’s Oracle Auditing. But IMO, setting up an audit trail for one Email in one case looked like the overkill to me and my simple DB decommissioning needs (finding autistic developers).

The concept with a trigger was too nice to drop it after examining the AFTER LOGON trigger, which is useless for my problem. But the oracle-l mailing list helped AGAIN: There’s another oracle trigger: AFTER SERVERERROR ON DATABASE – catch ORA-28000, and you are perfectly auditing logon attempts on locked user accounts.

This is a code snippet for 10g and above, that sends an email on each attempt. Might be a spam bomb if abused, but as I said: My simple needs …

Read more…

By Martin Klier in Oracle  .::. Read Comments (3)

String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004

In the last days, I touched hierarchical queries – it’s a strange field for a DBA, but I did need it for DBA desires: A sophisticated query on statspack. Why? Since oddly, the stats$sql_text table of statspack / perfstat DOES contain the full SQL for a statement, but fragmented over various lines. But I had to display all queries in one line, as one string. The hierachical function sys_connect_by_path() looked sexy for it, so I dived into for good. :)

But soon a problem occured: sys_connect_by_path() needs a seperator string, that does not already occur within the to-be-concatenated strings. Okay, this is understandable and rather easy to cope with: Just replace the known string out of the result, done. But most annoying, this seperator has to be LITERAL – no function is allowed there! So in my case, a cause-loop rised its ugly head: As soon as this literal string is stated, it will occur within the stats$sql_text table, because this table contains (nearly) all SQL ran against the DB. Whoop – I’m out of business?

Not really: the oracle-l mailing list saved me (again). This thread resolved the problem – if you ever should need a similar query, just have a look at the solution of this nice little puzzle:

Read more…

By Martin Klier in Oracle  .::. (Add your comment)

Oracle IMPDP: Wildcard hacking

Sometimes you want to use datapump import (impdp) for smart problems. An example is excluding (or including) a subset of objects, like tables. The usual way to do so is the EXCLUDE keyword.
(Footnote: All examples in this post are written for a parameter file, so don’t forget a proper quoting for your shell if you are using them on command line.)

(1) The basic syntax for excluding the table EMPLOYEES_1 from import is:


(2) More sophisticated is the use of an IN() statement for more than one table, let’s ignore EMPLOYEES_1, CARS_1 and TRUCKS_1: Read more…

By Martin Klier in Oracle  .::. Read Comments (5)

Oracle: Query/Monitoring autoextend of datafiles

Did you ever wonder how to see whether your datafile is on autoextend or not, or monitor the autoextension to keep eyeballs on target? The Oracle EM is a simple tool for that, but some people do prefer the direct query.

See a little example to query or autoextend a datafile.

select * from dba_data_files where tablespace_name='USERS';

alter database datafile &datafilenumber AUTOEXTEND
        on next &increment maxsize &maxsize;

Take care

By Martin Klier in Oracle  .::. Read Comment (1)

Oracle: Change (default) TEMP and UNDO tablespace to bigfile tablespaces

That’s how to change the Oracle Database’s TEMP and UNDO tablespaces to bigfile (as possible in 10g and above). It’s something I usually do for every DB I install.

create bigfile temporary tablespace TEMP1 tempfile \
     size 10G autoextend on next 1G maxsize 20G;
create bigfile undo tablespace UNDO1 datafile \
     size 10G autoextend on next 1G maxsize 20G;
alter system set undo_tablespace=UNDO1 scope=both sid='*';
alter database default temporary tablespace TEMP1;
drop tablespace UNDOTBS1;
drop tablespace TEMP;

Be careful,

By Martin Klier in Oracle  .::. Read Comments (2)

Oracle 11g look-at’s

That’s a personal, quite unsorted list of (new or older) features I recently collected. All of them are things, I’d consider valuable or at least important to care about as soon as 11g is involved. It might be for system architecture knowledge, concept tasks, DBA hands-on, good-to-know or any other thing that my happen in a DBA life.

  • Password profile (user pw expires after 180 days by default!)
  • (Client) Result cache
  • (DB_KEEP_CACHE / DB_RECYCLE_CACHE plus corresponding segment configuration)
  • the adrci command line tool, for the least example to view an alert log tail-like in windows
  • direct NFS supported
  • fast mirror resync in ASM
  • preferred read mirror in ASM
  • Optimizer simulation capabilities
  • Index invisible after creation
  • Statistics inactive after creation
  • DB replay feature with 10g partially possible (patch apply necessary)
  • I/O-calibration + I/O statistics
  • Adaptive cursor sharing
  • Resource manager with I/O quotas
  • Interval partitioning (auto-create addition to range partitioning)
  • PL/SQL native code

Look them up in Oracle Docs, and consider using them someday/somehow/somewhere.

Good, bugfree luck

By Martin Klier in Oracle  .::. Read Comment (1)

Oracle: Convert a partitioned table into an unpartitioned one

License fees can bee massive. So for some machines it’s just useless to buy Oracle Enterprise Edition, sometimes Standard Edition is simply enough. Migration to SE for a schema with partitioned tables leaves you one option: CTAS all partitioned tables and export them, reimport them on your new box and modify all DBMS_METATDATA-generated DDLs so that all constraints and indexes still fit.

For this case or for other cases it might be useful to simply “remove partitioning” from a table, preserving all dependent objects either valid (most) or running with a quick recompile (PL/SQL). The attached SQL script shows how to unpartition all tables of a given user, using DBMS_METADTA. You may want to change or remove the big loop wrapping the whole DBMS_REDEFINITION stuff to make some finer selection.

The unpartitioning works with a dummy table. It’s created “as select *” within the loop, used by the package, and dropped in the end of the iteration. The package duplicates all constraints, indexes and other metadata to the interim table’s values, and renames all that in the end. Since you created the table in the beginning “as select”, all data is already there. Finally, you have the option to synchronize original and dummy, so nothing will be lost in the end.


The script is rather simple, licensed under GPLv2, and considered as a starting point for future working with DBMS_REDEFINITION. This package is a very useful tool, once you discovered it’s possibilities.

If you find bugs, misses or have additions to make, feel free to contact me.


By Martin Klier in Oracle  .::. Read Comments (3)

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.