What is an index in oracle?

An index helps speed up retrieval data from large table. A column that is frequently referred to in the WHERE clause is a potential candidate for indexing.

Example EMP TABLE   has 100000 rows.

ID	SALARY
1	20000
2	5000
3	1000
………..
100000	1500

Figure 1.1

SELECT * FROM EMP WHERE SALARY=1000;

 Above this query which will return only on row if you create index on the “EMP” table then performance will be  increased  Reason of performance enhancement is that when you create index at that time table is  created by oracle  in which indexed column example here ”salary” will be sorted order and it’s rowid  as shown below Figure 1.2. When you fire query “SELECT * FROM EMP WHERE SALARY=1000” it first check in indexed   table and retrieve rowid from indexed table matched it with  “EMP” table  rowid and return row. Now full scan of “EMP” table no need .This is the Advantage of index.

CREATE INDEX IND_SAL ON EMP (SALARY);

SALARY	ROWID
1000	AABUZkAAGAAEFCbAAA
1500	AABUZkAAGAAEFCbAAB
5000	AABUZkAAGAAEFCbAAC
20000	AABUZkAAGAAEFCbAAD

Figure 1.2

SELECT * FROM EMP WHERE SALARY=1000;

Determining when to create an index:

Create an index if you frequently want to retrieve less than 15% of the rows in a large table.

Small tables do not require indexes.

Limit the Number of Indexes for Each Table.

A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated.

Explain Plan.

 Before execute the query ,please check explain plan . To check explain plan of the query write click on the query and click on explain in pl sql developer. If index is not called automatically then we have to manually call.

BEFORE INDEX  CREATE ON EMP TABLE

Plan

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

SELECT STATEMENT TABLE ACCESS FULL  EMP

AFTER INDEX CREATE ON EMP TABLE      

Plan

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

SELECT STATEMENT

 TABLE ACCESS BY INDEX ROWID EMP

  INDEX RANGE SCAN IND_SAL                     

Type of index

·        B*Tree Indexes

·        Bitmap index

·        Function base index

·        Unique index

·        Composite index

Examples are As Under

ð  Select a.id,a.name,b.address from emp a,dep b where a.id=b.id;

Create index ind_emp on emp (id);

                Create index ind_dep on dep (id);

ð  Select  *  from emp where upper(name)=’ROHIT’;

Create index ind_emp on emp(upper(name));

ð  Select * from emp order by id;

Create index ind_emp on emp (id);

ð  Select * from emp where id=5 and name=’rajesh’;

Create index ind_emp on emp (id,name);

ð  Select a.id,b.address from emp a,dept b where a.id=b.id group by a.id,b.address;

                Create index ind_emp on emp (id);

                Create index ind_dept on dept (id);

ð  select  *  from emp where id>=5;

Here id should be displayed which values greater than 5; create index as below and manually define index for scanning as under query.

Create index ind_emp on emp (id);

Select  /*+  index(emp ind_emp) */   id, name from emp  where id>=5;

ð  SELECT * FROM EMP WHERE SALARY IS NULL;

SELECT * FROM EMP WHERE  (nvl(SALARY,0))=0;

create index

    emp_null_emp_nbr_idx

on

   emp

   (nvl(ename,o));

SELECT * FROM EMP WHERE  (nvl(SALARY,0))=0;

Creating an index with NOLOGGING has the following benefits:

                Space is saved in the redo log files.

                The time it takes to create the index is decreased.

                Performance improves for parallel creation of large indexes.

ð  Create index ind_emp on emp (id) NOLOGGING;

 

Viewing Index Information

The following views display information about indexes:

ð  USER_INDEXES

ð  USER_IND_COLUMNS

QUICK OVERVIEW OF SHARED POOL TUNING

  Before talking about tuning the shared pool, let me define some terms related to it:

Cursor : It is basically a memory area in library cache where various information about the sql statement being executed is stored. The info stored is
  - text of sql statement
  - its execution plan
  - its execution statistics
  - Environment

In an earlier post, I had discussed in detail  about parent and child cursors .

The pointers to cursors are called cursor handles. The hash chains in the library cache contain cursor handles. When a SQL statement is issued,

  - The statement is checked for syntax and semantics.

  - The PGA is searched for any open cursor for the statement. Open cursor means that an instantiation for the cursor exists in the PGA and pin does not have to be obtained on the cursor handle. If open cursor is found, the statement is executed. Availability of the open cursors is influenced by the parameters OPEN_CURSORS and CURSOR_SPACE_FOR_TIME.

- If an open cursor is not found in PGA, it is searched for closed cursors i.e. for pointers to the cursor in library cache. If closed cursor is found, hash chains need not be searched and library cache lock does not have to be obtained. If a closed cursor is found, cursor is opened i.e. a pin is obtained and an instantiation of the cursor is created in the PGA and then the statement is executed.
The availability of the closed cursors is influenced by the parameter SESSION_CACHED_CURSORS.

- If closed cursor is also not found, hash chains in the library cache need to be searched  to locate the cursor handle for the statement. Hence SQL statement is hashed and the hash bucket is located where cursor handle might be found. Latch on the bucket is obtained to search the bucket.

