Diosco
het Struweel 51
5501 AP Veldhoven

tel nr.:06-29119286
E-mail : info@diosco.com
   Version1.0.1  
Problem :  .
During the execution of a critical application patch that should be executed on production we saw some dropping of  columns of very large tables. The problem with executing these statement where that the action would exceed the time window the patch could be deployed. The solution was to defer this action at a later convenient time by the following statement.
Solution :
First disable column
ALTER TABLE <TABLE_NAME> SET UNUSED (<COLUMN1>,<COLUMN2>);

In the weekend we could physically drop the column
ALTER TABLE <TABLE_NAME> DROP UNUSED COLUMNS;

Problem : Problem tracing back a query from its origin when a database link is used.

Solution : Run the query and use the SPID on the source database to identify which user is accessing the database via a database link

SELECT /*+ ORDERED */
SUBSTR(s.ksusemnm,1,10)||'-'|| SUBSTR(s.ksusepid,1,10) "ORIGIN user/spid",
SUBSTR(g.K2GTITID_ORA,1,35) "GTXID",
SUBSTR(s.indx,1,4)||'.'|| SUBSTR(s.ksuseser,1,5) "LSESSION" ,
SUBSTR(DECODE(bitand(ksuseidl,11),1,'ACTIVE',0,
DECODE(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,1) "S",
SUBSTR(event,1,10) "WAITING"
FROM x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
WHERE g.K2GTDXCB =t.ktcxbxba
AND g.K2GTDSES=t.ktcxbses
AND s.addr=g.K2GTDSES
AND w.sid=s.indx;

Manual enter text in the allert log

exec dbms_system.ksdwrt(2,'ORA-20002 manual enter text in allert log file!');

Problem :
None 
Solution :
Scheduling a job is something I always use and not want to retype it again and again.

set serveroutput on size 2000
begin
 DECLARE
  X NUMBER;
 BEGIN
  SYS.DBMS_JOB.SUBMIT
  (
  job  => X
  ,what  => ' begin
 dbms_stats.gather_schema_stats(ownname=>''SCOTT'', cascade=>TRUE);
 end;'
  ,next_date  => sysdate+1/1440
  ,interval  => 'SYSDATE+360/1440 '
  ,no_parse  => FALSE
  );
  dbms_output.put_line('job nr = ' || X);
 END;
end;
/
commit;

sqlt  SQL Trace
trca Trace Anakyse

Installing DBCONSOLE

emca -deconfig dbcontrol db -repos drop

emca -config dbcontrol db -repos create  -SID %ORACLE_SID% -PORT 1521 -SYS_PWD <PASSWORD> -DBCONTROL_HTTP_PORT 5501 -DBSNMP_PWD <PASSWORD> -SYSMAN_PWD <PASSWORD> -EMAIL_ADDRESS nospam@diosco.com -MAIL_SERVER_NAME smtp.disoco.com

DBONSOLE disable securety (https)

1. in 10.2g dbconsole

     emctl unsecure dbconsole

2. In oudere versies van dbconsole < 10.2g

   Update the DB Control standalone OC4J container to support unsecure port in the file

   $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<host>_<sid>/config/http-web-site.xml

   Change the following lines:

     <web-site host="[ALL]" port="1820"
         display-name="OC4J Java HTTP Web Site"
         secure="true">

   Into:

     <web-site host="[ALL]" port="1820"
         display-name="OC4J Java HTTP Web Site"
         secure="false">

Checkstatus of DB Console
emctl status dbconsole