vi for Apps DBAs

vi for Apps DBAs
I intend to start of my first Blog with "vi for DBAs". I used to receive queries ( not sql queries :) ) from fellow DBAs on how to search and replace in "vi". A collection of complex (which I think are) search and replace commands of vi that I came across are summarized below. These commands are useful for DBAs in their day-to-day Administration.
#1. Once upon a time a user sent me an excel sheet with 100 tables in it. Requesting me to grant INSSERT,UPDATE & DELETE privileges for two different users. Just imagine the complexity if you are not familiar with vi. I opened up "vi" (my favourite editor), snipped & pasted all the 100 tables into a file. Now my requirement is to generate a grant sql script with the following sql query.

SQL> select 'grant insert on' table_name 'to xyz user' from dba_tables where table_name in ('TABLE1','TABLE2',.....)
Just to high-light every table in the above sql need to be quoted using ' ' and separated by a ",". To achieve this for all the tables that are already copied into to a file I used the following command.

:1,$ s/.*/'&',/g

Explanation:

".*" - indicates any text/pattern in the line. "&" - indicates the text that is already in the line. So, if the line has TABLE1 it will be replaced by 'TABLE1', (notice the quote and comma).

#2. How to search and replace a text containing lots of "/"s.

Assume the search string is "/prod1/applmgr/prod1appl" and replace string is "/test1/applmgr/test1appl". If you use the syntax :1,$ s//prod1/applmgr/prod1appl/test1/applmgr/test1appl/g. Obviously "vi" is going to get confused and errors out. Because it will not know which "/" is part of the search string and which "/"is a delimiter that is part of the search-replace syntax.

So, use the syntax mentioned below which will make you smile.

:1,$s#/prod1/applmgr/prod1appl#/test1/applmgr/test1appl#g.
"#" is part of the search-replace syntax. In other words you can use any character as a delimiter as long as the character that you are using as delimeter is not part of the search/replace string. See, how flexible the "vi" is....

#3. How to do a copy and paste across files ?

Assume, we have two files a.txt and b.txt. Our goal is to copy the text from a.txt and paste it onto b.txt. In "vi" you can define buffers and even name them. Surprising isn't it !!. Yes you can. Where each buffer can be named after numbers 1-9 or alphabets a-i. So,follow the procedure mentioned below to copy and paste across files.

#1. First step in this process of search and replace is to define a buffer and put some content into it.
#2. vi a.txt and "a2yy (Remember #1. Buffer Name can be a-i or 1-9). So, in this case I have chosen buffer name to be "a". 2yy indicates 2 lines to be copied/yanked. #3. Now open b.txt by using :e b.txt - once b.txt is opened, go to the line you would like to have the lines pasted and type "ap. This will recall the contents of buffer "a" and the contents will be pasted.

Tip: Must be wondering how to remember the syntax ? It is easy. All you need to do is use it couple of times.

Enjoy !! & Happy vi'ing.