- If the cursor handle is found in hash chain (soft parse),
  . library cache lock is obtained,
  . cursor handle is stored in the PGA,
  . pin is obtained,
  . an instantiation of the cursor is created in the PGA,
  . latch on the bucket is released,
  . the statement is executed,
  . latch on the bucket is obtained,
  . statement is unpinned and
  . latch on the bucket is released.

- If the cursor handle is not found in hash chain (hard parse), some memory needs to be allocated in library cache to create the cursor
  . library cache load lock is obtained
  . memory is allocated (If free memory is not available, some objects may be aged out using LRU algorithm).
  . The location of the cursor is stored in the Cursor handle in hash chain
  . library cache lock is obtained,
  . THe cursor handle is pinned.
  . Execution plan for the statement is created and stored in the cursor.
  . cursor handle is stored in the PGA,
  . an instantiation of the cursor is created in the PGA,
  . latch on the bucket is released,
  . the statement is executed,
  . latch on the bucket is obtained,
  . statement is unpinned and
  . latch on the bucket is released.


  Waits on library cache may be caused due to following:

- Open/Closed cursor is not found in PGA  as
  . Earlier cursor was invalidated because
     - any of the objects the statement depended on have been altered
     - Procedure was recompiled
  . Parameter OPEN_CURSORS/SESSION_CACHED_CURSORS was set to a low value.
  . Cursors closed explicitly after each execution -
  . Frequent logins and logoffs

- A lot of hard parsing is there because
  . Open/closed cursors were not found
  . Earlier cursors have aged out due to inadequate size of shared pool
  . Earlier cursor was invalidated because
     - any of the objects the statement depended on have been altered
     - Procedure was recompiled
  . Bind variables were not used for the statement causing multiple cursors to be created
  . CURSOR_SHARING was set to EXACT

- Multiple sessions executing same SQL/Procedure so that they have to wait to pin the cursor

- Simultaneous compilation of the procedure which is being executed in another session

- Concurrent DDL on the underlying objects causing the depenedent SQL/Procedures to be invalidated

- Repetitive loading of frequently executed SQL/Procedures/Packages

- Using sequences without caching or a small value of cache

- Multiple parent cursors creation due to
  . Non Standardized SQL
  . Parameter CURSOR_SHARING=EXACT

-  Multiple child cursors  creation due to
  . Optimizer mismatches
  . Parameter mismatches
  . Translation mismatches
  . Bind variable mismatches
  . Language mismatches

SOLUTIONS:

- Avoid hard parsing by using bind variables instead of literal values in your queries.

 The script below can be used to find sqls which can use literals i.e. those statements
 which use the same execution plan but differ in text:

select plan_hash_value, count(plan_hash_value) cnt
from     v$sql
group by plan_hash_value
having   cnt > 5
order by count(plan_hash_value);

PLAN_HASH_VALUE        CNT
--------------- ----------
      272002086        520

select sql_text
from   v$sql
where  plan_hash_value =  272002086
  and  rownum < 10;

- Avoid application designs that result in large numbers of users issuing dynamic, unshared SQL statements. Typically, the majority of data required by most users can be satisfied using preset queries. Use dynamic SQL where such functionality is required.

- Ensure that users of the application do not change the optimization approach and goal for their individual sessions.

- Establish the following policies for application developers:
   . Standardize naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.

   . Consider using stored procedures whenever possible. Multiple users issuing the same stored procedure use the same shared PL/SQL area automatically. Because stored procedures are stored in a parsed form, their use reduces run-time parsing.

- For SQL statements which are identical but are not being shared, you can query V$SQL_SHARED_CURSOR to determine why the cursors are not shared.

- Single-User Logon and Qualified Table Reference :

   . Large OLTP systems where users log in to the database as their own user ID can benefit from explicitly qualifying the segment owner, rather than using public synonyms. This significantly reduces the number of entries in the dictionary cache. For example:

SELECT employee_id FROM hr.employees WHERE department_id = :dept_id;

   . An alternative to qualifying table names is to connect to the database through a single user ID, rather than individual user IDs. User-level validation can take place locally on the middle tier. Reducing the number of distinct userIDs also reduces the load on the dictionary cache.

- Use of PL/SQL :Using stored PL/SQL packages can overcome many of the scalability issues for systems with thousands of users, each with individual user sign-on and public synonyms. This is because a package is executed as the owner, rather than the caller, which reduces the dictionary cache load considerably.

-  Avoid performing DDL operations on high-usage segments during peak hours. Performing DDL on such segments often results in the dependent SQL being invalidated and hence reparsed on a later execution.

- Cache Sequence Numbers : Allocating sufficient cache space for frequently updated sequence numbers significantly reduces the frequency of dictionary cache locks, which improves scalability.

- Using related database parameters

  . CURSOR_SHARING: Setting this parameter to smilar can solve your hard parse problems caused by using literals but can have side effects mostly on DSS environments and systems which uses stored outlines.

     - CURSOR_SHARING=FORCE will substitute bind variables for literals and only one cursor will be created.
     - CURSOR_SHARING=SIMILAR will substitute bind variables for literals and cause only one parent but multiple child cursors to be created.

   . CURSOR_SPACE_FOR_TIME: This parameter specifies whether a cursor can be deallocated from the library cache to make room for a new SQL statement. CURSOR_SPACE_FOR_TIME has the following values meanings:

      - FALSE (default): A cursor can be deallocated from the library cache regardless of whether application cursors associated with its SQL statement are open. In this case, Oracle must verify that the cursor containing the SQL statement is in the library cache.

      - TRUE : A cursor can be deallocated only when all application cursors associated with its statement are closed. In this case, Oracle need not verify that a cursor is in the cache, because it cannot be deallocated while an application cursor associated with it is open.

