| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: archive query
Run the following script and check syntex for insert,update and delete which are generating redos....
select
substr(username,1,10)"USER",process,sid,serial#,substr(sql_text,1,200),disk_reads,buffer_gets,rows_processed
from v$session,v$sqlarea
where sql_address=address and status = 'ACTIVE'
order by disk_reads desc
/
Regards
MOHAMMAD RAFIQ
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Tue, 04 Sep 2001 00:25:26 -0800
Hi,
Can anyone explain how I can determine which objects and/or processes are causing logging/archiving to occur? I have a (large) database whereby all tables required to be populated with data on regular batch runs have been created with nologging. The tablespaces they reside on were also created with nologging. There are no constraints (eg triggers) on these tables that could possibly be invoking logging yet my archive destination directory is filling up on a regular basis with unknown archived data!!
I have a script to remove redundant archive files but the point is why is it filling up and with what??
There are only a few tables with logging activated but these are relatively small with minimal inserts/updates.
The following entries are taken from the parameter file (don't know whether this may help)....
db_block_buffers = 70000 db_block_size = 16384 db_block_lru_latches = 4 compatible = 8.1.0 log_archive_start = TRUE log_archive_dest_1 = location=archive_directory log_archive_format = arch_%t_%s.arc log_buffer = 163840
I am considering shutting down the database and changing the status of the database to noarchivelog mode, perform the data loads and then shutdown and reset to archivelog mode. I'm aware of the recovery implications.
We're using 8i on Solaris 2.6...and yes I'm fairly new to Oracle ;-)
TIA
Grant
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: grant.g.holyoake_at_centrelink.gov.au Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Tue Sep 04 2001 - 11:03:33 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: rafiq9857_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |