Script to Create a Plan table

create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000)
);

Read More

PGA and UGA Memory Usage Script

SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
,

(select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
,

(select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
,

(select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
,

(select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC

Read More

Autotrace Setup

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 29 14:46:26 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
/* Connect as a SYSDBA */

SQL> conn sys/password@oraprc as sysdba
Connected.



SQL> @E:\TestDB\sqlplus\admin\plustrce.sql

SQL> 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 with admin option;
Grant succeeded.

SQL> set echo off
/* Grant the plustrace to pulic */

SQL> grant plustrace to public;
Grant succeeded.

/* Connect as a Demo */

SQL> conn demo/demo@oraprc
Connected.

SQL> set autotrace traceonly
SQL> select * from t where num = 2;
no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
219 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>

Read More

Explain Plan Setup

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 29 14:46:26 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
1. Creation of the plan table.
SQL> @E:\TestDB\rdbms\admin\utlxplan.sql
Table 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\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL T
--------------------------------------------------------------------

Predicate Information (identified by operation id):---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------1 - filter("T"."NUM"=2)
Note: rule based optimization
14 rows selected.
SQL>

Read More

Solution for Dynamic String search

Script to implement it in Oracle:

Initial step:
SQL> conn sys/password@dba3 as sysdba

Connected.

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 15.0','15.0');
insert into product values ('PL/SQL Developer 5.0','5.0');
insert into product values ('PL/SQL Developer 5.1','5.1');
commit;

SQL> select PRODUT_NAME from product;

PRODUT_NAME --------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
PL/SQL Developer 5.0
PL/SQL Developer 5.1

7 rows selected.
Dynamic String search:
Need to identify the list product names based on the dynamic parameter.

1. The first parameter is WinZip,UltraEdit. So the below script is having the two conditions in where clause.

SQL> select PRODUT_NAME from product
2 where PRODUT_NAME like 'WinZip%'
3 or PRODUT_NAME like 'UltraEdit%';

PRODUT_NAME
----------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
2. The first parameter is WinZip,UltraEdit, PL/SQL Developer. So the below script is having the three Conditions in where clause.

SQL> select PRODUT_NAME from product
2 where PRODUT_NAME like 'WinZip%'
3 or PRODUT_NAME like 'UltraEdit%'
4 or PRODUT_NAME like 'PL/SQL Developer%';

PRODUT_NAME
------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
PL/SQL Developer 5.0
PL/SQL Developer 5.1

7 rows selected.
Issue: if we need to search 10 products then we need to add 10 Conditions in where clause.

Solution for Dynamic String search:

SQL> create or replace type str2tblType as table of varchar2(30);
2 /
Type created.


SQL> create or replace function str2tbl( p_str in varchar2 ) return str2tblType
2 PIPELINED
3 as
4 l_str long default p_str ',';
5 l_n number;
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
11 l_str := substr( l_str, l_n+1 );
12 end loop;
13 return;
14 end;
15 /

Function created.
Pass the parameter as a CSV.
SQL> variable foo varchar2(50);
SQL> exec :foo := 'WinZip%,UltraEdit%';

PL/SQL procedure successfully completed.
SQL> select product.PRODUT_NAME from product ,
2 (select *
3 from table( cast( str2tbl(:foo) as str2tblType ) )
4 where rownum >= 0) x
5 where product.PRODUT_NAME like x.column_value;


PRODUT_NAME
-------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0



SQL> exec :foo := 'WinZip%,UltraEdit%,PL/SQL Developer%';

PL/SQL procedure successfully completed.

SQL> select product.* from product ,
2 (select *
3 from table( cast( str2tbl(:foo) as str2tblType ) )
4 where rownum >= 0) x
5 where product.PRODUT_NAME like x.column_value;

PRODUT_NAME
------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
PL/SQL Developer 5.0
PL/SQL Developer 5.1

7 rows selected.
Script to implement it in SQL Server:


BEGIN

DECLARE @value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint,
@sText varchar(8000),
@sDelim varchar(20)


/* Provide list of software names */
SET @sText = 'WinZip,UltraEdit'
SET @sDelim =','
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1



if (SELECT OBJECT_ID('tempdb..#retArray')) > 0
begin
Drop table #retArray
end

create table #retArray (value char(1000))

WHILE @bcontinue = 1
BEGIN
IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT #retArray (value)
VALUES ('%'+@value+'%')
END
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
END
ELSE
BEGIN
SET @value = @sText
BEGIN
INSERT #retArray ( value) VALUES ('%'+@value+'%')
END
SET @bcontinue = 0
END
END




select distinct title ProductTitle,
Publisher Vendor,
version Productversion
from Product a , #retArray b
where rtrim(ltrim(a.title )) like rtrim(ltrim(b.value))
order by title
END

Read More

Oracle streams implementation at schema level

conn sys/password@dba1 as sysdba

create tablespace demo_TS datafile 'E:\TestDB\oradata\DBA1\demo_DAT.dbf' size 50M autoextend off extent management local;
create user demo IDENTIFIED BY demo DEFAULT TABLESPACE demo_TS TEMPORARY TABLESPACE TEMP;
ALTER USER demo QUOTA UNLIMITED on demo_TS;
grant connect to demo;


conn sys/password@dba2 as sysdba

create tablespace demo_TS datafile 'E:\TestDB\oradata\DBA2\demo_DAT.dbf' size 50M autoextend off extent management local;
create user demo IDENTIFIED BY demo DEFAULT TABLESPACE demo_TS TEMPORARY TABLESPACE TEMP;
ALTER USER demo QUOTA UNLIMITED on demo_TS;
grant connect to demo;

conn demo/demo@dba1

create table department
(departmentNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13));

create table emp
(EMPNO NUMBER(2),
DNO VARCHAR2(14),
LOC VARCHAR2(13));

insert into department values ( 10, 'ACCOUNTING', 'NEW YORK');
insert into department values ( 20, 'RESEARCH', 'DALLAS');
insert into department values ( 30, 'SALES' , 'CHICAGO');
insert into department values ( 40, 'OPERATIONS', 'BOSTON');


conn sys/password@dba1 as sysdba

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=1;
ALTER SYSTEM SET AQ_TM_PROCESSES=1;
ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
ALTER SYSTEM SET COMPATIBLE='9.2.0' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;


CONN sys/password@DBA1 AS SYSDBA

CREATE USER strmadmin_dept IDENTIFIED BY strmadminpw
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin_dept;

GRANT EXECUTE ON DBMS_AQADM TO strmadmin_dept;

GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin_dept;

GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin_dept;

GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin_dept;

GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin_dept;

GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin_dept;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'strmadmin_dept',
grant_option => FALSE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'strmadmin_dept',
grant_option => FALSE);
END;
/

CONNECT strmadmin_dept/strmadminpw@DBA1
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

CREATE DATABASE LINK dba2 CONNECT TO strmadmin_dept IDENTIFIED BY strmadminpw USING 'DBA2';

conn sys/password@dba2 as sysdba

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=1;
ALTER SYSTEM SET AQ_TM_PROCESSES=1;
ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
ALTER SYSTEM SET COMPATIBLE='9.2.0' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;


CONN sys/password@DBA2 AS SYSDBA

CREATE USER strmadmin_dept IDENTIFIED BY strmadminpw
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin_dept;

GRANT EXECUTE ON DBMS_AQADM TO strmadmin_dept;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin_dept;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin_dept;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin_dept;
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin_dept;
GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin_dept;

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'strmadmin_dept',
grant_option => FALSE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'strmadmin_dept',
grant_option => FALSE);
END;
/

CONNECT strmadmin_dept/strmadminpw@DBA2
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();


CONN sys/password@DBA1 AS SYSDBA
GRANT ALL ON demo.department TO strmadmin_dept;
GRANT ALL ON demo.emp TO strmadmin_dept;

CONN sys/password@DBA1 AS SYSDBA

CREATE TABLESPACE logmnr_ts1 DATAFILE 'E:\TestDB\ORADATA\DBA1\logmnr02.dbf'
SIZE 10 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnr_ts1');

CONN sys/password@DBA1 AS SYSDBA

ALTER TABLE demo.department ADD SUPPLEMENTAL LOG GROUP log_group_department_pk1 (departmentno) ALWAYS;
ALTER TABLE demo.emp ADD SUPPLEMENTAL LOG GROUP log_group_emp_pk1 (empno) ALWAYS;

CONNECT strmadmin_dept/strmadminpw@DBA1


BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'demo',
streams_name => 'dba1_to_dba2',
source_queue_name => 'strmadmin_dept.streams_queue',
destination_queue_name => 'strmadmin_dept.streams_queue@dba2',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'dba1');
END;
/