You must be sure that the shared pool is large enough for the work load otherwise performance will be badly affected and ORA-4031 eventually signalled.

   . OPEN_CURSORS: This parameter sets the upper bound for the number of cursor that a session can have open and if you size it correctly, cached cursors can be stay opened and won’t have to be closed to let new cursor open

   . PROCESSES / SESSIONS: You can review the high water mark for Sessions and Processes in the V$RESOURCE_LIMIT view. If the hard-coded values for these parameters are much higher than the high water mark information, consider decreasing the parameter settings to free up some memory in the Shared Pool for other uses.

   . SESSION_CACHED_CURSORS: When a cursor is closed, Oracle divorces all association between the session and the library cache state. If no other session has the same cursor opened, the library cache object and its heaps are unpinned and available for an LRU operation. The parameter SESSION_CACHED_CURSORS controls the number of cursors “soft” closed, much like the cached PL/SQL cursors. Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.

To determine whether the session cursor cache is sufficiently large for your instance, you can examine the session statistic session cursor cache hits in the V$SYSSTAT view. This statistic counts the number of times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of the total parse call count for the session, then consider setting SESSION_CACHED_CURSORS to a larger value.

Once activated, Oracle maintains a cache of open cursors that correspond to SQL statements that are repeatedly executed by the application. When the application re-executes a cursor, it can be found in the cursor cache and a reparse is avoided.
In general, you should set SESSION_CACHED_CURSORS to 20. Using this parameter can impact memory utilization.

- Eliminate large anonymous PL/SQL block. Large anonymous PL/SQL blocks should be turned into small anonymous PL/SQL blocks that call packaged functions. The packages should be ‘kept’ in memory. To view candidates:

select sql_text
from v$sqlarea
where command_type=47 — command type for anonymous block
and length(sql_text) > 500;

- Pin large SQL and PL/SQL statements in memory using the DBMS_Shared_Pool: Objects causing a large number of other objects been flushed out from the shared pool are candidates to be pinned into the shared pool. You should pin objects you find immediatelly after the each restart of instance.Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error.  You can pin the object by DBMS_SHARED_POOL package like below
EXECUTE DBMS_SHARED_POOL.KEEP(OWNER.TRIGGER, ‘R’)

- Avoid closing of rapidly executed cursors.

- Sizing the shared pool : If the shared pool is too small, then extra resources are used to manage the limited amount of available space. This consumes CPU and latching resources, and causes contention. Optimally, the shared pool should be just large enough to cache frequently accessed objects. Having a significant amount of free memory in the shared pool is a waste of memory.

To see Current size of the shared pool;

SQL>column bytes format 999999999999999

    select bytes,bytes/1024/1024/1024 size_gb
    from   v$sgainfo
    where name='Shared Pool Size';

When sizing the shared pool, the goal is to ensure that SQL statements that will be executed multiple times are cached in the library cache, without allocating too much memory.

The statistic that shows the amount of reloading (that is, reparsing) of a previously cached SQL statement that was aged out of the cache is the RELOADS column in the V$LIBRARYCACHE view. In an application that reuses SQL effectively, on a system with an optimal shared pool size, the RELOADS statistic will have a value near zero.

The INVALIDATIONS column in V$LIBRARYCACHE view shows the number of times library cache data was invalidated and had to be reparsed. INVALIDATIONS should be near zero. This means SQL statements that could have been shared were invalidated by some operation (for example, a DDL). This statistic should be near zero on OLTP systems during peak loads.

SQL>SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
    FROM V$LIBRARYCACHE
    ORDER BY NAMESPACE;

High invalidations indicates that there is parsing problem with the namespace and high reloads indicates that there is a sizing problem which causes aging out.

Another key statistic is the amount of free memory in the shared pool at peak times. The amount of free memory can be queried from V$SGASTAT, looking at the free memory for the shared pool. Optimally, free memory should be as low as possible, without causing any reloads on the system.

To find the free memory in shared pool:

SQL>SELECT *
    FROM V$SGASTAT
    WHERE NAME = 'free memory'
    AND POOL = 'shared pool';

The output will be similar to the following:

POOL           NAME                      BYTES
----------- -------------------------- ----------
shared pool free memory                 4928280

If free memory is always available in the shared pool, then increasing the size of the pool offers little or no benefit. However, just because the shared pool is full does not necessarily mean that there is a problem. It may be indicative of a well-configured system.

Lastly, a broad indicator of library cache health is the library cache hit ratio. This value should be considered along with the other statistics discussed in this section and other data, such as the rate of hard parsing and whether there is any shared pool or library cache latch contention.

To calculate the library cache hit ratio, use the following formula:
Library Cache Hit Ratio = sum(pinhits) / sum(pins)

SQL>SELECT SUM(PINHITS)/SUM(PINS)
    FROM V$LIBRARYCACHE;

Low hit ratio is an indication of a sizing or caching problem..

