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 […]
Category: IT
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 […]
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 […]
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_METADATA-generated DDLs so […]
Talk: IT Performance
On Thuesday, March 24th, 2009 at 9 a.m. I will speak at Berufsschule Wiesau about general matters of IT performance. Papers and the presentation are available online now: Handout “IT-performance” Slides Regards Martin Klier EDIT: That’s a nice article about the event on the school’s webpage. Thanks! […]
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 […]
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. […]
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 […]
Oracle 11g JDBC driver hangs blocked by /dev/random – entropy pool empty
On a headless (=without console) network server, the 11g JDBC driver used for (java) application connect may cause trouble. In my case, it refused to connect to the DB without any error, trace or log entry. It simply hung. After several hours, it connected one time, and freezed again. Remote debugging done by the development […]
Creating Oracle AWR reports quicksheet
For comparing, classifying, benchmarking and forecasting databases, Oracle’s Automatic Workload Repository (AWR) is neat. (But remember! You will need a diagnostics pack license for that as described here and here – if you need a free technology for performance monitoring, consider using Craig Shallahamer’s OSM toolkit.) This post simply describes the package calls to create […]