17 July 2013

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 >

No comments:

Post a Comment