There are five type of files

Parameter files – These files tell the Oracle instance where to find the control files.
For example, how big certain memory structures are, and so on.
The files that make up the database are:
Data files – For the database (these hold your tables, indexes and all other segments).
Redo log files – Our transaction logs.
Control files – Which tell us where these data files are, and other relevant information about
their state.
Temp files – Used for disk-based sorts and temporary storage.
Password files – Used to authenticate users performing administrative activities over the
network.

TNS stands for ???

TNS stands for Transparent Network Substrate

How oracle process a select in a multi-threaded server

The client connection will send a request to the dispatcher. The dispatcher will first place this request onto the request queue in the SGA
(1). The first available shared server will dequeue this request
(2) and process it. When the shared server completes,
the response (return codes, data, and so on) is placed into the response queue
(3) and subsequently picked up by the dispatcher
(4), and transmitted back to the client.

What the diff between an instance and a database

Database – A collection of physical operating system files

Instance – A set of Oracle processes and an SGA


The relationship between the two is that a database may be mounted and opened by many instances.

An instance is simply a set of operating system processes and some memory. They can operate on a database, a database just being a collection of files (data files, temporary files, redo log files, control files). At any time, an instance will have only one set of files associated with it. In most cases, the opposite is true as well; a database will have only one instance working on it.

How to limit a user to one session only

ops$tkyte@ORA8I.WORLD> create profile one_session limit sessions_per_user 1;
Profile created.
ops$tkyte@ORA8I.WORLD> alter user scott profile one_session;
User altered.
ops$tkyte@ORA8I.WORLD> alter system set resource_limit=true;
System altered.

What a hard parse...soft parse....latches

Hard parse - hard coded variables
soft parse - .....
latches - protect the data structures in the shared memory of Oracle from concurrent modifications by two sessions

command to clear shared pool
- alter system flush shared_pool;


tkyte@TKYTE816> set timing on
tkyte@TKYTE816> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for
10 ‘select object_name
11 from all_objects
12 where object_id = ‘ i;
13 fetch l_rc into l_dummy;
14 close l_rc;
15 end loop;
16 dbms_output.put_line
17 ( round( (dbms_utility.get_time-l_start)/100, 2 )
18 ‘ seconds...’ );
19 end;
20 /
14.86 seconds...
PL/SQL procedure successfully completed.


tkyte@TKYTE816> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for
10 ‘select object_name
11 from all_objects
12 where object_id = :x’
13 using i;
14 fetch l_rc into l_dummy;
15 close l_rc;
16 end loop;
17 dbms_output.put_line
18 ( round( (dbms_utility.get_time-l_start)/100, 2 )
19 ‘ seconds...’ );
20 end;
21 /
1.27 seconds...
PL/SQL procedure successfully completed.

Using bind variables

select * from emp where empno = :empno;


the query uses a bind variable, :empno, the value of which is supplied at query execution time.
This query is compiled once and then the query plan is stored in a shared pool (the library cache), from which it can be retrieved and reused.

Record should not be update while another program is running

select * from t for update

the for update - lock the record..until a commit or a rollback command

What is TKPROF

Differentiate between variables

create or replace package my_pkg
as
g_variable varchar2(10);
procedure p (p_variable in varchar2) is
l_variable varchar2(10);
begin
null;
end;
end;

the g_variable - can be viewed by the whole package
the p_variable - is a parameter variable
the l_variable - is the local variable

SQL plus enviroment

In order for DBMS_OUTPUT to work, the following command must be issued
SQL > set serveroutput on

The set serveroutput on size 1000000 ..... the set the default buffer size to be as large as possible...

Set trimspool on ensures that, when spooling text, lines will be blank-trimmed and not fixed width...

set long 5000 sets the default number of bytes displayed when selecting LONG and CLOB

set pagesize 9999 sets the pagesize, which controls how frequently sql*plus prints out headings, to big number (we get one set of heading per page).