Category: Oracle Database
Scenery: Mass distribution of rich and multimedia content over the Internet, made possible through advancements in broadband technologies, also contributes to the growth in overall data volume. The enormous growth in the volume of data that needs to be retained online makes storage one of the biggest cost elements of most IT budgets. The compression clause can be specified at the tablespace, table or partition level with the following options: NOCOMPRESS – The table or partition is not compressed. This is the default action COMPRESS – This option is considered suitable for data warehouse systems OMPRESS FOR ALL OPERATIONS – This option is...
During Oracle OpenWorld 2016, the company has announced new version (3.4.2) of their virtualization platform Some of the key new features that have been delivered include: Oracle VM Manager support for previous Oracle VM Server releases: – Manage Oracle VM Server for x86 – 3.4.x, 3.3.x or 3.2.10/11 releases. – Manage Oracle VM Server for SPARC – Agent 3.4.x or 3.3.x releases. Support for NVM Express (NVMe) devices: – Discover NVMe devices and present them to Oracle VM Manager – NVMe devices can be used to store virtual machine disks or create storage repositories. Extended SCSI functionality available for virtual...
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...
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...
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...
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...
In 11gr2, we now have to place the Voting Disk and Cluster Registry files on ASM disks and we do not have the option of placing them on OCFS or raw devices as in the case of previous versions. When you run the root.sh script as part of the 11gR2 RAC installation, it will create an ASM disk group CDATA and will place the OCR and Voting disk files on this ASM disk. We provide an ASM disk discovery string where the raw devices exist on which the ASM disk groups will be built. Initially we had provided the ASM...
Have you ever been in a situation in which some database queries that used to behave well suddenly started performing poorly? More likely than not, you traced the cause back to a change in the execution plan. Further analysis may have revealed that the performance change was due to newly collected optimizer statistics on the tables and indexes referred to in those queries. One of the many method to secure the execution plan is to use a baseline Below we will explain how to set an execution plan with a baseline having already ‘the optimal plan in memory or having...
When we see this error, the first thing is to control the flash recovery area In the event that indeed the FRA was full, you can delete old archive logs from the filesystem then on rman use the following run { crosscheck archivelog all; delete noprompt expired archivelog all; } The CROSSCHECK command is used to determine whether backups recorded in the repository still exist on disk or tape, if RMAN cannot locate the backups, then it updates their records in the RMAN repository to EXPIRED status. You can then use the DELETE EXPIRED command to remove records of expired...
The recycle bin is a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. Enabling and Disabling the Recycle Bin ALTER SESSION SET recyclebin = OFF; ALTER SYSTEM SET recyclebin = OFF; ALTER SESSION SET recyclebin = ON; ALTER SYSTEM SET recyclebin = ON; An example of recovery: drop table SALARY2016; select object_name,original_name,type,can_undrop as “undo”,can_purge as “PUR”, droptime from dba_recyclebin where original_name like SALARY%’ order by droptime; OBJECT_NAME ORIGINAL_NAME TYPE undo PUR DROPTIME BIN$JZgAyoU+u1DgUxQVCApelQ==$0 SALARY2016 TABLE YES YES 2016-08-04:13:39:27...
