find the LAST_DDL_TIME change time of an Oracle object

SQL to find the LAST_DDL_TIME change time of an Oracle object in the database.

-- Get the name, type, date of change of the DDL of a user object.

select OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME from dba_objects where
owner not in ('SYS','SYSTEM');

Dropping large columns in database - ORACLE

alter table table_name set unused 

There may be a situation where you want to drop a column that has a huge data 10 Million rows .It will take lot of time to drop that column and the worst part is that Oracle will place a lock on that tables until With the " alter table set unused " command you can make that column invisible to users. at a later point of time. when you set the column to unused it will be stored in sys as unused.

MARKING UNUSED COLUMN

sql> 

desc abc_test




Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER                                                                                                 

sql>  

alter table abc_test add (lname varchar2(20))




table ABC_TEST altered.

sql>  

desc abc_test




Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER       

LNAME           VARCHAR2(20) 

sql> 

alter table abc_test set unused (lname)

table ABC_TEST altered.

sql>  

desc abc_test

Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER                                                                                                 




Once this is done the columns will no longer be visible to the user. If at a later date you have time to physically delete the columns this can be done using the following. 

Note : 

Dropping the unused column will still put a lock on the base table. I suggest you to drop 
unused column during maintenance period, to avoid locking.

ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;

The DBA_UNUSED_COL_TABS view can be used to view the number of unused columns per table.

Physically dropping column 

To physically drop a column you can use one of the following syntaxes, depending on whether you wish to drop a single or multiple columns.

ALTER TABLE table_name DROP COLUMN column_name; -- 1 column
ALTER TABLE table_name DROP (column_name1, column_name2);  -- multiple columns

Configure email server to send job notifcations- Oracle

Sample for adding scheduler e-mail notification

    Connected to SQL*PLUS using a privileged user.Using the set_scheduler_attribute procedure we have set the email_sender attribute to the SMTP server IP address, and specified the port to 25:

SQL> connect / as sysdba
Connected.

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','10.155.252.333:25');

PL/SQL procedure successfully completed.

        where:

             host is the host name or IP address of the SMTP server.
            port is the TCP port on which the SMTP server listens. If not specified, the default port of 25 is used.

        If this attribute is not specified, set to NULL, or set to an invalid SMTP server address, the Scheduler cannot send job state e-mail notifications. SMTP servers that require secure sockets (SSL) connections or require user authentication are not supported.

Optional Setup (Default email sender )

    Using the same procedure we have set the email_sender attribute to set the default email address for the email notifications:

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','DBMS_NOTIFICATION@organization.net');

PL/SQL procedure successfully completed.

Confirm the email server has been set

Using the get_scheduler_attribute procedure we can check the current values of both email_server and email_sender attributes.

declare
 v_att VARCHAR2(64);
 v_att2 varchar2(64);
 BEGIN
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', v_att);
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', v_att2);
 dbms_output.put_line('server: ' || v_att);
 dbms_output.put_line('sender: ' || v_att2);
 END;
 /


 Create Sample Job

    Connecting to the test user to create a sample job and created the test_mail job, that will start after 10 seconds from now.

SQL> BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
 job_name => 'TEST_MAIL',
 job_type => 'PLSQL_BLOCK',
 job_action => 'begin
 for i in 1..200 loop
 for j in 1..200 loop
 null;
 end loop;
 end loop;
 end;
 ',
 start_date => systimestamp + interval '10' second ,
 repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
 enabled => True,
comments => '');
 END;
 /

Add Job Notifications to send emails :


    Added the mail notification to the job, we have altered the value of the sender, so the default sender will not be used. Also we used job_all_events that will send mail notification for all events:

SQL> BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
 job_name => 'TEST_MAIL',
 recipients => 'arvind@organization.net',
 sender => 'DBMS_NOTIFICATION@organization.net',
 subject => 'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
 body => '%event_type% occurred at %event_timestamp%. %error_message%',
 events => 'job_all_events');
 END;
 /

PL/SQL procedure successfully completed.


    To review the scheduler email notifications setup, we will use the newly introduced views:
    USER | ALL | DBA_SCHEDULER_NOTIFICATIONS

