Re: DBMS_SQL question. Help.
Date: Thu, 23 Mar 2000 20:43:14 GMT
Message-ID: <8bdvku$kfc$1_at_nnrp1.deja.com>
Hi.
- Which tablespace is used by a SQL statement using MINUS? User temporary tablespace or rollback segment?
Both.
MINUS implies sort, so temporary tablespace is used.
Rollback segment is used to supply your routine read-consistent
data ( Probably you get procedure "bumps" when relatively high
number of concurrent users/processes updating your DB and/or
you have a lot of data in some <user>_tbl/tbl2 tables.
2) Is it possible to place "set transaction use ..." in PL/SQL
procedure?
Yes. Use Oracle supplied package DBMS_TRANSACTION to do it.
Insert it before "OPEN c1;" line.
On the other hand there is another solution:
Close c1 cursor every n users and re-open it again:
DECLARE
start_id auditing_tbl.user_name%type; neof BOOLEAN := TRUE; cursor c1 ( uid auditing_tbl.user_name%type ) is select user_name from auditing_tbl WHERE username > uid ORDER BY username; user_name auditing_tbl.user_name%type; i NUMBER; BEGIN neof := TRUE; start_id := '0'; WHILE neof LOOP i := 0; OPEN c1 ( start_id ); FETCH c1 INTO user_name; WHILE c1%FOUND LOOP i := i + 1; /* || Insert your dynamic SQL here */ EXIT WHEN i = 10; FETCH c1 INTO user_name; END LOOP; CLOSE c1; IF i = 10 THEN start_id := user_name; ELSE neof := FALSE; END IF; END LOOP; CLOSE c1;
END; It closes and re-opens main cursor for each group of 10 users, so the necessary size of rollback is going to be smaller.
HTH. Michael.
In article <8bd7i9$oc5$1_at_nnrp1.deja.com>,
alan_psb_at_my-deja.com wrote:
> I developed a PL/SQL procedure as follows:
> create or replace procedure retrieve_count as
> cursor c1 is select user_name from auditing_tbl;
> user_name auditing_tbl.user_name%type;
> cursor_name number;
> string1 varchar2(1000) := '';
> t number;
> ignore number;
> begin
> open c1;
> cursor_name := dbms_sql.open_cursor;
> loop
> fetch c1 into user_name;
> string1 := 'select count(*) from (select * from ' || user_name ||
> '_tbl minus select * from ' || user_name || _tbl2')';
> dbms_sql.parse(cursor_name, string1, DBMS_SQL.V7);
> dbms_sql.define_column(cursor_name, 1, t);
> ignore := dbms_sql.execute(cursor_name);
> if dbms_sql.fetch_rows(cursor_name) > 0 then
> dbms_sql.column_value(cursor_name, 1, t);
> end if;
> dbms_output.put_line(to_char(t));
> exit when c1%notfound;
> end loop;
> dbms_sql.close_cursor(cursor_name);
> close c1;
> end;
> /
> The procedure is OK to compile. When I type "exec retrieve_count" in
> SQL*Plus, sometims the procedure cannot execute successfully (due to
not
> enough space in rollback segment). I have two questions now:
> 1) Which tablespace is used by a SQL statement using MINUS?
> User temporary tablespace or rollback segment?
> 2) Is it possible to place "set transaction use ..." in PL/SQL
> procedure? If yes, refer to retrieve_count procedure, where should
this
> statement be placed? I know that this statement should be placed
> immediately before the DML in SQL*Plus, however, I don't know where
> should this statement be placed in a PL/SQL procedure with dbms_sql
> package.
> Thanks,
> Alan
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Mar 23 2000 - 21:43:14 CET