SQL | ADVANCE SQL | PLSQL | DBA | Linux

Tuesday, 19 September 2017

SQL commands or sub-languages of SQL

SQL (Struture Query Language) :-SQL Commands are used to communicate with the database to perform specific tasks with data.

SQL commands are grouped into four major categories depending on their functionality

    1) Data Definition Language (DDL)
    2) Data Manipulation Language (DML)
    3) Transaction Control Language (TCL)
    4) Data Control Language (DCL)
    5) Data Query Language (DQL)

Data Definition Language (DDL) :-DDL commands are used to create, ALTER, drop, rename of a database objects like Table,view,sequence,index etc .
                                These commands works on structure. Below are commands under this category
1) CREATE
2) ALTER
3) DROP
4) RENAME
5) TRUNCATE

CREATE:- CREATE command is used to create Database objects like table,view,sequence, index etc.

Example:-
Create table STUDENT (regno NUMBER,name VARCHAR2(20),dob DATE,course VARCHAR2(10));

ALTER:-ALTER command is used for changing the existing structure of Database objects.

Example:-
ALTER table STUDENT ADD (address VARCHAR2(50));
ALTER table STUDENT ADD (address1 VARCHAR2(50),address2 VARCHAR2(50));
ALTER table STUDENT ADD PRIMARY KEY(regno);
ALTER table STUDENT ADD CONSTRAINT pk_regno PRIMARY KEY(regno);
ALTER table STUDENT MODIFY (name VARCHAR2(30));
ALTER table STUDENT RENAME COLUMN address TO address3;
ALTER table STUDENT RENAME CONSTRAINT pk_regno TO pk_regno1;
ALTER table STUDENT ENABLE CONSTRAINT pk_regno1;
ALTER table STUDENT DISABLE CONSTRAINT pk_regno1;
ALTER table STUDENT DROP COLUMN address2;
ALTER table STUDENT DROP CONSTRAINT pk_regno1;

DROP:-DROP command is used to drop the Database objects.

Example:-
DROP table STUDENT;
DROP View myview;
DROP SEQUENCE seq1;
     
TRUNCATE:-TRUNCATE command is used for deleting complete data from an existing table.

Example:-
TRUNCATE table STUDENT;
TRUNCATE table STUDENT DROP STORAGE;
TRUNCATE table STUDENT REUSE STORAGE;

RENAME:-RENAME command is used to rename a table

Example:-
RENAME STUDENT TO STUDENT1;
     
Data Manipulation Language (DML):-DML commands work on records in a table. These are basic operations we perform on data such as inserting new records, deleting unnecessary records, and updating existing records.Below are the commands are under this category
1) UPDATE
2) DELETE
3) INSERT

UPDATE:-UPDATE command is used to update existing records in a table.

Example:-
UPDATE student SET course='MCA' WHERE regno=101;

INSERT:-INSERT command is used to insert new records in a table.

Example:-

DELETE:-DELETE command is used to delete records in a table.

Example:-
DELETE FROM student WHERE regno=101;

Transaction Control Language (TCL):- The commands of SQL that are used to control the transactions made against the database.Below are the commands are under this category
1) COMMIT
2) ROLLBACK
3) SAVEPOINT

COMMIT:-COMMIT command is used for modify/insert/update records makes permanent.

Example:-
        COMMIT;

ROLLBACK:-Rollback is used to undo the changes made by any command but only before a commit is done

Example:-
        ROLLBACK;

SAVEPOINT:-SAVEPOINT command is used to creates points within groups of transactions in which to ROLLBACK.

Example:-
        SAVEPOINT ABC;

Data Control Language (DCL):-These commands of SQL are used to give the permission or revoke the permission on System/User's objects to user/role in the database

GRANT:-All users access previleges to database.

Example:-
GRANT CREATE TABLE FROM SCOTT;
GRANT CREATE TABLE FROM SCOTT WITH ADMIN OPTION;
GRANT SELECT ON emp to HR;
GRANT SELECT ON emp to HR WITH GRANT OPTION;

