ORACLE 11G_ HOW TO KNOW THE DDL FOR SCHEDULER-JOBS OR JOB_CLASS

Sometimes we need to create in another environment, a job or job class that it exist in current environment, but we don't know the ddl sintax, then we need to extract the sentence from dual.

In this case we need to know the ddl of job class called HISTORICAL_JOB_CLASS.

If you execute like SYSDBA

SQL> select dbms_metadata.get_ddl('JOB','HISTORICAL_JOB_CLASS') from dual;

the system return this error

ERROR:
ORA-31604: invalid NAME parameter "NAME" for object type JOB in function
SET_FILTER
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1

or

If you execute like SYSDBA

SQL> select dbms_metadata.get_ddl('JOB_CLASS','HISTORICAL_JOB_CLASS') from dual;

ERROR:

ORA-31600: invalid input value JOB_CLASS for parameter OBJECT_TYPE in function

GET_DDL

ORA-06512: at "SYS.DBMS_METADATA", line 5805

ORA-06512: at "SYS.DBMS_METADATA", line 8344

ORA-06512: at line 

but if you execute like SYSDBA

SQL>  select dbms_metadata.get_ddl('PROCOBJ','HISTORICAL_JOB_CLASS') from dual;

the system return

BEGIN 

dbms_scheduler.create_job_class('"HISTORICAL_JOB_CLASS"',NULL,'HISTORICO',64,NULL,

'Default Job Class for Bath jobs'

);

COMMIT; 

END; 

That's the right way to know the ddl of job or job_class

ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []

Sometimes during a query (select) it appears this fatal error

ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []

I usually solve it recreating the index or indexes of the affected table

To know and generate the sentences to rebuild the index/indexes from the affected table use this sentence


select 'alter index ' ||owner|| '.' ||index_name|| ' rebuild;' from dba_indexes where table_name='affected table name';

For example, to rebuild all indexes from a table named test01 and owner Gorka, the steps will be....

1.- SQL> select 'alter index ' ||owner|| '.' ||index_name|| ' rebuild;' from dba_indexes where table_name='TEST01';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index gorka.test_index01 rebuild;
alter index gorka.test_index_PK rebuild;
alter index gorka.test_index02 rebuild;
alter index gorka.test_index03 rebuild;


4 rows selected.

2.- execute the sentences generated (copy the list and paste in sqlplus and then return)

SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.

MATRIX: ORACLE DATABASE ROADMAP 2006-2022

MATRIX: ORACLE DATABASE UPGRADE

ORACLE CSSCAN: HOW TO INSTALL AND RUN

CSSCAN is a oracle utility to check the database character set conversion.

TO INSTALL:

The character set migration utility schema is installed by running the "$ORACLE_HOME/rdbms/admin/csminst.sql" script in SQL*Plus as the SYS user

TO RUN

csscan \"sys/password@sid AS SYSDBA\" full=y tochar=<new database character set> for example AL32UTF8

if you run with sintax csscan "sys/password@sid AS SYSDBA" it's appears the message

LRM-00108: invalid positional parameter value 'as'

failed to process command line parameters ç

Scanner terminated unsuccessfully

ORACLE: COMMAND HOW TO KNOW OPENED CURSORS CURRENT

This is a command to know opened cursors current order by user and sid

select b.sid, a.username, b.value Cursores_Abiertos from v$session a, v$sesstat b, v$statname c where c.name in ('opened cursors current') and b.statistic# = c.statistic# and a.sid = b.sid and a.username is not null and b.value >0 order by a.username,b.sid;

ORACLE HOW TO MANIPULATE LISTENER.LOG WITHOUT STOP THE LISTENER

Sometimes, you need manipulate the listener.log because it's too big or your disk device is full and you can't stop the service for your business then this is the solution, stop the generation of  entries into the logfile but the listener continue executing.

this is a script into crontab running each 2 weeks. In this script I stop the inserts into the listener.log, compress the file and start the inserts into a new  listener.log

lsnrctl << EOF
set current_listener LISTENER
set log_status off
exit
EOF
go to place of your listener.log and then, delete, move or compress....
gzip listener.log
lsnrctl << EOF
set current_listener LISTENER
set log_status on
exit
EOF

after this operation you can see in the directory

listener.log.gz -----> old file compressed

listener.log -----> new file generated

ORACLE UTL_SMTP: INSTALL AND EXAMPLE

---- To install UTL_SMTP ----

host@oracle]$ cd $ORACLE_HOME/rdbms/admin
[host@oracle]$sqlplus / as sysdba
SQL> @utlsmtp
SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
SQL> alter system set smtp_out_server='mailhost ip address:25' scope=both;


---- Example UTL_SMTP ----



DECLARE
v_From VARCHAR2 := 'aaaaa@gmail.com';
v_Recipient VARCHAR2 := 'bbbb@gmail.com';
v_Recipient2 VARCHAR2 := 'ccccc@gmail.com';
v_Subject VARCHAR2 := 'this is a test of utl_smtp';
v_Mail_Host VARCHAR2 := 'mailhost name or mailhost IP address';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2 := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient2);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || ';' || v_Recipient2 || crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/

ORACLE UTL_MAIL: INSTALL AND EXAMPLE

----- To install utl_mail -----

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
grant execute on UTL_MAIL to public;


----- Example of utl_mail -----

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => 'xxxxx@gmail.com',
            recipients => 'bbbbbbb@gmail.com,ccccccc@gmail.com',
               subject => 'This is the subject of message',
               message => 'this is the message',
             mime_type => 'text; charset=us-ascii');
END;
/

LINUX HOW TO CONFIGURE SENDMAIL. EXAMPLE OF SEND MESSAGE USING MAILX

This summary is not available. Please click here to view the post.

HOW TO SOLVE COMPATIBILITY MISTAKES DURING INSTALLATION ORACLE CLIENT 10G IN WINDOWS 7 MACHINE

To solve the compatibility mistake during installation of oracle client 10g in windows 7 machines

1.- you must add the following lines to all ...\stage\...\refhost.xml


  <CERTIFIED_SYSTEMS>
    <OPERATING_SYSTEM>
    <!--Microsoft Windows 2000-->
      <VERSION VALUE="5.0"/>
      <SERVICE_PACK VALUE="1"/>
    </OPERATING_SYSTEM>
    <OPERATING_SYSTEM>
    <!--Microsoft Windows XP-->
      <VERSION VALUE="5.1"/>
      <SERVICE_PACK VALUE="1"/>
    </OPERATING_SYSTEM>
    <OPERATING_SYSTEM>
    <!--Microsoft Windows 2003-->
      <VERSION VALUE="5.2"/>
    </OPERATING_SYSTEM>
    <!--Microsoft Windows Vista-->
    <OPERATING_SYSTEM>
      <VERSION VALUE="6.0"/>
    </OPERATING_SYSTEM>
    <!--Microsoft Windows 7-->
    <OPERATING_SYSTEM>
      <VERSION VALUE="6.1"/>
    </OPERATING_SYSTEM>
  </CERTIFIED_SYSTEMS>

2.- but we must add 6.1 also in the file install/oraparam.ini

[Certified Versions]
#You can customise error message shown for failure, provide value for CERTIFIED_VERSION_FAILURE_MESSAGE
Windows=5.0,5.1,5.2,6.0,6.1

Then you can install oracle client now without compatibility mistakes