Oracle Storage and High water Mark

Lets understand how data is stored in Oracle ...

Each table in oracle is made up of a number of extents and each extent is made up of a number of blocks. 

ie Table = x number of extents

  extent = y number of blocks

 segment = set of extents

A word about high water mark

HWM: Its divides segments into used and unused 

Blocks below HWM contain data and those beyond HWM are free.

DATA

_____________________________HWM__________________________

FREE BLOCKS

Resetting HWM:

Lets say that you fill your table with 4 million records. As a result, you fill in several extents. Now lets delete half of these records. Now your table still has those extents allocated but lots of them are empty. When we run a query on this table with a WHERE clause, all the extents are scanned including the empty ones which is obviously a performance overhead.

So to reset your HWM, use this statement ALTER TABLE MYTABLE MOVE TABLESPACE USERS.

Note: When data is inserted , HWM is automatically adusted. 

           When data is deleted, HWM is not adjusted automatically.

DELETE * v/s TRUNCATE

TRUNCATE resets the HWM, DELETE doesn't.

Global Temporary Tables

CREATE GLOBAL TEMPORARY TABLE was introduced in Oracle 8i.The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction while for session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data.

Transaction specific table:

CREATE GLOBAL TEMPORARY TABLE T_TEMP (
 column1  NUMBER,
 column2  NUMBER
) ON COMMIT DELETE ROWS;

Session Specific table

CREATE GLOBAL TEMPORARY TABLE T_TEMP
(
 column1  NUMBER,
 column2  NUMBER
) ON COMMIT PRESERVE ROWS;

Features:

Data in temporary tables is automatically deleted at the end of the database session,, no matter what happens.
If the TRUNCATE statement is carried out on a temporary table, only the session specific data is truncated. Other sessions are not affectd.
We can perform DDL commands like ALTER TABLE, DROP TABLE etc only when no session is currently bound to it. A session gets bound to a temporary table when an INSERT is performed on it. The session gets unbound by a TRUNCATE, at session termination, or by doing a COMMIT or ABORT for a transaction-specific temporary table.

ORACLE - SPOOL COMMAND

What is SPOOL ?
Spool Command in ORACLE is used to transfer data from oracle tables into other files.

How to SPOOL from ORACLE in CSV format ??

Set echo off;
Set define Off;
Set feedback Off;
Set serveroutput On;
SET PAGESIZE 0
SET LINESIZE 1000
Spool c:\file.csv

SELECT EMPNAME||','||EMPID||','||EMPSAL FROM EMPLOYEE;

Spool Off;
Set define On;
Set feedback On;

Fiddling with DATES

1. Selecting current date
SELECT SYSDATE FROM DUAL;

2. Adds one day to current date
SELECT SYSDATE+1 FROM DUAL;

3. Adding months
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;

4. TRUNC
It truncates the time part from the date TRUNC(SYSDATE).

5. MONTHS_BETWEEN( date1, date2 )
Gives the number of months between two dates.

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE VS DBMS_SQL
EXECUTE IMMEDIATE provides better performance than DBMS_SQL.
EXECUTE IMMEDIATE" was added in Oracle 9i. DBMS_SQL package was used to execute
dynamic sql queries in earlier versions.

Using EXECUTE IMMEDIATE

1.
begin
execute immediate 'SQL Statement';
end;

2.To retrieve value from a dynamic statement
declare
var varchar2(20);
begin
execute immediate 'select count(*) from employee'
into var;
dbms_output.put_line(var);
end;

In case of static sql statement we use select count(*) into var from employee;

3.EXECUTE IMMEDIATE doesn't commits a DML transaction.An explicit commit should be done.

4.Using variables with EXECUTE IMMEDIATE

x number := 10000;
execute immediate 'select count(*) from employee where salary > '||x into var;

Display text using file in GTK View

Here's a simple code to extract data from a text file and display it in a View.

FILE *fp; // FILE pointer
char buff[100]; // To hold text extracted from FILE

GtkWidget *det ;
GtkWidget *view1=NULL ; // View used to display text (initialized to NULL)

view1=lookup_widget(GTK_WIDGET(widget),"view_tm");

/* "view_tm" is the view name given using GLADE
widget (GtkWidget *) -> This pointer is passed automatically when an event takes place */

fp=fopen("file_to_display.txt","r");

while(!feof(fp))
{
fgets(buff,60,fp);

det=gtk_list_item_new_with_label (buff);

gtk_container_add (GTK_CONTAINER (view1), det);

}
/* gtk_list_item_new_with_label ()
Creates a new GtkListItem with a child label containing the given string.
label :
the string to use for the child label.
Returns :
a new GtkListItem with a child GtkLabel with the text set to label.
*/


gtk_widget_show(det);
fclose(fp);


}

Glade Interface and GTK

A word about Glade Interface Designer?
Well !! If you want to create Visual C++ like graphical application , you can use Glade. It comes as default installation with Fedora 6,7 ( i dun know abt other versions n distros). All you need to do is select and drag a couple of widgets (see previous post to know about widgets) and choose a layout. It can be used with any desktop environment in Linux, as long as the gtk+ and/or gnome libraries are installed. Glade can be downloaded from glade.gnome.org.

Other facts :

• Glade develops the GUI and associated code
• It creates empty callbacks and signal handlers to link the frontend GUI with the backend of the application
• Glade doesn't develop the 'backend' of your application

Glade is not a complete IDE, in other words it does not include a compiler, editor or debugger. It is used in conjunction with these, for example with gvim as the editor, gcc as the compiler and gdb as the debugger. There are several IDEs for Linux that work with, or attempt to work with Glade. Anjuta is one.

GTK + :
After creating the interface, we need to program it for responses received from users, this part is done by GTK- GNU ToolKit. The GTK+ library is responsible for drawing the windows, dialogue boxes and buttons in the GIMP, Pidgin and hundreds of other programs.