• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key


Play button


Play button




Click to flip

67 Cards in this Set

  • Front
  • Back
What are the Oracle Family Products?
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).
What are the 5 editions of Oracle 10g?
1- Enterprise.
2- Standard(upto 4 processors).
3- Standard Edition One(upto 2 processors).
4- Personal(for individual user).
5- Lite(for mobile applications).
What are the 2 types of Meta Data views available in Oracle 10g?
1- Data Dictionary Views(arround 1300 Data Dictionary views).
2- Dynamic performance Views(approximately 350 views depending upon which feature is installed).
What types of Data Dictionary views are available in Oracle 10g?
What kind of information do you get from DBA_ DD VIEWS?
All tables in the entire database.
Reserved for user accounts that have DBA privileges.
What kind of information do you get from ALL_ DD VIEWS?
All tables owned by a particular user plus all tables to which the user has been granted access to.
What kind of information do you get from USER_ DD VIEWS?
All tables owned by a particular user
What information does the DD View DBA_TABLES contains?
Shows name and physical storage information about all the tables.
What information does the DD View DBA_USERS contains?
Shows information about all the users in the database.
What information does the DD View DBA_VIEWS contains?
Info about all the views in the database
What information does the DD View DBA_TAB_COLUMNS contains?
Shows all the names and datatypes of the table column
What information does the Dynamic Performance View V$DATABASE contains?
Information about the database itself such as database name, creation date.
What information does the Dynamic Performance View V$VERSION contains?
Version of the database
What information does the Dynamic Performance View V$OPTION contains?
Which optional component is installed in the database.
What information does the Dynamic Performance View V$SQL contains?
Information about the SQL statements that database users have been using.
Define Constraints?
A contraint allows the database designer to enforce business rules about the data stored in the database's tables and the relationships between tables.
What are the types of table constraints?
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).
Where does the constraint informations are stored?
They are stored in DD Views
Give SQL Example of adding a Primary Key?
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))
Define Segment?
A segment is defined as any entity that consumes physical storage within the database.
What are the types of segments?
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)
Define Extent?
Each Oracle segment is made up of continous chun of storage space in the database called extent.
What is the minimum and maximum size of a segment in terms of extents?
At least 1 extent per segment but a segment can have as many as 2 Billion extents.
Define Blocks?
Each extent is made up of smaller chunks of space called blocks.
What is the minimum and maximum size of a extent in terms of blocks?
The minimum size of an extent is 5 blocks.
What is the size of blocks?
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.
What is a block composed of?
It is made up of Operating System blocks whose size is determined by the operating system, the most common size is 512 bytes.
What are the types of SQL Statement?
1-Queries created using SQL
2-DML (Insert, Update and Delete)
3-DDL (Create, Alter, Drop)
4-DCL (Grant and Revoke)
What are the benefits of PL/SQL on SQL?
SQL doesnot support:
1- Condition Testing
2- Looping
3- Exception Handling.
What are the types of PL/SQL Objects?
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).
What are the categories of Oracle Server Architecture?
1- User-related processes
2- Oracle Instance (memory structure)
3- Database (physical file structure).
What is SGA?
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.
What are the components of SGA?
1-Shared Pool
2-Database Buffer Cache
3-Redo Log Buffer
4-Java Pool
5-Large Pool
6-Streams Pool
Describe Shared Pool?
Shared Pool caches the most recent SQL issued by the user.
It uses LRU (Least Recently Used algorithm)to manage the contents
Describe Database Buffer Cache?
Caches the data that has been most recently accessed by the users.
It uses LRU (Least Recently Used algorithm)to manage the contents
Describe Redo Log Buffer?
It stores information for the recovery process.
Describe Large Pool?
Caches Data for large operations such as Recovery Manager backup and restore activities and Shared Server Component.
Describe Streams Pool?
Caches the Data stored with Queued message request when Oracle Advance Queuing option is used.
What is the unit of SGA memory space?
Granules can be 4MB, 8MB or 16MB Sizes.
How many types of Parameter files are their?
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.
What are the differences between PFILE and SPFILE?
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.

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.
What dynamic performance view can be used to examine SGA?
SQL>select * from V$SGA;
For looking into the SGA Components.
SQL>select components, current_size from v$sga_dynamic_components;
What are the types of Oracle Background Process?
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).
What are the required oracle background processes?
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)
What are the optional oracle background processes?
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.
What is the difference between Oracle Instance and Oracle Database?
An instance is a memory structure while Oracle Database is a set of physical files that resides on server's disk drives.
What are the physical files in Oracle Database?
1-Control files
2-Data Files
3-Redo Log
4-Parameter File (PFILE and SPFILE)
5-Archive Log
7-Oracle Net.
What information does control files have?
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.
What background process updates control files?
Check Point CKPT updates control files and also keep them synchronized.
What dynamic performance view contains the name of control files
Define tablespace?
A logical storage area within the database.
Define Data files?
The physical storage structure behind tablespaces.
What are the required tablespaces in Oracle 10g?
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.
What are the common table spaces in Oracle 10g
What DD view can be used to find the existing tablespaces in the database?
SQL>select * from dba_tablespaces;
How many minimum datafiles does a table space should have?
Atleast 1 data file for a tablespace is required.
What DD View can be used to find out the data files in the tables space
SQL>Select tablespace_name, file_name from DBA_DATA_FILES;
When does a DBWn (Database Writer) writes to a database file?
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.
Describe RedoLog files?
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.
What dynamic performance view contain the information regarding redo log group?
SQL>select group#, member from V$LOGFILE;
When does the log writer writes to the current redo log group?
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.
What are the Hardware requirments for installing Oracle 10g?
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.
What areas does OFA addresses?
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.
What are the 2 environment variables get created at the time of installation?
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)
What OFA directory should a physical PROD database files reside?
What command does an installer run under Unix OS?
How many tablespaces should a database must have?
Three 3