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

Posted on By Admin No Comments on How to find who is using which Rollback segment and how many rows or blocks in that rollback segments,

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

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

Oracle, SQL scripts

Generate SSH without password authentication.

Posted on By Admin No Comments on Generate SSH without password authentication.

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

Read More “Generate SSH without password authentication.” »

Linux/Unix, shell

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

Posted on By Admin No Comments on How to find Unix server conguraiton including Server Model number and underlying disk sub-system ( On Solaris)

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

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

Linux/Unix, shell

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

Posted on By Admin No Comments on How to Decide upto what level you can decrement your datafile size. ( Shrink Datafile)

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)” »

Oracle, SQL scripts

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

Posted on By Admin No Comments on How to find the real execution plan and binds used in that explain plan in Oracle 10g??

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

Read More “How to find the real execution plan and binds used in that explain plan in Oracle 10g??” »

Oracle, SQL scripts

For Perl DBI installation and testing program

Posted on By Admin No Comments on For Perl DBI installation and testing program

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…

Read More “For Perl DBI installation and testing program” »

PHP/MYSQL/Wordpress

Oracle Identifiers

Posted on By Admin No Comments on Oracle Identifiers

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

Read More “Oracle Identifiers” »

Oracle, SQL scripts