To manage bulk collection

select ....
bulk collect ....
from table name
where ....

bulk collect retrieve a bunch of data...

create table bc (
a number,
b varchar2(10)
);

insert into bc values (11,'elf');
insert into bc values (12,'zwoelf');
insert into bc values (13,'dreizehn');
insert into bc values (14,'vierzehn');

commit;

set serveroutput on size 10000

declare
type t_bc_a is table of bc.a%type;
type t_bc_b is table of bc.b%type;

l_bc_a t_bc_a;
l_bc_b t_bc_b;
begin
select a, b bulk collect into l_bc_a, l_bc_b from bc;

for i in l_bc_a.first .. l_bc_a.last loop
dbms_output.put_line(l_bc_a(i) || ', ' || l_bc_b(i));
end loop;

end;
/

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.