You can use the sizing advices from the view v$shared_pool_advice. This view displays information about estimated parse time in the shared pool for different pool sizes and the sizes range from %10 to %200 of current shared pool size. This can give you idea for sizing SGA and obliquely shared pool by the help of ASMM.

SQL>select *
    from V$SHARED_POOL_ADVICE;

Dictionary cache stats

SELECT PARAMETER
, SUM(GETS)
, SUM(GETMISSES)
, 100*SUM(GETS – GETMISSES) / SUM(GETS) PCT_SUCC_GETS
, SUM(MODIFICATIONS) UPDATES
FROM V$ROWCACHE
WHERE GETS > 0
GROUP BY PARAMETER;

High updates with low pct_succ_gets can be a clue of performance problems when accessing that dictionary object. For frequently accessed dictionary caches, the ratio of total GETMISSES to total GETS should be less than 10% or 15%, depending on the application. If this ratio is higher and every previous control is OK then you should consider to increase the shared pool size

SQL>SELECT (SUM(GETS – GETMISSES – FIXED)) / SUM(GETS) "ROW CACHE"
    FROM V$ROWCACHE;

Low hit ratio is an indication of a sizing problem.

- Size the Reserved Area properly

An ORA-04031 error referencing a large failed requests indicates the Reserved Area is too fragmented.

SQL>col free_space       for 999,999,999,999 head "TOTAL FREE"
col avg_free_size    for 999,999,999,999 head "AVERAGE|CHUNK SIZE"
col free_count       for 999,999,999,999 head "COUNT"
col request_misses   for 999,999,999,999 head "REQUEST|MISSES"
col request_failures for 999,999,999,999 head "REQUEST|FAILURES"
col max_free_size    for 999,999,999,999 head "LARGEST CHUNK"

select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures
    from v$shared_pool_reserved;

TOTAL FREE AVERAGE CHUNK SIZE COUNT LARGEST CHUNK REQUEST MISSES REQUEST FAILURES
7,238,192 212,888         34 212,888     0             0

The reserved pool is small when:

   REQUEST_FAILURES > 0 (and increasing)
   The DBA should Increase shared_pool_reserved_size and shared_pool_size together.

Too much memory has been allocated to the reserved pool
   If REQUEST_MISS = 0 or not increasing
   FREE_MEMORY = > 50% of shared_pool_reserved_size minimum
   The DBA should Decrease shared_pool_reserved_size

You should also use hidden and unsupported parameter “_shared_pool_reserved_pct” to control reserved pool. This parameter controls the allocated percentage of shared pool for reserved pool. By default it is %5 of the shared pool and if you use ASMM for memory management you can set this value higher like 10 to allocate reserved pool dynamically. When you set the parameter you will see the shared_pool_reserved_size parameter will be adjusted to the new setting.

The parameter can not be modified when instance is started. You can use the query below to see the current value

SQL>select a.ksppinm "Parameter",
           b.ksppstvl "Session Value",
           c.ksppstvl "Instance Value"
    from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
    where a.indx = b.indx
      and a.indx = c.indx
      and a.ksppinm = '_shared_pool_reserved_pct';

Parameter Session Value Instance Value
_shared_pool_reserved_pct 10 10

Adding memory to the shared pool can also be problematic, particularly if large amounts of non-reusable SQL are being generated. The shared pool is managed by a number of link lists. As more and more SQL statements are added to the shared pool, the set of link lists gets longer, and it takes Oracle longer to traverse the link lists as it searches for parsed SQL. In this case, it is much better to tune your SQL using bind variables than to add memory.

 If it appears that you need to add memory, (particularly if the system has been operating for some time and you have established that the initial settings seem sufficient), it is best to first determine if there is inefficient SQL being executed that needs to be tuned. This is often SQL that has been added to the system recently. Modifying memory allocations should be the last possible solution.

Decrease the shared pool size when the application does not use bind variables and when cursors are not shared and reused. When cursors are not shared, a smaller shared pool will be more efficient as a larger shared pool causes Oracle to perform more searches to find free memory for new cursors.

Creating a Tablespace Using an ASM Disk Group as the Destination

There are different ways to create tablespaces using ASM disks. This topic will first look at creating an ASM tablespace, allowing the default ASM disk location to be used (as a result of having set the DB_CREATE_FILE_DEST parameter). It will then describe how to create a tablespace datafile by explicitly referencing the ASM diskgroup that it is supposed to be assigned to.
Creating Tablespaces Using Default ASM Assignments
If you have defined a default ASM location, you can use the create tablespace command to create a tablepsace that will have a file in the ASM diskgroup by default as seen in this example:

create tablespace test_rgf datafile size 100k;

You can see where Oracle put the datafile by querying the DBA_DATA_FILES view:

Select tablespace_name, file_name
from dba_data_files
Where tablespace_name='TEST_RGF';
TABLESPACE FILE_NAME
---------- ---------------------------------------------------------
TEST_RGF   +COOKED_DGROUP1/rob10gr2/datafile/test_rgf.256.613064385

Note in this example that Oracle went ahead and filled out the rest of the path, giving you a complete filename in the DBA_DATA_FILES view to work with. You can also see this new file in the ASM instance using the V$ASM_FILES view as seen here:

SQL> select group_number, file_number, type, blocks, bytes
  2  from v$asm_file;

