How to Create Multiple Databases in Oracle 12c

Oracle 12c is Oracle's latest version of their database engine. Learn about all of the new Oracle 12c new features for developers in this massive guide.

Introduction

Oracle 12c comes with a range of new features. Many of these features are great improvements for developers, and some of them are more focused on database administrators.

I've read some articles and other material on the new features in Oracle 12c, and while they have been informative, I thought it would be a good idea to create a single, massive guide to all of the Oracle 12c new features for developers.

There's a long list of features, so I'll start off with a Table of Contents, which lists all of the new features.

Then, I'll go into more detail about what they are, and include some code samples where appropriate.

If you like this article, please share it with your coworkers and anyone else you know that might be interested in Oracle SQL. If you own a blog, feel free to link back to this.

Oracle 12c New Features for Developers

This is a list of new features in Oracle 12c. I've included all of the features that I've been able to find information about, and will be focusing on those that are relevant for developers.

• Increased Column Size Limits
• Approx Count
• Row Limiting with a Top-N Query
• Pattern matching within SELECT clause
• JSON Support in the Database
• Lateral Clause for Inline Views
• CROSS APPLY Clause
• OUTER APPLY Clause
• New Optimisation – Partial Join Evaluation
• Cascading Truncat
• Pluggable Databases
• Invisible Columns
• Invisible Indexes
• IDENTITY Columns
• Default Values
• Session Sequences
• Sequence KEEP and NO KEEP
• Data Redactions
• Grant Roles to PL/SQL Programs Only
• UTL_CALLSTACK Package
• Error Stack
• PLSQL – ACCESSIBLE BY Clause
• PLSQL types and the TABLE Operator
• WITH clause can contain PLSQL functions
• Online DDL Statements
• DDL Logging
• Bequeath Option
• Period Definition on Column Definition on Tables (Temporal Validity)

Increased Column Size Limits

In Oracle 12c, you can take advantage of an increased column size for some data types.

The VARCHAR2, NVARCHAR2 and RAW data types allow for a larger number of characters than previous versions.

Data Type	Oracle 11g Limit	Oracle 11g PL/SQL Limit	Limit in Oracle 12c
VARCHAR2	2K	4K	32K
NVARCHAR2	2K	4K	32K
RAW	2K	4K	32K

How can you use these new maximum sizes?

You need to change a setting within the init.ora file. The init.ora file is Oracle's initialization file, which is read every time the database starts up.

The setting is called MAX_STRING_SIZE. There are two values for this:

• STANDARD – The original sizes.
• EXTENDED – The new larger sizes.

There are a few things to remember with the new sizes though:

• With this setting is that you can't reverse it. Once you've set it, there's no going back.
• The maximum size for a column with an index is 6400 bytes. Make sure you're not using any indexes on any column larger than this.
• The database must be in upgrade mode.
• It is stored as a CLOB behind the scenes.

Approx Count

There is a new SQL function in Oracle 12c, called APPROX_COUNT_DISTINCT.

The function provides an approximate discount count, as the name implies. It performs much faster on large data sets, and is almost as close (but not exactly the same) as using a COUNT(DISTINCT).

Finding the number of distinct values of a column is a common task performed by developers. Being able to do it quicker will be a big help, as you'll be able to get your results sooner, and there will be less load on the database.

Example – COUNT(DISTINCT)

This example counts the distinct number of records in a table with 1,000,000 records.

SELECT COUNT(DISTINCT first_name) FROM student;

Example – APPROX_COUNT_DISTINCT

This example uses the new APPROX_COUNT_DISTINCT function to get an approximate number of distinct values from the same table.

SELECT APPROX_COUNT_DISTINCT(first_name) FROM student;

So, if you only need to get an approximate number, then use this function.

Row Limiting with a Top-N Query

Have you ever tried to get the top 10 or 50 rows from a data set?

Have you tried to do this in other SQL variants, such as MS SQL or MySQL, but not been able to do it in Oracle?

Have you tried to use the ROWNUM function but didn't get the results you wanted?

Or, have you tried to do pagination with query results and found it hard to do?

Well, all of that is easier in Oracle 12c.

They have introduced the Top-N query syntax. This lets you specify a certain number of rows to be returned from your query.

This is done using the FETCH FIRST|NEXT|PERCENT clause.

For example, to fetch the first 10 rows from the student table, we can use a query like this:

