General SQL Scripts
Sample SQL Matrix
rem -----------------------------------------------------------------------
rem UPDATED VERSION
rem Filename: matrix.sql
rem Purpose: Example of a CROSS MATRIX report implemented using
rem standard SQL.
rem Date: 12-Feb-2000
rem Author: Frank Naude, Oracle FAQ
rem
rem Updated By Mahesh Pednekar. (bunty609@hotmail.com)
rem Description Removed the Main query because the sub query itself
rem will full fill the requirement.
rem -----------------------------------------------------------------------
SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job
/
-- Sample output:
--
-- JOB DEPT10 DEPT20 DEPT30 DEPT40
-- --------- ---------- ---------- ---------- ----------
-- ANALYST 6000
-- CLERK 1300 1900 950
-- MANAGER 2450 2975 2850
-- PRESIDENT 5000
-- SALESMAN 5600
rem -----------------------------------------------------------------------
rem Filename: oerr.sql
rem Purpose: Lookup Oracle error messages. Similar to unix "oerr" command.
rem This script is handy on platforms like NT with no OERR support
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on
set veri off feed off
prompt Lookup Oracle error messages:
prompt
prompt Please enter error numbers as negatives. E.g. -1
prompt
exec dbms_output.put_line('==> '||sqlerrm( &errno ) );
set veri on feed on
undef errno
rem -----------------------------------------------------------------------
rem Filename: ver.sql
rem Purpose: Show database version with options intalled
rem (handy for your HELP/ABOUT menu)
rem Date: 12-Nov-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set head off feed off pages 0 serveroutput on
col banner format a72 wrap
select banner
from sys.v_$version;
select ' With the '||parameter||' option'
from sys.v_$option
where value = 'TRUE';
select ' The '||parameter||' option is not installed'
from sys.v_$option
where value <> 'TRUE';
begin
dbms_output.put_line('Port String: '||dbms_utility.port_string);
end;
/
set head on feed on
rem -----------------------------------------------------------------------
rem Filename: whoami.sql
rem Purpose: Reports information about your current database context
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set termout off
store set store rep
set head off
set pause off
set termout on
select 'User: '|| user || ' on database ' || global_name,
' (term='||USERENV('TERMINAL')||
', audsid='||USERENV('SESSIONID')||')' as MYCONTEXT
from global_name;
@store
set termout on
rem -----------------------------------------------------------------------
rem Filename: maxvalue.sql
rem Purpose: Select the Nth highest value from a table
rem Date: 18-Apr-2001
rem Author: Deepak Rai, SSE, Satyam Computer Services Ltd. India
rem -----------------------------------------------------------------------
select level, max('col_name') from my_table
where level = '&n'
connect by prior ('col_name') > 'col_name')
group by level;
-- Example :
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second highest salary:
--
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level
rem -----------------------------------------------------------------------
rem Filename: minvalue.sql
rem Purpose: Select the Nth lowest value from a table
rem Date: 18-Apr-2001
rem Author: Deepak Rai, SSE, Satyam Computer Services Ltd. India
rem -----------------------------------------------------------------------
select level, min('col_name') from my_table
where level = '&n'
connect by prior ('col_name') < 'col_name')
group by level;
-- Example:
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second lowest salary:
--
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level
rem -----------------------------------------------------------------------
rem Filename: default.sql
rem Purpose: Example script to demonstrate DEFAULT column values
rem Date: 25-Apr-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
-- drop table x
-- /
create table x (a char, b number default 99999, c date, d varchar2(6))
/
alter table x modify (c date default sysdate)
/
insert into x(a, d) values ('a', 'qwerty')
/
select * from x
/
--
-- Expected output:
--
-- A B C D
-- - ---------- ----------- ------
-- a 99999 25-APR-2001 qwerty
rem -----------------------------------------------------------------------
rem Filename: comments.sql
rem Purpose: Display table and column comments for the current schema
rem Handy for getting to know the database schema
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set pages 50000
set null 'No Comments'
tti 'Table Comments'
col comments format a29 wrap word
select * from user_tab_comments;
tti 'Column Comments'
col comments format a18 wrap word
break on table_name skip 1
select * from user_col_comments;
clear break
set null ''
set pages 23
rem -----------------------------------------------------------------------
rem Filename: appinfo.sql
rem Purpose: Example of how to pass application info through to Oracle RDBMS
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
-- The following code tells the database what the application is up to:
begin
dbms_application_info.set_client_info('BANCS application info');
dbms_application_info.set_module('BANCS XYZ module', 'BANCS action name');
end;
/
-- Retrieve application info from the database:
select module, action, client_info
from sys.v_$session where audsid = USERENV('SESSIONID')
/
select sql_text
from sys.v_$sqlarea
where module = 'BANCS XYZ module'
and action = 'BANCS action name'
/
rem -----------------------------------------------------------------------
rem Filename: help.sql
rem Purpose: Access the SQL*Plus Help table
rem Notes: If the HELP table doesn't exist, see the SQL*Plus FAQ for
rem installation instructions.
rem Date: 05-July-98
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
select info
from system.help
where upper(topic)=upper('&1')
/
rem -----------------------------------------------------------------------
rem Filename: leapyear.sql
rem Purpose: Check if a year is a leap year
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
select year,
decode( mod(year, 4), 0,
decode( mod(year, 400), 0, 'Leap Year',
decode( mod(year, 100), 0, 'Not a Leap Year', 'Leap Year')
), 'Not a Leap Year'
) as leap_year_indicator
from my_table
/
rem -----------------------------------------------------------------------
rem Filename: spellout.sql
rem Purpose: This script will spell out numbers to words (handy for
rem cheque printing). Example '10' --> Ten
rem Date: 12-Sep-2000
rem Author: Anonymous
rem -----------------------------------------------------------------------
select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/
rem -----------------------------------------------------------------------
rem Filename: encode.sql
rem Purpose: Demonstrate simple encoding and decoding of secret messages
rem This method can be extended to create simple password
rem encryption
rem Date: 15-Feb-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
SELECT TRANSLATE(
'HELLO WORLD', -- Message to encode
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'1234567890!@#$%^&*()-=_+;,.') ENCODED_MESSAGE
FROM DUAL
/
SELECT TRANSLATE(
'85@@%._%*@4', -- Message to decode
'1234567890!@#$%^&*()-=_+;,.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') DECODED_MESSAGE
FROM DUAL
/
rem -----------------------------------------------------------------------
rem Filename: countall.sql
rem Purpose: Count the number of rows for ALL tables in the current schema
rem Date: 30-Apr-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set termout off echo off feed off trimspool on head off pages 0
spool countall.tmp
select 'SELECT count(*), '''||table_name||''' from '||table_name||';'
from user_tables
/
spool off
set termout on
@@countall.tmp
set head on feed on
rem -----------------------------------------------------------------------
rem Filename: objopt.sql
rem Purpose: Demonstrate Oracle database types and object tables
rem Date: 12-Feb-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
drop type employee_typ;
create type employee_typ as object (
empno NUMBER,
emp_name varchar2(30),
hiredate date,
member function days_at_company return NUMBER,
pragma restrict_references(days_at_company, WNDS)
)
/
create type body employee_tye is
begin
member function days_at_company return number is
begin
return (SYSDATE-hiredate);
end;
end;
/
show errors
drop type department_typ;
create type department_typ as object (
deptno NUMBER(5),
manager ref employee_typ
)
/
rem -----------------------------------------------------------------------
rem Filename: varray.sql
rem Purpose: Demontrate VARRAY (variable array in one database column)
rem collection types
rem Date: 12-Aug-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
CREATE OR REPLACE TYPE vcarray AS VARRAY(10) OF VARCHAR2(128);
/
CREATE TABLE varray_table (id number, col1 vcarray);
INSERT INTO varray_table VALUES (1, vcarray('A'));
INSERT INTO varray_table VALUES (2, vcarray('B', 'C'));
INSERT INTO varray_table VALUES (3, vcarray('D', 'E', 'F'));
SELECT * FROM varray_table;
SELECT * FROM USER_VARRAYS;
-- SELECT * FROM USER_SEGMENTS;
-- Unnesting the collection:
select t1.id, t2.COLUMN_VALUE
from varray_table t1, TABLE(t1.col1) t2
/
-- Use PL/SQL to access the varray...
set serveroutput on
declare
v_vcarray vcarray;
begin
for c1 in (select * from varray_table) loop
dbms_output.put_line('Row fetched...');
FOR i IN c1.col1.FIRST..c1.col1.LAST LOOP
dbms_output.put_line('...property fetched: '|| c1.col1(i));
END LOOP;
end loop;
end;
/
-- Clean-up...
DROP TABLE varray_table;
DROP TYPE vcarray;
rem -----------------------------------------------------------------------
rem Filename: temptab.sql
rem Purpose: Demonstrate Oracle 8i temporary tables
rem Date: 23-Apr-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
drop table x
/
create global temporary table x (a date)
on commit delete rows -- Delete rows after commit
-- on commit preserve rows -- Delete rows after exit session
/
select table_name, temporary, duration
from user_tables
where table_name = 'X'
/
insert into x values (sysdate);
select * from x;
commit;
-- Inserted rows are missing after commit
select * from x;
rem -----------------------------------------------------------------------
rem Filename: conv2lob.sql
rem Purpose: Convert LONG datatypes to LOBs (Large Objects)
rem Dependancies: Oracle 8.1.x or higher
rem Date: 17-Sep-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
create table old_long_table(c1 number, c2 long);
insert into old_long_table values (1, 'LONG data to convert to CLOB');
create table new_lob_table(c1 number, c2 clob);
-- Use TO_LOB function to convert LONG to LOB...
insert into new_lob_table
select c1, to_lob(c2) from old_long_table;
-- Note: the same procdure can be used to convert LONG RAW datatypes to BLOBs.
rem -----------------------------------------------------------------------
rem Purpose: Delete duplicate values from a table
rem Date: 04-Mar-2005
rem Notes: Verify that the correct rows are deleted before you COMMIT!
rem Author: Dharmendra Srivastava,Associate,
rem MindTree Consulting Pvt Ltd. India
rem -----------------------------------------------------------------------
DELETE FROM my_table
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM my_table
GROUP BY delete_col_name);
-- Example :
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- To delete the duplicate values:
--
-- DELETE FROM emp
-- WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id);
--
-- COMMIT;
General PL/SQL: Scripts
em -----------------------------------------------------------------------
rem Filename: plsloop.sql
rem Purpose: Example: UPDATE/DELETE in a loop and commit very X records
rem Handy for huge tables that cause rollback segment problems
rem DON'T ISSUE COMMIT TOO FREQUENTLY!
rem Date: 09-Apr-1999; Updated: 25-Nov-2004
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
declare
i number := 0;
cursor s1 is SELECT rowid, t.* FROM tab1 t WHERE col1 = 'value1';
begin
for c1 in s1 loop
Update tab1 set col1 = 'value2'
Where rowid = c1.rowid;
i := i + 1; -- Commit after every X records
if i > 10000 then
commit;
i := 0;
end if;
end loop;
commit;
end;
/
-- Note: More advanced users can use the mod() function to commit every N rows.
-- No counter variable required:
--
-- if mod(i, 10000)
-- commit;
-- dbms_output.put_line('Commit issued for rows up to: '||c1%rowcount);
-- end if;
--
rem -----------------------------------------------------------------------
rem Filename: bulkbind.sql
rem Purpose: Simple program to demonstrate BULK COLLECT and BULK BIND.
rem Notes: Bulk operations on ROWTYPE only work from and above.
rem Date: 12-Feb-2004
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on size 50000
DECLARE
CURSOR emp_cur IS SELECT * FROM EMP;
TYPE emp_tab_t IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tab emp_tab_t; -- In-memory table
rows NATURAL := 10000; -- Number of rows to process at a time
i BINARY_INTEGER := 0;
BEGIN
OPEN emp_cur;
LOOP
-- Bulk collect data into memory table - X rows at a time
FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;
EXIT WHEN emp_tab.COUNT = 0;
DBMS_OUTPUT.PUT_LINE( TO_CHAR(emp_tab.COUNT)|| ' rows bulk fetched.');
FOR i IN emp_tab.FIRST .. emp_tab.LAST loop
-- Manipumate data in the memory table...
dbms_output.put_line('i = '||i||', EmpName='||emp_tab(i).ename);
END LOOP;
-- Bulk bind of data in memory table...
FORALL i in emp_tab.FIRST..emp_tab.LAST
INSERT /*+APPEND*/ INTO emp2 VALUES emp_tab(i);
END LOOP;
CLOSE emp_cur;
END;
/
rem -----------------------------------------------------------------------
rem Filename: profiler.sql
rem Purpose: Profile PL/SQL code to get run-time statistics.
rem Shows execution time for each PL/SQL line. This data can
rem be used to improve performance.
rem Date: 02-Mar-2004
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
conn / as sysdba
-- Install the profiler...
@?/rdbms/admin/proftab
@?/rdbms/admin/profload
@?/plsql/demo/profrep.sql
-- Create a test procedure to time...
CREATE OR REPLACE PROCEDURE proc1 IS
v_dummy CHAR;
BEGIN
FOR i IN 1..100 LOOP
SELECT dummy INTO v_dummy FROM dual;
END LOOP;
END;
/
SHOW ERRORS
-- Do the profilling and print the report...
set line 5000 serveroutput on size 1000000
DECLARE
v_run NUMBER;
BEGIN
DBMS_PROFILER.START_PROFILER('test','test1',v_run);
proc1;
DBMS_PROFILER.STOP_PROFILER;
DBMS_PROFILER.ROLLUP_RUN(v_run);
PROF_REPORT_UTILITIES.PRINT_RUN(v_run);
END;
/
rem -----------------------------------------------------------------------
rem Filename: plstable.sql
rem Purpose: Example: how to populate a PL/SQL Table from a cursor
rem Date: 09-Apr-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on
declare
-- Declare the PL/SQL table
type deptarr is table of dept%rowtype
index by binary_integer;
d_arr deptarr;
-- Declare cursor
type d_cur is ref cursor return dept%rowtype;
c1 d_cur;
i number := 1;
begin
-- Populate the PL/SQL table from the cursor
open c1 for select * from dept;
loop
exit when c1%NOTFOUND;
fetch c1 into d_arr(i);
i := i+1;
end loop;
close c1;
-- Display the entire PL/SQL table on screen
for i in 1..d_arr.last loop
dbms_output.put_line('DEPTNO : '||d_arr(i).deptno );
dbms_output.put_line('DNAME : '||d_arr(i).dname );
dbms_output.put_line('LOC : '||d_arr(i).loc );
dbms_output.put_line('---------------------------');
end loop;
end;
/
rem -----------------------------------------------------------------------
rem Filename: password.sql
rem Purpose: Simple password encryption package to demonstrate how
rem values can be encrypted and decrypted using Oracle's
rem DBMS Obfuscation Toolkit
rem Note: Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql
rem Date: 18-Mar-2003
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
CREATE OR REPLACE PACKAGE PASSWORD AS
function encrypt(i_password varchar2) return varchar2;
function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors
CREATE OR REPLACE PACKAGE BODY PASSWORD AS
-- key must be exactly 8 bytes long
c_encrypt_key varchar2(8) := 'key45678';
function encrypt (i_password varchar2) return varchar2 is
v_encrypted_val varchar2(38);
v_data varchar2(38);
begin
-- Input data must have a length divisible by eight
v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
input_string => v_data,
key_string => c_encrypt_key,
encrypted_string => v_encrypted_val);
return v_encrypted_val;
end encrypt;
function decrypt (i_password varchar2) return varchar2 is
v_decrypted_val varchar2(38);
begin
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
input_string => i_password,
key_string => c_encrypt_key,
decrypted_string => v_decrypted_val);
return v_decrypted_val;
end decrypt;
end PASSWORD;
/
show errors
-- Test if it is working...
select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;
rem -----------------------------------------------------------------------
rem Filename: refcurs.sql
rem Purpose: Pass result sets (REF CURSOR) between procedures and
rem functions
rem Date: 15-Jun-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on
-- Define TYPES package separately to be available to all programming
-- environments...
CREATE OR REPLACE PACKAGE types AS
TYPE cursortyp is REF CURSOR; -- use weak form
END;
/
-- Create test package to demonstrate passing result sets...
CREATE OR REPLACE PACKAGE test_ref_cursor AS
PROCEDURE main;
FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp;
PROCEDURE process_cursor(cur types.cursortyp);
END;
/
show errors
CREATE OR REPLACE PACKAGE BODY test_ref_cursor AS
-- Main program entry point
PROCEDURE main IS
BEGIN
process_cursor( get_cursor_ref(1) );
process_cursor( get_cursor_ref(2) );
END;
-- Get and return a CURSOR REF/ Result Set
FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp IS
cur types.cursortyp;
BEGIN
if typ = 1 THEN
OPEN cur FOR SELECT * FROM emp WHERE ROWNUM < 5;
ELSE
OPEN cur FOR SELECT * FROM dept WHERE ROWNUM < 5;
END IF;
RETURN cur;
END;
-- Process rows for an EMP or DEPT cursor
PROCEDURE process_cursor(cur types.cursortyp) IS
empRec emp%ROWTYPE;
deptRec dept%ROWTYPE;
BEGIN
LOOP
FETCH cur INTO empRec; -- Maybe it was an EMP cursor, try to fetch...
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line('EMP ROW: '||empRec.ename);
END LOOP;
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN -- OK, so it was't EMP, let's try DEPT.
LOOP
FETCH cur INTO deptRec;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line('DEPT ROW: '||deptRec.dname);
END LOOP;
END;
END;
/
show errors
EXEC test_ref_cursor.main;
rem -----------------------------------------------------------------------
rem Filename: dbms_numsystem.sql
rem Purpose: Package with functions to convert numbers between the
rem Decimal, Binary, Octal and Hexidecimal numbering systems.
rem Usage: See sampels at the bottom of this file
rem Author: Frank Naude, 17 February 2003
rem -----------------------------------------------------------------------
set serveroutput on
CREATE OR REPLACE PACKAGE dbms_numsystem AS
function bin2dec (binval in char ) RETURN number;
function dec2bin (N in number) RETURN varchar2;
function oct2dec (octval in char ) RETURN number;
function dec2oct (N in number) RETURN varchar2;
function hex2dec (hexval in char ) RETURN number;
function dec2hex (N in number) RETURN varchar2;
END dbms_numsystem;
/
show errors
CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS
FUNCTION bin2dec (binval in char) RETURN number IS
i number;
digits number;
result number := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(binval);
for i in 1..digits loop
current_digit := SUBSTR(binval, i, 1);
current_digit_dec := to_number(current_digit);
result := (result * 2) + current_digit_dec;
end loop;
return result;
END bin2dec;
FUNCTION dec2bin (N in number) RETURN varchar2 IS
binval varchar2(64);
N2 number := N;
BEGIN
while ( N2 > 0 ) loop
binval := mod(N2, 2) || binval;
N2 := trunc( N2 / 2 );
end loop;
return binval;
END dec2bin;
FUNCTION oct2dec (octval in char) RETURN number IS
i number;
digits number;
result number := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(octval);
for i in 1..digits loop
current_digit := SUBSTR(octval, i, 1);
current_digit_dec := to_number(current_digit);
result := (result * 8) + current_digit_dec;
end loop;
return result;
END oct2dec;
FUNCTION dec2oct (N in number) RETURN varchar2 IS
octval varchar2(64);
N2 number := N;
BEGIN
while ( N2 > 0 ) loop
octval := mod(N2, 8) || octval;
N2 := trunc( N2 / 8 );
end loop;
return octval;
END dec2oct;
FUNCTION hex2dec (hexval in char) RETURN number IS
i number;
digits number;
result number := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(hexval);
for i in 1..digits loop
current_digit := SUBSTR(hexval, i, 1);
if current_digit in ('A','B','C','D','E','F') then
current_digit_dec := ascii(current_digit) - ascii('A') + 10;
else
current_digit_dec := to_number(current_digit);
end if;
result := (result * 16) + current_digit_dec;
end loop;
return result;
END hex2dec;
FUNCTION dec2hex (N in number) RETURN varchar2 IS
hexval varchar2(64);
N2 number := N;
digit number;
hexdigit char;
BEGIN
while ( N2 > 0 ) loop
digit := mod(N2, 16);
if digit > 9 then
hexdigit := chr(ascii('A') + digit - 10);
else
hexdigit := to_char(digit);
end if;
hexval := hexdigit || hexval;
N2 := trunc( N2 / 16 );
end loop;
return hexval;
END dec2hex;
END dbms_numsystem;
/
show errors
-- Examples:
select dbms_numsystem.dec2bin(22) from dual;
select dbms_numsystem.bin2dec('10110') from dual;
select dbms_numsystem.dec2oct(44978) from dual;
select dbms_numsystem.oct2dec(127662) from dual;
select dbms_numsystem.dec2hex(44978) from dual;
select dbms_numsystem.hex2dec('AFB2') from dual;
/*
------------------------------------------------------------------------------
Filename: random.txt
Purpose: Random number/ string generator package
Author: Unknown
Original: http://orafaq.net/scripts/sql/random.txt
Edits:
19990908 Phil Rand
------------------------------------------------------------------------------
*/
create or replace package random
is
procedure srand(new_seed in number);
procedure get_rand(r OUT number);
procedure get_rand_max(r OUT number, n IN number);
function rand return number;
function rand_max(n IN number) return number;
function rand_string(ssiz IN number) return varchar2;
function smaller(x IN number, y IN number) return number;
pragma restrict_references(rand, WNDS);
pragma restrict_references(rand_max, WNDS);
pragma restrict_references(random, WNDS, RNPS);
pragma restrict_references(rand_string, WNDS);
pragma restrict_references(smaller, WNDS);
end random;
/
create or replace package body random
is
multiplier constant number := 22695477;
increment constant number := 1;
"2^32" constant number := 2 ** 32;
"2^16" constant number := 2 ** 16;
"0x7fff" constant number := 32767;
Seed number := 1;
function smaller(x IN number, y IN number) return number is
begin
if x <= y then
return x;
else
return y;
end if;
end smaller;
function rand_string(ssiz IN number) return varchar2 is
i number;
m number;
c char;
result varchar2(2000) := '';
begin
m := smaller(ssiz,2000);
for i in 1..m loop
c := substr('abcdefghijklmnopqrstuvwxyz0123456789',rand_max(36),1);
result := result || c;
end loop;
return result;
end rand_string;
procedure srand(new_seed in number) is
begin
Seed := new_seed;
end srand;
function rand return number is
begin
Seed := mod(multiplier * Seed + increment, "2^32");
return bitand(Seed/"2^16", "0x7fff");
end rand;
procedure get_rand(r OUT number) is
begin
r := rand;
end get_rand;
function rand_max(n IN number) return number is
begin
return mod(rand, n) + 1;
end rand_max;
procedure get_rand_max(r OUT number, n IN number) is
begin
r := rand_max(n);
end get_rand_max;
begin
select userenv('SESSIONID')
into Seed
from dual;
end random;
/
-- Some examples:
select random.rand_max(10) from dual;
select random.rand_max(10) from dual;
select random.rand_string(20) from dual;
select random.rand_string(20) from dual;
rem -----------------------------------------------------------------------
rem Filename: leapyear.sql
rem Purpose: Check if a year is a leap year
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION isLeapYear(i_year NUMBER) RETURN boolean AS
BEGIN
-- A year is a leap year if it is evenly divisible by 4
-- but not if it's evenly divisible by 100
-- unless it's also evenly divisible by 400
IF mod(i_year, 400) = 0 OR ( mod(i_year, 4) = 0 AND mod(i_year, 100) != 0) THEN
return TRUE;
ELSE
return FALSE;
END IF;
END;
/
show errors
-- Let's test it
SET SERVEROUTPUT ON
BEGIN
IF isLeapYear(2004) THEN
dbms_output.put_line('Yes, it is a leap year');
ELSE
dbms_output.put_line('No, it is not a leap year');
END IF;
END;
/
rem -----------------------------------------------------------------------
rem Filename: asciitab.sql
rem Purpose: Print ASCII table
rem Date: 13-Jun-2000
rem Author: Anonymous
rem -----------------------------------------------------------------------
set serveroutput on size 10240
declare
i number;
j number;
k number;
begin
for i in 2..15 loop
for j in 1..16 loop
k:=i*16+j;
dbms_output.put((to_char(k,'000'))||':'||chr(k)||' ');
if k mod 8 = 0 then
dbms_output.put_line('');
end if;
end loop;
end loop;
end;
/
show errors
rem -----------------------------------------------------------------------
rem Filename: recurse.sql
rem Purpose: Script to demonstrate how recursive algorithms like
rem Fibonacci and Factorials can be implemented in PL/SQL
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
-- Computing the Factorial of a number (n!)
CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER IS
BEGIN
IF n = 1 THEN -- terminating condition
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- recursive call
END IF;
END fac;
/
-- Test n!
SELECT fac(1), fac(2), fac(3), fac(4), fac(5) FROM dual;
-- Sample output:
-- FAC(1) FAC(2) FAC(3) FAC(4) FAC(5)
-- ---------- ---------- ---------- ---------- ----------
-- 1 2 6 24 120
-- Computing the Nth Fibonacci number
CREATE OR REPLACE FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
IF (n = 1) OR (n = 2) THEN -- terminating condition
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2); -- recursive call
END IF;
END fib;
/
-- Test Fibonacci Series:
SELECT fib(1), fib(2), fib(3), fib(4), fib(5) FROM dual;
-- Sample output:
-- FIB(1) FIB(2) FIB(3) FIB(4) FIB(5)
-- ---------- ---------- ---------- ---------- ----------
-- 1 1 2 3 5
--
rem -----------------------------------------------------------------------
rem Filename: readlob.sql
rem Purpose: Fetch LOB column values piece-wise from PL/SQL
rem Date: 12-Jun-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on
DROP TABLE lob_table; -- Create table to hols LOBs
CREATE TABLE lob_table (
id INTEGER,
b_lob BLOB,
c_lob CLOB,
b_file BFILE );
INSERT INTO lob_table -- Create sample record
VALUES (1, EMPTY_BLOB(), 'abcde', NULL);
DECLARE
clob_locator CLOB;
charbuf VARCHAR2(20);
read_offset INTEGER;
read_amount INTEGER;
BEGIN
-- First we need to get the lob locator
SELECT c_lob INTO clob_locator FROM lob_table WHERE id = 1;
DBMS_OUTPUT.PUT_LINE('CLOB Size: ' ||
DBMS_LOB.GETLENGTH(clob_locator));
-- Read LOB field contents
read_offset := 1;
read_amount := 20;
dbms_lob.read(clob_locator, read_amount, read_offset, charbuf);
dbms_output.put_line('CLOB Value: ' || charbuf);
END;
/
rem -----------------------------------------------------------------------
rem Filename: loadlob.sql
rem Purpose: Load a binary file (images, documents, etc) into a
rem database table.
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on
DROP TABLE lob_table;
DROP SEQUENCE lob_seq;
CREATE OR REPLACE DIRECTORY my_dir AS '/app/oracle/';
CREATE TABLE lob_table (id NUMBER, fil BLOB);
CREATE SEQUENCE lob_seq;
CREATE OR REPLACE PROCEDURE load_file(p_file VARCHAR2)
IS
src_lob BFILE := BFILENAME('MY_DIR', p_file);
dest_lob BLOB;
BEGIN
INSERT INTO lob_table VALUES(lob_seq.nextval, EMPTY_BLOB())
RETURNING fil INTO dest_lob;
DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
SRC_LOB => src_lob,
AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );
DBMS_LOB.CLOSE(src_lob);
COMMIT;
END;
/
show errors
-- Let's test it
exec load_file('pic1.gif');
SELECT id, DBMS_LOB.GETLENGTH(fil) AS bytes_loaded
FROM lob_table;
rem -----------------------------------------------------------------------
rem Filename: savelob.sql
rem Purpose: Save a binary file (images, documents, etc) from database
rem to a flat file.
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE save_file(p_id NUMBER, p_file VARCHAR2)
IS
v_lob_loc BLOB;
v_lob_len NUMBER;
v_buffer RAW(32767);
v_buffer_size BINARY_INTEGER := 32767;
v_offset NUMBER := 1;
v_out_file UTL_FILE.FILE_TYPE;
BEGIN
SELECT fil INTO v_lob_loc FROM lob_table WHERE id = p_id;
v_lob_len := DBMS_LOB.GETLENGTH(v_lob_loc);
DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);
v_out_file := UTL_FILE.FOPEN(location => 'MY_DIR',
filename => p_file,
open_mode => 'w',
max_linesize => 32767);
WHILE (v_offset <= v_lob_len) LOOP
dbms_output.put_line('v_start : ' || to_char(v_offset));
DBMS_LOB.READ(lob_loc => v_lob_loc,
amount => v_buffer_size,
offset => v_offset,
buffer => v_buffer);
v_offset := v_offset + v_buffer_size;
UTL_FILE.PUT_RAW(file => v_out_file,
buffer => v_buffer);
END LOOP;
UTL_FILE.FCLOSE(v_out_file);
DBMS_LOB.CLOSE(v_lob_loc);
END;
/
show errors
-- Let's test it
exec save_file(1, 'pic2.gif');
! ls -l /app/oracle/pic*.gif
rem -----------------------------------------------------------------------
rem Filename: readlong.sql
rem Purpose: Fetch Long column values piece-wise from PL/SQL
rem Date: 12-Jan-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on
-- Create test table
drop table longtable;
create table longtable (longcol long) tablespace TOOLS;
insert into longtable values ( rpad('x', 257, 'QWERTY') );
DECLARE
cur1 PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;;
rc NUMBER;
long_piece VARCHAR2(256);
piece_len INTEGER := 0;
long_tab DBMS_SQL.VARCHAR2S;
long_len INTEGER := 0;
BEGIN
DBMS_SQL.PARSE(cur1, 'select longcol from longtable', DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN_LONG(cur1, 1);
rc := DBMS_SQL.EXECUTE(cur1);
rc := DBMS_SQL.FETCH_ROWS(cur1); -- Get one row
-- Loop until all pieces of the long column are processed
LOOP
DBMS_SQL.COLUMN_VALUE_LONG(cur1, 1, 256, long_len, long_piece, piece_len);
EXIT WHEN piece_len = 0;
DBMS_OUTPUT.PUT_LINE('Long piece len='|| piece_len);
long_tab( NVL(long_tab.LAST, 0)+1 ) := long_piece; -- Add piece to table
long_len := long_len + piece_len;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cur1);
DBMS_OUTPUT.PUT_LINE('Total long col fetched, len='|| long_len);
END;
/
rem -----------------------------------------------------------------------
rem Filename: utlfile.sql
rem Purpose: Demonstrate writing to a file using the UTL_FILE package
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
CREATE DIRECTORY test_dir AS 'c:\';
-- CREATE DIRECTORY test_dir AS '/tmp';
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('test_dir', 'test_file.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file.');
END;
/
rem -----------------------------------------------------------------------
rem Filename: fileview.sql
rem Purpose: Create a database view on top of a file
rem Date: 27-Nov-2002
rem -----------------------------------------------------------------------
-- Utl_file_dir must be set to the directory you want to read from
show parameter utl_file_dir
-- Define the table's columns
CREATE OR REPLACE TYPE Alert_Row_Type AS OBJECT (
line NUMBER(8),
text VARCHAR2(2000)
);
/
-- Create a table of many row objects
CREATE OR REPLACE TYPE Alert_Type IS TABLE OF Alert_Row_Type;
/
-- Create a function to read the data into the table
CREATE OR REPLACE FUNCTION Get_Alert
RETURN Alert_Type
IS
Alert_Tab Alert_Type := Alert_Type(Alert_Row_Type(NULL, NULL));
v_file Utl_File.File_Type;
v_line NUMBER(10) := 1;
v_text VARCHAR2(2000);
b_read BOOLEAN := TRUE;
b_first BOOLEAN := TRUE;
BEGIN
dbms_output.put_line('About to open file...');
v_file := Utl_File.FOpen('/app/oracle/admin/orcl/bdump', 'alert_orcl.log', 'r');
WHILE b_read LOOP
BEGIN
Utl_File.Get_Line(v_file, v_text);
IF b_first THEN
b_first := FALSE;
ELSE
Alert_Tab.Extend;
END IF;
Alert_Tab(Alert_Tab.Last) := Alert_Row_Type(v_line, v_text);
v_line := v_line + 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
b_read := FALSE;
END;
END LOOP;
Utl_File.FClose(v_file);
RETURN Alert_Tab;
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_path');
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_mode');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.read_error');
WHEN utl_file.write_error THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.write_error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.internal_error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.other_error');
END;
/
show errors
-- Create a view to get the info from the function
CREATE OR REPLACE FORCE VIEW alert_log_file AS
SELECT LINE, TEXT
FROM Table(Cast(Get_Alert() As Alert_Type))
/
-- Test it!!!
set pages 50000
select * from alert_log_file
where text like '%ORA-%'
/
rem -----------------------------------------------------------------------
rem Filename: dynasql.sql
rem Purpose: Example PL/SQL code to demonstrate Dynamic SQL
rem Date: 25-Feb-2003
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE CREATE_TABLE1 AS
sql_stmt varchar2(4000);
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
END;
/
show errors
CREATE OR REPLACE PROCEDURE CREATE_TABLE2 AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
show errors
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
/
show errors
rem -----------------------------------------------------------------------
rem Filename: java.sql
rem Purpose: Demonstrate Java stored procedures (available from Oracle 8i)
rem Date: 13-Jun-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
conn / as sysdba
-- @?/javavm/install/initjvm.sql
grant javauserpriv to scott;
conn scott/tiger
prompt Loading java source into database...
create or replace java source named "Hello" as
public class Hello { /* Pure Java Code */
static public String Msg(String tail) {
return "Hello " + tail;
}
}
/
-- SHOW ERRORS not needed
-- Note, you can also use "loadjava" to load source files into Oracle.
prompt Publish Java to PL/SQL...
create or replace function hello (str varchar2) return varchar as
language java name 'Hello.Msg(java.lang.String) return java.lang.String';
/
show errors
prompt Call Java function...
select hello('Frank') from dual
/
rem -----------------------------------------------------------------------
rem Filename: oscmd.sql
rem Purpose: Execute operating system commands from PL/SQL
rem Notes: Specify full paths to commands, for example,
rem specify /usr/bin/ps instead of ps.
rem Date: 09-Apr-2005
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
rem -----------------------------------------------------------------------
rem Grant Java Access to user SCOTT
rem -----------------------------------------------------------------------
conn / as sysdba
EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.io.FilePermission', '/bin/sh', 'execute');
-- Other read ,write or execute permission may be requried
rem -----------------------------------------------------------------------
rem Create Java class to execute OS commands...
rem -----------------------------------------------------------------------
conn scott/tiger
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {
finalCommand = new String[4];
finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
} else { // Linux or Unix System
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}
// Execute the command...
final Process pr = Runtime.getRuntime().exec(finalCommand);
// Capture output from STDOUT...
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("stdout: " + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
} catch (IOException ioe) {
System.out.println("Error printing process output.");
ioe.printStackTrace();
} finally {
try {
br_in.close();
} catch (Exception ex) {}
}
// Capture output from STDERR...
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("stderr: " + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
} catch (IOException ioe) {
System.out.println("Error printing execution errors.");
ioe.printStackTrace();
} finally {
try {
br_err.close();
} catch (Exception ex) {}
}
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}
};
/
show errors
rem -----------------------------------------------------------------------
rem Publish the Java call to PL/SQL...
rem -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE host (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/
show errors
rem -----------------------------------------------------------------------
rem Let's test it...
rem -----------------------------------------------------------------------
CALL DBMS_JAVA.SET_OUTPUT(1000000);
SET SERVEROUTPUT ON SIZE 1000000
exec host('/usr/bin/ls');
rem -----------------------------------------------------------------------
rem Filename: ftpclient.sql
rem Purpose: PL/SQL FTP Client
rem Date: 19-Nov-2003
rem Author: Russ Johnson, Braun Consulting
rem -----------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BRNC_FTP_PKG
AS
/**
*
* PL/SQL FTP Client
*
* Created by: Russ Johnson, Braun Consulting
*
* www.braunconsult.com
*
* OVERVIEW
* --------------------
* This package uses the standard packages UTL_FILE and UTL_TCP to perform
* client-side FTP functionality (PUT and GET) for text files as defined in
* the World Wide Web Consortium's RFC 959 document - http://www.w3.org/Protocols/rfc959/
* The procedures and functions in this package allow single or multiple file transfer using
* standard TCP/IP connections.
*
* LIMITATIONS
* --------------------
* Currently the API is limited to transfer of ASCII text files only. This is
* primarily because UTL_FILE only supports text I/O, but also because the original
* design was for creating text files from data in the Oracle database, then transferring the file to a remote host.
* Furthermore, the API does not support SSH/Secure FTP or connection through a proxy server.
* Keep in mind that FTP passes the username/password combo in plain text over TCP/IP.
*
* DB versions - 8i (8.1.x) and above. 8.0.x may work if it has the SYS.UTL_TCP package.
*
*
* Note: Since UTL_FILE is used for the client-side I/O, this package is also limited to
* transfer of files that exist in directories available to UTL_FILE for read/write.
* These directories are defined by the UTL_FILE_DIR parameter in the init.ora file.
*
* USAGE
* --------------------
* Three functions are available for FTP - PUT, GET, and FTP_MULTIPLE. FTP_MULTIPLE takes
* a table of records that define the files to be transferred (filename, directory, etc.).
* That table can have 1 record or multiple records. The PUT and GET functions are included
* for convenience to FTP one file at a time. PUT and GET return true if the file is transferred
* successfully and false if it fails. FTP_MULTIPLE returns true if no batch-level errors occur
* (such as an invalid host, refused connection, or invalid login information). It also takes the
* table of file records IN and passes it back OUT. Each record contains individual error information.
*
* EXAMPLE
* --------------------
* Transfer multiple files - 1 GET and 2 PUT from a Windows machine to a host (assuming UNIX here).
* Display any errors that occur.
* DECLARE
*
* v_username VARCHAR2(40) := 'rjohnson';
* v_password VARCHAR2(40) := 'password';
* v_hostname VARCHAR2(255) := 'ftp.oracle.com';
* v_error_message VARCHAR2(1000);
* b_put BOOLEAN;
* t_files BRNC_FTP_PKG.t_ftp_rec; -- Declare our table of file records
*
* BEGIN
*
* t_files(1).localpath := 'd:\oracle\utl_file\outbound';
* t_files(1).filename := 'myfile1.txt';
* t_files(1).remotepath := '/home/oracle/text_files';
* t_files(1).transfer_mode := 'PUT';
*
* t_files(2).localpath := 'd:\oracle\utl_file\inbound';
* t_files(2).filename := 'incoming_file.xml';
* t_files(2).remotepath := '/home/oracle/xml_files';
* t_files(2).transfer_mode := 'GET';
*
* t_files(3).localpath := 'd:\oracle\utl_file\outbound';
* t_files(3).filename := 'myfile2.txt';
* t_files(3).remotepath := '/home';
* t_files(3).transfer_mode := 'PUT';
*
* b_put := BRNC_FTP_PKG.FTP_MULTIPLE(v_error_message,
* t_files,
* v_username,
* v_password,
* v_hostname);
* IF b_put = TRUE
* THEN
* FOR i IN t_files.FIRST..t_files.LAST
* LOOP
* IF t_files.EXISTS(i)
* THEN
* DBMS_OUTPUT.PUT_LINE(t_files(i).status||' | '||
* t_files(i).error_message||' | '||
* to_char(t_files(i).bytes_transmitted)||' | '||
* to_char(t_files(i).trans_start,'YYYY-MM-DD HH:MI:SS')||' | '||
* to_char(t_files(i).trans_end,'YYYY-MM-DD HH:MI:SS'));
* END IF;
* END LOOP;
* ELSE
* DBMS_OUTPUT.PUT_LINE(v_error_message);
* END IF;
*
* EXCEPTION
* WHEN OTHERS
* THEN
* DBMS_OUTPUT.PUT_LINE(SQLERRM);
* END;
*
* CREDITS
* --------------------
* The W3C's RFC 959 that describes the FTP process.
*
* http://www.w3c.org
*
* Much of the PL/SQL code in this package was based on Java code written by
* Bruce Blackshaw of Enterprise Distributed Technologies Ltd. None of that code
* was copied, but the objects and methods greatly helped my understanding of the
* FTP Client process.
*
* http://www.enterprisedt.com
*
* VERSION HISTORY
* --------------------
* 1.0 11/19/2002 Unit-tested single and multiple transfers between disparate hosts.
*
*
*/
/**
* Exceptions
*
*/
ctrl_exception EXCEPTION;
data_exception EXCEPTION;
/**
* Constants - FTP valid response codes
*
*/
CONNECT_CODE CONSTANT PLS_INTEGER := 220;
USER_CODE CONSTANT PLS_INTEGER := 331;
LOGIN_CODE CONSTANT PLS_INTEGER := 230;
PWD_CODE CONSTANT PLS_INTEGER := 257;
PASV_CODE CONSTANT PLS_INTEGER := 227;
CWD_CODE CONSTANT PLS_INTEGER := 250;
TSFR_START_CODE1 CONSTANT PLS_INTEGER := 125;
TSFR_START_CODE2 CONSTANT PLS_INTEGER := 150;
TSFR_END_CODE CONSTANT PLS_INTEGER := 226;
QUIT_CODE CONSTANT PLS_INTEGER := 221;
SYST_CODE CONSTANT PLS_INTEGER := 215;
TYPE_CODE CONSTANT PLS_INTEGER := 200;
/**
* FTP File record datatype
*
* Elements:
* localpath - full directory name in which the local file resides or will reside
* Windows: 'd:\oracle\utl_file'
* UNIX: '/home/oracle/utl_file'
* filename - filename and extension for the file to be received or sent
* changing the filename for the PUT or GET is currently not allowed
* Examples: 'myfile.dat' 'myfile20021119.xml'
* remotepath - full directory name in which the local file will be sent or the
* remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three'
* filetype - reserved for future use, ignored in code
* transfer_mode - 'PUT' or 'GET'
* status - status of the transfer. 'ERROR' or 'SUCCESS'
* error_message - meaningful (hopefully) error message explaining the reason for failure
* bytes_transmitted - how many bytes were sent/received
* trans_start - date/time the transmission started
* trans_end - date/time the transmission ended
*
*/
TYPE r_ftp_rec IS RECORD(localpath VARCHAR2(255),
filename VARCHAR2(255),
remotepath VARCHAR2(255),
filetype VARCHAR2(20),
transfer_mode VARCHAR2(5),
status VARCHAR2(40),
error_message VARCHAR2(255),
bytes_transmitted NUMBER,
trans_start DATE,
trans_end DATE);
/**
* FTP File Table - used to store many files for transfer
*
*/
TYPE t_ftp_rec IS TABLE of r_ftp_rec INDEX BY BINARY_INTEGER;
/**
* Internal convenience procedure for creating passive host IP address
* and port number.
*
*/
PROCEDURE CREATE_PASV(p_pasv_cmd IN VARCHAR2,
p_pasv_host OUT VARCHAR2,
p_pasv_port OUT NUMBER);
/**
* Function used to validate FTP server responses based on the
* code passed in p_code. Reads single or multi-line responses.
*
*/
FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN;
/**
* Function used to validate FTP server responses based on the
* code passed in p_code. Reads single or multi-line responses.
* Overloaded because some responses can have 2 valid codes.
*
*/
FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code1 IN PLS_INTEGER,
p_code2 IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN;
/**
* Procedure that handles the actual data transfer. Meant
* for internal package use. Returns information about the
* actual transfer.
*
*/
PROCEDURE TRANSFER_ASCII(u_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_pasv_host IN VARCHAR2,
p_pasv_port IN PLS_INTEGER,
p_transfer_mode IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE);
/**
* Function to handle FTP of many files.
* Returns TRUE if no batch-level errors occur.
* Returns FALSE if a batch-level error occurs.
*
* Parameters:
*
* p_error_msg - error message for batch level errors
* p_files - BRNC_FTP_PKG.t_ftp_rec table type. Accepts
* list of files to be transferred (may be any combination of PUT or GET)
* returns the table updated with transfer status, error message,
* bytes_transmitted, transmission start date/time and transmission end
* date/time
* p_username - username for FTP server
* p_password - password for FTP server
* p_hostname - hostname or IP address of server Ex: 'ftp.oracle.com' or '127.0.0.1'
* p_port - port number to connect on. FTP is usually on 21, but this may be overridden
* if the server is configured differently.
*
*/
FUNCTION FTP_MULTIPLE(p_error_msg OUT VARCHAR2,
p_files IN OUT t_ftp_rec,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
p_port IN PLS_INTEGER DEFAULT 21)
RETURN BOOLEAN;
/**
* Convenience function for single-file PUT
*
* Parameters:
* p_localpath - full directory name in which the local file resides or will reside
* Windows: 'd:\oracle\utl_file'
* UNIX: '/home/oracle/utl_file'
* p_filename - filename and extension for the file to be received or sent
* changing the filename for the PUT or GET is currently not allowed
* Examples: 'myfile.dat' 'myfile20021119.xml'
* p_remotepath - full directory name in which the local file will be sent or the
* remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three'
* p_username - username for FTP server
* p_password - password for FTP server
* p_hostname - FTP server IP address or host name Ex: 'ftp.oracle.com' or '127.0.0.1'
* v_status - status of the transfer. 'ERROR' or 'SUCCESS'
* v_error_message - meaningful (hopefully) error message explaining the reason for failure
* n_bytes_transmitted - how many bytes were sent/received
* d_trans_start - date/time the transmission started
* d_trans_end - date/time the transmission ended
* p_port - port number to connect to, default is 21
* p_filetype - always set to 'ASCII', reserved for future use, ignored in code
*
*/
FUNCTION PUT(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN;
/**
* Convenience function for single-file GET
*
* Parameters:
* p_localpath - full directory name in which the local file resides or will reside
* Windows: 'd:\oracle\utl_file'
* UNIX: '/home/oracle/utl_file'
* p_filename - filename and extension for the file to be received or sent
* changing the filename for the PUT or GET is currently not allowed
* Examples: 'myfile.dat' 'myfile20021119.xml'
* p_remotepath - full directory name in which the local file will be sent or the
* remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three'
* p_username - username for FTP server
* p_password - password for FTP server
* p_hostname - FTP server IP address or host name Ex: 'ftp.oracle.com' or '127.0.0.1'
* v_status - status of the transfer. 'ERROR' or 'SUCCESS'
* v_error_message - meaningful (hopefully) error message explaining the reason for failure
* n_bytes_transmitted - how many bytes were sent/received
* d_trans_start - date/time the transmission started
* d_trans_end - date/time the transmission ended
* p_port - port number to connect to, default is 21
* p_filetype - always set to 'ASCII', reserved for future use, ignored in code
*
*/
FUNCTION GET(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN;
END BRNC_FTP_PKG;
/
CREATE OR REPLACE PACKAGE BODY BRNC_FTP_PKG
AS
/*****************************************************************************
** Create the passive host IP and port number to connect to
**
*****************************************************************************/
PROCEDURE CREATE_PASV(p_pasv_cmd IN VARCHAR2,
p_pasv_host OUT VARCHAR2,
p_pasv_port OUT NUMBER)
IS
v_pasv_cmd VARCHAR2(30) := p_pasv_cmd; --Host and port to connect to for data transfer
n_port_dec NUMBER;
n_port_add NUMBER;
BEGIN
p_pasv_host := REPLACE(SUBSTR(v_pasv_cmd,1,INSTR(v_pasv_cmd,',',1,4)-1),',','.');
n_port_dec := TO_NUMBER(SUBSTR(v_pasv_cmd,INSTR(v_pasv_cmd,',',1,4)+1,(INSTR(v_pasv_cmd,',',1,5)-(INSTR(v_pasv_cmd,',',1,4)+1))));
n_port_add := TO_NUMBER(SUBSTR(v_pasv_cmd,INSTR(v_pasv_cmd,',',1,5)+1,LENGTH(v_pasv_cmd)-INSTR(v_pasv_cmd,',',1,5)));
p_pasv_port := (n_port_dec*256) + n_port_add;
EXCEPTION
WHEN OTHERS
THEN
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END CREATE_PASV;
/*****************************************************************************
** Read a single or multi-line reply from the FTP server and validate
** it against the code passed in p_code.
**
** Return TRUE if reply code matches p_code, FALSE if it doesn't or error
** occurs
**
** Send full server response back to calling procedure
*****************************************************************************/
FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN
IS
n_code VARCHAR2(3) := p_code;
n_byte_count PLS_INTEGER;
v_msg VARCHAR2(255);
n_line_count PLS_INTEGER := 0;
BEGIN
LOOP
v_msg := UTL_TCP.GET_LINE(p_ctrl_con);
n_line_count := n_line_count + 1;
IF n_line_count = 1
THEN
p_reply := v_msg;
ELSE
p_reply := p_reply || SUBSTR(v_msg,4);
END IF;
EXIT WHEN INSTR(v_msg,'-',1,1) <> 4;
END LOOP;
IF to_number(SUBSTR(p_reply,1,3)) = n_code
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_reply := SQLERRM;
RETURN FALSE;
END VALIDATE_REPLY;
/*****************************************************************************
** Reads a single or multi-line reply from the FTP server
**
** Return TRUE if reply code matches p_code1 or p_code2,
** FALSE if it doesn't or error occurs
**
** Send full server response back to calling procedure
*****************************************************************************/
FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code1 IN PLS_INTEGER,
p_code2 IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN
IS
v_code1 VARCHAR2(3) := to_char(p_code1);
v_code2 VARCHAR2(3) := to_char(p_code2);
v_msg VARCHAR2(255);
n_line_count PLS_INTEGER := 0;
BEGIN
LOOP
v_msg := UTL_TCP.GET_LINE(p_ctrl_con);
n_line_count := n_line_count + 1;
IF n_line_count = 1
THEN
p_reply := v_msg;
ELSE
p_reply := p_reply || SUBSTR(v_msg,4);
END IF;
EXIT WHEN INSTR(v_msg,'-',1,1) <> 4;
END LOOP;
IF to_number(SUBSTR(p_reply,1,3)) IN(v_code1,v_code2)
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_reply := SQLERRM;
RETURN FALSE;
END VALIDATE_REPLY;
/*****************************************************************************
** Handles actual data transfer. Responds with status, error message, and
** transfer statistics.
**
** Potential errors could be with connection or file i/o
**
*****************************************************************************/
PROCEDURE TRANSFER_ASCII(u_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_pasv_host IN VARCHAR2,
p_pasv_port IN PLS_INTEGER,
p_transfer_mode IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE)
IS
u_data_con UTL_TCP.CONNECTION;
u_filehandle UTL_FILE.FILE_TYPE;
v_tsfr_mode VARCHAR2(3) := p_transfer_mode;
v_mode VARCHAR2(1);
v_tsfr_cmd VARCHAR2(10);
v_buffer VARCHAR2(32767);
v_localpath VARCHAR2(255) := p_localpath;
v_filename VARCHAR2(255) := p_filename;
v_host VARCHAR2(20) := p_pasv_host;
n_port PLS_INTEGER := p_pasv_port;
n_bytes NUMBER;
v_msg VARCHAR2(255);
v_reply VARCHAR2(1000);
v_err_status VARCHAR2(20) := 'ERROR';
BEGIN
/** Initialize some of our OUT variables **/
v_status := 'SUCCESS';
v_error_message := ' ';
n_bytes_transmitted := 0;
IF UPPER(v_tsfr_mode) = 'PUT'
THEN
v_mode := 'r';
v_tsfr_cmd := 'STOR ';
ELSIF UPPER(v_tsfr_mode) = 'GET'
THEN
v_mode := 'w';
v_tsfr_cmd := 'RETR ';
END IF;
/** Open data connection on Passive host and port **/
u_data_con := UTL_TCP.OPEN_CONNECTION(v_host,n_port);
/** Open the local file to read and transfer data **/
u_filehandle := UTL_FILE.FOPEN(v_localpath,v_filename,v_mode);
/** Send the STOR command to tell the server we're going to upload a file **/
n_bytes := UTL_TCP.WRITE_LINE(u_ctrl_con,v_tsfr_cmd||v_filename);
IF VALIDATE_REPLY(u_ctrl_con,TSFR_START_CODE1,TSFR_START_CODE2,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;
d_trans_start := SYSDATE;
IF UPPER(v_tsfr_mode) = 'PUT'
THEN
LOOP
BEGIN
UTL_FILE.GET_LINE(u_filehandle,v_buffer);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
n_bytes := UTL_TCP.WRITE_LINE(u_data_con,v_buffer);
n_bytes_transmitted := n_bytes_transmitted + n_bytes;
END LOOP;
ELSIF UPPER(v_tsfr_mode) = 'GET'
THEN
LOOP
BEGIN
v_buffer := UTL_TCP.GET_LINE(u_data_con,TRUE);
/** Sometimes the TCP/IP buffer sends null data **/
/** we only want to receive the actual data **/
IF v_buffer IS NOT NULL
THEN
UTL_FILE.PUT_LINE(u_filehandle,v_buffer);
n_bytes := LENGTH(v_buffer);
n_bytes_transmitted := n_bytes_transmitted + n_bytes;
END IF;
EXCEPTION
WHEN UTL_TCP.END_OF_INPUT
THEN
EXIT;
END;
END LOOP;
END IF;
/** Flush the buffer on the data connection **/
--UTL_TCP.FLUSH(u_data_con);
d_trans_end := SYSDATE;
/** Close the file **/
UTL_FILE.FCLOSE(u_filehandle);
/** Close the Data Connection **/
UTL_TCP.CLOSE_CONNECTION(u_data_con);
/** Verify the transfer succeeded **/
IF VALIDATE_REPLY(u_ctrl_con,TSFR_END_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;
EXCEPTION
WHEN ctrl_exception
THEN
v_status := v_err_status;
v_error_message := v_reply;
IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);
WHEN UTL_FILE.invalid_path
THEN
v_status := v_err_status;
v_error_message := 'Directory '||v_localpath||' is not available to UTL_FILE. Check the init.ora file for valid UTL_FILE directories.';
UTL_TCP.CLOSE_CONNECTION(u_data_con);
WHEN UTL_FILE.invalid_operation
THEN
v_status := v_err_status;
IF UPPER(v_tsfr_mode) = 'PUT'
THEN
v_error_message := 'The file '||V_filename||' in the directory '||v_localpath||' could not be opened for reading.';
ELSIF UPPER(v_tsfr_mode) = 'GET'
THEN
v_error_message := 'The file '||V_filename||' in the directory '||v_localpath||' could not be opened for writing.';
END IF;
IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);
WHEN UTL_FILE.read_error
THEN
v_status := v_err_status;
v_error_message := 'The system encountered an error while trying to read '||v_filename||' in the directory '||v_localpath;
IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);
WHEN UTL_FILE.write_error
THEN
v_status := v_err_status;
v_error_message := 'The system encountered an error while trying to write to '||v_filename||' in the directory '||v_localpath;
IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);
WHEN UTL_FILE.internal_error
THEN
v_status := v_err_status;
v_error_message := 'The UTL_FILE package encountered an unexpected internal system error.';
IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);
WHEN OTHERS
THEN
v_status := v_err_status;
v_error_message := SQLERRM;
IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);
END TRANSFER_ASCII;
/*****************************************************************************
** Handles connection to host and FTP of multiple files
** Files can be any combination of PUT and GET
**
*****************************************************************************/
FUNCTION FTP_MULTIPLE(p_error_msg OUT VARCHAR2,
p_files IN OUT t_ftp_rec,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
p_port IN PLS_INTEGER DEFAULT 21)
RETURN BOOLEAN
IS
v_username VARCHAR2(30) := p_username;
v_password VARCHAR2(30) := p_password;
v_hostname VARCHAR2(30) := p_hostname;
n_port PLS_INTEGER := p_port;
u_ctrl_con UTL_TCP.CONNECTION;
n_byte_count PLS_INTEGER;
n_first_index NUMBER;
v_msg VARCHAR2(250);
v_reply VARCHAR2(1000);
v_pasv_host VARCHAR2(20);
n_pasv_port NUMBER;
invalid_transfer EXCEPTION;
BEGIN
p_error_msg := 'FTP Successful'; --Assume the overall transfer will succeed
/** Attempt to connect to the host machine **/
u_ctrl_con := UTL_TCP.OPEN_CONNECTION(v_hostname,n_port);
IF VALIDATE_REPLY(u_ctrl_con,CONNECT_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;
/** Send username **/
n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'USER '||v_username);
IF VALIDATE_REPLY(u_ctrl_con,USER_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;
/** Send password **/
n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'PASS '||v_password);
IF VALIDATE_REPLY(u_ctrl_con,LOGIN_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;
/** We should be logged in, time to transfer all files **/
FOR i IN p_files.FIRST..p_files.LAST
LOOP
IF p_files.EXISTS(i)
THEN
BEGIN
/** Change to the remotepath directory **/
n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'CWD '||p_files(i).remotepath);
IF VALIDATE_REPLY(u_ctrl_con,CWD_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;
/** Switch to IMAGE mode **/
n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'TYPE I');
IF VALIDATE_REPLY(u_ctrl_con,TYPE_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;
/** Get a Passive connection to use for data transfer **/
n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'PASV');
IF VALIDATE_REPLY(u_ctrl_con,PASV_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;
CREATE_PASV(SUBSTR(v_reply,INSTR(v_reply,'(',1,1)+1,INSTR(v_reply,')',1,1)-INSTR(v_reply,'(',1,1)-1),v_pasv_host,n_pasv_port);
/** Transfer Data **/
IF UPPER(p_files(i).transfer_mode) = 'PUT'
THEN
TRANSFER_ASCII(u_ctrl_con,
p_files(i).localpath,
p_files(i).filename,
v_pasv_host,
n_pasv_port,
p_files(i).transfer_mode,
p_files(i).status,
p_files(i).error_message,
p_files(i).bytes_transmitted,
p_files(i).trans_start,
p_files(i).trans_end);
ELSIF UPPER(p_files(i).transfer_mode) = 'GET'
THEN
TRANSFER_ASCII(u_ctrl_con,
p_files(i).localpath,
p_files(i).filename,
v_pasv_host,
n_pasv_port,
p_files(i).transfer_mode,
p_files(i).status,
p_files(i).error_message,
p_files(i).bytes_transmitted,
p_files(i).trans_start,
p_files(i).trans_end);
ELSE
RAISE invalid_transfer; -- Raise an exception here
END IF;
EXCEPTION
WHEN ctrl_exception
THEN
p_files(i).status := 'ERROR';
p_files(i).error_message := v_reply;
WHEN invalid_transfer
THEN
p_files(i).status := 'ERROR';
p_files(i).error_message := 'Invalid transfer method. Use PUT or GET.';
END;
END IF;
END LOOP;
/** Send QUIT command **/
n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'QUIT');
/** Don't need to validate QUIT, just close the connection **/
UTL_TCP.CLOSE_CONNECTION(u_ctrl_con);
RETURN TRUE;
EXCEPTION
WHEN ctrl_exception
THEN
p_error_msg := v_reply;
UTL_TCP.CLOSE_ALL_CONNECTIONS;
RETURN FALSE;
WHEN OTHERS
THEN
p_error_msg := SQLERRM;
UTL_TCP.CLOSE_ALL_CONNECTIONS;
RETURN FALSE;
END FTP_MULTIPLE;
/*****************************************************************************
** Convenience function for single-file PUT
** Formats file information for FTP_MULTIPLE function and calls it.
**
*****************************************************************************/
FUNCTION PUT(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN
IS
t_files t_ftp_rec;
v_username VARCHAR2(30) := p_username;
v_password VARCHAR2(50) := p_password;
v_hostname VARCHAR2(100) := p_hostname;
n_port PLS_INTEGER := p_port;
v_err_msg VARCHAR2(255);
b_ftp BOOLEAN;
BEGIN
t_files(1).localpath := p_localpath;
t_files(1).filename := p_filename;
t_files(1).remotepath := p_remotepath;
t_files(1).filetype := p_filetype;
t_files(1).transfer_mode := 'PUT';
b_ftp := FTP_MULTIPLE(v_err_msg,
t_files,
v_username,
v_password,
v_hostname,
n_port);
IF b_ftp = FALSE
THEN
v_status := 'ERROR';
v_error_message := v_err_msg;
RETURN FALSE;
ELSIF b_ftp = TRUE
THEN
v_status := t_files(1).status;
v_error_message := t_files(1).error_message;
n_bytes_transmitted := t_files(1).bytes_transmitted;
d_trans_start := t_files(1).trans_start;
d_trans_end := t_files(1).trans_end;
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_status := 'ERROR';
v_error_message := SQLERRM;
RETURN FALSE;
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
END PUT;
/*****************************************************************************
** Convenience function for single-file GET
** Formats file information for FTP_MULTIPLE function and calls it.
**
*****************************************************************************/
FUNCTION GET(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN
IS
t_files t_ftp_rec;
v_username VARCHAR2(30) := p_username;
v_password VARCHAR2(50) := p_password;
v_hostname VARCHAR2(100) := p_hostname;
n_port PLS_INTEGER := p_port;
v_err_msg VARCHAR2(255);
b_ftp BOOLEAN;
BEGIN
t_files(1).localpath := p_localpath;
t_files(1).filename := p_filename;
t_files(1).remotepath := p_remotepath;
t_files(1).filetype := p_filetype;
t_files(1).transfer_mode := 'GET';
b_ftp := FTP_MULTIPLE(v_err_msg,
t_files,
v_username,
v_password,
v_hostname,
n_port);
IF b_ftp = FALSE
THEN
v_status := 'ERROR';
v_error_message := v_err_msg;
RETURN FALSE;
ELSIF b_ftp = TRUE
THEN
v_status := t_files(1).status;
v_error_message := t_files(1).error_message;
n_bytes_transmitted := t_files(1).bytes_transmitted;
d_trans_start := t_files(1).trans_start;
d_trans_end := t_files(1).trans_end;
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_status := 'ERROR';
v_error_message := SQLERRM;
RETURN FALSE;
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
END GET;
END BRNC_FTP_PKG;
/
rem -----------------------------------------------------------------------
rem Filename: smtp.sql
rem Purpose: Send e-mail messages from PL/SQL
rem Notes: From Oracle8i release 8.1.6 one can send e-mail messages
rem directly from PL/SQL using either the UTL_TCP or UTL_SMTP
rem packages. JServer needs to be installed and configured.
rem Pont the IP Address to your local SMTP (Simple Mail
rem Transport) Server. No pipes or external procedures are
rem required.
rem Date: 27-Mar-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := '' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port 25 on local machine
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
when others then
raise_application_error(
-20000, 'Unable to send e-mail message from pl/sql because of: '||
sqlerrm);
END;
/
show errors
-- Examples:
set serveroutput on
exec send_mail(msg_to =>'you@yourdomain.com');
exec send_mail(msg_to =>'you@yourdomain.com', -
msg_text=>'Look Ma, I can send mail from plsql' -
);
rem ----------------------------------------------------------------------
rem Filename: smtp-att.sql
rem Purpose: Send e-mail messages and attachments from PL/SQL
rem Notes: From Oracle8i release 8.1.6 one can send e-mail messages
rem directly from PL/SQL using either the UTL_TCP or UTL_SMTP
rem packages. Jserver needs to be installed and configured.
rem No pipes or external procedures required.
rem Date: 15-MAR-2001
rem Author: Virgilio Nunes (Virgilio@logtek.co.za)
rem ----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'EMAILADDRESS@DOMAIN.COM', ----- MAIL BOX SENDING THE EMAIL
msg_to varchar2 := 'EMAILADDRESS@DOMAIN.COM', ----- MAIL BOX RECIEVING THE EMAIL
msg_subject varchar2 := 'Output file TEST1', ----- EMAIL SUBJECT
msg_text varchar2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
v_output1 varchar2 := 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT SHOULD BE IN A TEXT FILE ATTACHED TO THE EMAIL.')
IS
c utl_tcp.connection;
rc integer;
crlf VARCHAR2(2):= CHR(13)||CHR(10);
mesg VARCHAR2( 32767 );
BEGIN
c := utl_tcp.open_connection('196.35.140.18', 25); ----- OPEN SMTP PORT CONNECTION
rc := utl_tcp.write_line(c, 'HELO 196.35.140.18'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'EHLO 196.35.140.18'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from); ----- MAIL BOX SENDING THE EMAIL
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to); ----- MAIL BOX RECIEVING THE EMAIL
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); ----- EMAIL MESSAGE BODY START
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ));
rc := utl_tcp.write_line(c, 'From: '||msg_from||' <'||msg_from||'>');
rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');
rc := utl_tcp.write_line(c, 'To: '||msg_to||' <'||msg_to||'>');
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, 'Content-Type: multipart/mixed;'); ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
rc := utl_tcp.write_line(c, ' boundary="-----SECBOUND"'); ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
rc := utl_tcp.write_line(c, ''); ----- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED.
rc := utl_tcp.write_line(c, '-------SECBOUND');
rc := utl_tcp.write_line(c, 'Content-Type: text/plain'); ----- 1ST BODY PART. EMAIL TEXT MESSAGE
rc := utl_tcp.write_line(c, 'Content-Transfer-Encoding: 7bit');
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text); ----- TEXT OF EMAIL MESSAGE
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, '-------SECBOUND');
rc := utl_tcp.write_line(c, 'Content-Type: text/plain;'); ----- 2ND BODY PART.
rc := utl_tcp.write_line(c, ' name="Test.txt"');
rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
rc := utl_tcp.write_line(c, ' filename="Test.txt"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, v_output1);
rc := utl_tcp.write_line(c, '-------SECBOUND--');
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, '.'); ----- EMAIL MESSAGE BODY END
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT'); ----- ENDS EMAIL TRANSACTION
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
when others then
raise_application_error(-20000, SQLERRM);
END;
/
rem ----------------------------------------------------------------------
rem Filename: utlsmtp.sql
rem Purpose: Send e-mail messages and attachments from PL/SQL
rem Date: 01-Sep-2006
rem Author: Frank Naude - OraFAQ
rem ----------------------------------------------------------------------
DECLARE
v_From VARCHAR2(80) := 'oracle@mycompany.com';
v_Recipient VARCHAR2(80) := 'test@mycompany.com';
v_Subject VARCHAR2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="excel.csv"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="excel.csv"'|| crlf ||
crlf ||
'CSV,file,attachement'|| crlf || -- Content of attachment
crlf ||
'-------SECBOUND--' -- End MIME mail
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/
rem -----------------------------------------------------------------------
rem Filename: mailmerge.sql
rem Purpose: Merge data from the table/view data sources into a custom
rem template.
rem Date: 30 Dec 2004
rem Author: Claudiu Ariton (aritonc@yahoo.com)
rem
rem Package detail:
rem TEMP_MAIL_MERGE - temporary table to store the result
rem DATA_SOURCES - data sources table
rem mail_merge procedure - Merge data from list of values into template
rem generic_mail_merge - Merge data from a table/view into template
rem
rem To define a template use $n where n is the position of a field in
rem data_sources_v view (escape character is $).
rem
rem E.G.: $2 $3 born on $4 has to pay $$$5. Call us at $6.
rem -----------------------------------------------------------------------
/*************************** Create result temp table, test table and context *************************/
create GLOBAL TEMPORARY table TEMP_MAIL_MERGE
(
PKID NUMBER ,
RESULT VARCHAR2(4000) )
On Commit Delete Rows
/
create table data_sources
(ID Number(10) primary key,
P2 Varchar2(10),
P3 Varchar2(50),
P4 DATE,
P5 NUMBER
)
/
Insert into DATA_SOURCES
(ID, P2, P3, P4, P5)
Values
(1, 'Claudiu', 'Ariton', TO_DATE('09/21/1976 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100000)
/
Commit
/
create or replace context mm_ctx using mail_merge
/
create or replace view data_sources_v as
select ID PKID, P2, P3, P4, P5,
SYS_CONTEXT('mm_ctx','param1',2000) param1 from
data_sources
/
/*************************** Create package head *************************/
Create or Replace Package mail_merge as
/**************************************************************************************
*
* TITLE......: Mail Merge
* DESCRIPTION: Merge data from the data source into a custom template
*
* AUTHOR.....: Claudiu Ariton
* DATE.......: april 2004
*
* Modifications
*
**************************************************************************************/
type Argv is table of varchar2(4000) index by binary_integer;
emptyargv argv;
procedure set_ctx( p_name in varchar2, p_value in varchar2,p_ctx in varchar2 default 'mm_ctx' );
function mail_merge(
p_message in varchar2,
p_argv in argv ,
p_esc_char in varchar2 default '$') return varchar2;
Procedure generic_mail_merge(p_ttext varchar2, p_query in varchar2,
p_date_format in varchar2 default 'dd-MON-yyyy hh24:mi:ss',
p_bindid in number default null,p_list_val in argv default emptyargv);
end mail_merge;
/
/*************************** Create package body *************************/
Create or Replace Package Body mail_merge as
-- -----------------------------------------------------------------------------------
-- Set_ctx - set generic context
-- -----------------------------------------------------------------------------------
procedure set_ctx( p_name in varchar2, p_value in varchar2,p_ctx in varchar2 default 'mm_ctx' )
as
begin
dbms_session.set_context( p_ctx, p_name, p_value, USER);
end;
-- -----------------------------------------------------------------------------------
-- mail_merge - Merge data from list of vaalues into a custom template
-- -----------------------------------------------------------------------------------
function mail_merge(
p_message in varchar2,
p_argv in argv ,
p_esc_char in varchar2 default '$') return varchar2 is
--
l_message long := null;
l_str long := p_message;
l_idx number := 1;
l_ptr number := 1;
l_poz varchar2(10);
l_on number;
begin
if nvl( instr( p_message, p_esc_char ), 0 ) = 0 and
nvl( instr( p_message, '\' ), 0 ) = 0 then
return p_message;
end if;
loop
l_on:=0;
l_ptr := instr( l_str, p_esc_char );
exit when l_ptr = 0 or l_ptr is null;
l_message := l_message || substr( l_str, 1, l_ptr-1 );
l_str := substr( l_str, l_ptr+1 );
l_poz:=null;
while substr( l_str, 1, 1 ) in ('0','1','2','3','4','5','6','7','8','9') loop
l_poz:=l_poz||substr( l_str, 1, 1 );
l_str := substr( l_str, 2 );
l_on:=1;
end loop;
begin
l_message := l_message || p_argv(to_number(l_poz));
exception
when no_data_found then l_message := l_message || '
when others then null;
end;
if (substr( l_str,1,1 ) = p_esc_char) and (l_on=0) then
l_message := l_message || p_esc_char;
l_str := substr( l_str, 2 );
end if;
end loop;
l_str := l_message || l_str;
l_message := null;
loop
l_ptr := instr( l_str, '\' );
exit when l_ptr = 0 or l_ptr is null;
l_message := l_message || substr( l_str, 1, l_ptr-1 );
l_str := substr( l_str, l_ptr+1 );
if substr( l_str, 1, 1 ) = 'n' then
l_message := l_message || chr(10);
l_str := substr( l_str, 2 );
elsif substr( l_str, 1, 1 ) = 't' then
l_message := l_message || chr(9);
l_str := substr( l_str, 2 );
elsif substr( l_str, 1, 1 ) = '\' then
l_message := l_message || '\';
l_str := substr( l_str, 2 );
else
l_message := l_message || '\';
end if;
end loop;
return l_message || l_str;
end mail_merge;
-- -----------------------------------------------------------------------------------
-- mail_merge - Merge data from a table/viiew into a custom template
-- -----------------------------------------------------------------------------------
Procedure generic_mail_merge(p_ttext varchar2, p_query in varchar2,
p_date_format in varchar2 default 'dd-MON-yyyy hh24:mi:ss',
p_bindid in number default null,p_list_val in argv default emptyargv)
is
l_theCursor integer default dbms_sql.open_cursor;
l_defcolumn varchar2(4000);
l_columnValue argv;
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
v_result varchar2(4000);
v_pkid number;
begin
execute immediate
'alter session set
nls_date_format='''|| p_date_format ||'''';
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
if p_bindid is not null then
dbms_sql.bind_variable( l_theCursor, ':1', p_bindid );
end if;
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
-- seteaza context
begin
for i in 1..p_list_val.count loop
set_ctx('param'||to_char(i),p_list_val(i));
end loop;
exception
when others then null;
end;
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_defcolumn, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue(i) );
if upper(l_descTbl(i).col_name)='PKID' then
v_pkid:=to_number(l_columnValue(i));
end if;
end loop;
v_result:=mail_merge( p_ttext,l_columnValue);
insert into TEMP_MAIL_MERGE(pkid,result) values (v_pkid,v_result);
end loop;
dbms_sql.close_cursor( l_theCursor);
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
if dbms_sql.is_open(l_theCursor) then
dbms_sql.close_cursor( l_theCursor);
end if;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
end mail_merge;
/
/*************************** Test it *************************/
declare
v_list_val mail_merge.argv;
v_query varchar2(4000);
begin
v_list_val(1):='(040)-2313543';
v_query:= 'select * from data_sources_v vt where 1=1'||' AND vt.pkid=:1';
mail_merge.generic_mail_merge('$2 $3 born on $4 has to pay $$$5. Call us at $6. Thank you very much.',
v_query,'dd-MON-yyyy',1,v_list_val);
end;
/
select * from temp_mail_merge
/
rem -----------------------------------------------------------------------
rem Filename: httpget.sql
rem Purpose: Access Internet Web pages from SQL or PL/SQL
rem Notes: From Oracle 8.0 one can retrieve web pages directly
rem from SQL or PL/SQL. Note you need to run utlhttp.sql as
rem SYS before this procedure will work.
rem Date: 27-Mar-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set pages 50000
select utl_http.request('http://www.orafaq.net/') from dual;
rem -----------------------------------------------------------------------
rem Filename: auditdll.sql
rem Purpose: Maintain an audit log of DDL changes (alter/ drop/ create)
rem within a schema
rem Date: 15-Feb-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
DROP TRIGGER audit_ddl_changes
/
DROP TABLE dll_audit_log
/
CREATE TABLE dll_audit_log (
stamp DATE,
username VARCHAR2(30),
osuser VARCHAR2(30),
machine VARCHAR2(30),
terminal VARCHAR2(30),
operation VARCHAR2(30),
objtype VARCHAR2(30),
objname VARCHAR2(30))
/
CREATE OR REPLACE TRIGGER audit_ddl_changes
AFTER create OR drop OR alter
ON scott.SCHEMA -- Change SCOTT to your schema name!!!
-- ON DATABASE
BEGIN
INSERT INTO dll_audit_log VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'TERMINAL'),
ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME
);
END;
/
show errors
-- Now, let's test it
CREATE TABLE my_test_table (col1 DATE)
/
DROP TABLE my_test_table
/
set pages 50000
SELECT * FROM dll_audit_log
/
rem -----------------------------------------------------------------------
rem Filename: countall.sql
rem Purpose: Count the number of rows for ALL tables in current schema
rem using PL/SQL
rem Date: 15-Apr-2000
rem Author: Eberhardt, Roberto (Bolton) (reberhar@husky.ca)
rem -----------------------------------------------------------------------
set serveroutput on size 1000000
DECLARE
t_c1_tname user_tables.table_name%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 0; -- Only show tables with more rows
cursor c1 is select table_name from user_tables order by table_name;
BEGIN
t_limit := 0;
open c1;
loop
fetch c1 into t_c1_tname;
exit when c1%NOTFOUND;
t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records > t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')||
to_char(t_total_records,'99999999')||' record(s)');
end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/
rem -----------------------------------------------------------------------
rem Filename: countall2.sql
rem Purpose: List tables from schema with more than X rows
rem Date: 15-Sep-2005
rem Author: Praveen Kumar Chugh
rem -----------------------------------------------------------------------
REM First of all create the following function - rowcount...
CREATE OR REPLACE FUNCTION rowcount(tname VARCHAR2) RETURN NUMBER IS
x NUMBER;
stmt VARCHAR2(200);
BEGIN
stmt := 'select count(*) from '||tname;
execute immediate stmt into x;
return x;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
/
SHOW ERRORS
REM Then write this query...
SELECT table_name, roucount(table_name) Records
FROM cat
WHERE roucount(table_name) >= 100;
/
rem -----------------------------------------------------------------------
rem Filename: strreplace.sql
rem Purpose: Replace all occurences of a substring with another substring
rem Date: 28-Jul-2003
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
create or replace function strreplace(str varchar2, from_str varchar2, to_str varchar2)
return varchar2
AS
str_temp varchar2(4000);
str_pos number := instr(str, from_str);
BEGIN
str_temp := str;
while ( str_pos > 0 ) loop
str_temp := substr(str_temp, 0, str_pos-1) || to_str ||
substr(str_temp, str_pos + length(from_str));
str_pos := instr(str_temp, from_str);
end loop;
return str_temp;
END;
/
show errors
-- Examples
select strreplace('This is a beautiful day!', 'beautiful', 'horrible')
from dual
/
select 'mv '||name||' '||strreplace(name, 'OLDSID', 'NEWSID')
from v$datafile
/
rem -----------------------------------------------------------------------
rem Filename: cheqprint.sql
rem Purpose: This function will convert a number to words, handy for
rem ptinting cheques.
rem Notes: The number before the decimal should be between 1..5373484
rem Date: 24-Feb-2003
rem Author: Sebastian Thomas, sebastianthomas@rediffmail.com
rem -----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION NUMBER_CONVERSION(NUM NUMBER) RETURN VARCHAR2
IS
A VARCHAR2(1000);
B VARCHAR2(20);
X NUMBER;
Y NUMBER := 1;
Z NUMBER;
LSIGN NUMBER;
NO NUMBER;
BEGIN
X:= INSTR(NUM, '.');
LSIGN := SIGN(NUM);
NO := ABS(NUM);
IF X = 0 THEN
SELECT TO_CHAR(TO_DATE(NO, 'J'), 'JSP') INTO A FROM DUAL;
ELSE
SELECT to_char(to_date(SUBSTR(NO, 1,
NVL(INSTR(NO, '.')-1, LENGTH(NO))),
'J'), 'JSP') INTO A FROM DUAL;
SELECT LENGTH(SUBSTR(NO, INSTR(NO, '.')+1)) INTO Z FROM DUAL;
A := A ||' POINT ';
WHILE Y< Z+1 LOOP
SELECT TO_CHAR(TO_DATE(SUBSTR(NO, (INSTR(NO, '.')+Y), 1), 'J'), 'JSP')
INTO B FROM DUAL;
A := A || B ||' ';
y :=y+1;
END LOOP;
END IF;
IF LSIGN = -1 THEN
RETURN 'NEGATIVE '||A;
ELSE
RETURN A;
END IF;
END;
/
show errors
-- Examples:
Select number_conversion(-3786.9899876) from dual;
Select number_conversion(7685.78788) from dual;
Select number_conversion(7678) from dual;
rem -----------------------------------------------------------------------
rem Filename: indicheq.sql
rem Purpose: This procedure will convert numbers to chars for printing
rem cheques amount in Indian Style. It will print amount from
rem Rs1 to Rs.989999999.
rem Note: In order to see your output in SQL*Plus, set serverout on
rem Date: 22-Feb-2003
rem Author: Birender Kumar, bir_canada@yahoo.com
rem Updateded: 04-Aug-2003
rem Updated By: marine, marine00072003@yahoo.com
rem -----------------------------------------------------------------------
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE cheq(n NUMBER)
as
L NUMBER := 0;
OUTPUT VARCHAR2(2000) := '';
X VARCHAR2(2000) := '';
X1 VARCHAR2(2000) := '';
C1 VARCHAR2(2000) := '';
BEGIN
L := length(N);
IF N < 0 OR N > 999999999 THEN
DBMS_OUTPUT.PUT_LINE('INVALID AMOUNT');
else
if ( N = 0 ) THEN
X := 'ZERO ';
elsif ( N <= 99999 ) THEN
X := to_char(to_date(N,'J'),'JSP') || ' ';
else
if ( to_number(substr(N, L - 5 + 1)) = 0 ) then
X := '';
else
X := to_char(to_date(to_number(substr(N, L - 5 + 1)),'J'),'JSP') || ' ';
end if;
if ( L = 6 ) then
X1 := to_char(to_date(to_number(substr(N, 1, L - 5)),'J'),'JSP') || ' LAKH ';
else
if ( to_number(substr(N, L - 5 -1, 2)) = 0 ) then
X1 := '';
else
X1 := to_char(to_date(to_number(substr(N, L - 5 - 1, 2)),'J'),'JSP') || ' LAKH ';
end if;
if ( L >= 8 ) then
C1 := to_char(to_date(to_number(substr(N, 1, L-7)),'J'),'JSP')||' CRORE ';
end if;
end if;
end if;
if ( N = 0 OR N = 1 ) THEN
DBMS_OUTPUT.PUT_LINE(N||' => '||X||'RUPEE ONLY');
else
DBMS_OUTPUT.PUT_LINE(N||' => '||C1||X1||X||'RUPEES ONLY');
end if;
end if;
END CHEQ;
/
show errors
rem -----------------------------------------------------------------------
rem Filename: nysiis.pls
rem Purpose: NYSIIS function (an improvement on soundex)
rem Notes: Convert a name to a phonetic coding of up to six characters
rem Date: 19-Jul-2004
rem Author: Trevor Fairhurst, trevgf@yahoo.com
rem -----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION NYSIIS (v_text IN VARCHAR2) RETURN VARCHAR2 is
v_sub varchar2(300);
v_length number(10);
v_textin varchar2(300);
v_key varchar2(1);
begin
SELECT UPPER ( v_text ) into v_textin from dual;
dbms_output.put_line( 'Entered surname :' || v_textin);
dbms_output.put_line( ' [1] remove all S and Z chars from the end of the surname ' );
LOOP
SELECT SUBSTR ( v_textin , (length (v_textin))) into v_sub from dual;
dbms_output.put_line('Last letter :' || v_sub);
if v_sub = 'S' OR v_sub = 'Z' THEN
SELECT SUBSTR ( v_textin , 1 , (length (v_textin) -1 )) into v_textin from dual ;
dbms_output.put_line('As last letter s or z drop last letter giving :' || v_textin || ' and check new last letter');
else
dbms_output.put_line('Last letter not s or z completed step 1');
EXIT;
end if;
END LOOP;
dbms_output.put_line('Step 1 completed giving :' || v_textin );
dbms_output.put_line( ' [2] transcode initial strings MAC => MC and PF => F and PH => F ' );
if SUBSTR ( v_textin , 1 , 3 ) = 'MAC' THEN
SELECT 'MC' || SUBSTR ( v_textin , 4 ) into v_textin from dual ;
elsif SUBSTR ( v_textin , 1 , 2 ) = 'PH' THEN
SELECT 'F' || SUBSTR ( v_textin , 3 ) into v_textin from dual ;
elsif SUBSTR ( v_textin , 1 , 2 ) = 'PF' THEN
SELECT 'F' || SUBSTR ( v_textin , 3 ) into v_textin from dual ;
end if;
dbms_output.put_line('Step 2 completed giving :' || v_textin );
dbms_output.put_line( ' [3] transcode trailing strings IX => IC and EX => EC and YE,EE,IE => Y and NT,ND => D ' );
SELECT SUBSTR ( v_textin , (length (v_textin) - 1)) into v_sub from dual;
dbms_output.put_line('Last 2 letters :' || v_sub);
if UPPER(v_sub) in ('IX','EX','YE','EE','IE','NT','ND') THEN
SELECT decode ( UPPER(v_sub) , 'IX','IC',
'EX','EC',
'YE','Y',
'EE','Y',
'IE','Y',
'NT','D',
'ND','D', NULL ) into v_sub from dual ;
SELECT SUBSTR ( v_textin , 1, (length (v_textin) - 1)) || v_sub into v_textin from dual ;
end if;
dbms_output.put_line('Step 3 completed giving :' || v_textin );
dbms_output.put_line( '[4] transcode EV to EF if not at start of name');
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'EV','EF') into v_textin from dual ;
dbms_output.put_line('Step 4 completed giving :' || v_textin );
dbms_output.put_line( '[5] first character of name as first character of key continue with remaining characters');
SELECT SUBSTR ( v_textin , 1 , 1) into v_key from dual;
SELECT SUBSTR ( v_textin , 2 ) into v_textin from dual;
dbms_output.put_line('Step 5 completed first character of key :' || v_key );
dbms_output.put_line( '[6] replace all vowels with A');
SELECT TRANSLATE( v_textin,'AEIOU','AAAAA') into v_textin from dual ;
dbms_output.put_line('Step 6 completed giving :' || v_textin );
dbms_output.put_line( '[7] remove any W that follows a vowel');
SELECT REPLACE( v_textin,'AW','A') into v_textin from dual ;
dbms_output.put_line('Step 7 completed giving :' || v_textin );
dbms_output.put_line( '[8] transcode GHT to GT ');
SELECT REPLACE( v_textin,'GHT','GT') into v_textin from dual ;
dbms_output.put_line('Step 8 completed giving :' || v_textin );
dbms_output.put_line( '[9] transcode DG to G ');
SELECT REPLACE( v_textin,'DG','G') into v_textin from dual ;
dbms_output.put_line('Step 9 completed giving :' || v_textin );
dbms_output.put_line( '[10] transcode PH to F ');
SELECT REPLACE( v_textin,'PH','F') into v_textin from dual ;
dbms_output.put_line('Step 10 completed giving :' || v_textin );
dbms_output.put_line( '[11] if not first character, eliminate all H preceded or followed by a vowel ');
if length(v_textin) > 1 THEN
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'HA','A') into v_textin from dual ;
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'AH','A') into v_textin from dual ;
end if;
dbms_output.put_line('Step 11 completed giving :' || v_textin );
dbms_output.put_line( '[12] change KN to N, else K to C ');
SELECT REPLACE( v_textin,'KN','N') into v_textin from dual ;
SELECT TRANSLATE( v_textin,'K','C') into v_textin from dual ;
dbms_output.put_line( 'Step 12 completed giving :' || v_textin );
dbms_output.put_line( '[13] transcode M to N if not at start of name');
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'M','N') into v_textin from dual ;
dbms_output.put_line('Step 13 completed giving :' || v_textin );
dbms_output.put_line( '[14] transcode Q to G if not at start of name');
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'Q','G') into v_textin from dual ;
dbms_output.put_line('Step 14 completed giving :' || v_textin );
dbms_output.put_line( '[15] transcode transcode SH to S ');
SELECT REPLACE( v_textin,'SH','S') into v_textin from dual ;
dbms_output.put_line('Step 15 completed giving :' || v_textin );
dbms_output.put_line( '[16] transcode transcode SCH to S ');
SELECT REPLACE( v_textin,'SCH','S') into v_textin from dual ;
dbms_output.put_line('Step 16 completed giving :' || v_textin );
dbms_output.put_line( '[17] transcode transcode YW to Y ');
SELECT REPLACE( v_textin,'YW','Y') into v_textin from dual ;
dbms_output.put_line('Step 17 completed giving :' || v_textin );
dbms_output.put_line( '[18] if not first or last character, change Y to A');
if length(v_textin) > 2 THEN
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2, length(v_textin) - 2),'Y','A') || SUBSTR ( v_textin , (length (v_textin))) into v_textin from dual ;
end if;
dbms_output.put_line('Step 18 completed giving :' || v_textin );
dbms_output.put_line( '[19] transcode transcode WR to R ');
SELECT REPLACE( v_textin,'WR','R') into v_textin from dual ;
dbms_output.put_line('Step 19 completed giving :' || v_textin );
dbms_output.put_line( '[20] if not first character, change Z to S ');
if length(v_textin) > 1 THEN
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'Z','S') into v_textin from dual ;
end if;
dbms_output.put_line('Step 20 completed giving :' || v_textin );
dbms_output.put_line( '[21] transcode terminal AY to Y');
if length(v_textin) > 1 THEN
SELECT SUBSTR ( v_textin , (length (v_textin) - 1)) into v_sub from dual;
if v_sub = 'AY' THEN
SELECT SUBSTR(v_textin, 1 , length(v_textin) - 2 ) || 'Y' into v_textin from dual ;
end if;
end if;
dbms_output.put_line('Step 21 completed giving :' || v_textin );
dbms_output.put_line( '[22] remove trailing vowels');
LOOP
SELECT SUBSTR ( v_textin , (length (v_textin))) into v_sub from dual;
dbms_output.put_line('Last letter :' || v_sub);
if v_sub = 'A' THEN
SELECT SUBSTR ( v_textin , 1 , (length (v_textin) -1 )) into v_textin from dual ;
dbms_output.put_line('As last letter A drop last letter giving :' || v_textin || ' and check new last letter');
else
dbms_output.put_line('Last letter not A step 22 completed');
EXIT;
end if;
END LOOP;
dbms_output.put_line('Step 22 completed giving :' || v_textin );
dbms_output.put_line( '[23] collapse all strings of repeated characters');
if length(v_textin) > 2 THEN
LOOP
SELECT v_textin into v_sub from dual;
SELECT REPLACE( v_textin,'AA','A') into v_textin from dual ;
SELECT REPLACE( v_textin,'BB','B') into v_textin from dual ;
SELECT REPLACE( v_textin,'CC','C') into v_textin from dual ;
SELECT REPLACE( v_textin,'DD','D') into v_textin from dual ;
SELECT REPLACE( v_textin,'FF','F') into v_textin from dual ;
SELECT REPLACE( v_textin,'GG','G') into v_textin from dual ;
SELECT REPLACE( v_textin,'HH','H') into v_textin from dual ;
SELECT REPLACE( v_textin,'JJ','J') into v_textin from dual ;
SELECT REPLACE( v_textin,'LL','L') into v_textin from dual ;
SELECT REPLACE( v_textin,'NN','N') into v_textin from dual ;
SELECT REPLACE( v_textin,'PP','P') into v_textin from dual ;
SELECT REPLACE( v_textin,'RR','R') into v_textin from dual ;
SELECT REPLACE( v_textin,'SS','S') into v_textin from dual ;
SELECT REPLACE( v_textin,'TT','T') into v_textin from dual ;
SELECT REPLACE( v_textin,'VV','V') into v_textin from dual ;
SELECT REPLACE( v_textin,'WW','W') into v_textin from dual ;
SELECT REPLACE( v_textin,'XX','X') into v_textin from dual ;
SELECT REPLACE( v_textin,'ZZ','Z') into v_textin from dual ;
dbms_output.put_line( v_textin );
if v_sub = v_textin OR v_sub is NULL THEN
EXIT;
end if;
END LOOP;
end if;
dbms_output.put_line('Step 23 completed giving :' || v_textin );
dbms_output.put_line('[24] put back in first letter and return' );
SELECT v_key || v_textin into v_textin from dual;
RETURN( v_textin );
end NYSIIS;
/
rem --------------------------------------------------------------------------
rem Filename: M_SOUNDEX.SQL
rem Purpose: Converts a string of text into seperate soundex values. Treating
rem it as space deliminated words. Useful when searching text strings for a sounds like.
rem
rem Notes: USEAGE "select M_SOUNDEX('the cat sat on the mat') from dual;"
rem M_SOUNDEX('THECATSATONTHEMAT')
rem -----------------------------------
rem T000 C300 S300 O500 T000 M300
rem
rem
rem select M_SOUNDEX('the cat sat on the mat') from dual where
rem M_SOUNDEX('the cat sat on the mat') like ('%' || SOUNDEX('cot') || '%');
rem
rem Date: 01-Mar-2005
rem Author: Trevor Fairhurst, trevgf@yahoo.com
rem --------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "M_SOUNDEX" (v_text IN VARCHAR2) RETURN VARCHAR2 is
v_number number(10);
v_textin varchar2(4000);
v_textout varchar2(4000);
begin
SELECT UPPER (TRIM( v_text )) into v_textin from dual;
dbms_output.put_line( 'Entered text :' || v_textin);
SELECT '' into v_textout from dual;
LOOP
SELECT instr( v_textin , ' ' , 1 , 1 ) into v_number from dual;
if v_number = 0 THEN
SELECT v_textout || ' ' || SOUNDEX(v_textin) into v_textout from dual ;
EXIT;
else
SELECT v_textout || ' ' || SOUNDEX(substr (v_textin , 0 , v_number - 1 )) into v_textout from dual;
SELECT substr (v_textin , v_number + 1 ) into v_textin from dual;
end if;
END LOOP;
RETURN( v_textout );
end M_SOUNDEX;
/
General DBA Scripts
rem -----------------------------------------------------------------------
rem Filename: uptime.sql
rem Purpose: Display database uptime in days and hours
rem to SYS or SYSTEM
rem Date: 12-Jan-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"
from sys.v_$session
where sid=1 /* this is PMON */
/
rem -----------------------------------------------------------------------
rem Filename: cr8like.sql
rem Purpose: Script to create a new user (with privs) like an existing
rem database user. User data will not be copied.
rem Date: 02-Nov-1998
rem Author: Frank Naude, Oracle FAQ
rem Updated: Konstantin Krivosheyev - 7 Dec 2002
rem Updated: Frank Naude - 18 Dec 2003, 2 Dec 2004
rem -----------------------------------------------------------------------
set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
-- accept psw prompt "Enter new user's password: "
-- Create user...
select 'create user &&newname identified by values '''||password||''''||
-- select 'create user &&newname identified by &psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');
-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
-- Tablespace Quotas...
select 'alter user '||username||' quota '||
    decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
    ' on '||tablespace_name||';'
from  sys.dba_ts_quotas
where  username = upper('&&oldname');
-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||','||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/
rem -----------------------------------------------------------------------
rem Filename: su.sql
rem Purpose: Switch from one database user to another (local db only)
rem Syntax: @su
rem Notes: Require ALTER USER and SELECT ON DBA_USERS privileges
rem Date: 12-Apr-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set termout off head off
col nl newline
spool su.tmp
select 'alter user &&1 identified by &&1;' nl,
'connect &&1/&&1' nl,
'alter user &&1 identified by values '''||u.password||''';' nl
from sys.dba_users u
where u.username = upper('&&1')
and u.username <> user
/
spool off
@su.tmp
REM Delete the file. Use "rm" on Unix, "del" for Win/Dos
! rm su.tmp
! del su.tmp
set termout on
select 'Connected as '||USER||' on '||global_name||'.'
from global_name;
set termout on head on
----------------------------------------------------------------------------
-- -
-- Purpose: Dynamically zip a process's trace file -
-- -
-- Synopsis: @trc_zip -
-- -
-- Description: This script creates a named pipe in place of the process's -
-- trace file and spawns a gzip process to compress it. -
-- -
----------------------------------------------------------------------------
column trc_file new_value trc_file noprint
column zip_file new_value zip_file noprint
select p.value || '/ora_' || u.spid || '.trc' trc_file,
p.value || '/ora_' || u.spid || '.trc.gz' zip_file
from sys.v_$session s,
sys.v_$process u,
sys.v_$parameter p
where s.audsid = userenv('SESSIONID')
and u.addr = s.paddr
and p.name = 'user_dump_dest'
/
set define :
host mknod :trc_file p && nohup gzip < :trc_file > :zip_file &
set define &
alter session set max_dump_file_size = unlimited
/
rem -----------------------------------------------------------------------
rem Filename: archdist.sql
rem Purpose: Tabular display of redo-log archiving history (logs/hour)
rem - Can only run from sqlplus
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set pagesize 50000
set veri off
set colsep ""
set termout off
def time="time" -- Oracle7
col time new_value time
select 'to_char(first_time,''DD/MM/YY HH24:MI:SS'')' time
from dual
where &&_O_RELEASE like '8%' -- Oracle8
/
set termout on
select substr(&&time, 1, 5) day,
to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'99') "23"
from sys.v_$log_history
group by substr(&&time,1,5)
/
set colsep " "
rem -----------------------------------------------------------------------
rem Filename: ctlimits.sql
rem Purpose: List control file structures with usage limits
rem Date: 21-Sep-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set pages 50000
col PCT_USED format 990.09
-- Controlfile creation parameters:
-- Type DATAFILE is for MAXDATAFILES
-- Type REDO LOG is for MAXLOGFILES
-- Type LOG HISTORY is for MAXLOGHISTORY
-- Type REDO THREAD is for MAXINSTANCES
-- No entry for MAXLOGMEMBERS (?)
select type, records_used, records_total,
records_used/records_total*100 "PCT_USED"
from sys.v_$controlfile_record_section
/
rem -----------------------------------------------------------------------
rem Filename: db-error.sql
rem Purpose: Log all database errors to a table
rem Oracle8i or above/ DBA or CREATE ANY TRIGGER privs/ and
rem GRANT SELECT ON SYS.V_$SESSION required
rem Date: 21-Mar-2000
rem Author: Nico Booyse (booysen@saps.org.za)
rem -----------------------------------------------------------------------
drop trigger log_errors_trig;
drop table log_errors_tab;
create table log_errors_tab (
error varchar2(30),
timestamp date,
username varchar2(30),
osuser varchar2(30),
machine varchar2(64),
process varchar2(8),
program varchar2(48));
create or replace trigger log_errors_trig
after servererror on database
declare
var_user varchar2(30);
var_osuser varchar2(30);
var_machine varchar2(64);
var_process varchar2(8);
var_program varchar2(48);
begin
select username, osuser, machine, process, program
into var_user, var_osuser, var_machine, var_process, var_program
from sys.v_$session
where audsid = userenv('sessionid');
insert into log_errors_tab
values(dbms_standard.server_error(1),sysdate,var_user,
var_osuser,var_machine,var_process,var_program);
end;
/
rem -----------------------------------------------------------------------
rem Filename: db-trig.sql
rem Purpose: Demonstrate database triggers (available from Oracle 8i)
rem Need DBA or CREATE ANY TRIGGER privs
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
create or replace trigger restrict_login
after logon on database
-- after logoff on database
-- after servererror on database
-- after startup on database
-- after shutdown on database
-- after create on database
-- after drop on database
-- after alter on database
declare
flag number := 0;
begin
select 1 into flag from sys.v_$session where program like '%sqlplus%';
if flag = 1 then
raise_application_error(-20000, 'No access from sqlplus');
end if;
end;
/
show errors
CREATE OR REPLACE TRIGGER startup_db
after startup on database
begin
dbms_shared_pool.keep ('SYS.STANDARD','P');
dbms_shared_pool.keep ('SYS.DBMS_STANDARD','P');
end;
/
show errors
rem -----------------------------------------------------------------------
rem Filename: rsrc.sql
rem Purpose: Demonstrate resource manager capabilities (limit CPU,
rem degree and sessions, available from Oracle 8i)
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
---------------------------------------------------------------------------
-- Create plan with consumer groups
---------------------------------------------------------------------------
exec dbms_resource_manager.create_pending_area;
exec dbms_resource_manager.delete_plan_cascade('night_plan');
exec dbms_resource_manager.create_plan('night_plan', 'Plan to use after 6PM');
exec dbms_resource_manager.create_consumer_group('batch', 'Group for batch reports');
exec dbms_resource_manager.create_plan_directive('night_plan', 'batch', 'Rules for overnight batch jobs', -
cpu_p1 => 75, parallel_degree_limit_p1 => 20);
exec dbms_resource_manager.create_plan_directive('night_plan', 'OTHER_GROUPS', 'Rules for overnight batch jobs', -
cpu_p1 => 25, parallel_degree_limit_p1 => 0, -
max_active_sess_target_p1 => 1);
exec dbms_resource_manager.validate_pending_area;
exec dbms_resource_manager.submit_pending_area;
---------------------------------------------------------------------------
-- List plans and consumer groups
---------------------------------------------------------------------------
set pages 50000
col plan format a12
col status format a7
col cpu_p1 format 999
col cpu_p2 format 999
col cpu_p3 format 999
col group_or_subplan format a17
col parallel_degree_limit_p1 format 999
select plan, num_plan_directives, status, mandatory from sys.dba_rsrc_plans;
select plan, group_or_subplan, cpu_p1, cpu_p2, cpu_p3, parallel_degree_limit_p1 as parallel, status
from sys.dba_rsrc_plan_directives
order by plan;
---------------------------------------------------------------------------
-- Switch a user to a new consumer group
---------------------------------------------------------------------------
exec dbms_resource_manager_privs.grant_switch_consumer_group('SCOTT', 'batch', FALSE);
exec dbms_resource_manager.set_initial_consumer_group('SCOTT', 'batch');
-- exec dbms_resource_manager.switch_consumer_group_for_user('SCOTT', 'batch'); -- Switch on-line users
select username, initial_rsrc_consumer_group from sys.dba_users where username = 'SCOTT';
---------------------------------------------------------------------------
-- Enable resource management for this instance
---------------------------------------------------------------------------
alter system set resource_manager_plan = 'NIGHT_PLAN';
---------------------------------------------------------------------------
-- Monitor the resource manager
---------------------------------------------------------------------------
col program format a40
select program, resource_consumer_group from sys.v_$session where username = 'SCOTT';
-- select * from sys.v_$rsrc_plan;
select * from sys.v_$rsrc_consumer_group;
rem -----------------------------------------------------------------------
rem Filename: logmgr.sql
rem Purpose: Log Miner: extract undo statements from online and archived
rem redo log files based on selection criteria.
rem Date: 21-Sep-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
-- Create a dictionary file
-- (init.ora parameter utl_file_dir must be set)
exec dbms_logmnr_d.build('mydictfile', '/tmp');
-- Register log files, can be from a different db
-- (NEWFILE=start new list/ ADDFILE=add next file)
exec dbms_logmnr.add_logfile(
LogFileName =>
'/app/oracle/arch/oradba/log_1_0000000027.oradba',
Options => dbms_logmnr.NEW);
exec dbms_logmnr.add_logfile(
LogFileName =>
'/app/oracle/arch/oradba/log_1_0000000028.oradba',
Options => dbms_logmnr.ADDFILE);
-- Start the logminer session
exec dbms_logmnr.start_logmnr(DictFileName => '/tmp/mydictfile');
-- Query v_$logmnr_contents view to extract required info
select timestamp, sql_undo
from sys.v_$logmnr_contents
where seg_name = 'EMPLOYEES';
-- Stop the logminer session
exec dbms_logmnr.end_logmnr;
rem -----------------------------------------------------------------------
rem Filename: cursors.sql
rem Purpose: Track database cursor usage
rem Date: 29-Nov-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
prompt Open Cursor Limit
col value format a30 head "Open Cursors Parameter Value"
select value
from sys.v_$parameter
where name = 'open_cursors';
prompt Summary of Current Curor Usage
col name format a25
select min(value) min, max(value) max, avg(value) avg
from sys.v_$sesstat
where statistic# = (select statistic#
from sys.v_$statname
where name like 'opened cursors current');
prompt Top 10 Users With Most Open Cursors
col program format a15 trunc
col osuser format a15 trunc
col username format a15 trunc
select * from (
select s.sid, s.username, s.osuser, s.program, v.value "Open
Cursors"
from sys.v_$sesstat v, sys.v_$session s
where v.sid = s.sid
and v.statistic# = (select statistic#
from sys.v_$statname
where name like 'opened cursors current')
order by v.value desc
)
where rownum < 11;
rem -----------------------------------------------------------------------
rem Filename: tabreorg.sql
rem Purpose: Show how a table can be reorganized on-line using the
rem DBMS_REDEFINITION package introduced in Oracle9i.
rem Date: 22-May-2003
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
---------------------------------------------------------------------
-- Connect as SYSDBA to grant privs to scott...
---------------------------------------------------------------------
connect / as sysdba
grant execute on dbms_redefinition to scott;
grant dba to scott;
---------------------------------------------------------------------
-- Create new empty interim table...
---------------------------------------------------------------------
connect scott/tiger
CREATE TABLE emp_work AS SELECT * FROM emp WHERE 1=2;
---------------------------------------------------------------------
-- Test if table can be redefined...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('scott', 'emp', 2);
---------------------------------------------------------------------
-- Start table redefinition...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('scott', 'emp', 'emp_work', NULL, 2);
--------------------------------------------------------------------
-- Add ALL constraints, indexes, triggers, grants, etc...
---------------------------------------------------------------------
ALTER TABLE emp ADD PRIMARY KEY (empno);
---------------------------------------------------------------------
-- Finish the redefinition process (this will swap the two tables)...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'emp', 'emp_work');
---------------------------------------------------------------------
-- Drop the interim working table...
---------------------------------------------------------------------
DROP TABLE emp_work;
Object Management Scripts
REM ########################################################################
REM ## Author : Sunil Kumar
REM ##
REM ## This script gives the following:
REM ## 1. The list of all foreign keys of the given table.
REM ## 2. The list of all foreign keys that references the given table.
REM ## 3. Self referential integrity constraints
REM ##
REM ## The output is in tabular format that contains the list of columns
REM ## that consists the foreign key.
REM #######################################################################
set linesize 110
set verify off
set pagesize 40
break on owner on table_name on constraint_name on r_constraint_name
column owner format a5
column r_owner format a5
column column_name format a12
column tt noprint
column position heading P format 9
column table_name format a15
column r_table_name format a15
column constraint_name format a15
column r_constraint_name format a15
select
a.tt,
a.owner,
b.table_name,
a.constraint_name,
b.column_name,
b.position,
a.r_constraint_name,
c.column_name,
c.position,
c.table_name r_table_name,
a.r_owner
from
(select
owner,
constraint_name,
r_constraint_name,
r_owner,1 tt
from
dba_constraints
where
owner=upper('&&owner')
and table_name=upper('&&table_name')
and constraint_type!='C'
union
select
owner,
constraint_name,
r_constraint_name,
r_owner,2
from
dba_constraints
where
(r_constraint_name,r_owner) in
(select
constraint_name,
owner
from
dba_constraints
where
owner=upper('&owner')
and table_name=upper('&table_name'))
) a,
dba_cons_columns b,
dba_cons_columns c
where
b.constraint_name=a.constraint_name
and b.owner=a.owner
and c.constraint_name=a.r_constraint_name
and c.owner=a.r_owner
and b.position=c.position
order by 1,2,3,4,5
/
set verify on
clear columns
clear breaks
undef owner
undef table_name
-- ======================================================
-- Script to identify everything to do with a table.
--
-- This includes a DESC equivalent, sizing information, Triggers, Constraints, Granted priviliges
-- that are associated with a table or from other tables foreign keys that reference
-- that table.
--
-- Instructions
-- ============
-- Either run this script logged on to SYS or SYSTEM or GRANT SELECT on the DICTIONARY
-- TABLES:
--
-- DBA_TAB_COLUMNS
-- V$DATABASE
-- DBA_TABLES
-- DBA_EXTENTS
-- DBA_CONS_COLUMNS
-- DBA_CONSTRAINTS
-- DBA_TRIGGERS
-- DBA_TAB_PRIVS
-- DBA_COL_PRIVS
--
-- At SQL*PLUS You will be requested to enter the schema owner and the tablename.
-- If you want a count of the number of rows in that table you will need to manually
-- edit this file beforehand.
--
--
-- Mark Searle
-- Searle Database Systems Ltd - marksearle@mistral.co.uk
-- FILE NAME: DISPLAY_.SQL
-- Last Modified 10/01/97
--
--======================================================
-- ================================================
--
-- INSTRUCTIONS FOR MODIFICATION
-- =============================
--
-- SHOW THE DATABASE AND USER THAT YOU ARE LOGGED ONTO.
-- ========================================================
SELECT * FROM SYS.V_$DATABASE;
SHOW USER
SET ECHO ON FEED ON ARRAYSIZE 1 LONG 5000 VERIFY OFF TIMING ON
-- GET ALL TABLE DETAILS
-- =====================
-- =====================
-- If ANALYZE STATISTICS has not been run then count number of rows
-- in table.
-- =====================================================================
--O N L Y E D I T I F N E E D T H E C O U N T (*)
--??????????????????????????????????????????????????????
-- SELECT COUNT(*) FROM OPS$ISSPOWN.STANDARD_TEST_RESULT;
-- Show the Table Structure
-- ========================
COLUMN POS FORMAT 999 heading "POS"
COLUMN PCT_FREE FORMAT A4 heading "Null"
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, COLUMN_ID POS
FROM SYS.DBA_TAB_COLUMNS
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table')
ORDER BY COLUMN_ID;
-- Show Physical Attributes
-- ========================
COLUMN PCT_FREE FORMAT 999 heading "%|Free"
COLUMN PCT_INCREASE FORMAT 999 heading "%|Incr"
COLUMN INITIAL_EXTENT FORMAT 999999999 heading "Init|Extent"
COLUMN NEXT_EXTENT FORMAT 9999999999999 heading "Next|Extent"
COLUMN MAX_EXTENTS FORMAT 999 heading "Max|Ext"
COLUMN AVG_ROW_LEN FORMAT 99999 heading "Avg|Row|Len"
SELECT PCT_FREE,
PCT_INCREASE,
INITIAL_EXTENT,
NEXT_EXTENT,
MAX_EXTENTS,
NUM_ROWS,
AVG_ROW_LEN
FROM SYS.DBA_TABLES
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table');
-- Show the actual Maximum Size of a Row
-- ==============================
SELECT SUM(DATA_LENGTH)
FROM SYS.DBA_TAB_COLUMNS
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table');
-- Show the Number of Physical EXTENTS that have been allocated Attributes
-- ========================================================
COLUMN SEGMENT_NAME FORMAT A30 HEADING 'Table Name'
COLUMN COUNTER FORMAT 9999 HEADING 'Number Of Extents Used'
SELECT SEGMENT_NAME, COUNT(*) COUNTER
FROM SYS.DBA_EXTENTS
WHERE OWNER = upper('&&owner')
AND SEGMENT_NAME = upper('&&table')
GROUP BY SEGMENT_NAME;
COLUMN TABSIZE FORMAT 999999999999 HEADING 'Table Size In Bytes'
-- Show the Physical SIZE IN BYTES of the TABLE
-- =====================================
SELECT SEGMENT_NAME, SUM(BYTES) TABSIZE
FROM SYS.DBA_EXTENTS
WHERE OWNER = upper('&&owner')
AND SEGMENT_NAME = upper('&&table')
GROUP BY SEGMENT_NAME;
-- GET ALL THE INDEX DETAILS
-- =========================
-- =========================
-- Show all the indexes and their columns for this table
-- =====================================================
COLUMN OWNER FORMAT A8 heading "Index|Owner"
COLUMN TABLE_OWNER FORMAT A8 heading "Table|Owner"
COLUMN INDEX_NAME FORMAT A30 heading "Index Name"
COLUMN COLUMN_NAME FORMAT A30 heading "Column Name"
COLUMN COLUMN_POSITION FORMAT 9999 heading "Pos"
BREAK ON CONSTRAINT_NAME SKIP PAGE
SELECT IND.OWNER,
IND.TABLE_OWNER,
IND.INDEX_NAME,
IND.UNIQUENESS,
COL.COLUMN_NAME,
COL.COLUMN_POSITION
FROM SYS.DBA_INDEXES IND,
SYS.DBA_IND_COLUMNS COL
WHERE IND.TABLE_NAME = upper('&&table')
AND IND.TABLE_OWNER = upper('&&owner')
AND IND.TABLE_NAME = COL.TABLE_NAME
AND IND.OWNER = COL.INDEX_OWNER
AND IND.TABLE_OWNER = COL.TABLE_OWNER
AND IND.INDEX_NAME = COL.INDEX_NAME;
-- Display all the physical details of the Primary and Other
-- Indexes for this table
-- =========================================================
COLUMN OWNER FORMAT A8 heading "Index|Owner"
COLUMN TABLE_OWNER FORMAT A8 heading "Table|Owner"
COLUMN INDEX_NAME FORMAT A30 heading "Index Name"
COLUMN COLUMN_NAME FORMAT A30 heading "Column Name"
COLUMN COLUMN_POSITION FORMAT 9999 heading "Pos"
COLUMN PCT_FREE FORMAT 999 heading "%|Free"
COLUMN PCT_INCREASE FORMAT 999 heading "%|Incr"
COLUMN INITIAL_EXTENT FORMAT 999999999 heading "Init|Extent"
COLUMN NEXT_EXTENT FORMAT 999999999 heading "Next|Extent"
COLUMN MAX_EXTENTS FORMAT 999 heading "Max|Ext"
SELECT
IND.OWNER,
IND.TABLE_OWNER,
IND.INDEX_NAME,
IND.UNIQUENESS,
COL.COLUMN_NAME,
COL.COLUMN_POSITION,
IND.PCT_FREE,
IND.PCT_INCREASE,
IND.INITIAL_EXTENT,
IND.NEXT_EXTENT,
IND.MAX_EXTENTS
FROM DBA_INDEXES IND,
DBA_IND_COLUMNS COL
WHERE IND.TABLE_NAME = upper('&&table')
AND IND.TABLE_OWNER = upper('&&owner')
AND IND.TABLE_NAME = COL.TABLE_NAME
AND IND.OWNER = COL.INDEX_OWNER
AND IND.TABLE_OWNER = COL.TABLE_OWNER
AND IND.INDEX_NAME = COL.INDEX_NAME;
--
-- GET ALL THE CONSTRAINT DETAILS
-- ==============================
-- ==============================
-- Show the Non-Foreign Keys Constraints on this table
-- ====================================================================
COLUMN OWNER FORMAT A9 heading "Owner"
COLUMN CONSTRAINT_NAME FORMAT A30 heading "Constraint|Name"
COLUMN R_CONSTRAINT_NAME FORMAT A30 heading "Referenced|Constraint|Name"
COLUMN DELETE_RULE FORMAT A9 heading "Del|Rule"
COLUMN TABLE_NAME FORMAT A18 heading "Table Name"
COLUMN COLUMN_NAME FORMAT A30 heading "Column Name"
--COLUMN CONSTRAINT_TYPE FORMAT A4 heading "Type"
--COLUMN POSITION ALIAS POS
--COLUMN POSITION 9999 heading "Pos"
COLUMN POSITION FORMAT 9999 heading "Pos"
BREAK ON CONSTRAINT_NAME SKIP PAGE
SELECT COL.OWNER,
COL.CONSTRAINT_NAME,
COL.COLUMN_NAME,
COL.POSITION,
-- CON.CONSTRAINT_TYPE
DECODE (CON.CONSTRAINT_TYPE,
'P','primary','R','foreign','U','unique','C','check') "Type"
FROM DBA_CONS_COLUMNS COL,
DBA_CONSTRAINTS CON
WHERE COL.OWNER = upper('&&owner')
AND COL.TABLE_NAME = upper('&&table')
AND CONSTRAINT_TYPE <> 'R'
AND COL.OWNER = CON.OWNER
AND COL.TABLE_NAME = CON.TABLE_NAME
AND COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME
ORDER BY COL.CONSTRAINT_NAME, COL.POSITION;
-- Show the Foreign Keys on this table pointing at other tables Primary
-- Key Fields for referential Integrity purposes.
-- ====================================================================
SELECT CON.OWNER,
CON.TABLE_NAME,
CON.CONSTRAINT_NAME,
CON.R_CONSTRAINT_NAME,
CON.DELETE_RULE,
COL.COLUMN_NAME,
COL.POSITION,
-- CON1.OWNER,
CON1.TABLE_NAME "Ref Tab",
CON1.CONSTRAINT_NAME "Ref Const"
-- COL1.COLUMN_NAME "Ref Column",
-- COL1.POSITION
--FROM DBA_CONS_COLUMNS COL,
FROM DBA_CONSTRAINTS CON1,
DBA_CONS_COLUMNS COL,
DBA_CONSTRAINTS CON
WHERE CON.OWNER = upper('&&owner')
AND CON.TABLE_NAME = upper('&&table')
AND CON.CONSTRAINT_TYPE = 'R'
AND COL.OWNER = CON.OWNER
AND COL.TABLE_NAME = CON.TABLE_NAME
AND COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME
-- Leave out next line if looking for other Users with Foriegn Keys.
AND CON1.OWNER = CON.OWNER
AND CON1.CONSTRAINT_NAME = CON.R_CONSTRAINT_NAME
AND CON1.CONSTRAINT_TYPE IN ( 'P', 'U' );
-- The extra DBA_CONS_COLUMNS will give details of refered to columns,
-- but has a multiplying effect on the query results.
-- NOTE: Could use temporary tables to sort out.
--AND COL1.OWNER = CON1.OWNER
--AND COL1.TABLE_NAME = CON1.TABLE_NAME
--AND COL1.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME;
-- Show the Foreign Keys pointing at this table via the recursive call
-- to the Constraints table.
-- ================================================================
SELECT CON1.OWNER,
CON1.TABLE_NAME,
CON1.CONSTRAINT_NAME,
CON1.DELETE_RULE,
CON1.STATUS,
CON.TABLE_NAME,
CON.CONSTRAINT_NAME,
COL.POSITION,
COL.COLUMN_NAME
FROM DBA_CONSTRAINTS CON,
DBA_CONS_COLUMNS COL,
DBA_CONSTRAINTS CON1
WHERE CON.OWNER = upper('&&owner')
AND CON.TABLE_NAME = upper('&&table')
AND ((CON.CONSTRAINT_TYPE = 'P') OR (CON.CONSTRAINT_TYPE = 'U'))
AND COL.TABLE_NAME = CON1.TABLE_NAME
AND COL.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME
AND CON1.OWNER = CON.OWNER
AND CON1.R_CONSTRAINT_NAME = CON.CONSTRAINT_NAME
AND CON1.CONSTRAINT_TYPE = 'R'
GROUP BY CON1.OWNER,
CON1.TABLE_NAME,
CON1.CONSTRAINT_NAME,
CON1.DELETE_RULE,
CON1.STATUS,
CON.TABLE_NAME,
CON.CONSTRAINT_NAME,
COL.POSITION,
COL.COLUMN_NAME;
--
-- Show all the check Constraints
-- ==========================================================
SET HEADING OFF
SELECT 'alter table ', TABLE_NAME, ' add constraint ',
CONSTRAINT_NAME, ' check ( ', SEARCH_CONDITION, ' ); '
FROM DBA_CONSTRAINTS
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table')
AND CONSTRAINT_TYPE = 'C';
--
-- Show all the Triggers that have been created on this table
-- ==========================================================
-- add query to extract Trigger Body etcc WHEN CLAUSE here.
SET ARRAYSIZE 1
SET LONG 6000000
SELECT OWNER,
'CREATE OR REPLACE TRIGGER ',
TRIGGER_NAME,
DESCRIPTION,
TRIGGER_BODY,
'/'
FROM DBA_TRIGGERS
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table');
--
-- Show all the GRANTS made on this table and it's columns.
-- ========================================================
-- Table 1st
-- =========
SELECT 'GRANT ',
PRIVILEGE,
' ON ',
TABLE_NAME,
' TO ',
GRANTEE,
';'
FROM DBA_TAB_PRIVS
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table');
-- Columns 2nd
-- ===========
SELECT 'GRANT ',
PRIVILEGE,
' ( ',
COLUMN_NAME,
' ) ',
' ON ',
TABLE_NAME,
' TO ',
GRANTEE,
';'
FROM DBA_COL_PRIVS
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table');
SET HEADING ON
--EXIT
rem -----------------------------------------------------------------------
rem Filename: cmpall.sql
rem Purpose: Compile all invalid database objects in a schema
rem Run this script after each database upgrade or whenever
rem required.
rem Notes: This script akes quite a while to run. Use it after changes
rem in a package to recompile the dependent packages.
rem Date: 23-Jan-1999
rem Author: Kai-Hagen Wirth
rem -----------------------------------------------------------------------
-- Change
-- ^ ^
-- | |
EXEC DBMS_UTILITY.COMPILE_SCHEMA( 'schema-name' );
--
-- Later releases of Oracle also includes a script to recompile invalid objects:
-- SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
--
rem -----------------------------------------------------------------------
rem Filename: cmpall2.sql
rem Purpose: Compile all invalid database objects
rem Run this script after each database upgrade or whenever
rem required.
rem Notes: If the dependencies between objects are complicated, you can
rem end up re-compiling it many times, as recompiling some
rem invalidates others. This script uses dyamic SQL, and
rem recompile objects based on reverse dependency order.
rem Date: 24-Sep-1998
rem Author: Fiona Lindsay
rem -----------------------------------------------------------------------
set serveroutput on size 1000000
declare
sql_statement varchar2(200);
cursor_id number;
ret_val number;
begin
dbms_output.put_line(chr(0));
dbms_output.put_line('Re-compilation of Invalid Objects');
dbms_output.put_line('---------------------------------');
dbms_output.put_line(chr(0));
for invalid in (select object_type, owner, object_name
from sys.dba_objects o,
sys.order_object_by_dependency d
where o.object_id = d.object_id(+)
and o.status = 'INVALID'
and o.object_type in ('PACKAGE', 'PACKAGE BODY',
'FUNCTION',
'PROCEDURE', 'TRIGGER',
'VIEW')
order by d.dlevel desc, o.object_type) loop
if invalid.object_type = 'PACKAGE BODY' then
sql_statement := 'alter package '||invalid.owner||'.'||invalid.object_name||
' compile body';
else
sql_statement := 'alter '||invalid.object_type||' '||invalid.owner||'.'||
invalid.object_name||' compile';
end if;
/* now parse and execute the alter table statement */
cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(cursor_id, sql_statement, dbms_sql.native);
ret_val := dbms_sql.execute(cursor_id);
dbms_sql.close_cursor(cursor_id);
dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||
invalid.object_name, 32)||' : compiled');
end loop;
end;
/
rem -----------------------------------------------------------------------
rem Filename: cmpindx.sql
rem Purpose: Compare indexes on two databases and list the differences
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set pagesize 50000 veri off echo off
col table_name format a30
col column_name format a30
col column_position format 990
accept DB1 prompt "Enter database name to compare from: "
accept DB2 prompt "Enter database name to compare to: "
spool cmpindx.lst
prompt ======================================================================
prompt == Tables/ columns with indexes on &DB1 that's not on &DB2
prompt ======================================================================
select table_owner||'.'||table_name table_name, column_position, column_name
from dba_ind_columns@&DB1.
where table_owner not in ('SYS', 'SYSTEM')
minus
select table_owner||'.'||table_name table_name, column_position, column_name
from dba_ind_columns@&DB2.
where table_owner not in ('SYS', 'SYSTEM')
/
prompt ======================================================================
prompt == Tables/ columns with indexes on &DB2 that's not on &DB1
prompt ======================================================================
select table_owner||'.'||table_name table_name, column_position, column_name
from dba_ind_columns@&DB2.
where table_owner not in ('SYS', 'SYSTEM')
minus
select table_owner||'.'||table_name table_name, column_position, column_name
from dba_ind_columns@&DB1.
where table_owner not in ('SYS', 'SYSTEM')
/
spool off
rem -----------------------------------------------------------------------
rem Filename: idxrecr8.sql
rem Purpose: Script to spool a listing of all drop and create statements
rem required to rebuild indexes.
rem Notes: Will build all non-sys/system indexes
rem Date: 10-Oct-1998
rem Author: Anonymous
rem -----------------------------------------------------------------------
set linesize 80;
set pagesize 10000;
set long 50;
set trimspool on;
set termout off;
set echo off;
set feedback off;
col dummy noprint format a1;
col dummy2 noprint format a1;
col index_name noprint format a1;
col command format a80 wrap word;
spool bld_ndx2.rslt
/* ********************************* */
/* 1. Generate drop index commands */
/* 2. Generate create index commands */
/* ********************************* */
select 'drop index '||owner||'.'||index_name||';' command,
0 dummy,
index_name,
0 dummy2
from all_indexes
where table_owner NOT in ('SYS', 'SYSTEM')
UNION
select 'create'||decode(UNIQUENESS, 'UNIQUE', ' UNIQUE', NULL)||'
index '||owner||'.'||index_name||' on '
||table_owner||'.'||table_name||' (' command,
1 dummy,
index_name,
0 dummy2
from all_indexes
where table_owner NOT in ('SYS', 'SYSTEM')
UNION
select ' '||decode(column_position,1,null,',')||column_name||' ASC' command,
2 dummy,
index_name,
column_position dummy2
from all_ind_columns
where table_owner NOT in ('SYS', 'SYSTEM') AND
all_ind_columns.index_name in
(select index_name
from all_indexes
where table_owner NOT in ('SYS', 'SYSTEM'))
UNION
select ' )' command,
3 dummy,
index_name,
0 dummy2
from all_ind_columns
where table_owner NOT in ('SYS', 'SYSTEM') AND
all_ind_columns.index_name in
(select index_name
from all_indexes
where table_owner NOT in ('SYS', 'SYSTEM'))
UNION
select ' pctfree '||pct_free||'
initrans '||ini_trans||'
maxtrans '||max_trans||'
storage (initial '||initial_extent||'
next '||next_extent||'
minextents '||min_extents||'
maxextents '||max_extents||'
pctincrease '||pct_increase||'
) tablespace '||tablespace_name||';' command,
4 dummy,
index_name,
0 dummy2
from all_indexes
where table_owner NOT in ('SYS', 'SYSTEM')
order by 3,2,4
/
spool off;
set heading on;
set pagesize 100;
set termout on;
rem -----------------------------------------------------------------------
rem Filename: idxrebld.sql
rem Purpose: Re-build all user indexes on-line
rem Notes: - Run this script from sqlplus as a DBA user.
rem Date: 10-Oct-1998, updated on 04-Mar-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
rem -----------------------------------------------------------------------
rem Note: you need to run "ALTER INDEX ....LOGGING;" right after this
rem script if you have a standby database, otherwise it will be
rem invalidated - Branko Milosevic
rem -----------------------------------------------------------------------
set serveroutput on size 1000000
set line 1024 feed off trimspool on echo off
spool index_rebuild_run.sql
declare
INDEX_TABLESPACE_NAME constant varchar2(30) := 'INDX'; -- Set to your Index TS!!!
begin
for c1 in (
select i.owner, i.index_name, s.tablespace_name,
i.initial_extent, i.next_extent, i.min_extents, i.max_extents,
i.pct_increase, s.bytes
from sys.dba_segments s, sys.dba_indexes i
where s.segment_type = 'INDEX'
and i.index_name = s.segment_name
and i.owner not in ('SYSTEM','SYS','DBVISION','MONITOR','TUNING') )
loop
if c1.tablespace_name not like '%'||INDEX_TABLESPACE_NAME||'%' then
-- Make sure we use the right INDEX tablespace
c1.tablespace_name := INDEX_TABLESPACE_NAME;
end if;
if c1.pct_increase not in (0, 100) then -- Set PCTINCREASE to 0
c1.pct_increase := 0;
end if;
if c1.bytes/1024/1024 > 200 then -- Largest extent will be 200K
c1.bytes := 200*1024*1024;
end if;
if c1.max_extents < 121 then -- Set MAXEXTENTS=121 if smaller than 121
c1.max_extents := 121;
end if;
dbms_output.put_line('ALTER INDEX '||c1.owner||'.'||c1.index_name||
' REBUILD ONLINE NOLOGGING'||
' TABLESPACE '||c1.tablespace_name||' STORAGE ('||
' INITIAL '||to_char(c1.bytes)||
' NEXT '||to_char(c1.bytes)||
' PCTINCREASE '||c1.pct_increase||' MAXEXTENTS '||c1.max_extents||');');
dbms_output.put_line('ALTER TABLESPACE '||INDEX_TABLESPACE_NAME||' COALESCE;');
end loop;
end;
/
spool off
-- Run the generated script
set echo on time on timing on feed on
spool index_rebuild
@@index_rebuild_run
spool off
! rm index_rebuild_run.sql
exit
rem -----------------------------------------------------------------------
rem Filename: Create_Copy.sql
rem Purpose: Copy table from one database to another
rem Notes: Creates a sql script file to easily copy a table from one
database to another. The created script file disables all the
constraints to and from the destination table. It also
disables any triggers on the destination table. It then
truncates the destination table and executes a copy command
to copy the contents from the source database to the
destination database. It then enables all the constraints
and triggers associated with the table.
rem Warning: Use with extreme caution!!! Remember to run Tab_copy.sql.
rem Date: 02-Sep-2000
rem Author: Trevor Vermaak
rem -----------------------------------------------------------------------
-- STEPS:
--
-- Disable constraints
-- Disable triggers
-- Copy
-- Enable constraints
-- Enable triggers
SET SERVEROUTPUT ON
PROMPT Enter the table's name you want to copy
DEFINE tname = &table_name
PROMPT
PROMPT Enter the FROM database id
DEFINE dbname = &database_id
PROMPT
PROMPT Enter the FROM user id
DEFINE uname = &user_id
SPOOL TAB_COPY.SQL
DECLARE
v_table_name VARCHAR2( 30) := '&&tname';
v_user_name VARCHAR2( 30) := '&&uname';
v_db_name VARCHAR2( 30) := '&&dbname';
BEGIN
dbms_output.enable( 100000);
v_table_name := UPPER( v_table_name);
v_user_name := UPPER( v_user_name);
v_db_name := UPPER( v_db_name);
-- Disable the constraints from other tables
FOR c1 IN (SELECT con.constraint_name,
con.status
FROM user_constraints con
WHERE con.table_name = v_table_name
AND con.constraint_type = 'R'
)
LOOP
IF c1.status = 'ENABLED' THEN
dbms_output.put_line
( 'PROMPT Disable constraint '|| c1.constraint_name||
' on table '|| v_table_name
);
dbms_output.put_line
( 'ALTER TABLE '|| v_table_name|| ' DISABLE CONSTRAINT '||
c1.constraint_name
);
dbms_output.put_line( '/');
ELSE
dbms_output.put_line
( 'PROMPT Constraint '|| c1.constraint_name||
' on table '|| v_table_name||
' is already DISABLED.'
);
dbms_output.put_line
( 'REM ALTER TABLE '|| v_table_name|| ' DISABLE CONSTRAINT '||
c1.constraint_name
);
END IF;
END LOOP;
-- Disable the constraints to the table on other tables
FOR c1 IN (SELECT con.table_name,
con.constraint_name,
con.status
FROM user_constraints con
WHERE con.r_constraint_name IN
(SELECT con1.constraint_name
FROM user_constraints con1
WHERE con1.table_name = v_table_name
AND con1.constraint_type IN ('P', 'U'))
AND con.constraint_type = 'R'
)
LOOP
IF c1.status = 'ENABLED' THEN
dbms_output.put_line
( 'PROMPT Disable constraint '|| c1.constraint_name||
' on table '|| c1.table_name
);
dbms_output.put_line
( 'ALTER TABLE '|| c1.table_name|| ' DISABLE CONSTRAINT '||
c1.constraint_name
);
dbms_output.put_line( '/');
ELSE
dbms_output.put_line
( 'PROMPT Constraint '|| c1.constraint_name||
' on table '|| c1.table_name||
' is already DISABLED.'
);
dbms_output.put_line
( 'REM ALTER TABLE '|| c1.table_name|| ' DISABLE CONSTRAINT '||
c1.constraint_name
);
END IF;
END LOOP;
-- Disable the triggers on the table
FOR c1 IN (SELECT trg.trigger_name,
trg.status
FROM user_triggers trg
WHERE trg.table_name = v_table_name
)
LOOP
IF c1.status = 'ENABLED' THEN
dbms_output.put_line
( 'PROMPT Disable trigger '|| c1.trigger_name||
' on table '|| v_table_name
);
dbms_output.put_line
( 'ALTER TRIGGER '|| c1.trigger_name|| ' DISABLE'
);
dbms_output.put_line( '/');
ELSE
dbms_output.put_line( 'PROMPT Trigger '|| c1.trigger_name||
' on table '|| v_table_name||
' is already DISABLED.');
END IF;
END LOOP;
dbms_output.put_line( 'PROMPT Truncate table '|| v_table_name);
dbms_output.put_line( 'TRUNCATE TABLE '|| v_table_name);
dbms_output.put_line( '/');
-- dbms_output.put_line( 'PROMPT Delete the table');
-- dbms_output.put_line( 'DELETE '|| v_table_name);
-- dbms_output.put_line( '/');
dbms_output.put_line( 'PROMPT Copy the table from '|| v_db_name);
dbms_output.put_line( 'COPY FROM '|| v_user_name|| '@'|| v_db_name|| ' -');
dbms_output.put_line( 'INSERT '|| v_table_name ||' USING -');
dbms_output.put_line( 'SELECT * FROM '|| v_table_name);
-- dbms_output.put_line( 'SELECT '|| v_table_name);
-- FOR c1 IN (SELECT utcol.column_name
-- FROM user_tab_columns utcol
-- WHERE utcoltable_name = v_table_name)
-- LOOP
-- dbms_output.put_line( utcol.column_name|| ', -');
-- END LOOP
-- dbms_output.put_line( 'FROM '|| v_table_name);
--
-- Enable the constraints from other tables
FOR c1 IN (SELECT con.constraint_name,
con.status,
con2.table_name
FROM all_constraints con,
all_constraints con2
WHERE con.table_name = v_table_name
AND con.constraint_type = 'R'
AND con.r_constraint_name = con2.constraint_name
)
LOOP
dbms_output.put_line
( 'PROMPT Enable constraint '|| c1.constraint_name||
' to table '|| c1.table_name
);
dbms_output.put_line
( 'PROMPT on table '|| v_table_name
);
dbms_output.put_line
( 'ALTER TABLE '|| v_table_name|| ' ENABLE CONSTRAINT '||
c1.constraint_name
);
dbms_output.put_line( '/');
END LOOP;
-- Enable the constraints to the table on other tables
FOR c1 IN (SELECT con.table_name,
con.constraint_name,
con.status
FROM user_constraints con
WHERE con.r_constraint_name IN
(SELECT con1.constraint_name
FROM user_constraints con1
WHERE con1.table_name = v_table_name
AND con1.constraint_type IN ('P', 'U'))
AND con.constraint_type = 'R'
)
LOOP
dbms_output.put_line
( 'PROMPT Enable constraint '|| c1.constraint_name||
' on table '|| c1.table_name
);
dbms_output.put_line
( 'ALTER TABLE '|| c1.table_name|| ' ENABLE CONSTRAINT '||
c1.constraint_name
);
dbms_output.put_line( '/');
END LOOP;
-- Enable the triggers on the table
FOR c1 IN (SELECT trg.trigger_name,
trg.status
FROM user_triggers trg
WHERE trg.table_name = v_table_name
)
LOOP
dbms_output.put_line
( 'PROMPT Enable trigger '|| c1.trigger_name||
' on table '|| v_table_name
);
dbms_output.put_line
( 'ALTER TRIGGER '|| c1.trigger_name|| ' ENABLE'
);
dbms_output.put_line( '/');
END LOOP;
END;
/
SPOOL OFF
rem -----------------------------------------------------------------------
rem Filename: dropcol.sql
rem Purpose: Demonstrate Oracle8i drop column functionality
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
-- drop table x
-- /
create table x(a date, b date, c date)
/
-- Drop column B:
alter table x set unused column b -- Mark col as UNUSED
/
select * from sys.dba_unused_col_tabs
/
alter table x drop unused columns
/
-- Drop column C (different method):
alter table x drop column c cascade constraints
/
rem -----------------------------------------------------------------------
rem Filename: SET_SEQUENCE.SQL
rem Purpose: SETS an existing sequence to a value of choice without
rem dropping it, which would make objects using it invalid. It
rem also maintains the other sequence attributes where it can.
rem Notes: USEAGE "exec set_sequence ('&seqname' , '&nextval');
rem Date: 22-Dec-2004
rem Author: Trevor Fairhurst, trevgf@yahoo.com
rem -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE set_sequence (seqname IN VARCHAR2, newnumber IN INTEGER) as
curr_val INTEGER;
curr_inc INTEGER;
curr_min INTEGER;
BEGIN
SELECT INCREMENT_BY, MIN_VALUE into curr_inc, curr_min from user_sequences where sequence_name = seqname;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seqname||' MINVALUE ' || LEAST((newnumber - curr_inc - 1) , curr_min) ;
EXECUTE IMMEDIATE 'SELECT ' ||seqname ||'.nextval FROM dual' INTO curr_val;
IF (newnumber - curr_val - curr_inc) != 0 THEN
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seqname||' INCREMENT BY '||(newnumber - curr_val - curr_inc);
END IF;
EXECUTE IMMEDIATE 'SELECT ' ||seqname ||'.nextval FROM dual' INTO curr_val;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seqname||' INCREMENT BY ' || curr_inc;
END set_sequence;
/
rem -----------------------------------------------------------------------
rem # File Name: mktable.sql
rem #
rem # Purpose: Script to dump table creation script
rem # for the username (schema) provided as
rem # the parameter.
rem #
rem # This is script is useful for cases where
rem # Reverse Engineering is required. The resulting
rem # SQL is sent to an output file:
rem #
rem # tbl_
rem #
rem -----------------------------------------------------------------------
set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 79
define 1 = &&SCHEMA_NAME
spool tbl_&&SCHEMA_NAME
set termout off
col x noprint
col y noprint
select 'rem **** Create Table DDL for '||chr(10)||
'rem **** '||username||''''||'s tables'||chr(10)||chr(10)
from dba_users
where username = upper ('&&1')
/
select table_name y,
0 x,
'create table ' ||
rtrim(table_name) ||
'('
from dba_tables
where owner = upper('&&1')
union
select tc.table_name y,
column_id x,
rtrim(decode(column_id,1,null,','))||
rtrim(column_name)|| ' ' ||
rtrim(data_type) ||
rtrim(decode(data_type,'DATE',null,'LONG',null,
'NUMBER',decode(to_char(data_precision),null,null,'('),
'(')) ||
rtrim(decode(data_type,
'DATE',null,
'CHAR',data_length,
'VARCHAR2',data_length,
'NUMBER',decode(to_char(data_precision),null,null,
to_char(data_precision) || ',' || to_char(data_scale)),
'LONG',null,
'******ERROR')) ||
rtrim(decode(data_type,'DATE',null,'LONG',null,
'NUMBER',decode(to_char(data_precision),null,null,')'),
')')) || ' ' ||
rtrim(decode(nullable,'N','NOT NULL',null))
from dba_tab_columns tc,
dba_objects o
where o.owner = tc.owner
and o.object_name = tc.table_name
and o.object_type = 'TABLE'
and o.owner = upper('&&1')
union
select table_name y,
999999 x,
')' || chr(10)
||' STORAGE(' || chr(10)
||' INITIAL ' || initial_extent || chr(10)
||' NEXT ' || next_extent || chr(10)
||' MINEXTENTS ' || min_extents || chr(10)
||' MAXEXTENTS ' || max_extents || chr(10)
||' PCTINCREASE '|| pct_increase || ')' ||chr(10)
||' INITRANS ' || ini_trans || chr(10)
||' MAXTRANS ' || max_trans || chr(10)
||' PCTFREE ' || pct_free || chr(10)
||' PCTUSED ' || pct_used || chr(10)
||' PARALLEL (DEGREE ' || DEGREE || ') ' || chr(10)
||' TABLESPACE ' || rtrim(tablespace_name) ||chr(10)
||'/'||chr(10)||chr(10)
from dba_tables
where owner = upper('&&1')
order by 1,2
/
rem -----------------------------------------------------------------------
rem # File Name: mkindex.sql
rem #
rem # Purpose: Script that produces index creation script
rem # Based on User Schema (user name specified).
rem # Input Value 1: The name of the user which to
rem # dump an index creation script.
rem #
rem # This is script is useful for cases where
rem # Reverse Engineering is required. The resulting
rem # SQL is sent to an output file:
rem #
rem # ind_
rem #
rem -----------------------------------------------------------------------
set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 79
define 1 = &&SCHEMA_NAME
spool ind_&&SCHEMA_NAME
set termout off
col y noprint
col x noprint
col z noprint
select 'rem **** Create Index DDL for '||chr(10)||
'rem **** '||username||''''||'s tables'||chr(10)||chr(10)
from dba_users
where username = upper ('&&1')
/
select table_name z,
index_name y,
-1 x,
'create ' || rtrim(decode(uniqueness,'UNIQUE','UNIQUE',null))
|| ' index ' ||
rtrim(index_name)
from dba_indexes
where table_owner = upper('&&1')
union
select table_name z,
index_name y,
0 x,
'on ' ||
rtrim(table_name) ||
'('
from dba_indexes
where table_owner = upper('&&1')
union
select table_name z,
index_name y,
column_position x,
rtrim(decode(column_position,1,null,','))||
rtrim(column_name)
from dba_ind_columns
where table_owner = upper('&&1')
union
select table_name z,
index_name y,
999999 x,
')' || chr(10)
||'unrecoverable ' || chr(10)
||'STORAGE(' || chr(10)
||'INITIAL ' || initial_extent || chr(10)
||'NEXT ' || next_extent || chr(10)
||'MINEXTENTS ' || '1' || chr(10)
||'MAXEXTENTS ' || max_extents || chr(10)
||'PCTINCREASE '|| '0' ||')' || chr(10)
||'INITRANS ' || ini_trans || chr(10)
||'MAXTRANS ' || max_trans || chr(10)
||'PCTFREE ' || '0' || chr(10)
||'TABLESPACE ' || tablespace_name ||chr(10)
||'PARALLEL (DEGREE ' || DEGREE || ') ' || chr(10)
||'/'||chr(10)||chr(10)
from dba_indexes
where table_owner = upper('&&1')
order by 1,2,3
/
rem -----------------------------------------------------------------------
rem Filename: mksnplgs.sql
rem Purpose: Create a script to rebuild all snapshot logs
rem Date: 12-Feb-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
conn /@&1
set feed off echo off pagesize 50000 trimspool on head off line 500
col global_name new_value global_name
select global_name from global_name
/
spool snap_&global_name..sql
select 'drop snapshot log on '||log_owner||'.'||master||';'
from sys.dba_snapshot_logs
/
select 'create snapshot log on '||log_owner||'.'||master||
' storage (initial 1M next 1M maxextents unlimited pctincrease 0);'
from sys.dba_snapshot_logs
/
select 'GRANT '||privilege||' ON '||owner||'.'||table_name
||' to '||grantee||';'
from sys.dba_tab_privs
where table_name like 'MLOG$%'
/
spool off
spool snap_&global_name..log
prompt Recreating snapshot logs for database &global_name.select 'Re
@snap_&global_name..sql
spool off
exit
rem -----------------------------------------------------------------------
rem Filename: mktrig.sql
rem Purpose: This script will create a SQL script to recreate all
rem triggers in the current schema. --
rem Date: 12-Feb-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set feedback off
set head off
set echo off
set recsep off
set pages 50000
set long 5000
set lines 200
column trigger_body format a9999 wrap word;
-- Write the script to a file, otherwise it is useless
spool mktrig.run
-- Write the SQL stastements to rebuild the triggers
select 'create or replace trigger ' || description, trigger_body,'/'
from user_triggers;
spool off;
-- Reset some stuff
set feedback on
set head on
set echo on
Space Management Scripts
o Show used/free space per tablespace
o Show used/free space per datafile
o Show used/free space per segment type
o Save summary of database space history over time
o List segments that can not extend (tablespace full)
o List objects in the SYSTEM tablespace that doesn't belong to SYS or SYSTEM
o Oracle segment sizing recommendations
o Show database growth in Meg per month for the last year
o List segments with more than 200 Meg of free DB Blocks
o List tables with high water mark not equal to used blocks
o Check whether a tablespace is fragmented and show fragmentation type
o Check index fragmentation status for a schema
rem -----------------------------------------------------------------------
rem Filename: tsspace.sql
rem Purpose: Show Used/free space in Meg by tablespace name
rem Author: Balaji Rajagopalan [try2xl@yahoo.com]
rem -----------------------------------------------------------------------
tti "Space Usage for Database in Meg"
SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/
tti off
REM ------------------------------------------------------------------------------
REM Filename: dffree.sql
REM Purpose: Shows current size, used & freespace within the datafiles.
REM Author: Kam Muhamad Sirajdin [mdsirajdin@yahoo.com]
REM ------------------------------------------------------------------------------
TTI "Allocated, Used & Free space within datafiles"
COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
TTI off
rem -----------------------------------------------------------------------
rem Filename: usedsp.sql
rem Purpose: Used space in Meg by segment type
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set pagesize 50000
set line 80
col "Total Used Meg" format 999,999,990
col "Data part" format 999,999,990
col "Index part" format 999,999,990
col "LOB part" format 999,999,990
col "RBS part" format 999,999,990
tti 'Used space in Meg by segment type'
select sum(bytes)/1024/1024 "Total Used",
sum( decode( substr(segment_type,1,5), 'TABLE', bytes/1024/1024, 0))
"Data part",
sum( decode( substr(segment_type,1,5), 'INDEX', bytes/1024/1024, 0))
"Index part",
sum( decode( substr(segment_type,1,3), 'LOB', bytes/1024/1024, 0))
"LOB part",
sum( decode(segment_type, 'ROLLBACK', bytes/1024/1024, 0))
"RBS part",
sum( decode(segment_type, 'TEMPORARY', bytes/1024/1024, 0))
"TEMP part"
from sys.dba_segments
/
tti off
tti "Total database size"
select sum(bytes)/1024/1024 "Total DB size in Meg"
from sys.v_$datafile
/
tti off
rem -----------------------------------------------------------------------
rem Filename: spacehist.sql
rem Purpose: Save summary of database space history over time
rem Notes: Set JOB_QUEUE_PROCESSES to a value > 0 or schedule from
rem an external scheduler (corn, at...)
rem Date: 15-May-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
---------------------------------------------------------------------------
-- Create history table...
---------------------------------------------------------------------------
drop table db_space_hist;
create table db_space_hist (
timestamp date,
total_space number(8),
used_space number(8),
free_space number(8),
pct_inuse number(5,2),
num_db_files number(5)
);
---------------------------------------------------------------------------
-- Stored proc to populate table...
---------------------------------------------------------------------------
create or replace procedure db_space_hist_proc as
begin
-- Delete old records...
delete from db_space_hist where timestamp > SYSDATE + 364;
-- Insert current utilization values...
insert into db_space_hist
select sysdate, total_space,
total_space-nvl(free_space,0) used_space,
nvl(free_space,0) free_space,
((total_space - nvl(free_space,0)) / total_space)*100 pct_inuse,
num_db_files
from ( select sum(bytes)/1024/1024 free_space
from sys.DBA_FREE_SPACE ) FREE,
( select sum(bytes)/1024/1024 total_space,
count(*) num_db_files
from sys.DBA_DATA_FILES) FULL;
commit;
end;
/
show errors
---------------------------------------------------------------------------
-- Schedule the job using the DB Job System. This section can be removed if
-- the job is sceduled via an external scheduler.
---------------------------------------------------------------------------
declare
v_job number;
begin
select job into v_job from user_jobs where what like 'db_space_hist_proc%';
dbms_job.remove(v_job);
dbms_job.submit(v_job, 'db_space_hist_proc;', sysdate,
'sysdate+7'); -- Run every 7 days
dbms_job.run(v_job);
dbms_output.put_line('Job '||v_job||' re-submitted.');
exception
when NO_DATA_FOUND then
dbms_job.submit(v_job, 'db_space_hist_proc;', sysdate,
'sysdate+7'); -- Run every 7 days
dbms_job.run(v_job);
dbms_output.put_line('Job '||v_job||' submitted.');
end;
/
---------------------------------------------------------------------------
-- Generate a space history report...
---------------------------------------------------------------------------
select to_char(timestamp, 'DD Mon RRRR HH24:MI') "Timestamp",
total_space "DBSize (Meg)",
used_space "Free (Meg)",
free_space "Used (Meg)",
pct_inuse "% Used",
num_db_files "Num DB Files"
from db_space_hist
order by timestamp;
rem -----------------------------------------------------------------------
rem Filename: exterror.sql
rem Purpose: Segments that will cause errors when they try to extent!!!
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
prompt Objects that cannot extend (no space in TS)
column Sname form a40 heading 'Object Name'
column Stype form a15 heading 'Type'
column Size form 9,999 heading 'Size'
column Next form 99,999 heading 'Next'
column Tname form a15 heading 'TsName'
select a.owner||'.'||a.segment_name "Sname",
a.segment_type "Stype",
a.bytes/1024/1024 "Size",
a.next_extent/1024/1024 "Next",
a.tablespace_name "TName"
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces
and next_extent * 1 > ( -- Cannot extend 1x, can change to 2x...
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name)
order by 3 desc
/
-- THIS QUERY GIVES THE SAME RESULTS, BUT IS WAY TOO SLOW
--
-- select a.owner, a.segment_name, b.tablespace_name,
-- decode(ext.extents,1,b.next_extent,
-- a.bytes*(1+b.pct_increase/100)) nextext,
-- freesp.largest
-- from dba_extents a,
-- dba_segments b,
-- (select owner, segment_name, max(extent_id) extent_id,
-- count(*) extents
-- from dba_extents
-- group by owner, segment_name
-- ) ext,
-- (select tablespace_name, max(bytes) largest
-- from dba_free_space
-- group by tablespace_name
-- ) freesp
-- where a.owner=b.owner
-- and a.segment_name=b.segment_name
-- and a.owner=ext.owner
-- and a.segment_name=ext.segment_name
-- and a.extent_id=ext.extent_id
-- and b.tablespace_name = freesp.tablespace_name
-- and decode(ext.extents,1,b.next_extent, a.bytes*(1+b.pct_increase/100)) > freesp.largest
-- /
rem -----------------------------------------------------------------------
rem Filename: tswrong.sql
rem Purpose: List objects in the SYSTEM tablespace that doesn't belong
rem to SYS or SYSTEM
rem Date: 12-Jan-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
select *
from sys.dba_segments
where owner not in ('PUBLIC', 'SYS', 'SYSTEM')
and tablespace_name = 'SYSTEM'
/
rem -----------------------------------------------------------------------
rem Filename: sizing.sql
rem Purpose: Give some segment sizing recommendations
rem Date: 04-Jul-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
prompt Database block size:
select to_number(value) "Block size in bytes"
from sys.v_$parameter
where name = 'db_block_size'
/
prompt Max number of possible extents (if not set to UNLIMITED)
prompt is db_block_size/16-7
select to_number(value)/16-7 "MaxExtents"
from sys.v_$parameter
where name = 'db_block_size'
/
prompt The recommended min extent size is a multiple of
prompt db_block_size * db_file_multiblock_read_count. This gives
prompt the chunks Oracle ask from the OS when doing read-ahead
prompt with full table scans.
select to_number(a.value) * to_number(b.value) / 1024 "Min extent size in K"
from sys.v_$parameter a, sys.v_$parameter b
where a.name = 'db_block_size'
and b.name = 'db_file_multiblock_read_count'
/
rem -----------------------------------------------------------------------
rem Filename: dbgrowth.sql
rem Purpose: Show database growth in Meg per month for the last year
rem DB Version: 8.0 or above
rem Note: File extending is not factored in as it's not the available
rem in the dictionary.
rem Date: 19-Mar-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set pagesize 50000
tti "Database growth per month for last year"
select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')
/
tti off
rem -----------------------------------------------------------------------
rem Filename: freesp.sql
rem Purpose: List segments with more than 200 Meg of free DB Blocks.
rem These segments must be rebuilt to release space and
rem speed-up full table scans.
rem Date: 19-Apr-98
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on size 40000
declare
total_blocks number;
total_bytes number;
unused_blocks number;
unused_bytes number;
last_used_extent_file_id number;
last_used_extent_block_id number;
last_used_block number;
free_pct number;
begin
for c1 in (select owner, segment_type, segment_name
from sys.dba_segments
where owner not in ('SYS', 'SYSTEM', 'PUBLIC')
and segment_type not like '%LOB%')
loop
-- dbms_output.put_line('Check space for '||c1.segment_type||': '||
-- c1.owner||'.'||c1.segment_name||'...');
dbms_space.unused_space(c1.owner, c1.segment_name, c1.segment_type,
total_blocks, total_bytes, unused_blocks,
unused_bytes, last_used_extent_file_id,
last_used_extent_block_id, last_used_block);
free_pct := trunc((1 - (unused_blocks/total_blocks)) * 100);
if unused_bytes > 200*1024*1024 then
dbms_output.put(c1.segment_type||' '||c1.owner||'.'||c1.segment_name);
dbms_output.put(' - ');
dbms_output.put(to_char(trunc(unused_blocks)));
dbms_output.put(' from '||to_char(trunc(total_blocks)));
dbms_output.put(' blocks are empty (');
dbms_output.put(trunc(unused_bytes/1024/1024));
dbms_output.put(' Meg/ '||free_pct||'%)');
dbms_output.new_line;
end if;
end loop;
end;
/
REM ########################################################################
REM ## Author : Sunil Kumar
REM ## Senior DBA
REM ## Email : sunilagarwal@hotmail.com
REM ## sunil.kumar@thehartford.com
REM ##
REM ## Script to determine highwater mark of tables. It is essential
REM ## to run ANALYZE_SCHEMA utility or ANALYZE TABLE commands before
REM ## running this script for accurate statistics.
REM ##
REM ## It displays all the tables whose high water mark <> no# of used
REM ## blocks.
REM ##
REM ## How To Run:
REM ## SQL>@highwtr
REM ## It will ask for the owner (press enter for all owners) and
REM ## the table name (press enter for all tables).
REM ##
REM ########################################################################
set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page
select
a.owner,
a.table_name,
b.blocks alcblks,
a.blocks usdblks,
(b.blocks-a.empty_blocks-1) hgwtr
from
dba_tables a,
dba_segments b
where
a.table_name=b.segment_name
and a.owner=b.owner
and a.owner not in('SYS','SYSTEM')
and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'
and a.table_name like upper('&table_name')||'%'
order by 1,2
/
set verify on
clear columns
clear breaks
REM ------------------------------------------------------------------------------
REM Filename: tsfrag.sql
REM Purpose: Shows whether the TBS is fragmented and the type of fragmentation.
REM Author: Kam Muhamad Sirajdin [mdsirajdin@yahoo.com]
REM ------------------------------------------------------------------------------
TTI "Tablespace Fragmentation Details"
SELECT dfsc.tablespace_name tablespace_name,
DECODE (
dfsc.percent_extents_coalesced,
100,
(DECODE (
GREATEST ((SELECT COUNT (1)
FROM dba_free_space dfs
WHERE dfs.tablespace_name = dfsc.tablespace_name), 1),
1,
'No Frag',
'Bubble Frag'
)
),
'Possible Honey Comb Frag'
)
fragmentation_status
FROM dba_free_space_coalesced dfsc
ORDER BY dfsc.tablespace_name;
TTI off
/* ************************************************************* */
/* Index Fragmentation Status (idsfrag.sql): */
/* */
/* This script will report the index fragmentation status */
/* for a schema. */
/* */
/* Note: - Do not run this scrip during peak processing hours!!! */
/* - This script will fail for locked tables. */
/* */
/* ************************************************************* */
prompt -- Drop and create temporary table to hold stats...
drop table my_index_stats
/
create table my_index_stats (
index_name varchar2(30),
height number(8),
del_lf_rows number(8),
distinct_keys number(8),
rows_per_key number(10,2),
blks_gets_per_access number(10,2)
)
/
prompt -- Save script which we will later use to populate the above table...
insert into my_index_stats
select NAME, HEIGHT, DEL_LF_ROWS, DISTINCT_KEYS, ROWS_PER_KEY,
BLKS_GETS_PER_ACCESS
from INDEX_STATS
-- Note this open line...
save /tmp/save_index_stats.sql replace
prompt
prompt -- Spool listing with validate commands...
col line1 newline
col line2 newline
col line3 newline
set pagesize 0
set echo off
set termout off
set trimspool on
set feed off
set linesize 200
spool /tmp/validate_indexes.sql
select 'prompt Process table '||owner||'.'||table_name||
', index '||index_name||'...' line1,
'validate index '||owner||'.'||index_name||';' line2,
'@/tmp/save_index_stats.sql' line3
from sys.dba_indexes where owner = 'SCOTT'
order by table_name, index_name
/
spool off
set termout on
set feed on
prompt
prompt -- Run script to validate indexes...
@/tmp/validate_indexes.sql
prompt -- Print nice report...
set pagesize 50000
set trimspool on
col height format 99999
col del_rows format 9999999
col rows/key format 999999.9
spool idxfrag.lst
select INDEX_NAME, HEIGHT, DEL_LF_ROWS "DEL_ROWS", DISTINCT_KEYS "DIST KEYS",
ROWS_PER_KEY "ROWS/KEY",
BLKS_GETS_PER_ACCESS "BLKS/ACCESS"
from MY_INDEX_STATS
/
spool off
-- Cleanup
drop table my_index_stats
/
! rm /tmp/validate_indexes.sql
! rm /tmp/save_index_stats.sql
prompt
prompt Report is in idxfrag.lst
prompt Done!!!
SQL Performance Tuning Scripts
o Explain SQL execution plan
o List analyzed tables with not-analyzed indexes
o List all indexes for a given table
o Analyze all table and index partitions individually
rem -----------------------------------------------------------------------
rem Filename: x_plan.sql
rem Purpose: Explain SQL Execution plan
rem Date: 12-Apr-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
DELETE FROM plan_table WHERE statement_id = 'XXX';
COMMIT;
EXPLAIN PLAN
SET STATEMENT_ID = 'XXX'
FOR
select * from dual -----< Insert your SQL statement here <-----
/
-- Oracle 9.2 and above:
set linesize 132
SELECT * FROM TABLE(dbms_xplan.DISPLAY('PLAN_TABLE','XXX'));
set doc off
/* Earlier Oracle versions (9.0 and below):
column operation format a16
column options format a15
column object_name format a20
column id format 99
select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' '||
-- decode(partition_id, NULL, '',
-- 'PART:'||lower(partition_start||'-'||partition_stop))||
decode(optimizer, NULL, '', ' ['||optimizer||']')||
decode(id,0,' Cost=' ||nvl(position, 0)||
' Rows=' ||nvl(cardinality,0)||
' Bytes='||nvl(bytes, 0)) "Query Plan"
from plan_table
where statement_id = 'XXX'
start with id = 0 and statement_id = 'XXX'
connect by prior id = parent_id and statement_id = 'XXX'
/
*/
/* ************************************************************* */
/* List analyzed tables with un-analyzed indexes */
/* */
/* Sometimes indexes are re-build for performance and */
/* maintenance reasons but the assosiated table/index is not */
/* re-ANALYZED. This can cause servere performance problems. */
/* This script will catch out tables with indexes that is not */
/* analyzed. */
/* */
/* ************************************************************* */
-- select distinct 'analyze table '||i.table_name||
-- ' estimate statistics sample 25 percent;'
select 'Index '||i.index_name||' not analyzed but table '||
i.table_name||' is.'
from user_tables t, user_indexes i
where t.table_name = i.table_name
and t.num_rows is not null
and i.distinct_keys is null
/
/* ************************************************************* */
/* List all indexed columns for a given table */
/* */
/* ************************************************************* */
break on index_name skip 1
col index_name format a30
col uniuenes format a12
col column_name format a30
prompt Indexes for table: &&1
select c.index_name, i.uniqueness, c.column_name
from user_indexes i, user_ind_columns c
where i.index_name = c.index_name
and i.table_name = upper('&&1')
order by c.index_name, c.column_position
/
undef 1
rem -----------------------------------------------------------------------
rem Filename: analpart.sql
rem Purpose: Analyze all table and index partitions individually
rem Date: 23-Aug-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set feed off echo off head off trimspool on line 500
spool /tmp/analyze$$.sql
select 'ANALYZE TABLE '||table_owner||'.'||table_name||' partition ('||
partition_name||') estimate statistics;'
from sys.dba_tab_partitions
/
select 'ANALYZE INDEX '||index_owner||'.'||index_name||' partition ('||
partition_name||') estimate statistics;'
from sys.dba_ind_partitions
/
spool off
set feed on
@/tmp/analyze$$.sql
! rm /tmp/analyze$$.sql
Database Security and Security Auditing Scripts
o Block users from using tools like TOAD, SQL Navigator and PLSQL Developer on production databases (DB login trigger)
o Audit User Logins and start traces for selected users (User Login Trigger)
o List database auditing information
o Try to connect to well known database users
o Database users with deadly system privileges assigned to them
o Database users with deadly roles assigned to them
o Security related database initialization parameters and password file users
o List security related profile information
o List Unix OS users that can startup, shutdown and admin Databases
rem -----------------------------------------------------------------------
rem Filename: NoTOAD.sql
rem Purpose: Block developers from using TOAD and other tools on
rem production databases.
rem Date: 19-Jan-2004
rem Author: Frank Naude
rem -----------------------------------------------------------------------
CONNECT / AS SYSDBA;
CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND rownum = 1; -- Parallel processes will have the same AUDSID's
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed on PROD DB!');
END IF;
END;
/
SHOW ERRORS
rem -----------------------------------------------------------------------
rem Filename: logintrig.sql
rem Purpose: Audit user logins and enable SQL tracing for selected users
rem Date: 09-Jul-2005
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
CONNECT / AS SYSDBA
DROP TABLE user_login_audit;
CREATE TABLE user_login_audit (
login_time DATE,
username VARCHAR2(30),
machine VARCHAR2(30),
command VARCHAR2(128)
);
CREATE OR REPLACE TRIGGER user_login_trig
AFTER LOGON ON scott.SCHEMA
DECLARE
v_username sys.v_$session.username%TYPE;
v_machine sys.v_$session.machine%TYPE;
v_command sys.v_$session.command%TYPE;
BEGIN
SELECT username, machine, command
INTO v_username, v_machine, v_command
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND rownum = 1; -- Parallel processes will have the same AUDSID's
INSERT INTO sys.user_login_audit
VALUES (SYSDATE, v_username, v_machine, v_command);
IF UPPER(v_machine) like '%PC1%' THEN -- Start SQL trace for users from PC1
dbms_session.set_sql_trace(TRUE);
END IF;
END;
/
SHOW ERRORS
CONNECT SCOTT/TIGER
CONNECT / AS SYSDBA
SELECT * FROM sys.user_login_audit;
rem -----------------------------------------------------------------------
rem Filename: audit.sql
rem Purpose: Database auditing information (only if auditing is enabled)
rem Date: 04-Nov-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
conn / as sysdba
tti "Auditing Initialisation Parameters:"
select name || '=' || value PARAMETER
from sys.v_$parameter where name like '%audit%'
/
tti "Statement Audits Enabled on this Database"
column user_name format a10
column audit_option format a40
select *
from sys.dba_stmt_audit_opts
/
tti "Privilege Audits Enabled on this Database"
select * from dba_priv_audit_opts
/
tti "Object Audits Enabled on this Database"
select (owner ||'.'|| object_name) object_name,
alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe
from dba_obj_audit_opts
where alt != '-/-' or aud != '-/-'
or com != '-/-' or del != '-/-'
or gra != '-/-' or ind != '-/-'
or ins != '-/-' or loc != '-/-'
or ren != '-/-' or sel != '-/-'
or upd != '-/-' or ref != '-/-'
or exe != '-/-'
/
tti "Default Audits Enabled on this Database"
select * from all_def_audit_opts
/
rem -----------------------------------------------------------------------
rem Filename: try-conn.sql
rem Purpose: Try to connect to well known database users
rem Usage: sqlplus /nolog @try-conn.sql
rem Date: 04-Nov-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set feed off pages 0 head off
select '*** User '||USER||' uses default password on database '||
GLOBAL_NAME||' ***'
from global_name
-- The next line must be left blank!!!
conn sys/change_on_install
/
conn system/manager
/
conn hr/hr
/
conn oe/oe
/
conn sh/sh
/
conn scott/tiger
/
conn adams/wood
/
conn jones/steel
/
conn clark/cloth
/
conn blake/paper
/
conn outln/outln
/
conn ctxsys/ctxsys
/
conn tracesvr/trace
/
conn dbsnmp/dbsnmp
/
conn ordplugins/ordplugins
/
conn ordsys/ordsys
/
conn mdsys/mdsys
/
conn dssys/dssys
/
conn perfstat/perfstat
/
conn csmig/csmig
/
rem -----------------------------------------------------------------------
rem Filename: badsysprivs.sql
rem Purpose: Database users with deadly system privilages assigned to
rem them.
rem Date: 04-Nov-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
conn / as sysdba
select grantee, privilege, admin_option
from sys.dba_sys_privs
where (privilege like '% ANY %'
or privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
or admin_option = 'YES')
and grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA')
/
rem -----------------------------------------------------------------------
rem Filename: badroles.sql
rem Purpose: Database users with deadly roles assigned to them.
rem Date: 04-Nov-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
conn / as sysdba
select grantee, granted_role, admin_option
from sys.dba_role_privs
where granted_role in ('DBA', 'AQ_ADMINISTRATOR_ROLE',
'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR')
and grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA')
/
rem -----------------------------------------------------------------------
rem Filename: sec-parms.sql
rem Purpose: Security related database initialization parameters and
rem password file users.
rem Date: 04-Nov-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
conn / as sysdba
tti "Security related initialization parameters:"
select name || '=' || value "PARAMTER"
from sys.v_$parameter
where name in ('remote_login_passwordfile', 'remote_os_authent',
'os_authent_prefix', 'dblink_encrypt_login',
'audit_trail', 'transaction_auditing')
/
tti "Password file users:"
select * from sys.v_$pwfile_users
/
rem -----------------------------------------------------------------------
rem Filename: sec-prof.sql
rem Purpose: List security related profile information
rem Date: 04-Nov-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
conn / as sysdba
col profile format a20
col limit format a20
select profile, resource_name, limit
from dba_profiles
where resource_name like '%PASSWORD%'
or resource_name like '%LOGIN%'
/
#!/bin/ksh
rem -----------------------------------------------------------------------
rem Filename: sysdba.sh
rem Purpose: List Unix users that can startup, shutdown and admin Databases
rem Date: 04-Nov-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
echo "Users that can startup, shutdown and admin Oracle Databases:"
echo
grep `grep ^dba /etc/group | cut -d: -f3` /etc/passwd
Database Performance Tuning Scripts
o Measure the Buffer Cache Hit Ratio
o Display Database SGA Statistics
o Reports free memory available in the SGA
o List available INIT.ORA parms
o List unsupported INIT.ORA parms
o List active database transactions
o Rollback segment statistics
o Display database sessions using rollback segments
o Lookup database details for a given Unix process id
o Display database locks and latches (with tables names, etc)
o Another lock monitor script
o Display and release DBMS_LOCK locks
o OraYAPS - Yet another Oracle Performance Tuning script
rem -----------------------------------------------------------------------
rem Filename: bufhitra.sql
rem Purpose: Measure the Buffer Cache Hit Ratio
rem Note: Hit ratio based tuning is not recommended!
rem Date: 30-May-2005
rem Author: Frank Nade, Oracle FAQ
rem -----------------------------------------------------------------------
-- Get initial Buffer Hit Ratio reading...
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/
-- Let's artificially increase the buffer hit ratio...
DECLARE
v_dummy dual.dummy%TYPE;
BEGIN
FOR I IN 1..1000 LOOP
SELECT dummy INTO v_dummy FROM dual;
END LOOP;
END;
/
-- Let's measure it again...
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/
rem -----------------------------------------------------------------------
rem Filename: sga_stat.sql
rem Purpose: Display database SGA statistics
rem Date: 14-Jun-2001
rem Author: Anjan Roy (AnjanR@innotrex.com)
rem -----------------------------------------------------------------------
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
spsize number;
blkbuf number;
logbuf number;
BEGIN
select value into redlog from v$sysstat
where name = 'redo log space requests';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
select value into spsize from v$parameter where name = 'shared_pool_size';
select value into blkbuf from v$parameter where name = 'db_block_buffers';
select value into logbuf from v$parameter where name = 'log_buffer';
dbms_output.put_line('> SGA CACHE STATISTICS');
dbms_output.put_line('> ********************');
dbms_output.put_line('> SQL Cache Hit rate = '||libcac);
dbms_output.put_line('> Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('> Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('> Redo Log space requests = '||redlog);
dbms_output.put_line('> ');
dbms_output.put_line('> INIT.ORA SETTING');
dbms_output.put_line('> ****************');
dbms_output.put_line('> Shared Pool Size = '||spsize||' Bytes');
dbms_output.put_line('> DB Block Buffer = '||blkbuf||' Blocks');
dbms_output.put_line('> Log Buffer = '||logbuf||' Bytes');
dbms_output.put_line('> ');
if
libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
rem -----------------------------------------------------------------------
rem Filename: initora.sql
rem Purpose: List all supported INIT.ORA parameters
rem Date: 03-Apr-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault,
decode(a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file',
a.ksppity) type,
a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm not like '\_%' escape '\'
order by name
/
rem -----------------------------------------------------------------------
rem Filename: inithide.sql
rem Purpose: List all un-supported INIT.ORA parameters
rem Note: Run script from SYS or INTERNAL
rem Date: 03-Apr-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault,
decode(a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file',
a.ksppity) type,
a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '\_%' escape '\'
order by name
/
rem -----------------------------------------------------------------------
rem Filename: acttrx.sql
rem Purpose: Shows active (in progress) transactions
rem Date: 12-Apr-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
col name format a8
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
select username, terminal, osuser,
t.start_time, r.name, t.used_ublk "ROLLB BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/
rem -----------------------------------------------------------------------
rem Filename: rbs_stat.sql
rem Purpose: Display rollback segment statistics
rem Author: Anonymous
rem -----------------------------------------------------------------------
column "Rollback Segment" format a16
column "Size (Kb)" format 9,999,999
column "Gets" format 999,999,990
column "Waits" format 9,999,990
column "% Waits" format 90.00
column "# Shrinks" format 999,990
column "# Extends" format 999,990
Prompt
Prompt Rollback Segment Statistics...
Select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",
rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",
rs.Shrinks "# Shrinks", rs.Extends "# Extends"
from sys.v_$RollName rn, sys.v_$RollStat rs
where rn.usn = rs.usn
/
rem -----------------------------------------------------------------------
rem Filename: rbs_act.sql
rem Purpose: Display database sessions using rollback segments
rem Author: Anonymous
rem -----------------------------------------------------------------------
col RBS format a5 trunc
col SID format 9990
col USER format a10 trunc
col COMMAND format a78 trunc
col status format a6 trunc
SELECT r.name "RBS", s.sid, s.serial#, s.username "USER", t.status,
t.cr_get, t.phy_io, t.used_ublk, t.noundo,
substr(s.program, 1, 78) "COMMAND"
FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r
WHERE t.addr = s.taddr
and t.xidusn = r.usn
ORDER BY t.cr_get, t.phy_io
/
rem -----------------------------------------------------------------------
rem Filename: unixusr.sql
rem Purpose: Lookup database details for a given Unix process id
rem Date: 06-Oct-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on size 50000
set echo off feed off veri off
accept 1 prompt 'Enter Unix process id: '
DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select sid into v_sid
from sys.v_$process p, sys.v_$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1');
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;
select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;
dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select /*+ ordered */
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_$lock l, sys.obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END;
/
undef 1
rem -----------------------------------------------------------------------
rem Filename: lock.sql
rem Purpose: Display database locks and latches (with tables names, etc)
rem Date: 12-Apr-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set pagesize 23
set pause on
set pause 'Hit any key...'
col sid format 999999
col serial# format 999999
col username format a12 trunc
col process format a8 trunc
col terminal format a12 trunc
col type format a12 trunc
col lmode format a4 trunc
col lrequest format a4 trunc
col object format a73 trunc
select s.sid, s.serial#,
decode(s.process, null,
decode(substr(p.username,1,1), '?', upper(s.osuser), p.username),
decode( p.username, 'ORACUSR ', upper(s.osuser), s.process)
) process,
nvl(s.username, 'SYS ('||substr(p.username,1,4)||')') username,
decode(s.terminal, null, rtrim(p.terminal, chr(0)),
upper(s.terminal)) terminal,
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', decode(u.name, null,
'DICTIONARY OBJECT', u.name||'.'||o.name),
'TD', u.name||'.'||o.name,
'TM', u.name||'.'||o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE',
'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) object
from sys.v_$lock l, sys.v_$session s, sys.obj$ o, sys.user$ u,
sys.v_$process p
where s.paddr = p.addr(+)
and l.sid = s.sid
and l.id1 = o.obj#(+)
and o.owner# = u.user#(+)
and l.type <> 'MR'
UNION ALL /*** LATCH HOLDERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr)
from sys.v_$process p, sys.v_$session s, sys.v_$latchholder h
where h.pid = p.pid
and p.addr = s.paddr
UNION ALL /*** LATCH WAITERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait
from sys.v_$session s, sys.v_$process p, sys.v_$latch l
where latchwait is not null
and p.addr = s.paddr
and p.latchwait = l.addr
/
-- showlock.sql - show all user locks
-- originally from tsawmiller on the ORACLE-L list
-- and morphed somewhat
-- speeded up greatly by changing order of where clause,
-- and using all_objects instead of dba_objects.
-- Note that $ORACLE_HOME/rdbms/admin/catblock.sql
-- must be executed (from SYS) before this script functions.
-- jks 04/09/1997 - show lock addresses and lockwait
-- jks 04/09/1997 - outer join on all_objects
-- encountered situation on 7.2
-- where there was a lock with no
-- matching object_id
-- jks 02/24/1999 - join to dba_waiters to show waiters and blockers
set trimspool on
ttitle off
set linesize 155
set pagesize 60
column osuser heading 'OS|Username' format a7 truncate
column process heading 'OS|Process' format a7 truncate
column machine heading 'OS|Machine' format a10 truncate
column program heading 'OS|Program' format a25 truncate
column object heading 'Database|Object' format a25 truncate
column lock_type heading 'Lock|Type' format a4 truncate
column mode_held heading 'Mode|Held' format a15 truncate
column mode_requested heading 'Mode|Requested' format a10 truncate
column sid heading 'SID' format 999
column username heading 'Oracle|Username' format a7 truncate
column image heading 'Active Image' format a20 truncate
column sid format 99999
col waiting_session head 'WAITER' format 9999
col holding_session head 'BLOCKER' format 9999
select /*+ ordered */
--b.kaddr,
c.sid,
lock_waiter.waiting_session,
lock_blocker.holding_session,
c.program,
c.osuser,
c.machine,
c.process,
decode(u.name,
null,'',
u.name||'.'||o.name
) object,
c.username,
decode
(
b.type,
'BL', 'Buffer hash table instance lock',
'CF', 'Control file schema global enqueue lock',
'CI', 'Cross-instance function invocation instance lock',
'CU', 'Cursor bind lock',
'DF', 'Data file instance lock',
'DL', 'direct loader parallel index create lock',
'DM', 'Mount/startup db primary/secondary instance lock',
'DR', 'Distributed recovery process lock',
'DX', 'Distributed transaction entry lock',
'FS', 'File set lock',
'IN', 'Instance number lock',
'IR', 'Instance recovery serialization global enqueue lock',
'IS', 'Instance state lock',
'IV', 'Library cache invalidation instance lock',
'JQ', 'Job queue lock',
'KK', 'Thread kick lock',
'LA','Library cache lock instance lock (A..P=namespace);',
'LB','Library cache lock instance lock (A..P=namespace);',
'LC','Library cache lock instance lock (A..P=namespace);',
'LD','Library cache lock instance lock (A..P=namespace);',
'LE','Library cache lock instance lock (A..P=namespace);',
'LF','Library cache lock instance lock (A..P=namespace);',
'LG','Library cache lock instance lock (A..P=namespace);',
'LH','Library cache lock instance lock (A..P=namespace);',
'LI','Library cache lock instance lock (A..P=namespace);',
'LJ','Library cache lock instance lock (A..P=namespace);',
'LK','Library cache lock instance lock (A..P=namespace);',
'LL','Library cache lock instance lock (A..P=namespace);',
'LM','Library cache lock instance lock (A..P=namespace);',
'LN','Library cache lock instance lock (A..P=namespace);',
'LO','Library cache lock instance lock (A..P=namespace);',
'LP','Library cache lock instance lock (A..P=namespace);',
'MM', 'Mount definition global enqueue lock',
'MR', 'Media recovery lock',
'NA', 'Library cache pin instance lock (A..Z=namespace)',
'NB', 'Library cache pin instance lock (A..Z=namespace)',
'NC', 'Library cache pin instance lock (A..Z=namespace)',
'ND', 'Library cache pin instance lock (A..Z=namespace)',
'NE', 'Library cache pin instance lock (A..Z=namespace)',
'NF', 'Library cache pin instance lock (A..Z=namespace)',
'NG', 'Library cache pin instance lock (A..Z=namespace)',
'NH', 'Library cache pin instance lock (A..Z=namespace)',
'NI', 'Library cache pin instance lock (A..Z=namespace)',
'NJ', 'Library cache pin instance lock (A..Z=namespace)',
'NK', 'Library cache pin instance lock (A..Z=namespace)',
'NL', 'Library cache pin instance lock (A..Z=namespace)',
'NM', 'Library cache pin instance lock (A..Z=namespace)',
'NN', 'Library cache pin instance lock (A..Z=namespace)',
'NO', 'Library cache pin instance lock (A..Z=namespace)',
'NP', 'Library cache pin instance lock (A..Z=namespace)',
'NQ', 'Library cache pin instance lock (A..Z=namespace)',
'NR', 'Library cache pin instance lock (A..Z=namespace)',
'NS', 'Library cache pin instance lock (A..Z=namespace)',
'NT', 'Library cache pin instance lock (A..Z=namespace)',
'NU', 'Library cache pin instance lock (A..Z=namespace)',
'NV', 'Library cache pin instance lock (A..Z=namespace)',
'NW', 'Library cache pin instance lock (A..Z=namespace)',
'NX', 'Library cache pin instance lock (A..Z=namespace)',
'NY', 'Library cache pin instance lock (A..Z=namespace)',
'NZ', 'Library cache pin instance lock (A..Z=namespace)',
'PF', 'Password File lock',
'PI', 'Parallel operation locks',
'PS', 'Parallel operation locks',
'PR', 'Process startup lock',
'QA','Row cache instance lock (A..Z=cache)',
'QB','Row cache instance lock (A..Z=cache)',
'QC','Row cache instance lock (A..Z=cache)',
'QD','Row cache instance lock (A..Z=cache)',
'QE','Row cache instance lock (A..Z=cache)',
'QF','Row cache instance lock (A..Z=cache)',
'QG','Row cache instance lock (A..Z=cache)',
'QH','Row cache instance lock (A..Z=cache)',
'QI','Row cache instance lock (A..Z=cache)',
'QJ','Row cache instance lock (A..Z=cache)',
'QK','Row cache instance lock (A..Z=cache)',
'QL','Row cache instance lock (A..Z=cache)',
'QM','Row cache instance lock (A..Z=cache)',
'QN','Row cache instance lock (A..Z=cache)',
'QP','Row cache instance lock (A..Z=cache)',
'QQ','Row cache instance lock (A..Z=cache)',
'QR','Row cache instance lock (A..Z=cache)',
'QS','Row cache instance lock (A..Z=cache)',
'QT','Row cache instance lock (A..Z=cache)',
'QU','Row cache instance lock (A..Z=cache)',
'QV','Row cache instance lock (A..Z=cache)',
'QW','Row cache instance lock (A..Z=cache)',
'QX','Row cache instance lock (A..Z=cache)',
'QY','Row cache instance lock (A..Z=cache)',
'QZ','Row cache instance lock (A..Z=cache)',
'RT', 'Redo thread global enqueue lock',
'SC', 'System commit number instance lock',
'SM', 'SMON lock',
'SN', 'Sequence number instance lock',
'SQ', 'Sequence number enqueue lock',
'SS', 'Sort segment locks',
'ST', 'Space transaction enqueue lock',
'SV', 'Sequence number value lock',
'TA', 'Generic enqueue lock',
'TS', 'Temporary segment enqueue lock (ID2=0)',
'TS', 'New block allocation enqueue lock (ID2=1)',
'TT', 'Temporary table enqueue lock',
'UN', 'User name lock',
'US', 'Undo segment DDL lock',
'WL', 'Being-written redo log instance lock',
b.type
) lock_type,
decode
(
b.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SRX)', /* C */
6, 'Exclusive', /* X */
to_char(b.lmode)
) mode_held,
decode
(
b.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(b.request)
) mode_requested
from
v$lock b
,v$session c
,sys.user$ u
,sys.obj$ o
,( select * from sys.dba_waiters) lock_blocker
,( select * from sys.dba_waiters) lock_waiter
where
b.sid = c.sid
and u.user# = c.user#
and o.obj#(+) = b.id1
and lock_blocker.waiting_session(+) = c.sid
and lock_waiter.holding_session(+) = c.sid
and c.username != 'SYS'
order by kaddr, lockwait
/
rem -----------------------------------------------------------------------
rem Filename: lock_usr.sql
rem Purpose: Release DBMS_LOCK locks on database
rem Date: 12-Apr-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on size 50000
col name format a30
set veri off feed off pagesize 50000
cle scr
prompt Please enter the user's login id:
select *
from sys.dbms_lock_allocated
where upper(name) like upper('%&userid.%');
prompt Please enter lockid to release:
declare
rc integer;
begin
rc := dbms_lock.release('&lockid');
if rc = 0 then
dbms_output.put_line('Success.');
elsif rc = 3 then
dbms_output.put_line('Parameter Error.');
elsif rc = 4 then
dbms_output.put_line('Do not own lock specified.');
elsif rc = 5 then
dbms_output.put_line('Illegal lock handled.');
else
dbms_output.put_line('Unknown error.');
end if;
end;
/
/*
oraYAPS - Yet another Performance Script Version .01.
This script pulls important oracle performance variables. This is a working project and is version .01
Please send enhancement suggestions to cjm@integer.org
cjm@integer.org
http://www.integer.org
copyright (C) 2003 Cameron Michelis copying and redistribution of this file is permitted provided this notice and the above comments are preserved.
*/
prompt
prompt Rollback Segment Statistics
prompt
col name for a7
col xacts for 9990 head "Actv|Trans"
col InitExt for 990.00 head "Init|Ext|(Mb)"
col NextExt for 990.00 head "Next|Ext|(Mb)"
col MinExt for 99 head "Min|Ext"
col MaxExt for 999 head "Max|Ext"
col optsize for 9990.00 head "Optimal|Size|(Mb)"
col rssize for 9990.00 head "Curr|Size|(Mb)"
col hwmsize for 9990.00 head "High|Water|Mark|(Mb)"
col wraps for 999 head "W|R|A|P|S"
col extends for 990 head "E|X|T|E|N|D|S"
col shrinks for 990 head "S|H|R|I|N|K|S"
col aveshrink for 990.00 head "AVG|Shrink|(Mb)"
col gets head "Header|Gets"
col waits for 99990 head "Header|Waits"
col writes for 999,999,990 head "Total|Writes|Since|Startup|(Kb)"
col wpg for 9990 head "AVG|Writes|Per|HedGet|(bytes)"
set lines 132 pages 40 feed off
break on report
compute sum of gets on report
compute sum of waits on report
compute avg of aveshrink on report
compute avg of wpg on report
select name,
XACTS,
initial_extent/1048576 InitExt,
next_extent/1048576 NextExt,
min_extents MinExt,
max_extents MaxExt,
optsize/1048576 optsize,
RSSIZE/1048576 rssize,
HWMSIZE/1048576 hwmsize,
wraps,
extends,
shrinks,
aveshrink/1048576 aveshrink,
gets,
waits,
writes/1024 writes,
writes/gets wpg
from v$rollstat,v$rollname,dba_rollback_segs
where v$rollstat.usn=v$rollname.usn
and dba_rollback_segs.segment_id=v$rollname.usn
order by name
/
prompt
prompt More Rollback Segment Statistics
prompt
column "Rollback Segment" format a16
column "Size (Kb)" format 9,999,999
column "Gets" format 999,999,990
column "Waits" format 9,999,990
column "% Waits" format 90.00
column "# Shrinks" format 999,990
column "# Extends" format 999,990
Select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",
rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",
rs.Shrinks "# Shrinks", rs.Extends "# Extends"
from sys.v_$RollName rn, sys.v_$RollStat rs
where rn.usn = rs.usn;
/
prompt
prompt Yet some More Rollback Segment Statistics
prompt
col RBS format a5 trunc
col SID format 9990
col USER format a10 trunc
col COMMAND format a78 trunc
col status format a6 trunc
SELECT r.name "RBS", s.sid, s.serial#, s.username "USER", t.status,
t.cr_get, t.phy_io, t.used_ublk, t.noundo,
substr(s.program, 1, 78) "COMMAND"
FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r
WHERE t.addr = s.taddr
and t.xidusn = r.usn
ORDER BY t.cr_get, t.phy_io;
/
Prompt
Prompt Cache hit ratio
prompt
select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",
round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets' and
con.name = 'consistent gets' and
phy.name = 'physical reads';
/
Prompt
Prompt Another Buffer Cache hit ratio Calculation
prompt
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;
/
prompt
prompt Data Dictionary Hit Ratio should be over 90 percent
prompt
select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. Cache Misses",
round((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO",
round(sum(getmisses)*100/sum(gets)) "% MISSED"
from v$rowcache;
/
prompt
prompt Library Cache Miss Ratio
prompt
select sum(pins) "executions",
sum(reloads) "Cache Misses",
round((1-(sum(reloads)/sum(pins)))*100) "LIBRARY CACHE HIT RATIO",
round(sum(reloads)*100/sum(pins)) "% Missed"
from v$librarycache;
/
prompt
prompt More Library Cache stats
prompt
select namespace,
trunc(gethitratio*100) "Hit Ratio",
trunc(pinhitratio*100) "Pin Hit Ratio",
reloads "Reloads"
from v$librarycache;
/
prompt
prompt Another Library Cache Calculation, total reloads should be as close to 0 as possible.
prompt
column libcache format 99.99 heading 'Percentage' jus cen
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
/
prompt
prompt Redo Log Buffer should be as close to 0 as possible
prompt
select substr(name,1,30),value
from v$sysstat where name ='redo log space requests';
/
prompt
prompt Redo Log Contention, all ratios less than 1
prompt
SET feedback OFF
COLUMN name FORMAT a15
COLUMN gets FORMAT 99999999
COLUMN misses FORMAT 999999
COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS'
COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES'
PROMPT Examining Contention for Redo Log Buffer Latches...
PROMPT ----------------------------------------------------
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
/
prompt
prompt Disk Vs. Memory Sorts. Try to keep the disk/memory ratio to less than .10 by increasing the sort_area_size
prompt
SET HEADING OFF
SET FEEDBACK OFF
COLUMN name FORMAT a30
COLUMN value FORMAT 99999990
SELECT name, value FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
/
prompt
prompt Initialization Parameters
prompt
select substr(name,1,35) "Parameter" ,substr(value,1,35) "Value" from v$parameter order by name asc;
/
Backup and Recovery Scripts
o Simple on-line backup script (user managed - put tablespaces in backup mode and copy out)
o Check for tablespaces in backup mode and take them out of backup mode
o Take database data files out of backup mode
o List RMAN Backups registered in RMAN catalog database
o Monitor running RMAN Backups
o Summary of datafiles and archlogs backed up with RMAN over past 24 hours
o List completed RMAN Backups for past 24 hours
o Jack van Zanen's RMAN Scripts
o Demonstrate Oracle 8i transportable tablespaces
rem -----------------------------------------------------------------------
rem Filename: backup.sql
rem Purpose: Generate script to do a simple on-line database backup.
rem Notes: Adjust the copy_cmnd and copy_dest variables and run from
rem sqlplus. Uncomment last few lines to do the actual backup.
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on
set trimspool on
set line 500
set head off
set feed off
spool backup.cmd
declare
copy_cmnd constant varchar2(30) := 'cp'; -- Use "ocopy" for NT
copy_dest constant varchar2(30) := '/backup/'; -- C:\BACKUP\ for NT
dbname varchar2(30);
logmode varchar2(30);
begin
select name, log_mode
into dbname, logmode
from sys.v_$database;
if logmode <> 'ARCHIVELOG' then
raise_application_error(-20000,
'ERROR: Database must be in ARCHIVELOG mode!!!');
return;
end if;
dbms_output.put_line('spool backup.'||dbname||'.'||
to_char(sysdate, 'ddMonyy')||'.log');
-- Loop through tablespaces
for c1 in (select tablespace_name ts
from sys.dba_tablespaces)
loop
dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
-- Loop through tablespaces' data files
for c2 in (select file_name fil
from sys.dba_data_files
where tablespace_name = c1.ts)
loop
dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);
end loop;
dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
end loop;
-- Backup controlfile and switch logfiles
dbms_output.put_line('alter database backup controlfile to trace;');
dbms_output.put_line('alter database backup controlfile to '||''''||
copy_dest||'control.'||dbname||'.'||
to_char(sysdate,'DDMonYYHH24MI')||''''||';');
dbms_output.put_line('alter system switch logfile;');
dbms_output.put_line('spool off');
end;
/
spool off
set head on
set feed on
set serveroutput off
-- Unremark/uncomment the following line to run the backup script
-- @backup.cmd
-- exit
rem -----------------------------------------------------------------------
rem Filename: end_backup.sql
rem Purpose: This script will create a file called end_backup_script.sql
rem - run it to take all tablespaces out of backup mode.
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
column cmd format a80 heading "Text"
set feedback off
set heading off
set pagesize 0
spool end_backup_script.sql
select 'alter tablespace '||a.tablespace_name||' end backup;' cmd
from sys.dba_data_files a, sys.v_$backup b
where b.status = 'ACTIVE'
and b.file# = a.file_id
group by a.tablespace_name
/
spool off
set feedback on
set heading on
set pagesize 24
set termout on
start end_backup_script.sql
rem -----------------------------------------------------------------------
rem Filename: end_backup2.sql
rem Purpose: Take database data files out of backup mode
rem Notes: Run from QL*Plus (or SVRMGRL prior to 9i)
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
connect internal
spool end_backup2.log
select 'alter database datafile '''||f.name||''' end backup;'
from v$datafile f, v$backup b
where b.file# = f.file#
and b.status = 'ACTIVE'
/
spool off
! grep '^alter' end_backup2.log >end_backup2.log2
@end_backup2.log2
! rm end_baclup.log
! rm end_backup.log2
exit
rem -----------------------------------------------------------------------
rem Filename: rmanlist.sql
rem Purpose: List backups registered in RMAN catalog database
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
connect rman/rman
col media format a8
col tag format a12 trunc
col minutes format 990
select d.name, p.tag, p.media,
s.incremental_level "LEVEL",
to_char(s.start_time, 'DD-MON-YY HH24:MI') start_time,
s.elapsed_seconds/60 "MINUTES"
from RC_DATABASE d, RC_BACKUP_PIECE p, RC_BACKUP_SET s
where d.name = 'WH'
and s.start_time > '04-MAY-02'
and s.completion_time < '06-MAY-02'
and p.backup_type = 'D' -- D=Database, L=Log
and d.db_key = p.db_key
and s.db_key = p.db_key
and p.bs_key = s.bs_key
/
rem -----------------------------------------------------------------------
rem Filename: rman_run.sql
rem Purpose: Monitor RMAN status, while a backup is running
rem Date: 12-Nov-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
prompt RMAN Backup Status:
SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore'
/
rem -----------------------------------------------------------------------
rem Filename: rmanstat24.sql
rem Purpose: Summary of all datafiles and archlogs backed up over the
rem last 24-hours.
rem Date: 12-Feb-2005
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
tti "RMAN Status for past 24-hours"
-- Datafiles Backed up during past 24 Hours
SELECT dbfiles||' from '||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1)
/
-- Archlog Files Backed up during past 24 Hours
SELECT backedup||' from '||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*) archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO')
/
-- RMAN Backups Still Running:
SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore'
/
rem -----------------------------------------------------------------------
rem Filename: rmanlist24.sql
rem Purpose: List completed RMAN backups for the last 24-hours
rem (use info from Dictionary - Control File Views)
rem Date: 12-Feb-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
tti "RMAN Backups for last 24-hours"
col type format a4
col handle format a35 trunc
col file# format 9999
col duration format a9
select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'DDMON HH24:MI') start_time,
to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = b.SET_STAMP
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#
/
rem -----------------------------------------------------------------------
rem Filename: ttstest.sql
rem Purpose: Demonstrate Oracle 8i transportable tablespaces
rem Notes: This example script will create a tablespace, export it's
rem definitions, drop it and re-import it.
rem Date: 12-Feb-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
def TABLESPACE_NAME=test_ts
def DATAFILE_NAME=/tmp/test_ts_file1.dbf
-- Create a test tablespace and table
create tablespace &&TABLESPACE_NAME
datafile '&&DATAFILE_NAME' size 10M reuse
extent management local;
create table test (a date) tablespace &&TABLESPACE_NAME;
insert into test values (sysdate);
-- Test if the tablespace can be transported
exec sys.dbms_tts.transport_set_check('&&TABLESPACE_NAME', TRUE);
prompt Transport set violations:
select * from sys.transport_set_violations;
alter tablespace &&TABLESPACE_NAME read only;
! exp system/manager transport_tablespace=yes tablespaces=&&TABLESPACE_NAME triggers=no constraints=no
drop tablespace &&TABLESPACE_NAME including contents;
-- This should now give an error
select count(*) from test;
! imp system/manager transport_tablespace=yes tablespaces=&&TABLESPACE_NAME datafiles=\('&&DATAFILE_NAME'\)
alter tablespace &&TABLESPACE_NAME read write;
-- The table should now be back
select count(*) from test;
-- Cleanup
drop table test;
drop tablespace &&TABLESPACE_NAME;
Oracle Developer Forms and Reports
o Create a report of a Forms item and block properties
o Create MS-Word document from Developer Forms
o Tetris for Developer 6i
-- Creates a report (as a text file) of various properties of all blocks and items in a form.
-- Use as is, or paste in an Excel sheet
PROCEDURE Form_list_items (p_tabular_format IN BOOLEAN := FALSE)
-- Parameter p_tabular_format:
-- * FALSE for headers between block info;
-- * TRUE for no headers between, for use of the output in Excel-sheets etc.
IS
/* This utility is meant for development and QA, certainly NOT for use in production !!!
See below for further comments
*/
-- © Original code by Jan-Hendrik van Heusden, Transfer Solutions, Leerdam, Netherlands
-- Using / altering of this code is allowed; please keep the above line in your text,
-- and specify any changes below
/* CHANGE HISTORY:
WHEN (dd-mm-yyyy) WHO WHAT
----------------- ------------ -----------------------------------------------
01-06-2003 JHvHeusden Initial creation for Forms 5, 6, 6i;
See below for use in Forms 4.5
*/
/* Forms Versions
|| Use in Forms 5, 6, 6i. For use in 4.5, a few properties are renamed (DISPLAYED
|| instead of VISIBLE
||
|| Purpose
|| Creates a text file (use as is, or paste in Excel) with a report of all blocks
|| and items in a form.
|| May be useful especially:
|| * in complicated forms
|| * for quality checks, i.e. check format masks on numbers, dates etc.
||
|| Use:
|| Add the following statement to your WHEN-NEW-FORM-INSTANCE trigger (create one if
|| necessary):
|| Form_list_items(FALSE); -- report with headers and info for each block
|| or
|| Form_list_items(TRUE); -- tabular output, ie if output is pasted in Excel
||
|| Run your form, and get your report from the file system.
||
|| TAKE CARE: --> Once you got your report, remove are comment out the added line!!
||
|| Again, the utility is meant for development and QA, certainly NOT for use in
|| production !!!
||
|| Changes:
|| * Change output format
|| * Items may be added; variables have to be added for the values and the column width
|| Data length and format string are candidates.
|| * Pathname is hardcoded; change if necessary.
||
|| Robustness:
|| Not much care is given to exception handling of file IO, since the utility should
|| be used by developers, not in production.
|| If any file IO errors occur, check path and filename, and check your OS-privileges
|| on the specified path
||
*************************************************************************************/
-- Hardcoded path + name of output-file; alter if necessary
l_temp_filename VARCHAR2(255)
:= 'C:\temp\'||:SYSTEM.current_form||'_fmb_blk_items.txt';
l_tabular BOOLEAN := NVL(p_tabular_format, FALSE) ;
-- Constants below specify the column width for the various columns
-- The values are pretty small (maybe too small in certain cases), but
-- in most cases the output is more readable with not too large values
-- Ajust the values if necessary.
-- If you want to find out which width you need for a certain property, use
-- the "l_width_..."-variabeles (BELOW); uncomment the code that writes this
-- information to file
-- Ajusts the widths below, according to the found values
lc_width_block_name CONSTANT INTEGER := 18;
lc_width_data_target_type CONSTANT INTEGER := 7;
lc_width_data_target_name CONSTANT INTEGER := 20;
lc_width_item_name CONSTANT INTEGER := 32;
lc_width_col_name CONSTANT INTEGER := 44;
lc_width_visible CONSTANT INTEGER := 7;
lc_width_item_type CONSTANT INTEGER := 14;
lc_width_fmt_mask CONSTANT INTEGER := 22;
lc_width_datatype CONSTANT INTEGER := 10;
lc_width_canvas CONSTANT INTEGER := 24;
lc_width_prompt CONSTANT INTEGER := 28;
lc_width_hint_text CONSTANT INTEGER := 6;
-- This latter one for the header only, not for the hint itself.
-- Always use the rightmost column for this property so that it does not
-- ruin the layout of your report
-- Block variables
l_first_block VARCHAR2(100)
:= get_form_property(:SYSTEM.current_form, first_block);
l_last_block VARCHAR2(100)
:= get_form_property(:SYSTEM.current_form, last_block);
l_cur_block VARCHAR2(100);
l_is_last_block BOOLEAN := FALSE;
-- Item variable
l_first_item VARCHAR2(100);
l_last_item VARCHAR2(100);
l_cur_item VARCHAR2(100);
l_is_last_item BOOLEAN := FALSE;
-- variabele for Text_IO
l_tempfile text_io.file_type;
-- Counters
l_block_count INTEGER := 0;
l_items_in_block INTEGER := 0;
l_item_count INTEGER := 0;
-- Variables for block-properties
l_data_target_type VARCHAR2(100);
l_data_target_name VARCHAR2(100);
-- Variables for item-properties
l_col_name VARCHAR2(100);
l_visible VARCHAR2(30);
l_item_type VARCHAR2(30);
l_datatype VARCHAR2(30);
l_fmt_mask VARCHAR2(100);
l_canvas VARCHAR2(100);
l_prompt VARCHAR2(200);
l_hint_text VARCHAR2(2000);
/* */
-- Enable (uncomment) code below to find column widths (max. field lengths)
l_width_block_name INTEGER := 0;
l_width_data_target_type INTEGER := 0;
l_width_data_target_name INTEGER := 0;
l_width_item_name INTEGER := 0;
l_width_col_name INTEGER := 0;
l_width_fmt_mask INTEGER := 0;
l_width_visible INTEGER := 0;
l_width_item_type INTEGER := 0;
l_width_datatype INTEGER := 0;
l_width_canvas INTEGER := 0;
l_width_prompt INTEGER := 0;
l_width_hint_text INTEGER := 0;
/* */
l_str VARCHAR2(2000);
l_items_hdr_text VARCHAR2(2000) :=
RPAD('Block' , lc_width_block_name)
||RPAD('Item' , lc_width_item_name )
||RPAD('Disp.?' , lc_width_visible )
||RPAD('Item type' , lc_width_item_type )
||RPAD('Column' , lc_width_col_name )
||RPAD('Datatype' , lc_width_datatype )
||RPAD('Format Msk' , lc_width_fmt_mask )
||RPAD('Canvas' , lc_width_canvas )
||RPAD('Prompt' , lc_width_prompt )
||RPAD('Hint' , lc_width_hint_text )
;
l_items_hdr_2nd VARCHAR2(2000) :=
RPAD('*', lc_width_block_name-1, '*')||' '
||RPAD('*', lc_width_item_name -1, '*')||' '
||RPAD('*', lc_width_visible -1, '*')||' '
||RPAD('*', lc_width_item_type -1, '*')||' '
||RPAD('*', lc_width_col_name -1, '*')||' '
||RPAD('*', lc_width_datatype -1, '*')||' '
||RPAD('*', lc_width_fmt_mask -1, '*')||' '
||RPAD('*', lc_width_canvas -1, '*')||' '
||RPAD('*', lc_width_prompt -1, '*')||' '
||RPAD('*', lc_width_hint_text -1, '*')||' '
;
BEGIN
message('Writing item info...', no_acknowledge); synchronize;
l_tempfile := text_io.fopen(l_temp_filename, 'W');
-- For output in table format (ie Excel) than only 1 report header
IF l_tabular
THEN
-- Write header for item info
l_str := l_items_hdr_text;
text_io.put_line(l_tempfile, l_str);
l_str := l_items_hdr_2nd;
text_io.put_line(l_tempfile, l_str);
END IF;
-- Loop through blocks
l_cur_block := l_first_block;
WHILE NOT l_is_last_block
LOOP
l_block_count := l_block_count + 1;
l_items_in_block := 0;
-- Get type, name and datasource of the block (NONE, TABLE, STORED PROCEDURE, ...)
l_data_target_type := get_block_property(l_cur_block, dml_data_target_type);
IF l_data_target_type = 'TABLE' -- NAME of datatarget with tables only
THEN
l_data_target_name := get_block_property(l_cur_block, dml_data_target_name);
ELSE
l_data_target_name := '';
END IF;
/**/
-- Get property lengths of block properties
l_width_block_name := GREATEST(l_width_block_name , NVL(LENGTH(l_cur_block) , 0));
l_width_data_target_type := GREATEST(l_width_data_target_type, NVL(LENGTH(l_data_target_type), 0));
l_width_data_target_name := GREATEST(l_width_data_target_name, NVL(LENGTH(l_data_target_name), 0));
/**/
-- If not table format, than header and block info for each block
IF NOT l_tabular
THEN
-- Header + block info schrijven
text_io.put_line(l_tempfile, '***************************************************');
text_io.put_line(l_tempfile, 'Block: '||l_cur_block);
-- Info m.b.t. data-target van het block schrijven
text_io.put_line( l_tempfile
, ' Data target: '||l_data_target_type||' '
||l_data_target_name
);
text_io.put_line(l_tempfile, '***************************************************');
-- Write Header for items
l_str := l_items_hdr_text;
text_io.put_line(l_tempfile, l_str);
l_str := l_items_hdr_2nd;
text_io.put_line(l_tempfile, l_str);
END IF;
-- Get first and last items
l_first_item := get_block_property(l_cur_block, first_item);
l_last_item := get_block_property(l_cur_block, last_item);
-- Loop through the items
l_is_last_block := ( NVL(l_cur_block, l_last_block) = l_last_block );
l_cur_item := l_first_item;
l_is_last_item := FALSE;
WHILE NOT l_is_last_item
LOOP
message('Block: '||l_cur_block||' Item: '||l_cur_item, no_acknowledge);
synchronize;
l_items_in_block := l_items_in_block + 1;
l_item_count := l_item_count + 1;
l_is_last_item := ( NVL(l_cur_item, l_last_item) = l_last_item );
-- Displayed / non displayed? Format output
l_visible := REPLACE(REPLACE(get_item_property(l_cur_block||'.'||l_cur_item, visible)
, 'TRUE', 'Disp. '
)
, 'FALSE', ' '
);
-- Get other item properties
l_item_type := get_item_property(l_cur_block||'.'||l_cur_item, item_type);
IF l_item_type IN ('LIST', 'TEXT ITEM', 'DISPLAY ITEM', 'RADIO GROUP', 'CHECKBOX')
THEN
IF l_item_type = 'TEXT ITEM'
THEN
l_fmt_mask := get_item_property(l_cur_block||'.'||l_cur_item, format_mask);
ELSE
l_fmt_mask := '';
END IF;
l_datatype := get_item_property(l_cur_block||'.'||l_cur_item, datatype);
IF NVL(l_data_target_type, 'NONE') = 'TABLE'
THEN
-- Get tablename.columnname in l_col_name
l_col_name := l_data_target_name||'.'||get_item_property(l_cur_block||'.'||l_cur_item, column_name);
-- If l_col_name is empty, then the value ends with a period "."; get rid of it
IF SUBSTR(l_col_name, LENGTH(l_col_name)) = '.'
THEN
l_col_name := SUBSTR(l_col_name, 1, LENGTH(l_col_name)-1);
END IF;
END IF;
ELSE
l_datatype := '';
l_col_name := '';
END IF;
l_canvas := get_item_property(l_cur_block||'.'||l_cur_item, item_canvas);
l_prompt := get_item_property(l_cur_block||'.'||l_cur_item, prompt_text);
l_hint_text := get_item_property(l_cur_block||'.'||l_cur_item, hint_text);
-- Format output string item info
l_str := TRANSLATE( RPAD(l_cur_block , lc_width_block_name)
||RPAD(l_cur_item , lc_width_item_name )
||RPAD(NVL(l_visible , ' '), lc_width_visible )
||RPAD(NVL(l_item_type, ' '), lc_width_item_type )
||RPAD(NVL(l_col_name , ' '), lc_width_col_name )
||RPAD(NVL(l_datatype , ' '), lc_width_datatype )
||RPAD(NVL(l_fmt_mask , ' '), lc_width_fmt_mask )
||RPAD(NVL(l_canvas , ' '), lc_width_canvas )
||RPAD(NVL(l_prompt , ' '), lc_width_prompt )
||l_hint_text
, CHR(10)||CHR(13)
, ' '
)
;
-- Write to file
text_io.put_line(l_tempfile, l_str);
-- Get next item
l_cur_item := get_item_property(l_cur_block||'.'||l_cur_item, nextitem);
/* */
-- Get item property lengths
l_width_item_name := GREATEST(l_width_item_name, NVL(LENGTH(l_cur_item) , 0));
l_width_col_name := GREATEST(l_width_col_name , NVL(LENGTH(l_col_name) , 0));
l_width_visible := GREATEST(l_width_visible , NVL(LENGTH(l_visible) , 0));
l_width_item_type := GREATEST(l_width_item_type, NVL(LENGTH(l_item_type) , 0));
l_width_datatype := GREATEST(l_width_datatype , NVL(LENGTH(l_datatype) , 0));
l_width_fmt_mask := GREATEST(l_width_fmt_mask , NVL(LENGTH(l_fmt_mask) , 0));
l_width_canvas := GREATEST(l_width_canvas , NVL(LENGTH(l_canvas) , 0));
l_width_prompt := GREATEST(l_width_prompt , NVL(LENGTH(l_prompt) , 0));
l_width_hint_text := GREATEST(l_width_hint_text, NVL(LENGTH(l_hint_text) , 0));
/* */
END LOOP; -- End of item loop
-- If not tabular, write block info
IF NOT l_tabular
THEN
-- Item count within block
text_io.new_line(l_tempfile);
text_io.put_line(l_tempfile, 'Block '||l_cur_block||': '||l_items_in_block||' items');
text_io.new_line(l_tempfile);
text_io.new_line(l_tempfile);
END IF;
text_io.new_line(l_tempfile);
-- Get next block
l_cur_block := get_block_property(l_cur_block, nextblock);
END LOOP;
IF NOT l_tabular
THEN
-- Write footer (counts of blocks, items)
text_io.new_line(l_tempfile);
text_io.new_line(l_tempfile);
text_io.new_line(l_tempfile);
text_io.put_line(l_tempfile, '***************************************');
text_io.put_line(l_tempfile, 'END OF REPORT');
text_io.put_line(l_tempfile, 'Blocks: '||l_block_count||' Items: '||l_item_count);
text_io.put_line(l_tempfile, '***************************************');
text_io.new_line(l_tempfile);
END IF;
/* */
--/*
-- Enable (uncomment) code below to output max lengths of item property values
-- Display length of block properties
text_io.put_line(l_tempfile,'Max. length of block property values:');
text_io.put_line(l_tempfile,'------------------------------------ ');
text_io.put_line(l_tempfile,'l_width_block_name '||l_width_block_name );
text_io.put_line(l_tempfile,'l_width_data_target_type '||l_width_data_target_type);
text_io.put_line(l_tempfile,'l_width_data_target_name '||l_width_data_target_name);
-- Display length of item properties
text_io.new_line(l_tempfile);
text_io.put_line(l_tempfile,'Max. length of item property values:');
text_io.put_line(l_tempfile,'----------------------------------- ');
text_io.put_line(l_tempfile,'l_width_item_name '||l_width_item_name );
text_io.put_line(l_tempfile,'l_width_col_name '||l_width_col_name );
text_io.put_line(l_tempfile,'l_width_visible '||l_width_visible );
text_io.put_line(l_tempfile,'l_width_item_type '||l_width_item_type );
text_io.put_line(l_tempfile,'l_width_datatype '||l_width_datatype );
text_io.put_line(l_tempfile,'l_width_fmt_mask '||l_width_fmt_mask );
text_io.put_line(l_tempfile,'l_width_canvas '||l_width_canvas );
text_io.put_line(l_tempfile,'l_width_prompt '||l_width_prompt );
text_io.put_line(l_tempfile,'l_width_hint_text '||l_width_hint_text );
--*/
/* */
text_io.fclose(l_tempfile);
-- Message: we 're ready
message('Item info written to file '||l_temp_filename);
message('Item info written to file '||l_temp_filename, no_acknowledge);
synchronize;
EXCEPTION
WHEN OTHERS
THEN
-- If necessary, close file
IF text_io.is_open(l_tempfile)
THEN
text_io.fclose(l_tempfile);
END IF;
-- Show error in "alert"
message( 'Error in execution of procedure "Form_list_items" (Form: '
||:SYSTEM.current_form||' Block: '||l_cur_block||' Item: '||l_cur_item||')'
);
message( 'Error in execution of procedure "Form_list_items" (Form: '
||:SYSTEM.current_form||' Block: '||l_cur_block||' Item: '||l_cur_item||')'
, no_acknowledge
);
synchronize;
-- re-raise
RAISE;
END;
rem -----------------------------------------------------------------------
rem Filename: msword.pls
rem Purpose: This example creates a new Word document, inserts some text
rem in it and saves its contents into a new file.
rem Note: Will not work with Forms 9i and above as it does not
rem support OLE automation.
rem Date: 12-Sep-2002
rem Author: Sandy (get2sud@yahoo.com)
rem -----------------------------------------------------------------------
DECLARE
-- Declare the OLE objects
MyApplication OLE2.OBJ_TYPE;
MyDocuments OLE2.OBJ_TYPE;
MyDocument OLE2.OBJ_TYPE;
MySelection OLE2.OBJ_TYPE;
-- Declare handle to the OLE argument list
args OLE2.LIST_TYPE;
BEGIN
-- Create the Word.Application object and make Word visible
-- by setting the 'Visible' property to true
MyApplication := OLE2.CREATE_OBJ('Word.Application');
OLE2.SET_PROPERTY(MyApplication, 'Visible', 1);
-- get a handle on Documents collection
MyDocuments:= OLE2.GET_OBJ_PROPERTY(MyApplication, 'Documents');
-- Add a new document to the Documents collection
Mydocument := OLE2.INVOKE_OBJ(MyDocuments, 'Add');
-- Get a handle on Selection object
MySelection:=OLE2.GET_OBJ_PROPERTY(MyApplication, 'Selection');
-- Insert the text 'Hello Word97!' into word document
OLE2.SET_PROPERTY(MySelection, 'Text', 'Hello Word97!');
-- Save the document to the filesystem as EXAMPLE.DOC
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'C:\DOCS\EXAMPLE.DOC');
OLE2.INVOKE(MyDocument, 'SaveAs', args);
OLE2.DESTROY_ARGLIST(args);
-- Close the document
OLE2.INVOKE(MyDocument, 'Close');
-- Release the OLE objects
OLE2.RELEASE_OBJ(MySelection);
OLE2.RELEASE_OBJ(MyDocument);
OLE2.RELEASE_OBJ(MyDocuments);
OLE2.RELEASE_OBJ(MyApplication);
END;
Oracle Applications Scripts
o Print Oracle Apps versions
o Display status of all Concurrent Managers
rem -----------------------------------------------------------------------
rem Filename: appsver.sql
rem Purpose: Print Oracle Apps versions
rem Author: Anonymous
rem -----------------------------------------------------------------------
SELECT substr(a.application_short_name, 1, 5) code,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG')
/
rem -----------------------------------------------------------------------
rem Filename: appinfo.sql
rem Purpose: Script to display status of all the Concurrent Managers
rem Author: Anonymous
rem -----------------------------------------------------------------------
set head on
column OsId format A10 justify left
column CpId format 999999
column Opid format 999
column Manager format A30
column Status format A20
column Started_At format A30
column Cpid heading 'Concurrent|Process ID'
column OsId heading 'System|Process ID'
column Opid heading 'Oracle|Process ID'
column Manager heading 'Concurrent Manager Name'
column Status heading 'Status|of Concurrent|Manager'
column Started_At heading 'Concurrent Manager|Started at'
select distinct Concurrent_Process_Id CpId, PID Opid,
Os_Process_ID Osid, Q.Concurrent_Queue_Name Manager,
P.process_status_code Status,
To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
from Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process
where Q.Application_Id = Queue_Application_ID
and Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
and Spid = Os_Process_ID
and Process_Status_Code not in ('K','S')
order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name
/
No comments:
Post a Comment