GROUP_NUMBER FILE_NUMBER TYPE                     BLOCKS      BYTES
------------ ----------- -------------------- ---------- ----------
           1         256 DATAFILE                     14     114688

If you want to drop a tablespace that contains ASM files, you only need issue the drop tablespace command. Oracle will clean up all of the ASM datafiles associated with that tablespace.
You can have a mix of ASM datafiles and normal datafiles assigned to a tablespace as seen in this create tablespace statement:

Create tablespace part_asm_tbs
Datafile 'c:\oracle\oradata\rob10gr2\part_asm_tbs_01.dbf' size 10m,
'+COOKED_DGROUP1' size 100k;

You can see where the datafiles were created:

Select tablespace_name, file_name
from dba_data_files
Where tablespace_name='PART_ASM_TBS';

TABLESPACE_NAME FILE_NAME
--------------- ------------------------------------------------------------
PART_ASM_TBS    C:\ORACLE\ORADATA\ROB10GR2\PART_ASM_TBS_01.DBF
PART_ASM_TBS    +COOKED_DGROUP1/rob10gr2/datafile/part_asm_tbs.256.613066047

Note that in this case, if you drop the part_asm_tbs tablespace, only the ASM files related to that tablespace would be removed from the disk when you issue the drop tablespace command. In cases such as these, you need to make sure you include the including contents and datafiles parameter with the drop tablespace command.
Creating Tablespaces Referencing Specific ASM Disk Groups
There are going to be many times when you will not want to define a default ASM disk group to write all tablespaces too. In this case you will want to reference the specific ASM disk group that you want a datafile created in when you issue the create tablespace command. Here is an example:

create tablespace another_test
datafile '+COOKED_DGROUP1' size 100k;

You can see where Oracle put the datafile by querying the DBA_DATA_FILES view:

Select tablespace_name, file_name
from dba_data_files
Where tablespace_name='ANOTHER_TEST';

TABLESPACE_NAME FILE_NAME
--------------- ------------------------------------------------------------
ANOTHER_TEST    +COOKED_DGROUP1/rob10gr2/datafile/another_test.256.613065911

The create tablespace command comes with a number of different options when you are using cooked file systems and there is no reason you cannot use those options when you are using ASM file systems. For example, you can create a tablespace with autoextend enabled as seen here:

create tablespace another_test
datafile '+COOKED_DGROUP1' size 100k
autoextend on next 10m maxsize 300m;

If you want to create a tablespace using a template other than the default template, this is also possible as seen in this example:

create tablespace different_template
datafile '+COOKED_DGROUP1(alternate_template)';

Tablespace Maintenance When Using Tablespaces Referencing Specific ASM Disk Groups
Tablespace maintenance is basically unchanged when using ASM disks. For example, you can add a datafile with the alter tablespace command as you normally would:

Alter tablespace part_asm_tbs
Add datafile '+COOKED_DGROUP1' size 100k;

Most Frequently Used UNIX / Linux Commands (With Examples)

1. tar command examples

Create a new tar archive.

$ tar cvf archive_name.tar dirname/

Extract from an existing tar archive.

$ tar xvf archive_name.tar

View an existing tar archive.

$ tar tvf archive_name.tar

More tar examples: The Ultimate Tar Command Tutorial with 10 Practical Examples

2. grep command examples

Search for a given string in a file (case in-sensitive search).

$ grep -i "the" demo_file

Print the matched line, along with the 3 lines after it.

$ grep -A 3 -i "example" demo_text

Search for a given string in all files recursively

$ grep -r "ramesh" *

More grep examples: Get a Grip on the Grep! – 15 Practical Grep Command Examples

3. find command examples

Find files using file-name ( case in-sensitve find)

# find -iname "MyCProgram.c"

Execute commands on files found by the find command

$ find -iname "MyCProgram.c" -exec md5sum {} \;

Find all empty files in home directory

# find ~ -empty

More find examples: Mommy, I found it! — 15 Practical Linux Find Command Examples

4. ssh command examples

Login to remote host

ssh -l jsmith remotehost.example.com

Debug ssh client

ssh -v -l jsmith remotehost.example.com

Display ssh client version

$ ssh -V

OpenSSH_3.9p1, OpenSSL 0.9.7a Feb 19 2003

More ssh examples: 5 Basic Linux SSH Client Commands

5. sed command examples

When you copy a DOS file to Unix, you could find \r\n in the end of each line. This example converts the DOS file format to Unix file format using sed command.

$sed 's/.$//' filename

Print file content in reverse order

$ sed -n '1!G;h;$p' thegeekstuff.txt

Add line number for all non-empty-lines in a file

$ sed '/./=' thegeekstuff.txt | sed 'N; s/\n/ /'

More sed examples: Advanced Sed Substitution Examples

6. awk command examples

Remove duplicate lines using awk

$ awk '!($0 in array) { array[$0]; print }' temp

Print all lines from /etc/passwd that has the same uid and gid

$awk -F ':' '$3==$4' passwd.txt

Print only specific field from a file.

$ awk '{print $2,$5;}' employee.txt

More awk examples: 8 Powerful Awk Built-in Variables – FS, OFS, RS, ORS, NR, NF, FILENAME, FNR

7. vim command examples

Go to the 143rd line of file

$ vim +143 filename.txt

Go to the first match of the specified

$ vim +/search-term filename.txt

Open the file in read only mode.

