Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Khalid.

Asked: September 05, 2016 - 7:20 pm UTC

Last updated: September 07, 2016 - 12:47 pm UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

Hi,

In my company most of the developers generate too much redo because :-
1. they load txt files into staging table.
2. update the table with missing information "lockup table and select for each rows"
3. insert the data from the staging table to permanent table.
4. deleting the rows from the staging table.

keep repeat steps 1..4 and generate too much redo.

some of them also generate to much redo because.

1. loading the data to staging table.
2. create new staging table from the first table and third table from the second table and fourth table from the third table.
what i mean they keep create table from table three or five times till they get the final data and then insert the result to final table.

I need to tune these actions but I don't know how or what is the best solution.

can you please help me?

my problem they generate too much redo " backup & storage problem" and i have standby database for first problem that is slow because of eager size.


Regards,
Khalid.

and Connor said...

Well.... here's the problem. If you have a standby, my guess is that you have specified "alter database force logging", which means no matter what you do, these operations will generate logs.

You *could* turn off forced logging, but that opens you up to a high risk proposition of missing some critical data changes in your standby database and never knowing about it.

A couple of things you could explore:

1. they load txt files into staging table.
=> Make the staging table a global temporary table
=> You will use redo, but it will be less

4. deleting the rows from the staging table.
=> Use the truncate command, not delete.

Hope this helps.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rights?

A reader, September 06, 2016 - 5:41 am UTC

"Use truncate"
What solution to grant truncate since the app user don't have drop table priv? Could pls elaborate for 10, 11 and 12 version s?

Connor McDonald
September 06, 2016 - 9:29 am UTC

If you're connected as the user that owns the table, you can run a truncate. If you're connected as a different user then:

- Create a procedure to truncate the table in the owner's schema
- Grant your user execute privs on this proc

e.g.

create or replace procedure truncate_tab is
begin
  execute immediate 'truncate table t';
end;
/

grant execute on truncate_tab to app_user;


Chris

External Tables for Staging

A reader, September 06, 2016 - 6:56 am UTC

You might consider using External Tables for your staging tables instead of loading data into physical oracle tables.

https://oracle-base.com/articles/9i/external-tables-9i

Fahd Bahoo.
Connor McDonald
September 06, 2016 - 9:30 am UTC

Yep, that could help.

works? tested?

A reader, September 06, 2016 - 10:15 am UTC

create or replace procedure truncate_tab is
begin
execute immediate 'truncate table t';
end;
/

grant execute on truncate_tab to app_user;


could you please test this? didnt work
Chris Saxon
September 06, 2016 - 4:02 pm UTC

That's just an example - unless you have a table called t and a user called app_user in your database it's not going to work!

You need to change these to match your environment.

Chris

procedure dosnt work but pkg work fine

A reader, September 06, 2016 - 12:38 pm UTC

this works fine within a package but not procedure
----------------------------------------------


create or replace package my_pkg as
procedure truncate_tab (p_owner in varchar2, p_table in varchar2);
end;
/

CREATE OR REPLACE package body my_pkg as

procedure truncate_tab (p_owner in varchar2, p_table in varchar2) is
begin
execute immediate 'truncate table '||upper(p_owner)||'.'||upper(p_table);
end;

/
Connor McDonald
September 07, 2016 - 2:33 am UTC

works fine in a proc too

Connor

Are you serious?

A reader, September 06, 2016 - 4:28 pm UTC

Do you think I am stupid ?
Ok you have table T and your proc
I let you try and make it work
Chris Saxon
September 07, 2016 - 2:29 am UTC 

SQL> grant connect to app_user identified by app_user;

Grant succeeded.

SQL> sho user
USER is "MCDONAC"
SQL> create table t ( x int );

Table created.

SQL> create or replace procedure truncate_tab is
  2  begin
  3  execute immediate 'truncate table t';
  4  end;
  5  /

Procedure created.

SQL> grant execute on truncate_tab to app_user;

Grant succeeded.

SQL> conn app_user/app_user
Connected.

SQL> exec mcdonac.truncate_tab;

PL/SQL procedure successfully completed.


So.... if you're asking what my response should be to both of your questions...well :-)

Cheers,
Connor

Sure

A reader, September 07, 2016 - 7:14 am UTC

"So.... if you're asking what my response should be to both of your questions...well :-) "

Sure go ahead. But just before tell me what version is it 10? And do a complete test . Cannot see the app _ user priv there!

And moreover ps : my 2 questions are related to your previous response not to the fact that thus is technically feasible or not ...very smart "man".

Bests

Connor McDonald
September 07, 2016 - 9:30 am UTC

Looks like Connor did a complete test to me?

app_user isn't a privilege. It's a user. And a made up one at that! So it won't exist unless you create it.

You need to replace this with whatever user/role you want to grant privileges to in your database.

Chris

Really hard

A reader, September 07, 2016 - 10:06 am UTC

Dear Chris
Want to be sure that app user has not the drop any tabke priv. .otherwise the question make no sence right?
So a complete test is a complete test
Connor McDonald
September 07, 2016 - 12:47 pm UTC

Sigh...


"grant connect to app_user identified by app_user;"

*creates* the user. There is no additional privs. Its a *complete* test

Connor
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.