SELECT first_name, last_name, date_of_birth FROM student ORDER BY date_of_birth FETCH FIRST 10 ROWS ONLY;              

Result:

FIRST_NAME	LAST_NAME	DATE_OF_BIRTH
Tom	Capper	06/Mar/92
Michelle	Randall	23/Jan/93
Susan	Johnson	12/Jan/94
Robert	Pickering	30/Jan/94
Mark	Holloway	20/Jan/95
Tanya	Hall	28/Jan/95
Andrew	Cooper	04/Mar/95
John	Smith	01/Oct/95
Julie	Armstrong	12/Feb/96
Steven	Webber	09/Mar/97

If you use the WITH TIES keyword, you can include records that match row N. For example, if row 11 has the same date_of_birth as row 10, then they will both be included in your result set.

SELECT first_name, last_name, date_of_birth FROM student ORDER BY date_of_birth FETCH FIRST 10 ROWS ONLY WITH TIES;

Result:

FIRST_NAME	LAST_NAME	DATE_OF_BIRTH
Tom	Capper	06/Mar/92
Michelle	Randall	23/Jan/93
Susan	Johnson	12/Jan/94
Robert	Pickering	30/Jan/94
Mark	Holloway	20/Jan/95
Tanya	Hall	28/Jan/95
Andrew	Cooper	04/Mar/95
John	Smith	01/Oct/95
Julie	Armstrong	12/Feb/96
Steven	Webber	09/Mar/97
Michael	Jones	09/Mar/97

Another thing you can do with the Oracle Top-N clause is to display the top N percentage of a result set. For example, you can show the top 5% of students based on the date_of_birth.

SELECT first_name, last_name, date_of_birth FROM student ORDER BY date_of_birth FETCH FIRST 10 PERCENT ROWS ONLY;

Result:

FIRST_NAME	LAST_NAME	DATE_OF_BIRTH
Tom	Capper	06/Mar/92
Michelle	Randall	23/Jan/93
Susan	Johnson	12/Jan/94

Also with the Top N clause, you can display the rows from 11 to 20. As in, you can ignore the first 10 rows and display the next 10.

This is how to do pagination in Oracle. It's much easier than the old methods of using ROWNUM inside subqueries.

SELECT first_name, last_name, date_of_birth FROM student ORDER BY date_of_birth OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Result:

FIRST_NAME	LAST_NAME	DATE_OF_BIRTH
Tanya	Hall	28/Jan/95
Andrew	Cooper	04/Mar/95
John	Smith	01/Oct/95
Julie	Armstrong	12/Feb/96
Steven	Webber	09/Mar/97

This new query syntax can also be used within PL/SQL code. It's not limited to just standard SQL queries.

There are a few drawbacks of this new Top-N syntax though:

• You can't use it within SELECT statements that use the FOR UPDATE keyword.
• If you use it within a materialised view, then that view can't perform an incremental refresh.

Pattern matching within SELECT clause

Oracle 12c introduces some syntax that allows you to do pattern matching – which is where you can compare data across rows to identify any patterns.

Before Oracle 12c, doing this was complicated and inefficient. However, in Oracle 12c, new keywords and syntax allow this to be done much easier.

Why would you want to do pattern matching?

It could be used in data analysis or business intelligence, to identify trends in your company's data. It could be used for stock prices to see the strength of any increase or decrease in the price. It can be used in many other cases where you need to analyse data across multiple rows.

Pattern matching uses the MATCH_RECOGNIZE keyword.

So, what's the syntax for this?

SELECT columns FROM table MATCH_RECOGNIZE (   PARTITION BY  ...   ORDER BY ...   MEASURES ...   PATTERN...   DEFINE... ) ORDER BY col1...

The usage for this feature deserves its own article, so I won't go into details here. However, it's good that this functionality is now included in Oracle 12c.

JSON Support in the Database

Oracle 12c now supports JSON values in database columns.

This means that if you have a JSON string inside a database column, you can query the values from the string directly, just as if they were database columns.

For example, if you had a structure like this in your document column within a table:

{

"businessName":"Cars Galore",

"address":{

"streetNumber":"14",

"streetName":"Main Street",

"city":"Denver",

"state":"Colorado",

"country":"USA"},

"businessIndustry":"Automotive"

}

You could then run a query like this:

SELECT b.document.businessName, b.document.address.streetNumber, b.document.address.streetName FROM businesses b;

This makes it much easier to access data that's in the JSON format in your database.

Lateral Clause for Inline Views

Normally, in Oracle SQL, you can't refer to the columns outside an inline view from within an inline view.

For example:

SELECT first_name, last_name, school_name FROM student s, (SELECT school_name FROM school sc WHERE sc.school_id =                s.school_id)

Notice how I am referring to the student table from within the inline query.

This will give you an error.

However, in Oracle 12c, the concept of LATERAL has been introduced.

The LATERAL keyword allows you to specify the table to the left of the LATERAL keyword inside the inline view to the right of the keyword.

Let's use this in the example above.

SELECT first_name, last_name, school_name FROM student s, LATERAL (SELECT school_name FROM school sc WHERE sc.school_id = s.school_id)

This query should run. It allows for inline views to work like correlated subqueries.

CROSS APPLY Clause

The CROSS APPLY clause is similar to the LATERAL clause.

It's a variant of the CROSS JOIN in Oracle. It returns all rows from the table on the left side of the keyword where at least one row is returned by the table on the right.

The right side of the keyword can reference columns on the left.

For example:

SELECT first_name, last_name, school_id, school_name FROM student s, CROSS APPLY (SELECT school_name FROM school sc WHERE sc.school_id = s.school_id AND sc.school_state = 'California')

What's the difference between a CROSS APPLY and an INNER JOIN?

Well, a CROSS APPLY can be used when there is no easy way to run an inner join. This can also be used when you need to run a function on the table with the parameters to be executed once per row, and then joined to the main table.

Check out this Stack Overflow question for some further examples.

You can also read the guide to Cross Apply (and Outer Apply) here.

OUTER APPLY Clause

The OUTER APPLY keyword is similar to the CROSS APPLY, but it's more like the LEFT OUTER JOIN.

It returns all rows from the left side of the table, but if there are no rows that match on the right side of the table, then a NULL is displayed.

SELECT first_name, last_name, school_id, school_name FROM student s, OUTER APPLY (SELECT school_name FROM school sc WHERE sc.school_id = s.school_id AND sc.school_state = 'California')

Just like with the CROSS APPLY, you can use the OUTER APPLY instead of an OUTER JOIN when the two tables can't be easily joined with an OUTER JOIN query.

New Optimisation – Partial Join Evaluation

Oracle 12c includes a new type of evaluation as part of the optimisation process.

When you write a query that has a standard IN clause or EXISTS subquery, Oracle would normally give you two different execution plans.

In Oracle 12c, the optimizer will recognise that these are similar queries and may perform a different type of optimization, called a Partial Join Evaluation, or PJE.

You might notice this if you run an Explain Plan on any of your queries. It just means Oracle has worked out a good way of running the query.

Cascading Truncate

If you try to run a TRUNCATE statement on a table that has records in another table linked to it using a foreign key (a parent-child relationship), then you'll get this error:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

This means you can't delete the parent records using TRUNCATE without deleting the child records first.

In Oracle 12c, you can use the Cascading Truncate syntax to skip this step.

TRUNCATE TABLE parent_table_name CASCADE;

This will only work if the foreign key is defined as ON DELETE CASCADE. Otherwise, you'll get the same error.

If you have this set, and you run this TRUNCATE command, then the parent table's records will get deleted, and any related records in the child's table will get deleted as well.

Pluggable Databases

Pluggable databases are new features in Oracle 12c.

They are essentially a database that contains other databases. It's probably the main feature that Oracle 12c has introduced.

As a developer, the might not be all that relevant to you, but they are still good to know because they can impact the way you design your system and write queries.

So, how does it work?

A "root" database contains some metadata about the databases, and data about common users between all of the database.

You then have a "seed" database, which is a template for new databases.

Finally, you have the actual "pluggable database". This is the actual database that is used by the application, where you store your application data.

So, why would this benefit you?

• One set of Oracle executables per container, which saves a lot of resources
• Setup features are shared across all pluggable databases
• Backup and resource management is easier
• Overall database administration is much easier
• Allows the move to a more could-based infrastructure, such as easily setting up new databases.

A container can store up to 252 pluggable databases – which seems like a lot!

Users can also share pluggable databases, and these users are called "common users".

