Learn Database Concepts

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');

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                                                                                   ...

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                                                                                           ...

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...

This is an awesome script that I have found online blogpost by  Gokhan Atil  (ORACLE ACE).I wanted to share this with my friends, as it will be very help full 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 ...

This is an awesome script that I have found online blogpost by  Gokhan Atil  (ORACLE ACE).I wanted to share this with my friends, as it will be very help full in daily maintenance. PLSQL to sen AWR report to you 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    sta...

Creation on a CDB (Container database) creates a service named is the CDB name. This is a side effect of creating a PDB (Pluggable Database) in the CDB, a service is created inside it with a property that identifies it as the initial current container. The service is also started as a side effect of creating the PDB. Although its metadata is recorded inside the PDB, the invariant is maintained so that a service name is unique within the entire CDB. Use the Easy Connect syntax to connect to the root unless a net service name is configured in the tnsnames for the root service. . oraenv [enter cdb1 at the prompt] sqlplus sys/oracle@localhost:1521/cdb1 as sysdba show con_name show con_id Connect to the...