$ vim -R /etc/passwd

More vim examples: How To Record and Play in Vim Editor

8. diff command examples

Ignore white space while comparing.

# diff -w name_list.txt name_list_new.txt

2c2,3

< John Doe --- > John M Doe

> Jason Bourne

More diff examples: Top 4 File Difference Tools on UNIX / Linux – Diff, Colordiff, Wdiff, Vimdiff

9. sort command examples

Sort a file in ascending order

$ sort names.txt

Sort a file in descending order

$ sort -r names.txt

Sort passwd file by 3rd field.

$ sort -t: -k 3n /etc/passwd | more

10. export command examples

To view oracle related environment variables.

$ export | grep ORACLE

declare -x ORACLE_BASE="/u01/app/oracle"

declare -x ORACLE_HOME="/u01/app/oracle/product/10.2.0"

declare -x ORACLE_SID="med"

declare -x ORACLE_TERM="xterm"

To export an environment variable:

$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0

11. xargs command examples

Copy all images to external hard-drive

# ls *.jpg | xargs -n1 -i cp {} /external-hard-drive/directory

Search all jpg images in the system and archive it.

# find / -name *.jpg -type f -print | xargs tar -cvzf images.tar.gz

Download all the URLs mentioned in the url-list.txt file

# cat url-list.txt | xargs wget –c

12. ls command examples

Display filesize in human readable format (e.g. KB, MB etc.,)

$ ls -lh

-rw-r----- 1 ramesh team-dev 8.9M Jun 12 15:27 arch-linux.txt.gz

Order Files Based on Last Modified Time (In Reverse Order) Using ls -ltr

$ ls -ltr

Visual Classification of Files With Special Characters Using ls -F

$ ls -F

More ls examples: Unix LS Command: 15 Practical Examples

13. pwd command

pwd is Print working directory. What else can be said about the good old pwd who has been printing the current directory name for ages.

14. cd command examples

Use “cd -” to toggle between the last two directories

Use “shopt -s cdspell” to automatically correct mistyped directory names on cd

More cd examples: 6 Awesome Linux cd command Hacks

15. gzip command examples

To create a *.gz compressed file:

$ gzip test.txt

To uncompress a *.gz file:

$ gzip -d test.txt.gz

Display compression ratio of the compressed file using gzip -l

$ gzip -l *.gz

         compressed        uncompressed  ratio uncompressed_name

              23709               97975  75.8% asp-patch-rpms.txt

16. bzip2 command examples

To create a *.bz2 compressed file:

$ bzip2 test.txt

To uncompress a *.bz2 file:

bzip2 -d test.txt.bz2

More bzip2 examples: BZ is Eazy! bzip2, bzgrep, bzcmp, bzdiff, bzcat, bzless, bzmore examples

17. unzip command examples

To extract a *.zip compressed file:

$ unzip test.zip

View the contents of *.zip file (Without unzipping it):

$ unzip -l jasper.zip

Archive:  jasper.zip

  Length     Date   Time    Name

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

    40995  11-30-98 23:50   META-INF/MANIFEST.MF

    32169  08-25-98 21:07   classes_

    15964  08-25-98 21:07   classes_names

    10542  08-25-98 21:07   classes_ncomp

18. shutdown command examples

Shutdown the system and turn the power off immediately.

# shutdown -h now

Shutdown the system after 10 minutes.

# shutdown -h +10

Reboot the system using shutdown command.

# shutdown -r now

Force the filesystem check during reboot.

# shutdown -Fr now

19. ftp command examples

Both ftp and secure ftp (sftp) has similar commands. To connect to a remote server and download multiple files, do the following.

$ ftp IP/hostname

ftp> mget *.html

To view the file names located on the remote server before downloading, mls ftp command as shown below.

ftp> mls *.html -

/ftptest/features.html

/ftptest/index.html

/ftptest/othertools.html

/ftptest/samplereport.html

/ftptest/usage.html

More ftp examples: FTP and SFTP Beginners Guide with 10 Examples

20. crontab command examples

View crontab entry for a specific user

# crontab -u john -l

Schedule a cron job every 10 minutes.

*/10 * * * * /home/ramesh/check-disk-space

More crontab examples: Linux Crontab: 15 Awesome Cron Job Examples

21. service command examples

Service command is used to run the system V init scripts. i.e Instead of calling the scripts located in the /etc/init.d/ directory with their full path, you can use the service command.

Check the status of a service:

# service ssh status

Check the steatus of all the services.

service --status-all

Restart a service.

# service ssh restart

22. ps command examples

ps command is used to display information about the processes that are running in the system.

While there are lot of arguments that could be passed to a ps command, following are some of the common ones.

To view current running processes.

$ ps -ef | more

To view current running processes in a tree structure. H option stands for process hierarchy.

$ ps -efH | more

23. free command examples

This command is used to display the free, used, swap memory available in the system.

Typical free command output. The output is displayed in bytes.

$ free

             total       used       free     shared    buffers     cached

Mem:       3566408    1580220    1986188          0     203988     902960

-/+ buffers/cache:     473272    3093136

Swap:      4000176          0    4000176

If you want to quickly check how many GB of RAM your system has use the -g option. -b option displays in bytes, -k in kilo bytes, -m in mega bytes.

$ free -g

             total       used       free     shared    buffers     cached

