Category: 12c New Features

Oct 03

CBO Statistics White Paper

At Oracle OpenWorld 2013, I attended a presentation by Jonathon Lewis, who of course is one of the world’s leading Oracle experts especially when it comes to the Cost Based Optimizer (CBO). As is usually the case, the room was completely packed for his presentation. He had a co-presenter, Maria Colgan who works for Oracle …

Continue reading

Sep 17

12c Move Datafiles Online

Prior to Oracle 12c, if you wanted to move a database’s file, you either had to shutdown the database, or take the datafile/tablespace offline. Here is an example of the steps you might take: ALTER TABLESPACE my_ts OFFLINE; !mv /old_dir/my_ts01.dbf /new_dir/my_ts01.dbf ALTER DATABASE RENAME FILE ‘/old_dir/my_ts01.dbf’ TO ‘/old_dir/my_ts01.dbf’; ALTER TABLESPACE my_ts ONLINE; Now in Oracle …

Continue reading

Sep 17

12c Adaptive Plans

I’ve been meaning to write something up on Oracle 12c’s new Adaptive Plans. When I do have poorly performing SQL statements, it seems that the issue is often due to bad optimizer stats. Recalculate the stats and performance is improved. I like the new 12c feature of Adaptive Plans where the database engine will reformulate …

Continue reading

Sep 05

12c Data Redaction

Oracle 12c’s Advanced Security option now includes Data Redaction. This is a great way to secure your data , transparently to the application, from prying eyes. If an end user does not need the entire tax id, they can be presented with just the last four digits, as an example. To me, this is just …

Continue reading

Aug 01

12c DBA_USERS Changes

Today, I was looking at the ability to query a user’s last login time. Prior to 12c, one had to create a logon trigger to store the logon time in a table or set up auditing and audit successful logins. Now, Oracle 12c includes, by default, a way to determine the last time someone logged …

Continue reading

Jul 31

12c VARCHAR2(32767)

I’ve always wondered why the VARCHAR2 datatype was limited to so few characters. For most attributes, you do not need more than 2,000 bytes. I remember when Oracle upped the limit from 2,000 bytes to 4,000. But SQL Server 2008R2 lets you use 8,000. We are talking about character data and the 2,000 or 4,000 …

Continue reading

Jul 31

12c Autopopulating Column with Sequence Value

In yesterday’s blog post, I wrote about creating a table with an IDENTITY value.  But the Oracle 12c new feature that I like better is allowing a sequence to be the number generator for the default value of a column. To see what I mean, look at this example: SQL> create sequence test_seq; Sequence created. …

Continue reading

Jul 30

12c IDENTITY columns

I live and work close to a Microsoft facility. As such, many of our current employees are former Microsoft employees that come from a SQL Server background. SQL Server lets you create a table with an IDENTITY column. Oracle 12c now lets you do the same. This should help those who are making the transition …

Continue reading