Improve Bulk Insert with Array Processing

DECLARE
    CURSOR c_cur IS
      SELECT COL1, COL2, COL3, COL4
        FROM R2.TAB2 E
       WHERE E.ID = 5;

    TYPE c_array_t IS TABLE OF R1.TAB1%ROWTYPE; -- define collection for rows selected

    k_rows_max   CONSTANT INTEGER := 500; -- defines the maximum rows per fetch
    l_collect    c_array_t; -- define variable of rows collection
    
BEGIN
    OPEN c_cur;

    LOOP
        FETCH c_cur                  -- fetch up to LIMIT rows from cursor
        BULK COLLECT INTO l_collect LIMIT k_rows_max;

        FORALL i IN 1 .. l_collect.COUNT -- run insert for ALL rows in the collection
            INSERT INTO R1.TAB1
                 VALUES (l_collect (i).COL1,
                         l_collect (i).COL2,
                         l_collect (i).COl3,
                         l_collect (i).COL4);

        EXIT WHEN c_cur%NOTFOUND;                  -- no more rows so exit
        
    END LOOP;

    CLOSE c_cur;

    COMMIT;                                                  -- JUST 1 COMMIT;
    
END;

Improve Bulk Update with Array Processing

DECLARE
    CURSOR c_dim_cur IS
        SELECT /*+ parallel(10) */  
               k.col1, t.ID, t.col4
          FROM r1.tab1 t, r1.tab2 k
         WHERE t.no = k.no
           AND t.insert_sysdate < TRUNC (SYSDATE)
           AND t.col2 = '123456789'
           AND t.no IS NOT NULL;

    TYPE c_dim_array_t_1 IS RECORD
    (
        col1    r1.tab1.col2%TYPE,
        id      r1.tab1.ID%TYPE,
        col4    r1.tab1.col4%TYPE
    );

    TYPE c_dim_array_t IS TABLE OF c_dim_array_t_1; -- define collection for rows selected

    k_dim_rows_max   CONSTANT INTEGER := 5000; -- defines the maximum rows per fetch
    l_dim_collect             c_dim_array_t; -- define variable of rows collection
    
BEGIN
    OPEN c_dim_cur;
    LOOP
        FETCH c_dim_cur                  -- fetch up to LIMIT rows from cursor
        BULK COLLECT INTO l_dim_collect LIMIT k_dim_rows_max;

        FORALL i IN 1 .. l_dim_collect.COUNT -- run update for ALL rows in the collection
            UPDATE /*+ index(tab1 PK_tab1IX) parallel(10) */
                   r1.tab1 f
               SET f.col2 = l_dim_collect (i).col1
             WHERE f.id = l_dim_collect (i).id
               AND f.col4 = l_dim_collect (i).col4;

        EXIT WHEN c_dim_cur%NOTFOUND;                  -- no more rows so exit

        COMMIT;
        
    END LOOP;

    CLOSE c_dim_cur;

    COMMIT;
END;

Cancel SQL

From Oracle 12.2:

 

SQL> select S.SID,S.SERIAL#,s.status

from v$session s

where s.osuser='shakeri'

and s.sql_id='a4ayr73ytbp8p';

 

       SID    SERIAL#
----------     ----------
     10923      64869
 

 

SQL> alter system cancel sql '10923,64869';

 

اجرای یک اسکریپت خاص در چند اسکیمای مختلف ( DBMS_SYS_SQL )

اگر بخواهیم یک اسکریپت خاص را در چند اسکیما اجرا کنیم ، راه حل های زیر وجود دارد ( البته به جز این موارد ممکن است راه حل های دیگری هم وجود داشته باشد ) :

1- به تک تک آن اسکیماها وصل شده و اسکریپت مورد نظر را اجرا کنیم.

2- به یک اسکیمای خاص وصل شده و با دستور alter session set current_schema=X ،  اسکیمای خود را تغییر دهیم و اسکریپت مورد نظر را اجرا کنیم.

3- راه حل سوم استفاده از پکیج DBMS_SYS_SQL است. کاربرد این پکیج شبیه DBMS_SQL است با این تفاوت که یک کارکرد اضافه تر با نام "parse as user"  دارد که به شما اجازه میدهد یک SQL خاص را برای دیگر اسکیماها در دیتابیس اجرا کنید.

