Monday, October 31, 2011

Fractured block in Oracle


A block in which the header and footer are not consistent at a given SCN. In a user-managed backup, an operating system utility can back up a datafile at the same time that DBWR is updating the file. It is possible for the operating system utility to read a block in a half-updated state, so that the block that is copied to the backup media is updated in its first half, while the second half contains older data. In this case, the block is fractured.

For non-RMAN backups, the ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP command is the solution for the fractured block problem. When a tablespace is in backup mode, and a change is made to a data block, the database logs a copy of the entire block image before the change so that the database can reconstruct this block if media recovery finds that this block was fractured.

The block that the operating system reads can be split, that is, the top of the block is written at one point in time while the bottom of the block is written at another point in time. If you restore a file containing a fractured block and Oracle reads the block, then the block is considered a corrupt.

What Happens When A Tablespace/Database Is Kept In Begin Backup Mode (Doc ID 469950.1)

Vi Editor Commands


Here are a few useful commands for those who are new to vi.

esc :q!
Just quit - don't save
esc :e!
Revert to saved
esc :wq
Save and exit
esc shift zz
Save and exit
esc i
Enter insert mode (edit mode)
esc a
Enter append mode (edit mode)
esc
Exit edit mode
esc r
Replace a single character
esc x
Delete a single character
esc  u
Undo last Change
esc  U
Undo all changes to line
esc dd
Delete a single line
esc yy
Copy a single line
esc p
Paste a single line
.
Repeat the last command
esc /
String search
esc $
Jump to end of line
esc ^
Jump to begining of line
shift g (or) :$
Jump to the end of the file
:1  or gg
Jump to the begining of the file
:.=
Display the current line number
nG  (or) :n
Move to nth line of the file
:set nu
To turn ON numbering to each line
:set nonu
To turn OFF numbering to each line


FIND & REPLACE :

Syntax:          :%s/WORD-To-Find-HERE/Replace-Word-Here/g   


Examples
To find each occurrence of 'UNIX', and replace it with 'Linux', enter (press ESC, type : and following command):
                                 :%s/UNIX/Linux/g

Task: Find and Replace with Confirmation
Find a word called 'UNIX' and replace with 'Linux', but ask for confirmation first, enter:
:%s/UNIX/Linux/gc

Task: Find and Replace Whole Word Only
Find whole words exactly matching 'UNIX' to 'Linux'; and ask for confirmation too:
:%s/\<UNIX\>/Linux/gc

Task: Case Insensitive Find and Replace
Find 'UNIX' (match UNIX, unix, UnIx, Unix and so on) and replace with 'Linux':
:%s/unix/Linux/gi



Same command with confirmation:
:%s/unix/Linux/gic

Task: Case sensitive Find and Replace
Find each 'UNIX' and replace with 'bar':
:%s/UNIX/bar/gI
Same command with confirmation:
:%s/UNIX/bar/gIc

How Do I Replace In the Current Line Only?
Find 'UNIX' and replace with 'Linux' in the current line only (note % is removed from substitute command)
       :s/UNIX/Linux/g



NOTE: You need to prefix % the substitute command to make changes on all lines:
     :%s/UNIX/Linux/g
 How Do I Replace All Lines Between line 100 and line 250?
     :{START-n},{END-n}s/word1/word2/g



EX : Find 'UNIX' and replace with 'Linux' all lines between line 100 and line 250, enter
           :100,200s/UNIX/Linux/g

OR
           :100,200s/UNIX/Linux/gc

Task :  Count the word ABC  in entire vi file     :%s/ABC/ABC/g


Saturday, October 1, 2011

JOINS


Hash join :

Hash Join will perform mostly superior when one table is big and the other is small.  Smaller table is made into hash and fed into the larger table. This is good when you have 1 Large and 1 Small table.
            A hash join (ideally) takes the smaller table (or row source), iterates over its rows and performs a hash algorithm on the columns and stores the result. After it has finished, it iterates over the other table and performs the same hashing algorithm on the joined columns. It then searches the previously built hashed values and if they match, it returns the row.

Nested Loop :

It’s a loop with in a loop. The nested loop iterates over all rows of the outer table. If there are conditions in the where clause of the SQL statement that apply to the outer table only. These rows from the inner table are either found using an index (if a suitable exists) or by doing a full table scan.


Sort Merge:

 Good in cases when Both are large tables . A merge join basically sorts all relevant rows in the first table, and also sorts the relevant rows in the second table, then merges these sorted rows.


Auto Scroll Stop Scroll