SPOOL COMMAND

‘spool … ’ command is used to send queries and their results to a file. This can be said, it is like a screen shot format of the commands to the file. You have to spool on and spool off between which executions to store. But there are two different output files when spooling in different editors, for sqlplus command line and for pl/sql developer. After connecting to the database:

* In sqlplus command line:

SQL> spool on
SQL> spool c:\spool_here.txt
SQL> select count(*) from user_objects;
COUNT(*)
———-
71

SQL> spool off

–The output in the spool_here.txt file is:

SQL> select count(*) from user_objects;
COUNT(*)
———-
71

SQL> spool off

* In pl/sql developer command window:

SQL> spool c:\spool_here.txt
Started spooling to c:\spool_here.txt

SQL> select count(*) from user_objects;
COUNT(*)
———-
71

SQL> spool off
Stopped spooling to c:\spool_here.txt

–The output in the spool_here.txt file is:

COUNT(*)
———-
71

According to this test situation, it can be said that, pl/sql developer only takes the outputs of the queries while sqlplus command window takes all the written queries and their outputs.
In addition to this, you cannot see any output in the output files until ‘spool off ’ command executes.

DATA INTEGRITY

In the applications, we want to prevent insertion, manipulation or deletion of invalid data to satisfy data integrity. So, to provide acceptable information, we have to apply some rules.

Data integrity is helpful for enforcing types of rules associated with the database. For instance, validity of data, manipulation and navigation of data in the tables correctly. Let’s explain the rules applied to table columns for data integrity.

First one is Null Rule, which is defined on a single column and controls if the value in this column can contain null values while insertion or deletion. Second one is Unique Column Rule, it can be defined on one or more columns. It controls if the column (s) contain unique values. According to this rule, values of these columns can contain null values. The Third one is Primary Key Rule, that is defined as a key value on a column or set of columns. It satisfies each row in the table can be uniquely defined. The difference to the Unique Column Rule is, none of the values can contain null values in Primary Key Rule. The other one is Referential Integrity Rules which are used frequently. The definition for this rule is the necessity of existence of a column or set of columns also in the referenced table. It controls the data manipulations on the referential values. The last one is Complex Integrity Checking. This is a user defined rule that checks insertion, deletion and update operations on a column or more than one columns.

Oracle enforces data integrity by definitions. Integrity constraints and triggers help these rules to be applied to the database.

ORACLE DATA MINING (ODM)

As a general look, data is raw information, however information is the result of processing, manipulating and organizing data which forms knowledge. Real-world applications consist of complex data.

Data mining means extracting information from large data sets and databases. Market Basket Analysis is an example of data mining used in sales : If men buy beer, diaper sales also increases.
Data mining is embedded in Oracle database and is one of the area of data warehouse. Data is stored in tables although there are many different types of information and does not leave database while the operation taking place. Also, the number of columns you mine is not important in Oracle Data Mining (ODM).

There should be a problem waiting to be solved. In addition to this, there are some basic steps for the solution. Data gathering is the first step. The next step will be to choose one of the data mining algorithms. Always remember, there is no one perfect algorithm for problems. The choice of algorithm can change according to the type of data and problem. Data should be filtered and normalized before used in algorithms.

Humans and machines are different from each other. Although one of them has ability to sense, identify and think , the other one behaves through what he learned. There are kinds of functions and algorithms required by Oracle Data Mining. Data mining applies machine learning concepts to data. There are two types of Data Mining functions. These learnings are generated from machine learning area. One of them is supervised data mining which is direct learning, making prediction. The second one is unsupervised learning which is used for descriptive uses and non-directed.
The conspicuous algorithms used in data mining are Naive Bayesian, Decision Tree, Clustering (k-means) ect.

TEMPORARY TABLES

Oracle can hold session-private data which means data is stored for the duration of transaction or session. Two types of temporary tables can be created with “create global temporary table…” statement, transaction-specific or session-specific. These names are the data existence for the temporary tables. For transaction-specific tables, data is stored during the transaction, or for session-specific transaction during the session. The rows are private to the session in temporary tables to modify its own data.

There are lots of characteristics like permanent tables. Temporary tables can be indexed and analyzed, but indexes created on temporary tables will be temporary too. Views and triggers can be created on temporary tables. Also, export and import utilities can be used with temporary tables.

In Oracle, data changes are stored in redo logs. But, for temporary tables, DML statements <insert , update, delete> do not generate redo logs. However, temporary tables generate a minimum amount of redo. They are not stored in rollback segments.

Oracle allocate temporary segments for the user needs. Data are deallocated at the end of the session or transaction.

The syntax of temporary tables is “create global temporary table…” .

The statement “… on commit delete rows” is used to specify that rows are only visible within the transaction.

“…on commit preserve rows” is also used to specify that rows are visible for the session.

A note from High Performance Tuning by Guy Harrison:

“Take advantage of temporary tables when your Pl/Sql needs to store data that does not need to persist beyond the session or the transaction”.

I will explain the difference between permanent tables and temporary tables with a simple example:

*The first part is about permanent tables.

First, we connect to the database:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

Then, create a table named test with a parameter id type number:

SQL> create table test (id number);
Table created

After that, insert a row with a value of id 1:

SQL> insert into test values(1);
1 row inserted

When we show the table’s contents:

SQL> select * from test;
ID
———-
1

At the end of these executions, disconnect from the database:

SQL> disconnect;
Not logged on

Again we connect to the database, and show the data in our test table:

SQL> connect hr/hr;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> select * from test;
ID
———-
1

It seems that, Oracle hold data in permanent tables until you delete the row, truncate or drop the table.

*The second part of the example is about temporary tables.

We drop the permanent table and create a new one that is temporary:

SQL> drop table test purge;
Table dropped

Following this, create a temporary table which hold the data during the session:

SQL> create global temporary table test (id number) on commit preserve rows;
Table created

SQL> insert into test values (2);
1 row inserted

SQL> select * from test;
ID
———-
2

Again disconnect from the database:

SQL> disconnect;
Not logged on

Connect once more:

SQL> connect hr/hr;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

Finally, as select the data from the test table, there is no data to be stored:

SQL> select * from test;
ID
———-

 

In conclusion, temporary tables are useful in applications which you need to store data for transaction or session level.

‘DEFAULT’ CLAUSE IN ‘CREATE TABLE’ STATEMENT

When a table will be updated or any data is inserted into the table, a default value can be used. At the beginning, the column’s value is set to value in create table statement with default clause. Also, the data type of the expression must match with the data type of the column It will be clear with a simple example.

First , a table named default_tbl is created without a default value in its columns:

SQL> create table default_tbl (x integer, y varchar2(10));
Table created

After creating the table, we insert a row, which its x column has a value 1:

SQL> insert into default_tbl(x) values (1);
1 row inserted

When we select the table’s data to show, x column has the value 1, but y column is null:

SQL> select * from default_tbl;
X                             Y
————– ———-
1

Let’s drop our table and create again but now with a default clause for y column:

SQL> drop table default_tbl purge;
Table dropped

SQL> create table default_tbl (x integer, y varchar2(10) default ‘y’);
Table created

Once we insert a row with an x column has a value 1:

SQL> insert into default_tbl(x) values (1);
1 row inserted

Finally, select table clause shows that table has a row with a y column value ‘y’ which we determined in create table statement:

SQL> select * from default_tbl;

X                         Y
————- ———-
1                         y

HEAP ORGANIZED TABLES – OBJECT TABLES

In Oracle, tables are the basic storage units. There are several types of database tables. While designing an application in the database, you should use the appropriate table type to store data for necessities. Each type of table has different characteristics to be used in different applications.

Heap Organized Table:

The default table type in Oracle is heap table. If you use “create table” clause normally, you create a standard database table. Exp:

SQL> create table test (id integer);
Table created

If you want to create another type of table, it should be denoted in create table statement.
“Heap” name comes from its heap-like management. When data is added to the table, the first free space that data can fit is used. As data is removed from table, insert and update statements can use this freed spaces.

Object Table:

This type of table is created based on an object type, not collection of columns. It is generally used in special cases. The logic depends on object-oriented programming. “create table ” statement for object tables is:

create table test of x_type;

An example from Thomas Kyte-Expert one-on-one book :

SQL> create or replace type address_type
2 as object
3 ( city varchar2(30),
4 street varchar2(30),
5 add_state varchar2(2),
6 zip number
7 )
8 /
Type created

SQL> create or replace type person_type
2 as object
3 ( name varchar2(30),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created

SQL> create table people of person_type
2 /
Table created

SQL> desc people;

Name Type Nullable Default Comments
———— ———— ——– ——- ——–
NAME VARCHAR2(30) Y
DOB DATE Y
HOME_ADDRESS ADDRESS_TYPE Y
WORK_ADDRESS ADDRESS_TYPE Y

As you can see from the example, two objects are created. One of them is address_type and the other one is person_type. After that, a table named people is created based on these objects. This table has type person_type, also pay attention to the person_type object. It includes two attributes which has types address_type. The table people has four columns , two of them are object types.

Like in C structure types, C++ or Java class types , Object types are good structures for representing objects to use in the tables, instead of specifying the columns of the object again.
But according to heap tables, object tables allocates more spaces.

Remember that, there is no one truth. Performance depends on the application.

DROP TABLE … PURGE

The aim to use “drop table” command is to remove a table and all its data from the database. For example, let’s create a table named table_name :

SQL> create table table_name(id varchar(5));
Table created

If we decide to move the table:

SQL> drop table table_name;
Table dropped

But this execution moves the table, that we want to drop, to the recycle bin .We can see the contents of user’s recycle bin:

select * from recyclebin;

SQL> select object_name from recyclebin;
OBJECT_NAME
————————————————-
BIN$uCbv7uf/R/WSU6L3mWI1uQ==$0

After you drop the table Oracle will return an error:

SQL> select * from table_name;
select * from table_name
ORA-00942: table or view does not exist

If you accidentally drop the table, you can retrieve the oldest version of the table from the recycle bin:

SQL> flashback table table_name to before drop;
Done

SQL> select * from table_name;
ID
—–

But if you want to drop the table entirely and release the space, you should use “purge” clause. While , the table will be not moved to the recycle bin . You cannot recover the dropped table anymore.

SQL> drop table table_name purge;
Table dropped

On the other hand, you can use the following steps:

SQL> drop table table_name;
Table dropped

SQL> purge table table_name;
Done

Another way to purge table is:

SQL> drop table table_name;
Table dropped

SQL> select object_name from recyclebin;
OBJECT_NAME
————————————————–
BIN$KIkRl/BNTzmEdg9BW4Z3Pg==$0

SQL> purge table “BIN$KIkRl/BNTzmEdg9BW4Z3Pg==$0″;
Done