You can perform cross-pluggable-database queries as well. So, if you're on one pluggable database, you can run a query that selects from another pluggable database within the same container.

However, there is less flexibility if you write code this way.

What are the drawbacks?

Well, Dan Hotka has outlined some of the drawbacks of pluggable databases, such as:

• You might have the same synonym names pointing to different objects in each pluggable database.
• You can have the same schema names in multiple pluggable databases which can cause confusion
• It only supports Oracle 12 databases

Overall, it seems that pluggable databases are a useful feature and I can really see the benefits of them to companies who implement them.

Invisible Columns

Oracle 12c introduces the concept of invisible columns.

An invisible column is a column that:

• Does not appear in the table definition
• Does not appear in SELECT * queries

To define an invisible column, you can use the keyword "invisible" when defining a column.

Temporary tables, external tables, and cluster tables don't support invisible columns.

Here's an example.

CREATE TABLE invis_test ( first NUMBER, second NUMBER, third NUMBER INVISIBLE );  INSERT INTO invis_test VALUES (1, 2); INSERT INTO invis_test (first, second, third) VALUES (3, 4, 5);

Notice that I did not need to specify the invisible column when I ran the first INSERT statement. It is set to NULL.

Now, let's query the table using SELECT *.

SELECT * FROM invis_test;

Result:

As you can see, both rows are showing, but there is no column for THIRD.

Let's specify the columns now.

SELECT first, second, third FROM invis_test;

Result:

FIRST	SECOND	THIRD
1	2
3	4	5

The invisible column is shown, but the value is set to NULL for the first row, as it was not specified in the INSERT statement.

Columns in views can also be set to invisible.

Invisible Indexes

Before Oracle 12c, if you tried to create an index on a column (or multiple columns), you couldn't create another index using those same columns in the same order.

In Oracle 12c, however, you can do this. You just need to use the INVISIBLE keyword, and ensure that only one index for a combination of columns is VISIBLE at a time.

Here's an example.

CREATE INDEX idx1 ON student (student_id, first_name); CREATE BITMAP INDEX idx2 ON student(student_id, first_name) INVISIBLE;

If you want to use invisible indexes in your optimisation, you can turn this on at the session level:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

To turn it off, just set this value back to False;

IDENTITY Columns

In other databases, such as MySQL, you can set a column to use automatically generated values. This is often used for primary keys, so that when you insert a record, the primary key is generated automatically.

This is implemented as AUTO_INCREMENT in MySQL.

Before Oracle 12c, to do this, you needed to use a sequence and a BEFORE INSERT trigger, as I've outlined my article on how to create an auto increment column.

Now, in Oracle 12c, it's much easier to do this.

You can use the IDENTITY feature to set a column as an identity, which works in much the same way as an auto increment column.

Here's an example:

CREATE TABLE idtest ( new_id NUMBER GENERATED AS IDENTITY, first_name VARCHAR2(100) last_name VARCHAR2(100) );

Now, when you insert data into this table, you don't need to specify a value for new_id. It will get generated automatically.

INSERT INTO idtest (first_name, last_name) VALUES ('Peter', 'Parker'); INSERT INTO idtest (first_name, last_name) VALUES ('Clark', 'Kent'); INSERT INTO idtest (first_name, last_name) VALUES ('Bruce', 'Wayne');

Then we can select the data.

SELECT new_id, first_name, last_name FROM idtest;

NEW_ID	FIRST_NAME	LAST_NAME
1	Peter	Parker
2	Clark	Kent
3	Bruce	Wayne

You can also specify the identity column as though it was a sequence.

CREATE TABLE idtest2 ( new_id NUMBER GENERATED AS IDENTITY (START WITH 100 INCREMENT BY 10) testval VARCHAR2(50) );

Now, whenever you insert new values, the values will start at 100 and go up by 10 (100, 110, 120, 130, etc).

Default Values

There are a few changes to the way Oracle 12c allows default values.

In Oracle 12c, you can:

• Use sequences as the default value for a column
• Use default values only when the specified value is NULL

Before Oracle 12c, this had to be done using INSERT triggers.

This is a great way to simplify your programs and data entry.

Let's take a look at an example, where we have a normal column, a column that defaults to a sequence, and a column that uses defaults only when NULL.

