Oracle Database 12c PRAGMA UDF and WITH clause enhancements

Here are two interesting enhancements in Oracle database 12c PL/SQL. PL/SQL subprogram defined using WITH clause of a subquery – Oracle database 12c allows PL/SQL declaration section in the WITH clause. One can define PL/SQL function or procedure into a WITH clause. Functions declared in the PL/SQL declaration section can (more...)

Stuck Timezone Upgrades and Smart Scans

I heard a strange report from a client late last week - they weren't getting any smart scans on their Exadata - a data warehouse, no less (and we checked to see if cell_offload_processing was set to true).  Queries would run normally, but for some reason, the "cell physical IO (more...)

Tightening Security with SELECT ANY DICTIONARY in Oracle 12c

Developers and users sometimes request the SELECT ANY DICTIONARY system privilege to enable them to view various data dictionary tables.  This may be fine for querying DBA_TABLES, etc, but the Oracle data dictionary contains a LOT of information.  Some of the views/tables are compromising from a security standpoint. (more...)

Data Pump 12c – Pumping Data with the LOGTIME Parameter

Since its release, Oracle Data Pump has been a worthy successor to the traditional exp/imp tools.  However, one area lacking with Data Pump has been something as simple as the ability to identify how long each step of a Data Pump job actually takes.  The log will show (more...)

Oracle 12c – Out With the Grid, In With the Cloud

So Oracle database 12c is out now.  The cloud (and hence the “c”) is hanging over us.  At least we don’t have to hear about “the grid” anymore.  New versions of Oracle are funny things – as DBAs, we’re kinda excited about the possibilities, but then reality (more...)

12c: Sqlplus Displays Last Login Time For Non – Sys Users

Original Post can be viewed at 12c: Sqlplus Displays Last Login Time For Non – Sys Users

12c database has introduces a pretty nifty Security feature which allows you to check last login time for non-sys user. e.g If I connect to scott user as below, it displays that (more...)

Oracle 12c has arrived

I really didn't think we would be seeing 12c until OpenWorld. I got a nice surprise a couple of weeks ago when people started tweeting that the docs were out. That followed quickly with the actual software being available for download.

 

I realize that people won't really be producing (more...)

12c:Limiting PGA with pga_aggregate_limit

Original Post can be viewed at 12c:Limiting PGA with pga_aggregate_limit

This is a interesting feature for Oracle DBA’s. Till now there has been no way of limiting total PGA being used by oracle instance. _pga_max_size (hidden parameter) could be used to limit per process memory. SGA_TARGET used to be hard (more...)

Creating an Oracle 12c Pluggable Database & Container Database using DBCA

In this example, I’ve already installed the Oracle Database 12c software on one of my Linux 6.4 machines.  Using the DBCA tool (in advanced mode), I’m going to create a new container database named “cdb” and at the same time create a pluggable database named “pdb”. The idea behind (more...)

OBIEE 11.1.1.7.0 Certified with Database 12c

Couple of updates in the world of Oracle, both related. The first is that the Oracle Database 12c is now generally available and can be downloaded from the Oracle Technology Network here (subject to the OTN Developer License Terms). Three platforms are supported - Linux x86-64, Solaris Sparc64, and (more...)

Oracle database 12c

So there is a new toy in the market for database geeks : Oracle has released database 12c. Every social platform is abuzz with the 12c activity. So thought that I should also complete the ritual Winking smile

In this post Aman has already summed up many important links.

Maria Colgan has (more...)

Required Reading

It's not often that I run across articles that really resonate with me. Last night was one of those rare occasions. What follows is a sampling of what I consider to be required reading for any IT professional with a slant towards database development.

Bad CaRMa

Oracle Database Gateways 11g R2 (11.2) Installation and Configuration for heterogeneous connection from Oracle to Microsoft SQL database

Installation

Install the Oracle Database Gateways 11g R2 (11.2). See the screen snapshots of the installation here.

