IMP failed:Create snapshot

Import data dmp dari oracle 9i ke 10g release 2 akan mengalami error sbb:
 ORA-600 [kkzlcsl-1]

Hal ini dapat diselesaikan dengan create manual dengan command
 CREATE SNAPSHOT LOG ON "Schema"."Nama Table" WITH ROWID , PRIMARY KEY EXCLUDING  NEW VALUES ;

Optimasi Undo

Optimasi fungsi undo Undo merupakan salah satu bagian vital dari oracle database system.Undo berperan dalan menyimpan infomasi dan data-data sebelum perubahan (before image).

Dua hal penting dari manajemen undo adalah sbb:
1. Undo size.
2. Undo retention.

Kedua parameter ini saling berkaitan satu dengan lainnya. Hubungan kedua parameter dapat digambarkan pada perhitungan nilai optimal dari undo retention dan undo size.

* Perhitungan nilai optimal dari undo_retention 
Rumus perhitungan undo retention adalah sbb:

optimal undo retention = undo size / (db_block_size * undo_block_per_sec)

Bagaimana mendapatkan nilai undo size?
Hal ini dapat diperoleh dari query sbb:
SELECT SUM(a.bytes) "UNDO_SIZE" FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

Bagaimana mendapatkan nilai undo_block_per_sec?
Hal ini dapat diperoleh dari query sbb:
SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat; Bagaimana mendapatkan nilai db_block_size? Hal ini dapat diperoleh dari query sbb: SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]" FROM v$parameter WHERE name = 'db_block_size';

Bila semua nilai yang diperlukan untuk menghitung nilai optimal dari undo retention telah didapat dengan menggunakan query-qery diatas, maka nilai optimal dari undo retention dapat dihitung dengan menggunakan kalkulator atau pun bisa langsung dihitung dengan menggunakan query sbb:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f,
( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'

** Perhitungan undo size yang diperlukan Rumus dari perhitungan undo size adalah sbb:
undo_size = undo_retention * db_block_size * Undo_block_per_sec

Query untuk mendapatkan nilai optimal dari undo size adalah sbb:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]"
FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f,
( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'

Perhatian:
Perhitungan nilai optimal undo retention dan undo size diatas bukanlah perhitungan EXACT, yang harus tepat. Perhitungan diatas hanyalah untuk memperkirakan apakah nilai undo retention dan undo size database kita telah memadai atau untuk keperluan troubleshoot ORA-1555 (snapshoot too old). Nilai optimal undo retention diatas dapat dibandingan dengan informasi dari alert log yang menyertai ORA-1555.

Misalnya: ORA-1555: snapshot too old. (select....... from.... where... [xxxx second] Nilai xxxx second inilah waktu aktual yang diperlukan untuk memproses query tersebut (yang berada dalam tanda kurung).

Apa perbedaan antara undo retention dan undo size?
Undo retention adalah salah satu dari initial parameter yang berguna untuk mengatur berapa lama informasi di simpan dalam undo segment. Undo size adalah ukuran dari undo segment atau undo tablespace.

oracle xml

oracle xml

xml site

URL agar blog kita ada di halaman satu google
http://www.xml-sitemaps.com/

Oracle Database 11g Fundamental SQL (Chapter 8)

Set Operator

SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY employee_id;

SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;


SELECT employee_id
FROM employees
MINUS
SELECT employee_id
FROM job_history;


SELECT location_id, department_name "Department",
TO_CHAR(NULL) "Warehouse location"
FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department",
state_province
FROM locations;


SELECT employee_id, job_id,salary
FROM employees
UNION
SELECT employee_id, job_id,0
FROM job_history;


SELECT employee_id, job_id,salary
FROM employees
UNION
SELECT employee_id, job_id,0
FROM job_history
ORDER BY 2;

Oracle Database 11g Fundamental SQL (Chapter 7)

SubQuery

Penggunaan subquery single row

SELECT last_name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

Penggunaan subquery multiple row

SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141);

Penggunaan single row sub query
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE last_name = ‘Taylor’)
AND salary > (SELECT salary FROM employees WHERE last_name = ‘Taylor’);

Penggunaaan subquery dengan function

SELECT last_name, job_id, salary
FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

SELECT department_id, MIN(salary) FROM employees GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

SELECT job_id, AVG(salary) FROM employees GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);

Kasus jika tidak ada data yang dihasilkan dari inner query

SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas');


Multiple row subquery
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id);

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';


SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr);

SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id IN
(SELECT mgr.manager_id
FROM employees mgr);

SELECT last_name FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);

Oracle Database 11g Fundamental SQL (Chapter 6)

Natural join

SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;

SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations
WHERE department_id IN (20, 50);

Join table menggunakan using clause

SELECT employee_id, last_name,
location_id, department_id
FROM employees JOIN departments
USING (department_id) ;

Join table dengan menggunakan on clause

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149 ;

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 149 ;

Self join

SELECT worker.last_name emp, manager.last_name mgr
FROM employees worker JOIN employees manager
ON (worker.manager_id = manager.employee_id);

Nonequijoin

SELECT e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON e.salary
BETWEEN j.lowest_sal AND j.highest_sal;

Left join

SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

Right join

SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

Full outer join

SELECT e.last_name, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

Cross join

SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;