Archive for July, 2011
The issue occurs when there is a db link which is a loop back database link i.e. points to itself Example: 01:52:20 @> DROP DATABASE LINK TEST.WORLD; DROP DATABASE LINK TEST.WORLD * ERROR at line 1: ORA-02082: a loopback database link must have a connection qualifier -- Shows the global name matches the db link [...]
“RESOURCE_LIMIT” parameter is used to enable/disable “KERNEL” resource type profiles. Password parameters are part of profiles but they are controlled by this parameter. This parameter is a SYSTEM level parameter so it can be changed using “ALTER SYSTEM” and can be changed on the fly. Possible values are true/false and the default value is false. [...]
Shows a way to find if table was truncated, using object_id, data_object_id one can tell possibly if the table was truncated if the object_id, data_object_id is being monitored. SQL> create table x ( x number ); Table created. -- shows object_id, data_object_id is the same when table is initially created SQL> select object_id, data_object_id, last_ddl_time, [...]
Using OPatch with lsinventory and bugs_fixed options one can list the PSU patches installed in ORACLE_HOME Example: localhost:/home/oracle $ $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i psu 8974548 10248516 Fri Mar 04 16:05:46 MST 2011 BACKOUT BUG 7438445 FROM PSU 11.1.0.7.1 RELEASE LA 9352237 10248516 Fri Mar 04 16:05:46 MST 2011 DATABASE PSU 11.2.0.1.1 9654983 10248516 [...]
On AIX ran into the following issue where sqlplus generated the following error when using local connection (bequeath). scott@localhost:/home/scott:$ sqlplus system SQL*Plus: Release 10.2.0.5.0 – Production on Fri Apr 1 15:47:21 2011 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Enter password: exec(): 0509-036 Cannot load program oracleTEST because of the following error 0509-150 Dependent [...]
Tom Kyte’s following routine is a utility that can be used to to collect statistics of a session at the start, middle and at the end. http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701
There are 2 types of DBCC TRACEON (session or global). Session trace is turned on that session only and global trace is visible for all connections on the server. Trace#: 3502 – This trace writes information to SQL Server log every time a check point occurs SQLServer 2008 R2 trace flags SQLServer 2005 trace flags [...]
In SQLServer Managment Studio to the execution plan one can use one of the following SET commands “SET SHOWPLAN_XML ON” – SQLServer will not execute the SQL and it will return execution plan and has estimated row counts and stats in form of a XML document. The output displayed in SQL Server management studio can [...]
Shows how to flush buffer cache and v$bh, you may only want to do this on Dev or Test enviornment as it would affect performance on production. -- displays the status and number of pings for every buffer in the SGA SQL> select distinct status from v$bh; STATUS ——- xcur cr -- flush buffer cache [...]
There are various methods of displaying execution plan. Here is few of them. 1) Using EXPLAIN PLAN and setting STATEMENT_ID and then display execution plan using PLAN_TABLE. EXPLAIN PLAN SET statement_id = 'TEST' FOR select * FROM scott.x; SELECT LPAD(' ', 2*LEVEL) || operation || ' ' || options || ' ' || object_name plan [...]
