DDL to disable triggers in Oracle and SQLServer

To disable to trigger that are enabled in cases when loading data manually in a particular schema or table can be done using the following
Oracle:
SELECT 'ALTER TRIGGER ' || trigger_name || ' DISABLE;'
FROM user_triggers
WHERE status = 'ENABLED'

SQL Server:
SELECT 'ALTER TABLE ' + pobj.name + ' DISABLE TRIGGER ' + str.name + ';'
FROM sysobjects str, sysobjects pobj
where str.type = 'TR'
AND pobj.id = str.parent_obj
AND OBJECTPROPERTY(str.[id], 'ExecIsTriggerDisabled') = 0

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

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

sysaux – v$sysaux_occupants

– view to monitor space usage by different occupants and procedure to move the feature to a different tablespace.
SQL> COLUMN move_procedure FORMAT a35
SQL> COLUMN occupant_name FORMAT a30

SQL> SELECT occupant_name, move_procedure, space_usage_kbytes FROM v$sysaux_occupants;

OCCUPANT_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES
—————————— ———————————– ——————
LOGMNR SYS.DBMS_LOGMNR_D.SET_TABLESPACE 6080
LOGSTDBY SYS.DBMS_LOGSTDBY.SET_TABLESPACE 896
STREAMS 512
…

DBA_HIST_DATABASE_INSTANCE

In 10g there is a view called DBA_HIST_DATABASE_INSTANCE which has database instance information. It has history on information such as instance startup time, last ASH sample id.

Example:
SQL> column startup_time format a35
SQL> alter session set nls_timestamp_format=’DD-MON-YYYY HH24:MI:SS’;

Session altered.

SQL> SELECT startup_time, last_ash_sample_id FROM dba_hist_database_instance WHERE dbid = 1992878807 order by startup_time;

STARTUP_TIME LAST_ASH_SAMPLE_ID
—————————— ——————
12-AUG-2009 21:12:19 447735
21-AUG-2009 17:48:29 494755
..
27-SEP-2009 15:24:04 3036072
29-SEP-2009 21:44:22 0

8 rows selected.

Oracle Created (Default) Database Users

List of oracle default users and their password. After creating the database one may want to change the password for these users or lock the account if the feature is not being used.

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_26.shtml