Unix Commands

Command	Description
passwd	Change user password
pwd	Print current directory
cd	Change directory
ls	List of the file in the directory
Wildcards	* matches any number of characters, ? matches a single character
file	Print the type of file
cat	Display the contents of a file
pr	Display the contents of a file
pg or page	Display the contents of a file one page at a time
more	Display the contents of a file one page at a time
clear	Clear the screen
cp or copy	Copy a file
chown	Change the owner of a file
chgrp	Change the group of a file
chmod	Change file modes, permissions
rm	Remove a file from the system
mv	Rename the file
mkdir	Create a directory
rmdir	Remove a directory
grep	Pattern Matching
egrep	Grep command with extended regular expression
find	Used to locate files or directories
date	Display the system date and time
echo	Write strings to standard output
sleep	Execution halts for the specified number of seconds
wc	Count the number of words, lines, and characters in a file
head	View the top of a file
tail	View the end of a file
diff	Compare two files
sdiff	Compare two files side by side (requires 132-character display)
spell	Spell checker
lp, lpr, enq, qprt	Print a file
lpstat	Status of system print queues
enable	Enable, or start, a print queue
disable	Disable, or stop, a print queue
cal	Display a calendar
who	Display information about users on the system
whoami	Display $LOGNAME or $USER environment parameter
who am i	Display login name, terminal, login date/time, and where logged in
talk	Two users have a split screen conversation
write	Display a message on a user’s screen
wall	Display a message on all logged-in users’ screens
rwall	Display a message to all users on a remote host
rsh or remsh	Execute a command, or log in, on a remote host
df	File system statistics
ps	Information on currently running processes
netstat	Show network status
vmstat	Show virtual memory status
iostat	Show input/output status
uname	Name of the current operating system, as well as machine Information
sar	System activity report
basename	Base filename of a string parameter
man	Display the on-line reference manual
su	Switch to another user, also known as super-user
cut	Write out selected characters
awk	Programming language to parse characters
sed	Programming language for character substitution
vi	Start the vi editor
emacs	Start the emacs editor

Different type of shell to declare

#!/usr/bin/sh      OR      #!/bin/sh               Declares a Bourne shell
#!/usr/bin/ksh    OR      #!/bin/ksh            Declares a Korn shell
#!/usr/bin/csh    OR      #!/bin/csh             Declares a C shell
#!/usr/bin/bash  OR     #!/bin/bash          Declares a Bourne-Again shell

Full table scans

The oracle optimizer uses full table scan in one of the following condition:

Lack of Index :  if query is unable to use the existing index, optimiser uses a full table scan ( unless a ROWID filter or cluster access path is available).

Large amount of data with low selectivity

Small table : A table which contains less than DB_FILE_MULTIBLOCK_COUNT_READ  blocks under high water mark. Full table is good for small tables.

Full Table Hints: If table is using FULL table hint.

High Degree of parallelism : Because of high degree parallelism optimizer uses full table scan over range scan.For more information on Degree of parallelism refer ALL_TABLES.

Virtual Private database concept

Virtual private database concept is used to support many users with access to the same base tables with restricted row viewing. This is also known as row level security. A table may have 5000 records, but even, if user select all the rows from the table but he can only see those he’s approved without using any filter. This isn’t just applying to SELECT statement but all INSERT, UPDATE and DELETE statement against the table.

Virtual Private database Example

          Create table employee

CREATE TABLE EMPLOYEE