CONNECT strmadmin_dept/strmadminpw@DBA1

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'demo',
streams_type => 'capture',
streams_name => 'capture_demo',
queue_name => 'strmadmin_dept.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'dba1');
END;
/



exp userid=demo/demo@dba1 FILE=E:\TestDB\testDump\demo_instant.dmp OBJECT_CONSISTENT=y ROWS=n

imp userid=demo/demo@dba2 FILE=E:\TestDB\testDump\demo_instant.dmp IGNORE=y COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y


CONN sys/password@DBA2 AS SYSDBA
ALTER TABLE demo.department DROP SUPPLEMENTAL LOG GROUP log_group_department_pk1;
ALTER TABLE demo.emp DROP SUPPLEMENTAL LOG GROUP log_group_emp_pk1;



CONNECT strmadmin_dept/strmadminpw@dba1
DECLARE
v_scn NUMBER;
BEGIN
v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@DBA2(
source_schema_name => 'demo',
source_database_name => 'dba1',
instantiation_scn => v_scn);
END;
/


CONNECT strmadmin_dept/strmadminpw@DBA2

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'demo',
streams_type => 'apply',
streams_name => 'apply_demo',
queue_name => 'strmadmin_dept.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'dba1');
END;
/

CONNECT strmadmin_dept/strmadminpw@DBA2
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_demo',
parameter => 'disable_on_error',
value => 'n');

DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_demo');
END;
/


CONNECT strmadmin_dept/strmadminpw@DBA1
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_demo');
END;
/

Read More

GROUP BY queries and the equivalent GROUPING SET queries

Records in the table:

SQL> select * from Grouping_tbl;

A B AMT
-- --- ----------
10 101 55
10 102 75
20 8001 35
20 8002 45
10 101 25
20 8001 25

6 rows selected.

Script 1:

SQL> SELECT a, b, SUM(amt) FROM Grouping_tbl GROUP BY a, b;

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
20 8001 60
20 8002 45

SQL> SELECT a, b, SUM(amt) FROM Grouping_tbl GROUP BY GROUPING SETS ( (a,b) );

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
20 8001 60
20 8002 45

Script 2:


SQL> SELECT a, b, SUM( amt ) FROM Grouping_tbl GROUP BY a, b
2 UNION
3 SELECT a, null, SUM( amt ) FROM Grouping_tbl GROUP BY a
4 ;

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
10 155
20 8001 60
20 8002 45
20 105

6 rows selected.

SQL> SELECT a, b, SUM( amt ) FROM Grouping_tbl GROUP BY GROUPING SETS ( (a,b), a) ;

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
10 155
20 8001 60
20 8002 45
20 105

6 rows selected.


Script 3:

SQL> SELECT a, null, SUM( amt ) FROM Grouping_tbl GROUP BY a
2 UNION
3 SELECT null, b, SUM( amt ) FROM Grouping_tbl GROUP BY b;

A B SUM(AMT)
--- ----- ------
10 155
20 105
101 80
102 75
8001 60
8002 45

6 rows selected.

SQL> SELECT a,b, SUM( amt ) FROM Grouping_tbl GROUP BY GROUPING SETS (a,b) ;

A B SUM(AMT)
--- ----- ------
10 155
20 105
101 80
102 75
8001 60
8002 45

6 rows selected.

Script 4:


SQL> SELECT a, b, SUM( amt ) FROM Grouping_tbl GROUP BY a, b UNION
2 SELECT a, null, SUM( amt ) FROM Grouping_tbl GROUP BY a, null UNION
3 SELECT null, b, SUM( amt ) FROM Grouping_tbl GROUP BY null, b UNION
4 SELECT null, null, SUM( amt ) FROM Grouping_tbl;

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
10 155
20 8001 60
20 8002 45
20 105
101 80
102 75
8001 60
8002 45
260

11 rows selected.

SQL> SELECT a, b, SUM( amt ) FROM Grouping_tbl GROUP BY GROUPING SETS ( (a, b), a, b, ( ) );

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
10 155
20 8001 60
20 8002 45
20 105
101 80
102 75
8001 60
8002 45
260

11 rows selected.

Read More