REVOKE:-Withdraw users access previleges given by using the Grant command.

Example:-
REVOKE CREATE TABLE FROM SCOTT;
REVOKE SELECT ON emp FROM SCOTT;

Data Query Language (DCL):-These commands are used to select the data from database tables,view,synonyms and sequence etc.

SELECT:-SELECT command is used to select data from a database.

Example:-
SELECT * FROM emp;
SELECT empno,ename,job,sal from emp;
SELECT empno,ename,job,sal from emp WHERE sal>2000;

activate administrator account on window

Follow the below steps to activate administrator account on Window 7 or Window 8

1) Click on Start button and Write "cmd" on Search Option


2) Write click on "cmd" Application and select "Run as administrator"


3) Run below command on command prompt



net user administrator /active:yes

4) Restart the Window system "Administrator Account" will be enabled


Monday, 18 September 2017

Enabling the telnet command line utility in Window OS

Below are the steps to enabling the telnet command line utilities in Window7, 8

1) Click on "Start" and select "Control Panel"
 
Below is the Control Panel Screen and Select "Programs and Features"

Below is the "Programs and Features" Screen and select "Turn Windows features on or off"

Below is the "Turn Windows features on or off" screen

Scroll slide bar and check the check button for telnet option

Click on OK and now you can check telnet command utility will work.

If still telnet utility is not working then restart the system, It will work

Sunday, 17 September 2017

checking the connectivity from local machine to server

Below are the methods for checking the connectivity of any server from your local machine
    1) ping
    2) traceroute
    3) telnet
   
ping :- Below is the sytax for checking the ping of sever from your local machine    

ping servers-ip

Example :- if server-ip is 192.168.12.101 then from terminal you have check below command

ping 192.168.12.101
a) Output will be like below if server is accessible :-

PING 192.168.12.101 56(84) bytes of data.
64 bytes from 192.168.12.101: icmp_req=1 ttl=64 time=5.94 ms

--- 192.168.12.101 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 5.946/5.946/5.946/0.000 ms

b) Output will be like below if server is not accessible :-

$ping 192.168.12.101

Pinging 192.168.12.101 with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.

Ping statistics for 192.168.12.101:
    Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

traceroute:- Below is the for checking the traceroute from your local machine

tracert servers-ip

Example :- if server-ip is 192.168.12.101 then from terminal you have check below command

$traceroute 192.168.12.101

a) Output will be like below if server is accessible :-

Tracing route to 192.168.12.101 over a maximum of 30 hops

  1     3 ms     3 ms     2 ms  smbhost [192.168.10.12]
  2     3 ms     3 ms     2 ms  smbhost [192.168.122.1]

b) Output will be like below if server is not accessible :-

Tracing route to 192.168.12.101 over a maximum of 30 hops

  1     3 ms     3 ms     2 ms  smbhost [192.168.122.1]
  2     *        *        *     Request timed out.
  3     *        *        *     Request timed out.
  4     *        *        *     Request timed out.
  5     *        *        *     Request timed out.
  6     *        *        *     Request timed out.
  7     *        *        *     Request timed out.
  8     *        *        *     Request timed out.
  9     *        *        *     Request timed out.
 10     *        *        *     Request timed out.


telnet:-Below is the syntax for telnet command for checking the connectivity from your local machine

telnet server-ip port

Example :- if server-ip is 192.168.12.101 and port is 922 then from terminal you have check below command

$telnet 192.168.12.101 922   

a) Output will be like below if server is accessible :-

$telnet 192.168.12.101 922
Trying 192.168.12.101...
Connected to 192.168.12.101.
Escape character is '^]'.
SSH-2.0-OpenSSH_5.3

b) Output will be like below if server is not accessible :-

$telnet 192.168.12.101 922
Connecting To 192.168.12.101...Could not open connection to the host, on port 922: Connect failed
$

Saturday, 2 September 2017

DML operation in Oracle SQL