(

  EMPNO    NUMBER(4),

  ENAME    VARCHAR2(10),

  JOB      VARCHAR2(9),

  HIREDATE DATE,

  SAL      NUMBER(7,2),

  COMM     NUMBER(7,2),

  DEPTNO   NUMBER(2)

       ) 

           Populate table with valid set of data

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7369, 'SMITH', 'CLERK',  to_date('17-12-1980', 'dd-mm-yyyy'), 800.00, null, 20);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7499, 'ALLEN', 'SALESMAN', to_date('20-02-1981', 'dd-mm-yyyy'), 1600.00, 300.00, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7521, 'WARD', 'SALESMAN', to_date('22-02-1981', 'dd-mm-yyyy'), 1250.00, 500.00, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7566, 'JONES', 'MANAGER', to_date('02-04-1981', 'dd-mm-yyyy'), 2975.00, null, 20);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7654, 'MARTIN', 'SALESMAN', to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 1400.00, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7698, 'BLAKE', 'MANAGER', to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, null, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7782, 'CLARK', 'MANAGER', to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, null, 10);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7788, 'SCOTT', 'ANALYST', to_date('19-04-1987', 'dd-mm-yyyy'), 3000.00, null, 20);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7839, 'KING', 'PRESIDENT', to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, null, 10);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7844, 'TURNER', 'SALESMAN', to_date('08-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7876, 'ADAMS', 'CLERK', to_date('23-05-1987', 'dd-mm-yyyy'), 1100.00, null, 20);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7900, 'JAMES', 'CLERK', to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, null, 30);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7902, 'FORD', 'ANALYST', to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, null, 20);

INSERT INTO employee (EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)

VALUES (7934, 'MILLER', 'CLERK', to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10);

           Create virtual private database package

CREATE OR REPLACE PACKAGE PKG_VPDC

AS

  L_DEPTNO EMPLOYEE.DEPTNO%TYPE;

  PROCEDURE SET_DEPTNO(V_DEPTNO EMPLOYEE.DEPTNO%TYPE);

  FUNCTION PREDICATE (OBJ_SCHEMA VARCHAR2, OBJ_NAME VARCHAR2)  RETURN VARCHAR2;

END PKG_VPDC;

/

CREATE OR REPLACE PACKAGE BODY PKG_VPDC AS

 

  PROCEDURE SET_DEPTNO(V_DEPTNO EMPLOYEE.DEPTNO%TYPE) IS

  BEGIN

    L_DEPTNO := V_DEPTNO;

  END SET_DEPTNO;

  FUNCTION PREDICATE  (OBJ_SCHEMA VARCHAR2, OBJ_NAME VARCHAR2)

  RETURN VARCHAR2 IS

  BEGIN

    RETURN 'DEPTNO = ' || L_DEPTNO;

  END PREDICATE;

 

END PKG_VPDC;

    /

           Create DEPTUSER table and populate it with valid set of data   

CREATE TABLE DEPTUSER

(

  DEPTNO         NUMBER,

  USERNAME              VARCHAR2 (10)

);

INSERT INTO DEPTUSER VALUES ( 10, 'USER10');

INSERT INTO DEPTUSER VALUES ( 20, 'USER20');

INSERT INTO DEPTUSER VALUES ( 30, 'USER30');

          Create database logon trigger

CREATE OR REPLACE TRIGGER TRG_VPDC

  AFTER LOGON ON DATABASE

DECLARE

  L_DEPTNO DEPTUSER.DEPTNO%TYPE;

BEGIN

  SELECT DEPTNO INTO L_DEPTNO

  FROM DEPTUSER WHERE UPPER(USERNAME) = USER;

  PKG_VPDC.SET_DEPTNO(L_DEPTNO);

END;

      /

          Add policy

BEGIN

  DBMS_RLS.ADD_POLICY(USER,'EMPLOYEE', 'DEPARTMENT SECRET', USER,

                       'PKG_VPDC.PREDICATE','SELECT, UPDATE, DELETE');

END;

           Add database Users

CREATE USER USER10 IDENTIFIED BY USER10;

CREATE USER USER20 IDENTIFIED BY USER20;

CREATE USER USER30 IDENTIFIED BY USER30;

GRANT CREATE SESSION TO USER10;

GRANT CREATE SESSION TO USER20;

GRANT CREATE SESSION TO USER30;

GRANT ALL ON EMPLOYEE TO USER10;

GRANT ALL ON EMPLOYEE TO USER20;

GRANT ALL ON EMPLOYEE TO USER30;

GRANT EXECUTE ON PKG_VPDC TO PUBLIC

CREATE PUBLIC SYNONYM EMPLOYEE FOR EMPLOYEE;

          Output

SQL> CONN USER10/USER10

CONNECTED TO ORACLE DATABASE 10G ENTERPRISE EDITION RELEASE 10.2.0.1.0

CONNECTED AS USER10

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME      JOB       HIREDATE          SAL      COMM DEPTNO

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

 7782 CLARK      MANAGER   6/9/1981      2450.00               10

 7839 KING       PRESIDENT 11/17/1981    5000.00               10

 7934 MILLER     CLERK     1/23/1982     1300.00               10

SQL> CONN USRE20/USER20

NOT LOGGED ON

SQL> CONN USER20/USER20

CONNECTED TO ORACLE DATABASE 10G ENTERPRISE EDITION RELEASE 10.2.0.1.0

CONNECTED AS USER20

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME      JOB       HIREDATE          SAL      COMM DEPTNO

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

 7369 SMITH      CLERK     12/17/1980     800.00               20

 7566 JONES      MANAGER   4/2/1981      2975.00               20

 7788 SCOTT      ANALYST   4/19/1987     3000.00               20

 7876 ADAMS      CLERK     5/23/1987     1100.00               20

 7902 FORD       ANALYST   12/3/1981     3000.00               20

SQL> CONN USER30/USER30

CONNECTED TO ORACLE DATABASE 10G ENTERPRISE EDITION RELEASE 10.2.0.1.0

CONNECTED AS USER30

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME      JOB       HIREDATE          SAL      COMM DEPTNO

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

 7499 ALLEN      SALESMAN  2/20/1981     1600.00    300.00     30

 7521 WARD       SALESMAN  2/22/1981     1250.00    500.00     30

 7654 MARTIN     SALESMAN  9/28/1981     1250.00   1400.00     30

 7698 BLAKE      MANAGER   5/1/1981      2850.00               30

 7844 TURNER     SALESMAN  9/8/1981      1500.00      0.00     30

 7900 JAMES      CLERK     12/3/1981      950.00               30

6 ROWS SELECTED

        SQL>