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

Multitable Inserts

Multitable Inserts:

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions.

Scripts:

Scneraio1:
drop table org_tab;
drop table dept_tab;
drop table mgr_tab;

create table org_tab ( empid number,dept_name varchar2(10),manager_name varchar2(10));
insert into org_tab values ( 101,'sales','A');
insert into org_tab values ( 102,'sales','A');
insert into org_tab values ( 103,'sales','A');
insert into org_tab values ( 104,'sales','A');
insert into org_tab values ( 105,'sales','A');
insert into org_tab values ( 106,'Marketing','B');
insert into org_tab values ( 107,'Marketing','B');
insert into org_tab values ( 108,'Marketing','B');
insert into org_tab values ( 109,'Marketing','B');
insert into org_tab values ( 110,'Marketing','B');

SQL> select * from org_tab;
EMPID DEPT_NAME MANAGER_NA
---------- ---------- ----------
101 sales A
102 sales A
103 sales A
104 sales A
105 sales A
106 Marketing B
107 Marketing B
108 Marketing B
109 Marketing B
110 Marketing B

10 rows selected.

create table dept_tab (empid number,dept_name varchar2(10));
create table mgr_tab (empid number,manager_name varchar2(10));

SQL> INSERT ALL
2 INTO dept_tab values ( empid,dept_name)
3 INTO mgr_tab values ( empid,manager_name)
4 SELECT empid,dept_name,manager_name from org_tab ;

20 rows created.

SQL> select * from dept_tab;
EMPID DEPT_NAME
---------- ----------
101 sales
102 sales
103 sales
104 sales
105 sales
106 Marketing
107 Marketing
108 Marketing
109 Marketing
110 Marketing

10 rows selected.
SQL> select * from mgr_tab;
EMPID MANAGER_NA
---------- ----------
101 A
102 A
103 A
104 A
105 A
106 B
107 B
108 B
109 B
110 B

10 rows selected.
Scneraio2:

drop table dept_sales;
drop table dept_market;
create table dept_sales (empid number,dept_name varchar2(10));
create table dept_market (empid number,dept_name varchar2(10));


SQL> INSERT ALL
2 WHEN dept_name = 'sales' THEN
3 INTO dept_sales values ( empid,dept_name)
4 WHEN dept_name = 'Marketing' THEN
5 INTO dept_market values ( empid,dept_name)
6 SELECT empid,dept_name,manager_name from org_tab ;

10 rows created.

SQL> select * from dept_sales;
EMPID DEPT_NAME
---------- ----------
101 sales
102 sales
103 sales
104 sales
105 sales

SQL> select * from dept_market;
EMPID DEPT_NAME
---------- ----------
106 Marketing
107 Marketing
108 Marketing
109 Marketing
110 Marketing
The restrictions on multitable INSERTs are:

• Multitable inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multitable insert via a DB link.
• You cannot perform multitable inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the subquery of the multitable insert statement.

Read More

Table Space Status Script

Script to identify the table space status:
SELECT Substr(ddf.tablespace_name,1,20) "Tablespace Name",
Round(ddf.bytes/1024/1024,2) "Allocated Bytes(MB)",
Round(de.used_bytes/1024/1024,2) "Used Bytes(MB)",
Round(dfs.free_bytes/1024/1024,2) "Free Bytes(MB)",
Round((de.used_bytes/ddf.bytes)*100,2) "% Used Bytes ",
Round((dfs.free_bytes/ddf.bytes)*100,2) "% Free Bytes"
FROM DBA_DATA_FILES DDF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) DE,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) dfs
WHERE de.file_id = ddf.file_id
AND ddf.file_id = dfs.file_id
ORDER BY ddf.tablespace_name;

Read More