--To see the how many tables available in your schema
select * from tab;
--To see the data of table "DEPT"
select * from DEPT;

Drop Table Command:-
--To drop table "EMP22" without purge (Table will linked with recyclebin)
drop table EMP22;
--To see the recyclebin
select * from recyclebin;
--To purge the recyclebin
purge recyclebin;
--To drop table "EMP22" with purge (Table will drop permanently)
drop table EMP_CUR purge;

Create Table Command :-
1) Create table without any constraint:-
create table student3 (regno number(5),name varchar2(20),dob date,course varchar2(10));
--To see the table structure
desc student3;
describe student3;
--To see the table data
select * from student3;
2) Create table with constraint:-
create table student4 (regno number(5) primary key,name varchar2(20) ,dob date,course varchar2(10));
create table student5 (regno number(5) primary key,name varchar2(20) not null ,dob date not null,course varchar2(10));

DML (Data Manipulation Language):-Only insert,update and delete commands are the DML commands. Rollback is possible for DML commands if commit statement is not issued.
Insert Command:-
1) Inserting data in all column:-
insert into student3  values (101, 'Suresh', '12-JUL-1987','BCA');
insert into student3  values (102, 'Mahesh', '10-JAN-1985','MCA');
insert into student3  values (103, 'Ganesh', '12-APR-1982','BCA');
insert into student3  values (104, 'Rajesh', '10-JUN-1980','MCA');
insert into student3  values (105, 'Ratnesh', '10-MAY-1980','MCA');
select * from student3;Note :- inserted records will be available for current session only till the commit command is not issued means inserted data will not be displayed in another session till the commit is not issued in this session.

commit;
grant select on student3 to hr;

2) Inserting data into selected column:-
insert into student3(regno,name,dob,course)  values (106, 'Rohit', '18-FEB-1980','BCA');
insert into student3(regno,name)  values (107, 'Mohit');

select * from student3;
alter table student3 modify (dob date not null,course varchar2(10) not null);
--SQL Error: ORA-02296: cannot enable (SCOTT.) - null values found
Note:- You can not add not null constraint in column where null data available on that column

alter table student3 modify (regno number(5) not null,name varchar2(20) not null);

insert into student3(dob,course)  values ('11-JUL-1985', 'MCA');
--SQL Error: ORA-01400: cannot insert NULL into ("SCOTT"."STUDENT3"."REGNO")
Note:-Record can be inserted only when all not null columns have taken for insert values

3) Inserting data from reference:-
insert into student3 values (&regno,'&name','&dob','&course');

4) Inserting data into a table from another table:-
insert into student3 select * from student;
insert into student3 (REGNO,NAME,dob,course) select id,name,dob,course from student;
insert into student select * from student3 where regno<106;
commit;
Note:- 1) Datatype of columns should be sequentially matched in source and target tables
       2) Number of Columns should be matched in  source and target tables

select * from student3 where regno<106;
select * from student;

Delete Command :-By delete command, we can delete records .
1) Delete all records:-
delete from student;
delete student;
rollback;
Note:- If no where clause is mentioned with delete then all records will be deleted
2) Delete selected records :-
delete student where id<104;
Note:- Before going to delete, you have to verify records by select command which need to be delete
select * from student where id<104;


Update Command:-By "update" statement we can modify the existing data in a table.
1) Update all records:-If no where clause is there then all recoreds will be updated
a) one column data update:-
update student set name='Neha';
rollback;
b) more than one columns data update :-
update student set name='Neha',id=113;


2) Update selected record:-If where clause is there then matched records will be updated
select * from student;
select rowid,id,name,dob,course from student where id=113 and NAME!='Suresh';
select rowid,a.* from student a --set name='Neena'
where rowid='AAATEJAAEAAAUlLAAF';
Note:-rowid is a pscudo column that is not physical stored in table data.This denote the physical
      address of record. 
