How to find average row length for a table?

Using the following PL/SQL code one can find average size of a row in a table, the following code samples the first 100 rows. It expects 2 parameters table owner and table_name.

DECLARE
l_vc2_table_owner VARCHAR2(30) := '&table_owner';
l_vc2_table_name VARCHAR2(30) := '&table_name';
/* sample number of rows */
l_nu_sample_rows NUMBER := 100;

/* loop through columns in the table */
CURSOR l_cur_columns IS
SELECT column_name, data_type FROM dba_tab_columns
WHERE owner = l_vc2_table_owner
AND table_name = l_vc2_table_name;
l_rec_columns l_cur_columns%ROWTYPE;
l_vc2_sql VARCHAR2(10000);
l_avg_row_size NUMBER(10,2);
BEGIN
l_vc2_sql := '';
OPEN l_cur_columns;
FETCH l_cur_columns INTO l_rec_columns;
/* loop through columns */
WHILE l_cur_columns%FOUND
LOOP
/* if LOB datatype use dbms_log.get_length to find length */
IF l_rec_columns.data_type = 'CLOB' OR l_rec_columns.data_type = 'BLOB' THEN
l_vc2_sql := l_vc2_sql || 'NVL(dbms_lob.getlength(' || l_rec_columns.column_name || '), 0) + 1';
ELSE
l_vc2_sql := l_vc2_sql || 'NVL(VSIZE(' || l_rec_columns.column_name || '), 0) + 1';
END IF;
FETCH l_cur_columns INTO l_rec_columns;
IF l_cur_columns%FOUND THEN
l_vc2_sql := l_vc2_sql || ' + ';
END IF;
END LOOP;
IF l_vc2_sql IS NOT NULL THEN
l_vc2_sql := 'SELECT 3 + AVG(' || l_vc2_sql || ') FROM ' || l_vc2_table_owner || '.' || l_vc2_table_name
|| ' WHERE rownum < ' || l_nu_sample_rows;
EXECUTE IMMEDIATE l_vc2_sql INTO l_avg_row_size;
dbms_output.put_line(l_vc2_table_owner || '.' || l_vc2_table_name || ' average row length: ' || l_avg_row_size);
ELSE
dbms_output.put_line('Table ' || l_vc2_table_owner || '.' || l_vc2_table_name || ' not found');
END IF;
END;
/
Output
Enter value for table_name: TEST_OBJECTS
old 3: l_vc2_table_name VARCHAR2(30) := ‘&table_name’;
new 3: l_vc2_table_name VARCHAR2(30) := ‘TEST_OBJECTS’;
SCOTT.TEST_OBJECTS average row length: 76.88

PL/SQL procedure successfully completed.

Sample program that transfers data from MySQL to Oracle (CLOB)

Table structure in Oracle
SQL> desc scott.oracletable;
Name Null? Type
—————————————– ——– ———-
TRANSDATE NOT NULL DATE
SEQUENCE_NO NOT NULL NUMBER(6)
LOBDATA NOT NULL CLOB

Table structure in MySQL:
Name Type
—————————————– ——–
trandate Date
sequence_no smallint(6)
textfield longtext

– Code: TransferMySQLToOracle.java
import java.sql.*;
import java.io.*;
import java.lang.StringBuffer;
import java.util.Date;
import java.text.SimpleDateFormat;

