Friday, September 11, 2009

Export backup of the previous 15 days' data?

The Oracle export utility is not "time aware," so it cannot do what you are asking by default. Just about the only way to accomplish this task with the export utility is to add a column to your tables which contains a date when the row was inserted or updated. You can then use the WHERE parameter of the exp utility to export where this column is less than 15 days old, similar to the following:

exp file=mydump.dmp owner=scott where='date_modified < sysdate -15 '

Friday, February 20, 2009

Query - Names in given order

SELECT REGEXP_REPLACE('Muthu Bernandas Savari', '(.*) (.*) (.*)','\2 \3\1') FROM dual

Query - Contain any character that is not a numeric digit.

SELECT ENAME FROM EMP WHERE REGEXP_LIKE(ENAME, '[^[:digit:]]')

Tuesday, February 17, 2009

Second Largest Number

SELECT empno FROM (SELECT e.empno, RANK() over (ORDER BY e.empno DESC) rk FROM emp e ) WHERE rk = 2

Last month first date

select last_day(add_months(sysdate,-2))+1 from dual

To know the current week of the year

SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'IW')) FROM DUAL

Monday, January 19, 2009

Wrapping

Wrapping is converting the PL/SQL code into the form, Which nobody can understand using the Wrap utility. This helps in hiding the source code.