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.

Early binding and Late binding

Early binding is checking the existence of objects being referred in the program unit during the compilation phase. Late binding will not check the existence of objects till the runtime.

Wednesday, January 7, 2009

Fine Points

  • Can have CASE statement in the WHERE clause
  • All the DDL statements are auto commit.
  • SELECT SYSTIMESTAMP FROM DUAL
  • Triggers Cannot have ROLLBACK, COMMIT, SAVEPOINT and TRUNCATE.

Tuesday, January 6, 2009

Temporary Tables

Global Temporary Tables

Applications often uses some form of temporary data store for processes that are to complicated to complete in a single pass. often, these temporary stores are defined as database tables or PL/SQL tables.

Creation of Global Temporary Tables

The data in a global temporary table is private, such that inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. the ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE TEMP_TBL(
COL1 NUMBER,
COL2 NUMBER) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

CREATE GLOBAL TEMPORARY TABLE TEMP_TBL(
COL1 NUMBER,
COL2 NUMBER) ON COMMIT PRESERVE ROWS;

FEATURES :-
  • If the truncate statement is issued against a temporary table, only the session specific data is truncated. there is no affect on the data of other session.
  • Data in temporary tables is automatically delete at the end of the session, even if it ends abnormally.
  • Indexes can be created on temporary tables. the content of the index and the scope of the index is that same as the database session.
  • Views can be created against temporary tables and combinations of temporary and permanent tables.
  • Temporary tables can have triggers associated with them.
  • Export and import utilities can be used to transfer the table definitions, but no data rows are processed.
  • There are a number of restrictions related to temporary tables but these are version specific.