public class TransferMySQLToOracle {
public static void main(String argv[]) throws Exception {
// DB connection for MySQL
Connection mysqlConn = null;
Statement mysqlStmt = null;
ResultSet mysqlRS = null;

// DB connection for Oracle
Connection oraConn = null;
PreparedStatement oraPstmt = null;

// parameters passed in start date and end date
String dtStart=argv[0];
String dtEnd=argv[1];

// print date
System.out.println(“: \n dtStart=” + dtStart);
System.out.println(“: \n dtEnd=” + dtEnd);

// set connection string for Oracle
String oradriverName = “oracle.jdbc.driver.OracleDriver”;
String oraurl = “jdbc:oracle:thin:@hostname:OraclePort:ORACLE_SID”;

// format to print timestamp
Date todaysDate;
SimpleDateFormat formatter = new SimpleDateFormat(“dd-MMM-yyyy HH:mm:ss”);
String formattedDate;

// default values
java.sql.Date dtTransactionDate = null;
int iSequenceNo = -1;

// print today date
todaysDate = new java.util.Date();
formattedDate = formatter.format(todaysDate);
System.out.println(“Start Time: ” + formattedDate);

try {
// connect to MySQL
String mysqlurl = “jdbc:mysql://mysqlserver:mysqlport/database?user=username&password=userpassword”;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
mysqlConn = DriverManager.getConnection(mysqlurl);

// connect to Oracle
Class.forName(oradriverName);
oraConn = DriverManager.getConnection(oraurl, “scott”, “scottpassword”);
String oraInsert = “insert into scott.oracletable (trandate, sequence_no, lobdata) values(?, ?, ?)”;

if ( mysqlConn != null )
// Get a statement from the connection and prepare statement
// mysqlStmt = mysqlConn.createStatement();
mysqlStmt = mysqlConn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
mysqlStmt.setFetchSize(Integer.MIN_VALUE);
oraPstmt = oraConn.prepareStatement(oraInsert);

// date in yyyy-mm-dd format in mysql
// extract records from MySQL for the date specified
String msqlQry = “SELECT trandate, sequence_no, textfield FROM mysqltable” ;
msqlQry += ” Where trandate between ‘” + dtStart + “‘ and ‘” + dtEnd + “‘”;
mysqlRS = mysqlStmt.executeQuery(msqlQry) ;
todaysDate = new java.util.Date();

// print date for logging start time
formattedDate = formatter.format(todaysDate);
System.out.println(“Starting loop: ” + formattedDate);

// Loop through the result set
while( mysqlRS.next() ) {
// get and set trandate and sequence number
dtTransactionDate = mysqlRS.getDate(1);
iSequenceNo = mysqlRS.getInt(2);
oraPstmt.setDate(1, dtTransactionDate);
oraPstmt.setInt(2, iSequenceNo);

// loop through the lob stream and convert to string
Reader in = mysqlRS.getCharacterStream(3);
StringBuffer sb = new StringBuffer();
int buf = -1;
while((buf = in.read()) > -1) {
sb.append((char)buf);
}
in.close();

// ObjectInput in = new ObjectInputStream(mysqlRS.getCharacterStream(5));
oraPstmt.setString(3, sb.toString());

// insert record in oracle
oraPstmt.executeUpdate();
}
oraPstmt.close();
mysqlRS.close();
mysqlStmt.close();

// print end time
todaysDate = new java.util.Date();
formattedDate = formatter.format(todaysDate);
System.out.println(“End Time: ” + formattedDate);
}
catch(Exception e)
{
// print the record it failed on as date and sequence # was unique so one know the offending record in source
System.out.println(“Failed on Record:”);
System.out.println(“Transaction Date: ” + dtTransactionDate.toString());
System.out.println(“Sequence No: ” + iSequenceNo);

throw e;
}
finally {
if ( mysqlRS != null ) mysqlRS.close();
mysqlRS = null;
if ( mysqlStmt != null ) mysqlStmt.close();
mysqlStmt = null;
if ( mysqlConn != null ) mysqlConn.close();
mysqlConn = null;
if ( oraConn != null ) oraConn.close();
oraConn = null;
}
}
}

c:\> SET CLASSPATH=.;C:\driver\mysql-connector-java-5.0.8-bin.jar;C:\driver\ojdbc14.jar
c:> javac TransferMySQLToOracle.java

How to skip blank lines in between SQL statements in SQL*Plus?

If a SQL script file has blank lines in between SQL statements one may see errors when executing the script in SQL*Plus so if one would like to ignore the blanklines in SQL*Plus one can ignore them by set blanklines on.

Example:
SQL> select object_name
2
SQL> from dba_objects;
SP2-0734: unknown command beginning “from dba_o…” – rest of line ignored.
SQL> set sqlblanklines on
SQL> select object_name
2
3 from dba_objects
4 where rownum < 3;

