0% found this document useful (0 votes)

422 views

Dba Code

This document contains tips for summarizing and analyzing Oracle database objects and usage. The first tip shows how to suppress repeating groups in a query result using the RANK analytic function instead of ROWNUM. The second tip demonstrates a function to count the number of occurrences of a substring within a string. The third tip describes a query to identify numeric and non-numeric records in a table.

Uploaded by

Pradeep Kumar

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOC, PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

422 views

Dba Code

Uploaded by

Pradeep Kumar

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOC, PDF, TXT or read online on Scribd

You are on page 1/ 83

Tip for Week of November 29, 2004

Suppress Repeating Groups Using RANK (not ROWNUM)

This tip comes from Mohammad Anwar, Oracle Developer, Tuskerdirect Ltd, in London, United Kingdom.

This tip achieves the same output as the Tip for Week of August 23, 2004. In that tip, the author uses
ROWNUM and inline views, whereas I use the analytic function RANK() to do the job.

Here is the complete script:

create table emp (

empno number(3),
dept number(3),
name varchar2(15)
);

insert into emp values(1,100,'Tracey Turner');

insert into emp values(10,100,'Peter Watson');
insert into emp values(99,200,'David Truman');
insert into emp values(80,200,'William Fender');
insert into emp values(2,300,'Sara Joshua');
insert into emp values(3,200,'Joseph Derner');
insert into emp values(60,100,'Sandra Harper');

Original Statement:

select decode(rownum-min_sno,0,a.dept,null) dept,

decode(rownum-min_sno,0,1,rownum+1-min_sno) sno,
a.empno,name
from (select * from emp order by dept,empno ) a,
(select dept,min(rownum) min_sno
from ( select * from emp order by dept,empno )
group by dept ) b
where a.dept=b.dept
/

Modified Statement using RANK():

select case when rank() over (partition by dept order by empno) = 1 then
dept else null end as dept,
rank() over (partition by dept order by empno) as sno,
empno,
name
from emp;
/
Output:

DEPT SNO EMPNO NAME

---------- ---------- ---------- ---------------
100 1 1 Tracey Turner
2 10 Peter Watson
3 60 Sandra Harper
200 1 3 Joseph Derner
2 80 William Fender
3 99 David Truman
300 1 2 Sara Joshua

Tip of the Week

Tip for Week of December 13, 2004

Identify and Count Characters

This tip comes from Aui de la Vega, DBA, in Makati, Philippines.

This function provides the number of times a pattern occurs in a string (VARCHAR2).

SQL> CREATE FUNCTION NUM_CHARS(INSTRING VARCHAR2, INPATTERN VARCHAR2)

RETURN NUMBER
IS
COUNTER NUMBER;
NEXT_INDEX NUMBER;
STRING VARCHAR2(2000);
PATTERN VARCHAR2(2000);
BEGIN
COUNTER := 0;
NEXT_INDEX := 1;
STRING := LOWER(INSTRING);
PATTERN := LOWER(INPATTERN);
FOR I IN 1 .. LENGTH(STRING) LOOP
IF (LENGTH(PATTERN) <= LENGTH(STRING)-NEXT_INDEX+1)
AND (SUBSTR(STRING,NEXT_INDEX,LENGTH(PATTERN)) = PATTERN) THEN
COUNTER := COUNTER+1;
END IF;
NEXT_INDEX := NEXT_INDEX+1;
END LOOP;
RETURN COUNTER;
END;

Function created.

*Note: Use CREATE to make sure you are not overwriting an existing function.

Try it out:
SQL> select num_chars('miSSissipPi','i') from dual
2 /

NUM_CHARS('MISSISSIPPI','I')
----------------------------
4

SQL> c/'i'/'ssi'
1* select num_chars('miSSissipPi','ssi') from dual
SQL> /

NUM_CHARS('MISSISSIPPI','SSI')
------------------------------
2

SQL> c/ssi'/ppi'
1* select num_chars('miSSissipPi','ppi') from dual
SQL> /

NUM_CHARS('MISSISSIPPI','PPI')
------------------------------
1
SQL> c/'ppi/'p
1* select num_chars('miSSissippi','p') from dual
SQL> /

NUM_CHARS('MISSISSIPPI','P')
----------------------------
2

SQL> c/p'/s'
1* select num_chars('miSSissippi','s') from dual
SQL> /

NUM_CHARS('MISSISSIPPI','S')
----------------------------
4

Tip of the Week

Tip for Week of December 6, 2004

Find Numeric and Non-Numeric Records

This tip comes from Ilya Petrenko, Senior Oracle DBA, Open Distributed Solutions, Inc., in Jamison,
Pennsylvania.

This script uses two Oracle functions—TRANSLATE and Length—to identify if data includes numeric or non-
numeric characters.

create table TMP_DATA

(ROW_SEQ# NUMBER,
STRING# VARCHAR2(100)
);

INSERT INTO TMP_DATA

SELECT ROWNUM,
OBJECT_NAME
||DECODE( MOD(ROWNUM,4), 0, NULL,to_char(ROWNUM))
||OBJECT_TYPE
from user_objects
where object_name like 'T%'
UNION ALL
select 100, ' 87 ' from dual
UNION ALL
select 100, '0123' from dual
UNION ALL
select 100, '911' from dual
;

col STRING# for a60

select * from TMP_DATA;

Find ALL rows where you have numeric characters only:

select ROW_SEQ#, STRING#

from TMP_DATA
WHERE length(STRING#)
- Length( TRANSLATE(STRING#, CHR(1)||TRANSLATE(String#,
CHR(1)||'1234567890', CHR(1) ), CHR(1) ) ) = 0
;

ROW_SEQ# STRING#
---------- --------
100 0123
100 911

Find ALL rows where you have non-numeric characters only:

select ROW_SEQ#, STRING#

from TMP_DATA
WHERE length(STRING#)
- Length( TRANSLATE(STRING#, CHR(1)||TRANSLATE(String#,
CHR(1)||'1234567890', CHR(1) ), CHR(1) ) ) > 0
;

ROW_SEQ# STRING#
---------- ------------------------------------
1 T0011TABLE
2 TEST22TABLE
3 TMP_DATA3TABLE
5 TRANS5TABLE
6 TRANS216TABLE
7 TRIV7TRIGGER
100 87
Tip of the Week
Tip for Week of November 22, 2004

Tablespace Owner and Usage

This tip comes from Nikhil Dave, Sr. Software Enginner, Hexaware Technologies Ltd., in Navi Mumbai,
India.

This tip shows the tablespace owner and usage for all within a database.

SET ECHO OFF

set newpage 0
ttitle center 'Database Usage by user and Tablespace'-
right 'Page:' format 999 sql.pno skip skip
break on owner skip 2
col K format 999,999,999 heading 'Size K'
col ow format a24 heading 'Owner'
col ta format a30 heading 'Tablespace'
spool tfsdbspa.lst
set feedback off
set feedback 6
select us.name ow,
ts.name ta,
sum(seg.blocks*ts.blocksize)/1024 K
from sys.ts$ ts,
sys.user$ us,
sys.seg$ seg
where seg.user# = us.user#
and ts.ts# = seg.ts#
group by us.name,ts.name
/
prompt End of Report
spool off
ttitle off
clear breaks
clear columns
clear computes
set verify on

Tip of the Week

Tip for Week of November 8, 2004

Generate Factorials

This tip comes from Kailasanathan Subbaraman, Senior Technical Architect, Hexaware Technologies, in
East Brunswick, New Jersey.

The following code snippet uses Oracle analytic functions to generate factorials. This snippet generates a
factorial for each row number up to 20.
select rownum,
exp(sum(ln(rownum)) over (order by rownum)) Factorial
from user_objects
where rownum < 21;

Tip of the Week

Tip for Week of November 1, 2004

Check TEMP Usage

This tip comes from Jony Safi, Database Administrator, Videotron Ltee, in Montreal, Canada.

This query lists information needed by a DBA to trace the use of the TEMP tablespace.

SELECT s.sid "SID",s.username "User",s.program "Program", u.tablespace

"Tablespace",
u.contents "Contents", u.extents "Extents", u.blocks*8/1024 "Used Space
in MB", q.sql_text "SQL TEXT",
a.object "Object", k.bytes/1024/1024 "Temp File Size"
FROM v$session s, v$sort_usage u, v$access a, dba_temp_files k, v$sql q
WHERE s.saddr=u.session_addr
and s.sql_address=q.address
and s.sid=a.sid
and u.tablespace=k.tablespace_name;

Tip of the Week

Tip for Week of November 1, 2004

Check TEMP Usage

This tip comes from Jony Safi, Database Administrator, Videotron Ltee, in Montreal, Canada.

This query lists information needed by a DBA to trace the use of the TEMP tablespace.

SELECT s.sid "SID",s.username "User",s.program "Program", u.tablespace

Tip of the Week

Tip for Week of October 25, 2004
Weed Out the Numeric

This tip comes from Amit Zhankar, DBA, Tata Share Registry Limited, in Mumbai, India.

This tip selects a column from a table, weeds out the numeric characters, and returns the alphabetical
characters.

Note: This tip was written for use with Oracle8i.

create table Vtable ( name varchar2(20));

insert into Vtable values('AMIT ZHANKAR');

insert into Vtable values('PANKAJ NARKHEDE');
insert into Vtable values('ASHISH GHELANI');
insert into Vtable values('TEJWANT SINGH');
insert into Vtable values('SUKHDEV DARIRA');
insert into Vtable values('BILL GATES');

insert into Vtable values('AMIT 1ZHANKAR');

insert into Vtable values('PANKAJ 2NARKHEDE');
insert into Vtable values('ASHISH 3GHELANI');
insert into Vtable values('TEJWANT 4SINGH');
insert into Vtable values('SUKHDEV 5DARIRA');
insert into Vtable values('BILL 6GATES');

insert into Vtable values('AMIT 7ZHANKAR');

insert into Vtable values('PANKAJ 8NARKHEDE');
insert into Vtable values('ASHISH 9GHELANI');
insert into Vtable values('TEJWANT 0SINGH');
insert into Vtable values('SUKHDEV 11DARIRA');
insert into Vtable values('BILL 22GATES');
insert into Vtable values('11BILL 22GATES');
insert into Vtable values('2BILL 22GATES');
insert into Vtable values('22BILL 22GATES');

select name from Vtable

select
name ,
translate('.1234567890',name,'$')
from
Vtable
where
length(translate('1234567890','$'||name,'$')) = 10
/

Truncate table Vtable;

drop table Vtable;

Tip of the Week

Tip for Week of September 13, 2004
Display the PL/SQL Dependency Tree

This tip comes from Casimir Saternos, Systems Engineer, Trifecta Technologies Inc, in Allentown,
Pennsylvania.

As PL/SQL a code base for an application grows over time, the number of dependencies that exist can grow
and become difficult to manage. This can be especially difficult for the DBA being called upon to support a
legacy system that has had numerous independent developers contributing code. The following query can
be used to identify the objects that a given object is dependent upon:

select lvl, u.object_id, u.object_type, LPAD(' ',lvl)||object_name obj

FROM

SELECT level lvl, object_id

FROM SYS.public_dependency s

START WITH s.object_id = (select object_id

from user_objects

where object_name=UPPER('&OBJECT_NAME')

and object_type =UPPER( '&OBJECT_TYPE' )

CONNECT BY s.object_id = PRIOR referenced_object_id

GROUP BY level, object_id

)tree,

user_objects u

WHERE

tree.object_id = u.object_id

ORDER BY lvl

When prompted, enter the OBJECT_NAME of the object whose dependencies you want to identify.
The OBJECT_NAME can be a PACKAGE, PACKAGE BODY, or PROCEDURE. The OBJECT_NAME is the
name of the object at the root of the tree.

Tip of the Week

Tip for Week of September 6, 2004

Another Way to Find the Wait

This tip comes from Wolfgang Genser, Database Administrator, ONE, in Vienna, Austria.

This tip provides an alternate way to get the name of the file, segment, or resource that's causing a wait,
with times.
Suppose you need the object_name, ..., from dba_extents for an event. Traditionaly you can look at block_id
(P1) and file_id (P2) from v$session_wait as follows:

12:57:16 SQL> select segment_name,partition_name

from dba_extents
where 15017
between block_id and (block_id + blocks -1) and file_id=1;

SEGMENT_NAME PARTITION_NAME

------------------------ --------------

LOGSTDBY$APPLY_PROGRESS P0

Elapsed: 00:00:27.19

Here is a script--named search_object_with_block_id.sql in the execution that follows--

that returns a similar output based on object information:

select

o.name as OBJECT_NAME,

o.subname as OBJECT_SUBNAME,

so.object_type as OBJECT_TYPE

from

sys.obj$ o,

sys.sys_objects so,

sys.seg$ s

where

s.file# = so.header_file

and s.block# = so.header_block

and o.obj# = so.object_id

and s.type# = so.segment_type_id

and o.type# = so.object_type_id

and s.block# = 15017 --(P1)

and s.file#=1; --(P2)

Note the elapsed time in this result:

12:59:24 SQL> sta

/var/adm/reporting/TUNING/search_object_with_block_id.sql

OBJECT_NAME OBJECT_SUBNAME
OBJECT_TYPE

------------------------------ ------------------------------
------------------

LOGSTDBY$APPLY_PROGRESS P0 TABLE
PARTITION

Elapsed: 00:00:00.13

Tip of the Week

Tip for Week of August 30, 2004

EXPLAIN Plan Workaround

This tip comes from Jaromir Nemec , consultant at DBN, in Vienna, Austria.

Note: This tip was written for use with Oracle9i.

The general rule for performing an explain plan is that you can perform the explain plan only if you have
sufficient privileges to execute the statement. It means with only read rights you cannot explain, for example,
an INSERT statement or any other DML statement.

But there is a simple workaround, if your DBA--as a user that has sufficient privilegs--creates the following
procedure:

CREATE OR REPLACE package utl_explan AS

--
PROCEDURE explan(
i_sql_text IN VARCHAR2, --- text of SQL statement to be explained
i_plan_table IN VARCHAR2 DEFAULT 'SYS.PLAN_TABLE', --- plan table
[owner.]table_name[@link]
i_statement_id IN VARCHAR2 DEFAULT USER -- statement_id in explain
table
)
;
--
END utl_explan;
/
CREATE OR REPLACE package BODY utl_explan as
--
PROCEDURE explan(
i_sql_text IN VARCHAR2,
i_plan_table IN VARCHAR2 DEFAULT 'SYS.PLAN_TABLE',
i_statement_id IN VARCHAR2 DEFAULT USER
)
--
IS
v_sql_text VARCHAR2(32000);
v_sqlerrm VARCHAR2(2000);
--
BEGIN
--
BEGIN
v_sql_text := 'EXPLAIN PLAN '||
'SET STATEMENT_ID = '''||i_statement_id||''' ' ||
'INTO '||i_plan_table ||' '||
'FOR! '|| i_sql_text;
---
EXECUTE IMMEDIATE v_sql_text;
EXCEPTION
WHEN others THEN
v_sqlerrm := SQLERRM;
raise_application_error(-20101, 'wrong SQL '||v_sqlerrm);
END;
END explan;
--
END utl_explan;
/
Then have the DBA grant you privileges to execute this procedure and use it in the following way:

SQL> @test_explan
SQL> exec utl_explan.explan(-
> 'select * -
> from dual'-
> )

PL/SQL procedure successfully completed.

View the execution plan using the DBMS_XPLAN package (the default statement_id is your user name):

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY('sys.plan_table',

'NEMECJ')) ;

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------
--------

--------------------------------------------------------------------

Note: rule based optimization

9 rows selected.

You may now, because of definers rights of the procedure, perform explain plan on a statement that you
can't execute.

Don't forget that this is a workaround. Import the database statistics to your environment where you have full
rights and test and tune there.

Tip of the Week

Tip for Week of August 23, 2004

Reset ROWNUM and Suppress Repeating Groups

This tip comes from Satyanarayana Josyula, Sr. Oracle DBA at Nexus Energy Software, in Reston, Virginia.

Note: This tip was written for use with Oracle9i.

You can reset the serial numbers after each group break using a single query--without using any utilities.
This technique uses the ROWNUM returned by Oracle and resets the serial number at the beginning of
each group. Also this query shows how to suppress the repeating group values in the result set.

Let us look at this simple example:

Table test

Name Null? Type

----------------------------------------- -------- ---------------

EMPNO NUMBER(3)
DEPT NUMBER(3)
NAME VARCHAR2(15)

SQL> SELECT * FROM emp;

EMPNO DEPT NAME

---------- ---------- ---------------
1 100 Tracey Turner
10 100 Peter Watson
99 200 David Truman
80 200 William Fender
2 300 Sara Joshua
3 200 Joseph Derner
60 100 Sandra Harper
Suppose you want output in the form:

DEPT SNO EMPNO NAME

---------- ---------- ---------- ---------------
100 1 1 Tracey Turner
2 10 Peter Watson
3 60 Sandra Harper
200 1 3 Joseph Derner
2 80 William Fender
3 99 David Truman
300 1 2 Sara Joshua
You can use the following query:

SELECT DECODE(ROWNUM-min_sno,0,a.dept,NULL) dept,DECODE(ROWNUM-

min_sno,0,1,rownum+1-min_sno) sno,a.empno,name FROM
(SELECT * FROM emp ORDER BY dept,empno ) a,
(
SELECT dept,MIN(rownum) min_sno FROM
(
SELECT * FROM emp ORDER BY dept,empno
)
GROUP BY dept
) b
WHERE a.dept=b.dept
/
Note: The following revision of this query works with the standard SCOTT/TIGER sample schema:

SELECT DECODE(ROWNUM-min_sno,0,a.deptno,NULL) dept,DECODE(ROWNUM-

min_sno,0,1,rownum+1-min_sno) sno,a.empno,ename FROM

(SELECT * FROM emp ORDER BY deptno,empno ) a,

(SELECT deptno,MIN(rownum) min_sno FROM (SELECT * FROM emp ORDER BY

deptno,empno)GROUP BY deptno) b

WHERE a.deptno=b.deptno

Tip of the Week

Tip for Week of August 16, 2004

Get Table Details with SQL*Plus

This tip comes from Ajay Garg, a developer at Accenture Services Pvt. Ltd., in Bangalore, India.

Note: This tip was written for use with Oracle9i.

You can use SQL*Plus to provide the following details about a table:
1. Column Details
2. PRIMARY KEY
3. INDEXES
4. FOREIGN KEYS
5. CONSTRAINTS
6. ROWCOUNT
7. Other Tables That REFER to this Table
8. PARTITIONED COLUMNS
9. PARTITIONS
10. TRIGGERS
11. DEPENDANTS
I use the following code in SQL*Plus to provide this information:
SET AUTOTRACE OFF
SET TIMING OFF
COLUMN COMMENTS FORMAT A50
COLUMN column_name FORMAT A35
COLUMN Data_Type FORMAT A15
COLUMN DATA_DEFAULT FORMAT A20
COLUMN "PK Column" FORMAT A35
COLUMN "FK Column" FORMAT A20

UNDEF Owner
ACCEPT Owner PROMPT 'Enter Owner :'

UNDEF Table_Name
ACCEPT Table_Name PROMPT 'Enter Table Name :'

SET HEADING OFF

PROMPT
PROMPT Comments for Table &Table_Name.
SELECT COMMENTS
FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.') ;

SET HEADING ON

PROMPT
PROMPT Column Details for Table &Table_Name.

SELECT
ROWNUM "Sr No", T.COLUMN_NAME , T.Data_Type , T.DATA_LENGTH,
DECODE(T.Nullable, 'N' , 'NOT NULL' , 'Y', ' ') NULLABLE ,
T.Data_Default , C.Comments
FROM
ALL_TAB_COLS T , All_Col_Comments C
WHERE
T.OWNER = C.OWNER
AND T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME
AND T.TABLE_NAME = UPPER('&Table_Name.')
AND T.Owner = UPPER('&Owner.') ;

PROMPT
PROMPT PRIMARY KEY for Table &Table_Name.

select COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.')
AND CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND CONS! TRAINT_T YPE = 'P'
AND Owner = UPPER('&Owner.')
)
ORDER BY POSITION
/

PROMPT
PROMPT INDEXES for Table &Table_Name.

BREAK ON INDEX_NAME ON UNIQUENESS SKIP 1

SELECT I.INDEX_NAME , C.COLUMN_NAME , I.UNIQUENESS

FROM ALL_IND_COLUMNS C , ALL_INDEXES I
WHERE C.INDEX_NAME = I.INDEX_NAME
AND C.TABLE_NAME = I.TABLE_NAME
AND I.TABLE_NAME = UPPER('&Table_Name.')
AND I.Owner = UPPER('&Owner.')
AND C.Table_Owner = UPPER('&Owner.')
AND NOT EXISTS ( SELECT 'X'
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_NAME = I.INDEX_NAME
AND Owner = UPPER('&Owner.')
)
ORDER BY INDEX_NAME , COLUMN_POSITION
/

CLEAR BREAKS

PROMPT
PROMPT FOREIGN KEYS for Table &Table_Name.

BREAK ON CONSTRAINT_NAME ON TABLE_NAME ON R_CONSTRAINT_NAME SKIP 1

COLUMN POSITION NOPRINT

SELECT UNIQUE A.CONSTRAINT_NAME,

C.COLUMN_NAME "FK Column" ,
B.TABLE_NAME || '.' || B.COLUMN_NAME "PK Column",
A.R_CONSTRAINT_NAME ,
C.POSITION
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B, ALL_CONS_COLUMNS C
WHERE A.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME
AND B.OWNER=UPPER('&OWNER')
AND A.CONSTRAINT_NAME=C.CONSTRAINT_NAME
AND A.OWNER=C.OWNER
AND A.OWNER = B.OWNER
AND A.TABLE_NAME=C.TABLE_NAME
AND B.POSITION=C.POSITION
AND A.TABLE_NAME LIKE UPPER('&TABLE_NAME')
ORDER BY A.CONSTRAINT_NAME, C.POSITION
/

COLUMN POSITION NOPRINT

CLEAR BREAKS

PROMPT
PROMPT CONSTRAINTS for Table &Table_Name.

SELECT CONSTRAINT_NAME , SEARCH_CONDITION

FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.')
AND CONSTRAINT_TYPE NOT IN ( 'P' , 'R');

PROMPT
PROMPT ROWCOUNT for Table &Table_Name.

SET FEEDBACK OFF

SET SERVEROUTPUT ON
DECLARE N NU MBER ;
V VARCHAR2(100) ;
BEGIN
V := 'SELECT COUNT(*) FROM ' || UPPER('&Table_Name.') ;
EXECUTE IMMEDIATE V INTO N ;
DBMS_OUTPUT.PUT_LINE (N);
END;
/

SET FEEDBACK ON

PROMPT
PROMPT Tables That REFER to Table &Table_Name.

BREAK ON TABLE_NAME ON CONSTRAINT_NAME skip 1

SELECT C.TABLE_NAME , C.CONSTRAINT_Name , CC.COLUMN_NAME "FK

Column"
FROM ALL_CONSTRAINTS C
, All_Cons_colUMNs CC
WHERE C.Constraint_Name = CC.Constraint_Name
AND R_CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND CONSTRAINT_TYPE = 'P'
AND Owner = UPPER('&Owner.')
)
AND C.Owner = UPPER('&Owner.')
/

CLEAR BREAKS

PROMPT
PROMPT PARTITIONED COLUMNS for Table &Table_Name.

SELECT COLUMN_NAME , COLUMN_POSITION

FROM All_Part_Key_Columns
WHERE NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.') ;

PROMPT
PROMPT PARTITIONS for Table &Table_Name.

SELECT PARTITION_NAME , NUM_ROWS

FROM All_Tab_Partitions
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Table_Owner = UPPER('&Owner.') ;

PROMPT
PROMPT TRIGGERS for Table &Table_Name.

SELECT Trigger_Name
FROM All_Triggers
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.') ;

PROMPT
PROMPT DEPENDANTS for Table &Table_Name.

BREAK ON TYPE SKIP 1

SELECT TYPE , NAME

FROM ALL_DEPENDENCIES
WHERE REFERENCED_NAME = UPPER('&Table_Name.')
ORDER BY TYPE ;

CLEAR BREAKS

SET TERMOUT OFF

SET AUTOTRACE ON
SET TIMING ON
SET TERMOUT ON

Tip of the Week

Tip for Week of August 9, 2004

PL/SQL String Tokenizer

This tip comes from Eli Leiba, an Applications DBA at Israel Electric Co., in Haifa, Israel.

Note: This tip was written for use with Oracle9i.

String tokenizing is an important string parsing function in both C and Java programming languages.

Here I show my PL/SQL implementation for the tokenizer process.

-- The pl/sql Oracle version for the Tokenizer procedure

Create or Replace
procedure tokenizer ( iStart IN NUMBER,
sPattern in VARCHAR2,
sBuffer in VARCHAR2,
sResult OUT VARCHAR2,
iNextPos OUT NUMBER)
AS
nPos1 number;
nPos2 number;
BEGIN
nPos1 := Instr (sBuffer ,sPattern ,iStart);
IF nPos1 = 0 then
sResult := NULL ;
ELSE
nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1);
IF nPos2 = 0 then
sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1)));
iNextPos := nPos2;
else
sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1);
iNextPos := nPos2;
END IF;
END IF;
END tokenizer ;
/

-- a Procedure to Test the Tokenizer

Create or Replace procedure sp_test_tokenizer
as
sepr varchar2(1);
sbuf varchar2(200);
sres varchar2(200);
pos number;
istart number;
begin

sbuf := '@0@11@222@3333@44444@555555@6666666@77777777@888888888';
sepr := '@';
istart := 1;
tokenizer (istart ,sepr,sbuf,sres,pos);
if (pos <> 0) then
dbms_output.put_line (sres);
end if;
while (pos <> 0)
loop
istart := pos;
tokenizer (istart ,sepr,sbuf,sres,pos );
dbms_output.put_line (sres);
end loop;
END sp_test_tokenizer;
/

-- Test the Procedure From sqlPlus

set serveroutput on
exec sp_test_tokenizer;

Tip of the Week

Tip for Week of July 26, 2004

Track Updates, Changes, and Deletions

This tip comes from Sanjay Ray, an Oracle Applications technical consultant in London, UK.
Note: This tip was written for use with Oracle8i. This code tip demonstrates a technique to track changes occurring in the unde
application, or runs a process,and the audit table will record the full details of how the tables were affected.
The information will include:
1) Table name and Unique ID(s) and values to identify the record
2) The event (INSERT, UPDATE, DELETE)
3) In case of UPDATE, the list of column names whose values have changed along with the old and new values
4) In case of INSERT or DELETE, column names and values from the record Inserted or Deleted
5) A Sequence number indicating the order in which the events occurred.
This procedure may be helpful in reverse engineering of code, or to get a better understanding of how an pplication process w
The process involves the following steps:
1) Creation of an audit table and a sequence number generator to supply the ID of the audit table
2) Generation of creation scripts for triggers on the tables likely to be modified by the Application Process (Also see Note #4 at
3) Creation of Triggers by running the spool file produced from Step 2
4) Run the Application Process or complete a transaction
5) Query the audit table to view the changes at the database level
Script:
--------
(This script assumes that TEST_TABLE1 and TEST_TABLE2 have already been created)

--Create the Sequence and the audit table

drop sequence xxtest_log_s1;
drop table xxtest_log;

create sequence xxtest_log_s1 start with 1 increment by 1 nocache;

create table xxtest_log
(seq_no number primary key,
table_name varchar2(255),
key_val varchar2(2000),
column_val long,
event varchar2(10),
creation_date date,
created_by varchar2(255),
sessionnum number
);

--Trigger Code Gen! eration

set long 2000set l inesize 2000
set pagesize 0
set trimspool on
spo xxspool.sql
set serveroutput on size 1000000
declare
type t_v2 is table of varchar2(255) index by binary_integer;
vt t_v2;
v_keys t_v2;
k_cols_disp CONSTANT number := 4;
kq CONSTANT varchar2(1):='''';
k_dm CONSTANT varchar2(1) := '*';
k_prefix CONSTANT varchar2(5) := 'XX_';
k_suffix CONSTANT varchar2(5) := '_T';
v_comm varchar2(2);
procedure pr(p_msg in varchar2) is
begin
dbms_output.put_line(p_msg);
end;
begin
--Populate the following section with the tables names
--likely to be impacted by the Application Process
vt(vt.count+1):='TEST_TABLE1';
vt(vt.count+1):='TEST_TABLE2';

--Alternatively generate the list dynamically based on a criteria.

/*
for c2 in (select a.object_name
from all_objects a
where a.object_type = 'TABLE'
and owner = ''
)
loop
vt(vt.count+1):=c2.object_name; end loop;
*/

for i in 1..vt.count
loop
v_keys.delete;

--Check for Unique Index in the Data Dictionary Index tables

--and if found get the index column names
select b.column_name
bulk collect into v_keys
from all_indexes a, all_ind_columns b
where a.owner=b.index_owner
and a.index_name=b.index_name
and a.uniqueness = 'UNIQUE'
and a.table_name = vt(i)
order by b.index_name, b.column_position;

--If no Unique Index exists, use the first column in the table
if v_keys.count = 0 then
select column_name into v_keys(1)
from all_tab_columns
where table_name=vt(i)
and column_id=1;
end if;

pr('create or replace trigger '||k_prefix||vt(i)||k_suffix);

pr('before insert or update or delete');
pr('on '||vt(i));
pr('for each row');
pr('declare');
pr('v_table varchar2(255)! :='||kq| |vt(i)||kq||';');
pr('v_event varchar2(1);');
pr('v_keyval varchar2(2000);');
pr('v_colval long:=null;');
pr('begin');

pr(' if INSERTING then');

pr(' v_event:=' ||kq||'I'||kq||';');
pr(' elsif UPDATING then');
pr(' v_event:=' ||kq||'U'||kq||';');
pr(' else ');
pr(' v_event:=' ||kq||'D'||kq||';');
pr(' end if; ');

--For INSERT and UPDATE, IDs will be the :new values

