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.