set lines 132 set pages 1400 SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM v$sql_plan START WITH id = 0 and sql_id=’4ftvbpzhwkfd8′ and child_number=0 CONNECT BY PRIOR id = parent_id and sql_id=’4ftvbpzhwkfd8′ and child_number=0;
Good Doc About UNDO Management: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6894817116500 Cache Buffer Chain Latch: http://www.orafaq.com/maillist/oracle-l/2003/02/27/2671.raw
/etc/init.d/init.cssd stop to stop cssd process.
column osuser format a10 column username format a30 column machine format a30 column program format a50 set lines 132 set pages 500 select osuser, username, machine, program from v$session; select machine, username, count(1) from v$session group by machine, username;
REM ***** gsp.sql ***** REM ***** This is used to get SPID from SID. col username format a30 col machine format a20 col program format a40 accept _sid prompt ‘Enter Oracle Session ID ->’ select a.sid, b.pid, b.spid, a.username,a.program,a.machine from v$session a,V$process b where a.paddr = b.addr and a.sid = &_sid / REM ***** gsq.sql…
Using following query, we can estimate # of rows resides in each USN (Rollback Segment) and who fired that transactions. New modified GCU.sql on 16-JUN-2006 /* Get Curent USN */ column start_dt format a20 set lines 132 select /*+ ALL_ROWS */ a.usn, a1.name, a.extents, a.hwmsize, a.status usn_status, to_char(b.start_date, ‘DD-MON-RRRR:HH24:MI:SS’) start_dt, b.status tx_status, c.sid, c.sql_id, b.used_ublk,…
We can directly resize datafiles TEST.SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=’SYSTEM’; FILE_NAME BYTES —————- ———- /…/dbsGNX.dbf 419430400 TEST.SQL>ALTER DATABASE DATAFILE ‘/…/dbsGNX.dbf’ RESIZE 390M; Database altered. TEST.SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=’SYSTEM’; FILE_NAME BYTES ————— ———- /…/dbsGNX.dbf 408944640 But the minimum file size is the size of the extend the furthest in the datafile:…
Read More “How to Decide upto what level you can decrement your datafile size. ( Shrink Datafile)” »
Step-1 : Find the Query and its SQL_ID to trace from AWR reports. Step-2 : Execute following query to find real execution plan: SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM v$sql_plan START WITH id = 0 and sql_id=’8ska10yfjx4b5′ CONNECT BY PRIOR id = parent_id and sql_id=’8ska10yfjx4b5′;…
SELECT owner, name, type FROM v$db_object_cache WHERE kept = ‘YES’ AND TYPE = ‘CURSOR’;
13:01:01 SYS@AWRTEST:-> select lebsz from x$kccle; LEBSZ ——— 512 512 512 3 rows selected. or df -g on unix prompt.
