Autotrace Setup

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 29 14:46:26 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved./* Connect as a SYSDBA */SQL> conn sys/password@oraprc as sysdbaConnected.SQL> @E:\TestDB\sqlplus\admin\plustrce.sqlSQL> drop role plustrace;SQL> create role plustrace;Role created.SQL> grant select on v_$sesstat to plustrace;Grant succeeded.SQL> grant select on v_$statname to plustrace;Grant succeeded.SQL> grant select on v_$session to plustrace;Grant succeeded.SQL> grant plustrace to dba...

Read More

Explain Plan Setup

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 29 14:46:26 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.1. Creation of the plan table.SQL> @E:\TestDB\rdbms\admin\utlxplan.sqlTable created.SQL> create table t( num number(2));Table created.SQL> delete from plan_table;0 rows deleted.2. Collect the plan for SQL script.SQL> explain plan for 2 select * from t where num = 2;Explained.3. View the Explain plan. SQL> @E:\TestDB\rdbms\admin\utlxplsPLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------Id...

Read More

Solution for Dynamic String search

Script to implement it in Oracle: Initial step:SQL> conn sys/password@dba3 as sysdbaConnected.SQL> GRANT create type TO Demo1;Grant succeeded.SQL> GRANT CREATE ANY PROCEDURE to Demo1;Grant succeeded.SQL> conn demo1/demo1@dba3;Connected.create table product ( produt_name varchar2(50), version varchar2(20));insert into product values('WinZip 6.3','6.3.0');insert into product values('WinZip 8.0','8.0');insert into product values('WinZip 8.1','8.1');insert into product values ('UltraEdit 14.10','14.10');insert into product values ('UltraEdit...

Read More