Oracle 10G Chapter 1

Front 1

What are the Oracle Family Products?

Back 1

1-Oracle Database 10g.
2-Oracle Application Server 10g.
3-Oracle Developer Suite.
4-Oracle Applications 11i.
5-Oracle Collaboration Suite.
6-Oracle Services(technical support and consulting services).

Front 2

What are the 5 editions of Oracle 10g?

Back 2

1- Enterprise.
2- Standard(upto 4 processors).
3- Standard Edition One(upto 2 processors).
4- Personal(for individual user).
5- Lite(for mobile applications).

Front 3

What are the 2 types of Meta Data views available in Oracle 10g?

Back 3

1- Data Dictionary Views(arround 1300 Data Dictionary views).
2- Dynamic performance Views(approximately 350 views depending upon which feature is installed).

Front 4

What types of Data Dictionary views are available in Oracle 10g?

Back 4

1-DBA_TABLES
2-ALL_TABLES
3-USER_TABLES

Front 5

What kind of information do you get from DBA_ DD VIEWS?

Back 5

All tables in the entire database.
Reserved for user accounts that have DBA privileges.

Front 6

What kind of information do you get from ALL_ DD VIEWS?

Back 6

All tables owned by a particular user plus all tables to which the user has been granted access to.

Front 7

What kind of information do you get from USER_ DD VIEWS?

Back 7

All tables owned by a particular user

Front 8

What information does the DD View DBA_TABLES contains?

Back 8

Shows name and physical storage information about all the tables.

Front 9

What information does the DD View DBA_USERS contains?

Back 9

Shows information about all the users in the database.

Front 10

What information does the DD View DBA_VIEWS contains?

Back 10

Info about all the views in the database

Front 11

What information does the DD View DBA_TAB_COLUMNS contains?

Back 11

Shows all the names and datatypes of the table column

Front 12

What information does the Dynamic Performance View V$DATABASE contains?

Back 12

Information about the database itself such as database name, creation date.

Front 13

What information does the Dynamic Performance View V$VERSION contains?

Back 13

Version of the database

Front 14

What information does the Dynamic Performance View V$OPTION contains?

Back 14

Which optional component is installed in the database.

Front 15

What information does the Dynamic Performance View V$SQL contains?

Back 15

Information about the SQL statements that database users have been using.

Front 16

Define Constraints?

Back 16

A contraint allows the database designer to enforce business rules about the data stored in the database's tables and the relationships between tables.

Front 17

What are the types of table constraints?

Back 17

