UNIX Command: Looking into files.

As it is important to know how to navigate in UNIX to find files it is important to know how to navigate within files. There are obviously few commands in UNIX that helps you to navigate within the files or view the required contend of your files without much difficulty.

Below are the few commands which are helpful to navigate through files in UNIX.

file - to identify file types
head- peak at the first few lines
tail - view last few lines
cat - view content of file
more - view larger files.


file:
A program that can easily offer you a good hint as to the contents of a file by looking at the first few lines. The disadvantage of this command is, it is not 100% accurate
eg:- A text file has executable permission and the initial contents of the files look like a C program the file command interpret it as an executable program rather that an English text file.

% file newtext.txt helloworld.txt 'shiyas resume.doc'
newtext.txt: ascii text
....
...
photos: directory

% file *
will analyze all files in home directory
 
head: Use it to view up to the first few hundred lines of a very long file, actually. You can specify the number as well.
% head newfile.txt
first few lines are displayed

% head -4 newfile.txt
first 4 lines are displayed

Can supply multiple files as well
% head -4 newfile.txt passwd
=====>passwd<====
......................................

=====>newfile.txt<===
.......................................


Can be used along with pipes (|):
% who | head -5
the output of who is supplied to head -5 and lists down first 5 users in the list.


tail:
Provide last lines of the file.
%

cat:
flags: -v -shead -12 .cshrc | tail -3

combine the two, head and tail, so you can see just the tenth, eleventh, and twelfth lines of a file?

UNIX questions

Q: What is the output of % echo $DATE?
Ans: Nothing is displayed.
Note: Here we are trying to display the value stored in the variable DATE, which not declared nor initialized, hence nothing is displayed

Q: What is the output of %date?
Ans: date with time

Q: In my directory /u/home/phoenix I have one file name magazine.txt and a directory magazine. What will be the output for ls -l magaz* ? Will it list both the file and directory or only one among them? If one, which one?

Ans: ls -l magaz* will list the magazine.txt file only.
If you need to see the directory then you should run ls -ld magaz*

Q: Does the command mkdir has arguments?
Ans: No

Q: What if you create a directory with same name as one that exist already?
Ans: Will throw error saying file already exist

Q: umask is set to 0222, decode the default permission for this value.
Ans:
The value for umask is just opposite of what the real privilege is set, hence
0 indicates owner has all access except execute : rw-
2 indicates group has all privilege except write and execute: r--
2:r--
Hence finally we have: -rw-r--r--

Q: What is command use for moving a file?
Ans: mv

Q: What is the command used for renaming a file?
Ans: mv

Q: I have a directory in which there are many directories and files and some are as below:
directories:DIR1, DIR2,DIR3
files:DIR1.txt, DIR2.txt
there are no files in DIR1 and DIR2, whereas DIR3 has two files hello.txt and hi.txt
Now i ran

ls- l DI*, what will be the output?

ls -ld DI* what will be the output?

Q: What is the result, cp newdir latestdir, where newdir is a directory?
cp: omitting directory 'newdir'

Q: what is the resutl, cp newfile.txt
Ans: cp: missing destination file operand after newfile.txt
Try 'cp --help' for more information.

What is the result on executing umask 77777?
bash: umask: 777777: octal number out of range

I have changed the umask value from the default one (022) to 777 and then I exit from the terminal and logged in again. What will the value of umask.

ans:022.
Each time you login to the terminal umask value get reset always.

****
Hope this is helpful. Thanks Phoenix..

Why Shell Scripts always require both read and execute permission?

Any program works fine in UNIX even it has only execute permission. By giving execute privilege alone for any program we make sure that, the users of this particular program has the permission to run the program but not to examine or copy or modify the code that we have written.

Shell scripts belongs to a special class of programs, which fails to execute if only execute privilege is granted on those. Why is this becasue, shell script, which acts as a UNIX command line macro facility, which helps in clubbing togther a series of commands into a single file and run them as a single program. Hence for the shell to execute this program (or commands inside the file), it need to read the file first and then execute.

So shell script always need both read and execute permissions!!

UNIX Commands

ls : List files in a directory

ls -a : List hidden dot files as well in a directory

