|
||||||
|
Use the below query to find out the current usage of temporary tablespace(sort usage) by active users. SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size /1024/1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM gv$sort_usage T, gv$session S, dba_tablespaces TBS, gv$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr [...]  Oracle 10046 sesssion tracing There are different ways to enable tracing on an Oracle database user session. They are: Using DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procudure. Using oradebug ipc setorapid/setospiod Using a Logon trigger The first 2 methods are used used when the sessions are already connected to the database. For the first method, use the session ID, Serial# [...] Oracle provides a procedure to insert messages to the alert log and/or trace files for testing/development purposes. This can be used to check the effectiveness of the monitoring tools/scripts used in the environment, to understand how well the monitoring tool captures the messages in the alert log. Usage: SQL> exec dbms_system.ksdwrt(1, ‘This message goes to [...] The below query helps to identify the session that has locked the resource that your session is trying to lock. You can try executing the query/job after some time (to allow the other session to complete) or kill the session that has locked the resource to proceed with this situation, use the below query [...] In scenarios where there is no disk space to hold the export dumps or when migrating schemas across different versions of Oracle, this script helps to do the export/import over the network or on the same server using the Unix pipe. In this example, data is exported from the Oracle Database instance named PROD to [...] One of the most used script used by the DBA in day-to-day work, this script gives the space usage of each tablespace in the database. set heading on set pagesize 500 set lines 400 column tablespace format a30 heading "Tablespace" column avail format 9,999,999,999,999 heading "MB Avail." column used format 9,999,999,999,999 heading "MB Used" column [...] The below query comes handy to understand the archivelog generation of an Oracle database on an hourly /daily basis, per thread – in case of RAC databases. Archivelog generation on a daily basis: set pages 1000 select trunc(COMPLETION_TIME,’DD’) Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,’DD’),thread# order by 1; Archive log generation on [...] |
||||||
|
Copyright © 2012 DBAPundits.com - All Rights Reserved |
||||||
