EXP-00008 dan ORA-00904

Pada Waktu export ditemukan error.

Error sbb:
About to export specified tables via Conventional Path ...
Current user changed to SSP
. . exporting table BODES_BLACKLIST
EXP-00008: ORACLE error 904 encountered
ORA-00904: : invalid identifier

Periksa:
SQL> select instance_name, status from v$instance;

package yang invalid statusnya artinya harus di patch ulang
dengan menjalankan dua script terakhir
utluxxx dan catpatch

Panggil fire fox dari command

bash> cd /usr/bin
bash> ./firefox

Oracle 10g Dbconsole: ERROR: NMO not setuid-root

Jalankan sebagai root
cd $ORACLE_HOME (your OEM HOME directory)
chmod 6750 bin/nm?
chmod 700 bin/emdctl
chmod 700 bin/emagent
chown root bin/nm?

sbagai oem user:
emctl stop agent
emctl start agent

Langkah-langkah migrasi dari Oracle 9i ke oracle 10g dengan menggunakan dbua

1. Install Oracle 10g
a. Pada level OS membuat user baru untuk install oracle 10g, misalnya oracle10
b. Buat direktori baru khusus untuk oracle base dari oracle 10g
c. Ikuti langkah-langkah instalasi oracle 10g.
d. install juga oracle 10g companion untuk meng-install oracle ultra search.
2. Berikan permission 775 / 777 secara recursive kepada:
a. direktori oracle base oracle 9i
b. direktori oracle base oracle 10g
c. direktory yang menyimpan data-data file, redo log, control files dan
undo tablespace database di oracle 9i
3. Aktifkan database dalam oracle 9i (startup open).
4. Copy script utlu102i.sql ke direktory yang dapat dikases baik dari user oracle untuk
oracle 9i dan user untuk oracle 10g (dalam level OS).
5. Masuk ke SQL plus di oracle 9i.
SQL> spool info.log
SQL> @utlu102i.sql
SQL> spool off
6. Periksa hasil spool dan lakukan perbaikan sesuai dengan saran yang diberikan oleh
hasil verifikasi dari script yang baru saja dijalankan.
7. Sysaux tablespace dan setting initial parameter streams_pool_size akan dilakukan
secara otomatis oleh dbua.
8. Jalankan query berikut ini:
SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
9. Query diatas digunakan untuk melihat user-user mana saja dalam database yang mempunyai
role connect. Role connect di oracle 10g hanya mempunyai create session privilege.
10.Setelah upgrade selesai, user-user dari query diatas perlu di periksa apakah masih
memerlukan privilege lain dalam role connect.
11.Supaya proses migrasi cepat, lakukan
grant analyze any to sys;
exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('DMSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('SI_INFORMTN_SCHEMA',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
12.Hal ini dilakukan karena selama upgrade, oracle 10g akan menghitung statistik dari optimizer oracle 9i. agar proses migrasi cepat, perhitungan statistik
optimizer dilakukan sebelum proses migrasi.
13.Jalankan dbua.
14.Pilih option backup, jika belum dilakukan full backup physical.
15.Bila tidak muncul direktory database yang akan di migrasi, periksa list /etc/oratab
SID:direkri_database=N
16.Setelah proses migrasi selesai, buat tnsname di oracle10g.

contoh query time different beda hari dalam detik yang dihitung jumlahnya

select beda_wkt, count(beda_wkt)
from
(
select (case when to_date(dtm2,'DD-MM-YYYY') <> to_date(dtm1,'DD-MM-YYYY') and to_date(dtm2,'DD-MM-YYYY') = to_date(dtm1,'DD-MM-YYYY')+1 then (to_number(to_char(dtm2,'SSSSS')) - to_number(to_char(dtm1,'SSSSS')))+86400 else to_number(to_char(dtm2,'SSSSS')) - to_number(to_char(dtm1,'SSSSS')) end) as beda_wkt from test2
)
group by beda_wkt;

contoh query time different beda hari dalam detik

select (case when to_date(dtm2,'DD-MM-YYYY') <> to_date(dtm1,'DD-MM-YYYY') and to_date(dtm2,'DD-MM-YYYY') = to_date(dtm1,'DD-MM-YYYY')+1 then (to_number(to_char(dtm2,'SSSSS')) - to_number(to_char(dtm1,'SSSSS')))+86400 else to_number(to_char(dtm2,'SSSSS')) - to_number(to_char(dtm1,'SSSSS')) end) as beda_wkt from test2;

Patch oracle 10g ke release oracle 10g yang lebih tinggi

Langkah-langkahnya sbb:
1. Download patch
2. unzip patch tersebut.

Prinsip yang perlu diperhatikan:
Patch selalu pada orale home yang sama.
Migras1 selalu harus membuat oracle home yang baru

3. Matikan listener dari oracle database yang akan di patch.
4. Shutdown database yang yang akan di patch.

5. Masuk ke direktory patch diatas /Disk1
6. Jalankan ./runInstaller
7. Klik dan pilih option yang ditampilkan dari installer.
8. Jalankan script yang harus dijalankan dari root (spt root.sh)

9. Listener dijalankan
10. Startup database migrate
SQL> startup upgrade

Pada oracle 9i gunakan
SQL> startup migrate
11.Jalankan script berikut ini:
SQL> spool upgrade_info.log
SQL> @?/rdbms/admin/utlu102i.sql
SQL spool off

12.Bila telah selesai menjalankan script ini,perhatikan dari log
apakah terdapat error atau tidak. Bila terdapat error, ulangi
lagi proses menjalankan script ini. Bila ada table yg di lock,
maka kill/logout dari sesi itu.

13. Jalankan script berikut:
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off

14.Bila telah selesai menjalankan script ini,perhatikan dari log
apakah terdapat error atau tidak. Bila terdapat error, ulangi
lagi proses menjalankan script ini. Bila ada table yg di lock,
maka kill/logout dari sesi itu.

13.shutdown database
14.startup database
15.jalankan script berikut ini utk memerika object yg invalid
SQL> @?/rdbms/admin/utlrp.sql
16.Memeriksa hasil patch
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME

SQL> select * from v$version;

Patch Oracle 9.2.0.4 ke Oracle 9.2.0.8

Langkah-langkahnya sbb:
1. Download patch p4547809_92080_linux
2. unzip patch tersebut.

Prinsip yang perlu diperhatikan:
Patch selalu pada orale home yang sama.
Migrasu selalu harus membuat oracle home yang baru

3. Matikan listener dari oracle database yang akan di patch.
4. Shutdown database yang yang akan di patch.

5. Masuk ke direktory patch diatas /Disk1
6. Jalankan ./runInstaller
7. Klik dan pilih option yang ditampilkan dari installer.
8. Jalankan script yang harus dijalankan dari root (spt root.sh)

9. Listener dijalankan
10. Startup database migrate
SQL> startup migrate

Pada oracle 10g gunakan
SQL> startup upgrade
11.Jalankan script berikut ini:
SQL> spool patch.log
SQL> @?/rdbms/admin/catpatch.log

12.Bila telah selesai menjalankan script ini,perhatikan dari log
apakah terdapat error atau tidak. Bila terdapat error, ulangi
lagi proses menjalankan script ini. Bila ada table yg di lock,
maka kill/logout dari sesi itu.

13.shutdown database
14.startup database
15.jalankan script berikut ini
SQL> @?/rdbms/admin/utlrp.sql
16.Memeriksa hasil patch
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME

SQL> select * from v$version;

script utk mengetahui job yang sedang berjalan

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

script untuk mengetahui session dan sql statement yg sedang berjalan

col sid format '9999'
col serial format '999'
col username format a9
col TIME format a20
col status format a8
col program format a28
col sql_text format a40

select a.sid, a.Serial#, a.username,
to_char(a.logon_time,'DD-MM-YYYY hh:mm:ss') as TIME,
a.status, a.program,
b.sql_text
from v$session a, v$sqlarea b
where
a.sql_address=b.address
and
a.username is not NULL
order by 4 desc;

script mengetahui job

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

script persentase free tablespace (lanjutan 2)

SET LINESIZE 135
SET PAGESIZE 9999
SET VERIFY OFF

COLUMN status FORMAT a9 HEADING 'Status'
COLUMN name FORMAT a15 HEADING 'Tablespace Name'
COLUMN type FORMAT a12 HEADING 'TS Type'
COLUMN extent_mgt FORMAT a10 HEADING 'Ext. Mgt.'
COLUMN segment_mgt FORMAT a9 HEADING 'Seg. Mgt.'
COLUMN ts_size FORMAT 9,999,999,999,999 HEADING 'Tablespace Size'
COLUMN used FORMAT 9,999,999,999,999 HEADING 'Used (in bytes)'
COLUMN free FORMAT 9,999,999,999,999 HEADING 'Free (in bytes)'
COLUMN pct_used FORMAT 999 HEADING 'Pct. Used'

BREAK ON report
COMPUTE SUM OF ts_size ON report
COMPUTE SUM OF used ON report
COMPUTE SUM OF free ON report
COMPUTE AVG OF pct_used ON report

SELECT
d.status status
, d.tablespace_name name
, d.contents type
, d.extent_management extent_mgt
, d.segment_space_management segment_mgt
, NVL(a.bytes, 0) ts_size
, NVL(a.bytes - NVL(f.bytes, 0), 0) used
-- , NVL(f.bytes, 0) free
, NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used
FROM
sys.dba_tablespaces d
, ( select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name
) a
, ( select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name
) f
WHERE
d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (
d.extent_management like 'LOCAL'
AND
d.contents like 'TEMPORARY'
)
UNION ALL
SELECT
d.status status
, d.tablespace_name name
, d.contents type
, d.extent_management extent_mgt
, d.segment_space_management segment_mgt
, NVL(a.bytes, 0) ts_size
, NVL(t.bytes, 0) used
-- , NVL(a.bytes - NVL(t.bytes,0), 0) free
, NVL(t.bytes / a.bytes * 100, 0) pct_used
FROM
sys.dba_tablespaces d
, ( select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name
) a
, ( select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name
) t
WHERE
d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
/

script persentase free tablespace (lanjutan 1)

tti "Space Usage for Database in Meg"

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/

tti off

script persentase free tablespace

clear buffer
clear columns
clear breaks
set linesize 500
set pagesize 5000
column a1 heading 'Tablespace' format a15
column a2 heading 'Data File' format a45
column a3 heading 'Total|Space [MB]' format 99999.99
column a4 heading 'Free|Space [MB]' format 99999.99
column a5 heading 'Free|%' format 9999.99
break on a1 on report
compute sum of a3 on a1
compute sum of a4 on a1
compute sum of a3 on report
compute sum of a4 on report
SELECT a.tablespace_name a1,
a.file_name a2,
a.avail a3,
NVL(b.free,0) a4,
NVL(ROUND(((free/avail)*100),2),0) a5
FROM (SELECT tablespace_name,
SUBSTR(file_name,1,45) file_name,
file_id,
ROUND(SUM(bytes/(1024*1024)),3) avail
FROM sys.dba_data_files
GROUP BY tablespace_name,
SUBSTR(file_name,1,45),
file_id) a,
(SELECT tablespace_name,
file_id,
ROUND(SUM(bytes/(1024*1024)),3) free
FROM sys.dba_free_space
GROUP BY tablespace_name, file_id) b
WHERE a.file_id = b.file_id (+)
ORDER BY 1, 2
/

lokasi data file

select file_name, tablespace_name
from
dba_data_files
order by 1 asc;


select file_name
from
dba_data_files
order by 1 asc;

Query busy connection

SQL> SELECT
2 name s_name
3 , ROUND(busy / (busy + idle) * 100, 2) s_busy
4 FROM v$shared_server
5 ORDER BY name;

S_NA S_BUSY
---- ----------
S000 100
S001 99.68
S002 99.89
S003 100
S004 99.92
S005 99.78
S006 99.97
S007 99.98
S008 99.96
S009 99.95
S010 99.86

S_NA S_BUSY
---- ----------
S011 99.84
S012 99.87
S013 99.8
S014 99.82
S015 99.93
S016 99.7
S017 99.98
S018 99.99
S019 99.98

20 rows selected.

Create dblink

create public database link
mylink
connect to
remote_username
identified by
mypassword
using 'tns_service_name';

Deteksi Lock

set linesize 150
set pages 100

col name for a21 head "Locked Object"
col session_id for 99999 head SID
col serial# for 99999 head SER#
col oracle_username for a12 head "Locking User"
col lock_type for a12 head "Lock Type"
col mode_held for a12 head "Mode Held"
col event for a30

SELECT a.session_id, b.serial#, a.oracle_username, c.name,
decode(d.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distrib Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
d.type) lock_type,
decode(d.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(d.lmode)) mode_held,
e.event, e.SECONDS_IN_WAIT "Wait(Seconds)"
FROM sys.obj$ c, v$session b, v$locked_object a,
sys.v_$lock d, v$session_wait e
WHERE a.session_id=b.sid
AND b.sid=e.sid
AND c.obj#=a.object_id
AND a.object_id=d.id1
AND b.sid=d.sid
order by e.SECONDS_IN_WAIT desc
;

Lock session?

SELECT a.sid "Locking Sid (yang nge-lock)",
b.sid "Locked SID (Sedang Menunggu)"
FROM v$lock a , v$lock b
WHERE a.id1=b.id1
AND a.id2=b.id2
AND a.request=0
AND b.lmode=0
;

Blocking Session?

SELECT SUBSTR(TO_CHAR(session_id),1,5) "SID",
SUBSTR(lock_type,1,15) "Lock Type",
SUBSTR(mode_held,1,15) "Mode Held",
SUBSTR(blocking_others,1,15) "Blocking?"
FROM dba_locks
/

contoh pivoting dan case when (table berbentuk matrix)

select categori,
jam1_2,
jam2_3
from
(
select categori,
count(case when dtm between (trunc(sysdate)+1/24) and (trunc(sysdate)+2/24) then 'A' else null end) as jam1_2,
count(case when dtm between (trunc(sysdate)+2/24) and (trunc(sysdate)+3/24) then 'B' else null end) as jam2_3
from test1
group by categori
);