ls -C -F: List files with slash ('/') for current driectory

ls -C -F /: List files with slash ('/') for root directory

ls -l: List files in long format.

ls -lr: List files in long format and in reverse order based on file/directory name.

ls -lt: List files in long format and ordered based on file/directory modified date. The most recently accessed files.

ls -lrt: List files in long format and in reverse order based on file/directory modified date.

ls -s: List the filenames along with its size. The total size of all files in the directory is given in the first line. Size is given in Kilobites rounded upwards.

ls -s will list all the files under the current directory and its size as well. If you are intereseted to see size details of any specific directory you can give the command as below
ls -s <dirname>

we can specify the filename as well
ls -s <filename>

You can specify multiple filenames or dierctories as well and seperate them by space.

ls -s <dir1> <dir2> <file1>
Output:
10 file1

dir1:
 total 456
     2 file1
     4 file2
     etc...
dir2:
 total 100
     45 file1
     50 file2
     etc...
ls -f: Suffices the file type at the end of the filename
Different types of suffices
       - / indicates directory
       - * Indicates program
       - @ indicates a symbolic link to another file or directory.

ls -m: List the contents of a directory comma seperated.

ls -1: List the content of directory in a single column.

-a

List all files, including any dot files.

-F

Indicate file types; / = directory, * = executable.

-m

Show files as a comma-separated list.

-s

Show size of files, in blocks (typically, 1 block = 1,024 bytes).

-C

Force multiple-column output on listings.-1 Force single-column output on listings.
ls -x: Change the default sorting order from column first then row to row first then column

ls -


pwd- stands for present working directory and will  display the absolute path of your current directory.

bc: Used for infix mathematical calcualtions.
$bc
12*4
48
quit
$

-1

Force single-column output on listings.

-a

List all files, including any dot files.-C Force multiple-column output on listings

-d

List directories rather than their contents.

-F

Indicate file types; / = directory, * = executable.

-l

Generate a long listing of files and directories.

-m

Show files as a comma-separated list.

-r

Reverse the order of any file sorting.

-R

Recursively show directories and their contents.

-s

Show size of files, in blocks (typically 1 block = 1,024 bytes).

-t

Sort output in most-recently-modified order.-x

Touch Command:
helps you create new files on the system

The main reason that

updated, as the following example demonstrates.

touch is used in UNIX is to force the last-modified time of a file to be

%

ls -l iecc.list

-rw------- 1 taylor 3843 Oct 6 18:02 iecc.list

%

touch iecc.list

%

ls -l iecc.list-rw------- 1 taylor 3843 Oct 10 16:22 iecc.list

If you try to use the

creates the file:

Compress:
compress textfile.txt

compress -v textfile.txt

Uncompress:
uncompres textfile.txt / uncompress textfile.txt.z
touch command on a file that doesn’t exist, the programSort output in row-first order.

Flag Meaning

mv
% ls -1
Payroll
newfile.txt
photos
newphotos

% mv newfile.txt latestfile.txt
% ls -1
Payroll
latestfile.txt
photos
newphotos

All the contents of the newfile.txt is moved into the latestfile.txt and latestfile.txt is removed from directory. The same is applicable in the case of directories also. We can entirely move the content of a directory into a new one. If you look closer you can find that this is nothing but just renaming the folder or file. Then what is actual moving of file or dirctory?

% mv newphotos ./Photos
% ls -1
Payroll
latestfile.txt
helloworld.txt
Photos
% ls -1 ./Photos
newphotos

Here the entire newphotos directory is moved into Photos directory. Similarly we can move the files as well from directory to directory.

What if I execute the below query?
% mv latestfile.txt helloworld.txt
ans: The contents of latestfile.txt is overwritten into helloworld.txt. A popup will come before overriding for which if give 'y' as input itwill override else wont.

rmdir:
rmdir is used to remove any directory. One advantage with rmdir is that, it removes only empty data, hence no need to worry on deleting the content of a directory unknowigly.
On executing rmdir against a directory which is not empty we get a message as below.
rmdir: failed to remove 'dir': Directory not empty