Configuration

  • During the installation, the following default initialization parameter file is created:
[ORACLE_DG_HOME]\dg4msql\admin\initdg4msql.ora
dg4msql = Database gateway for Microsoft SQL Server. 
If you choose a different database option,  (more...)

When to consider sharding a MySQL database

Uncategorized
| Mar 13, 2013

When to consider sharding a MySQL database

 

Why should you not just design your applications from the start to account for data sharding? Sharding a database, that is horizontally partitioning tables among multiple databases, creates an added level of complexity that is not desirable in an application. However, there are times when sharding is required to maintain acceptable performance. So when is the right time to shard your database?

 

When to consider sharding a MySQL database   Why should you not just design your applications from the start to account for data sharding? Sharding a database, that is horizontally partitioning tables among multiple databases, creates an added level of complexity that is not desirable in an application. However, there are times when sharding is […]

-->

Schema and User 101

Uncategorized
| Jan 22, 2013

Schemas and users are frequently used interchangeably as if they were the same thing. For the most part, that's ok. In Oracle the concepts are so tightly coupled that thinking of them as the same thing is not really that far wrong. 


In reality, a user is a database account that is given a password and permissions. A schema is a container, or namespace, for containing schema objects. Schema objects are things like tables, views and indexes.


In Oracle, when a us

How long did Oracle materialized view refresh run?

The LAST_REFRESH_DATE column of the DBA_MVIEWS or the LAST_REFRESH column of the DBA_MVIEW_REFRESH_TIMES indicates the start refresh time. But what if we’d like to find out how long the refresh of the materialized view really takes. Well, we can query the DBA_MVIEW_ANALYSIS.

For Complete Refresh, the refresh duration will be in the FULLREFRESHTIM column of the DBA_MVIEW_ANALYSIS. For Fast Refresh duration, it will be in the INCREFRESHTIM column.

Both values are in seconds.

SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim
FROM dba_mview_analysis
WHERE owner='JOHN';

MVIEW_NAME               LAST_REFRESH_DATE      FULLREFRESHTIM INCREFRESHTIM
------------------------ ---------------------- -------------- -------------
MV_CHANGE_HISTORY        07-JAN-13 04.36.58 PM               0             (more...)

Reinstalling an Oracle Database Appliance Part 3

An Oracle Database appliance is a set of standard hardware and software molded together to created a 2 node RAC cluster that is easy to deploy. In parts 1 & 2 i talked about what methods you can use to reformat or erase the Oracle Database Appliance and rebuild it. (more...)

Restoring a Database with Rman Controlfile Parameters

There are occasions when you recieve a complete backup of a database from a  place different then yours and have to do a restore. normally a restore is simple . put the files in a directory run rman catalog on them and you are done. but what if you have rman (more...)

Oracle Throws in the Towel on VMware Licensing – Reprise

Given the incredible number of comments I received on my last blog post, and the content of those comments, it is very obvious that folks are extremely confused by what I meant by that post, as well as the comments by Mr. Garsthagen (Oracle Director Level Employee), referenced in that post.

The confusion is typified by the following comment, the most recent I have received:

Oracle does not recognize either (Vmware/DRS Affinity) as a hard partition

First, to be completely clear, I have never stated, nor do I believe, that VMware (or any other software hypervisor for that matter) (more...)

Oracle Throws in the Towel on VMware Licensing

As all of my readers know, I have been a strong proponent of VMware virtualization of Oracle Database servers for license cost savings purposes. Predictably, Oracle has pushed back on this issue in the past. Well, they have now thrown in the towel.

In an online video, Richard Garsthagen, Director of Cloud Business Development EMEA for Oracle, has stated publicly that VMware host affinity rules (when combined with vMotion logging) work just fine, thank you very much, for purposes of establishing where Oracle software is "installed and/or running" for purposes of the Oracle Software License Agreement (OSLA).

Previously, (more...)