Example of bulk collect and performance using different values of limit
Sample code that shows using BULK COLLECT and performance of using different values for LIMIT.
declare
cursor l_cur is select * from scott.emp;
type emp_tbl is table of l_cur%rowtype index by pls_integer;
l_emp emp_tbl;
limit_in number;
i number;
begin
limit_in := &limit_param;
open l_cur;
loop
fetch l_cur bulk collect into l_emp limit limit_in;
for i in 1..l_emp.count
loop
null;
end loop;
exit when l_emp.count < limit_in;
end loop;
close l_cur;
end;
Limit value 1: 25.76 seconds
Limit value 10: 03.54 seconds
Limit value 100: 01.28 seconds
Limit value 1000: 00.95 seconds
Limit value 2000: 01.00 seconds
Limit value 3000: 01.03 seconds
Limit value 5000: 01.06 seconds
Limit value 10000: 01.20 seconds
As you notice in the case above as one increases the value of LIMIT after 1000, the execution time increases slightly. The following was timed using “set timing on” on SQL*Plus.
How to use PRAGMA EXCEPTION_INIT?
PRAGMA EXCEPTION_INIT – allows one to map ORA- error and it can be raised in PL/SQL code. The SQL Error number passed in “EXCEPTION_INIT” is the same as error code except for “NO_DATA_FOUND” ORA-01403 which is 100.
See http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#BABGIIBI – Summary of Predefined PL/SQL Exceptions
Example:
declare
no_rows_found exception;
pragma exception_init(no_rows_found, 100);
begin
raise no_rows_found;
end;
/
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
declare
too_many_rows exception;
pragma exception_init(too_many_rows, -1422);
begin
raise too_many_rows;
end;
/
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5
Change auto extensible from all datafiles to OFF
set serveroutput on
BEGIN
for i in (select file_name,autoextensible from dba_data_files where autoextensible = ‘YES’) loop
dbms_output.put_line(‘File name is: ‘||i.file_name||’ and autoextensible is: ‘||i.autoextensible);
execute immediate ‘alter database datafile ”’||i.file_name||”’ autoextend off’;
end loop;
END;
/
Script to find Oracle Bind Variables
Filed under: General DBA, PL/SQL, Scripts, SQL*Plus, Tuning
I use this simple script to find bind variables with or without sql_id.
select
sql_id,
t.sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from
v$sql t
join v$sql_bind_capture b
using (sql_id)
where
b.value_string is not null
– and sql_id=’974ju5zc5whfn’;
How does one count all tables in a schema
set serveroutput on
declare
row_cnt number;
BEGIN
for i in (select table_name from user_tables) loop
execute immediate ‘select count(*) from ‘|| i.table_name into row_cnt;
dbms_output.put_line(‘Table sum for ‘||i.table_name||’ is ‘ ||row_cnt);
end loop;
END;
/
Grant access to source (procedure,function,package) without granting execute access
Depending on tool one can grant access to users if they need access to look at code for store procedure, function and package without giving the user execute access.
For AquaStudio for example one can set up user with following access which would allow to look at the code, the access needs to be granted by sys. In this setup you are replace the view all_objects with dba_objects and all_source with dba_source.
grant select on sys.dba_source to ;
create synonym .all_source for sys.dba_source;
grant select on sys.dba_objects to ;
create synonym .all_objects for sys.dba_objects;
Generate DDL for all Database Links
Filed under: DBMS_METADATA, General DBA, PL/SQL, Scripts
Simple script to generate DDL for all database links in the database:
set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,a.db_link,a.owner) || ‘/’ FROM dba_db_links a
/
Script to generate Index DDL from a table with DBMS_METADATA.GET_DEPENDENT_DDL
Filed under: DBMS_METADATA, General DBA, PL/SQL, Scripts
Here is a simple script to generate the DDL code for all indexes in a particular table.
set heading off;
set echo off;
Set pages 999;
set long 90000;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL(‘INDEX’,UPPER(‘&table_name’),UPPER(‘&schema_name’))
from dual
/
set heading on;
set echo on;
Oracle to_char format (to_date)
select to_char(sysdate,’DD/MM/YYYY HH24:MI:SS’) from dual;
Would return something like: 20/04/2010 19:42:35
Format mask Description CC Century
SCC – Century BC prefixed with –
YYYY – Year with 4 numbers
SYYY – Year BC prefixed with –
IYYY – ISO Year with 4 numbers
YY – Year with 2 numbers
RR – Year with 2 numbers with Y2k compatibility
YEAR – Year in characters
SYEAR – Year in characters, BC prefixed with –
BC – BC/AD Indicator *
Q – Quarter in numbers (1,2,3,4)
MM – Month of year 01, 02…12
MONTH – Month in characters (i.e. January)
MON – JAN, FEB
WW – Weeknumber (i.e. 1)
W – Weeknumber of the month (i.e. 5)
IW – Weeknumber of the year in ISO standard.
DDD – Day of year in numbers (i.e. 365)
DD – Day of the month in numbers (i.e. 28)
D – Day of week in numbers(i.e. 7)
DAY – Day of the week in characters (i.e. Monday)
FMDAY – Day of the week in characters (i.e. Monday)
DY – Day of the week in short character description (i.e. SUN)
J – Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH – Hournumber of the day (1-12)
HH12 – Hournumber of the day (1-12)
HH24 – Hournumber of the day with 24Hours notation (0-23)
AM – AM or PM
PM – AM or PM
MI – Number of minutes (i.e. 59)
SS – Number of seconds (i.e. 59)
SSSSS – Number of seconds this day.
DS – Short date format. Depends on NLS-settings. Use only with timestamp.
DL – Long date format. Depends on NLS-settings. Use only with timestamp.
E – Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE – The full era name
FF – The fractional seconds. Use with timestamp.
FF1..FF9 – The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM – Fill Mode: suppresses blianks in output from conversion
FX – Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I – the last 3,2,1 digits of the ISO standard year. Output only
RM – The Roman numeral representation of the month (I .. XII)
RR – The last 2 digits of the year.
RRRR – The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC – Century. BC dates are prefixed with a minus.
CC – Century
SP – Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns “ten”
SPTH – Spelled and ordinal format; 1 results in first.
TH – Converts a number to it’s ordinal format. For example 1 becoms 1st.
TS – Short time format. Depends on NLS-settings. Use only with timestamp.
TZD – Abbreviated time zone name. ie PST.
TZH – Time zone hour displacement.
TZM – Time zone minute displacement.
TZR – Time zone region
X – Local radix character. In america this is a period (.)
Kill session with TOAD or other tools
SQL>SQLPLUS ‘/AS SYSDBA’
CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_program sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_program
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_program ) LIKE ‘%TOAD%’ OR UPPER(v_prog) LIKE ‘%T.O.A.D%’ OR — Toad
UPPER(v_program ) LIKE ‘%SQLNAV%’ OR — SQL Navigator
UPPER(v_program ) LIKE ‘%PLSQLDEV%’ OR — PLSQL Developer
UPPER(v_program ) LIKE ‘%BUSOBJ%’ OR — Business Objects
UPPER(v_prog) LIKE ‘%EXCEL%’ — MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, ‘Development tools are not allowed here.’);
END IF;
END;
/
SHOW ERRORS