rm:
Used to remove files. Cant be used for directory (will throw error). This command removes any file. This command removes the file permanantly, cant be restored at any point of time. You can remove multiple files at a time.
For the safe side always use -i flag along with rm command, where i stands for interactive, which will ask us before removing the file
rm -r  is a dangerous one as it will delete files recursively

How could you check, what type of login shell you're running?
% grep pnaraya /etc/passwd
pnaraya:x:1000:1000:pnaraya,,,:/u/pnaraya:/bin/bash

on the password entry the login shell is specified. (/bin/bash)

How to trim the column value while using SQL*LOADER

Scenario:
I was using sqlloader to load the data in my file data.csv to my table emp. The column size of lastname is varchar2(20), but the value in my csv file was having space appended to the name making the size more than 20. Hence when I use sqlloader it faisl with error.

Record 302: Rejected - Error on table emp, column lastname.
ORA-12899: value too large for column "SCHEMA"."EMP"."LASTNAME" (actual: 23, maximum: 20)

And this record goes to bad file. I have to go and check for such values and edit it manually for each and every one.

How can I use trim in my control file?

My Control file is as below:
OPTIONS (SKIP=1)
load data infile 'D:\Load\data.csv'
TRUNCATE into table emp fields
terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(ID,FIRSTNAME ,LASTNAME)

Solution:
You can add trim into control file as below:
OPTIONS (SKIP=1)
load data infile 'D:\Load\data.csv'
TRUNCATE into table emp fields
terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(ID "TRIM(:ID)",FIRSTNAME "TRIM(:FIRSTNAME)",LASTNAME"TRIM(:LASTNAME)")

This will solve the issue.

Oracle Global Temporary Table

Temporary tables were introduced from Oracle 8i onwards and is called global temporary tables. This is called temporary table because it is created when the data is inserted and is similar to normal tables.

Syntax:
create global temporary table temp_data
( emp_id number, emp_name varchar2(10));

The definition of this table is visible to all sessions, but the data is visible to only the session that creates this table. This is used by developers to store session/transaction specific data which can be ignored at the end of the session/transaction. On issuing a truncate on this table the data that is specific to the current session alone will get deleted.

It is possible to store the session specific data in this temporary table with the help of an additional cluase that we use while creating the table, on commit. We can define the temporary table either to delete or store the session specific data with "on commit" parameter.

Syntax:
create global temporary table temp_data
( emp_id number, emp_name varchar2(10))
on commit delete rows;

Here as soon as the transaction ends with a commit, the records in the table are deleted.

Syntax:
create global temporary table temp_data
( emp_id number, emp_name varchar2(10))
on commit preserve rows;

The above definition will preserve the data in the temporary table even after end of a transaction.

Limitations:

Temporary tables cannot contain nested tables or varray types or they cannot be partitioned, index-organized or clustered. They cannot be used in parallel DML or parallel queries and distributed transactions are not supported on these tables.

Oracle Interview Qestions Part 2: PL/SQL

Q: What is PL/SQL?
Ans: PL/SQL stands for Procedural Language extension of SQL.

Q: Where do we store a PL/SQL code?
Ans: In database and in client system as well.

Q: Is it possible to have two procedures in a same package with same name?
Ans: Yes, until you have these procedures accept different variables with different datatypes.

Q: A new procedure is added to a package body and it is not added to spedification. On combilation will this package be valid or invalid?
Ans: Valid. A procedure if not placed in the package specification doesnt lead the package to invalid state. It makes the procedure as private, ie this particular procedure cannot be called from any other package.

Q: How can we declare a procedure as private?
Ans: As explained above.

Q. What is a collection?
Ans:
Ordered group of elements, all of the same type, similar to lists and arrays.

Q: What is Record?
Ans:
A record is a group of related data items stored in fields, each with its own name and datatype. You can think of a record as a variable that can hold a table row, or some columns from a table row. The fields correspond to table columns.

Q: What are different types of Collections in PL/SQL?
Ans:
Nested Tables
Associative Arrays; similar to hash table
Variable-size arrays.

Q: What is a Subprogram?
Ans:
A named pl/sql block.

