Good Topics

Dictionary managed tablespaces
Extents are allocated according to the following storage parameters
initial
next
pctincrease
The information about used and free extents is stored in the dictionary.
Locally managed tablespaces
A 'bitmap' is stored within the tablespace. Each bit within this bitmap determines if a corresponding extent in the tablespace is free or used.
The extent sizes are either uniform or autoallocate. Hence, the following storage parameters don't make sense and are not permitted:
next
pctincrease
minextents
maxextents
default storage
Locally managed tablespaces have two advantages: recursive space management is avoided and adjacent free space is automatically coalesced.

REF CURSOR Overview
A cursor variable is a cursor that actually contains a pointer to a query result set. The result set is determined by the execution of the OPEN FOR statement using the cursor variable.
A cursor variable is not tied to a single particular query like a static cursor. The same cursor variable may be opened a number of times with OPEN FOR statements containing different queries. Each time, a new result set is created from that query and made available via the cursor variable.
REF CURSOR types may be passed as parameters to or from stored procedures and functions. The return type of a function may also be a REF CURSOR type. This provides the capability to modularize the operations on a cursor into separate programs by passing a cursor variable between programs.
We can define ref cursor with SYS_REFCURSOR built-in data type as well as creating a type of REF CURSOR and then declaring a variable of that type. SYS_REFCURSOR is a REF CURSOR type that allows any result set to be associated with it. This is known as a weakly-typed REF CURSOR.
Only the declaration of SYS_REFCURSOR and user defined REF CURSOR variable's is different. The remaining usage like opening the cursor, selecting into the cursor and closing the cursor is the same across both the cursor types.
The following is the syntax for declaring a SYS_REFCURSOR cursor variable: name SYS_REFCURSOR;
DECLARE
emp_refcur SYS_REFCURSOR;
You must perform two distinct declaration steps in order to use a user defined REF CURSOR variable:
1. Create a referenced cursor TYPE
2. Declare the actual cursor variable based on that TYPE
The syntax for creating a user defined REF CURSOR type is as follows: TYPE cursor_type_name IS REF CURSOR [RETURN return_type];
The following is an example of a cursor variable declaration. DECLARE
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
my_rec emp_cur_type;
CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno emp.deptno%TYPE
)
IS
emp_refcur SYS_REFCURSOR;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno ' ' v_ename);
END LOOP;
CLOSE emp_refcur;
END;

A VARRAY is a collection with maximum limit set. A VARRAY can hold any defined datatype. A VARRAY is a dense collection, meaning that you can only add or remove objects from the end. You define a VARRAY first as a type, and then define a variable that is the actual collection.
SQL> declare 2 type auth_var is varray(10) of 3 author.author_last_name%type; 4 a_auth auth_var := auth_var(); --Initilization 5 begin 6 a_auth.extend; 7 a_auth(1) := 'Arpit'; 8 a_auth.extend; 9 a_auth(a_auth.last) := 'Nirav';
10 a_auth.extend; 11 a_auth(a_auth.last) := 'Amit'; 10 dbms_output.put_line( a_auth(1) ' and ' a_auth(2)); 11 end; /
A VARRAY can not be used until it is initialized. In line 4 the auth_var() function actually does the initialization.
Once the array is initialized you can extend it and add elements, which we do in lines 6 through 9. We access each element using the VARRAY variable and the index number. When the PL/SQL block ends (or the array variable goes out of scope), the memory used by the array is recovered automatically by the PL/SQL engine. Unlike a cursor, you do not close a collection.




10g New Features:
Provides support for regular expressions as in Unix/Perl
Ability to receive warning when compiling the code. We can get warnings using parameter plsql_warning or DBMS_WARNING package.
BINARY_FLOAT / BINARY_DOUBLE (Native floating point data types and is alternative to NUMBER datatype)
DBMS_LOB offers support of large LOBs (between 8 and 12 terrabytes)
Handling of quotes in string (') can be done using q'! <text with quotes...>!'
ErWin

When you create a relationship between entities, ERwin automatically migrates the primary key attribute(s) of the parent entity, to the child entity. The designator (FK) indicates the name of the migrated key attribute, also known as a foreign key. This migration occurs automatically in the physical model.
In an identifying relationship (displayed using solid line), the FK migrates above the line and becomes part of the primary key of the child entity. In a non-identifying relationship (displayed using dotted line), the foreign key migrates below the line and becomes a non-key attribute in the child entity. In ERwin, a dashed line represents a non-identifying relationship.


Data warehousing questions
What is a Star Schema?
Star schema is a type of organizing the tables such that we can retrieve the result from the database quickly in the warehouse environment.

What is Snow Flake Schema?
Snowflake Schema, each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.

Differences between star and snowflake schema?
Star schema - A single fact table with N number of Dimension, all dimensions will be linked directly with a fact table. This schema is de-normalized and results in simple join and less complex query as well as faster results.
Snow schema - Any dimensions with extended dimensions are know as snowflake schema, dimensions maybe interlinked or may have one to many relationship with other tables. This schema is normalized and results in complex join and very complex query as well as slower results.

What is Difference between ER Modeling and Dimensional Modeling?
ER modeling is used for normalizing the OLTP database design. Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.

What is degenerate dimension table?
If a table contains the values, which are neither dimension nor measures is called degenerate dimensions.

Why is Data Modeling Important?
Data modeling is probably the most labor intensive and time consuming part of the development process. The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language , it can be reviewed and verified as correct by the end-users.In computer science, data modeling is the process of creating a data model by applying a data model theory to create a data model instance. A data model theory is a formal data model description. When data modelling, we are structuring and organizing data. These data structures are then typically implemented in a database management system. In addition to defining and organizing the data, data modeling will impose (implicitly or explicitly) constraints or limitations on the data placed within the structure.Managing large quantities of structured and unstructured data is a primary function of information systems. Data models describe structured data for storage in data management systems such as relational databases. They typically do not describe unstructured data, such as word processing documents, email messages, pictures, digital audio, and video. (Reference : Wikipedia)