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

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

TRUNCATE Vs Index Status

Comments: The Redo size of the current session

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;NAME VALUE
-------------- ----------
redo size 32404
Comments: create the CTAS table with NOLOGGING.

SQL> create table t nologging as select * from all_objects where 1=0;

Table created.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;
NAME VALUE
-------------- ----------
redo size 69912

Comments: create the index with NOLOGGING option.

SQL> create index object_name_idx on t(object_name) nologging;

Index created.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;
NAME VALUE
-------------- ----------
redo size 87920

Comments: Insert the records into table with the append hint. It will not create any Redo for the table data but it will create a Redo for the index. Check the Redo size and It is high.

SQL> insert /*+ append */ into t select * from all_objects;

27196 rows created.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;

NAME VALUE
-------------- ----------
redo size 4302432

Comments: Make the index as UNUSABLE state.

SQL> alter index object_name_idx unusable;

Index altered.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;
NAME VALUE
-------------- ----------
redo size 4305028
Comments: Check the status of the index and truncate the table data.

SQL> select index_name, status from user_indexes where table_name = 'T';INDEX_NAME STATUS
------------------------------ --------
OBJECT_NAME_IDX UNUSABLE

SQL> truncate table t;

Table truncated.
Comments: Insert the records into table with the append hint.

SQL> insert /*+ append */ into t select * from all_objects;

27196 rows created.

Comments: Now we check the Redo generation. Still it is high and it has generated the Redo for the index because the TRUNCATE operation has made that index as VALID.


SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;

NAME VALUE
-------------- ----------
redo size 8529448

Comments: check the current status of the index.

SQL> select index_name, status from user_indexes where table_name = 'T';
INDEX_NAME STATUS
------------------------------ --------
OBJECT_NAME_IDX VALID

Read More

Identify the redo size of the current session

select name,a.value from v$sesstat a, v$sysstat b where b.statistic#=a.statistic# and b.name = 'redo size' and sid = 16;

Read More

Query to identify the SID of current session

select sid from V$mystat where rownum =1;

Read More

NOLOGGING Vs Redo entries.

Title: NOLOGGING Vs Redo entries.
Comments: This document is related to the redo log generation of the scripts in LOGGING mode and the NOLOGGING mode.

Checking the redo generation for table creation:
Ø Checking the record count and the current Redo log size of the session.

SQL> select count(*) from all_objects;



COUNT(*)
----------
27189
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 01 row selected.
Ø Creating the table CTAS (Create Table As Select) table with the NOLOGGING mode. It is not creating the Redo logs for the data.

SQL> create table t NOLOGGING as select * from all_objects;

Table created.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 56732
Ø Creating the table CTAS table with the LOGGING mode. It is creating the Redo logs for the data.

SQL> create table tt as select * from all_objects;
Table created.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 31768281 row selected.
Ø Creating the table CTAS table with the NOLOGGING mode (only table structure) and INSERT the records. It is creating the Redo logs for the data.

SQL> create table ttt nologging as select * from all_objects where 1=2;
Table created.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 3212880
1 row selected.

SQL> insert into ttt
2 select * from all_objects;

27192 rows created.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 6257108

1 row selected.
Ø Creating the table CTAS table with the LOGGING mode (only table structure) and INSERT records with /*+ APPEND */ Hint. The Redo logs are not generated for the data.

SQL> create table tttt nologging as select * from all_objects where 1=2;
Table created.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 6293312
1 row selected.
SQL> INSERT /*+ APPEND */ INTO tttt select * from all_objects;
27193 rows created.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 6311384
1 row selected.

Redo Log generation table:
The Below table shows when the log generates.




----------- ------------- --------------- ---------------
Checking the redo generation for indexes:
Ø Drop the Redo_check table.

SQL> drop table redo_check;
Table dropped.

Ø Checks the current redo size of the session.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 25016
Ø Create a table and insert the records.

SQL> create table redo_check (
2 num number,
3 data1 varchar2(20)
4 );

Table created.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 32460
SQL> declare
2 i int;
3 begin
4 for i in 1 .. 200000
5 loop
6 insert into redo_check values(i,'Redo check ' i);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 49555748
Ø Create the index with NOLOGGING mode.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 495558601 row selected.
SQL> create index dat_idx on redo_check (data1) nologging;
Index created.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 49633080