a) one column data update:-
update student set name='Neena' where rowid='AAATEJAAEAAAUlLAAF';
update student set name='Neeshu' where rowid='AAATEJAAEAAAUlLAAH';
Note :-Before going to update please verify the records by selecting data
       1) place "select * from" in place of update
       2) comment "set" clause      
select rowid,a.id,a.name from student a --set name='Neena'
where rowid='AAATEJAAEAAAUlLAAF';

b) more than one columns data update :-
update student set name='Neha',id=113 where rowid='AAATEJAAEAAAUlNAAA';

Order By Clause:- By "order by" clause we can sort data of any column/columns .
1) Sorting data of 1 column :-
select * from student order by id ;
Note:-If we do not mention "asc" or "desc" then system will sort in ascending order of column data
select * from student order by id desc;
1) Sorting data of more than one columns :-
select * from student order by id desc ,dob ;
select * from student order by id desc ,dob desc;
select * from student order by  id desc,name desc,dob desc,course desc;

Sunday, 20 August 2017

insert data into table

INSERT statement:-This is used for inserting new records into table. Below are the ways to insert new record
    1) INSERT INTO Statement
    2) INSERT ALL Statement
    3) Multiple Row INSERT into TABLE Statement (By reference of column values)
    4) INSERT Data only selected COLUMNS
    5) INSERT INTO SELECT Statement (Insert data from another table)


Below table is created for understanding examples :-

create table student (id number,name varchar2(20),dob date,course varchar2(10));

1) INSERT INTO statement:-Below is the syntax for same.
Syntax :-INSERT INTO <table_name> VALUES (value1, value2, value3, ...);

Note:-When inserting data into all columns of table then no need to specify the column names.
      Values should be in sequence of columns

Example:-

SQL> INSERT INTO STUDENT VALUES (101, 'Suresh', '12-JUL-1993', 'MCA');

1 row created.

If you don't know the sequence of columns the you can use below synatx
Syntax :-INSERT INTO <table_name>(column_name1, column_name2, ...) VALUES (value1, value2, ...);

Note:- In above syntax you have to put values on sequence what you have mentioned column name before "values" clause

Example:-

SQL> INSERT INTO STUDENT (id,name,dob,course) VALUES (101, 'Suresh', '12-JUL-1993', 'MCA');

1 row created.
 

2) INSERT ALL statement:-INSERT ALL statement is used to insert more then one records into table by one statement.
Syntax :-

INSERT ALL
    INTO <table_name>  (column1, column2, ...)  VALUES (value1, value2, ...)
    INTO <table_name>  (
column1, column2, ...)  VALUES (value1, value2, ...)
    INTO <table_name>  (
column1, column2, ...)  VALUES (value1, value2, ...)
    ....
    SELECT * FROM dual;
   
Example :-

SQL> INSERT ALL
  INSERT INTO STUDENT (id,name,dob,course) VALUES (101, 'Suresh', '12-JUL-1993', 'MCA')
  INSERT INTO STUDENT (id,name,dob,course) VALUES (102, 'Mahesh', '18-AUG-1991', 'BCA')
  INSERT INTO STUDENT (id,name,dob,course) VALUES (103, 'Dinesh', '23-SEP-1990', 'BCA')
  INSERT INTO STUDENT (id,name,dob,course) VALUES (104, 'Ganesh', '25-APR-1993', 'MCA')
SELECT * FROM dual;

4 rows created.

3) SQL Multiple Row Insert into Table Statements :-By taking reference for column values, we can INSERT multiple rows. "&" sign is used to take a value from keyboard with column name.
 

Note:-If you want to add another record you just execute forward slash (/) on SQL*PLUS to again execute last statement automatically and you can insert new data again.

Note :-In SQL*PLUS Forward Slash "/" is used to execute last statement.