Q: What are different types of subprograms available in PL/SQL?
Ans:
Procedures and Functions.

Q: What is the difference between a procedure and function?
Ans:
Procedure does an action wheras function computes and return a value.

Q: What are the different blocks in PL/SQL?
Ans:
Declarative
Execution
Exception

Q: What all are the things that you can declare in Declaration section?
Ans:
Types
Cursors
Constants
Exceptions
Nested Subprograms
Variables

Q: How does nested table differ from arrays?
Ans:
1. Nested tables does not have declared number of elements whereas arrays has. Size of nested tables will increase dynamically.
2. Nested tables might not have consecutive subscripts whereas arrays always has.

You can delete elements from a nested table using the built-in procedure DELETE.

The built-in function NEXT lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.

Q: What are the PL/SQL datatypes used to define collections?
Ans:
TABLE and VARRAY.

Q: How many types of tables are available in Oracle?
Ans:
Three Types:
Relational table (eg: employee table to hold employee data.)
Object tables
XMLType tables

Q: Different type of table level constraints?
Ans:
-Primary Key
-Unique Key
-Check
-Foriegn

Q: When do you use compressed tables?
Ans:
For some applications, particularly data warehousing, with large tables that are frequently queried but very rarely updated, you may create compressed tables. These require less disk storage than uncompressed tables (which are the default).

Q: What is the syntax for IF THEN ELSE statement in PL/SQL?

IF department=1 THEN
result:= 'Accounts'
ELSIF department='2' THEN
result:='Finance'
ELSE
result:='Miscellaneous'
END IF;


Q: What is the syntax for CASE statement in PL/SQL?
Ans: CASE perfoms the same function of IF-THEN-ELSE statement.
The sysntax is:
The syntax for the case statement is:
  CASE  [ expression ]
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  WHEN condition_n THEN result_n
  ELSE result
END

Q: What are all the advantages of PLSQL?
Ans:
1. Tightly integrated with SQL
               a) You can perform all actions done by SQL with PLSQL
               b) PLSQL fully support SQL datatypes.

2. High Performance
               a) You can send a block of sql statements to the database, thereby reducing traffic.



3. Productivity

3. Portability & Scalability & Manageability
4. Support for Object-Oriented-Programming, Developing Web Applications, Developing Server Pages

Q: What is the difference between Static and Dynamic SQL?
Ans:
Static SQL is SQL whose full text is known at compilation time.
Dynamic SQL is SQL whose full text is known only at run time.

Understanding database Schema and Schema Objects

Tables, Indexes, views, synonyms, procedures, packages etc that exists in database is reffered as objects. These objects grouped together logically is called schema. This schema is owned by a user and the name of schema is same as user.

Hence schema is nothing but a logical structure created by database users, which is a collection of database objects.

Every object in a database belogns ton one schema and each object has a unique name within the schema. All objects that belongs to a single application is placed in same schema.

There are certain naming conventions that need to be followed while creating a new object
* Name of each object should be unique
* Name of object cannot be longer than 30 character
* Must begin with a letter.

Violating any of these rules results in Oracle error.

ORA-04021: timed out occured while waiting to lock object

Scenario:
I came across this error whilst trying to recompile a PL\SQL package in the database. It took some time to show this errro message, until then the sqldeveloper was in a locked state.

My package was doing parallel processing of procedures, where we use jobs, chains, programs, steps etc... I triggered this parallel processing once, and closed this before it completes. After this whenever I am trying to edit the package I get this time out error.

Analysis:
When someone is trying to recompile a package and found it is hanging or waiting it may be because some one else is using it and a new attempt to use the package would find it locked.

It seems there may be a lock on package.

Let us find out the session that locks the packages and Kill those.

select * from v$locked_object
no rows selected

select a.sid,a.serial#,b.sql_text from v$session a, v$sql b
where a.sql_id=b.sql_id
and a.username='schema'

select sessionid,owner, name from dba_ddl_locks where name like 'test_pkg'
SESSIONID
OWNER
NAME
2174,schema,test_pkg

SELECT * from v$access where object='test_pkg';
SID
OWNER
OBJECT
TYPE

2174,SCHEMA,TEST_PKG,PACKAGE