SQL> desc user_SCHEDULER_NOTIFICATIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB_NAME                                  NOT NULL VARCHAR2(30)
 JOB_SUBNAME                                        VARCHAR2(30)
 RECIPIENT                                 NOT NULL VARCHAR2(4000)
 SENDER                                             VARCHAR2(4000)
 SUBJECT                                            VARCHAR2(4000)
 BODY                                               VARCHAR2(4000)
 FILTER_CONDITION                                   VARCHAR2(4000)
 EVENT                                              VARCHAR2(19)
 EVENT_FLAG                                NOT NULL NUMBER

SQL> select EVENT from user_SCHEDULER_NOTIFICATIONS where job_name='TEST_MAIL';

EVENT
-------------------
JOB_STARTED
JOB_SUCCEEDED
JOB_FAILED
JOB_BROKEN
JOB_COMPLETED
JOB_STOPPED
JOB_SCH_LIM_REACHED
JOB_DISABLED
JOB_CHAIN_STALLED
JOB_OVER_MAX_DUR

10 rows selected


Finally, after all the testing is done we can now remove the (Just) email notification part for the job:

SQL> connect test_job/test_job
Connected.

SQL> exec DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION ('TEST_MAIL');

PL/SQL procedure successfully completed.

Drop scheduler job :

SQL> BEGIN
    DBMS_SCHEDULER.DROP_JOB(job_name => 'TEST_MAIL',
                                defer => false,
                                force => false);
END;
/



Note : this has been tested on Oracle 11g Databases

Automating Oracle ADDM Reports with PL/SQL: A Guide to Sending Performance Reports via Email

Blog Article Title: "Automating Oracle ADDM Reports with PL/SQL: A Guide to Sending Performance Reports via Email"

Article Description:

In this article, we will explore how to automate the process of generating and sending Oracle Automatic Database Diagnostic Monitor (ADDM) reports using PL/SQL. ADDM provides valuable insights into database performance, identifying potential issues and offering recommendations for tuning. By integrating PL/SQL with email functionalities, you can streamline the delivery of these performance reports directly to your inbox, making it easier to monitor and address database health. This guide will cover the components of a PL/SQL package designed for this purpose, with detailed explanations of each section, making it an essential resource for Oracle DBAs looking to enhance their monitoring capabilities.

Note: You can use either DBMS_Sceduler to set job inside the database or trigger through any shell script through the command prompt (sqlplus)

This is an awesome script that I found online blog post by Gokhan Atil (ORACLE ACE). I wanted to share this with my friends, as it will be very helpful in daily maintenance.

PLSQL for ADDM sent via EMAIL:

DECLARE
   dbid           NUMBER;
   bid            NUMBER;
   eid            NUMBER;
   db_unique_name VARCHAR2(30);
   host_name      VARCHAR2(64);
   status         VARCHAR2(11);
   starttime      CHAR (5);
   endtime        CHAR (5);
   output         VARCHAR2 (32000);
   v_from         VARCHAR2 (80);
   v_recipient    VARCHAR2 (80) := 'arvind@domain.com';
   v_mail_host    VARCHAR2 (30) := 'YOUR_SMTP_SERVER';
   v_mail_conn    UTL_SMTP.connection;
   tname varchar2(50);
   tid   number;
BEGIN
   starttime := '01:00';
   endtime := '12:00';

   SELECT MIN (snap_id), MAX (snap_id)
     INTO bid, eid
     FROM dba_hist_snapshot
    WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime
      AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime
      AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)
      AND TRUNC (end_interval_time) = TRUNC (SYSDATE);

   SELECT dbid, db_unique_name
     INTO dbid, db_unique_name
     FROM v$database;

   SELECT host_name INTO host_name
     FROM v$instance;
   
    DBMS_ADVISOR.CREATE_TASK('ADDM',tid,tname,'ADDM Report( '
    || bid || ',' || eid || ' )');
    DBMS_ADVISOR.SET_TASK_PARAMETER( tname,'START_SNAPSHOT',bid );
    DBMS_ADVISOR.SET_TASK_PARAMETER( tname,'END_SNAPSHOT',eid );
    DBMS_ADVISOR.EXECUTE_TASK( tname );  
   
    status := 0;
 
    while status <> 'COMPLETED' loop
    select status into status from dba_advisor_tasks where task_id = tid;
    dbms_lock.sleep(5);  
    end loop;

   v_from := db_unique_name ||  '@' || host_name;

   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.OPEN_DATA( v_mail_conn );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: '
        || 'ADDM Report of ' || v_from || ' '
        || SYSDATE || ' ' || starttime || '-' || endtime
        || UTL_TCP.CRLF || UTL_TCP.CRLF );


   SELECT DBMS_ADVISOR.GET_TASK_REPORT( tname) INTO output FROM DUAL;
   UTL_SMTP.WRITE_DATA (v_mail_conn, output );
 
   UTL_SMTP.CLOSE_DATA (v_mail_conn);
   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;