Syntax:-
SQL> INSERT INTO STUDENT (id,name,dob,course) VALUES (&id,'&name','&dob','&course');
Enter value for id: 101
Enter value for name: Ganesh
Enter value for dob: 12-AUG-1993
Enter value for course: MCA
old   1: INSERT INTO STUDENT (id,name,dob,course) VALUES (&id,'&name','&dob','&course')
new   1: INSERT INTO STUDENT (id,name,dob,course) VALUES (101,'Ganesh','12-AUG-1993','MCA')

1 row created.

SQL> /
Enter value for id: 102
Enter value for name: Mahesh
Enter value for dob: 18-APR-1990
Enter value for course: BCA
old   1: INSERT INTO STUDENT (id,name,dob,course) VALUES (&id,'&name','&dob','&course')
new   1: INSERT INTO STUDENT (id,name,dob,course) VALUES (102,'Mahesh','18-APR-1990','BCA')

1 row created.

SQL>

4) Insert Data in selected COLUMNS :-By this way, we can insert data into selected columns only. below is the syntax

Syntax:-INSERT INTO <Table_Name> (column_name1, ...) VALUES (value1,...);

Note:-All null-able column you can left. but all mandatory columns have to take on insert statement

Example:-

SQL> INSERT INTO STUDENT (id,name,course) VALUES (109, 'Gyanesh', 'MCA');

1 row created.


5) INSERT INTO SELECT Statement:-By this way, we can insert data into table which is selected from another table.

Syntax:- INSERT INTO <table_name> (column_name1,column_name2,...)
    SELECT column_name1, column_name1 ... FROM <another_table_name>
    WHERE condition;

Example:-

SQL> create table student1 (id number,name varchar2(20),dob date,course varchar2(10),address varchar2(50));

Table created.

SQL> insert into student1 (id,name,dob,course) select id,name,dob,course from student;

2 rows created.

SQL>

drop oracle database

DROP database:-To drop the Database,Database must be mounted in EXCLUSIVE restrict mode and not open for session.

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive restrict ;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             671089544 bytes
Database Buffers          390070272 bytes
Redo Buffers                5517312 bytes
Database mounted.

SQL>  drop database;

Database dropped.

Friday, 21 July 2017

clear terminal history in linux

Below command is useful to delete terminal history

history -c 

Also you can use below command for complete deletion of terminal history

cat /dev/null >~/.bash_history && history -c && exit

To delete uses of USB and other external h/w logs, login with root user and do the below

cd /var/log

rm -f messages-*


Wednesday, 19 July 2017

rman backup validate and restore validate

RMAN BACKUP VALIDATE and RESTORE VALIDATE :-The RMAN BACKUP VALIDATE and RESTORE VALIDATE commands are useful for recovery plan testing.

BACKUP VALIDATE:-BACKUP VALIDATE command reads all of the specified files but does not produce any output files. All of the data blocks in the input files are validated.

RESTORE VALIDATE:-RESTORE VALIDATE command reads all of the backup files that would be needed to restore the specified objects, but the objects are not actually restored to disk. All of the data blocks in the backup files are validated. RESTORE VALIDATE automatically chooses which backup files to restore from. For example, the command RESTORE VALIDATE DATABASE ensures that every file in the database, a valid backup exists, can be read, and contains valid data


Examples:-
1) Check for physical corruption of files to be backed up.
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

2) Check for physical and logical corruption of files to be backed up.
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

3) Check for physical corruption of files to be restored.
RESTORE VALIDATE DATABASE;

4) Check for physical and logical corruption of files to be restored.
RESTORE VALIDATE CHECK LOGICAL DATABASE;
RESTORE CONTROLFILE VALIDATE CHECK LOGICAL;
RESTORE DATABASE VALIDATE CHECK LOGICAL;
RESTORE ARCHIVELOG FROM TIME ‘SYSDATE-1’ UNTIL TIME ‘SYSDATE’ validate;

RESTORE CONTROLFILE VALIDATE CHECK LOGICAL;

Friday, 14 July 2017

shell script examples

1) Script to print "Hello World"

cat>example1.sh
#!/bin/bash
echo "Hello World"

ctrl+d (To save and exit)

chmod a+x example1.sh (To give the execute permission to all)