CREATE SEQUENCE seq_test;  CREATE TABLE default_test ( seq_val NUMBER DEFAULT seq_test.NEXTVAL PRIMARY KEY, null_val NUMBER DEFAULT ON NULL 99 normal_val NUMBER );

Now, let's insert some data into our table.

INSERT INTO default_test (null_val, normal_val) VALUES (1, 2); INSERT INTO default_test (null_val, normal_val) VALUES (NULL, 3); INSERT INTO default_test (null_val, normal_val) VALUES (NULL, 4); INSERT INTO default_test (seq_val, null_val, normal_val) VALUES (10, 11, 12); INSERT INTO default_test (normal_val) VALUES (13);

The output would look like this:

SEQ_VAL	NULL_VAL	NORMAL_VAL
1	1	2
2	99	3
3	99	4
10	11	12
4	99	13

Session Sequences

Before Oracle 12c, whenever you created a sequence, the values were unique within the database. Every time you requested a new sequence value using NEXTVAL, the sequence's value was incremented, based on how you had set up the sequence.

Oracle 12c introduces the concept of "session sequences".

A session sequence is a sequence that retains its value only for the session, and is reset at the end of the session.

This may not work well for primary keys, as values can be repeated, but you can find other uses for it (such as within temporary tables).

To define a normal sequence, you can add the word GLOBAL to the end of the sequence definition, or leave it off.

CREATE SEQUENCE global_seq1; CREATE SEQUENCE global_seq1 GLOBAL;

To create a session sequence, you can specify the word SESSION at the end of the sequence definition.

CREATE SEQUENCE session_seq SESSION;

From here, they are used just like regular sequences, but the current value is not persisted between sessions.

Sequence KEEP and NO KEEP

KEEP and NOKEEP are two keywords that can be applied when creating sequences.

KEEP allows the NEXTVAL to retain its original value during replays for Application Continuity.

NOKEEP means the NEXTVAL will not retain its original value during replays for Application Continuity. NOKEEP is the default setting.

For KEEP to work, the user running the application needs to be the owner of the schema containing the sequence.

Data Redactions

Data Redaction is a new feature in Oracle 12c, which allows you to hide certain columns of data based on a user's role, without affecting the way the data is stored.

It allows you to hide sensitive information, such as dates of birth or government ID numbers, from users, while keeping the actual values stored in the database.

Have you ever seen one of those action or spy movies where someone tries to get a copy of a report from a secret mission from the CIA or something, and when they get the report, there are all these black lines covering up all the details that they are looking for? And then the guy who gets the report says, "Oh no, it's been redacted!" Well, this is a similar thing.

When you use data redaction, the real data is still stored in the database, but it is transformed based on the rules that have been set up before it is returned to the user.

It's great for production environments to protect data.

The way data redaction works in Oracle 12c is:

• You add a new "policy" using a specific inbuilt Oracle PL/SQL procedure.
• You add rules to this policy to specify what column is to be redacted, the level of redaction, and who can see the real data.

There are a few different rules or levels of data redaction:

• Full redaction: All values in the column are replaced depending on their data type. Numbers are returned as 0, characters are returned as space.
• Partial redaction: Only part of the information is changed.
• Regular expressions: You can use a regular expression to change part of the database column.
• Random redaction: The value returned is different every time a query is run.
• No redaction: No data redaction is applied, which is useful for testing purposes.

To apply data redaction, you can use a range of PL/SQL procedures:

• ALTER_POLICY
• DISABLE_POLICY
• DROP_POLICY
• ENABLE_POLICY
• UPDATE_FULL_REDACTION_VALUES

This is an interesting new feature, and I can think of many ways that this can be used.

Grant Roles to PL/SQL Programs Only

Prior to Oracle 12c, when a PL/SQL program accessed a table, you needed to grant access to the user to both the PL/SQL program and the table that it accessed.

If you didn't, you would get some kind of access error.

Now, in Oracle 12c, you can grant access to the user to just the PL/SQL program, and not the table it reads from.

This makes it much more secure. It means that you can set up your database so users do not have access to the underlying tables.

UTL_CALLSTACK Package

In Oracle 11g and earlier versions, you can use a procedure called DBMS_UTILITY.FORMAT_CALL_STACK to get information about your "call stack" (the functions that are called and what order they are called in).

Now, in Oracle 12c, the functionality has been improved. You can use the package called UTL_CALL_STACK to get even more information about your call stack, and in a better format.

