oracle snippets

How to generate an Oracle statspack report and an explain plan for a query

Tagged oracle, explain plan, statspack  Languages sql
-- Connect

sqlplus <username>/<password>@<database>

-- Generate statspack report

@?/rdbms/admin/spreport.sql

-- Find execution plan for a specific query
@?/rdbms/admin/sprepsql.sql

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2


Specify the Hash Value
~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 2967942512
Hash Value specified is: 2967942512

Find the amount of CPU time and total time runtime for Oracle SQL queries

Tagged oracle, performance  Languages sql

There's no equivalent to the MySQL slow query log in Oracle but these queries and the statspack reports are helpful when trying to find slow queries:

-- Order queries by how many cpu seconds they consume
SELECT   hash_value, executions, ROUND (elapsed_time / 1000000, 2) total_time,
         ROUND (cpu_time / 1000000, 2) cpu_seconds
    FROM (SELECT   *
              FROM v$sql
          ORDER BY elapsed_time DESC)
ORDER BY cpu_seconds DESC;

-- Find the total cpu seconds consumed
SELECT SUM (cpu_seconds)
  FROM (SELECT   hash_value, executions,
                 ROUND (elapsed_time / 1000000, 2) total_time,
                 ROUND (cpu_time / 1000000, 2) cpu_seconds
            FROM (SELECT   *
                      FROM v$sql
                  ORDER BY elapsed_time DESC)
        ORDER BY cpu_seconds DESC);


-- Find the total time queries have taken
SELECT SUM (total_time)
  FROM (SELECT   hash_value, executions,
                 ROUND (elapsed_time / 1000000, 2) total_time,
                 ROUND (cpu_time / 1000000, 2) cpu_seconds
            FROM (SELECT   *
                      FROM v$sql
                  ORDER BY elapsed_time DESC)
        ORDER BY cpu_seconds DESC);

-- Find execution plan for the damned query you should fix
SELECT *
  FROM v$sql_plan
 WHERE hash_value = 2967942512;

How to write UTF-8 data to an Oracle BLOB column with Java and JDBC

Tagged oracle, blob, jdbc, java, utf-8  Languages java

This example works with Oracle:

private Blob getBlob(Connection connection, String data)
{
  BLOB blob = BLOB.createTemporary(connection, true, BLOB.DURATION_SESSION);

  try
  {
      blob.open(BLOB.MODE_READWRITE);
      blob.putBytes(1, data.getBytes("UTF-8")); // Consider streaming, if data size is unknown. Note that setBytes doesn't work
  }
  catch(UnsupportedEncodingException ex)
  {
      throw new RuntimeException("Unable to get a blob for '" + data + "'", ex);
  }
  catch(SQLException ex)
  {
      throw new RuntimeException("Unable to get a blob for '" + data + "'", ex);
  }
  finally
  {
      try { if(blob != null) blob.close(); } catch(Exception ex) {};
  }
}

Then use the method like this:

Connection connection = getConnection();
PreparedStatement statement = getPreparedStatement(yer sequel);

statement.setBlob(1, getBlob(connection,  <Mao's Little Red Book>));

How to read UTF-8 data from an Oracle BLOB column with Java and JDBC

Tagged oracle, blob, utf-8, java  Languages java

This example works with Oracle:

private String getBlobAsString(Blob blob)
{
    StringBuffer result = new StringBuffer();
    
    if ( blob != null ) 
    {
        int read = 0;
        Reader reader = null;
        char[] buffer = new char[1024];
                                
        try
        {
            reader = new InputStreamReader(blob.getBinaryStream(), "UTF-8");

            while((read = reader.read(buffer)) != -1) 
            {
                result.append(buffer, 0, read);
            }
        }
        catch(SQLException ex)
        {
            throw new RuntimeException("Unable to read blob data.", ex);
        }
        catch(IOException ex)
        {
            throw new RuntimeException("Unable to read blob data.", ex);
        }
        finally
        {
            try { if(reader != null) reader.close(); } catch(Exception ex) {};
        }
    }
    
    return result.toString();
}

Then use the method like this:

ResultSet resultSet = your JDBC result set;

String utf8 = getBlobAsString(resultSet.getBlob("xml"));

Example of how to do a for loop with PL/SQL

Tagged sql, pl/sql, oracle, for loop  Languages sql

A PL/SQL example of a for loop that prints out the primary key for all rows returned by a query:

DECLARE
  ID VARCHAR2(50);
BEGIN
    DBMS_OUTPUT.ENABLE (1000000);
    FOR current_row IN (SELECT id FROM your_table)
    LOOP
        ID := current_row.id;    -- Assign value to variable
        DBMS_OUTPUT.PUT_LINE( '' || ID);
        DBMS_OUTPUT.PUT_LINE( '' || current_row.column_xxx);
    END LOOP;
END;
/

Simplify and make SQL scripts run faster by using Oracle variables

Tagged sql, variables, oracle  Languages sql

Oracle variables are a great way of removing duplicate SQL statements from your Oracle SQL scripts. By using a variable, you can store the return value of a query and use it later in another, as this example illustrates:

COLUMN your_column new_value your_variable;

SELECT DISTINCT (your_column)
                                 FROM ...
                                WHERE ...;

SELECT &your_variable FROM dual;

The above example stores the value of the SELECT DISTINCT query in a variable named your_variable. You declare the variable with new_value, and set the value by executing an SQL statement that returns one row and column.

How to export and import Oracle schemas, or backup Oracle databases

Tagged import, export, oracle, dump, backup  Languages bash

You can use the exp and imp executables to perform data backup and restoration.

-- Export
exp <username>/<password>@<server> file=data.dmp consistent=y grants=no statistics=none

-- Import
imp <username>/<password>@<server> file=data.dmp fromuser=<source_username> touser=<username>

How to disable Oracle's variable name scanning

Tagged sql, oracle, disable  Languages sql

Normally Oracle will scan SQL for variables and prompt for the value at runtime, to disable this feature use the "set scan off" command:

set scan off;

select * from links where href = 'http://...&....';