The UTL_MAIL database package is used to manage email. It allows you to send an email message directly from the database server, with cc and bcc, and also catering for RAW attachments. This package is not installed by default due to obvious security reasons, but needs to be installed manually via two scripts: utlmain.sql and prvtmail.plb, both of which are in the rdbms/admin directory under the Oracle Home. sqlplus / as sysdba SQL> @?/rdbms/admin/utlmail.sql SQL> @?/rdbms/admin/prvtmail.plb However, in the case of an Oracle 12c Database, it is not enough to run these scripts in the root container. You need to Read More

Global Temporary Table

Global Temporary Table (GTT): Data base version: Oracle Database 11g Environment: DWH This kind of table defines a temporary table for the current connection, these tables do not reside in the system catalogues and are not persistent. Temporary tables exist only during the connection that declared them and cannot be referenced outside of that connection. When the connection closes, the table rows are deleted, and the table is dropped. Temporary tables are useful when: The table structure is not known before using an application Data in the temporary table is needed while using the application The table can be declared Read More

ORACLE 12C : Extended Data Type

With the previous Oracle database release, long characters strings of more then 4000 bytes had to be handled using CLOB or LONG data types. Starting from Oracle 12c was introduced a new system parameter: MAX_STRING_SIZE. This parameter allows string data types to be much larger when the parameter is changed from its default value STANDARD to EXTENDED. New higher limits (in bytes) are available for following types: – VARCHAR2 – 32767 bytes – NVARCHAR – 32767 bytes – RAW – 32767 bytes To use extended version you need to set new initialization parameter MAX_STRING_SIZE and run sql script utl32k.sql. Set Read More

Oracle 12C Last Login Time

In the previous releases, to be able to know the last login time of the user, we had to enable “AUDIT SESSION” and then query the AUD$ table. The SYS.USER$ table in Oracle Database 12c includes a column to store the last login time of the user. As part of the increased security features in 12c, last login time is now stored in the USER$ table itself, in SPARE6 column. This is great information to know when a user last used the database. Security related values in the USER$ table are : CTIME: Date & Time when user was created Read More