--For DELETE, use the :old values
pr(' if INSERTING or UPDATING then');
for j in 1..v_keys.count
loop
pr(' v_keyval:='||'v_keyval||'||kq||v_keys(j)||'='||kq||'||:new.'||v_keys(j)||
end loop;
pr(' else ');
for k in 1..v_keys.count
loop
pr(' v_keyval:='||'v_keyval||'||kq||v_keys(k)||'='||kq||'||:old.'||v_keys(k)||
end loop;
pr(' end if; ');

--For UPDATE, compare! each column and log details if any column values
--have changed. Compare only scallar values. Comment out other data types.
pr('if v_event in ('||kq||'U'||kq||') then');
pr('null;');
for c1 in (select column_name, data_type from all_tab_columns
where table_name = vt(i)
order by column_id )
loop
if c1.data_type in ('NUMBER', 'DATE', 'LONG', 'CHAR', 'VARCHAR2')
then
v_comm:=null;
else
v_comm:='--';
end if;
pr(v_comm||'if :old.'||c1.column_name||' is null and :new.'||c1.column_name||'
pr(v_comm||'or :new.'||c1.column_name||' is null and :old.'||c1.column_name||'
pr(v_comm||'or :old.'||c1.column_name||' <> :new.'||c1.column_name||' ');
pr(v_comm||'then');
pr(v_comm||'v_colval:=v_colval||'||kq||c1.column_name||'('||kq||'||:old.'||c1.
kq||';');!

pr(v_co! mm||'end if;');

end loop;
pr('end if;');

--For INSERT, log the :new values of the first k_cols_disp columns
pr('if v_event in ('||kq||'I'||kq||') then');
pr('null;');
for c1 in (select column_name, data_type from all_tab_columns
where table_name = vt(i)
and column_id <= k_cols_disp
order by column_id )
loop
if c1.data_type in ('NUMBER', 'DATE', 'LONG', 'CHAR', 'VARCHAR2')
then
v_comm:=null;
else
v_comm:='--';
end if;
pr(v_comm||'v_colval:=v_colval||'||kq||c1.column_name||'='||kq||'||:new.'||c1.
end loop;
pr('end if;');

--Insert into the Audit table

pr('insert into xxtest_log (');
pr('seq_no, table_name, key_val, column_val, event, creation_date, created_by,
pr(')(select xxtest_log_s1.nextval, v_table, v_keyval, v_colval, v_event, sysd
SESSIONID'||kq||') from dual);');

pr('end;');
pr('/');
pr('sho err;');
end loop;
end;
/
spo off
Testing:
--------------

create table TEST_TABLE1 (

f1 number PRIMARY KEY,
f2 clob,
f3 varchar2(10),
f4 varchar2(10),
f5 varchar2(10),
CONSTRAINT TEST_TABLE_C1 UNIQUE ( f3, f5 )
);

create table TEST_TABLE2(

g1 number,
g2 date,
g3 number
);
Generate the code for the triggers by running the script. Then run the spool file xxspool.sql to create the triggers. This Step cre

insert into test_table1 values (1, null, 'AB3', 'AB4', 'AB5');

insert into test_table1 values (2, null, 'CD3', 'CD4', 'CD5');
update test_table1 set f4='XY4' where f1=2;
update test_table1 set f4='XY4' where f1=2;
update test_table1 set f4='XY4', f5='XY5' where f1=2;
insert into test_table2 values (1, sysdate, 2);
inse! rt into test_table2 values (2, sysdate+1, 21);
update test_table2 set g2=sysdate+2 where g1=1;
update test_table2 set g2=sysdate where g1=1;
update test_table2 set g2=sysdate+1, g3=33 where g1=2;
delete from test_table1;
delete from test_table2;

--Query the Audit table

select seq_no, event, table_name, key_val, column_val from xxtest_log order by

1 I TEST_TABLE1 F1=1F3=AB3F5=AB5* F1=1F3=AB3F4=AB4*

2 I TEST_TABLE1 F1=2*F3=CD3*F5=CD5* F1=2*F3=CD3*F4=CD4*
3 U TEST_TABLE1 F1=2*F3=CD3*F5=CD5* F4(CD4,XY4)*
4 U TEST_TABLE1 F1=2*F3=CD3*F5=CD5*
5 U TEST_TABLE1 F1=2*F3=CD3*F5=XY5* F5(CD5,XY5)*
6 I TEST_TABLE2 G1=1* G1=1*G2=13-JUN-04*G3=2*
7 I TEST_TABLE2 G1=2* G1=2*G2=14-JUN-04*G3=21*
8 U TEST_TABLE2 G1=1* G2(13-JUN-04,15-JUN-04)*
9 U TEST_TABLE2 G1=1* G2(15-JUN-04,13-JUN-04)*
10 U TEST_TABLE2 G1=2* G2(14-JUN-04,14-JUN-04)*G3(21,33)*
11 D TEST_TABLE1 F1=2*F3=CD3*F5=XY5*
12 D TEST_TABLE1 F1=1*F3=AB3*F5=AB5*!
13 D TEST_TABLE2 G1=2*
14 D TEST_TABLE2 G1=1*
A) TEST_TABLE1.F2 has been excluded altogether as it has a datatype of CLOB
B) For Seq#=4, an update event has been recorded. But column_val is null as no column has actually changed values.
C) For Insert and Delete events, values for column F5 has been excluded as k_cols_disp = 4 in the script. To include F5, incre
D) For TEST_TABLE1, the columns F1, F3 and F5 form part of a Unique Index. So these columns have been included in the k
Note:
1) Only simple scallar datatypes are supported. For example a column with a datatype (say) CLOB will be excluded as they ca
2) Unique ID(s) is determined by the columns that are! part of a Unique Index on th! e table. If no such Unique index exists, the
type). The Unique ID names and their values will be held in the key_val column.
3) For Update events, all column values that have changed will be displayed. However in the Insert or Delete mode, only the fi
high value which must be at least equal the total number of columns in the table. To not print any value at all, set this value to
4) You will need to know in advance the table names that are likely to be affected by the application, and incorporate them into
vt(vt.count+1):='TABLE1';
vt(vt.count+1):='TABLE2';
etc.

Or you can generate the table name! s at runtime based on some criteria, e.g. for a particular schema.
5) The script does not check the ownership of the tables. So unique table name is assumed in the code. (Thus if you are dealin
6) Trigger name is generated by prefixing the table name with 'XX_' and adding '_T' at the end. Thus the trigger name for the t
necessary, change the k_prefix and k_suffix values in the master script.
7) The delimiter value is determined by k_dm variable which is set to an asterix '*' in the master script.
8) Format of a date value will be determined by database settings, which quite often exclude the time component. However co
to be reported to have been changed during an update, the displayed values (in column_val) bein! g the sa me (Seq#=10)
9) Code assumes you have the appropriate privileges to create the necessary objects. Sequence, Audit table and Triggers are
10) Ensure no trigger is created on the audit table xx_test_log!

Tip of the Week

Tip for Week of July 19, 2004
Analyze The Analyzed

This tip comes from Jayant Kulkarni, Senior Oracle DBA/Tech Lead, at Rapidigm, Inc., in Houston, TX.

NOTE: The following tip applies to Oracle9i Database. Oracle Database 10g provides new GUI and
command line analysis and manageability tools, including the SQL Access Advisor and SQL Tuning advisor,
that Oracle Database 10g DBAs should investigate as primary tools for managing optimizer stats analysis
and tuning operations.

See http://otn.oracle.com/tech/manageability/index.html for more information on Oracle Database 10g

manageability and tuning.

Analyze The Analyzed: Are all my tables, indexes and their sub-objects analyzed?

One of the key pieces of information needed in performance tuning under a Cost Based Optimizer (CBO) is
to identify whether stats are run on all the tables, indexes, and their sub-objects (partitions and sub-
partitions).

If stats are not being run, the very first step is to run stats on the objects in question and provide the
optimizer all the information that is needed to choose and execute the most optimal execution plan.

However, getting this information in one single shot is pretty difficult and often a DBA has to query
DBA_TABLES, DBA_TAB_PARTITIONS, DBA_TAB_SUBPARTITIONS, DBA_INDEXES,
DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS dictionary views to extract this information.

The following query attempts to consolidate this information in a report format and add warnings at
appropriate places so that the DBA can know in a glance what objects/sub-objects have stats missing on
them.

accept v_schema PROMPT "Enter the schema name: "

colum! n table_owner heading "Tab|Owner" format a12

break on report

compute sum of tab_parts on report

compute sum of non_analyzed_tab_part on report
compute sum of ind_count on report
compute sum of ind_parts on report
compute sum of non_analyzed_ind_part on report

SELECT tab_stat.table_owner, tab_stat.table_name,

tab_stat.nos_partitions "TAB_PARTS",
tab_stat.non_analyzed "NON_ANALYZED_TAB_PART",
tab_stat! .warning "TAB_WARN", ind_stat.ind! _count,
ind_stat.nos_partitions "IND_PARTS",
ind_stat.non_analyzed "NON_ANALYZED_IND_PART",
ind_stat.warning "IND_WARN"
FROM (SELECT table_owner, table_name,
MAX (table_partitions) nos_partitions,
MAX (non_analyzed_table_partitions) non_analyzed,
(CASE
WHEN MAX (non_analyzed_table_partitions) > 0
THEN '**Warning**'
ELSE NULL
END
) "WARNING"
FROM (SELECT owner table_owner, table_name,
0 table_partitions,
COUNT
(DECODE (num_rows, NULL, 1, NULL)
) non_analyzed_table_partitions
FROM dba_tables
WHERE owner LIKE UPPER ('&v_schema')
! GROUP BY owner, table_name
UNION
SELECT table_owner, table_name,
COUNT (*) table_partitions,
COUNT
(DECODE (num_rows, NULL, 1, NULL)
) non_analyzed_table_partitions
FROM dba_tab_partitions
WHERE table_owner LIKE UPPER ('&v_schema')
GROUP BY table_owner, table_name
UNION
SELECT table_owner, table_name,
COUNT (*) table_partitions,
COUNT
(DECODE (num_rows, NULL, 1, NULL)
) non_analyzed_table_partitions
FROM dba_tab_subpartitions
WHERE table_owner LIKE UPPER ('&v_schema')
! GROUP BY table_owner, ! table_na me)
GROUP BY table_owner, table_name
ORDER BY table_owner, table_name) tab_stat,
(SELECT table_owner, table_name, MAX (index_count) ind_count,
MAX (ind_partitions) nos_partitions,
MAX (non_analyzed_ind_partitions) non_analyzed,
(CASE
WHEN MAX (non_analyzed_ind_partitions) > 0
THEN '**Warning**'
ELSE NULL
END
) "WARNING"
FROM (SELECT table_owner, table_name,
COUNT (index_name) index_count, 0 ind_partitions,
COUNT
(DECODE (num_rows, NULL, 1, NULL)
) non_analyzed_ind_partitions
FROM dba_indexes
WHERE owner LIKE UPPER ('&v_schema')
! GROUP BY table_owner, table_name
UNION
SELECT i.table_owner, i.table_name,
COUNT (DISTINCT (ip.index_name)) index_count,
COUNT (ip.partition_name) ind_partitions,
COUNT
(DECODE (ip.num_rows, NULL, 1, NULL)
) non_analyzed_ind_partitions
FROM dba_indexes i, dba_ind_partitions ip
WHERE table_owner LIKE UPPER ('&v_schema')
AND i.owner = ip.index_owner
AND i.index_name = ip.index_name
GROUP BY table_owner, table_name
UNION
SELECT i.table_owner, i.table_name,
COUNT (DISTINCT (isp.index_name)) index_count,
COUNT (isp.subparti! tion_name) ind_partitions,!
COUNT
(DECODE (isp.num_rows, NULL, 1, NULL)
) non_analyzed_ind_partitions
FROM dba_indexes i,
dba_ind_partitions ip,
dba_ind_subpartitions isp
WHERE table_owner LIKE UPPER ('&v_schema')
AND i.owner = ip.index_owner
AND i.index_name = ip.index_name
AND i.owner = isp.index_owner
AND i.index_name = isp.index_name
AND ip.partition_name = isp.partition_name
GROUP BY table_owner, table_name)
GROUP BY table_owner, table_name
ORDER BY table_owner, table_name) ind_stat
WHERE tab_stat.table_owner = ind_stat.table_owner(+)
AND tab_stat.table_name = ind_stat.table_name(+)
! ORDER BY tab_stat.table_owner, tab_stat.table_name;

The output of the query looks like this:

| | Tab| Tab| | | | Ind|

Tab |Tab | Parts| Parts|Tab | Ind| Ind| Parts|Ind
Owner |Name | (Nos)|Non-analyzed|Warning | Count| Parts|Non-
analyzed|Warning
____________|______________________________|______|____________|
___________|______|______|____________|___________
IRS |IA_AR_ACTIVITY | 0| 0| | 2| 0| 0|
IRS |IA_AR_ACTIVITY_A1 | 36| 0| | 6| 216| 36|**Warning**
IRS |IA_AR_BALANCE | 0| 0| | 1| 0| 0|
IRS |IA_AR_XACTS | 72| 0| | 3| 21! 6| 0|
IRS ! |IA_BA NKS | 0| 0| | 1| 0| 0|
IRS |IA_BUSN_CONTCTS | 0| 0| | 2| 0| 0|
IRS |IA_CUST_ACCTS | 8| 0| | 4| 32| 0|
IRS |IA_CUST_ACCT_KF | 76| 28|**Warning**| 5| 380| 140|**Warning**
IRS |IA_CUST_ACCT_KF_08JAN2004 | 72| 0| | 5| 360| 0|
IRS |IA_CUST_ACCT_KF_MV01 | 19| 7|**Warning**| 1| 0| 0|
IRS |IA_CUST_ACCT_KF_MV02 | 18| 7|**Warning**| 1| 0| 0|

...
...
...
... some more rows
IRS |OD_XRATES | 0| 0| | 1| 0| 0|
IRS |WM_AR_INVOICE_ACTIVITY | 36| 0| | 2| 72| ! 0|
IRS |WM_CAF_FLAG_MV01 | 0| 0| | 2| 0| 0|
IRS |WM_CUST_COD_PLN_MV01 | 0| 0| | 1| 0| 0|
IRS |WM_CUST_DREF_MV01 | 0| 0| | | | |
IRS |WM_CUST_RELS | 8| 0| | | | |
IRS |WM_CUST_STATUS_CODE | 0| 0| | 4| 0| 0|
IRS |WM_DELINQUENT_ACCOUNTS_MV01 | 0| 0| | | | |
IRS |WM_MAS_CUST_LOC | 0| 0| | 1| 0| 0|
| |------|------------| |------|------|------------|
sum | | 820| 42| | 272| 1658| 248|

149 row! s selected.

Tip of the Week

Tip for Week of July 12, 2004

Convert Alphabetical Phone Numbers to Numeric Phone Numbers

This tip comes from Galina Petrenko, Sr. IT Specialist, TPAS, in Voorhees, NJ.

We sometimes have to store phone numbers in a system in alphabetical format.

We can convert and format alphabetical phone numbers using the TRANSLATE function.

Example:
----------------

def Phone=1-800-VERIZON

select TRANSLATE(UPPER('&Phone')
,'ABCDEFGHIJKLMNOPQRSTUVWXYZ)(-'
,'22233344455566677778889999'
) Phone
from dual;

PHONE
-------------
1-800-8374966

So we can create a function, ConvertPhone#, that uses TRANSLATE:

create function ConvertPhone# (Inp_Phone VARCHAR2) RETURN NUMBER

IS
BEGIN
RETURN
to_number( TRANSLATE( UPPER('&PHONE')
,'ABCDEFGHIJKLMNOPQRSTUVWXYZ)(-','22233344455566677778889999'
))
;
END;
/

Now, note the size difference between the alphabetical and numeric phone numbers:

--
-- Check Size in a Database
-- get rid of formatting - could also be done via format masks
--
def PHONE="1(800)-CALLATT"

select
VSIZE('&PHONE') VSIZE_WORD,
VSIZE(
to_number( TRANSLATE( UPPER('&PHONE')
,'ABCDEFGHIJKLMNOPQRSTUVWXYZ)(-','222333444555666777! 78889999'
))
) VSIZE_NUMBER
from dual;

VSIZE_WORD VSIZE_NUMBER
---------- ------------
14 7

Now, if we create a function-based index on the alphabetical phone number column in our table, the
response will be faster since size of the data is smaller (almost half the size):

create INDEX IF_MyTABLE_ABCPHONE on

