Wednesday, 26 October 2016

LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 1

Oracle 12c introduced the LATERAL inline view syntax, as well as CROSS APPLY and OUTER APPLY joins into the SELECT syntax. There is some similarity between them, so it's easier to deal with them in a single article.
  • Setup
The examples in this article require the following tables to be present.

--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;

Tuesday, 25 October 2016

Online Statistics Gathering for Bulk Loads in Oracle Database 12c Release 1

Oracle is now able to gather statistics during some bulk operations, making it unnecessary to gather statistics subsequently. This means extra table scans for statistics collection are unnecessary and there is less chance that you will forget to gather statistics after bulk loads.
  • CREATE TABLE ... AS SELECT (CTAS)
Oracle is able to gather statistics during a CREATE TABLE ... AS SELECT (CTAS) operation for most regular heap organized table, as shown below.

Monday, 24 October 2016

Multitenant : Database Triggers on Pluggable Databases (PDBs) in Oracle 12c

With the introduction of the multitenant option, database event triggers can be created in the scope of the CDB or PDB. Some extra trigger events are also available.
  • Trigger Scope
To create a trigger on a database event in a CDB requires a connection to the CDB as a common user with the ADMINISTER DATABASE TRIGGER system privilege.

CONN sys@cdb1 AS SYSDBA

Friday, 21 October 2016

Concurrent Statistics Collection in Oracle Database 12c Release 1

Concurrent statistics collection is simply the ability to gather statistics on multiple tables, or table partitions, at the same time. This is done using a combination of the job scheduler, advanced queuing and resource manager. Concurrent statistics collection can reduce the time it takes to gather statistics, provided the system can cope with the extra workload.

  • Enabling Concurrent Statistics Collection

From a user perspective, the concurrent statistics collection functionality is very simple. You set the CONCURRENT global preference to the required value using the DBMS_STATS package and Oracle determines if concurrency is appropriate and if so, the level of concurrency to use. The CONCURRENT preference is set to OFF by default, as shown below.

Thursday, 20 October 2016

Oracle DBA in an Oracle Applications World

Transitioning from being a “normal” Oracle DBA into becoming an Oracle Applications DBA has two components. The first concerns the job or position itself, that is, how do you get a job as an Oracle Apps DBA? The second component has to do with the technology aspect of the job. In other words, what is the architecture behind the following items?
  • Oracle Applications
  • Oracle Apps
  • EBS
  • E-Business Suite
  • Oracle 11i
  • EBS 12

Wednesday, 19 October 2016

Mapping SQL Server Management Features to Oracle Database

Oracle as a Data Source, Setting up Oracle as a Data Source for SQL Server and Configuring Oracle as a Data Source for SQL Server address the use of replication within SQL Server where Oracle was used as the data source, and many of the comparisons or mappings back into Oracle are addressed therein, so I’ll pass on repeating this material and move on to the next category.

Replication

Continuing on in SQL Server Management Studio’s (SSMS) Object Explorer tree, we see that the next major category deals with replication.

Tuesday, 18 October 2016

SQL Server Management Features vs Oracle Database Management Features

Notification Services

SQL Server Notification Services, referred to as SQLNS, was meant to be an all-purpose message or messaging delivery system. Its architecture is based on a subscriber, subscriptions, one or more events, and a notification. If you have events whose occurrence you want messaged to a user, that user could receive a notification via PDA, SMS text, and other forms of delivery.

Monday, 17 October 2016

Migrate Oracle Database To ASM Using ASMCMD

Introduction


Oracle Database 10g Release 1 introduced Automatic Storage Management (ASM), a new framework for managing Oracle database files. ASM provides a foundation for highly efficient storage management with direct I/O, redundancy, striping and load balancing. Files stored on ASM are evenly distributed across disks in a disk group and mirrored according to the policies defined in the disk group. Since ASM provides an easy and highly efficient way to manage storage, it is the recommended file system for storing database files for RAC as well as single instance databases.

Saturday, 15 October 2016

Explore Oracle Database In-Memory – Part 2

Oracle In-Memory is fully compatible with Oracle multitenant database architecture, allowing us to take advantage of fast In-Memory and low-cost storage.

This multitenant architecture was also introduced in Oracle 12c, featuring multiple pluggable databases (PDBs) within a container database (CDB). This makes it easy to consolidate databases onto the cloud, deliver a high density of schema-based consolidation without making changes to application, increase the utilization of resources, and allows for the rapid provision and cloning of databases for various purposes by lowering infrastructure and administrative costs.

The pluggable databases share the system global area (SGA) and background processes of a common container database, therefore PDBs also share a single IM column store. The size of the IM column store is controlled by the INMEMORY_SIZE parameter in the CDB.

Thursday, 6 October 2016

Temporal Validity in Oracle Database 12c

The temporal validity feature has been added in Oracle database 12c to make querying of effective date ranges simpler.
  • Setup
The following script creates three tables to store student and module records, along with the relationship between them.

CONN sys@pdb1 AS SYSDBA

GRANT EXECUTE ON dbms_flashback TO test;

CONN test/test@pdb1

Wednesday, 5 October 2016

Control Invoker Rights Privileges for PL/SQL Code in Oracle Database 12c

Invoker rights procedures and functions can present a security risk by allowing privilege escalation if the contents of the procedure and functions are not checked for malicious code.

  • The Problem

The following represents a scenario where a sneaky developer takes advantage of invoker rights to escalate their privileges.

Create the following users.

Tuesday, 4 October 2016

Explore Oracle Database In-Memory – Part 1

The Oracle Database In-Memory option was introduced in Oracle 12c (12.1.0.2 patch set). By using the In-Memory option, businesses can benefit from better decisions made in real time, improved productivity, increased competitiveness and lowered costs.

The Oracle In-Memory option is designed to fit both Data warehouse and mixed workload OLTP databases, and can be deployed with any existing application that supports Oracle 12c. This feature is fully integrated with Oracle Database’s scale-up and scale-out features, security technologies, storage tiering and availability.

Monday, 3 October 2016

Flashback Data Archive (FDA) Enhancements in Oracle Database 12c Release 1

Flashback Data Archive (FDA), also known as Flashback Archive (FBA), was introduced in Oracle 11g to provide long term storage of undo data, allowing undo-based flashback operations to be performed over an extended period of time. Oracle database 12c includes a number of changes that will allow FDA to reach a wider audience.

  • Licensing Changes (All Database Versions for Free)

In previous releases, flashback data archive used compression features from the advanced compression option, so it could only be used with enterprise edition and the advanced compression option. In Oracle 12c, the default when creating flashback data archives is no compression, so it is available for free in all editions of the database. This new default setting has also been back-ported to 11.2.0.4, making it free in all editions that can be patched to that level.

Saturday, 1 October 2016

Online Move of Partitions and Sub-Partitions in Oracle 12c Release 1

In Oracle 12c it is now possible to move table partitions and sub-partitions as online operations.

  • MOVE PARTITION ... ONLINE

The following example shows how to online move a table partition.

-- Create partitioned table.
DROP TABLE t1 PURGE;