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,…
*******************************************************************************************Step by Step Implementation of key based authentications from Source A to Destination B. ******************************************************************************************* (1) At source A (1-A) Generate a Key ssh-keygen -t dsa Sampe Output posdbprod21->CIF@(/export/home/oracle/paresh)ssh-keygen -t dsa Generating public/private dsa key pair. Enter file in which to save the key (/export/home/oracle/.ssh/id_dsa): Enter passphrase (empty for no passphrase): Enter same passphrase again:…
$ /usr/platform/sun4u/sbin/prtdiag System Configuration: Sun Microsystems sun4u 8-slot Sun Enterprise E4500/E5500 System clock frequency: 100 MHz Memory size: 10240Mb ========================= CPUs ========================= Run Ecache CPU CPU Brd CPU Module MHz MB Impl. Mask — — ——- —– —— —— —- 0 0 0 400 8.0 US-II 1.0 0 1 1 400 8.0 US-II 1.0 2…
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’;
http://www.cedet.dk/perl/ #!c:perlbinperl use DBI; # Following line is necessary of you are using perl to write html in browser. print “Content-type: text/htmlnn”; print ““; print “CGI Test“; print ““; print “Newer Verion of File Management Operations !“; my $dbname = “pvmeh”; ## DB Name from tnsnames.ora my $user = “pvmeh”; my $passwd = “pvmeh”; print…
This is the script used for merging multiple text files in single large text(ASCII) file. for i in `ls *sql *sh` { echo “****************************************************” >> myfile echo File Name : $i >> myfile echo “****************************************************” >> myfile cat $i >> myfile echo ” ” >> myfile }