Mem:             3          1          1          0          0          0

-/+ buffers/cache:          0          2

Swap:            3          0          3

If you want to see a total memory ( including the swap), use the -t switch, which will display a total line as shown below.

ramesh@ramesh-laptop:~$ free -t

             total       used       free     shared    buffers     cached

Mem:       3566408    1592148    1974260          0     204260     912556

-/+ buffers/cache:     475332    3091076

Swap:      4000176          0    4000176

Total:     7566584    1592148    5974436

24. top command examples

top command displays the top processes in the system ( by default sorted by cpu usage ). To sort top output by any column, Press O (upper-case O) , which will display all the possible columns that you can sort by as shown below.

Current Sort Field:  P  for window 1:Def

Select sort field via field letter, type any other key to return

  a: PID        = Process Id              v: nDRT       = Dirty Pages count

  d: UID        = User Id                 y: WCHAN      = Sleeping in Function

  e: USER       = User Name               z: Flags      = Task Flags

  ........

To displays only the processes that belong to a particular user use -u option. The following will show only the top processes that belongs to oracle user.

$ top -u oracle

More top examples: Can You Top This? 15 Practical Linux Top Command Examples

25. df command examples

Displays the file system disk space usage. By default df -k displays output in bytes.

$ df -k

Filesystem           1K-blocks      Used Available Use% Mounted on

/dev/sda1             29530400   3233104  24797232  12% /

/dev/sda2            120367992  50171596  64082060  44% /home

df -h displays output in human readable form. i.e size will be displayed in GB’s.

ramesh@ramesh-laptop:~$ df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda1              29G  3.1G   24G  12% /

/dev/sda2             115G   48G   62G  44% /home

Use -T option to display what type of file system.

ramesh@ramesh-laptop:~$ df -T

Filesystem    Type   1K-blocks      Used Available Use% Mounted on

/dev/sda1     ext4    29530400   3233120  24797216  12% /

/dev/sda2     ext4   120367992  50171596  64082060  44% /home

26. kill command examples

Use kill command to terminate a process. First get the process id using ps -ef command, then use kill -9 to kill the running Linux process as shown below. You can also use killall, pkill, xkill to terminate a unix process.

$ ps -ef | grep vim

ramesh    7243  7222  9 22:43 pts/2    00:00:00 vim

$ kill -9 7243

More kill examples: 4 Ways to Kill a Process – kill, killall, pkill, xkill

27. rm command examples

Get confirmation before removing the file.

$ rm -i filename.txt

It is very useful while giving shell metacharacters in the file name argument.

Print the filename and get confirmation before removing the file.

$ rm -i file*

Following example recursively removes all files and directories under the example directory. This also removes the example directory itself.

$ rm -r example

28. cp command examples

Copy file1 to file2 preserving the mode, ownership and timestamp.

$ cp -p file1 file2

Copy file1 to file2. if file2 exists prompt for confirmation before overwritting it.

$ cp -i file1 file2

29. mv command examples

Rename file1 to file2. if file2 exists prompt for confirmation before overwritting it.

$ mv -i file1 file2

Note: mv -f is just the opposite, which will overwrite file2 without prompting.

mv -v will print what is happening during file rename, which is useful while specifying shell metacharacters in the file name argument.

$ mv -v file1 file2

30. cat command examples

You can view multiple files at the same time. Following example prints the content of file1 followed by file2 to stdout.

$ cat file1 file2

While displaying the file, following cat -n command will prepend the line number to each line of the output.

$ cat -n /etc/logrotate.conf

    1    /var/log/btmp {

    2        missingok

    3        monthly

    4        create 0660 root utmp

    5        rotate 1

    6    }

31. mount command examples

To mount a file system, you should first create a directory and mount it as shown below.

# mkdir /u01

# mount /dev/sdb1 /u01

You can also add this to the fstab for automatic mounting. i.e Anytime system is restarted, the filesystem will be mounted.

/dev/sdb1 /u01 ext2 defaults 0 2

32. chmod command examples

chmod command is used to change the permissions for a file or directory.

Give full access to user and group (i.e read, write and execute ) on a specific file.

$ chmod ug+rwx file.txt

Revoke all access for the group (i.e read, write and execute ) on a specific file.

$ chmod g-rwx file.txt

Apply the file permissions recursively to all the files in the sub-directories.

$ chmod -R ug+rwx file.txt

More chmod examples: 7 Chmod Command Examples for Beginners

33. chown command examples

chown command is used to change the owner and group of a file. \

To change owner to oracle and group to db on a file. i.e Change both owner and group at the same time.

$ chown oracle:dba dbora.sh

Use -R to change the ownership recursively.

$ chown -R oracle:dba /home/oracle

34. passwd command examples

Change your password from command line using passwd. This will prompt for the old password followed by the new password.

$ passwd

Super user can use passwd command to reset others password. This will not prompt for current password of the user.

# passwd USERNAME

Remove password for a specific user. Root user can disable password for a specific user. Once the password is disabled, the user can login without entering the password.

# passwd -d USERNAME

35. mkdir command examples

Following example creates a directory called temp under your home directory.

$ mkdir ~/temp

Create nested directories using one mkdir command. If any of these directories exist already, it will not display any error. If any of these directories doesn’t exist, it will create them.

