Useful simple vi commands for DBA’s

Join us at FACEBOOK

COMMAND                                                        EFFECT

:.=                                                        find out the current line number

:1                                                         go to line 1

Ctrl-d                                                 page down

Ctrl-u                                                 page up

Shift-g                                               go to end of file

i                                                           insert text at current position

Shift-a                                               append text after end of current line

Shift-I                                                insert text before start of current line

Esc                                                      get out of edit mode, back into normal vi command mode

dd                                                        delete current line

10dd                                                  delete 10 lines from current line on down

d shift-g                                            delete all lines from current line and below

d 1 shift-g                                                            delete all lines from current line and above

.                                                            repeat previous command

shift-y                                               yank (copy) current line

p                                                          paste that copy into line below

/data                                                  search forward for occurencies of string "data"

/                                                           search forward for next occurrence of remembered search string

?                                                           search backward for next occurrence of remembered search string

:set ic                             make searches case insensitive

:1,$s/data/index/g                      replace all occurrencies of "data" with "index"

:1,$s/"//g                                        remove all " characters

:1,$s/$/ ;/                                       append " ;" to the end of every line

:1,$s/^/rem /                                insert "rem " to the start of every line

:w                                                        write (save) file

:q                                                         quit out of vi

:q!                                                        quit out of vi without saving changes

:wq                                                     write (save) file and quit out of vi

shift-z                           shift-z       same as above ":wq" except does not write (change file modification

                                                              times) if you have not made any changes.

:n                                                         next file (when vi'ing a series of files, e.g. with using "vi *" at the

                                                             command prompt)

u                                                          undo last command

shift-j                                                 Join next line onto end of current line

                           

                    VI Quick Reference Guide

                     ---------------------------------

BASIC commands

$ vi filename              - normal mode

$ vedit filename       - user friendly edit

$ esc                              - return to command mode

                       

INSERTING

o                  - insert a line after cursor

O                 - insert a line before cursor

i                   - insert text at cursor

a                  - insert text after cursor

I                   - insert text at start of line

A or $a      - insert text at eol

SAVING AND QUITING                      

ZZ               - save and exit                     

:w               - save without exiting              

:w!              - save write protected file         

:q                - quit editor                       

:q!               - quit without saving

                     

DELETING

dd               - delete current line

x                  - delete char at cursor

X                 - delete char before cursor

dw              - delete word

D or d$     - delete from cursor to eol

              

CURSOR MOVEMENT                 

     k                            

     ^                             

h < > l                          

     v                            

      j                             

     

                           

        REPLACING / CHANGING

R - replace till eol

r - replace one character

C - change all text after cursor

cw - change a word

c$ - change from cursor to EOL

cG - change from cursor to EOF

MOVING ABOUT                            

f  - page forward                 

 b  - page back

 d  - 1/2 page forward

 u  - 1/2 page back

 w  - next word                       

 b  - previous word     

^B  - Goes one page UP

^  - start of line (shift 6)                                                        

 G  - go to end of file                        

 1G - go to beginning of file                  

 g  - show position in file               

^D  - Goes half page down                  

^U  - Goes half page up 

^F  - Goes one page DOWN                                       

 $  - end of line

 nG - go to nth line

MOVING TEXT

To move n lines of text from A to B

1. move cursor to A

2. ndd - delete n lines

3. move cursor to B

4. P - pull text in after cursor

p - pull text in before cursor

