Flashback table

The table could be restored to using flashback table option. Before attempting it, find out how far to flash the table.

Steps :-

1. sql> select count(*) from scott.emp;

2.  export of the table 

3. flashback table

sql> alter table scott.emp enable row movement;

sql> flashback table scott.emp to timestamp

       to_timestamp('2010-12-18 12:00:00','YYYY-MM-DD HH24:MI:SS');

      

Flashback complete.

4. sql> select count(*) from scott.emp;

  COUNT(*)

----------

    784871

5. confirm with apps team

Hope this helps! Regards Rupam

Windows - Find space usage by windows folders using diruse

Directory Disk Usage, known as diruse is a free command line tool found on Microsoft's Help . Using diruse is easy. After you have downloaded the tool, install by clicking on diruse_setup.exe.

After installing the program, open a command prompt and run:

cd "\Program Files\Resource Kit"
diruse /M /* c:\

where:
/M – reports in Magabytes
/*  – Uses the top-level directories residing in the specified directory (In the above example C:\ is the specifed directory)

Below is the results of the output:

Date function in Oracle

Date function in Oracle

 examples of date function:

   insert into mydate values (sysdate -1);

   insert into mydate values (sysdate - 6/24); #   6 hours ago

  insert into mydate values (sysdate - 720/1440); # 12 hours ago
  click read for complete example

usage

 delete noprompt archivelog until time 'sysdate - 1';

 delete archivelog all backed up 1 times to device type disk completed before 'sysdate-6/24';

Tablespace details ( includes datafiles, type, autoextend, maxsize etc)

Summary

1. list datafiles

2. get type, auroextend, management type etc

Tablespace report

Run the following sql to get the space report in Oracle

Archiver Hung in Oracle database - ORA-16038, ORA-19504, ORA-00257

Archiver Hung in Oracle database

Steps to delete archive log file not needed for recovery (older than the last backup of the database)

Summary

1. check how much space is used by archiver

2. check the last good backup of the database

3. delete archive log files older than last good backup

4. crosscheck archive log

Blocking in Oracle Database

Follow the steps to locate and terminate blocking session from Oracle database (10g & up). Blocking_session_status contains ‘VALID’ when blocking_session is populated in 10g and up.

steps:

1. Display blocked session and their blocking session details

2. Find what is Blocking session Doing

3. Find sid and serial # of blocking session

4.  To terminate the session:

nfs volumes on Linux

-         Set the mount options explictly. Here are the mount options that need to be used for nfs volumes on Linux.

Use : rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0

default rsize and wsize for NFS is 4096 Blocks so if you have rsize=32k and wsize=32k then NFS would be able to read and write large datagram as compared to deafult one TCP option will make sure that your client are getting the data or not Hard & INTR - The program accessing a file on a NFS mounted file system will hang when the server crashes. The process cannot be interrupted or killed unless you also specify intr. When the NFS server is back online the program will continue undisturbed from where it was. actimeo is for access timeout and it should be 0 .

Hope this Helps!. Rupam

Table lock in Oracle

Explanation

Dropping or truncating a table requires you to acquire an exclusive lock on the table. A table is a "busy" resource if there are other sessions modifying or holding a lock on the same table.