Arsitektur Database Oracle

Kata database dalam frasa “arsitektur database Oracle” seharusnya adalah Database Management System (DBMS). Untuk penyederhanaan penyebutan, Database Management System memang sering cukup disebut database saja.
Secara umum komponen DBMS Oracle terdiri atas memory, proses, dan file-file. Lebih jauh lagi, komponen-komponen tersebut dikelompokkan sebagai berikut:
Instance – Memory yang disebut sebagai System Global Area (SGA), terdiri atas: Shared Pool (Libary Cache and Data Dictionary Cache), Database Buffer Cache, Redolog Buffer Cache, Java Pool, Large Pool. – Back ground process: PMON, SMON, DBWR, LGWR, CKPT, dan lain-lain
Database – Datafile – Control file – Redo log file
Komponen lain – process: Server Process, user process – memory: Program Global Area (PGA) – File: Archived log, parameter, dan password file
Struktur Memory Oracle terdiri dari :

1. SGA (Shared Global Area):  Merupakan area memory yang menyimpan data dan control information untuk Instance. SGA sendiri dishared oleh server dan background process.
2. PGA (Program Global Area): Merupakan area memory yang menyimpan data dan control information untuk setiap server proses. PGA inilah yang meng-handle server proses. Setiap proses memiliki satu PGA. PGA bersifat Private.

SGA terdiri dari :
- Shared Pool:  Menyimpan semua aktivitas pengguna agar tidak terjadi parsing. contoh : sebuah SELECT statement akan dieksekusi. Statement tersebut disimpan ke cache Shared Pool. Apabila statement tersebut ingin dieksekusi kembali, tidak perlu menyimpan ke cache Shared Pool (parsing) kembali melainkan ambil dari Shared Pool dan langsung eksekusi
- Streams Pool : digunakan untuk menyimpan data untuk kepentingan replikasi database.
- Large Pool : Memiliki kapasitas yang besar dan Biasa digunakan untuk recovery, backup, restore. Large Pool menyimpan session information ketika oracle dalam mode shared.
- Java Pool : Memiliki fungsi hampir sama dengan Shared Pool tetapi menyimpan eksekusi yang menggunakan bahasa atau aplikasi Java.
- Database Buffer Cache : Menyimpan hasil eksekusi dari Shared Pool. Hasil eksekusi yang disipan hanya yang bersifat READ ONLY saja. DB Buffer Cache menggunakan LRU (Least Recent Use) sehingga akan menghapus data yang baru masuk apabila sudah full.
- Redo Log Buffer : Menyimpan semua eksekusi yang memodifikasi data database. Hasil modifikasi ini akan disimpan dalam redo log buffer saat user melakukan commit. Apabila user belum melakukan commit, perubahan data yang telah dilakukan tidak dapat dilihat atau diakses oleh user yang lain.

SQL *PLUS

TABLESPACE

Untuk menyimpan obyek-obyek database dibutuhkan suatu ruang memori yang disebut TABLESPACE. Untuk mengakses tablespace dibutuhkan sebuah account user. Berikut ini perintah SQL untuk membuat tablespace, user dan perintah untuk manipulasi tabel.

Melihat tablespace
select * from user_free_space;
Membuat tablespace
SQL>Create tablespace nama-tablespace datafile ‘nama-datafile.dat’ size 1M autoextend on next 500K maxsize 2M;
Mengubah ukuran tablespace
SQL>alter database datafile ‘nama-datafile.dat’ resize 2M;
Menambah ukuran tablespace dengan cara menambahkan data file baru
SQL>alter tablespace dataku add datafile ‘nama-datafile.dat’ size 2M;
Menghapus table space
SQL>drop tablespace nama-tablespace including contents;