(,(   next, previous sentence

},{   next, previous paragraph

]],[[ next, previous section

%    goto matching parenthesis () {} []

YANK OPERATOR TO BUFFER

 yw - yank word

 y$ - yank from cursor to EOL

 yG - yank from cursor to EOF

 yy - yank complete current line

 p  - paste from buffer after cursor

 P  - paste from buffer before cursor

                               USEFUL COMMANDS

                         . - repeat last command

                         u - undo last command

                         U - undo changes made to current line

                         J - join the next line to the current line

                        ^G - Show the current filename and status

                         ! - Ejecuta uan linea del shell

           :1,$s/old/new/g - global search and replace old with new *

           :.,$d           - delete from current line to eof

           :r filename     - read filename into this vi buffer

           :n,mw filename  - write lines n through m to filename

SEARCHING

/abc - search for string "abc"

N - find previous occurrence

n - find next occurrence 

COPYING TEXT

To copy n lines of text from A to B

1. move cursor to A

2. nyy - yank n lines

3. move cursor to B

4. P - pull text in after cursor                                            

    p - pull text in before cursor

See Also

Networking in Oracle

User Management

USER

Creating the user

SQL>select tablespace_name,file_name from dba_data_files;

SQL>select tablespace_name,file_name from dba_temp_files;

SQL> Create user USER01 identified by USER01 default tablespace USER01_ts   temporary tablespace temp                            

quota unlimited on USER01_ts

quota 100m on USER01_demo_ts

password expire

account lock;

Assigning the quota to the user on other tablespace

SQL> alter user USER01 quota 10m on tbs1;

How To unlock the user account

SQL> alter user USER01 account unlock;

How To force user to change the Password

SQL> alter user USER01 password expire;

How to Grant privileges to users

SQL>grant connect, resource to user02 identified by user02;

SQL> grant create session, create table, create sequence to USER01;

SQL> conn USER01/USER01;

Views

SQL> select * from session_privs;

SQL> select * from role_sys_privs;

 

How to Assign sysdba and sysoper privilages to user

1. Creating Password file for instance

$orapwd file=orapw<sid>  password=<password> force=y ignorecase=y

Eg:

 $orapwd file=orapwdevlp password=password force=y ignorecase=y

Note:

If u don’t create password file, then you will get the error  as Below

ERROR at line 1:

ORA-01994: GRANT failed: password file missing or disabled

2. Grant using command

Sql> grant sysdba to user01;

Sql>grant sysoper to user01;

Now connect that user, as below

$sqlplus user01/user01  as sysdba

Sql>show user

       SYS

$sqlplus user01/user01 as sysoper

Sql>show user

       PUBLIC

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB  SYSOP  SYSAS

-------------------------------- ---------- -------  ---------

SYS                                    TRUE   TRUE    FALSE

USER01                                            TRUE   FALSE  FALSE

*** By default password is case sensitive, to disable it set the following parameter to false            

Sec_case_sensitive_logon=false;

SQL>alter system set sec_case_sensitive_logon=true scope=both;

Profiles

Creating profile

Sql> create profile profile1 limit

                Failed_login_attempts 3   (no of attempts)

                Password_lock_time 1       (no of days)   1/24  (for 1 hour)

                Password_life_time 7         (no of days)

                Sessions_per_user 5            (no of total sessions)

                Idle_time 1                           (in minutes)

                Connect_time 600;             (10 hours)

Assigning profile

Sql>  create user emp1 idenfied by emp1 profile profile1;

Sql> alter user emp1 profile profile1;

SQL>select * from dba_profiles where profile=’profile1’;

SQL>select username, profile from dba_users where profile=’PROFILE1’;

SQL>alter profile profile1 limit password_lock_time 2;

SQL>select * from dba_profiles where profile=’PROFILE1’;

·         To enforce kernel/resource parameters the following parameter must be set

Sql> alter system set resource_limit=true scope=both;

How to Apply password restriction in profile:

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql;

SQL>alter profile default limit password_verify_function null;

SQL> alter profile prof1 limit Password_verify_function verify_function;

SQL> alter profile prof1 limit Password_verify_function null;

Important views for Managing Users:

v$process

v$session

v$sqlarea

v$sqltext

v$lock

v$session_wait

v$sess_io

Data Pump Gets Better

Today I will Discuss some new Features of Datapump in 11G

One of the big issues with Data Pump was that the dumpfile couldn't be compressed while getting created. That was something easily done in the older Export/Import utility. In Oracle Database 11g, Data Pump can compress the dumpfiles while creating them. This is done via a parameter COMPRESSION in the expdp command line. The parameter has three options:

METDATA_ONLY - only the metadata is compressed; the data is left as it is (available in Oracle Database 10.2 as well).

DATA_ONLY - only the data is compressed; the metadata is left alone.

ALL - both the metadata and data are compressed.

NONE - this is the default; no compression is performed.

Here is how you compress the export of the table UNITS_FACT:

$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_comp.dmp compression=all

When importing the dumpfile, the import does not have to decompress the file first; it decompresses as it reads it, making the process really fast.

The two other enhancements in Data Pump are:

Encryption: the dumpfile can be encrypted while getting created. The encryption uses the same technology as TDE (Transparent Data Encryption) and uses the wallet to store the master key. This encryption occurs on the entire dumpfile, not just on the encrypted columns as it was in the case of Oracle Database 10g.

Masking: when you import data from production to QA, you may want to make sure sensitive data such as social security number, etc. are obfuscated (altered in such a way that they are not identifiable). Data Pump in Oracle Database 11g enables you do that by creating a masking function and then using that during import.