About

Martin Klier

usn-it.de

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 […]

read more

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 […]

read more

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 […]

read more

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 […]

read more

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 […]

read more

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 […]

read more

Oracle: Specify the number of occurrences before metric alerting starts

It’s not possible to specify a minimum of consecutive occurrences before alerting starts in in Enterprise Manager, it will always scream out at first time touching the line. But you can do so in command line. Just execute DBMS_SERVER_ALERT.SET_THRESHOLD with parameter consecutive_occurrences set to a value >1. It’s a very nice way to stop annoying […]

read more

Oracle Explain Plans or Execution Plans: Guess or Reality?

Oracle finally desupported the rule-based optimizer with version 10g. Since 9i, users are good adviced to make themself familiar with the cost-based-optimizer (CBO). In order to understand what happens with your statement after parsing with CBO involved, and to be able to tune your queries, displaying, understanding and optimizing explain- or execution plans is essential. […]

read more

Get Oracle User DDL with dbms_metadata

Sometimes you have to recreate a user in a DB, and you like it to be the same as before again. To have its DDL at hand is usually rather convenient. DBMS_METADATA is a great toolbox to get it, my example is just a short but hopefully useful excerpt of its possibilities: set long 200000 […]

read more