OBJECT_NAME
——————————————————————————–
ICOL$
I_USER1
– Disable blank lines in between SQL statements
SQL> set sqlblanklines off

How to turn off case sensitive in 11g

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
———————————— ———– ——————————
sec_case_sensitive_logon boolean TRUE
SQL>
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

How to find users with default password in 11g?

Now Oracle has a quick way to find users with detault password.

SQL> SELECT * FROM dba_users_with_defpwd;

And here is the output:

USERNAME
——————————
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS

Sample JDBC program which connects to Oracle

Sample program that demonstrates using JDBC to connect to Oracle database.

Source code
$ cat TestConnection.java
import java.sql.*;

public class TestConnection {
public static void main (String args []) throws SQLException, InterruptedException
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
java.util.Properties props = new java.util.Properties();

// user/password@host:port:SID
Connection conn = DriverManager.getConnection
(“jdbc:oracle:thin:” + args[0] + “/” + args[1] + “@” + args[2] + “:” + args[3] + “:” + args[4], props);

Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(“select to_char(sysdate, ‘DD-MON-YYYY HH24:MI:SS’) from dual”);
while (rset.next()) {
String s = rset.getString(1);
System.out.println(“Date: ” + s);
}
stmt.close();
}
}

To compile and run
$ set CLASSPATH=.:/home/oracle/jar/ojdbc14.jar
$ javac TestConnection.java
$ java TestConnection username passowrd hostname port sid
Date: 14-OCT-2009 19:27:51

How to list the access the roles the different users have?

– print usernames and the roles assigned to the user, it can be run against any database and it will print the users and the roles assigned to different users
SELECT sl.name, g.name
FROM sysusers u, sysusers g, sysmembers m, master.dbo.syslogins sl
where g.name IN (SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1))
and g.uid = m.groupuid
and sl.sid = u.sid
and g.issqlrole = 1
and u.uid = m.memberuid
and sl.denylogin = 0
order by sl.name

Sample output:
localuser db_owner
localuser db_datareader
domain\ADuser db_datareader
domain\ADuser db_denydatawriter
….

How to find a length of LOB column?

Using dbms_lob.getlength(column name) one can find the length of the LOB (CLOB/BLOB/BFILE) data type.

Example:
SQL> SELECT dbms_lob.getlength(lob_column) FROM scott.lob_table;

DBMS_LOB.GETLENGTH(lob_column)
——————————–
358
153
…

How to find if a table has row chaining?

Using ANALYZE TABLE owner.table_name COMPUTE STATISTICS one can find if the table has chained rows.

Example:

– analyze again
SQL> analyze table scott.chain_table COMPUTE STATISTICS;

Table analyzed.

– shows row chaining occurred when existing rows were updated and percent of rows
SQL> SELECT chain_cnt,
round(chain_cnt/num_rows*100,2) pct_chained
FROM user_tables
WHERE table_name = 'CHAINED_ROW';

CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED
———- ———– ———– ———- ———-
76824 58.61 16 10 40

How to find the IO stats of filesystem?

Using v$filestat one can find the physical reads and writes to datafiles it also includes reads done by RMAN. So using this SQL one can find physical read and write on a filesystem. Note: The data reported is since the database started.

SQL> column filesystem format a40
SQL> SELECT substr(vdf.name, 1, instr(vdf.name, '/', -1)) filesystem, sum(vfs.phyrds) totalreads, sum(vfs.phywrts) totalwrts
FROM v$filestat vfs, v$datafile vdf
WHERE vfs.file# = vdf.file#
GROUP BY substr(vdf.name, 1, instr(vdf.name, '/', -1));

FILESYSTEM TOTALREADS TOTALWRTS
—————————————- ————— —————-
/u01/oradata/TEST/data01/ 33397136 1315151
/u02/oradata/TEST/idx01/ 71951 35720
….

Here is the description of the view V$FILESTAT and V$DATAFILE