and to turn it off use:
oradebug setorapid 165 oradebug event 10046 trace name context forever, level 12;
For whatever tracing is appropriate.
oradebug setorapid 165 oradebug event 10046 trace name context off;
964470 wrote:doing so at the system level traces EVERY session in the DB.
Thank you sb.
This is a test DB (that will eventaully go into production). Is there anything wrong with setting the SQL_TRACE parameter using alter system instead of session?
Do you know where the log file would be placed on the system?/adump DIRECTORY
I am trying to use these commands for SID 100, and receive the following:
SQL> oradebug setorapid 100
ORA-00072: process "Unix process pid: 0, image: <none>" is not active
Edited by: 964470 on Jan 17, 2013 11:48 AM
DBMS_MONITOR.SESSION_TRACE_ENABLE( session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE, plan_stat IN VARCHAR2 DEFAULT NULL);
964470 wrote:The recovery file dest is a setting for when you want Oracle to handle some functions for you. If it is being filled up with archived logs, you should use rman to backup so the unneeded logs get deleted. This depends on various settings. Since you are running noarchivelog mode, that is probably not what is happening, so I wonder what else is going on there, maybe you were once archivelog and then changed without cleaning up. You might want to run rman crosscheck and delete obsolete commands in case there are old things there, so Oracle can match what it thinks with reality. Look at the directories nearby (like udump and cdump), perhaps you may be getting some other trace files that need to be cleaned up. Also check for adcri filling up stuff. http://gavinsoorma.com/2010/09/purging-trace-and-dump-files-with-11g-adrci/
Again, thanks a lot guys. I see in my alert.log:
Thread 1 cannot allocate new log, sequence 33254
Checkpoint not complete
After I bounced the instance, I see:
ORA-19815: WARNING: db_recovery_file_dest_size of 4194304000 bytes is 99.43% used, and has 23871488 remaining bytes available.
I am not using RMAN or any other backup utility.
I create an empty table (table B) using the NOLOGGING optionYou could convert that to a Direct Path INSERT with an APPEND Hint in the INSERT statement.
truncate table B; insert into table B select
my query still shows in the v$session_longops tableIf SOFAR = TOTALWORK, there is no longop in progress at that time.
even though it is not doing anything (that I can see).Tracing the session would show what it is doing.
Now it runs the normal 90 minutes and sits idle another 90 minutes before finishing.How do you verify that it is idle ? (only on the basis of querying v$session_longops ?). When is the COMMIT issued ?