unlock orcladmin password in shell script

Posted on 5 December 2011 by Andrew Fraser

Shell script to check if orcladmin account is locked, and unlock it if required

# Check to see if orcladmin account is locked, and unlock it if it is.
if [ "`ldapbind -p <myport> -D cn=orcladmin -w <myorcladminpassword>`" = "bind successful" ]
then
   echo orcladmin account is ok, is not locked.
else
   echo unlocking orcladmin account...
   oidpasswd connect=<mydatabase> unlock_su_acct=true <<END_PASSWD
<mydatabasepassword>
END_PASSWD
fi

Useful for running from cron for an environment where orcladmin repeatedly gets locked.

The -p port is optional depending on configuration.

Posted in Oracle forms, Scripts, Security | Leave a comment

Indexed views – workaround for “Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.”

Posted on 5 December 2011 by Andrew Fraser

Indexed views (the SQL Server equivalent of Oracle’s materialized views) are a good performance fix, especially for reporting and data warehouse systems.

However they only work with inner joins. Attempts to create an indexed view with outer joins fail with:

Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.

This is quite a limitation of SQL Server – Oracle’s materialized views do not have this limitation.

One workaround is to manually create a table using the same SQL that you tried to use in the indexed view:

select col1, col2, col3
INTO myreportingtable
from table1
left join table2 on ...

And then change the view definition to reference only that new table:

create view myview as select col1, col2, col3 from myreportingtable

That does mean a scheduled task or job will need to be created to refresh the new reporting table every night (or as required).

Continue reading →

Posted in Performance tuning, SQL server | Leave a comment

Oracle range limit AD BC dates

Posted on 11 August 2011 by Andrew Fraser

This is the extreme limit for oracle dates:

alter session set nls_date_format = 'Dy DD-Mon-YYYY AD' ;
select sysdate, to_date('01-JAN-4712 BC','DD-MON-YYYY AD'), to_date('30-DEC-9999 AD','DD-MON-YYYY AD') from dual ;

Without encountering this error:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 – “(full) year must be between -4713 and +9999, and not be 0″
*Cause: Illegal year entered
*Action: Input year in the specified range

Posted in Uncategorized | Leave a comment

Oracle SQL Developer privileges for Other Users procedures and package bodies

Posted on 11 August 2011 by Andrew Fraser

To view code for procedures and package bodies in SQL Developer that are owned by other users, you need the following privilege:

grant select_catalog_role to <user> ;

This privilege does not give access to the sys.link$ view, so should be safe to grant out.

You also need to use version 3 or above of Oracle SQL Developer, because of bug 9530717 in version 2 which caused package body to not display for users who did not have elevated security privileges.  Note that Oracle Support Note 1226324.1 still states that SQL Developer v3 is not released yet – actually it was released March 2011.

Granting ‘select any dictionary’ would also be sufficient for SQL Developer users to see other users procedures and package bodies – but that is more powerful than select_catalog_role especially in 11g and above with this security issue.

Mike Smithers has a related posting on this SQL Developer issue – although his workaround is no longer required with SQL Developer v3.

If moving from an older to a newer version of SQL Developer, you can export your saved connections first – including stored passwords – and then import them into the new version. To do this, right click on ‘Connections’ and choose ‘Export Connections’ or ‘Import Connections’. The export writes the information to an xml file which you can delete afterwards, although the passwords inside it are encrypted anyway.

Posted in SQL Developer | Leave a comment

Oracle Data Guard Overview

Posted on 9 August 2011 by Andrew Fraser

Oracle Data Guard is Oracle’s main solution for high availability in the event of a disaster. This overview is for Oracle 11gR2. A pdf version of this page is available here.

Data Guard Overview

Continue reading →

Posted in Dataguard | 1 Comment

Switch Oracle Forms List of Values on and off dynamically at run time

Posted on 9 August 2011 by Andrew Fraser

In e.g. post-query block level trigger:

IF block.check_field = 'F'
THEN
   -- switch on
   Set_Item_Property( 'block.field' , LOV_NAME, 'your lov') ;
   Set_Item_Property( 'block.field', VALIDATE_FROM_LIST, PROPERTY_TRUE);
ELSE
   -- switch off
   Set_Item_Property( 'block.field' , LOV_NAME, '') ;
   Set_Item_Property( 'block.field', VALIDATE_FROM_LIST, PROPERTY_FALSE);
END IF ;

H/t François Degrelle

Posted in Oracle forms | Leave a comment

Oracle Developer Suite 10g 9.0.4.0.1 windows software download media

Posted on 15 July 2011 by Andrew Fraser

Oracle Developer Suite 10g 9.0.4.0.1 for Windows (forms developer) is not easy to obtain now, out of support by Oracle for a long time, but that version is still in use out there. So I have zipped both installation CDs here.

Posted in Uncategorized | 5 Comments

Cron fix for ulimit: max user processes: cannot modify limit:operation not permitted

Posted on 14 July 2011 by Andrew Fraser

Werner Puschitz has details of how to use ulimit and limits.conf to change maximum processes and open file descriptors.

But for processes running from cron, I found I had to additionally make the below changes (this is on old version: 32 bit Linux 2.4.21-27.ELsmp Red Hat Enterprise Linux AS release 3 (Taroon Update 4) – maybe not needed for newer versions).

vi /etc/init.d/crond

Add lines like:

ulimit -Hu 131072  # new line Jul 2011
ulimit -Su 131072  # new line Jul 2011
ulimit -Hn 262144  # new line Jul 2011
ulimit -Sn 262144  # new line Jul 2011

These should be the first commands in that file.

Restart cron with:

/etc/init.d/crond restart

Not relevant for cron, but file /etc/profile is loaded at login and may have been edited to contain ulimit settings – if so those lines would need changed or removed for normal (non cron) logins.

Thanks to Michael Heiming for this.

Posted in Uncategorized | 1 Comment

Split space delimited string with regexp SQL

Posted on 13 July 2011 by Andrew Fraser

Split up a delimited string with:

select regexp_substr('Hello world !' ,'[^ ]+', 1, 1)
  , regexp_substr('Hello world !' ,'[^ ]+', 1, 2)
  , regexp_substr('Hello world !' ,'[^ ]+', 1, 3)
from dual ;

Output:

REGEX REGEX R
----- ----- -
Hello world !

From http://psoug.org/reference/regexp.html

Posted in Scripts | Leave a comment

sed change entire line

Posted on 8 June 2011 by Andrew Fraser

Here I use sed to replace the entire 2nd line in a lot of files with a new 2nd line.

The shell script:

for fle in `ls *.msg`
do
    # copy file first so as can keep permissions and ownership identical
    cp -p $fle test_$fle
    # replace entire 2nd line with a new 2nd line
    sed -f t.sed $fle > test_$fle
done

Which calls this sed command file, named t.sed:

2c\
\To: dummy.email@me.com

The result is that the 2nd line in each file reads: 

To: dummy.email@me.com
Posted in Linux, Scripts | Leave a comment