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 use dbms_xplan to display execution plan?

Using dbms_xplan.display_cursor one can see the execution plan of a SQL statement. The function takes following 3 parameters.

SQL_ID (VARCHAR2) – If passed NULL, then it would assume SQL_ID of the last SQL statement
CURSOR_CHILD_NO – If passed NULL, then it would assume child_number of last SQL Statement
FORMAT – Possible values are BASIC, TYPICAL, ALL. The default value in TYPICAL

SQL> select count(1) from table1;

COUNT(1)
———-
32

Show execution plan of last SQL statement
SQL> SELECT *FROM TABLE (DBMS_XPLAN.display_cursor());

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID dtzn8k4hzc88d, child number 0
————————————-
select count(1) from table1

Plan hash value: 1869552569

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| I_TABLE1 | 32 | 1 (0)| 00:00:01 |
——————————————————————–

14 rows selected.

Using sql_id, sql_child_number of a SQL statement that was last executed
SQL> select sql_id, sql_child_number from v$session where sid = 130;

SQL_ID SQL_CHILD_NUMBER
————- —————-
5mf7bd0jnctjv 0

SQL> SELECT * FROM TABLE (DBMS_XPLAN.display_cursor(‘dtzn8k4hzc88d’, 0));
SQL_ID dtzn8k4hzc88d, child number 0
————————————-
select count(1) from table1

Plan hash value: 1869552569

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| I_TABLE1 | 32 | 1 (0)| 00:00:01 |
——————————————————————–

14 rows selected.

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

List of all system privileges

Using the view “system_privilege_map” one can list all the system privileges or search for a specific privilege.

Example: List all privilege that have “TABLE” keyword in it.
SQL> select * from system_privilege_map where NAME like ‘%TABLE%’;

PRIVILEGE NAME PROPERTY
———- —————————————- ———-
-10 CREATE TABLESPACE 0
-11 ALTER TABLESPACE 0
-12 MANAGE TABLESPACE 0
-13 DROP TABLESPACE 0
-15 UNLIMITED TABLESPACE 0
-40 CREATE TABLE 0
-41 CREATE ANY TABLE 0
-42 ALTER ANY TABLE 0
-43 BACKUP ANY TABLE 0
-44 DROP ANY TABLE 0
-45 LOCK ANY TABLE 0
-46 COMMENT ANY TABLE 0
-47 SELECT ANY TABLE 0
-48 INSERT ANY TABLE 0
-49 UPDATE ANY TABLE 0
-50 DELETE ANY TABLE 0
-213 UNDER ANY TABLE 0
-243 FLASHBACK ANY TABLE 0

How to check if auditing is turned on and privilege auditing is turned on?

If AUDIT_TRAIL is not set to NONE then using the following SQL one can find the statements that have auditing turned on.

SQL> SELECT * FROM dba_stmt_audit_opts union SELECT * FROM dba_priv_audit_opts;

USER_NAME PROXY_NAME
—————————— ——————————
AUDIT_OPTION SUCCESS FAILURE
—————————————- ———- ———-

ALTER ANY PROCEDURE BY ACCESS BY ACCESS

ALTER ANY TABLE BY ACCESS BY ACCESS

ALTER DATABASE BY ACCESS BY ACCESS
..

How to find and enable/disable parallel DML, DDL, Query?

In the view v$session there are columns that can tell if parallel DDL, DML, Query is enabled/disabled.
PDML_ENABLED and PDML_STATUS – indicates is Parallel DML operation is enabled/disabled, the default is DISABLED.
PDDL_STATUS – Indicates if parallel DDL is enabled/disabled, by the default value is ENABLED
PQ_STATUS – Indicates if parallel query is enabled/disabled, the default value is ENABLED

The values can set at session level.

Example:
SQL> select PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$session where sid = (select sid from v$mystat
where rownum = 1);

PDM PDML_STA PDDL_STA PQ_STATU
— ——– ——– ——–
NO DISABLED ENABLED ENABLED

-- enables parallel DML
SQL> alter session enable parallel dml;

Session altered.

-- Display parallel DML is set to enabled
SQL> select PDML_ENABLED, PDML_STATUS FROM V$session where sid = (select sid from v$mystat where rownum = 1);

PDM PDML_STA
— ——–
YES ENABLED

-- To disable parallel DML
SQL> alter session disable parallel dml;

Session altered.

-- To disable parallel DDL
SQL> alter session disable parallel ddl;

Session altered.

-- To disable parallel query
SQL> alter session disable parallel query;

Session altered.

-- Print the parallel DDL, DML and Query for the current session
SQL> select PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$session where sid = (select sid from v$mystat where rownum = 1);

PDM PDML_STA PDDL_STA PQ_STATU
— ——– ——– ——–
NO DISABLED DISABLED DISABLED

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