/

Note : 

works only if you have set the SMTP server and ACL if you are working on Database 11G or higher



Extracted from :

http://www.gokhanatil.com/2011/07/create-awr-and-addm-reports-and-send-them-via-email.html

Plsql Package to receive an Oracle Database AWR Report sent to EMAIL

In this article, we delve into the practical implementation of using a PL/SQL package to automate the generation and distribution of Oracle Database Automatic Workload Repository (AWR) reports via email. AWR reports are essential tools for database administrators, providing in-depth performance metrics and insights that help in diagnosing issues, optimizing queries, and improving overall database performance. Manually generating these reports and sharing them with stakeholders can be time-consuming and prone to delays.

To streamline this process, we demonstrate how to create a PL/SQL package that automates the generation of AWR reports and sends them directly to your inbox or other designated recipients. We will cover the package setup, key PL/SQL procedures, and how to configure the email functionality using Oracle’s UTL_MAIL or UTL_SMTP package. This solution not only saves time but also ensures that critical performance data is consistently monitored and shared with the right team members.

By the end of this article, you will have a ready-to-use script that simplifies AWR report distribution, enhancing your ability to maintain optimal database performance with minimal manual intervention. Whether you are a seasoned DBA or new to Oracle performance tuning, this guide will help you automate a critical aspect of your database management workflow.

This is an awesome script that I found online blog post by Gokhan Atil (ORACLE ACE).I wanted to share this with my friends, as it will be very helpful in daily maintenance.

PLSQL to send the AWR report to your email directly.

DECLARE
   dbid           NUMBER;
   inst_id        NUMBER;
   bid            NUMBER;
   eid            NUMBER;
   db_unique_name VARCHAR2(30);
   host_name       VARCHAR2(64);
   starttime      CHAR (5);
   endtime        CHAR (5);
   v_from         VARCHAR2 (80);
   v_recipient    VARCHAR2 (80) := 'arvind@domain.com';
   v_mail_host    VARCHAR2 (30) := 'YOUR_SMTP_SERVER';
   v_mail_conn    UTL_SMTP.connection;
BEGIN
   starttime := '06:00';
   endtime := '10:00';

   SELECT MIN (snap_id), MAX (snap_id)
     INTO bid, eid
     FROM dba_hist_snapshot
    WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime
      AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime
      AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)
      AND TRUNC (end_interval_time) = TRUNC (SYSDATE);

   SELECT dbid, inst_id, db_unique_name
     INTO dbid, inst_id, db_unique_name
     FROM gv$database;

   SELECT host_name INTO host_name
     FROM v$instance;

   v_from := db_unique_name ||  '@' || host_name;

   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.OPEN_DATA( v_mail_conn );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: '
        || 'AWR Report of ' || v_from || ' '
        || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF  );
   UTL_SMTP.WRITE_DATA ( v_mail_conn,
        'Content-Type: text/html; charset=utf8'
        || UTL_TCP.CRLF || UTL_TCP.CRLF );  

   FOR c1_rec IN
      (SELECT output
         FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(dbid,
           inst_id, bid, eid, 8 )))
   LOOP
      UTL_SMTP.WRITE_DATA (v_mail_conn, c1_rec.output || UTL_TCP.CRLF );
   END LOOP;
 
   UTL_SMTP.CLOSE_DATA (v_mail_conn);
   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;



Note : 

works only if you have set the SMTP server and ACL if you are working on Database 11G or higher



Extracted from :

http://www.gokhanatil.com/2011/07/create-awr-and-addm-reports-and-send-them-via-email.html