1 row selected.
Ø We try to update the index column and check the Redo generation.




SQL> update redo_check set data1 = ' redo check' where num <100001 em="">
Ø Yes. The updates are generating the Redo logs for the table entry and index if we create a table in the NOLOGGING mode also.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 127536500
1 row selected.SQL> commit;Commit complete.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 127536592
1 row selected.
Ø We drop the index and try to update the records.

SQL> drop index dat_idx;
Index dropped.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 127548252
1 row selected.
SQL> update redo_check set data1 = ' redo check' where num < 100001;Ø Now it has created the redo only for the table entry.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 152859364
1 row selected.
Ø After that creating the index with NOLOGGING. This is one of the best ways to increase the performance of the scripts.
SQL> create index dat_idx on redo_check (data1) nologging;
Index created.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 152906760
1 row selected.SQL> commit;
Ø Now we check the redo generation with unusable the index and rebuilding the index way.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 0Ø Make the index as an Unusable and update the records in the table.
SQL> alter index dat_idx unusable;
Index altered.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 2224
SQL> update redo_check set data1 ='reupdate' where num < data1 ="'reupdate'">
Ø The update statement is giving the error because the index is in the unusable state. To void this error message alter the current session with below statement.
SQL> alter session set skip_unusable_indexes=true;
Session altered.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 2876
Ø Execute the update statement now and check the redo log. It has created the redo generation only for the table entries.
SQL> update redo_check set data1 ='reupdate' where num < 100001;>SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 24896420
Ø Rebuild the index and if we check the redo generation. It did not create any redo for the index entries.

SQL> alter index data1_idx rebuild;
Index altered.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;

NAME VALUE
------------ ----------
redo size 24934104

Read More

Indexes on two different table space( Associated to different schema)

Question: Can we create indexes on two different table spaces. ( Associated to different schema)

Comments: The answer is “Yes”. We can create a table in one table space and index in another table space.

Now the question is some thinking different. Can we create an index on some other user’s table space (Demo2_TS) which is not associated to the current user (Demo2_TS)? (Where table has created)

Scripts:
sql> conn demo1/demo1 ;
sql> create table table_in_demo1 ( col1 number, col2 number );
sql> insert into table_in_demo1 values (10,11);
sql> insert into table_in_demo1 values (20,21);
sql> insert into table_in_demo1 values (30,31);
sql> select * from table_in_demo1;

COL1 COL2
----- ------

10 11
20 21
30 31

sql> grant all on table_in_demo1 to demo2
sql> conn demo2/demo2
sql> select * from demo1.table_in_demo1
COL1 COL2
------ -----
10 11
20 21
30 31
Comments: Now the Demo2 is having the access to “table_in_demo1”. Can we create a index on table_in_demo1 table from DEMO2?

The table is available in the Demo1_TS table space. Will it allow to create a index in the
Demo2_TS. (The demo2 is associated with the Demo2_TS).

Let’s check in the Demo2 schema

Scripts:
sql> conn demo2/demo2
sql> create index tab_idx on demo1.table_in_demo1(col2);

sql> select owner,index_name,table_owner from all_indexes
where table_name = 'TABLE_IN_DEMO1';

Solution:
It is allowing programmer to create an index in Demo2 schema.

sql> conn demo1/demo1
sql> select * from table_in_demo1 where col2 = 21;
Explain plan from Demo1 schema:

So,

We can create an index on some other user’s table space (Demo2_TS) which is not associated to the current user (Demo1_TS)

Comments:
One more question. What will happen if we revoke the permission from Demo1 schema?

No. Two more question because of the above question.

Question 1: will it allow programmer to revoke the permission?
Question 2: if it allows programmer to revote, will it use the query use that index?

Answer for Question 1:sql> conn demo1/demo1
sql> revoke all on table_in_demo1 from demo2;
Yes. It is allowing programmer to revoke.

Answer for Question 2:
sql> select * from table_in_demo1 where col2 = 21;
Explain plan from Demo1 schema:

Wow! What a surprise! Still it is using Demo2 index.

We try to insert one record.

