PLsql bilinmesi gerekenler -3

5-DBMS_WARNING paketi:
Detaylı Debug ve Compiling işlemleri
– plsql_warnings init.ora parameter
• 3 şeçenekli (enable, disable, error)
• 3 uyarı seviyeli
– Severe: PLW aralık 5000 to 5999
– Informational: PLW aralık 6000 to 6249
– Performance: PLW aralık 7000 to 7249
• Default DISABLE:ALL

select name, value, isses_modifiable, issys_modifiable
from v$parameter
where name like ‘plsql_warning%’
NAME VALUE ISSES ISSYS_MOD
————– ————————————– —– ———
plsql_warnings DISABLE:ALL TRUE IMMEDIATE
alter system set plsql_warnings=’enable:severe’;
System altered.
select name, value, isses_modifiable, issys_modifiable
from v$parameter
where name like ‘plsql_warning%’
NAME VALUE ISSES ISSYS_MOD
————– ——————————————- —— ——–
——————————————- —— ——–
plsql_warnings DISABLE:INFORMATIONAL, DISABLE:PERFORMANCE
ENABLE:SEVERE

alter system set plsql_warnings=’enable:all’;
alter session set plsql_warnings = ‘enable:(7000, 7203)’
alter system set plsql_warnings=’enable:performance’;

EXECUTE dbms_warning.set_warning_setting_string(‘ENABLE:ALL’,’SYSTEM’);
PL/SQL procedure successfully completed.

select dbms_warning.get_warning_setting_string
from dual;

GET_WARNING_SETTING_STRING
————————————————————-
ENABLE:ALL

Compile sırasında uyarı mesajını alabiliriz. Bunun show error yada user_errors viewdan görebiliriz.

SQL> show errors
Errors for PROCEDURE MY_PROC:
LINE/COL ERROR
——– —————————————————-
2/2 PLW-07203: parameter ‘P_DATE_INFO’ may benefit from
use of the NOCOPY compiler hint

6-DBMS_APPLICATION_INFO

Gerçek zamanlı asynchronous uygulama bilgisi

SET_MODULE Procedure
– Updates V$SESSION ve V$SQLAREA
– MODULE ve ACTION kolonları
– commite gerek yok
– Syntax
– V$SESSION Updated anında
– V$SQLAREA Updated yeni calıştırmalar için

DBMS_APPLICATION_INFO.SET_MODULE
(module_name VARCHAR2, action_name VARCHAR2);

SELECT sid, serial#, username, module, action
FROM v$session
WHERE USERNAME = ‘PLSQL_USER’;

SID SERIAL# USERNAME MODULE ACTION
—- ———- ———– —————– ——
7 7 PLSQL_USER SQL*Plus

DBMS_APPLICATION_INFO.SET_MODULE
(‘Package: TEST’, ‘Procedure: MAIN’);

SELECT sid, serial#, username, module, action
FROM v$session
WHERE USERNAME = ‘PLSQL_USER’;

SID SERIAL# USERNAME MODULE ACTION
—- ——- ———– ————– —————
7 7 PLSQL_USER Package: TEST Procedure: MAIN

SELECT sql_text, module, action
FROM v$sqlarea
WHERE INSTR(UPPER(sql_text), ‘INVENTORY’) > 0;

SQL_TEXT MODULE ACTION
————————– ————— —————
SELECT * FROM s_inventory SQL*Plus
SELECT sql_text, module, a Package: TEST Procedure: MAIN
ction FROM v$sqlarea
WHERE INSTR(UPPER(s
ql_text), ‘INVENTORY’) > 0

DECLARE
CURSOR cur_employee IS
SELECT employee_id, salary, ROWID
FROM s_employee;
lv_count_num PLS_INTEGER := 0;
lv_start_time_num PLS_INTEGER;
BEGIN
lv_start_time_num := DBMS_UTILITY.GET_TIME;
FOR cur_employee_rec IN cur_employee LOOP
lv_count_num := lv_count_num + 1;
— Employee processing logic…
IF MOD(lv_count_num, 1000) = 0 THEN
DBMS_APPLICATION_INFO.SET_MODULE
(‘Records Processed: ‘ || lv_count_num,
‘Elapsed: ‘ || (DBMS_UTILITY.GET_TIME –
lv_start_time_num)/100 || ‘ sec’);
END IF;
END LOOP;
END;
/

SELECT username, module, action
FROM v$session
WHERE username = ‘PLSQL_USER’;

USERNAME MODULE ACTION
———- ————————- —————–
PLSQL_USER SQL*Plus
PLSQL_USER Records Processed: 1000 Elapsed: 4.37 sec

USERNAME MODULE ACTION
———- ————————- ——————-
PLSQL_USER SQL*Plus
PLSQL_USER Records Processed: 25000 Elapsed: 126.66 sec

Advertisements

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s