You can run the following functions:

• OWNER: The owner of the subprogram.
• CURRENT_EDITION: The edition of the subprogram.
• DYNAMIC_DEPTH: This displays the number of subprograms on the call stack to the initial call.
• LEXICAL_DEPTH: This displays the depth of the subprogram of the current call.
• UNIT_LINE: The line of the subprogram.
• SUBPROGRAM: The name of the subprogram.
• CONCATENATE_SUBPROGRAM: Displays the subprogram in the format of UNIT.SUBPROGRAM.LOCAL_SUBPROGRAM.

Let's look at an example of this:

CREATE OR REPLACE PROCEUDRE test_callstack AS P_depth PLS_INTEGER; BEGIN   P_depth := UTL_CALL_STACK.dynamic_length   Dbms_output.put_line('Owner, Depth, Lexical Depth, Line Number, Name');   FOR I IN 1 .. p_depth LOOP     UTL_CALL_STACK.owner (i) || ', ' ||     DBMS_output.put_line(I || ', ' ||     UTL_CALL_STACK.lexical_depth(i) || ', ' ||     UTL_CALL_STACK.unit_line(i) || ', ' ||     UTL_CALL_STACK.subprogram(i));   END LOOP; END;  CREATE OR REPLACE PACKAGE run_package AS   PROCEDURE run_proc1;   PROCEDURE run_proc2;   PROCEDURE run_proc3;   PROCEDURE run_proc4; END;  CREATE OR REPLACE PACKAGE BODY run_package AS PROCEDURE run_proc1 AS BEGIN   Proc4; END;  PROCEDURE run_proc2 AS BEGIN   Proc3; END;  PROCEDURE run_proc3 AS BEGIN   Test_callstack; END;  PROCEDURE run_proc4 AS BEGIN   Proc2; END;

Now, we have the packages created, we can run them, and see what the output is.

EXEC run_package.run_proc1;

Result:

Owner, Depth, Lexical Depth, Line Number, Name

BB, 1, 0, 10, TEST_CALLSTACK

BB, 2, 1, 20, run_proc3

BB, 3, 1, 16, run_proc2

BB, 4, 1, 12, run_proc4

BB, 5, 1, 8, run_proc1

BB, 6, 0,1, _anonymous_block

I've displayed this in a simple comma-separated list of function calls, but you can display it however you like.

Error Stack

You can also use some of the functions inside UTL_CALLSTACK to get information about errors.

These functions are:

• ERROR_DEPTH: Number of errors on the stack.
• ERROR_MSG: Error message from the current line in the error stack.
• ERROR_NUMBER: Error number from the current line in the error stack.

PLSQL – ACCESSIBLE BY Clause

The ACCESSIBLE BY clause is a new clause in Oracle 12c. It allows you to specify which packages can have access to other packages.

Before Oracle 12c, if you wanted to limit package A to only be accessible by package B, you had to call it from within package B from a private package.

Now, you can use the ACCESSIBLE BY clause to make this easier.

For example:

PACKAGE package_a ACCESSIBLE BY (package_b) IS PROCEDURE someProcedure; END;

This makes it much easier to control access to packages by other packages, therefore making your database more secure and easier to maintain.

PLSQL types and the TABLE Operator

Before Oracle 12c, you could only use the TABLE operator with locally defined types if they were used within pipelined table functions.

Now, in Oracle 12c, you can use them with locally defined types without this restriction.

WITH clause can contain PLSQL functions

In Oracle 12c, you can define PL/SQL functions and procedures inside a WITH clause.

This will likely make your code run faster than if it was a separately compiled function.

Let's take a look at an example.

WITH FUNCTION findLatestHireDate IS BEGIN   SELECT MAX(hire_date)   FROM employee; END SELECT department_name, findLatestHireDate FROM department;

Result:

MAX(HIREDATE)

03/Oct/16

This means that your function is compiled when you run your query, rather than creating an object and storing it in the database.

Online DDL Statements

Normally, DDL statements lock tables that they are working on. This can cause delays with accessing the tables and the data inside them.

In Oracle 12c, some operations can now be done using an "online" parameter, which means they do not impact any DML that is currently running and they don't lock the tables.

The commands that can do this are:

