PGA and UGA Memory Usage Script

SELECTs.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 snwhere ss.sid = s.sid andsn.statistic# = ss.statistic# andsn.name = 'session pga memory') session_pga_memory, (select ss.value from v$sesstat ss, v$statname snwhere ss.sid = s.sid andsn.statistic# = ss.statistic# andsn.name = 'session pga memory max') session_pga_memory_max, (select ss.value from v$sesstat ss, v$statname snwhere ss.sid = s.sid andsn.statistic#...

Read More

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

Oracle streams implementation at schema level

conn sys/password@dba1 as sysdbacreate 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 sysdbacreate 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...

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 5510 102 7520 8001 3520 8002 4510 101 2520 8001 256 rows selected. Script 1: SQL> SELECT a, b, SUM(amt) FROM Grouping_tbl GROUP BY a, b;A B SUM(AMT)--- ----- ------10 101 8010 102 7520 8001 6020 8002 45SQL> SELECT a, b, SUM(amt) FROM Grouping_tbl GROUP BY GROUPING SETS ( (a,b) );A B SUM(AMT)--- ----- ------10 101 8010 102 7520 8001 6020 8002 45 Script 2: SQL> SELECT a, b, SUM( amt ) FROM Grouping_tbl GROUP BY a, b2 UNION3 SELECT a, null, SUM( amt...

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...

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_bytesFROM dba_extentsGROUP by file_id) DE,(SELECT Max(bytes) free_bytes,file_idFROM dba_free_spaceGROUP BY file_id)...

Read More

TRUNCATE Vs Index Status

Comments: The Redo size of the current session SQL> select name,a.valuefrom v$sesstat a, v$sysstat bwhere 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.valuefrom v$sesstat a, v$sysstat bwhere b.statistic#=a.statistic#and b.name = 'redo size' and sid = 16;NAME VALUE -------------- ----------redo size 69912 Comments: create...

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 = ...

Read More

Query to identify the SID of current session

select sid from V$mystat where rownum ...

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(*)----------27189SQL> select name,a.value2 from v$sesstat a, v$sysstat b3 where b.statistic#=a.statistic#4 and b.name like 'redo...

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...

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 1520 2317 20 Result of OR: select * from test_tab where num1 = 10 or num2 = 20;NUM1 NUM2---------- ----------10 1517 20 Result of UNION: select * from test_tab where num1 = 10unionselect * from test_tab where num2 = 20; NUM1 NUM2----------...

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: 11 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1Comments: We know that, we have to create index on the “NAME” field.create index NAME_IDX...

Read More