The PL/SQL compiler warnings have been enhanced in 11.2.0.1 and up to include a warning for the dreaded “when others then null” exception handler. Actually, the warning is raised any time an “others” is handled (even if it is logged…
Querying load balancing information from the Load Balancing Advisory
I use what is below to periodically gather what the load balancing advisory posts. The data reflects the percentage of connections to distribute to each instance for a given service in a RAC. #!/home/oracle/local/bin/python import cx_Oracle, string conn = cx_Oracle.connect(mode…
Simple example of a type stored in a table
Nothing fancy today, this is just a placeholder for using object types as a column in a table. SQL> create type mytype as object (p_name varchar2(20),p_age number); 2 / SQL> create table type_table(c mytype); Table created. SQL> insert into type_table…
Disabling LOGGING on a LOB
In Oracle 10.2.0.4, we were doing a large data migration of archive records. Initially, we could recreate the data from the source, so our plan was to: copy rows nologging to destination database backup destination database truncate tables in the…
Which SQL statement is responsible for a wait event?
We generate a daily health check report for each database that contains high level summaries of the previous days activities. One element of this report is a list of the top ten non idle wait events (disclaimer: I know that…
Useful script to print session gv$sesstat changes over time
Oracle provides a plethora of tools for monitoring user activity, including tracing, monitoring wait events, as well as ASH. However, sometimes I want a quick (literally) overview of a session. To do this, I query the gv$sesstat view. However, the…