• DROP INDEX ONLINE
• DROP CONSTRAINT ONLINE
• SET UNUSED COLUMN ONLINE
• ALTER INDEX UNUSABLE ONLINE
• ALTER INDEX [VISIBLE | INVISIBLE]

DDL Logging

In Oracle 12c, you can set an option on the database so that all DDL activity is logged.

This makes it easier to see when DDL statements, such as CREATE and ALTER, were run.

You can turn this on at the database level or just for your session.

To turn it on at the database level:

ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

To turn it on at the session level

ALTER SESSION SET ENABLE_DDL_LOGGING=TRUE;

Every time a DDL statement is run, it is logged in an XML file. This XML file is located at $ORACLE_BASE/diag/rdbms/DBNAME/log/ddl.

The following statements are logged in this file:

• CREATE, ALTER, DROP and TRUNCATE TABLE
• CREATE, ALTER, DROP PACKAGE/FUNCTION/VIEW/SYNONYM/SEQUENCE
• DROP USER

Bequeath Option

The BEQUEATH option in Oracle 12c allows you to define which user's privileges are used when running a statement.

Currently, when you run an SQL statement on a view or a function, it runs as though the owner of that object has called the object.

In Oracle 12c, you can set up your views and other objects so they run using the invoker's rights instead of the definer's rights.

To clarify:

• Definer's rights – the rights and privileges of the person who defined or created the object.
• Invoker's rights – the rights and privileges of the person who is calling the object or running a statement using the object.

To do this, you add a BEQUEATH clause to your CREATE statement, either BEQUEATH CURRENT_USER (for invoker) or BEQUEATH DEFINER (for definer).

For example:

CREATE VIEW my_employee_view BEQUEATH CURRENT_USER AS SELECT first_name, last_name FROM employee;

This means the view is run with the rights of the person who queries the view.

As an alternative:

CREATE VIEW my_employee_view2 BEQUEATH DEFINER AS SELECT first_name, last_name FROM employee;

This means the view is run with the rights of the person who owns this view.

The default behaviour is BEQUEATH DEFINER if no BEQUEATH clause is specified.

Hopefully this section explains this concept clearly. I had a hard time finding a page that explained this concept to me easily!

Period Definition on Column Definition on Tables (Temporal Validity)

Have you ever worked on a database, or designed a database, where you needed to have some records valid over a certain date range, and others valid for other date ranges?

Did you implement this using start_date and end_date columns? I've done this a few times.

In Oracle 12c, the concept of period definition, or "temporal validity" has been introduced.

It allows the database to track which records are valid at a particular date.

This is done by altering the table to add a new keyword allowing for these kinds of queries.

When you create a table, you add a PERIOD clause:

CREATE TABLE student ( student_id NUMBER first_name VARCHAR2(50), last_name VARCHAR2(50), start_date DATE, end_date DATE, period for valid(start_date, end_date);

This PERIOD keyword can be added in to existing tables, so if you have tables that have start and end dates, there's no need to delete them.

Now, when you run your queries, you can specify a date to use for checking when a record is valid.

SELECT student_id, first_name, last_name, start_date, end_date FROM student AS OF PERIOD FOR VALID sysdate;              

This query will show all records that are valid as of today (using SYSDATE). It finds records where the SYSDATE is between start_date and end_date.

You can write this query to use any date – in the future or in the past.

Using a future date:

SELECT student_id, first_name, last_name, start_date, end_date FROM student AS OF PERIOD FOR VALID sysdate+31;

Using a past date

SELECT student_id, first_name, last_name, start_date, end_date FROM student AS OF PERIOD FOR VALID sysdate-180;

This simple addition to Oracle's functionality should help you if you're using this kind of logic.

Conclusion

There are a lot of new features in Oracle 12c. I have detailed many of them in this article, as I believe these are the features most useful to Oracle SQL developers.

There's a lot that I haven't included, mainly due to them being more specific to DBAs.

You can use this page as a resource if you're new to Oracle 12c or looking to upgrade in the future.

Leave a comment below and let me know what you think or if you have any suggestions for this article.

Lastly, if you enjoy the information and career advice I've been providing,sign up to my newsletter below to stay up-to-date on my articles. You'll also receive a fantastic bonus. Thanks!

Image courtesy of cuteimage / FreeDigitalPhotos.net

How to Create Multiple Databases in Oracle 12c

Source: https://www.databasestar.com/oracle-12c-new-features-for-developers/

Share :