1- Not Null (needs a value, it doesnot have to be unique)
2- Unique Key (every value in the column must be unique but null values are allowed).
3- Primary Key (unique values in the column but can't have null)
4- Foreign Key (Every value in the column must match with a value in another column in this table or another table, otherwise the value is null)
5- Check (The value must match one of the specified value in this column).

Front 18

Where does the constraint informations are stored?

Back 18

They are stored in DD Views
1- DBA_CONSTRAINTS
2- DBA_CONS_COLUMNS.

Front 19

Give SQL Example of adding a Primary Key?

Back 19

SQL>ALTER TABLE DEPT add constraint DEPT_PK primary key (DEPTNO);
SQL>CREATE TABLE EMP(empno number(4) constraint EMP_PK primary key, empname varchar2(10), deptno number(2) constraint EMP_PK_DEPTNO references DEPT(deptno))

Front 20

Define Segment?

Back 20

A segment is defined as any entity that consumes physical storage within the database.

Front 21

What are the types of segments?

Back 21

1- Table (stores data in rows and columns)
2- Index(Improves access to the table data)
3- Rollback (to maintain read consistency and perform transaction recovery)
4- Partition (Divides table into smaller, more manageable pieces for performance purposes)

Front 22

Define Extent?

Back 22

Each Oracle segment is made up of continous chun of storage space in the database called extent.

Front 23

What is the minimum and maximum size of a segment in terms of extents?

Back 23

At least 1 extent per segment but a segment can have as many as 2 Billion extents.

Front 24

Define Blocks?

Back 24

Each extent is made up of smaller chunks of space called blocks.

Front 25

What is the minimum and maximum size of a extent in terms of blocks?

Back 25

The minimum size of an extent is 5 blocks.

Front 26

What is the size of blocks?

Back 26

The default size of a block is determined at the time of database creation.
The common database block sizes are 2KB, 4KB, 8KB and 16KB.

Front 27

What is a block composed of?

Back 27

It is made up of Operating System blocks whose size is determined by the operating system, the most common size is 512 bytes.

Front 28

What are the types of SQL Statement?

Back 28

1-Queries created using SQL
2-DML (Insert, Update and Delete)
3-DDL (Create, Alter, Drop)
4-DCL (Grant and Revoke)

Front 29

What are the benefits of PL/SQL on SQL?

Back 29

SQL doesnot support:
1- Condition Testing
2- Looping
3- Exception Handling.

Front 30

What are the types of PL/SQL Objects?

Back 30

1- Anonymous Block (a block of PL/SQL code that is not stored in the database, but instead its embedded in a form, webpage, or SQL script)
2-Procedure (the code in the database which performs a specific action)
3- Function (the PL/SQL code that is stored in the database and return a value)
4- Package(collection of related functions and procedures)
5- Trigger(the code which runs whenever some activity occurs).

Front 31

What are the categories of Oracle Server Architecture?

Back 31

1- User-related processes
2- Oracle Instance (memory structure)
3- Database (physical file structure).

Front 32

What is SGA?

Back 32

An Oracle Server Instance is made up of Oracle's main memory structure, called the System Global Area.
The Server Processes communicates with SGA when the user accesses the data in the database.

Front 33

What are the components of SGA?

Back 33

1-Shared Pool
2-Database Buffer Cache
3-Redo Log Buffer
4-Java Pool
5-Large Pool
6-Streams Pool

Front 34

Describe Shared Pool?

Back 34

Shared Pool caches the most recent SQL issued by the user.
It uses LRU (Least Recently Used algorithm)to manage the contents

Front 35

Describe Database Buffer Cache?

Back 35

Caches the data that has been most recently accessed by the users.
It uses LRU (Least Recently Used algorithm)to manage the contents

Front 36

Describe Redo Log Buffer?

Back 36

It stores information for the recovery process.

Front 37

Describe Large Pool?

Back 37

Caches Data for large operations such as Recovery Manager backup and restore activities and Shared Server Component.

Front 38

Describe Streams Pool?

Back 38

Caches the Data stored with Queued message request when Oracle Advance Queuing option is used.

Front 39

What is the unit of SGA memory space?

Back 39

Granules.
Granules can be 4MB, 8MB or 16MB Sizes.

Front 40

How many types of Parameter files are their?

Back 40

2 Types
1- PFILES Parameter Files
2- SPFILES Server Parameter Files.
Both of these files can be used to configure instance and database options including the size of SGA and its components if manual SGA management is being used.

Front 41

What are the differences between PFILE and SPFILE?

Back 41

PFILE:-
1- Its a text file
2- Instance need to be shutdown and restarted in order to get the changes.
3- Naming Convention initinstance name.ora
4- It can be created from SPFILE using create PFILE command.

SPFILE:-
1- its a binary file.
2- Most changes can be made dynamically when the instance is up and running.
3- The naming conventions are spfileinstance name.ora
4-Can be created from PFILE by using create SPFILE command.

Front 42

What dynamic performance view can be used to examine SGA?

Back 42

V$SGA
SQL>select * from V$SGA;
For looking into the SGA Components.
SQL>select components, current_size from v$sga_dynamic_components;

Front 43

What are the types of Oracle Background Process?

Back 43

1-Required(they are found in all the instances of Oracle)
2-Optional(may or may not be used depending upon what features are being used).

Front 44

What are the required oracle background processes?

Back 44

1-System Monitor (SMON- Perform instance recovery following the instance failure also manages the space in the database)
2-Process Monitor (PMON- Cleans up failed user database connections)
3-Database Writer (DBWn - writes the modified db blocks from SGA's DB Buffer Cache to the datafiles on the disk)
4- Log Writer (LGWR - Writes transaction recovery information from the SGA's Redo Log Buffer to the online Redo Log Files on disk)
5- Check Point(CKPT- Update the database file following the checkpoint event)

Front 45

What are the optional oracle background processes?

Back 45

1-Archiver ARCn
2-Recoverer RECO
3-Job Queue Monitor CJQn
4-Job Queue Jnnn
5-Queue Monitor QMNn
6-Parallel Query Slave Qnnn
7-Dispatcher Dnnn
8-Shared Server Snnn
9-Memory Manager MMAN
10-Memory Monitor MMON
11-Memory Monitor Light MMNL
12- Recovery Witer RVWR
13- Change Tracking Writer CTWR.

Front 46

What is the difference between Oracle Instance and Oracle Database?

Back 46

An instance is a memory structure while Oracle Database is a set of physical files that resides on server's disk drives.

Front 47

What are the physical files in Oracle Database?

Back 47

1-Control files
2-Data Files
3-Redo Log
4-Parameter File (PFILE and SPFILE)
5-Archive Log
6-Password
7-Oracle Net.

Front 48

What information does control files have?

Back 48

1- The name of the database
2- The names, locations and sizes of the datafiles and redo log files.
3-Information used to recover the database in case of a disk failure or user error.

Front 49

What background process updates control files?

Back 49

Check Point CKPT updates control files and also keep them synchronized.

Front 50

What dynamic performance view contains the name of control files

Back 50

V$CONTROLFILE

Front 51

Define tablespace?

Back 51

A logical storage area within the database.

Front 52

Define Data files?

Back 52

The physical storage structure behind tablespaces.

Front 53

What are the required tablespaces in Oracle 10g?

Back 53

1- System - Stores the DD tables and PL/SQL code.
2- SYSAUX - Stores segments used for database options such as Automatic Work load repository,OLAP etc.
3-TEMP - Used for large sort operations.

Front 54

What are the common table spaces in Oracle 10g

Back 54

1-TOOLS
2-USERS
3-UNDOTBS1

Front 55

What DD view can be used to find the existing tablespaces in the database?

Back 55

DBA_TABLESPACE
SQL>select * from dba_tablespaces;

Front 56

How many minimum datafiles does a table space should have?

Back 56

Atleast 1 data file for a tablespace is required.

Front 57

What DD View can be used to find out the data files in the tables space

Back 57

DBA_DATA_FILES
SQL>Select tablespace_name, file_name from DBA_DATA_FILES;

Front 58

When does a DBWn (Database Writer) writes to a database file?

Back 58

1- User's Server Process have searched too long for a free buffer when reading a buffer into the Buffer Cache.
2- The number of modified and committed but unwritten , buffers in the db buffer cache is too large.
3-Check Point event.
4-Instance is shutting down by any process other than Shutdown abort.
5-A tablespace is placed in a backup mode.
6- A tablespace is taken offline to make it unavailable or changed to READ ONLY.
7- A segment is dropped.

Front 59

Describe RedoLog files?

Back 59

When a user performs a db transaction the information needed to reproduce this transaction are recorded in redo log buffer which is ultimately written into redo log files.

Front 60

What dynamic performance view contain the information regarding redo log group?

Back 60

V$LOGFILE
SQL>select group#, member from V$LOGFILE;

Front 61

When does the log writer writes to the current redo log group?

Back 61

1-Every 3 seconds
2-User commits the transaction
3-Redo Log Buffer is 1/3 full
4-The Redo Log Buffer contains 1MB worth of redo information.
5- Before the DBWn process whenever check point occurs.

Front 62

What are the Hardware requirments for installing Oracle 10g?

Back 62

1-Memory 512 MB
2-Swap space 1 GB or two times the amount of RAM.
3-Temp space 400MB of free space in the /tmp directory of UNIX systems.
4-Free disk space 1.5GB disk space.

Front 63

What areas does OFA addresses?

Back 63

1-Naming convention for Unix file systems.
2-Naming convention for directory paths.
3-Naming convention for database files.
4-Standardized locations for Oracle-related files.

Front 64

What are the 2 environment variables get created at the time of installation?

Back 64

1-$ORACLE_BASE ("/u01/app/oracle" "D:\\Oracle" "Top level directory for Oracle on the server)
2-$ORACLE_HOME ("/u01/app/oracle/product/10/0.1" "D:\\Oracle\\ORA101"Directory into which the product will be installed)

Front 65

What OFA directory should a physical PROD database files reside?

Back 65

/u01/oradata/PROD
/u02/oradata/PROD
/u03/oradata/PROD

Front 66

What command does an installer run under Unix OS?

Back 66

runInstaller.sh

Front 67

How many tablespaces should a database must have?

Back 67

Three 3
SYS, SYSAUX and TEMP