Setelah Tablespace siap, maka langkah selanjutnya membuat account baru. Account ini akan anda pergunakan selama praktikum. Account yang sudah ada ketika instalasi selesai adalah SYS, SYSTEM dan SCOTT. Untuk membuat account baru kita mempergunakan user SYS dan SYSTEM. Pergunakan tablespace yang tadi telah anda buat untuk menyimpan data anda selama praktikum.
Membuat user baru dan dengan tablespace yang dipakai misalnya dtpraktek.
SQL>create user nama_user identified by password-user default tablespace dtpraktek;
Parameter nama_user adalah nama login, dan password-user adalah password user yang dipilih. Misalnya dipilih untuk nama_user user01.
Berikan hak akses user pada database. Misalnya hak akses grant dan
resource.
SQL> grant connect to user01;
SQL> grant resource to user01;
Langkah  berikutnya,  lakukan  sambungan  dengan  server  dengan
mempergunakan user_name yang sudah dibuat tadi.

SQL> connect user01
Enter password: ******
Connected.

Saat ini sudah tersambung dengan server dengan mempergunakan user01. Buat tabel mahasiswa yang memiliki struktur nim varchar(5), nama varchar(25), alamat varcahr(30).
SQL> create table mahasiswa (nim varchar(5), nama varchar(25), alamat varchar(30));
Untuk melihat tabel yang ada dalam tablespace mempergunakan perintah :
SQL> select * from tab;
Untuk  melihat  struktur  tabel  dapat  dilakukan  dengan  mempergunakan
perintah :
SQL>describe mahasiswa;
Perintah alter berguna untuk melakukan perubahan tabel diantaranya merubah tipe field, menambah field baru dan merubah nama tabel.
Merubah tipe field tabel :
SQL>alter table mahasiswa modify (nim number(5));
Menambah field tabel :
SQL>alter table mahasiswa add (usia number(2));
Mengganti nama tabel :
SQL>alter table mahasiswa rename to mhs;
Untuk menambahkan data kedalam tabel, pergunakan perintah insert
SQL>insert into mahasiswa values(1,’Sumanto’,’Sleman’);
Data  yang  sudah  disimpan   dalam  tabel  dapat   diedit  dengan
memperguanakan perintah update.
SQL>update mahasiswa set nama = ‘Sumanto’ where nim=1;
Untuk keperluan penghapusan data, pergunakan perintah :
SQL>delete from mahasiswa where nim=1;

CONSTRAINT

Constraint (konstrain) adalah aturan yang diberikan pada suatu tabel supaya data yang dimasukkan lebih terjamin validitasnya.
Memberi konstrain pada saat membuat tabel :
SQL>create table mahasiswa(nim char(5) constraint pnim primary key, nama char(20) constraint nnama not null, alamat char(30));
Memberi konstrain pada tabel yang sudah ada :
SQL>alter table mahasiswa add constraint pnim primary key (nim);
SQL>alter table mahasiswa modify (nama char(20) constraint nnama not null);
Menghapus konstrain :
SQL>alter table mahasiswa drop constraint nnama;

ORDER BY

Klausa ORDER BY digunakan untuk mengurutkan data yang di-query. Klausa ORDER BY diikuti dengan nama kolom atau kolom alias kemudian diikuti klausa jenis urutan yang kita inginkan. Jenis urutan bisa ASC (ascending) atau DESC (descending). Sebagai ilustrasi, tampilkan semua data dengan cara diurutkan ascending berdasarkan nama mahasiswa.

SQL> select * from mahasiswa order by nama;
SQL> select * from mahasiswa order by nama desc;

WHERE

Klausa LIKE WHERE digunakan untuk membatasi query menurut kriteria tertentu. Kriteria ini didefinisikan menggunakan bentuk perbandingan. Sebagai ilustrasi, tampilkan data mahasiswa yang berasal dari Jogjakarta.
SQL> select * from mahasiswa where alamat = ‘Jogjakarta’;

LIKE

Klausa LIKE digunakan untuk membatasi query menurut nilai tertentu. Nilai ini merupakan sebagian dari nilai suatu field. Sebagai ilustrasi, akan ditampilkan data mahasiswa yang memiliki nama yang nama akhirnya mengandung huruf “ni”.

SQL> select * from mahasiswa where nama like ‘%ni’;

VIEW

View merupakan objek database seperti tabel namun secara fisik tidak memiliki data. Isi data view merupakan hasil query dari sebuah atau beberapa tabel. View disebut juga virtual tabel karena strukturnya seperti tabel tapi tidak memiliki data.

