• The Queen's Innovation Award is Ours

    Today I heard from my director that our company received a letter from the Secretary of the Queens Awards for Enterprise to say we won the Queen’s Awards for Enterprise: Innovation 2012.

    What great news! It is an absolute pleasure to be part of this success and celebrate it as being the head of software development.

  • The True Value of Software

    A software application’s feature list or its level of sophistication cannot determine its value. It is almost nothing to do with how well it is coded by the latest and trendy technologies.

    The true value of an app is depends on how its users value it and how well it integrates into their organisations and their lives where the app becomes a practical tool genuinely helping its users to solve their everyday problems seamlessly.

  • Google's New Privacy Policy

    Google with the latest changes in its privacy policy has raised the price of its all services including search, gmail, youtube.

    We don’t need to send any money; all we have to do is to pay it with our privacy by providing more information about ourselves including mobile numbers, caller information, GPS location etc. Or, is it the fact that our privacy has lost its value and became less valuable so we have to give more to satisfy Google and continue using the same services? If so, who is making our privacy less valuable then?

  • Oracle Database Cheatsheet

    How to Log in to Oracle as DBA

    sqlplus / as sysdba

    or

    sqlplus sys/[email protected] as sysdba
    sqlplus [email protected] as sysdba;
    sqlplus "/as sysdba"

    How to unlock an account in Oracle

    ALTER USER user ACCOUNT UNLOCK;

    How to change a user’s password in Oracle

    ALTER USER user IDENTIFIED BY password;

    How to Export and Import Oracle Database

    exp schemaname/[email protected]/databasename FILE=schemaname.dmp log=log.txt statistics=none
    imp schemaname/[email protected] FILE=schemaname.dmp FROMUSER=fromuser TOUSER=touser log=implog.txt

    if you importing only data then you can use IGNORE=Y

    imp schemaname/[email protected] 
        tables=users 
        FILE=users.dmp 
        FROMUSER=fromuser TOUSER=touser 
        IGNORE=Y

    How to use Oracle TO_DATE function

    to_date('10-12-06','MM-DD-YY')
    to_date('jan 2007','MON YYYY')
    to_date('2007/05/31','YYYY/MM/DD')
    to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI')
    to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')
    to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss')
    to_date('022002','mmyyyy')
    to_date('12319999','MMDDYYYY')
    to_date(substr( collection_started,1,12),'DD-MON-YY HH24')
    to_date('2004/10/14 21', 'yyyy/mm/dd hh24')
    TO_DATE(First_Load_Time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60)

    Create a new Schema in Oracle

    sqlplus sys/password@database as sysdba
    
    CREATE USER newschema IDENTIFIED BY password;
    GRANT CONNECT,RESOURCE,DBA TO newschema;
    CREATE tablespace NEWSCHEMA_DATA datafile 'E:\app\oracle\tablespace\NEWSCHEMA_DATA.DBF' size 2048M extent management local;
    ALTER USER newschema quota unlimited on NEWSCHEMA_DATA;

    Oracle User Schema Sizes

    SELECT u.username, SUM(s.bytes/1024/1024) SIZE_IN_MB FROM all_users u, dba_segments s WHERE s.owner=u.username GROUP BY u.username ORDER BY SIZE_IN_MB DESC;

    How to show who I am in Oracle

    SHOW USER;

    How to list created tablespaces in Oracle

    select tablespace_name from user_tablespaces;

    How to list all users in an Oracle Database

    select * from all_users;

    How to show schema details of a table in Oracle

    DESC tablename;

    Change Oracle Password Expiry Policy

    If you keep getting error messages like ORA-28001: the password has expired in your automated Jenkins build logs then that means oracle set to expire user passwords. This could be a good security measure for production databases but does not help in the Continuous Integration environment. Oracle 11g sets password expiry by default to 180 days.

    This can be turned off or changed easily:

    • Password policies are registered under profiles, so first find out the active profile of the user:
        SELECT PROFILE FROM dba_users WHERE username = 'username'
    • Check the expiry limit in use in that profile
        SELECT resource_name, limit FROM dba_profiles WHERE profile='<profile>' AND resource_name = 'PASSWORD_LIFE_TIME';
    • You can set the limit to ‘UNLIMITED’ if you would like user’s password to never expire:
        ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

    How to solve EXP-00003: no storage definition found for segment(0, 0) error in oracle

    There is a bug in the export utility on version 11.2.0.1 when running against a 11.2.0.2 (and possiby 11.2.0.1) database that has tables without segments. Deferred segment creation was introduced as a smart feature in 11gR2, but this is causing error EXP-00003 when running the old export utility from lower versions. This is reported on MOS for pre-11g clients, but right now I could not find any reports that this is also a problem with the 11.2.0.1 version when this feature was introduced.

    Solution: Upgrade client or allocate one extent for the table that fails:

    alter table tableName allocate extent;