فقط قبل از اجرا باید به دو نکته خیلی خیلی مهم توجه کنید :

- این پکیج در اوراکل جزو undocumented packageها به حساب می آید و اوراکل به صورت internally برای سرویس های خود از آن استفاده میکند. و برای استفاده در خارج از اوراکل در نظر گرفته نشده است.

- این پکیج بسیار پکیج قدرتمندی است ، زیرا هر دستوری را با آن میتوان اجرا کرد ، به همین دلیل باید شدیدا این موضوع تحت کنترل قرار گیرد که این پکیج توسط چه user قرار است اجرا شود.

با در نظر گرفتن نکات بالا من یک مثال از نحوه استفاده از این پکیج را در زیر شرح داده ام :

1-Create User

create user TEST identified by TEST quota unlimited on USERS;

grant connect,create procedure,select on dba_users to TEST;

grant execute on sys.dbms_sys_sql to TEST;

 

create user C identified by C quota unlimited on USERS;

grant CONNECT,CREATE TABLE to C;

 

create user D identified by D quota unlimited on USERS;

grant CONNECT,CREATE TABLE to D;

 

2-Create Procedure In TEST User

create or replace procedure execute_script_prc(v_script in varchar2) as

  l_cursors dbms_sql.number_table;

  l_result  number;

begin

 for cur in (select user_id 

                from dba_users 

               where username in ('C','D')) loop

  --parse the cursor only if we haven't already

  if (not l_cursors.exists(cur.user_id)) then

    l_cursors(cur.user_id) := sys.dbms_sys_sql.open_cursor;

    --parsing anonymous pl/sql block

    sys.dbms_sys_sql.parse_as_user(

    c => l_cursors(cur.user_id), 

    statement => v_script, 

    language_flag => dbms_sql.native,

    userid => cur.user_id);

  end if;

 --execute script

 l_result := sys.dbms_sys_sql.execute(l_cursors(cur.user_id));

 end loop;

end execute_script_prc;

 

3-Execute script on C,D schemas

--Create table T1 in C,D schemas

exec test.execute_script_prc('create table T1 ( id number )');

--Insert to table T1 in C,D schemas

exec test.execute_script_prc('begin insert into T1 values(1); commit; end;');

• منبع

https://blog.pythian.com/using-dbms_sys_sql-to-execute-statements-as-another-user/

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1325202421535

تغییر پسورد user یک دیتابیس دیگر با استفاده از db link

اگر بخواهیم پسورد یک user را که در دیتابیس دیگری قرار دارد با استفاده از database link تغییر دهیم ، میتوانیم از پروسیجر زیر برای انجام این کار استفاده کنیم.

create or replace procedure change_pass_prc authid current_user as

begin

  cursor usr_cur is

    select username from dba_users@pass_link;

  begin

    for usr_rec in usr_cur loop

      if usr_rec.username = 'USR1' then

        declare

          job binary_integer;

        begin

          dbms_job.submit@pass_link(job,'begin execute immediate ''alter user ' || usr_rec.username ||

                                                             ' identified by "USR1" ''; end;');

          commit;

        end;

        dbms_output.put_line(usr_rec.username || ' password changed.');

      end if;

    end loop; 

  when

  exception when others then 

     raise_application_error(-20000,sqlerrm);

end change_pass_prc;

•  منبع 

https://community.oracle.com/thread/709410

استفاده از sequence در قسمت DEFAULT ستون یک جدول

یکی از مواردی که همواره در پایگاه داده ها مطرح است پرشدن یک ستون با استفاده از اعداد پشت سر هم است . در این خصوص راه کارهای زیادی در پایگاه داده اوراکل وجود دارد که یکی از آنها استفاده از sequence می باشد، در نسخه های قبل از 12C برای پر کردن ستون ها به وسیله sequence یا باید موقع وارد کردن رکورد از nextval استفاده کرد و یا با استفاده از trigger اینکار را انجام داد. در نسخه 12C این امکان وجود دارد که از sequence وتابع nextval در قسمت default ستون استفاده شود.

;create sequence test_seq

create table my_tab

(

, id number default test_seq.nextval

  (name varchar2(20

;(

;('insert into my_tab (name) values('aaaa

;commit

;select * from my_tab

                ID   NAME

---------- --

        1aaaa