Home » RDBMS Server » Server Administration » INSERT without archive (oracle 10g)
INSERT without archive [message #394158] Wed, 25 March 2009 21:15 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I have 1 script which includes INSERT statement.
But i done want to generate archive log for those statements.

INSERT INTO bk_contents_ins
SELECT  REPLACE(REPLACE( sql_redo, ',NULL);', ');' ),'"MSG_T","")','"MSG_T")')
FROM bk_contents 
WHERE row_id =i.row_id
AND UPPER(SQL_REDO) LIKE 'INSERT INTO ;


How can i do that?

Thanks,

[Updated on: Wed, 25 March 2009 21:18]

Report message to a moderator

Re: INSERT without archive [message #394159 is a reply to message #394158] Wed, 25 March 2009 21:19 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Do not have archiving enabled.

[Updated on: Wed, 25 March 2009 21:20]

Report message to a moderator

Re: INSERT without archive [message #394160 is a reply to message #394158] Wed, 25 March 2009 21:21 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
But for only 1 table i dont want to make archive generation.
For whole database i want to use in archive log mode.

Thanks,
Re: INSERT without archive [message #394162 is a reply to message #394158] Wed, 25 March 2009 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It is all or nothing.
The choice is yours.
Re: INSERT without archive [message #394166 is a reply to message #394158] Wed, 25 March 2009 22:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
alter table bk_contents_ins nologging;
insert /*+ append */ ...
alter table bk_contents_ins logging;
backup of the database

This will minimize the redo logs generated.

Regards
Michel
Re: INSERT without archive [message #394191 is a reply to message #394158] Thu, 26 March 2009 00:42 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks Smile
But why backup of full database?
Need to have backup of bk_contents_ins tables essential?

[Updated on: Thu, 26 March 2009 00:42]

Report message to a moderator

Re: INSERT without archive [message #394192 is a reply to message #394158] Thu, 26 March 2009 00:48 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>But why backup of full database?
Your data, your choice.
Re: INSERT without archive [message #394291 is a reply to message #394191] Thu, 26 March 2009 07:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you insert data without generating Redo logs, then when you come to do a recovery, there will be no redo data available for those tables, and the data in them will not be recovered.

A Full backup back up all committed data as of that point in time, and so will include the data you've just added.

It is rare that not generating redo saves enough time to be worth the extra hassle - why do you think that you want to avoid generating Redo for these transactions?

Is it possible that you're getting Undo and Redo data confused?
Previous Topic: DBMS_STATS.GATHER_SCHEMA_STATS
Next Topic: DBverify Vs RMAN Validate
Goto Forum:
  


Current Time: Sun Dec 04 12:48:00 CST 2016

Total time taken to generate the page: 0.14146 seconds