Untuk membuat view dengan perintah CREATE VIEW seperti contoh

berikut :

SQL> create view mhs_jogja as (

select * from mahasiswa
where alamat like 'J%');

View created.
Untuk melihat view yang telah dibuat, diberikan perintah untuk melihat

view sebagaimana perintah untuk melihat data dalam tabel.

SQL> select * from mhs_joigja;

Struktur field view dapat dilihat dengan mempergunakan perintah :

SQL> describe mhs_jogja;

Data yang ditampilkan dalam view dapat diupdate, dengan catatan tidak terdapat constraint yang menolak perubahan data. Sebagai contoh, untuk

merubah data dalam view dipergunakan perintah :

SQL> update mhs_jogja

set nama = 'Abdullah'

where nim = '1';

row updated.

Jalankan perintah berikut untuk melihat perubahan yang terjadi akibat perintah diatas.

SQL> select * from mhs_jogja;

Untuk kepentingan merubah definisi view dapat dipergunakan perintah

berikut :

SQL> create or replace view mhs_jogja as

(select * from mahasiswa);

View dapat dihapus dengan perintah DROP

SQL> drop view jml_sks;

HAK AKSES Pada Oracle

Cara Membuat User pada Oracle Database
Pengertian User pada Database Oracle  
Dalam terminologi Oracle, user merupakan seseorang yang dapat melakukan koneksi ke database (jika mendapatkan hak akses yang cukup) yang dapat memiliki objectnya sendiri dalam database.  
User dalam Oracle harus diciptakan.
USER umumnya diciptakan oleh seorang yang memiliki hak akses sebagai DBA.
DBA merupakan level privilege tertinggi dalam Oracle.
Makna privilege adalah hak atas sesuatu. 
Contoh hak akses : 
Hak untuk melakukan koneksi ke database.
Hak untuk menciptakan tabel.
Hak untuk menambah data pada tabel, dan lain-lain.
B.  Menciptakan User pada Oracle  
Dalam praktek menciptakan user pada oracle database, kita harus terlebih dahulu memastikan bahwa pada komputer kita sudah terinstall oracle database untuk bisa mengetikan perintah atau query database yang kita inginkan, jika anda belum menginstall oracle database anda bisa klik link download di bawah ini :
Untuk membuat user baru digunakan perintah CREATE USER.
CREATE USER user
IDENTIFIED BY password;
dimana:
user adalah nama user yang akan diciptakan.
password adalah password dari user.
Contoh :  Kita akan menciptakan user dengan nama “toni” dengan password “nagamerah”, maka ketikan perintah berikut ini pada SQL Command Line.

CREATE USER toni
IDENTIFIED BY nagamerah;

Hak akses User pada Oracle Database dikenal dengan User Privileges. Privilege dibagi menjadi  dua jenis yaitu :
System Privilege System Privilege adalah sebuah hak untuk  user agar bisa mengeksekusi perintah ke databasenya. Ada tujuh system privilege yang disediakan oleh ORACLE :
CREATE TABLE = Hak untuk membuat table
CREATE ANY TABLE = Hak untuk membuat table di user lain
CREATE  SEQUENCE = Hak untuk membuat sequence (Urutan)
CREATE VIEW = Hak untuk membuat View
CREATE INDEXTYPE = Hak untuk membuat Index
CREATE PROCEDURE = Hak untuk membuat Procedure
CREATE SESSION = Hak untuk membuat Session
Object Privilege Sedangkan Object Privilege adalah sebuah hak untuk user agar bisa mengakses object object pada database. Ada tujuh object privilege yang disediakan oleh ORACLE :
SELECT = Hak untuk melihat data pada table
UPDATE = Hak untuk mengubah data pada table
INSERT = Hak untuk menambahkan data pada table
DELETE = Hak untuk menghapus data pada table / Object
ALTER = Hak untuk merubah data pada table / Object
INDEX = Hak untuk membuat index
REFERENCES = Hak untuk membuat foreign key pada kolom table
Perintah membuat user yang paling sederhana adalah (contoh, saya akan membuat user dengan nama ROHMAD dan passwordnya PASSROH): SQL> conn system SQL> create user ROHMAD identified by PASSROH;
Karena tidak di-specify, maka atribut-atribut yang lainnya memakai nilai default. Untuk melihatnya lihat di view dba_users SQL> select * from dba_users where username='ROHMAD'; Ini hasilnya: USERNAME: ROHMAD ACCOUNT_STATUS : OPEN EXPIRY_DATE : DEFAULT_TABLESPACE : USERS TEMPORARY_TABLESPACE : TEMP PROFILE : DEFAULT INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP

Berikut ini contoh membuat user dengan men-specify default tablespace, quota pemakaian di tablespace, dan temporary tablespace (ini contoh yang paling sering digunakan): SQL> CREATE USER rohmad IDENTIFIED BY passroh DEFAULT TABLESPACE users QUOTA unlimited ON users QUOTA 500K ON data_ts TEMPORARY TABLESPACE temp;
Alter (mengubah) user
Semua atribut user bisa diubah (alter) kecuali username itu sendiri. Secara umum perintah alter sama dengan create, hanya mengganti kata create menjadi alter. Contoh: SQL> alter user ROHMAD identified by PASSROH2;
Grant User
Setelah user dibuat, user tersebut tidak bisa melakukan koneksi sebelum diberi grant (hak) untuk connect ke database. SQL> conn rohmad/passroh2 ERROR: ORA-01045: user ROHMAD lacks CREATE SESSION privilege; logon denied
Berikut ini perintah untuk memberi grant ke user agar bisa connect ke database SQL> conn system; SQL> grant connect to rohmad;
Setelah itu, dengan user rohmad tersebut, mari kita coba membuat table SQL> conn rohmad/passroh2 SQL> create table tab_test (no number); ERROR at line 1: ORA-01031: insufficient privileges
OO… ternyata user rohmad belum punya priviledges untuk membuat table. Coba beri privilege ke user rohmad agar bisa membuat tabel SQL> conn system; SQL> grant create table to rohmad;
Sekarang buat table SQL> conn rohmad/passroh2 SQL> create table tab_test (no number); ERROR at line 1: ORA-01950: no privileges on tablespace 'USERS'
OO… ternyata error. Walaupun default tablespace untuk user ROHMAD adalah tablespace USERS, ternyata ROHMAD masih belum bisa membuat table (menulis) di tablespace USERS. Beri quota ke user ROHMAD, bisa unlimited ataupun di-specify besarannya. Contoh, beri quota unlimited: SQL> conn system; SQL> alter user rohmad quota unlimited on USERS;
Coba lagi, dan berhasil … SQL> conn rohmad/passroh2 SQL> create table tab_test (no number); Table created.
Agar bisa membuat index, user rohmad harus diberi grant. Demikian juga untuk bisa membuat view. Wow… dapat anda bayangkan, berapa banyak privilege yang mesti gita beri (grant)? Untuk mengatasi itu, Oracle membuat role. Role berisi beberapa (banyak) privilege. Contoh, role RESOURCE berisi grant untuk membuat table, membuat index, quota unlimited di default tablespace, dan lain-lain.
Berikut ini perintah untuk memberi hak (grant) role RESOURCE ke user ROHMAD SQL> conn system SQL> grant RESOURCE to rohmad;
Umumnya, cukup dengan memberi grant CONNECT dan RESOURCE ke user yang baru dibuat, user tersebut sudah bisa melakukan banyak pekerjaan. SQL> create user ROHMAD identified by PASSROH; SQL> grant connect to rohmad; SQL> grant RESOURCE to rohmad;
Menghapus User SQL> drop user rohmad;
Kalau user tersebut mempunyai object (misalnya tabel), maka akan muncul error berikut ORA-01922: CASCADE must be specified to drop 'ROHMAD'
Kalau begitu, tambahkan parameter CASCADE untuk sekaligus menghapus semua object yang dimiliki user tersebut SQL> drop user rohmad CASCADE;