Real Application Cluster vs Replication

Oracle Real Application Cluster (RAC)
Real Application Cluster (RAC) is 2 servers or more, each other work together to handle 1 data.
2 Oracle Database Instances with one external data shared storage. So 2 Database Servers is not stand-alone and very different from the Replication.

If the problem occurs in a 1 server, the other server will take over automatically without intervention from the Administrator / Oracle DBA.

RAC aimed for High Availability and High Performance.

Replication
Replication is 2 servers , the 1 is active server and other is standby server (works only receive data replication). So do not work together.

If the problem occurs in the Main Server , it must be taken over /switch to standby server manually, even also need to change ORATNS.ORA on each client PC.

Replication aimed for High Availability and not for High Performance because it only take advantage of performance of the main server.

Oracel to_date function

The Oracle to_date function is used to change a test string (or variable) into an internal date format. The to_date function is usually used in SQL when storing into the database.

Insert into mytab (date_col) values to_date(string,format);

Examples of the to_date function might include:

• to_date('10-12-06','MM-DD-YY')
• to_date('jan 2007','MON YYYY')
• to_date('2007/05/31','YYYY/MM/DD')
• to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI')
• to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')
• to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss')
• to_date('022002','mmyyyy')
• to_date('12319999','MMDDYYYY')
• to_date(substr( collection_started,1,12),'DD-MON-YY HH24')
• to_date('2004/10/14 21', 'yyyy/mm/dd hh24')
• TO_DATE(First_Load_Time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60)

Creating a view

View is a syntax or script of query oracle syntax stored in database.
Basic syntax is :

CREATE or REPLACE VIEW view_name AS select column1,column2,... from table_name;

example :

CREATE or REPLACE VIEW v_customer AS select id_custmer,name from customer;

Modify Table Structure

Oracle basic syntax to modify table structure

Syntax 1
add a column into existing table

ALTER TABLE table_name
ADD column_name datatype;

example :
ALTER TABLE customer
ADD phone_number varchar(20);


Syntax 2
modify a data type of a field of existing table

ALTER TABLE table_name
MODIFY (column_name datatype);

example :
ALTER TABLE customer
MODIFY (name varchar(100));

create new table

Syntax 1

creating new table
----------------------------
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
...);

example :
CREATE TABLE customer (
id number,
name varchar2(50));


Syntax 2
creating table from other existing table,
the structure of new table copied from source table.
----------------------------
CREATE TABLE table_name as select * from existing_table;

example :
CREATE TABLE customer2 as select * from customer;

Add column into existing table

Syntax 1
To add a column into an existing table :

ALTER TABLE table_name ADD column_name column-definition;
Contoh :
ALTER TABLE customers ADD name varchar2(50);

Syntax above adding column name into customers table with data type varchar2 dan length 50.
-------------------------------------------------
Syntax 2
To add columns into existing table:
ALTER TABLE table_name
ADD (
column_1 column-definition,
column_2 column-definition,
...
column_n column_definition );

Contoh:
ALTER TABLE customers
ADD ( name varchar2(50),
address varchar2(100),
country varchar2(45) );

Syntaxs above adding three fields name, address, and country into customers table.

Rename table name

Basic syntax for renaming table name

ALTER TABLE table_name RENAME TO new_table_name;

example :
ALTER TABLE suppliers RENAME TO customers;

this script will change table name from "table_name" to "new_table_name"