How to extend an account whose password is expiring?

One of the ways to extend the password of an expiring account is to change the profile of the user.

– show the current user profile which shows the password expires 180 days
SQL> select * FROM dbA_profiles where profile = ‘APPLICATIONS’;

PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
..
APPLICATIONS FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
APPLICATIONS PASSWORD_LIFE_TIME PASSWORD 180
..

– show the user profile and expiry date and encrypted password
13:08:03 sys> select expiry_date, password, profile from dba_users where username = ‘SCOTT’;

EXPIRY_DA PASSWORD PROFILE
——— —————————— ——————————
21-DEC-09 8C465A58AE456660 APPLICATIONS

1 row selected.

– show the profile for DEFAULT which is set to not expire
SQL> select * FROM dbA_profiles where profile = ‘DEFAULT’;
PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
..
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

– change the profile
13:08:17 sys@> alter user SCOTT profile default;

User altered.

– change the password
13:08:28 sys@> alter user SCOTT identified by values ‘8C465A58AE456660′;

User altered.

– change the profile for the user back to expire password
13:08:42 sys@> alter user SCOTT profile APPLICATIONS;

User altered.

– check the password expiry date has changed
13:08:51 sys@> select expiry_date, password, profile from dba_users where username = ‘SCOTT’;

EXPIRY_DA PASSWORD PROFILE
——— —————————— ——————————
20-JUN-10 8C465A58AE456660 APPLICATIONS

– shows the password history is kept, note the password changed wasn’t captured in the password history
sys> SELECT name, password_date
FROM sys.user$, sys.user_history$
WHERE user$.user# = user_history$.user#
and name = ‘SCOTT’;

NAME PASSWORD_
—————————— ———
SCOTT 01-MAY-06
SCOTT 26-APR-07
SCOTT 17-APR-08

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

snapshot controlfile and RMAN-08512

As RMAN needs a consistent view of the control file it takes a backup of the controlfile by creating a snapshot and during the backup RMAN uses the snapshot of the controlfile. By default the snapshot controlfile is created in $ORACLE_HOME/dbs/snapcf_ORACLE_SID.f which can be changed by setting the following parameter in RMAN

$ rman target /
– display parameter value
RMAN> show all;
..
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/10.2.0.4/TESTDB/snapcf_TESTDB.f'; # default
– set to new path for controlfile snapshot
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/TESTDB/snapcf_TESTDB.f';

When multiple RMAN are running one could run into an issue RMAN-08512 where RMAN is waiting for getting a lock on snapshot controlfile header. To find the session run the following SQL.

SELECT vs.sid, vs.username, vs.program, vs.module, TO_CHAR(vs.logon_time, 'DD-MON-YYYY HH24:MI:SS')
FROM v$session vs, v$enqueue_lock vel
WHERE vs.sid = vel.sid AND vel.type = 'CF' AND vel.id1 = 0 AND vel.id2 = 2

How to find length of a TEXT column?

The “LEN” function cannot be used to find length of a column of datatype “TEXT” so using function “DATALENGTH” one can find the length of TEXT field.

Eg: SELECT DATALENGTH(textfiled) lentextfile FROM table;

Script to create grants to roles to database for SQL authenticated users.

Code that be used to scripts to extract role permissions for a database for SQL authenticated accounts, it is useful when rebuilding a server and after databases are reattached the SQL authenticated users access has to be granted.

DECLARE db_cursor CURSOR FOR
SELECT 'exec sp_addrolemember ''' + g.name + ''', ''' + sl.name + '''', sl.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 sl.isntuser = 0
AND sl.name != 'sa'
AND u.uid = m.memberuid
ORDER BY sl.name

DECLARE @l_sql_stmt VARCHAR(200)
DECLARE @l_user VARCHAR(200), @l_prev_user VARCHAR(200)

SET @l_prev_user = ''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @l_sql_stmt, @l_user
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l_prev_user != @l_user
BEGIN
PRINT 'IF EXISTS (SELECT 1 FROM sysusers WHERE [name] = ''' + @l_user + ''')'
PRINT ' EXEC sp_revokedbaccess ''' + @l_user + ''''
PRINT 'EXEC sp_grantdbaccess ''' + @l_user + ''''
SET @l_prev_user = @l_user
END
PRINT @l_sql_stmt
FETCH NEXT FROM db_cursor INTO @l_sql_stmt, @l_user
END
CLOSE db_cursor
DEALLOCATE db_cursor

How to find permissions granted on individual objects in a database?

Run the following SQL to find permissions granted on individual objects on the database

SELECT
o.name AS object_name,
x.name AS user_name,
MAX(case when p.action = 193 then 'X' ELSE '' end) AS 'SELECT',
MAX(case when p.action = 197 then 'X' ELSE '' end) AS 'UPDATE',
MAX(case when p.action = 195 then 'X' ELSE '' end) AS 'INSERT',
MAX(case when p.action = 196 then 'X' ELSE '' end) AS 'DELETE',
MAX(case when p.action = 224 then 'X' ELSE '' end) AS 'EXECUTE'
FROM sysusers u
INNER JOIN sysprotects p ON u.uid = p.uid
INNER JOIN sysobjects o ON o.id = p.id
INNER JOIN (SELECT name FROM sysusers uu) x ON x.name = u.name AND o.name = 'object_name'
GROUP BY o.name, x.name

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

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