select sid,serial# from v$session where sid=2174;
SID
SERIAL#
2174,23456

Kill this session:
alter system kill session '2174,23456' immediate;

system altered

Now you may be able to recombile your package.

Understanding Orcle Instance and Instance Management

Understanding Oracle database:
All of us are familiar with creating a new folder under one directory and placing our files under each folder in an organized manner. We have an option to search for the file name or directory. But we dont have a proper mechanism to search the data and store the data in relation to other. For this purpose we have database. More specifically we say Relational Database Management System.

Hence Oracle database is combination of operating system files having data entered by the user and the structural information about the database, which is called metadata (data about data).

If a person needs to see these stored data or need to update the existing one, there should be some processes running by Oracle and allocate some memory to be used during these operations. This background process and memory allocation together is called an instance. So whenever we need to read/write data from database we should start a new session/instance.

What is Initialization parameter?
When a person starts a new session/instance of an Oracle database to read/write, the session that opens up with some basic charecteristics which are configured in the initialization parameter file. Properties of an instance depends up on the parameter values in the initialization parameter files.

When an instance is started, Oracle database server reads these parameters and monitor them throughout the session and is stored in memory, of which some changes are dynamically. Availability of this dyanmic changes during database startup and shutdown depends on the type of parameter files.

What are different type of parameter files?
Server Parameter File:
A binary file which can be read and write by database. Can't be edited manually. This file resides on the machine where oracle is running on and changes are persistenct across database shutdown and startup.

Text Initialization Parameter File:
File which is configured by user and read by database. File is persistent across database shut down and start up.

Structure of Memory allocation while initiating a session:
The performance of Oracle database is affected by the size of instance memory structures which are configured in the initialization parameter file.
When a database is created, the memory parameters are set automatically based on database load, however it can set manually based on our usage.

Oracle provides alerts and advicor to determine the optimal values to set to handle memory isssues.

The two different memory structures in oracle are:
System Global Area (SGA):
This is a shared memory area where data and instance controlling information resides. Multiple users can use data in this area, hence called shared area.

Program Global Area (PGA):
Area used by a single Oracle server process. A server process is a process that service client's request. Each server process has its own non shared PGA when the process is started.

Why PGA is used for? What are all the kind of services processed in PGA?
To process SQL statements and to hold logon and other session information.

Oracle Background Process: Why Background Processess?
* To Manage memory structure
* Asynchronously perform I/O to write data to disk
* General Maintenance

What are different type of processes available?
* Database Writer (DBWn): Writes modified blocks from buffer cache to disk.
* Log Writer (LGWR): Write redo log entries to disk.
* Checkpoint: At a specific period of time data from buffer in SGA is written to disk. This point is called checkpoint. Checkpoint process signals DBWn, updates all files and logs the time of update.
* System Monitor (SMON): Perfroms crash recovery when a failed instance starts again
* Process Monitor (PMON): Performs recovery when a user process fails. Cleans up cache and free memory used by failed process.
* Archiver (ARCn): Copy redo log files into archival storage when log files are full.

Select from table procedure/package details

I want to search for a particular procedure in the list of packages available in the system. How can it be achieved through SQL script?

SQL> SELECT object_name,procedure_name FROM user_procedures WHERE object_type='PACKAGE' AND procedure_name='procedurename'

Incrementing sequence value

How can we set sequence value to a higher one? Say for example the current value of our sequence is 100 and we need to set this to 250, how can we accomplish this?

SQL> select seq_name.nextval from dual;
100

SQL> alter sequence seq_name
increment by 150;

SQL> select seq_name.nextval from dual;
250

It works!!

Try fetching next value once again..

SQL> select seq_name.nextval from dual;
400

Oops!! It incremented by 150.

Follow below step to increment your sequence without behaving like as above.
Now let us increment to 500.

SQL> alter sequence seq_name
increment by 100;

SQL> select seq_name.nextval from dual;
500

Reset the increment parameter!!
SQL> alter sequence seq_name
increment by 1;

SQL> select seq_name.nextval from dual;
501

SQL> select seq_name.nextval from dual;
502

****Happy CoDiNg!!****