Monthly Archives: August 2016

Unknown's avatar Frank 5:05 am on August 30, 2016  

oracle locked objects

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.inst_id,
   b.serial#,
   b.status,
   b.username,
   b.osuser,
   b.machine,
   case a.locked_mode
      when 0 then '0 - NONE: lock requested but not yet obtained'
      when 1 then '1 - NULL'
      when 2 then '2 - ROWS_S (SS): Row Share Lock'
      when 3 then '3 - ROW_X (SX): Row Exclusive Table Lock'
      when 4 then '4 - SHARE  : Share Table Lock'
      when 5 then '5 - S/ROW-X (SSX): Share Row Exclusive Table Lock'
      when 6 then '6 - Exclusive  : Exclusive Table Lock'
      else to_char(a.locked_mode)
  end locked_mode
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
;
Unknown's avatar Frank 4:52 am on August 30, 2016  

oracle sid and spid

Find session details


select s.INST_ID, s.SID, s.USERNAME, s.OSUSER,
       s.MACHINE, p.SPID, s.PROGRAM
from gv$session s
join gv$process p on s.PADDR = p.ADDR
and s.INST_ID = p.INST_ID
where sid =
;

select s.SID, s.USERNAME, s.OSUSER,
       s.MACHINE, p.SPID, s.PROGRAM
from v$session s
join v$process p on s.PADDR = p.ADDR
where sid =
;
Unknown's avatar Frank 4:59 am on August 24, 2016
Tags: jdbc   

example jdbc program

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
class TableCount
{
public static void main(String[] args)
{
TableCount t = new TableCount();
t.run(args);
}
public void run(String[] args)
{
try
{
Connection conn = getConnection(args[0], args[1], args[2]);
getTableCount(conn, args[3]);
conn.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
public static Connection getConnection(Properties p) throws Exception
{
Connection conn = null;
String username = p.getProperty("USERNAME");
String password = p.getProperty("PASSWORD");
String url = p.getProperty("URL");
String driver = p.getProperty("DRIVER");
System.out.println(username);
System.out.println(url);
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
conn.setAutoCommit(false);
return conn;
}
public static Connection getConnection(String file, String username, String password) throws Exception
{
Properties props = loadProperties(file);
props.setProperty("USERNAME", username);
props.setProperty("PASSWORD", password);
return getConnection(props);
}
private static Properties loadProperties(String file) throws FileNotFoundException, IOException
{
Properties props = new Properties();
props.load(new FileInputStream(new File(file)));
return props;
}
public void getTableCount(Connection conn, String owner) throws SQLException
{
String sql = null;
ResultSet rs = null;
PreparedStatement stmt = null;
sql = "SELECT count(*) c from all_tables where owner = ? ";
try
{
stmt = conn.prepareStatement(sql);
stmt.setString(1, owner);
rs = stmt.executeQuery();
while (rs.next())
{
long count = rs.getLong("c");
System.out.println("~~");
System.out.println(count);
}
}
catch(SQLException exception)
{
exception.printStackTrace();
}
finally
{
}
}
}
view raw TableCount.java hosted with ❤ by GitHub
Unknown's avatar Frank 2:55 am on August 24, 2016  

perl by example

Source code,

sub build_script
{
my $cols = shift;
foreach my $col (@$cols)
{
print($col . ",\n");
}
}
sub parse_source_file
{
my $filename = shift;
my $table_name = shift;
my $file;
my $line;
my $cols = ();
if (!open($file, "<", $filename))
{
print "file not found\n";
return;
}
while ($line = <$file>)
{
$line =~ s/\W+$//;
my @fields = split(',', $line);
if ($fields[0] eq $table_name)
{
push(@cols, $fields[1]);
}
}
close($file);
return \@cols;
}
sub main
{
my $db = uc($ARGV[0]);
my $table = uc($ARGV[1]);
my $source = $ARGV[2];
my $cols = parse_source_file($source, $table);
build_script($cols);
}
main
view raw parser.pl hosted with ❤ by GitHub