ABC_PHONE_TABLE(ConvertPhone#(ABC_PHONE));

You can use ConvertPhone# in later queries, such as:

select ......
from ......
where ConvertPhone#(ABC_PHONE)=ConvertPhone#('1-866-CALLABC')

Tip of the Week

Tip for Week of June 21, 2004

Find Active SQL Statements

This tip comes from Sudhindra Chillarige, Software Engineer, Global Computer Enterprises, Inc., in Reston,
Virginia.
Everyone has a favorite script to find the active SQL statements. Here is one of mine.

It builds the information using an anonymous PL/SQL block. It reads the information of active sessions from
gv$session_wait and gv$session. (They have been made generic to support RAC instances, too.)

And it pulls the information on the current SQL from gv$sqltext.

set serverout on size 999999

declare
begin
dbms_output.put_line(' ');
dbms_output.put_line('************* Start report for WAITING
sessions with current SQL ***************');
for x in (select vs.inst_id, vs.sid || ',' || vs.serial# sidser,
vs.sql_address, vs.sql_hash_value,
vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
from gv$session_wait vsw, gv$session vs
where vsw.sid = vs.sid
and vsw.inst_id = vs.inst_id
and vs.type <> 'BACKGROUND'
and vsw.event NOT IN ('rdbms ipc message'
,'smon timer'
,'pmon timer'
,'SQL*Net message from client'
,'lock manager wait for remote message'
,'ges remote message'
,'gcs remote message'
,'gcs for action'
,'client message'
,'pipe get'
,'Null event'
,'PX Idle Wait'
,'single-task message'
,'PX Deq: Execution Msg'
,'KXFQ: kxfqdeq - normal deqeue'
,'listen endpoint status'
,'slave wait'
,'wakeup time manager'))
loop
begin
dbms_output.put_line('Event WaitState InstID SidSerial LastCallEt
SecondsInWait');
dbms_output.put_line('*************************
******************** ****** *********** ********** *************');
dbms_output.put_line(rpad(x.event,25) ||' '|| rpad(x.state,20) ||'
'|| lpad(x.inst_id,6) ||' '|| lpad(x.sidser,11) ||'
'|| lpad(x.last_call_et,10) ||' '|| lpad(x.seconds_in_wait,13));
dbms_output.put_line(' SQLText ');

dbms_output.put_line('**************************************************
**************');
for y in (select sql_text
from gv$sqltext
where address = x.sql_address
and hash_value = x.sql_hash_value
and inst_id = x.inst_id
order by piece)
loop
dbms_output.put_line(y.sql_text);
end loop;
end;
end loop;
dbms_output.put_line('************** End re! port for sessions
waiting with current SQL ****************');
dbms_output.put_line(' ');
end;
/

Tip of the Week

Tip for Week of June 14, 2004

Auditing and Restricting User Connections

This tip comes from Ajwat Ul-Hasan, Database Administrator/System Analyst, The
Carphonewarehouse in London, United Kingdom.

In Oracle9i, this script can restrict users logging into the database. In one table you provide
information about which users are allowed to log into the database. You can modify this script to allow
or block a user with respect to application, ip address, username, osuser, terminal, etc. It is a very
easy and efficient way of implementing security in database.

create table valid_users (

valid_ora VARCHAR2(30),
valid_os varchar2(30)
);
and another table
CREATE TABLE USER_AUDIT
(
SID NUMBER NULL,
SERIAL# NUMBER NULL,
TIMESTAMP DATE NULL,
USERNAME VARCHAR2(30) NULL,
OSUSERID VARCHAR2(30) NULL,
MACHINENAME VARCHAR2(64) NULL,
PROGRAM VARCHAR2(48) NULL
)

And then create this trigger:

CREATE OR REPLACE TRIGGER test_audit

AFTER LOGON ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
ora_username VARCHAR2(30) DEFAULT NULL;
os_username VARCHAR2(30);
v_sid NUMBER;
v_serial NUMBER;
v_program VARCHAR2(48);
v_numuser NUMBER;
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session
WHERE audsid = userenv('sessionid');
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename,
v_program;
CLOSE c1;
SELECT count(*)
INTO v_numuser
FROM valid_users
WHERE valid_ora = user;

IF v_numuser > 0 then

INSERT INTO user_audit
VALUES (v_sid, v_serial, sysdate,
user, osuserid, machinename,v_program);
ELSE
INSERT INTO user_audit
VALUES (v_sid, v_serial, ! sysdate,
user, osuserid, machinename,'IN ELSE');
raise_application_error(-20001,'You are not allowed to connect
to the database');
END IF;
END;
/

In the valid_user table you have to insert information about valid users, otherwise no user can log in to
the database. You can generate information about user connection by using information in user_audit
table.

Tip of the Week

Tip for Week of June 21, 2004

Find Active SQL Statements

This tip comes from Sudhindra Chillarige, Software Engineer, Global Computer Enterprises, Inc., in Reston,
Virginia.

Everyone has a favorite script to find the active SQL statements. Here is one of mine.

set serverout on size 999999

Tip of the Week

Tip for Week of June 7, 2004

Tablespace Growth Report

This tip comes from Vishnu Bhavaraju, Oracle Lead DBA, Tata Consultancy Services, in Cincinnati, Ohio.

In Oracle8i, DBAs want to know how fast the tablespaces in their databases are growing so that they can
plan for additional disk space. At the time of database/tablespace creation, DBAs use their best estimates to
allocate optimal size to the tablespaces, but often need to resize the tablespaces frequently. Keeping
historical information also helps to predict the growth more accurately. The following set of scripts does that.

Create the table to store TS stats:

create table TSReport (Instance varchar2(16), tablespace_name

varchar2(25), total_size number, total_free number, Pct_Free number,
max_free
number, chunks_free number, report_date date );

Set up a job to gather the TS stats daily:

CronJob:
00 07 * * * /opt/oracle/tools/scripts/TSStats pfin >>
/tmp/TSStats_pfin.log

Shell script TSStats:

#!/bin/ksh

ExitHere()
{
if [ ! $STATUS = "0" ] ; then
cat ${Output} | mailx -s "TS Statistics Job: $ORACLE_SID :
FAILED" $MAIL_TO
else
cat ${Output} | mailx -s "TS Statistics Job: $ORACLE_SID :
SUCCESS" $MAIL_TO
fi
}

ProgName=`basename ${0}`
LogFile="${ProgName}"
SqlFile="/technology/opt/oracle/tools/sql/${ProgName}"
MAIL_TO="vishnu.murthy@ge.com"
Output="/technology/tmp/${LogFile}.${LOGNAME}"

echo "Output from ${0}@`hostname` : " > ${Output}

if [ ! -z ${1} ] ; then

ORACLE_SID=${1}
export ORACLE_SID

ORACLE_HOME=`grep -i ^$ORACLE_SID /var/opt/oracle/oratab| cut

-d ':' -f2 `
export ORACLE_HOME

if [ ! -z $ORACLE_HOME ] ; th en
PATH=$PATH:$ORACLE_HOME/bin
export PATH

if [ -e ${SqlFile}.sql ] ; then

sqlplus -S internal @"${SqlFile}" >> "${Output}"

STATUS=`grep -i error ${Output} | wc -l`

ExitHere

else
echo "${SqlFile}.sql doesnot exist" >> ${Output}
STATUS=1
ExitHere
fi

else
echo "No entry found in oratab for ${ORACLE_SID} " >>
${Output}
STATUS=1
ExitHere
fi

else
echo "Oracle sid is not supplied..." >> ${Output}
echo "usage: ${ProgName} " >> ${Output}
STATUS=1
ExitHere
fi

SQL Script TSStats.SQL:

set serveroutput on;

BEGIN
DELETE FROM DBMONITOR.TSREPORT
WHERE REPORT_DATE < SYSDATE - 90;

INSERT INTO DBMONITOR.TSREPORT

(INSTANCE ,
TABLESPACE_NAME ,
TOTAL_SIZE ,
TOTAL_FREE ,
PCT_FREE ,
MAX_FREE ,
CHUNKS_FREE ,
REPORT_DATE
)
SELECT UPPER(B.INSTANCE_NAME) INSTANCE,
A.TABLESPACE_NAME,SUM(A.TOTS) TOT_SIZE,
SUM(A.SUMB) TOT_FREE,
SUM(A.SUMB)*100/SUM(A.TOTS) PCT_FREE,
SUM(A.LARGEST) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE,
TRUNC(SYSDATE)
FROM (
SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
MAX(BYTES) LARGEST,COUNT(*) CHUNKS
FROM SYS.DBA_FREE_SPACE A
GROUP BY TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
WHERE A.TABLESPACE_NAME NOT LIKE 'RBS%'
GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME;

COMMIT;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' ROWS ARE INSERTED INTO
DBMONITOR.TSREPORT TABLE');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FATAL ERROR:Failed to Collect TableSpace
Stats');
RAISE;
END;
/
exit;

Generate the tablespace growth report for a specific

period:

DBGrowthReport.sql generates an HTML report for database

growth statistics. It uses the data captured and stored in dbmonitor.tsreport
table by a daily cron job. The report is created in the same directory from
which it is executed.

It prompts for the report start date and report end date
which need to be entered in 'DD-MON-YY' format only; otherwise, the script will
terminate immediately. If the user chooses not to enter the dates, the script
will use the minimum and maximum report_date from dbmonitor.tsreport as start
and end dates.

Author : Vishnu
History: 16-Sep-03 Vishnu Created

spool log
undef start_date

col instance_name new_value instance_name

col today new_value today
Select the instance name and today's
date:

select upper(instance_name) instance_name from v$instance;

select sysdate today from dual;

Enter the report start date and end date in DD-MON-YY

format:

col Date_Start new_value Date_Start

col Date_End new_value Date_End

Exit if date values entered are invalid whenever

sqlerror exit.

Prompt the user to enter the start and end dates for the
period of report to be generated.
If the user doesn't enter any dates, choose
default values as least and highest dates from the tsreport table.

select
decode('&&start_date',null,min(report_date),min(to_date(upper('&&start_d
ate'),'DD-MON-YY')))
Date_Start from dbmonitor.tsreport;
select
decode('&&end_date',null,max(report_date),max(to_date(upper('&&end_date'
),'DD-MON-YY')))
Date_End from dbmonitor.tsreport;

ttitle left 'START - DATABASE GROWTH REPORT OF &instance_name'

skip left '========================================' skip 4 -
left 'This Report is generated on &today' skip 2 -
left 'Period for which the report is generated -> &Date_Start
to &Date_End' skip 4

btitle left 'END - DATABASE GROWTH REPORT OF &instance_name'

skip left '========================================'

btitle on
ttitle on
set linesize 200

spool off

whenever sqlerror continue

btitle on
ttitle on

set numf 999,999,999,999

set linesize 200

set pages 9999
set feedback off
set verify off

col instance for a16;

col tablespace_name for a30;
col cur_total_size head cur_total_size
col cur_free_bytes head cur_free_bytes
col prev_day_total_size head prev_day_total_size
col prev_day_free_bytes head prev_day_free_bytes
col growth_size head TS_Growth_Size
col report_date for a12
col percent_growth head percent_growth for 099.99

break on tablespace_name on report skip page on instance skip

compute sum LABEL Total_TS_Growth of growth_size on

tablespace_name;
compute sum LABEL Database_Growth of growth_size on report;

spool '&instance_name'_Growth_Report.htm
set markup html on

select tg2.instance, tg2.tablespace_name,

tg1.total_size cur_total_size,
tg1.total_free cur_free_bytes,
tg2.total_size prev_day_total_size,
tg2.total_free prev_day_free_bytes,
((tg1.total_size - tg1.total_free) - (tg2.total_size -
tg2.total_free)) growth_size,
(tg1.report_date - tg2.report_date ) growth_period,
rep_dates.report_date report_date,
round(((tg1.total_size - tg1.total_free) - (tg2.total_size -
tg2.total_free)) * 100 / tg2.total_size,2) percent_growth
from dbmonitor.tsreport tg1,
dbmonitor.tsreport tg2,
(select ts1.report_date report_date,max(ts2.report_date)
prev_report_date
from dbmonitor.tsreport ts1,
dbmonitor.tsreport ts2
where ts1.instance = ts2.instance(+)
and ts1.tablespace_name = ts2.tablespace_name(+)
and ts1.report_date > ts2.report_date
group by ts1.report_date
order by ts1.report_date) rep_dates
where tg1.tablespace_name = tg2.tablespace_name
and tg1.report_date = rep_dates.report_date
and tg2.report_date = rep_dates.prev_report_date
--and tg1.tablespace_name = 'ARX'
and ((tg1.total_size - tg1.total_free) - (tg2.total_size -
tg2.total_free)) <> 0
and tg1.report_date >= '&Date_Start'
and tg1.report_date <= '&Date_End'
order by tablespace_name,report_date;

spool off

clear breaks;
clear computes;!
btitle off
ttitle off

undef start_date
undef end_date

set markup html off

set feedback on
set verify on

Tip of the Week

Tip for Week of May 31, 2004

Get Unique from Duplicates

This tip comes from Yakov Shlafman, DBA, Open Distributed Solutions, Inc. in Jamison,
Pennsylvania.

This script shows how to extract unique "valid" accounts from a group of duplicates using Oracle's
analytical function ROW_NUMBER().

In the Account table Account_id is the primary key.

According to the specification, the Customer_Id column should have only one Account_id.

In cases where a single customer has more than one account, we need to flag the "valid" account so
the migration process knows which account to load into the new system. The "valid" account is the
"oldest" account in a particular group and can be determined by getting the MIN(Account_id) of that
group.

There is one additional condition that complicates the task — in each group of duplicates the "oldest"
account with an open account bill plan gets priority over the "oldest" account without a plan.

Here is a short specification:

Case 1— if customer has 2 or more rows with flag_ind equals 1

select the row with min(accountid)

Case 2— if customer has 2 or more rows with flag_ind equals 0

select the row with min(accountid)
Case 3— if customer has rows with flag_ind equal to 1 and 0
select the row with flag_ind equal to 1 that has min(accountid)

After intermediate steps of identifying duplicated accounts and among them the accounts with account
bill plan, we have the table— table account_duplicates_flagged. We are going to use this table with a
sample population.

Step 1:

create table account_duplicates_flagged

( ACCOUNTID number(9) not null
,CUSTOMERENTITYID number(9) not null
,FLAG_IND number(1) not null);

Step 2:

Populate table account_duplicates_flagged with preprocessed data where flag_ind equals 1 if a

customer has an open account bill plan and flag_ind equals 0 if a customer does not have an open
account bill plan.

1. Customer has only one row.

insert into account_duplicates_flagged values (5, 1, 1);

insert into account_duplicates_flagged values (3, 8, 0);
2. Customer has multiple accounts and has an account bill plan.
insert into account_duplicates_flagged values (558, 2, 1);
insert into account_duplicates_flagged values (52, 2, 1);
insert into account_duplicates_flagged values (551, 2, 1);
insert into account_duplicates_flagged values (128, 2, 1);

3. Customer has multiple accounts and does not have an account bill plan.

insert into account_duplicates_flagged values (77, 3, 0);

insert into account_duplicates_flagged values (771, 3, 0);
insert into account_duplicates_flagged values (7, 3, 0);
insert into account_duplicates_flagged values (444, 3, 0);
4. Customer has multiple accounts and some of customers do have an account bill plan but some do
not.

insert into account_duplicates_flagged values (6, 4, 0);

insert into account_duplicates_flagged values (9, 4, 0);
insert into account_duplicates_flagged values (10, 4, 1);
insert into account_duplicates_flagged values (109, 4, 1);
insert into account_duplicates_flagged values (11, 4, 0);
insert into account_duplicates_flagged values (12, 4, 1);
insert into account_duplicates_flagged values (316, 5, 1);
insert into account_duplicates_flagged values (315, 5, 0);

commit;

Step 3: Populate table Accounts_To_Migrate_Driver with unique data &151; customers with only one
selected Account_id.

drop table Accounts_To_Migrate_Driver;

create table Accounts_To_Migrate_Driver
as
select--+PARALLEL
ACCOUNTID,
CUSTOMERENTITYID
FROM(
select
ACCOUNTID,
CUSTOMERENTITYID,
! ROW_NUMBER() OVER ( PARTITION BY CUSTOMERENTITYID
ORDER BY FLAG_IND desc, ACCOUNTID asc) Row#
from account_duplicates_flagged
)
WHERE ROW#=1
;

Checking the results:

select *
from Accounts_To_Migrate_Driver
order by CUSTOMERENTITYID;

ACCOUNTID CUSTOMERENTITYID
--------- ----------------
5 1
52 2
7 3
10 4
316 5
3 8

6 rows selected.
Step 4: Populate table Accounts_To_Delete_Driver with all duplicates to delete.

create view Accounts_To_Delete_Driver

as
select--+PARALLEL
ACCOUNTID,
CUSTOMERENTITYID
FROM(
select
ACCOUNTID,
CUSTOMERENTITYID,
ROW_NUMBER() OVER ( PARTITION BY CUSTOMERENTITYID
ORDER BY FLAG_IND desc, ACCOUNTID asc) Row# from
account_duplicates_flagged
)
WHERE ROW#>1
;

Checking the results:

select *
from Accounts_To_Delete_Driver
order by CUSTOMERENTITYID, ACCOUNTID;

ACCOUNTID CUSTOMERENTITYID
--------- ----------------
128 2
551 2
558 2
77 3
444 3
771 3
6 4
9 4
11 4
12 4
109 4
315 5

12 rows selected.

You should be able to run this script on Oracle8i and later releases.

Tip of the Week

Tip for Week of May 24, 2004

How Many Seconds

This tip comes from Mir Mirhashimaali, Oracle Systems Manager, Rice University in Houston, Texas.

Here's a function to calculate the number of seconds elapsed in a given period of time.
create or replace function how_many_seconds (
p_startdate date,
p_enddate date,
p_starttime varchar2,
p_endtime varchar2
)
return number
is
--
mv_sysdate varchar(8) := to_char(sysdate, 'rrrrmmdd');
mn_return_value number;
mn_seconds_per_day number;
mn_total_days number;
mn_seconds_in_start_day number;
mn_seconds_in_end_day number;
--
begin
--
mn_seconds_per_day := to_char(to_date(mv_sysdate || p_endtime,
'rrrrmmddhh24mi'), 'sssss') -
to_char(to_date(mv_sysdate || p_starttime, 'rrrrmmddhh24mi'),
'sssss');
mn_total_days := (trunc(p_enddate) - trunc(p_startdate)) - 1;
mn_seconds_in_start_day := to_char(to_date(mv_sysdate ||
p_endtime, 'rrrrmmddhh24mi'), 'sssss') -
to_char(p_startdate, 'sssss');
mn_seconds_in_end_day := to_char(p_enddate, 'sssss') -
to_char(to_date(mv_sysdate || p_starttime, 'rrrrmmddhh24mi'),
'sssss');
--
if mn_total_days < 0 then
mn_total_days := 0;
mn_seconds_in_start_day := 0;
end if;
--
if mn_seconds_in_start_day < 0 then
mn_seconds_in_start_day := 0;
end if;
--
if mn_seconds_in_end_day < 0 then
mn_seconds_in_end_day := 0;
end if;
--
mn_return_value := mn_seconds_in_start_day + (mn_total_days *
mn_seconds_per_day) + mn_seconds_in_end_day;
--
return mn_return_value;
--
end;
/

select how_many_seconds(to_date('200404281900',
'rrrrmmddhh24mi'),
to_date('200404291000', 'rrrrmmddhh24mi'), '0900', '1500')
"Seconds"
from dual!
/
!
Seconds
----------
3600

Tip of the Week

Tip for Week of May 24, 2004

How Many Seconds

This tip comes from Mir Mirhashimaali, Oracle Systems Manager, Rice University in Houston, Texas.

select how_many_seconds(to_date('200404281900',
'rrrrmmddhh24mi'),
to_date('200404291000', 'rrrrmmddhh24mi'), '0900', '1500')
"Seconds"
from dual!
/
!
Seconds
----------
3600

Tip of the Week

Tip for Week of May 17, 2004

Comma, Pipe, or Tab Delimited Output

This tip comes from Galina Petrenko, Sr. IT Specialist, TPAS Systems Development in Voorhees, New
Jersey.

To generate comma or pipe delimited output for tools such as Excel, Access, and so on while spooling from
SQL*Plus, use settings from SQL*Plus such as:

SET COLSEP ,

SET COLSEP |

Example:

set pages 0 feed off

set colsep ,
select
object_name,
object_type,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;

Output:

ALL_ALL_TABLES ,SYNONYM ,VALID ,01-APR-2004

ALL_ARGUMENTS ,SYNONYM ,VALID ,01-APR-2004
ALL_ASSOCIATIONS ,SYNONYM ,VALID ,01-APR-2004
ALL_CATALOG ,SYNONYM ,VALID ,01-APR-2004
ALL_CLUSTERS ,SYNONYM ,VALID ,01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS ,SYNONYM ,VALID ,01-APR-2004
ALL_COLL_TYPES ,SYNONYM ,VALID ,01-APR-2004
ALL_COL_COMMENTS ,SYNONYM ,VALID ,01-APR-2004
ALL_COL_PRIVS ,SYNONYM ,VALID ,01-APR-2004

For pipe delimited output:

set colsep |

select
object_name,
object_type,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;

Output:

ALL_ALL_TABLES |SYNONYM |VALID |01-APR-2004

For TAB delimited output, you can use the following:

col TAB# new_value TAB NOPRINT

select chr(9) TAB# from dual;
set colsep "&TAB"

select
object_name,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;

Output:

ALL_ALL_TABLES VALID 01-APR-2004

ALL_ARGUMENTS VALID 01-APR-2004
ALL_ASSOCIATIONS VALID 01-APR-2004
ALL_CATALOG VALID 01-APR-2004
ALL_CLUSTERS VALID 01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS VALID 01-APR-2004
ALL_COLL_TYPES VALID 01-APR-2004
ALL_COL_COMMENTS VALID 01-APR-2004
ALL_COL_PRIVS VALID 01-APR-2004

Tip of the Week

Tip for Week of April 26, 2004

Top 10 Segments by V$SEGSTAT Statistic Name

This tip comes from Robert Ware, DBA, TEK Systems in St. Louis, Missouri.

The following lists the top 10 segments by V$SEGSTAT statistic name (since the instance was started). I
usually employ this method to help determine which segments are responsible for most of the Logical I/O or
Buffer Busy Waits on my system. Once I have this information, then I will search for SQL statements to tune
in either my statspack report or by querying V$SQL.

set feed off pagesize 42 linesize 132 veri off echo off pause off

col value form 999,999,999,999

col owner form a12

col object_name head 'Object Name'form a30

col object_type head 'Object Type'form a18

col statistic_name head 'Statistic Name' form a25

set head off feed off

select lpad(rownum,36)||'. '||statname

from (select distinct(statistic_name) statname

from v$segstat
order by statistic_name)

prompt

ACCEPT statnum prompt 'Which statistic do you wish to retrieve: '

col statname noprint new_value statname

select statname

from (select distinct statistic_name statname

from v$segstat

where statistic_name = CASE WHEN &statnum = 1 THEN 'ITL waits'

WHEN &statnum = 2 THEN 'buffer busy waits'

WHEN &statnum = 3 THEN 'db block changes'

WHEN &statnum = 4 THEN 'global cache cr blocks served'

WHEN &statnum = 5 THEN 'global cache current blocks served'

WHEN &statnum = 6 THEN 'logical reads'

WHEN &statnum = 7 THEN 'physical reads'

WHEN &statnum = 8 THEN 'physical reads direct'

WHEN &statnum = 9 THEN 'physical writes'

WHEN &statnum = 10 THEN 'physical writes direct'

WHEN &statnum = 11 THEN 'row lock waits'

! END )

set head on

ttitle "-- Top 10 Segments by &statname" skip 2

select owner, object_name, object_type, value, statistic_name

from (select DO.owner,

DO.object_name,

DO.object_type,

SS.value,
SS.statistic_name,

row_number () over (order by value desc) RN

from dba_objects DO, v$segstat SS

where DO.object_id = SS.obj#

and statistic_name = '&statname')

where RN <= 10

prompt

ttitle off

set feed on

Tip of the Week

Tip for Week of April 12, 2004

Generate SQL*Loader Control File for Any Table

This tip comes from Navroz Kapadia, DBA, Economics & Business Cluster in Toronto, Ontario, Canada.

This script will generate a SQL*Loader control file for any specified table, which can then be customized as
per individual needs. Ensure it is run by a user with SELECT_CATALOG_ROLE. Parameter 1 is the owner
of the table. Parameter 2 is the tablename.

set verify off

set heading off

set embedded on

set recsep off

set pages 500

set lines 80

set feedback off

define &&1 = ''

spool c:\your_path_name\&&2..ctl

select

'load data

infile ''' || 'C:\your_path_name\' || upper('&2') || '.CSV'''|| '

into table ' || upper('&1') || '.' || upper('&2') || '

replace

fields terminated by '',''' || '

trailing nullcols'

from all_tables where owner = upper('&1') and table_name = upper('&2')

select decode(a.column_id, '1', '(') || a.column_name ||

(case when a.column_id < max(b.column_id) then ',' else ')' END)

from all_tab_columns a, all_tab_columns b

where a.owner = upper('&1') and a.table_name = upper('&2') and

b.owner = upper('&1') and b.table_name = upper('&2')

group by a.column_name, a.column_id

order by a.column_id

spool off

set verify on

set heading on

set embedded off

set recsep wrapped

set pages 64

set lines 80

set feedback 6

undefine 1

undefine 2

Tip of the Week

Tip for Week of April 5, 2004

Find Out Where Your PL/SQL Session Is

This tip comes from Sudhindra Chillarige, DBA, Global Computer Enterprises in Reston, Virginia.

Suppose you run a relatively long PL/SQL procedure that processes several rows, and you want to track its
progress. The following procedure lets you see the total number of rows and the current row being
processed, using client_info in the V$SESSION view.

Log in as MYUSER and create the following sample procedure:

CREATE OR REPLACE PROCEDURE PROC_MYPROGRESS

-- declare variables here

L_CNT NUMBER DEFAULT 0;

L_TOTAL NUMBER DEFAULT 100000;

begin

-- get the total number of rows that I am processing

select count(*)

into L_TOTAL

from all_objects;

for x in (SELECT object_name, object_type from all_objects) loop

begin

L_CNT := L_CNT + 1;

DBMS_APPLICATION_INFO.SET_CLIENT_INFO ( 'PROCESSING row ' || L_CNT || '

out of ' || L_TOTAL );

end;

end loop;

DBMS_APPLICATION_INFO.SET_CLIENT_INFO ( 'Done');
end PROC_MYPROGRESS;

Execute the above procedure in a SQL session and query the V$SESSION table with the following SQL.

select username, client_info

from v$session

where username = 'MYUSER';

This can be easily modified to specific procedures/jobs to track that particular session.

Tip of the Week

Tip for Week of March 29, 2004

Optimize Your UNDO Parameters

This tip comes from Marco Gilbert, DBA, MRQ in Ste-Foy, Quebec, Canada.

When you are working with UNDO (instead of ROLLBACK) there are two important things to consider: 1.)
the size of the UNDO tablespace, and 2.) the UNDO_RETENTION parameter.

There are two ways to proceed :

1) You can choose to allocate a specific size for the UNDO tablespace and then set the
UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If
your disk space is limited and you do not want to allocate more space than necessary to the UNDO
tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION
parameter :

select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",

substr(e.value,1,25) "UNDO RETENTION (Secs)",

round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec)))

"OPTIMAL UNDO RETENTION (Secs)"

from (select sum(a.bytes) undo_size

from v$datafile a,

v$tablespace b, dba_tablespaces c

where c.contents = 'UNDO'

and c.status = 'ONLINE'

and b.name = c.tablespace_name

and a.ts# = b.ts#) d,

v$parameter e,

v$parameter f,

(select max(undoblks/((end_time-begin_time)360024)) undo_block_per_sec

from v$undostat) g

where e.name = 'undo_retention'

and f.name = 'db_block_size'

2) If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is
best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the
database activity:

select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",

substr(e.value,1,25) "UNDO RETENTION (Secs)",

(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) /

(1024*1024) "NEEDED UNDO SIZE (MEGS)"

from (select sum(a.bytes) undo_size

from v$dataf! ile a,

v! $tablespace b,

dba_tablespaces c

where c.contents = 'UNDO'

and c.status = 'ONLINE'

and b.name = c.tablespace_name

and a.ts# = b.ts#) d,

v$parameter e,

v$parameter f,

(select max(undoblks/((end_time-begin_time)360024)) undo_block_per_sec

from v$undostat) g

where e.name = 'undo_retention'

and f.name = 'db_block_size'

;
The previous query may return a "NEEDED UNDO SIZE" that is less than the "ACTUAL UNDO SIZE". If this
is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value
or increase your UNDO_RETENTION parameter to use the additional space.

WARNING: Because these queries use the V$UNDOSTAT statistics, run the queries only after the
database has been running with UNDO for a significant and representative time.

Tip of the Week

Tip for Week of March 22, 2004

Deleting Duplicate SYS Constraints

This tip comes from Devanshi Parikh, Sr. DBA, Ernst and Young LLP in Lyndhurst, New Jersey.

System generated (redundant) check constraints can really drain the performance of a database. But
sometimes, such as when using third party tools with Oracle, it becomes impossible to avoid them. I have
developed a script, which is actually a UNIX Korn shell script, that retrives information from the Oracle
dictionary tables, and then formats the output to generate SQL statements that can be executed in the
database. Also, the script puts you in more control since, because you decide which and how many tables
you are interested in.

To run the script, first create a text file with one column, and one or more entries in the form tablename(
Uppercase ). The file can include any number of table names. The script accepts this text file's name as
the input argument. Each table listed in the input file is read and passed to Oracle Database to retrive the
relevent information.

Note that I have used hard coded the schema name and password in the script, which you can edit.

For each table three files in the format _.tmp, _.txt and _.sql are generated by the script in the working
directory. The .tmp and .txt files are intermidiate files while the .sql files contain the actual drop constraint
statements. The script leaves out the originally created check constraints; it generates statements for the
constraints that are genuine duplicates.

The script assumes that each column originally did not have more than 2 distinct SEARCH_CONDITION
values. (This is true in most cases, because the columns end up having a NOT NULL and a LENGTH
condition in the SEARCH_CONDITION.) In this kind of a scenerio, if data migrations occur between
databases, with the CONSTRAINTS=N clause missing, you end up with lots of duplicate check constraints.

Run the script, search.sh, from the UNIX prompt:

#!/us! r/bin/ksh

# search.sh

# Name of the file containing table names.

# First Written : 02/12/04 Devanshi Parikh

#
INFILE=$1

infile=`cat ${INFILE}`

for TAB_NAME in ${infile}

rm ${TAB_NAME}_${ORACLE_SID}.txt

rm ${TAB_NAME}_${ORACLE_SID}.tmp

rm ${TAB_NAME}_${ORACLE_SID}.sql

sqlplus -s sysadm/config <<-!

spool ${TAB_NAME}_${ORACLE_SID}.tmp

set head off

select '${TAB_NAME}' from dual;

whenever sqlerror exit 1

column search_condition format a35

column constraint_name format a20

column column_name format a22

select a.constraint_name,b.column_name,search_condition

from user_constraints a,user_cons_columns b

where a.table_name=b.table_name and

a.constraint_type='C' and a.constraint_name=b.constraint_name

and a.table_name = '${TAB_NAME}'

order by b.column_name,last_change;

spool off

exit

cat ${TAB_NAME}_${ORACLE_SID}.tmp | grep -v "IS NOT NULL" |\

sed '/^$/d' > ${TAB_NAME}_${ORACLE_SID}.txt

table_name=`grep -v "^SYS_" ${TAB_NAME}_${ORACLE_SID}.txt | grep -v

rows`

column_name='aa'

constraint_name='aa'

grep "^SYS_" ${TAB_NAME}_${ORACLE_SID}.txt | \

while read cons_line

constraint_name_next=`echo $cons_line |cut -d " " -f 1`

column_name_next=`echo $cons_line |cut -d " " -f 2`

if [ ${column_name_next} = ${column_name} ]

then

echo "ALTER TABLE SYSADM.${table_name} DROP CONSTRAINT

${constraint_name_n

ext};" >> ${TAB_NAME}_${ORACLE_SID}.sql

column_name=${column_name_next}

constraint_name=${constraint_name_next}

done

exit
Tip of the Week
Tip for Week of March 8, 2004

Blank Lines and Beginning Spaces with DBMS_OUTPUT.PUT_LINE

This tip comes from Pasupuleti Sailaja, in Hyderabad, India.

To display a blank line you can use chr(0) with DBMS_OUTPUT.PUT_LINE.

To display beginning spaces you can use chr(9) or chr(160) with DBMS_OUTPUT.PUT_LINE.

Use chr(9) for fewer spaces and chr(160) for more spaces.

Example:

set serveroutput on feedback off

prompt

begin
dbms_output.put_line('This is 1st message Line and next line is
blank.');

dbms_output.put_line(chr(0));

dbms_output.put_line('This is 2nd message Line and next line is

blank.');

dbms_output.put_line(chr(0));

dbms_output.put_line('This is 3rd message Line.');

dbms_output.put_line(chr(9)||' This is 4th message line with

beginning spaces.')! ;

dbms_output.put_line(chr(160)||' This is 5th message line with

beginning spaces.');

end;

prompt

set serveroutput off feedback on feedback 6

------------------------------------------------------------------------
----------------------------------------

Output:

This is 1st message Line and next line is blank.

This is 2nd message Line and next line is blank.

This is 3rd message Line.

This is 4th message line with beginning spaces.

This is 5th message line with beginning spaces.

Tip of the Week
Tip for Week of March 1, 2004

Separating Comma Separated Strings

This tip comes from Ramesh Periyasamy, Programmer/Analyst, in Iselin, New Jersey.

This function will split an input string separated by commas into a string enclosed with single quotes. This is
helful in applying conditions in dynamic SQL.

dyn_string1 Function:

create or replace function dyn_string1(ab varchar2)return varchar2

t varchar2(2):=',';

t1 varchar2(2):='''';

t2 varchar2(32767);

t_check number(10);

a varchar2(32767);

begin

a:=ab;

-- dbms_output.put_line('The string passed is '||a);

t_check:=instr(a,',');

-- dbms_output.put_line('The instr value is '||t_check);

if t_check!=0 then

t2:=t1||t||t1;

a:=replace(a,',',t2);

a:='('||''''||a||''''||')';

-- dbms_output.put_line(a);

else

a:=a;

a:='('||''''||a||''''||')';

end if;

return a;
END ;

Example:

select dyn_string1('2334,3!
45,5656,6767,7878,78989,8989,8989,9090,9090,90,90,9090,90,090,,9090,909)
from dual

Output:

DYN_STRING1('2334,345,5656,6767,7878,78989,8989,8989,9090,9090,90,90,909
0,90,090,9090,909)

------------------------------------------------------------------------
------------------

('2334','345','5656','6767','7878','78989','8989','8989','9090','9090','
90','90','9090','90','090','','9090','909')

To use this dyn_string1 function along with dynamic SQL:

Create or replace PROCEDURE test(actcode IN VARCHAR2,fr_date IN

DATE,to_date IN DATE,)

B date;

C date;

actcode VARCHAR2(32767);

sql_stmt varchar2(32767);

Begin

a:=dyn_string1(actcode);

b:=fr_date;

c:=to_date;
sql_stmt:='INSERT INTO Table2 select A1.NYCWAY_CASE_NUMBER

FROM table1 a

WHERE A.column1 in '||a||' and

A.column2 between '||''''||b||''''||' AND '||''''||c||''''||' ;

execute immediate sql_stmt ;

end;

Tip of the Week

Tip for Week of February 23, 2003

Collected Date/Time Tips, Part 2

We get a lot of tips on managing date and time date. Here's part two of a collection of some date/time
tips.

Calculating Working Days Without Using a Function

byMike Selvaggio

Date/Time from Substraction of Two Date Values

by Galina Petrenko

Find the Numer of Days

by Sidhartha Upadhyay

Get Weeks' Start and End Dates

by Murthy Mamidanna

Get the Next "D" Day

by Marco Gilbert

Back to Date/Time Tips, Part 1

Tip of the Week

Tip for Week of February 16, 2003

Collected Date/Time Tips, Part 1

We get a lot of tips on managing date and time data. Here's part one of a collection of some date/time
tips.

Finding Local Time Zone in Oracle8i

by Krishna Sarabu

Get Monthly Calendar from SQL*Plus

by Pablo Rovedo

Return Hours Worked

by Srikanth Cherla

Find How Many Times a Day Appears

by Srinivas Kasam

Date/Time Tips, Part 2

Tip of the Week
Tip for Week of February 9, 2004

Generate Spool File Layout

This tip comes from Gautam Arora, DBA, in NY, USA.

Use this simple SQL script--lay.sql--to generate the layout for the spooled file. Run the script before spooling
the table to get the layout.
Layout contains :
------------------
Column Name,
Data Type,
Length of Column,
Start Position of Column,
End Position of Column,
SQL*LOADER script,
R/L of Rec is "End Position" in Last Line
Paste the following script into c:\lay.sql, and run this script at the SQL prompt.

set linesize 400

set heading off
set pagesize 9999
select column_name
,data_type
,len
,start_pos
,end_pos
,(case when (data_type='CHAR' or data_type='VARCHAR2')
then ','||lower(column_name)||' '||'position('||start_pos||':'||
end_pos||')'
when data_type='NUMBER'
then ','||lower(column_name)||' '||'position('||start_pos||':'||
end_pos||')
integer external defaultif ('||st!art_pos||':'||end_pos||')=blanks'
when data_type='DATE'
then ','||lower(column_name)||' '||'position('||start_pos||':'||
end_pos||') date nullif
('||start_pos||':'||end_pos||')=blanks' end) as scr
from (select column_name, data_type, len, ((end_pos-len)+1) as
start_pos, end_pos
from (select column_name, data_Type, len, sum(len) over (order by
column_id) as end_pos
from (select column_name
,column_id
,substr(data_type,1,10) data_Type
,decode(data_type,
'NUMBER',(case when length(column_name) <= 10
then 10
when length(column_name) > 10
then length(column_name)
! ! end)
,'CHAR',data_length
,'DATE',length(sysdate)) as len
from user_tab_cols
where table_name='&table_name'
order by column_id)));
Example: You want to spool scott.emp.

SQL>spool c:\emp.lay
SQL>@c:\lay.sql
Note the R/L (End Pos in Last Line) from emp.lay. Now you are ready to 'spool set' the following and change
the line size with R/L:

SQL> set linesize 77

SQL> set colsep ""
SQL> set pagesize 0
SQL> set newpage none
SQL> set feedback off
SQL> set termout off
SQL> set recsep off
SQL> set sqlprompt ""
SQL> set heading off
SQL> set echo off

SQL> spool c:\emp.txt

SQL> select * from emp;
SQL> spool off

Tip of the Week

Tip for Week of February 2, 2004

Using LogMiner

This tip comes from Frank Pantaleo, Database Technologist, in Rochester, NY.

This tip is about using LogMiner to replicate data between a production instance and a remote instance. The
biggest advantage is this technique does not require any changes to the production instance if you are just
capturing inserts. If you want to capture updates/deletes there are issues in Oracle8i. Oracle8i does not
capture key information in logs as it does in Oracle9i. I have a work around for this, but it does not work very
well for high volume tables.
This body of work that follows was born out of a desire to replicate data out of production. Of course we
needed to do this while minimizing the effect to production. The best way to do this was to leverage API’s
that Oracle made available in Oracle8i. Some or all of this logic provided becomes unnecessary in Oracle9i
with the advent of Oracle Streams. (Although I would contend that this provides a better level of control. I
made a pitch to a technical community that I am involved in. The pitch was based on a article from George
Jucan at http://www.opendatasys.com . The article was called "Using Oracle LogMiner as a Data Replication
Utility". The article explains the way a program or set of programs could make use of this api. Our business
had already leveraged information available in the LogMiner to track activity in an application. )
There are some C applications are involved in this as well. I am only supplying the PL/SQL and C Oracle
external functions as I feel this is enough to get started. Some of these file and directory procdures were
created to reproduce functions that are now available in Oracle9i.
Dir_proc: C Oracle external application to get a list of all files in a unix directory
File_proc: C Oracle external application to determine the existence of a UNIX file
File_del_proc: C oracle external application to delete a file in /tmp
Get_ora_tab: Function that drives the diy$oratab view
Logminer_stats: Procedure that returns counts of local logmnr_contents store
Load_logminer: The workhorse of the application. Identifies, Extracts, and deletes archive log into local store
For Oracle8i LogMiner, I have another program that creates a known state. Oracle8i LogMiner captures the
rowid for update and deletes, but Oracle9i LogMiner captures additional information on deletes and updates,
including whether the table being captured has a primary/unique key.
For Oracle8i LogMiner, I have another program that creates a known state:
The initial table state is captured from the source instance, including the rowid of each row from the source
table, and I capture this in a column called drowid. The drowid is used to apply the update or delete to the
destination table data. Once a state is captured, I can then move on to the logic supplied here and extract
changes from a set of archive logs.
So the process is:
1. Enable archive logging in source instace.
2. Create a known state--this is only necessary in Oracle8i and then only if you need to worry about
update/deletes.
3. Copy all archive logs from source to the machine where the destination instance lives; this process is
ongoing afterward.
4. Change the init.ora of the destination instance to have log_archive_dest point to the archive logs that
have been captured from the source instance.
5. At regular intervals:
a. run the load_logminer proc provided
b. apply the dml in logmnr_contents to the destination instance
c. update checkpoint_change# in lmstate_checkpoint as each dml is applied
d. commit after the scn changes in logmnr_contents table
-------------------------- pl/sql source --------------------
First I create a schema with dba rights (lmdba):

create user lmdba identified by password default tablespace lmdata

temporary tablespace temp;
grant dba,connect to lmdba;
grant execute on dbms_logmnr to lmdba;
grant execute on dbms_logmnr_d to lmdba;
grant select on sys.v_$database to lmdba;
grant select on sys.v_$parameter to lmdba;
grant select on sys.GV_$LOGMNR_logs to lmdba;
grant select on sys.GV_$LOGMNR_contents to lmdba;
grant select on sys.V_$LOGMNR_contents to lmdba;
grant execute on dbms_backup_restore to lmdba;
grant analyze any,select any table,delete any table,insert any table,
update any table to lmdba;
grant alter any table to lmdba;
grant drop any table to lmdba;
Then as the lmdba user:

drop view diy$oratab;

drop function get_oratab;
drop type oratab_type;
drop type oratab_row_type;

drop LIBRARY os_lib ;

CREATE LIBRARY os_lib is '/u01/apps/oracle/product/8.1.7/lib/osfunc.so';
/
CREATE OR REPLACE procedure dir_proc (FileName IN char,Path in char)
as external
library os_lib
name "dir_func"
language C
parameters
(FileName string,Path string);
/
--
CREATE OR REPLACE procedure file_proc (FileName IN char, bexists out
binary_integer,file_size out binary_integer, block_count out
binary_integer)
as external
library os_lib
name "file_func"
language C
with context
parameters
(context,
FileName string,
bexists int,
file_size int,
block_count int
);
/
CREATE OR REPLACE procedure file_del_proc (FileName IN char)
as external
library os_lib
name "file_del_func"
language C
parameters
(FileName string);
/
drop TABLE LMSTATE_CHECKPOINT;
CREATE TABLE LMSTATE_CHECKPOINT (CHECKPOINT_CHANGE# NUMBER);
--
drop TABLE LMSUBSCRIBE;
CREATE TABLE LMSUBSCRIBE (
OWNER VARCHAR2 (30),
TABLE_NAME VARCHAR2 (30) ) ;
--
drop TABLE LMTABLES;
CREATE TABLE LMTABLES (
OWNER VARCHAR2 (30),
TABLE_NAME VARCHAR2 (30),
LOAD_ORDER numeric default 10 not null) ;
--
drop TABLE LM_LOG;
CREATE TABLE LM_LOG (LM_STATE VARCHAR2(2000));
--
drop table LOGMNR_CONTENTS;
create table LOGMNR_CONTENTS (
SCN NUMBER,
TIMESTAMP DATE,
THREAD# NUMBER,
LOG_ID NUMBER,
XIDUSN NUMBER,
XIDSLT NUMBER,
XIDSQN NUMBER,
RBASQN NUMBER,
RBABLK NUMBER,
RBABYTE NUMBER,
UBAFIL NUMBER,
UBABLK NUMBER,
UBAREC NUMBER,
UBASQN NUMBER,
ABS_FILE# NUMBER,
REL_FILE# NUMBER,
DATA_BLK# NUMBER,
DATA_OBJ# NUMBER,
DATA_OBJD# NUMBER,
SEG_OWNER VARCHAR2(32),
SEG_NAME VARCHAR2(32),
SEG_TYPE NUMBER,
SEG_TYPE_NAME VARCHAR2(32),
TABLE_SPACE VARCHAR2(32),
ROW_ID VARCHAR2(19),
SESSION# NUMBER,
SERIAL# NUMBER,
USERNAME VARCHAR2(32),
SESSION_INFO VARCHAR2(4000),
ROLLBACK NUMBER,
OPERATION VARCHAR2(32),
SQL_REDO VARCHAR2(4000),
SQL_UNDO VARCHAR2(4000),
RS_ID VARCHAR2(32),
SSN NUMBER,
CSF NUMBER,
INFO VARCHAR2(32),
STATUS NUMBER,
PH1_NAME VARCHAR2(32),
PH1_REDO VARCHAR2(2000),
PH1_UNDO VARCHAR2(2000),
PH2_NAME VARCHAR2(32),
PH2_REDO VARCHAR2(2000),
PH2_UNDO VARCHAR2(2000),
PH3_NAME VARCHAR2(32),
PH3_REDO VARCHAR2(2000),
PH3_UNDO VARCHAR2(2000),
PH4_NAME VARCHAR2(32),
PH4_REDO VARCHAR2(2000),
PH4_UNDO VARCHAR2(2000),
PH5_NAME VARCHAR2(32),
PH5_REDO VARCHAR2(2000),
PH5_UNDO VARCHAR2(2000)
);
--
drop TABLE get_oratab_setting;
CREATE GLOBAL TEMPORARY TABLE get_oratab_setting
(thedir varchar2(200))
ON COMMIT PRESERVE ROWS;
--
Create Type Oratab_Row_Type As Object ( FILE_NAME Varchar2(100));
/
Create Type Oratab_Type Is Table Of Oratab_Row_Type;
/
--
Create Or Replace Function GET_ORATAB Return Oratab_Type Is
Ora_Tab Oratab_Type :=
Oratab_Type( Oratab_Row_Type( Null ) );
f_Handle Utl_File.File_Type;
i_Pos Integer;
v_file_name Varchar2(100);
b_Read Boolean := TRUE;
b_First Boolean := TRUE;
tmp_file varchar2(50);
mydir varchar2(200);
Begin
--
-- Note that in order to make the code shorter all the
-- utl_file defined exceptions are left unhandled.
--
tmp_file :='oracle_' || to_char(sysdate,'yyyymmddhhss');
begin
select thedir into mydir from get_oratab_setting;
Exception
When NO_DATA_FOUND Then mydir := null;
end;
if mydir is not null then
dir_proc('/tmp/' || tmp_file,mydir);
f_Handle := Utl_File.FOpen( '/tmp' , tmp_file, 'r' );
While b_Read Loop
Begin
Utl_File.Get_Line( f_Handle, v_file_name);
if b_first then
b_First := FALSE;
Else
Ora_Tab.Extend;
End If;

Ora_Tab( Ora_Tab.Last ) :=
Oratab_Row_Type( rtrim(v_file_name));
Exception
When NO_DATA_FOUND Then
b_Read := FALSE;
End;
End Loop;
Utl_File.FClose( f_Handle );
end if;
file_del_proc(tmp_file);
Return Ora_Tab;
End;
/
Create Or Replace View DIY$ORATAB
As Select * From Table( Cast( GET_ORATAB() As Oratab_Type ) );
/
CREATE OR REPLACE PROCEDURE "LOGMINER_STATS" (insert_count out
integer,delete_count out integer, update_count out integer,total_count
out integer) is
EMPTY_LOGMNR_CONTENTS exception;
PRAGMA EXCEPTION_INIT(EMPTY_LOGMNR_CONTENTS, -1306);
cursor the_csr is Select count(*) the_count,operation
from LOGMNR_contents,lmsubscribe b
where seg_owner = b.owner
and seg_name = b.table_name
and operation in ('INSERT','DELETE','UPDATE')
group by operation;

begin
insert_count :=0;
update_count :=0;
delete_count :=0;
total_count :=0;
for the_rec in the_csr loop
if the_rec.operation = 'INSERT' then
insert_count := the_rec.the_count;
elsif the_rec.operation = 'DELETE' then
delete_count := the_rec.the_count;
elsif the_rec.operation = 'UPDATE' then
updat

Tip of the Week

Tip for Week of January 25, 2004

Comparing Two Tables: Highlighting the Differences

This tip comes from Sanjay Ray, Oracle Applications Technical Consultant in Sydney, Austrailia.

This is an extension to the code tip that I supplied on 21 December 2003, "Comparing Contents of Two
Tables with Identical Structure." The following script highlights the columns that are different in the two
tables, so there is no need to compare each column individually. The columns values may optionally be
displayed as well.</SPAN< p> This script generates a spool file that can be run from a SQL*Plus session to
list the differences between 2 tables with identical structure.
This can be useful if you need to compare two similar tables across different schemas (or different
databases, in which case you will need to create a local view using a database link), or to compare a table
with itself at different points in time, such as before and after an update through an application to track how a
table is affected. It reports whether a record is present in one but not in the other table, or if the same key is
present in both tables but non-key columns have different values.

undefine TABLE1
undefine TABLE2
define g_table1 = '&&TABLE1'
define g_table2 = '&&TABLE2'
set verify off
set feedback off
set serveroutput on size 1000000
set linesize 132
set trimspool on
spo temp_file.sql
declare
v_owntab1 varchar2(255) := '&&g_table1';
v_owntab2 varchar2(255) := '&&g_table2';
v_own1 varchar2(255);
v_own2 varchar2(255);
v_tab1 varchar2(255);
v_tab2 varchar2(255);
v_dot1 number := 0;
v_dot2 number := 0;
type t_cols is table of varchar2(255) index by binary_integer;
v_cols1 t_cols;
v_cols2 t_cols;
v_keys t_cols;
v_out1 varchar2(255);
v_out2 varchar2(255);
kq CONSTANT varchar2(1) := '''';
v_ind number := 0;
v_str varchar2(2000):=null;
v_ind_found boolean := FALSE;
v_ind_colno number := 0;
v_is_key boolean := FALSE;
v_nonkey number := 0;
procedure print_cols (p_cols in t_cols) is
begin
for i in 1..p_cols.count
loop
dbms_output.put_line(','||p_cols(i)); end loop;
end print_cols;

begin
v_dot1 := instr(v_owntab1, '.');
v_dot2 := instr(v_owntab2, '.');

if v_dot1 > 0 then

v_own1 := upper(substr(v_owntab1, 1, v_dot1-1));
v_tab1 := upper(substr(v_owntab1, v_dot1+1));
else
v_own1 := null;
v_tab1 := upper(v_owntab1);
end if;

if v_dot2 > 0 then

v_own2 := upper(substr(v_owntab2, 1, v_dot2-1));
v_tab2 := upper(substr(v_owntab2, v_dot2+1));
else
v_own2 := null;
v_tab2 := upper(v_owntab2);
end if;

select column_name
bulk collect into v_cols1
from all_tab_columns
where table_name = v_tab1
and owner = nvl(v_own1, user)
order by column_id;

select column_name
bulk collect into v_cols2
from all_tab_columns
where table_name = v_tab2
and owner = nvl(v_own2, user)
order by column_id;

if v_cols1.count = 0 or v_cols2.count = 0 then

dbms_output.put_line('Either or Both the tables are invalid');
return;
end if;

--Outer select
dbms_output.put_line('select * from ( ');
--Main select (key attributes)
dbms_output.put_line('select ');
for c1 in (
select b.column_name
from all_indexes a, all_ind_columns b
where a.owner=b.index_owner
and a.index_name=b.index_name
and a.uniqueness = 'UNIQUE'
and a.table_owner = nvl(v_own1, user)
and a.table_name = v_tab1
order by b.index_name, b.column_position
)
loop
v_ind_found := TRUE;
v_keys(nvl(v_keys.count, 0)+1):=c1.column_name;
dbms_output.put_line('nvl(a.'||c1.column_name||', b.'||
c1.column_name||') '||c1.column_name||',');
end loop;

if not v_ind_found then

v_keys(nvl(v_keys.count, 0)+1):=v_cols1(1);
dbms_output.put_line('nvl(a.'||v_cols1(1)||', b.'||v_cols1(1)||')
'||v_cols1(1)||',');
end if;

--Identifier column to indicate if the key is present in either, or

the other or both tables
dbms_output.put_line('decode(a.'||v_cols1(1)||', null, '||
kq||'TAB2'||kq
||', decode(b.'||v_cols1(1)||', null, '||kq||'TAB1'||kq||', '||
kq||'BOTH'||kq||')) WHICH, ');

--Main select (non-key attributes)

for i in 1..v_cols1.count
loop
v_is_key:=FALSE;
--If the column is a key column, it should be excluded
for j in 1..v_keys.count
loop
if v_cols1(i)=v_keys(j) then
v_is_key:=TRUE;
end if;
end loop;
if not v_is_key then
v_nonkey:=v_nonkey+1;
--The following code has 2 sections. Only one can be activated at
any time
--Presently Section 1 is active, i.e. only column list will be
displayed

-- To display column list without values, uncomment Section 1

--Start of Section 1 : Column List only. Without values
if v_nonkey = 1 then
dbms_output.put_line('decode(a.'||v_cols1(i)||',b.'||v_cols1(i)||',
null,'||kq||v_cols1(i)||kq||')');
else
dbms_output.put_line('||'||kq||','||kq||'||'||'decode(a.'||
v_cols1(i)||',b.'||v_cols1(i)||', null,'||kq||v_cols1(i)||kq||')');
end if;
--End of Section 1 : Column List without values

/*
--To display column list with values, uncomment Section 2
--Start of Section 2 : Column List with values
if v_nonkey = 1 then
dbms_output.put_line('decode(a.'||v_cols1(i)||',b.'||v_cols1(i)||',
null,'||kq||v_cols1(i)
||'=('
||kq||'||a.'||v_cols1(i)||'||'||kq||','
||kq||'||b.'||v_cols1(i)||'||'||kq||')'||kq
||')');
else
dbms_output.put_line('||'||kq||','||kq||'||'||'decode(a.'||
v_cols1(i)||',b.'||v_cols1(i)||', null,'||kq||v_cols1(i)
||'=('
||kq||'||a.'||v_cols1(i)||'||'||kq||','
||kq||'||b.'||v_cols1(i)||'||'||kq||')'||kq
||')');
end if;
--End of Section 2 : Column List with values
*/

end if;
end loop;
dbms_output.put_line('COL_LIST');

--from clause
dbms_output.put_line('from '||nvl(v_own1, user)||'.'||v_tab1||' a
');
dbms_output.put_line('full outer join '||nvl(v_own2, user)||'.'||
v_tab2||' b ');
dbms_output.put_line('on (');

--Where condition
for i in 1..v_keys.count
loop
if i = 1 then
dbms_output.put_line('a.'||v_keys(i)||'=b.'||v_keys(i));
else
dbms_output.put_line('and a.'||v_keys(i)||'=b.'||v_keys(i));
end if;
end loop;

dbms_output.put_line(')');

--Close Outer select

dbms_output.put_line(') ');
dbms_output.put_line('where WHICH = '||kq||'TAB1'||kq);
dbms_output.put_line('or WHICH = '||kq||'TAB2'||kq);
dbms_output.put_line('or (WHICH = '||kq||'BOTH'||kq||' and
ltrim(rtrim(replace(COL_LIST,'||kq||','||kq||',null))) is
not null)');

--We shall order by the key columns

dbms_output.put_line('order by');
for i in 1..v_keys.count
loop
if i=v_keys.count then
dbms_output.put_line(i);
else
dbms_output.put_line(i||',');
end if;
end loop;

dbms_output.put_line(';');

end;
/
spo off
set feedback on

Testing:

--Setup of Test Data

drop table test1;
drop table test2;
create table test1 (f1 number unique, f2 number unique, f3 number,
f4 varchar2(10), f5 date);
create table test2 (f1 number, f2 number, f3 number, f4
varchar2(10), f5 date);

insert into test1 values (1, 10, 100, 'A1000', to_date('01011901',

'DDMMRRRR'));
insert into test1 values (2, 20, null, null, to_date('01011901',
'DDMMRRRR'));
insert into test1 values (3, 30, 300, 'A3000', to_date('01011901',
'DDMMRRRR'));
insert into test1 values (4, 40, 400, 'A4000', to_date('01011901',
'DDMMRRRR'));
insert into test1 values (7, 70, 700, 'A7000', to_date('01011901',
'DDMMRRRR'));

insert into test2 values (1, 10, -100, 'A1000', to_date('01011901',

'DDMMRRRR'));
insert into test2 values (2, 20, null, null, to_date('01011901',
'DDMMRRRR'));
insert into test2 values (3, 30, -500, 'A5000', to_date('01011901',
'DDMMRRRR'));
insert into test2 values (6, 60, -600, 'A6000', to_date('01011901',
'DDMMRRRR'));
insert into test2 values (7, 70, 700, 'A7000', to_date('01011901',
'DDMMRRRR'));
Run the script and pass the table names TEST1 and TEST2 when prompted. Run the spool file temp_file.sql
from SQL*Plus:
SQL> @temp_file
Output (column list only):
F1 F2 WHICH COL_LIST
1 10 BOTH F3,,
3 30 BOTH F3,F4,
4 40 TAB1 F3,F4,F5
4 60 TAB2 F3,F4,F5
7 70 BOTH ,,F5
Output (with column values displayed):
F1 F2 WHICH COL_LIST
1 10 BOTH F3=(100,-100),,
3 30 BOTH F3=(300,-500),F4=(A3000,A5000),
4 40 TAB1 F3=(400,),F4=(A4000,),F5=(01-JAN-01,)
4 60 TAB2 F3=(,-600),F4=(,A6000),F5=(,01-JAN-01)
7 70 BOTH ,,F5=(01-JAN-01,02-JAN-01)
Note:
1. Parameters can be in either schema.table_name or table_name format (in which case current schema is
assumed).
2. By default, the script lists non-key columns using the key columns as the basis of comparison. If you don't
need all the columns, edit the spool file to comment out the columns that are not required. If key column is
being removed, remove the column from the "order by" list as well.
3. Output is sorted on the key columns. It determines the key list by querying the data dictionary ALL_INDEX
and ALL_IND_COLUMNS views for the FIRST table passed as input. If no unique indexes are found, then
ordering is based always on the first column of the tables. (In the test case, columns F1 and F2 have been
defined as Unique keys of the first table.) So if one of the tables is located in a remote database, make it the
second table when generating the spool file.
4. This script was tested for scalar values, for example, number, varchar2, and date. The script may not
work for for long, LOB and Object Types.
5. If the key value is present in either of the tables but not both (the value of the WHICH column is TAB1 or
TAB2), then the column list will include all columns in the table. If WHICH=BOTH, then only columns with
different values are displayed.
6. The script would normally print the column list without the values. There is, however, a provision to output
the values as well. To display the column values, comment out the segment of code marked ?Section 1?
and uncomment ?Section 2?. This may affect performance for large tables.
7. No Key column may have null values in both tables.
8. The first Key column may not be null in either table.

Tip of the Week

Tip for Week of January 11, 2004

List Table Names, Rows, and Created Date

This tip comes from Pasupuleti Sailaja, in Hyderabad, Andhra Pradesh, India.

Description: -- Aim : To display table names, number of rows

existed in table
-- and table created date in current login user for
any ORACLE version
-- Usage : Step 1) All lines from given program save with
filename DIR.SQL
-- Step 2) Run the file DIR.SQL by giving START DIR (or)
@ DIR at SQL *Plus prompt
-- i.e. SQL> @ DIR
-- Author : Pasupuleti Sailaja, ORACLE favorite, Hyderabad-
500072, India.
-- E-mail : SAILAJAMAIL@YAHOO.COM
-- Program :

set serveroutput on size 100000 feedback off

declare
rs integer;
cur integer;
rp integer;
trs integer;
n integer;
un varchar2(30);
begin
dbms_output.put_line(rpad('Table Name',40)||' Number of Rows
Created Date');
dbms_output.put_line(rpad('-',73,'-'));
cur:= dbms_sql.open_cursor;
for t in (select object_name, created from user_objects
where object_type='TABLE') loop
dbms_sql.parse(cur,'select count(*) from ' || t.object_name,
dbms_sql.v7);
dbms_sql.define_column(cur, 1, rs);
rp:= dbms_sql.execute(cur);
n:=dbms_sql.fetch_rows(cur);
dbms_sql.column_value(cur, 1, rs);
dbms_output.put_line(rpad(t.object_name,48,'.')||
rpad(rs,15,'.')||t.created);
end loop;
dbms_sql.close_cursor(cur);
select count(*) into n from tab where tabtype='TABLE';
select user into un from dual;
dbms_output.put_line(rpad('-',73,'-'));
dbms_output.put_line(un||' User contain '||n||' Table(s)');
end;
/

set serveroutput off feedback on feedback 6

-- Example Output:

Table Name Number of Rows

Created Date

------------------------------------------------------------------------
-
ACCTS...........................................5..............29-
JUN-03
ACCT_ADDRS......................................5..............29-
JUN-03
BONUS...........................................0..............09-
AUG-00
CHESS_SAVE......................................0..............29-
JUN-03
CHESS_SAVE_BOARDSTATE...........................0..............29-
JUN-03
CHESS_SAVE_CAPTURED.............................0..............29-
JUN-03
CHESS_SAVE_PLAYER...............................0..............29-
JUN-03
CITIES..........................................205............29-
JUN-03
COMPANY_SUMMARY.................................3..............29-
JUN-03
CUSTOMER........................................9..............09-
AUG-00
CUSTOMERS.......................................14.............29-
JUN-03
DEMOKIT_CLASSES.................................9..............29-
JUN-03
DEMOKIT_DEMOS...................................23.............29-
JUN-03
DEMOKIT_DEMO_SCRIPTS............................14.............29-
JUN-03
DEMOKIT_INSTALLED_SCRIPTS.......................5..............29-
JUN-03
DEMOKIT_SCRIPTS.................................10.............29-
JUN-03
DEPT............................................4..............09-
AUG-00
DUMMY...........................................1..............09-
AUG-00
DUPEMP..........................................7168...........12-
AUG-00
EMP.............................................14.............09-
AUG-00
FFI$FUNCTION....................................2..............29-
JUN-03
FFI$FUNCTIONARGS................................10.............29-
JUN-03
FFI$LIBRARY.....................................1..............29-
JUN-03
FFI$TYPEDEF.....................................14.............29-
JUN-03
FUNDS...........................................5..............29-
JUN-03
FUND_CONTRIB....................................16.............29-
JUN-03
FUND_XACT.......................................45.............29-
JUN-03
F_EMPCOMP.......................................2..............29-
JUN-03
F_XACT_TYPE.....................................7..............29-
JUN-03
GAME_SEMAPHORE..................................0..............29-
JUN-03
INDCAT..........................................11.............29-
JUN-03
INVINFO.........................................5..............29-
JUN-03
INVREQUEST......................................12.............29-
JUN-03
ITEM............................................0..............09-
AUG-00
MENU_CAT........................................7..............29-
JUN-03
MENU_ITM........................................35.............29-
JUN-03
MODE_BUTTON.....................................9..............29-
JUN-03
ORD.............................................21.............09-
AUG-00
ORDER_HISTORY...................................5..............29-
JUN-03
ORDPICT.........................................21.............29-
JUN-03
PORTFOLIO.......................................39.............29-
JUN-03
PRICE...........................................17.............09-
AUG-00
PRODUCT.........................................10.............09-
AUG-00
RCL_CLASSIFICATIONS.............................6..............29-
JUN-03
RCL_COMPONENTS..................................7..............29-
JUN-03
RCL_COMPONENT_DEPENDANCIES......................6..............29-
JUN-03
RCL_FRAGMENTS...................................31.............29-
JUN-03
RCL_INSTRUCTIONS................................57.............29-
JUN-03
RCL_INSTRUCTIONS_TEXT...........................520............29-
JUN-03
RCL_MODULES.....................................31.............29-
JUN-03
SALES_REVENUE...................................16.............29-
JUN-03
SALGRADE........................................5..............09-
AUG-00
STOCKS..........................................328............29-
JUN-03
STOCK_HISTORY...................................11.............29-
JUN-03
TABLE_CONFIG....................................11.............29-
JUN-03
USA.............................................50.............29-
JUN-03

------------------------------------------------------------------------
-
SCOTT User contain 56 Table(s)

Tip of the Week

Tip for Week of January 11, 2004

List Table Names, Rows, and Created Date

This tip comes from Pasupuleti Sailaja, in Hyderabad, Andhra Pradesh, India.

Description: -- Aim : To display table names, number of rows

set serveroutput on size 100000 feedback off

set serveroutput off feedback on feedback 6

-- Example Output:

Table Name Number of Rows

Created Date

------------------------------------------------------------------------
-
SCOTT User contain 56 Table(s)

Tip of the Week

Tip for Week of January 4, 2004

Fast Reset Sequences (Alternative)

This tip comes from Ilya Petrenko, Senior DBA at ICT Group, Inc. USA, in Newtown, PA.

In a previous tip, Fast Reset Sequences, I showed how to reset a sequence to MinValue with few calls to the
data dictionary (SQL and PL/SQL implementations). The following script below is another approach to
achiev the same result with even fewer calls. This technique is very useful for environments with the heavy
use of sequences.
Script:
-------

set ver off

def SeqOwner=&Owner
def SeqName=&SequenceName

-- Save Current Settings

col Cur_Increment_By new_value Seq_Increment_By
col Cur_Min_Value new_value Seq_MinValue
col Cur_Cache_Size new_value Seq_Cache
select
Increment_By Cur_Increment_By,
Min_Value Cur_Min_Value,
DECODE(Cache_Size,0,'NoCache','Cache '||Cache_Size)
Cur_Cache_Size
from all_sequences
where
sequence_owner=UPPER('&SeqOWNER')
and sequence_name=UPPER('&SeqName')
and rownum<2;

-- Calculate Increment_By and MinValue for "BACK" Operation

col Value1 new_value Seq_Increment_By_Back
col Value2 new_value Seq_MinValue_Back
select
-1*(&SeqOwner..&SeqName..NextVal -&Seq_MinValue) Value1,
-1*(&Seq_Increment_By - &Seq_MinValue) Value2
from dual;

-- Modify for Reset

alter sequence &SeqOwner..&SeqName increment by &Seq_Increment_By_Back
MinValue &Seq_MinValue_Back;

-- Reset even Deeper (Increment_By Low than Original MinValue) select

&SeqOwner..&SeqName..NextVal from dual;

-- Return Back Increment_By , and Reset Current Cache Value alter

sequence &SeqOwner..&SeqName increment by &Seq_Increment_By NOCACHE;

-- Return Old(Current) Values Back such as Min_Value anc Cache Size (if
exists) alter sequence &SeqOwner..&SeqName increment by
&Seq_Increment_By MinValue &Seq_MinValue &Seq_Cache;
Example:
--------
set ver off

DEFINE SeqOwner=SBC
DEFINE SeqName=Seq1

select &SeqOwner..&SeqName..NextVal from dual;

NEXTVAL
----------
55

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CACHE_SIZE

LAST_NUMBER
-------------------- ---------- ---------- ------------ ----------
-----------
SEQ1 -19 1.0000E+27 5 30 160

CUR_INCREMENT_BY CUR_MIN_VALUE CUR_CACHE_SIZE

---------------- -------------
----------------------------------------------
5 -19 Cache 30

VALUE1 VALUE2
---------- ----------
-79 -24

SQL> -- Reset even Deeper (Increment_By Low than MinValue)

NEXTVAL
----------
-19

SEQUENCE_NAME CURRENT_MIN CURRENT_MAX CURRENT_STEP CACHE_SIZE

LAST_NUMBER
-------------------- ----------- ----------- ------------ ----------
-----------
SEQ1 -24 1.0000E+27 -79 30 -98

SQL> -- Return Back Increment_By , and Reset Current Cache Value

SEQUENCE_NAME CURRENT_MIN CURRENT_MAX CURRENT_STEP CACHE_SIZE

LAST_NUMBER
-------------------- ----------- ----------- ------------ ----------
-----------
SEQ1 -24 1.0000E+27 5 0 -14

SQL> -- Return Old(Current) Values Back such as Min_Value anc Cache Size

SQL> (if exists)

SEQUENCE_NAME CURRENT_MIN CURRENT_MAX CURRENT_STEP CACHE_SIZE

LAST_NUMBER
-------------------- ----------- ----------- ------------ ----------
-----------
SEQ1 -19 1.0000E+27 5 30 -14
The next call to the sequence will cache values again based on Increment_By and Cache_Size values.
To avoid output, you can use NOPRINT for all defined columns.
P.S. For my PL/SQL approach to resetting sequences, please refer to my previous tip Fast Reset
Sequences.

Data Analysis Made Easy
100% (1)
Data Analysis Made Easy
243 pages
Guide To SQL 9th Edition Pratt Solutions Manual 1
100% (74)
Guide To SQL 9th Edition Pratt Solutions Manual 1
7 pages
Exam Activator
100% (1)
Exam Activator
231 pages
SQL Queries: 200+ Queries to Challenge you.
From Everand
SQL Queries: 200+ Queries to Challenge you.
Swaroop Kallakuri
5/5 (2)
Oracle SQL Cheatsheet
No ratings yet
Oracle SQL Cheatsheet
2 pages
Counting The Costs Martin Widlake's Yet Another Oracle Blog
No ratings yet
Counting The Costs Martin Widlake's Yet Another Oracle Blog
7 pages
Unit 2 Advanced SQL
No ratings yet
Unit 2 Advanced SQL
21 pages
Rdbms 2
No ratings yet
Rdbms 2
21 pages
Oracle SQL Faq
No ratings yet
Oracle SQL Faq
14 pages
Most Useful Queries
No ratings yet
Most Useful Queries
26 pages
SQL Query
No ratings yet
SQL Query
139 pages
How To Remove Duplicate Rows Without Using DISTINCT or ROWID or GROUP by Methods in Oracle
0% (1)
How To Remove Duplicate Rows Without Using DISTINCT or ROWID or GROUP by Methods in Oracle
36 pages
Oracle SQL Functions
No ratings yet
Oracle SQL Functions
5 pages
Lab 4 Updated
No ratings yet
Lab 4 Updated
8 pages
Oracle SQL FAQ: Topics
No ratings yet
Oracle SQL FAQ: Topics
10 pages
SQL Interview Questions and Answers: What Is SQL and Where Does It Come From?
No ratings yet
SQL Interview Questions and Answers: What Is SQL and Where Does It Come From?
9 pages
Oracle SQL FAQ: What Is SQL and Where Does It Come From?
No ratings yet
Oracle SQL FAQ: What Is SQL and Where Does It Come From?
8 pages
Decode
100% (1)
Decode
9 pages
Oracle SQL FAQ Topics: DDL Is Data Definition Language Statements. Some Examples
No ratings yet
Oracle SQL FAQ Topics: DDL Is Data Definition Language Statements. Some Examples
7 pages
Oracle Basic Functions Tutorial
No ratings yet
Oracle Basic Functions Tutorial
7 pages
Oracle SQL Faq: Neosoft Technologies
No ratings yet
Oracle SQL Faq: Neosoft Technologies
10 pages
The Top 20 Design Tips: For Mysql Enterprise Data Architects
No ratings yet
The Top 20 Design Tips: For Mysql Enterprise Data Architects
37 pages
DB2-PPT-3-SQL DDL V1.0
No ratings yet
DB2-PPT-3-SQL DDL V1.0
48 pages
Collection of SQL Queries With Answers
No ratings yet
Collection of SQL Queries With Answers
132 pages
Indexing in DBMS
No ratings yet
Indexing in DBMS
5 pages
SQL For Dbas: Developers and Users Will Learn Something As Well!
No ratings yet
SQL For Dbas: Developers and Users Will Learn Something As Well!
61 pages
Class01 DDL DML TCL
No ratings yet
Class01 DDL DML TCL
10 pages
Functtion in Base Sas and Proc SQL
No ratings yet
Functtion in Base Sas and Proc SQL
12 pages
SQLX
No ratings yet
SQLX
22 pages
Using DDL Statements
No ratings yet
Using DDL Statements
8 pages
B210 Sizing
100% (1)
B210 Sizing
51 pages
Syntax: Example
No ratings yet
Syntax: Example
12 pages
Oracle SQL Cheatsheet
No ratings yet
Oracle SQL Cheatsheet
2 pages
SQL_Fns
No ratings yet
SQL_Fns
11 pages
Oracle SQL FAQ: What Is SQL and Where Does It Come From?
No ratings yet
Oracle SQL FAQ: What Is SQL and Where Does It Come From?
8 pages
SQL1
0% (2)
SQL1
20 pages
DDL Is Data Definition Language Statements. Some Examples
No ratings yet
DDL Is Data Definition Language Statements. Some Examples
34 pages
Day 16 Scalar Functions
No ratings yet
Day 16 Scalar Functions
17 pages
SQL Server 2005
No ratings yet
SQL Server 2005
80 pages
The Top 20 Design Tips For MySQL Enterprise Data Architects
91% (11)
The Top 20 Design Tips For MySQL Enterprise Data Architects
38 pages
S - UNIT VII Indexing in Database
No ratings yet
S - UNIT VII Indexing in Database
9 pages
Dbms Module-3 Csbs
No ratings yet
Dbms Module-3 Csbs
65 pages
2019-Cpe-27 DBMS Lab Manual 10
No ratings yet
2019-Cpe-27 DBMS Lab Manual 10
5 pages
Oracle Tips and Tricks
No ratings yet
Oracle Tips and Tricks
28 pages
Dbms Mod3
No ratings yet
Dbms Mod3
54 pages
Steven Feuerstein - Programming With Collections
No ratings yet
Steven Feuerstein - Programming With Collections
47 pages
03 Exp3 Abhishek
No ratings yet
03 Exp3 Abhishek
4 pages
The Essential R Reference
From Everand
The Essential R Reference
Mark Gardener
No ratings yet
Profound Python Data Science
From Everand
Profound Python Data Science
Onder Teker
No ratings yet
Introduction to PHP, Part 2, Second Edition
From Everand
Introduction to PHP, Part 2, Second Edition
Adam Majczak
No ratings yet
Basic DBA Query v.1: Oracle Database
From Everand
Basic DBA Query v.1: Oracle Database
Oraclesql-plsql
5/5 (1)
Sql Plsql Oracle
From Everand
Sql Plsql Oracle
Andrew Igla
No ratings yet
Cassandra Query Language by Examples - Puzzles with Answers
From Everand
Cassandra Query Language by Examples - Puzzles with Answers
Cristian Scutaru
No ratings yet
SQL Server: Tips and Tricks - 1
From Everand
SQL Server: Tips and Tricks - 1
Priyanka Agarwal
5/5 (1)
Computer Engineering Laboratory Solution Primer
From Everand
Computer Engineering Laboratory Solution Primer
Karan Bhandari
No ratings yet
Profound Linux For Developers
From Everand
Profound Linux For Developers
Onder Teker
No ratings yet
Rust Package 100 Knocks: One-Hour Mastery Series 2024 Edition
From Everand
Rust Package 100 Knocks: One-Hour Mastery Series 2024 Edition
Kanto
No ratings yet
DBMS Lab Manual
From Everand
DBMS Lab Manual
Jitendra Patel
1.5/5 (3)
Advanced C Concepts and Programming: First Edition
From Everand
Advanced C Concepts and Programming: First Edition
Gayatri
3/5 (1)
SQL Server: Tips and Tricks - 2
From Everand
SQL Server: Tips and Tricks - 2
Priyanka Agarwal
4.5/5 (3)
Advanced SAS Interview Questions You'll Most Likely Be Asked
From Everand
Advanced SAS Interview Questions You'll Most Likely Be Asked
Vibrant Publishers
No ratings yet
Update Actions
No ratings yet
Update Actions
3 pages
Cbse 12 CS SP1
No ratings yet
Cbse 12 CS SP1
11 pages
Jawaban Final Basis Data 1-60
No ratings yet
Jawaban Final Basis Data 1-60
5 pages
DBMS Lab File
No ratings yet
DBMS Lab File
9 pages
Sant Gadge Baba Amravati University Gazette - 2021 - Part Two - 517
No ratings yet
Sant Gadge Baba Amravati University Gazette - 2021 - Part Two - 517
19 pages
DBMS 3a(employee, department, location)
No ratings yet
DBMS 3a(employee, department, location)
6 pages
Programming Skill Notes
No ratings yet
Programming Skill Notes
13 pages
Optimizing SQL Query Processing: Patient 1, 0 0 0, 0 0 0
No ratings yet
Optimizing SQL Query Processing: Patient 1, 0 0 0, 0 0 0
6 pages
SQL Notes
No ratings yet
SQL Notes
21 pages
Oracle Joins
No ratings yet
Oracle Joins
7 pages
Khanna Class Notes
No ratings yet
Khanna Class Notes
196 pages
OracleAPEX Interior
100% (1)
OracleAPEX Interior
246 pages
PL-SQL - Part2: Exception Handling Procedure Function Cursors Trigger
No ratings yet
PL-SQL - Part2: Exception Handling Procedure Function Cursors Trigger
80 pages
Triggers in SQL
No ratings yet
Triggers in SQL
13 pages
Data Mapper Guide-1.6.2
No ratings yet
Data Mapper Guide-1.6.2
108 pages
1V Sem - Database Management System
No ratings yet
1V Sem - Database Management System
29 pages
Oracle WIP Jobs Pending Resource Transactions, Pending Transactions in WIP - COST - TXN - INTERFACE Table - OracleAppsDNA
No ratings yet
Oracle WIP Jobs Pending Resource Transactions, Pending Transactions in WIP - COST - TXN - INTERFACE Table - OracleAppsDNA
7 pages
Mini Paper
No ratings yet
Mini Paper
9 pages
Oracle Tutorial
100% (1)
Oracle Tutorial
8 pages
SQL Server Stored Procedures
No ratings yet
SQL Server Stored Procedures
10 pages
SQL Statements
No ratings yet
SQL Statements
9 pages
SAS Vs SQL
No ratings yet
SAS Vs SQL
52 pages
Complete Practical File of Class XII-IP 2020-21
No ratings yet
Complete Practical File of Class XII-IP 2020-21
80 pages
Creating A Virtual Table
No ratings yet
Creating A Virtual Table
2 pages
Report Definition Rule Form
No ratings yet
Report Definition Rule Form
5 pages
Oracle Business Intelligence Publisher Performance Recommendations For Fusion Cloud Applications BIP PSR Technote V1-2
No ratings yet
Oracle Business Intelligence Publisher Performance Recommendations For Fusion Cloud Applications BIP PSR Technote V1-2
49 pages
Insurance Domain Project PPT2
No ratings yet
Insurance Domain Project PPT2
14 pages
Complete Download Oracle Performance Survival Guide A Systematic Approach to Database Optimization 1st Edition Guy Harrison PDF All Chapters
100% (9)
Complete Download Oracle Performance Survival Guide A Systematic Approach to Database Optimization 1st Edition Guy Harrison PDF All Chapters
50 pages
SQL Server Stored Procedures For Beginners
No ratings yet
SQL Server Stored Procedures For Beginners
13 pages