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 set vim/gvim on windows?
Using the following steps one can setup vim/gvim to run on windows.
1) Add the following new variable VIM on your system. Example: VIM=C:\apps\vim\vim73 (Where C:\apps\vim\vim73 is where vim/gvim is installed)
2) Copy the file %VIM%\vimrc_example.vim as _vimrc in %VIM% folder
3) Add the following 2 lines at the end in %VIM%\_vimrc so it sets up vim for windows
source $VIM/mswin.vim
behave mswin
Example of using tracefile_identifier
Using “tracefile_identifier” Oracle will add that to the name of the user’s trace filename so the file can easily be identified. For example if one needs to trace multiple users session who have different schema or OS user then using “tracefile_identifier” one can identify the trace file specific to a user.
Example: The following trigger will create a trace file with the user’s OSuser and machine part of the filename for any user who login with “SCOTT”
CREATE OR REPLACE TRIGGER sys.session_trace_on
– to be created by sys user
AFTER LOGON ON database
DECLARE
v_machinename VARCHAR2(64);
v_ora_username VARCHAR2(30) DEFAULT NULL;
v_os_username VARCHAR2(30);
v_sid NUMBER;
v_serial NUMBER;
v_program VARCHAR2(48);
v_numuser NUMBER;
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session
WHERE sid = userenv('sid')
and username = 'SCOTT';
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, v_os_username, v_machinename, v_program;
IF c1%FOUND THEN
— DBMS_SESSION.set_sql_trace (TRUE);
v_machinename := replace(replace(v_machinename, '\', '_'), '/', '_');
v_os_username := replace(replace(v_os_username, '\', '_'), '/', '_');
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''' || trim(v_os_username) || '''';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
CLOSE c1;
END;
/
Trace file created:
ls testdb_ora_5480_CAL-AMIN_Administrator.trc
How to add a comment when changing a parameter?
In “ALTER SYSTEM” one can include a comment which gets recorded in the spfile and also in v$parameter as shown below. It can be used to record why parameter was changed.
Example:
SQL> alter system set open_cursors=1000 comment='07-Jan-2012 Changed AJ needed for application XYZ';
SQL> select value, update_comment from v$parameter where name = 'open_cursors';
VALUE
——————————————————————————–
UPDATE_COMMENT
——————————————————————————–
400
$ strings spfileTESTDB.ORA | grep open_cursors
*.open_cursors=400#07-Jan-2012 Changed AJ needed for application XYZ
How to add a new not null column in a table without providing a default value?
The following shows how to add in a table a new column that is NOT NULL when a table already has rows without providing a default value.
SQL> create table scott.testn ( x number not null);
Table created.
SQL> insert into scott.testn values ( 11 );
1 row created.
SQL> commit;
Commit complete.
-- First step add new column without the NULL constraint
SQL> alter table scott.testn add nn number;
Table altered.
-- Then modify the column to be NOT NULL using NOVALIDATE
SQL> alter table scott.testn modify nn not null novalidate;
Table altered.
-- Shows the table doesn't show the NOT NULL constraint on the column NN yet
SQL> desc scott.testn;
Name Null? Type
—————————————– ——– ———————
X NOT NULL NUMBER
NN NUMBER
-- As shown above even though not shown the NOT NULL constraint is there as new values cannot be inserted in the table that are NULL
SQL> insert into scott.testn values (12, null);
insert into scott.testn values (12, null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“SCOTT”.”TESTN”.”NN”)
-- Now we can enable the constraint by updating the rows that have NULL value so we need to find the constraint name
SQL> select column_name, constraint_name from dba_cons_columns where table_name = 'TESTN' and owner = 'SCOTT';
COLUMN_NAME CONSTRAINT_NAME
——————– ——————————
X SYS_C005261
NN SYS_C005262
-- Rows update
SQL> update scott.testn set nn = 1 where nn is null;
1 row updated.
SQL> commit;
Commit complete.
--
SQL> alter table scott.testn modify constraint SYS_C005262 validate;
Table altered.
-- Shows the NOT NULL constraint on the column NN
SQL> desc scott.testn;
Name Null? Type
—————————————– ——– —————————-
X NOT NULL NUMBER
NN NOT NULL NUMBER
-- shows that one can't add a new column not null as table is not empty
SQL> alter table scott.testn add mm number not null;
alter table scott.testn add mm number not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
-- shows it can't add a new column not null with novalidate as table is not empty
SQL> alter table scott.testn add mm number not null novalidate;
alter table scott.testn add mm number not null novalidate
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
