Oracle 10G Chapter 1 Part Ii

Front 1

Define Constraints?

Back 1

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 2

What are the types of table constraints?

Back 2

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 3

Where does the constraint informations are stored?

Back 3

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

Front 4

Give SQL Example of adding a Primary Key?

Back 4

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 5

Define Segment?

Back 5

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

Front 6

What are the types of segments?

Back 6

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 7

Define Extent?

Back 7

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

Front 8

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

Back 8

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

Front 9

Define Blocks?

Back 9

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

Front 10

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

Back 10

The minimum size of an extent is 5 blocks.

Front 11

What is the size of blocks?

Back 11

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 12

What is a block composed of?

Back 12

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

Front 13

What are the types of SQL Statement?

Back 13

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

Front 14

What are the benefits of PL/SQL on SQL?

Back 14

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

Front 15

What are the types of PL/SQL Objects?

Back 15

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 16

What are the categories of Oracle Server Architecture?

Back 16

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