Ever since I first ran the webinar, Oracle Database 12c SQL and PL/SQL New Features & Development Best Practices, I’ve received a lot of feedback and even more questions on the subject. In this post, I’ve consolidated the most common and vexing of those questions along with my answers.

So without further ado, here are the questions and answers —

Question: What happens when you use an IDENTITY column defined as “BY DEFAULT” (users can explicitly insert values into the identity column) for a primary key column? Will an insert statement fail in when the sequence’s next value already exists in the identity column?

Answer: Yes, your statement will definitely fail.

Suppose you create a table with the primary key column defined as “IDENTITY BY DEFAULT”, then an INSERT statement is performed and the value “2” is explicitly inserted into the primary key column. Then two inserts are performed without specifying primary key values. In this example, the first statement will be fine while the second returns an error since the value “2” already exists in the column, resulting in the message “Unique Constraint Violated”.

Oracle-Database-12c-SQL

Let’s break the process down:

  • CREATE TABLE T1 (id NUMBER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT t1_id_pk PRIMARY KEY, description VARCHAR2(30));
  • Displaying sequence information (used for the identity column):
      SELECT column_name,generation_type,identity_options FROM user_tab_identity_cols WHERE table_name=’T1′
  • Inserting new row explicitly with id “2”:
      INSERT INTO T1 (id,description) VALUES (2,’id = 2’);
  • Inserting new row without specifying id value, identity will auto generate the id using the
      sequence which starts by default with “1”:
      INSERT INTO T1 (description) VALUES (’id = Sequence Next Value’);
  • Inserting new row without specifying id value, this time identity will auto generate the id
      • using the sequence next value which will be “2”:
      INSERT INTO T1 (description) VALUES (’id = Sequence Next Value’);
  • You’ll receive an error since id “2” already exists in the table:

INSERT INTO T1 (description) VALUES (‘id = Sequence Next Value’)

ERROR at line 1:

ORA-00001: unique constraint (HR.T1_ID_PK) violated

Unique-Constraint-Violated-Oracle-Database-12c-SQL
Question: How do you query the dictionary for metadata of invisible columns?

Answer: Running a query based on the data dictionary table called “USER_TAB_COLS”, you’ll see column metadata. The information displayed will includean indication of whether the column is visible or invisible (column name “HIDDEN_COLUMN” will show YES or NO) and the column id tag, which will always be null for an invisible column (column “SEGMENT_COLUMN_ID” will show the actual column id).

SELECT table_name, column_name, column_id, hidden_column, segment_column_id FROM user_tab_cols WHERE table_name=’your table name’ ORDER BY column_id ASC;

Question: Who uses DBmaestro?

Answer: DBmaestro is a Database Enforced Change Management, it’s used by developers and DBAs on a regular basis in order to manage and document all changes committed as part of the development process.

Changes can be done on table structure, procedures, packages, functions and configuration data sets stored in lookup tables. It’s also used by DBAs and developers who are responsible for collecting all changes and generating the database deploy script as part of upgrades. These change logs and deploy scripts are them deceminated either internally to QA or to the production environment.

Question: How much time does it take to implement DBmaestro?

DBmaestro-Oracle-Database-12c-SQL.png

Answer: Installing DBmaestro is very easy and can be completed within a few minutes. The day-to-day functionality (check-out, check-in, view history, labels, …) is straight forward and the learning curve is very gradual.

With the Oracle SQL Developer extension, developers and DBA can perform all the day-to-day activities on the same IDE.

The only complex part of implementing DBmaestro is adding it into the automation processes using DBmaestro web services API. Still, this can be done in less than a week, depending on the culture in the organization.

Question: Is DBmaestro part of Oracle 12C?

Answer: We see DBmaestro as an integral complement to Oracle, thus we developed an Oracle SQL Developer extension for our users so that work can be done without leaving Oracle SQL Developer environment. DBmaestro supports management activties conducted from within Oracle 12c and stores its version repository in Oracle 12c.