Search

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.

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

How to Log in to Oracle as DBA

sqlplus / as sysdba

or

sqlplus sys/[email protected]hema 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;