Bug in 12c found ;-)

In  $ORACLE_HOME/bin/dbshut I noted the typo

# Stop Oracle Net Listener
  if [ -f $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
    echo "$0: Stoping Oracle Net Listener" >> $LOG 2>&1
    $ORACLE_HOME_LISTNER/bin/lsnrctl stop >> $LOG 2>&1 &
  else
    echo "Failed to auto-stop Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"
  fi

It was there in 10.2, 11.1, 11.2 and now in 12.2. Don't they ever check their syntax?

By the way, Stoping is something different: http://en.wikipedia.org/wiki/Stoping

Oracle 12c: Create an empty container database

In this post I will create an empty container database using dbca. The dbca tool has been around since Oracle 9i (before it was dbassist, if you remember ...).
You will find the tool in $ORACLE_HOME/bin, it's graphic based, so you should set the DISPLAY if you work on Linux.

Ok, startup dbca. Follow the screenshots.

Select the Advanced Mode, Next.

 Now just wait until finished. This strongly depends on your speed of hardware.

Click close and your'e done.

Add Oracle 12c database to Enterprise Manager Cloud Control

It's quite easy to add your 12c databases to Cloud Control. You will need to do so, because the EM Express (which replaces DB Control) is very-very limited.

Just follow Setup --> Add Targets --> Add Targets Manually.

Now you can follow the steps, select host etc.

The Container database and the PDB's come up nicely in Cloud Control:

Note that the Container Database is clearly mentioned. If you click the little arrow icon, you get the PDB list:

This opens up the PDB screen:

During Configuration you will need to set the password for monitoring (default the DBSNMP user). This user is defined on containerlevel, so it doesn't really matter if you set (or reset) the password on CDB or PDB. 

Truncate table cascade in Oracle 12c

In Oracle 12c, the TRUNCATE TABLE CASCADE option is new. Here is a demo.

SCOTT@PDB01 > create table PARENT (
  2  ID number generated always as identity PRIMARY KEY,
  3  NAME VARCHAR2(20)
  4  );

Table created.

SCOTT@PDB01 > create table CHILD (
  2   ID number generated always as identity PRIMARY KEY,
  3  NAMECHILD VARCHAR2(20)
  4  );

Table created.

SCOTT@PDB01 > alter table CHILD add constraint FK_PARENT
  2  FOREIGN KEY (ID) references PARENT(ID) ON DELETE CASCADE;

Table altered.

SCOTT@PDB01 > insert into PARENT (NAME) values ('JOHN');

1 row created.

SCOTT@PDB01 > insert into CHILD (NAMECHILD) values ('JULIAN');

1 row created.

SCOTT@PDB01 > commit;

Commit complete.

SCOTT@PDB01 > select * from PARENT;

        ID NAME
---------- --------------------
         1 JOHN

SCOTT@PDB01 > select * from CHILD;

        ID NAMECHILD
---------- --------------------
         1 JULIAN

SCOTT@PDB01 > truncate table PARENT cascade;

Table truncated.

SCOTT@PDB01 > select * from PARENT;

no rows selected

SCOTT@PDB01 > select * from CHILD;

no rows selected

Oracle 12c: create table with sequence as primary key

I've created a small demo to test a few things.

• connect / as sysdba
• switch to the pluggable database as sysdba
• create a table with a sequence as primary key

More demo will follow.

Enjoy.



oracle@srv01:~> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 17 10:03:14 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @demo
SYS@ORA12CDB AS SYSDBA>
SYS@ORA12CDB AS SYSDBA> conn / as sysdba
Connected.
SYS@ORA12CDB AS SYSDBA> show con_name

CON_NAME
------------------------------
CDB$ROOT

SYS@ORA12CDB AS SYSDBA> alter session set container=PDB01;

Session altered.

SYS@ORA12CDB AS SYSDBA> show con_name

CON_NAME
------------------------------
PDB01
SYS@ORA12CDB AS SYSDBA> drop user scott cascade;

User dropped.

SYS@ORA12CDB AS SYSDBA> create user scott identified by tiger;

User created.

SYS@ORA12CDB AS SYSDBA> grant create session to scott;

Grant succeeded.

SYS@ORA12CDB AS SYSDBA> grant unlimited tablespace to scott;

Grant succeeded.

SYS@ORA12CDB AS SYSDBA> grant create table to scott;

Grant succeeded.

SYS@ORA12CDB AS SYSDBA> grant create sequence to scott;

Grant succeeded.


SYS@ORA12CDB AS SYSDBA> conn scott/tiger@PDB01
Connected.
SCOTT@PDB01 > create table test (
  2     ID number generated always as identity (start with 100) PRIMARY KEY,
  3     NAME VARCHAR2(20)
  4     );

Table created.

SCOTT@PDB01 > insert into test (NAME) values ('JOHN');

1 row created.

SCOTT@PDB01 > insert into test (NAME) values ('PAUL');

1 row created.

SCOTT@PDB01 > insert into test (NAME) values ('GEORGE');

1 row created.

SCOTT@PDB01 > insert into test (NAME) values ('RINGO');

1 row created.

SCOTT@PDB01 > commit;

Commit complete.

SCOTT@PDB01 > select * from test;

        ID NAME
---------- --------------------
       100 JOHN
       101 PAUL
       102 GEORGE
       103 RINGO

SCOTT@PDB01 >