Oracle
Getting DDL for a scheduler job
If you need to copy the DDL to create a scheduler job from one schema to another, what is below works well… 13:28:56 oracle@oh1xpwcdb01 ~ >./dynColumns.py "select dbms_metadata.get_ddl('PROCOBJ','CHECK_TEMP_FREE','HOWARDS') from dual" ----------------------------------------------- BEGIN dbms_scheduler.create_job('"CHECK_TEMP_FREE"', job_type=>'PLSQL_BLOCK', job_action=> 'begin oclc_checks.check_temp_free(10); end;' , number_of_arguments=>0, start_date=>TO_TIMESTAMP_TZ('03-OCT-2009 04.40.00.000000000 PM AMERICA/NEW_YORK','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=> 'FREQ=MINUTELY;INTERVAL=5' , end_date=>NULL, job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE,... »
Standard exception pragmas
Oracle provides several standard exception pragmas (essentially, a compiler directive) for exceptions found on a fairly frequent basis. These are documented in the PL/SQL User Guide and Reference, but you can also get them at runtime using what is below: 1 2 3 4 5 6 7 8 9 10 11 12 13 14... »
Extracting SCN range from an online redo log or archived redo log
I can’t imagine that this would be useful in a well managed system, but if you find yourself with a “raw” log file that is not in the dictionary, you can at least extract the SCN range it encompasses using what is below. $ awk --version GNU Awk 3.1.5 Copyright (C) 1989, 1991-2005... »
ASM device structure
Today, we had an issue where the asmlib disk label was removed from a disk at some point in the past. When we added new storage and issued oracleasm scan disks , the disk was “cleaned”, rendering it unavailable on the server from which the scan command was run. The data itself was OK,... »
Additional cursor trace information in 11.2.0
Oracle has added a lot of additional trace output in the 10046 optimizer trace files. As I have posted previously, I find the output in these files to be useful not only for troubleshooting performance issues, but also understanding the business flow of the application in cases where I don’t have access to the... »
Posting GV$SESSION_LONGOPS from a user procedure
The GV$SESSION_LONGOPS view can save you if you are nervous about how much time a long running operation has remaining, and are asked for an update from someone in a higher “zip code”. Oracle posts an entry to this memory structure whenever an operation is expected to last more than six seconds. Note the... »
Which transaction is being rolled back?
This morning, we had an issue in a development database where we couldn’t drop an UNDO tablespace as a transaction was currently using it. We saw nothing in the GV$TRANSACTION view, so we knew it must be rolling back a **failed** transaction. Sure enough, a quick run through GV$FAST_START_TRANSACTIONS showed that a large transaction... »
filter and access in explain plan output
I have found that there is often confusion on exactly what these mean. The short answer is this: The access is the where clause used to locate the rows, which are then subject to further inclusion (filtering) by the another secondary column that wasn’t the main driver in finding rows. I think the confusion... »
Using awk to extract bind variable values from a trace file
While troubleshooting a high CPU utilization problem recently, I found that half the sessions using a given database service were using a high amount of CPU, while the other were using a normal amount. Since I knew they were each running the exact same code, the logical conclusion was that the data differed. I... »
Help! Select queries are…filling up my redo logs?!!
It is theoretically possible (although unlikely in a well managed system) to cause a large amount of transaction logging to occur if you do something as simple as update a row in one session, don’t commit, and repeatedly query that same row from another session. How is this possible? Let’s assume we have a... »