bash example1.sh  (To execute/run the script)
bash -x example1.sh (To execute/run the script in debug mode)

2) Script to print "Hello World" if logged in user is oracle else print Bye

cat>example2.sh
#!/bin/bash
if [[ $USER = "oracle" ]] then #For Testing string value we use [[ ]]
    echo "Hello World"
else
    echo "Bye"
fi

ctrl+d
chmod a+x example2.sh

bash -x example2.sh

3) Script to print "Hello World" if logged in user is not oracle else print Bye

cat>example3.sh
#!/bin/bash
if [[ $USER != "oracle" ]] then #For Testing string value we use [[ ]]
    echo "Hello World"
else
    echo "Bye"
fi

ctrl+d
chmod a+x example3.sh

bash -x example3.sh

4) Script to print "Hello World" if logged in user is not oracle else print Bye
   also script should run on debug mode

cat>example4.sh
#!/bin/bash -x    #Here "-x" is for debug mode
if [[ $USER != "oracle" ]] then #For Testing string value we use [[ ]]
    echo "Hello World"
else
    echo "Bye"
fi

ctrl+d
chmod a+x example4.sh

5) Print number 10 to 1 by while loop in a line and print "Hello All!!" at the end 
cat>while.sh
count=10
while (( count > 0 ))
do
    echo -e "$count \c"
sleep 1   
(( count -- ))
done
echo -e "\n\n Hello All!!"

ctrl+d
chmod a+x  while.sh
 
6) Write a shell script to take a argument as file, directory or links and list the all files, directories or links accordingly (as per argument) of that directory
cat>example6.sh
#!/bin/bash
dir=`pwd`
if [[ $1 = "directory" ]]
then
    find $dir -maxdepth 1 -type d
elif [[ $1 = "link" ]]
then
    find $dir -maxdepth 1 -type l
elif [[ $1 = "file" ]]
then
    find $dir -maxdepth 1 -type f
else
    echo "Usages : $0 file | directory |link"
fi

ctrl+d
chmod a+x example6.sh

7) Write a shell script for factorial of a number
cat>fact.sh
#!/bin/sh
factorial()
{
  if [ "$1" -gt "1" ]; then
    i=`expr $1 - 1`
    j=`factorial $i`
    k=`expr $1 \* $j`
    echo $k
  else
    echo 1
  fi
}

echo "Enter a number for factorial :"
read x
y=`factorial $x`
echo "Factorial of $x is :- "$y

ctrl+d
chmod a+x fact.sh

step by step upgrade Oracle 10g to 11g

Below are steps need to perform to upgrade Oracle 10g to 11g :-
1) Create a Directory structure for Oracle Home for 11g
 mkdir -p /u01/app/oracle/oracle/product/11.1.0
2) Run the run Installer for software installation of 11g
./runInstaller
3) Pre-Upgrade Utility (Login to 10g Database and run the below sql)
 SQL>@/u01/app/oracle/oracle/product/11.1.0//rdbms/admin/utlu111i.sql
Note:-This sql will give the output in the form of recommendations to be implemented before starting the upgrade.
4) Executing the recommended steps
5) run the pre-upgrade utility again

Note:-Now no critical warning should be there
6) Set the ORACLE_HOME, ORACLE_SID,PATH for oracle 11g
7) connect sys as sysdba for database 11g (idle instance will started) and run the upgrade command

SQL>startup upgrade
8) After completion run the catalog upgrade sql
 SQL>@catupgrd.sql
Note:-Once the upgrades finishes. It will shut down the database automatically.
Login again as sysdba and startup in normal mode.Check the dba_registry for the components and its status
9) Post-Upgrade StepsOnce the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.
SQL> STARTUP
10) Run utlu111s.sql, catuppst.sql and utlrp.sql sqls to complete upgrade and validate objects
SQL> @?/rdbms/admin/utlu111s.sql
SQL> @?/rdbms/admin/catuppst.sql
SQL> select count(*) from dba_objects where status = ‘INVALID’;
SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status = ‘INVALID’;