$ mkdir -p dir1/dir2/dir3/dir4/

36. ifconfig command examples

Use ifconfig command to view or configure a network interface on the Linux system.

View all the interfaces along with status.

$ ifconfig -a

Start or stop a specific interface using up and down command as shown below.

$ ifconfig eth0 up

$ ifconfig eth0 down

More ifconfig examples: Ifconfig: 7 Examples To Configure Network Interface

37. uname command examples

Uname command displays important information about the system such as — Kernel name, Host name, Kernel release number,
Processor type, etc.,

Sample uname output from a Ubuntu laptop is shown below.

$ uname -a

Linux john-laptop 2.6.32-24-generic #41-Ubuntu SMP Thu Aug 19 01:12:52 UTC 2010 i686 GNU/Linux

38. whereis command examples

When you want to find out where a specific Unix command exists (for example, where does ls command exists?), you can execute the following command.

$ whereis ls

ls: /bin/ls /usr/share/man/man1/ls.1.gz /usr/share/man/man1p/ls.1p.gz

When you want to search an executable from a path other than the whereis default path, you can use -B option and give path as argument to it. This searches for the executable lsmk in the /tmp directory, and displays it, if it is available.

$ whereis -u -B /tmp -f lsmk

lsmk: /tmp/lsmk

39. whatis command examples

Whatis command displays a single line description about a command.

$ whatis ls

ls               (1)  - list directory contents

$ whatis ifconfig

ifconfig (8)         - configure a network interface

40. locate command examples

Using locate command you can quickly search for the location of a specific file (or group of files). Locate command uses the database created by updatedb.

The example below shows all files in the system that contains the word crontab in it.

$ locate crontab

/etc/anacrontab

/etc/crontab

/usr/bin/crontab

/usr/share/doc/cron/examples/crontab2english.pl.gz

/usr/share/man/man1/crontab.1.gz

/usr/share/man/man5/anacrontab.5.gz

/usr/share/man/man5/crontab.5.gz

/usr/share/vim/vim72/syntax/crontab.vim

41. man command examples

Display the man page of a specific command.

$ man crontab

When a man page for a command is located under more than one section, you can view the man page for that command from a specific section as shown below.

$ man SECTION-NUMBER commandname

Following 8 sections are available in the man page.

1.    General commands

2.    System calls

3.    C library functions

4.    Special files (usually devices, those found in /dev) and drivers

5.    File formats and conventions

6.    Games and screensavers

7.    Miscellaneous

8.    System administration commands and daemons

For example, when you do whatis crontab, you’ll notice that crontab has two man pages (section 1 and section 5). To view section 5 of crontab man page, do the following.

$ whatis crontab

crontab (1)          - maintain crontab files for individual users (V3)

crontab (5)          - tables for driving cron

$ man 5 crontab

42. tail command examples

Print the last 10 lines of a file by default.

$ tail filename.txt

Print N number of lines from the file named filename.txt

$ tail -n N filename.txt

View the content of the file in real time using tail -f. This is useful to view the log files, that keeps growing. The command can be terminated using CTRL-C.

$ tail -f log-file

More tail examples: 3 Methods To View tail -f output of Multiple Log Files in One Terminal

43. less command examples

less is very efficient while viewing huge log files, as it doesn’t need to load the full file while opening.

$ less huge-log-file.log

One you open a file using less command, following two keys are very helpful.

CTRL+F – forward one window

CTRL+B – backward one window

More less examples: Unix Less Command: 10 Tips for Effective Navigation

44. su command examples

Switch to a different user account using su command. Super user can switch to any other user without entering their password.

$ su - USERNAME

Execute a single command from a different account name. In the following example, john can execute the ls command as raj username. Once the command is executed, it will come back to john’s account.

[john@dev-server]$ su - raj -c 'ls'

[john@dev-server]$

Login to a specified user account, and execute the specified shell instead of the default shell.

$ su -s 'SHELLNAME' USERNAME

45. mysql command examples

mysql is probably the most widely used open source database on Linux. Even if you don’t run a mysql database on your server, you might end-up using the mysql command ( client ) to connect to a mysql database running on the remote server.

To connect to a remote mysql database. This will prompt for a password.

$ mysql -u root -p -h 192.168.1.2

To connect to a local mysql database.

$ mysql -u root -p

If you want to specify the mysql root password in the command line itself, enter it immediately after -p (without any space).

46. yum command examples

To install apache using yum.

$ yum install httpd

To upgrade apache using yum.

$ yum update httpd

To uninstall/remove apache using yum.

$ yum remove httpd

47. rpm command examples

To install apache using rpm.

# rpm -ivh httpd-2.2.3-22.0.1.el5.i386.rpm

To upgrade apache using rpm.

# rpm -uvh httpd-2.2.3-22.0.1.el5.i386.rpm

To uninstall/remove apache using rpm.

# rpm -ev httpd

More rpm examples: RPM Command: 15 Examples to Install, Uninstall, Upgrade, Query RPM Packages

48. ping command examples

Ping a remote host by sending only 5 packets.

$ ping -c 5 gmail.com

More ping examples: Ping Tutorial: 15 Effective Ping Command Examples

49. date command examples

Set the system date:

# date -s "01/31/2010 23:59:53"

Once you’ve changed the system date, you should syncronize the hardware clock with the system date as shown below.