Size of blob

select sum(dbms_lob.getlength(IMAGE_BLOB))/1024/1024 from MY_TAB_WITH_IMAGE

Fie size in octal

stat -c '%A %a %n' *

Oracle silent install

To record a response file:

/home/oracle/runInstaller -record -destinationFile response_filename

/home/oracle/runInstaller -record -destinationFile /tmp/response_10g_ni.rsp


When Oracle Installer displays the Summary screen, perform:

* Click Install to create the response file, then continue with the installation.

* Click Cancel and then Yes to create the response file or it will just exit (and create the file).

Log Miner

Log miner, example code.
For prouction, I logmine and quickly make a copy of content and then stop logmining to minimize any impact on live.

[18 Apr 2013: Performed similar on live database, worked fine.]

[1 Aug 2011: Performed this on a restored prod database, worked fine. (was not open, open resetlog will allow you to mine)]


You need enable supplemental logging before generating log files that will be analyzed by LogMiner.

select SUPPLEMENTAL_LOG_DATA_MIN from V$DATABASE;
 
-- On live took around 4 - 6 minutes (depends on db)
alter database add SUPPLEMENTAL LOG DATA;


Specify Redo/Archive Log Files for Analysis (first line different)

EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149910_698844939.arc', options => DBMS_LOGMNR.NEW);

EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149911_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149912_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149913_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149914_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149915_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149916_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149917_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149918_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149919_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149920_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149921_698844939.arc', options => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/u11/oracle/arch_PRDB1/1_149922_698844939.arc', options => DBMS_LOGMNR.ADDFILE);

Then start Log Miner

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);


Copy the content of Log Miner in a fixed table

create table ni_copy_logminer tablespace USERS as select * from V$LOGMNR_CONTENTS;
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO FROM ni_copy_logminer WHERE username IN ('NISLAM_PROD');

SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO
FROM ni_copy_logminer

To properly end a LogMiner session

EXECUTE DBMS_LOGMNR.END_LOGMNR;

Disabling Supplemental Logging

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;


Examine the fixed log mining table e.g.

select SEG_OWNER, count(*) from ni_copy_logminer group by SEG_OWNER;

Track DML statements and Monitoring DML

Check number of archive per hour

col day format a15;
col hour format a4;
col total format 999;

select
to_char(first_time,'yyyy-mm-dd') day,
to_char(first_time,'hh24') hour,
count(*) total
from
v$log_history
group by
to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24')
order by
to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24')
asc;


Clean up and create a monitoring table, check no triggers with name TRG_%

drop table log_dml;
purge recyclebin;

CREATE TABLE log_dml (dml_time timestamp, username varchar2(32), sid number, information varchar2(64));

select count(*) from user_triggers where trigger_name like 'TRG_%';


Create triggers for every table.

DECLARE
CURSOR all_tables
IS
SELECT object_name, object_id, object_type
FROM user_objects WHERE object_type = 'TABLE'
AND object_name not like 'LOG_DML';

BEGIN
FOR rec_cur IN all_tables
LOOP
EXECUTE IMMEDIATE 'create or replace trigger trg_'
|| rec_cur.object_id
|| ' before insert or update or delete on '
|| rec_cur.object_name
|| '
declare
begin
if UPDATING then
insert into log_dml values(sysdate, sys_context(''USERENV'',''CURRENT_SCHEMA''), sys_context(''USERENV'',''SID''), ''UPDATING on '||rec_cur.object_name||''');
elsif DELETING then
insert into log_dml values(sysdate, sys_context(''USERENV'',''CURRENT_SCHEMA''), sys_context(''USERENV'',''SID''), ''DELETING on '||rec_cur.object_name||''');
elsIF INSERTING then
insert into log_dml values(sysdate, sys_context(''USERENV'',''CURRENT_SCHEMA''), sys_context(''USERENV'',''SID''), ''INSERTING on '||rec_cur.object_name||''');
end if;
end;';
END LOOP;
END;
/


To check and remove the triggers.

select count(*) from user_triggers where trigger_name like 'TRG_%';

DECLARE
CURSOR all_triggers
IS
SELECT trigger_name
FROM user_triggers WHERE trigger_name like 'TRG_%';
BEGIN
FOR rec_cur IN all_triggers
LOOP
EXECUTE IMMEDIATE 'drop trigger '|| rec_cur.trigger_name ||'';
END LOOP;
END;
/


block corrupted

1: java.sql.SQLException: ORA-01578: ORACLE data block corrupted (file # 10, block # 377287)
ORA-01110: data file 10: '/u21/oracle/oradata/P1AR/P1AR_INDX_02.dbf'


SELECT segment_type, owner||'.'||segment_name
FROM dba_extents
WHERE file_id = 10 AND 377287 BETWEEN block_id
AND block_id+blocks -1;

Oracle 11g

Effective Tuning Goal
- Specific
- Measurable
- Achiveable
- Cost effective

Goals are also derived from related Service Level Agrements.

Key v$ views
============
V$sysstat
v$sesstat
v$system_event
v$session_event
v$session

5.2 Overview of the Automatic Workload Repository
=================================================
The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.

6.2 Automatic Database Diagnostic Monitor
=========================================
The Automatic Database Diagnostic Monitor (ADDM) provides a holistic tuning solution. ADDM analysis can be performed over any time period defined by a pair of AWR snapshots taken on a particular instance. Analysis is performed top down, first identifying symptoms and then refining them to reach the root causes of performance problems.


Unit 7
Automated Maintenance Tasks
===========================
In 11g auto stats gathering is more flexiable, allows you to change the % of staleness or auto etc.

Javascript on keypress


[script language="javascript"]

function myCharKey(e) {

var e=window.event || e
var keyunicode=e.charCode || e.keyCode

//Allow alphabetical keys, plus BACKSPACE and SPACE
return (keyunicode>=65 && keyunicode<=122 || keyunicode==8 || keyunicode==32)? true : false
}

[/script]


[input type="text" name="myName" size="25" onkeypress="return myCharKey(event);" ]



Number only


var e=window.event || e;
var keyunicode=e.charCode || e.keyCode;

//Allow alphabetical keys, plus BACKSPACE and SPACE
//return (keyunicode>=48 && keyunicode<=57 || keyunicode==46)? true : false;

if((keyunicode>=48 && keyunicode<=57) || keyunicode==46 || keyunicode==8 || keyunicode==32){

var numIs = document.changeProfile.myName.value;

if((keyunicode>=48 && keyunicode<=57) || keyunicode==46){
numIs = numIs + String.fromCharCode(keyunicode);
}

alert(numIs);

return true;
}
else {
return false;
}