Thursday, 6 July 2017

tar command examples in Linux

tar command:- tar command is used to archive and un-archive of files and directories  
 
1) Create a archive (tar file) for directory
tar cvf archive.tar dir_name/

Note:- Here options are below
    c – create a new archive
    v – verbosely list files which are processed
    f – following is the archive file name
                (OR)

tar cvzf archive.tar.gz dir_name/

Note:- "z" option is for creating zip file
                (OR)
               
tar cvfj archive.tar.bz2 dir_name/               

Note:- "j" option is for creating bz2 file

2)  Extract the archive (tar file)
tar xvf archive.tar

Note:- Here "x" option is for extract file from archive
                     (OR)
tar xvfz archive.tar.gz
                      (OR) 
tar xvfj archive.tar.bz2

3) View the tar file content without extracting the tar file
tar tvf archive.tar
tar tvfz archive.tar.gz
tar tvfj archive.tar.bz2
         
4) Extract a single file from tar file                      
tar xvf archive.tar /path/file_name
tar xvfz archive.tar.gz /path/file_name
tar xvfj archive.tar.bz2 /path/file_name

Note:- here file_name is the name of file which need to extrct and
       /path/ is the complete file path which is available in tar file

5) Extract a single directory from tar file
tar xvf archive.tar /path/dir_name
tar xvfz archive.tar.gz /path/dir_name
tar xvfj archive.tar.bz2 /path/dir_name

Note:- here dir_name is the name of directory which need to extract from tar file

6) Extract multiple directories from tar file
tar xvf archive.tar /path/dir1 /path/dir2 /path/dir3
tar xvfz archive.tar.gz /path/dir1 /path/dir2 /path/dir3
tar xvfj archive.tar.bz2 /path/dir1 /path/dir2 /path/dir3

Note:- Here dir1,dir2 and dir3 are the directories which need to extract from tar file

7) Extract multiple files through RE string from tar file
tar xvf archive.tar --wildcards '*.txt'

8) Add new file or directory in existing archive (tar file)
tar rvf archive.tar abc.txt
tar rvf archive.tar xyz/

Note:-"r" option provide the flexibility to add new file or directory

xargs command examples in Linux

1) How can we limit output per line for any command like ls command output ?
ls |xargs -n 3

2) How can we ask for prompt from xargs command before execution ?
ls |xargs -p -n 3

3) How can we remove all html files older than 30 days by help of xargs ?

find / -name "*.html" -type f -mtime +30 |xargs rm -f

4) How can we remove all files those have white-space " " in it's name and extension is csv ?

find / -name "*.csv" -print0 | xargs -0 rm -f

Note:- "print0" option search files those have whitespace in it's name with regular file name as well.

5) How can we find all txt files those have "string" word in it's content ?

find / -name "*.txt" -type f |xargs grep string

6)  Find all mp3 files and copy all to directory by xargs

find / -name "*.mp3" -type f |xargs cp {} /path/

7) Find all mp3 files and archived those

find / -name "*.mp3" -type f |xargs tar -cvfz songs.tar.gz

Wednesday, 5 July 2017

grep command examples in Linux

grep command :- grep command is used to search files and print lines that have the matched string .
 
1) Print all lines that have the "error" word in file
grep error file.txt

2) Print all lines of files that have the "error" word
grep error file1.txt file2.txt file3.txt

3) Print all lines that have unix word in any capital and small letter
grep -i unix file.txt

Note:- "i" option for case insensitive search.

4) Print all lines those starts with digit
grep "^[0-9]*" file.txt

Note:-"^" denote start of line

5) Print all lines those does not start with digit
grep "^[^0-9]*" file.txt

Note:-"^" sign inside [ ] denote negation of given range

6) Print all lines those end with digit
grep "*[0-9]$" file.txt

7) Print all lines those does not end with digit
grep "*[^0-9]$" file.txt