sql> conn demo1/demo1
sql> insert into table_in_demo1 values (40,41);
sql> select * from table_in_demo1;
COL1 COL2
----- ------
10 11
20 21
30 31
40 41
Yes. It is allowing programmer to insert.

Read More

UNION = OR?

Question: Are UNION and OR doing to same or different?

Comments: We check with some data.

Scripts:

create table test_tab ( num1 number, num2 number );
insert into test_tab values(10,15);
insert into test_tab values(20,23);
insert into test_tab values(17,20);
commit;
select * from test_tab;
NUM1 NUM2
---------- ----------
10 15
20 23
17 20
Result of OR:
select * from test_tab where num1 = 10 or num2 = 20;

NUM1 NUM2
---------- ----------
10 15
17 20
Result of UNION:
select * from test_tab where num1 = 10
union
select * from test_tab where num2 = 20;
NUM1 NUM2
---------- ----------
10 15
17 20
Conclusion:
Wow! The result of Union and OR clause is same.
So
UNION=OR
Do not come to conclusion with above result. Check with some other scenario.
Question: UNION and OR will give same result ?

Comments: check with some other data.

Scripts:

drop table test_tab;create table test_tab
(
num1 number,
num2 number
);
insert into test_tab values ( 1,1);
insert into test_tab values ( 1,1);
Result of OR:

select * from test_tab where num1 = 1 or num2 = 1;
NUM1 NUM2
---------- ----------
1 1
1 1
Result of UNION:

select * from test_tab where num1 = 1
union
select * from test_tab where num2 = 1;
NUM1 NUM2
---------- ----------
1 1
Result of UNION ALL:
select * from test_tab where num1 = 1
union all
select * from test_tab where num2 = 1;

NUM1 NUM2
---------- ----------
1 1
1 1
1 1
1 1
Conclusion:
Yes! The result of Union and OR clause is not same.

So
UNION<>OR

Read More

Index scan on Null able column

Issue: Tune the below SQL script.

Comments: The statement is having the two conditions in the WHERE clause. One it is checking the number and another one is checking the NULL value.
Scripts:
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836' or NAME IS NULL
Explain plan: It is going for the TABLE ACCESS FULL.
PlanSELECT STATEMENT CHOOSE Cost: 156 Bytes: 81 Cardinality: 1
1 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1
Comments: We know that, we have to create index on the “NAME” field.
create index NAME_IDX on CUSTOMER_ORDER_TUNE(NAME)

Comments: Now we check the explain plan for the script.
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836' or NAME IS NULL
Explain plan: Still it is going for the TABLE ACCESS FULL because the script is checking the NULL.

Plan
SELECT STATEMENT CHOOSE Cost: 156 Bytes: 81 Cardinality: 1
1 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1

Comments: Let we divide the SQL statement as a two part and we check the explain plan.

select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836'
union all
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME IS NULL
Explain plan: Good! Now partially it is going for the index scan. The second part of the query is going for the FULL TABLE access. The reason is, because the NAME is null able, the index on only on name and entirely null keys are NOT entered into b*tree indexes.

Plan
SELECT STATEMENT CHOOSE Cost: 158 Bytes: 162 Cardinality: 2
4 UNION-ALL
2 TABLE ACCESS BY INDEX ROWID SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 2 Bytes: 81 Cardinality: 11 INDEX RANGE SCAN NON-UNIQUE SLSFORCE.NAME_IDX Cost: 1 Cardinality: 1
3 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1


Comments: Still it is not going for the index scan. So we drop the index and create it on different way.
drop index NAME_IDX
create index NAME_IDX on CUSTOMER_ORDER_TUNE(NAME,0)
Comments: Now we check the explain plan for the script.

select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836'
union all
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME IS NULL
Explain plan: Wow! Now it is going for the index scan and cost also very less.

Plan
SELECT STATEMENT CHOOSE Cost: 4 Bytes: 162 Cardinality: 2
5 UNION-ALL
2 TABLE ACCESS BY INDEX ROWID SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 3 Bytes: 81 Cardinality: 1
1 INDEX RANGE SCAN NON-UNIQUE SLSFORCE.NAME_IDX Cost: 2 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 1 Bytes: 81 Cardinality: 1
3 INDEX RANGE SCAN NON-UNIQUE SLSFORCE.NAME_IDX Cost: 1 Cardinality: 1

Read More