Author: Admin

How to find who is using which Rollback segment and how many rows or blocks in that rollback segments,

16-Jun-2006 Admin No Comments

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,…

Oracle, SQL scripts

Generate SSH without password authentication.

14-Jun-2006 Admin No Comments

*******************************************************************************************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:…

Linux/Unix, shell

How to find Unix server conguraiton including Server Model number and underlying disk sub-system ( On Solaris)

12-Jun-2006 Admin No Comments

$ /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…

Linux/Unix, shell

How to Decide upto what level you can decrement your datafile size. ( Shrink Datafile)

12-Jun-2006 Admin No Comments

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:…

Oracle, SQL scripts

How to find the real execution plan and binds used in that explain plan in Oracle 10g??

09-Jun-2006 Admin No Comments

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′;…

Oracle, SQL scripts

How to find pinned objects from shared pool. (pinned via dbms_shared_pool.keep)

08-Jun-2006 Admin No Comments

SELECT owner, name, type FROM v$db_object_cache WHERE kept = ‘YES’ AND TYPE = ‘CURSOR’;

Oracle, SQL scripts

For Perl DBI installation and testing program

08-Jun-2006 Admin No Comments

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…

PHP/MYSQL/Wordpress

catting.sh

07-Jun-200602-Jun-2023 Admin No Comments

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 }

Linux/Unix, shell

how to find OS block size

01-Jun-2006 Admin No Comments

13:01:01 SYS@AWRTEST:-> select lebsz from x$kccle; LEBSZ ——— 512 512 512 3 rows selected. or df -g on unix prompt.

Oracle, SQL scripts

Oracle Identifiers

31-May-2006 Admin No Comments

Oracle Identifiers: =================== In order not to make using Oracle confusing, there are some identifiers whose meaning must be totally clear if one want to work on Oracle: SID (System Identifier): ======================== A SID (almost) uniquely identifies an instance. Actually, $ORACLE_HOME, $ORACLE_SID and $HOSTNAME identify an instance uniquely. The SID is 64 characters, or less;…