8) Print all matched lines with 2 lines before matched line that have the "error" word in file
grep -B 2 error file.txt

9) Print all matched lines with 3 lines after matched line that have the "error" word in file
grep -B 2 error file.txt

10) Print all lines that does not contain "error" word in file
grep -v error file.txt

Note:- "v" option invert the selection

11) Print all non-empty lines
grep -v "^$" file.txt

12) Print line count that have matched pattern in file
grep -c error file.txt

13) Print all matched line with line number
grep -n error file.txt

14) Print the file name that contain matched pattern
grep -l error *.txt

Find Command examples in Linux

Find Command:-The Linux find command is very powerful. It can search the entire Filesystem to find files and directories according to the search criteria you specify.

Basic Syntax :- find path option
 

Values for "path" :-
       1)  (.) denote current directory
       2)  (/) denote root directory or full system
       3)  (/path/) denote the path where you want to search 


"Options" for Find command :-
1) time    a) mtime
                 b) ctime
                 c) atime
 

2) min        a) amin
                    b) cmin
                    c) mmin
 

3) name       a) name
                     b) iname
4) size
5) perm
6) type
7) user
8) group

9) empty 
 

Examples:-
1) Find all empty files in /tmp directory
find /tmp -type f -empty

2) Find all empty directories in /tmp directory
find /tmp -type d -empty

3) Find all hidden files in /tmp directory
find /tmp -type f -name ".*"

4) Fill all files of User "oracle" in /tmp directory
find /tmp -user oracle -type f

5) Fill all files of Group "developer" in /tmp directory
find /tmp -group developer -type f

6) Find all files whose modification times is 50-100 days older from today
find / -mtime +50 –mtime -100 -type f

7) Find all files those modified in last 1 hour
find / -mmin -60 -type f

8) Find all files those size is between 50MB - 100MB
find / -size +50M -size -100M -type f

9) Find all mp3 extension files those size is greater than 10MB
find / -type f -name *.mp3 -size +10M

10) Find all mp3 extention files and copy to /tmp/Music directory
find / -type f -name "*.mp3" -exec cp {} /tmp/Music \;

11) Find files that don't have a pattern (.html)
find / -type f -not -name "*.html"
           (OR)
find / -type f ! -name "*.html"

12) Find all files in multiple directories (/opt /usr /var) with extension txt
find /opt /usr /var -type f -name "*.txt"

13) Find all files with different extensions
find / -type f \( -name "*.c" -o -name "*.sh" \)

find / -type f \( -name "*.log" -o -name "*.xml" -o -name "*.html" \) 


Find with "exec" command :-



14) Find all files having 777 permission and change it to 644
find / -perm 777 -type f -exec chmod 644 {} \;

15) Find all directories having 777 permission and change it to 755
find / -perm 777 -type d -exec chmod 755 {} \;

16) Find all files with extension java and having the "string" in its content
find / -type f -name "*.java" -exec grep -l string {} \;
                    (OR)
find / -type f -name "*.java" -exec grep -il string {} \;    

Note:-i option with grep search case insensitive

17) copy one file to many directories by find command
find dir1 dir2 dir3 dir4 -type d -exec cp header.html {} \;

18) Find all .err files older than 60 days and remove those files
find / -type f -name "*.err" -mtime +60 -exec rm -f {} \;

19) Find all directories older than 90 days and remove those  
find . -type d -mtime +90 -exec rm -rf {} \;


Find with "xargs" command :- 

20) Find all files older than 90 days and with extension mp3 and size is greater than 5MB, tar all those files
find / -type f -name "*.mp3" -mtime +90 | xargs tar cvf myfile.tar
                    (OR)
find . -type f -name '*.mp3' -mtime +90 -print0 | xargs -0 tar rvf music.tar

Note:-print0 helps handle spaces in filenames

SQL commands or sub-languages of SQL

SQL (Struture Query Language) :-SQL Commands are used to communicate with the database to perform specific tasks with data. SQL commands ...