Create Pluggable Database using the seed database as a template

In my previous posts, I combined tools SQL*Plus, SQL Developer, and Oracle Enterprise Manager EM12c for many different tasks. They allow me to connect to the pluggable database PDBORCL, to create PL/SQL subprograms and packages, to run SQL queries, DML, and DDL statements. I also showed how to make the PDBORCL pluggable database larger by adding a new tablespace to it.

All of that was the introduction to this post as I am going to demonstrate how to create a new pluggable database.

Oracle database version 12.1.0.2 introduces 3 new SQL DDL statements:
– CREATE PLUGGABLE DATABASE
– ALTER PLUGGABLE DATABASE
– DROP PLUGGABLE DATABASE

The CREATE PLUGGABLE DATABASE statement enables us to perform the following tasks:
1) Create a PDB by using the seed pluggable database PDBSEED as a template.
The files associated with the seed are copied to a new location and the copied files are then associated with the new PDB.
2) Create a PDB by cloning an existing PDB or non-CDB.
The files associated with the existing PDB or non-CDB are copied to a new location    and the copied files are associated with the new PDB. Here, it should be noticed that creating a PDB by cloning a non-CDB is available starting with Oracle Database 12c Release 1 (12.1.0.2).
3) Create a PDB by plugging an unplugged PDB or a non-CDB into a CDB.
This option is done by  using an XML metadata file.

The pluggable database has four different open modes:
1) READ WRITE
A PDB in open read/write mode allows queries and user transactions to proceed and allows users to generate redo logs.
2) READ ONLY
A PDB in open read-only mode allows queries but does not allow user changes.
3) MIGRATE
When a PDB is in open migrate mode, you can run database upgrade scripts on the PDB.
4) MOUNTED
When a PDB is in mounted mode, it behaves like a non-CDB in mounted mode. It does not allow changes to any objects, and it is accessible only to database administrators. It cannot read from or write to data files. Information about the PDB is removed from memory caches. Cold backups of the PDB are possible.

To remove a PDB from a CDB the following should be done:
– Unplug the PDB from a CDB.
– Drop the PDB.

task#11 Connect to the Container database CDB$ROOT as SYSDBA and create a pluggable database “BOOKSTORE” by using the seed pluggable database PDBSEED as a template.

step#1 Create BOOKSTORE database

SYS@orcl > create pluggable database "BOOKSTORE"
2  admin user "BOOKSTOREADMIN"
3  identified by "BOOKSTOREADMIN"
4  file_name_convert = ('C:\app\orauser\oradata\orcl\pdbseed\',
5  'C:\app\orauser\oradata\orcl\bookstore\');

Pluggable database created.

The other way to create the pluggable database “BOOKSTORE” is to omitt the clause FILE_NAME_CONVERT.
In that case, the database will attempt to use the Oracle Managed Files or the PDB_FILE_NAME_CONVERT initialization parameter.
In my environment, the Oracle Managed Files are not specified, so the database will attempt to use the parameter PDB_FILE_NAME_CONVERT which could be defined by using statements ALTER SYSTEM or ALTER SESSION. For this task I will be using ALTER SESSION statement

SYS@orcl > ALTER SESSION SET PDB_FILE_NAME_CONVERT = ('C:\app\orauser\oradata\orcl\pdbseed\',
2  'C:\app\orauser\oradata\orcl\bookstore\');

Session altered.

Now, the CREATE PLUGGABLE DATABASE statement is simple

SYS@orcl > create pluggable database "BOOKSTORE"
2  admin user "BOOKSTOREADMIN"
3  identified by "BOOKSTOREADMIN";

Pluggable database created.

If the parameter PDB_FILE_NAME_CONVERT is not set, then an error will occur after the CREATE PLUGGABLE DATABASE statement.
step#2 Open BOOKSTORE database in READ WRITE mode

SYS@orcl > alter pluggable database "BOOKSTORE" open read write;

Pluggable database altered.

step#3 Prove that CDB$ROOT now contains the seed and two PDBs

SYS@orcl > show PDBS

CON_ID      CON_NAME             PEN MODE  RESTRICTED
---------- --------------------  ---------- ----------
2          PDB$SEED              READ ONLY  NO
3          PDBORCL               READ WRITE NO
4          BOOKSTORE             READ WRITE NO

step4# Show the open time of PDBs

SYS@orcl > select substr(name,1,10) name
, con_id, open_mode
, to_char(open_time,'DD-MON-YYYY') open_time
,total_size
from v$pdbs;

NAME           CON_ID OPEN_MODE  OPEN_TIME   TOTAL_SIZE
---------- ---------- ---------- ----------- ----------
PDB$SEED            2 READ ONLY  10-SEP-2015  975175680
PDBORCL             3 READ WRITE 10-SEP-2015 2747269120
BOOKSTORE           4 READ WRITE 10-SEP-2015  996147200

step5# Start SQL Developer and show that CDB$ROOT has two pluggable databases: PDBORCL and BOOKSTORE

The Container Database

The Container Database

step#6 Open the list Datafiles in the DBA View and show that there is no additional datafiles for CDB$ROOT.

Datafiles

Datafiles

step#7 Open the list Tablespaces in the DBA View and show that there is no added tablespaces for CDB$ROOT either.

Tablespaces

Tablespaces

step#8 Create a new connection to BOOKSTORE database in the Connection View

Create Connection

Create Connection

The following elements should be specified in order to create a connection: the connection name,  the user name,  the password,  connection type and role, the hostname, the port, and the service name.

The Connection has been Created

The Connection has been Created

We are connected to the BOOKSTORE database which is empty of the user elements for now.

step#9 Click on the bookstore connection and choose the option Manage Database to show BOOKSTORE Tablespaces.

ManageDatabase

Manage Database

step#10 BOOKSTORE contains the following tablespaces: SYSTEM, SYSAUX, and TEMP. In order to create any schema, BOOKSTORE needs a new tablespace for users tables and other schema elements.

BOOKSTORE tablespaces

BOOKSTORE tablespaces

step#11 Open the Database Configuration Assistant and create EM12c port 5504 as the entry point for BOOOKSTORE database.

The Configuration Assistant

The Configuration Assistant

step#12 Open the browser with “localhost:5504/em” and login to BOOKSTORE as SYSDBA

BOOKSTORE EM12c

BOOKSTORE EM12c

step#13 Show how many tablespaces are there in the BOOKSTORE database

Tablespaces Of BOOKSTORE

Tablespaces Of BOOKSTORE

step#14 Create a new tablespace “USER”

Create Tablespace USER

Create Tablespace USER

step#15 The USER tablespace is created and we are ready to create the very first schema in the BOOKSTORE database.

The USER tablespace

The USER tablespace

step#16 Back to SQL Developer again, the BOOKSTORE database is ready for the new elements.

SQL Developer worksheet

SQL Developer worksheet

step#17 Click on the BOOKSTORE connection in the Connect View and choose the option Create User.

Create Schema

Create Schema

step#18 Enter the name, the password, roles, privileges, quotas, and default and temporary tablespace for the new user.

Enter details about the schema

Enter details about the schema

The following SQL CREATE USER statement will be automatically created and executed and the user “CODE” will be created

CREATE USER code IDENTIFIED BY code
DEFAULT TABLESPACE "USER"
TEMPORARY TABLESPACE "TEMP";

GRANT "CONNECT" TO code ;
GRANT "RESOURCE" TO code ;
ALTER USER code DEFAULT ROLE "CONNECT","RESOURCE";

step#19 Open the Oracle Network Manager and create the service @bookstore,
enter the connection type, protocol, the host name, and the port number.

Net Manager

Net Manager

step#20 Use SQL*Plus and connect to the BOOKSTORE database as user code/code@bookstore

@bookstore

@bookstore

step#21 Show The Oracle Listener configuration

C:\temp>lsnrctl

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 10-SEP-2015 23:13:18

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start           stop            status          services
version         reload          save_config     trace
quit            exit            set*            show*

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                10-SEP-2015 15:04:56
Uptime                    0 days 8 hr. 8 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\orauser\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\orauser\diag\tnslsnr\HPblue\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=HPblue)(PORT=5500))(Security=(my_wallet_directory=C:\APP\ORAUSER\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=HPblue)(PORT=5502))(Security=(my_wallet_directory=C:\APP\ORAUSER\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=HPblue)(PORT=5504))(Security=(my_wallet_directory=C:\APP\ORAUSER\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "bookstore" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "bookstore" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:50 refused:0 state:ready
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:50 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:68 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: HPBLUE, pid: 2564>
(ADDRESS=(PROTOCOL=tcp)(HOST=HPblue)(PORT=49169))
Service "pdborcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:50 refused:0 state:ready
LOCAL SERVER
The command completed successfully
LSNRCTL>

Summary

The Oracle Database 12c SQL statement CREATE PLUGGABLE DATABASE allows us to create a new pluggable database  by using the seed pluggable database PDBSEED as a template. The new database does not contain any user tablespace for schemas and the schema elements ( tables, indexes, etc.) and it should be created next.

The other ways to create pluggable databases are by cloning an existing PDB or non-CDB, or by plugging an unplugged PDB or a non-CDB into a CDB.

Beside SQL*Plus, SQL Developer, and Oracle Enterprise Manager EM12c,  two new tools are introduced : the Oracle Net Manager and the Database Configuration Assistant.

EM12c comes to the rescue when you need a Tablespace for the new Application

I like to combine all Oracle Database 12c Tools: SQL*Plus, SQL Developer, and Oracle Enterprise manager Database Express 12 – EM12c. While I am working on a project, this allows me to switch fast from one tool to the other especially when I need to run any DDL statements, then go back to SQL Developer and create and test some PL/SQL code. Usually, EM12c is extremely fast for the tablespace creation or its management, especially when the new project starts.

task#9 In this task I will demonstrate the fastest way to create a tablespace when you  needed it the most.

step# Connect to the pluggable database PDBORCL through EM12c using address “localhost:5502/em”. Ports 5500 and 5502 are by default reserved and assigned to the CDB$ROOT and PDBORCL databases accordingly on my Oracle Database 12c.

EM12c Connect To PDBORCL

EM12c Connect To PDBORCL

step#2 Open the Tablespace control window.

The Pluggable Database Tablespaces

The Pluggable Database Tablespaces

At the moment, we have 6 tablespaces: SYSTEM, SYSAUX, USERS, TEMP, EXAMPLE, and COMPANYAB.

step#3 Click on the Action Button and choose the Create option.

The Action Button

The Action Button

There are other options too, we can Drop any of the existing tablespaces, change the Status of the tablespace, Add a new datafile to the existing tablespace, and set a tablespace as Default one.

step#4 In the open Create tablespace window, enter the name of the new tablespace, in this case “COMPANYCD”, then click “>” button and follow the procedure.

Create a New Tablespace

Create a New Tablespace

step#5 See what SQL “CREATE TABLESPACE” statement will be executed, and make sure that all parameters are correct, such as the name of the datafile, size, logging, and compression. And if it is all as it should be, press OK button to execute that DDL statement.

The SQL CREATE TABLESPACE statement

The SQL CREATE TABLESPACE statement

step#6 The new tablespace has been created successfully.

The Tablespace Successful Creation

The Tablespace Successful Creation

step#7 Finally, The list of tablespaces  has a new member, the tablespace “COMPANYCD”, and we are ready to start a new app.

The List Of the Current Tablespaces

The List Of the Current Tablespaces

step#8 Check with SQL*Plus that the tablespace datafile “COMPANYCD.DBF” is in the right directory on your computer

c:\TEMP>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 9 00:42:10 2015

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@orcl > select dbms_xdb.getHttpPort() from dual;

DBMS_XDB.GETHTTPPORT()

----------------------

0

SYS@orcl > select dbms_xdb_config.getHttpsPort() from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()

------------------------------

5500

SYS@orcl > host

Microsoft Windows [Version 6.3.9600]

(c) 2013 Microsoft Corporation. All rights reserved.

c:\TEMP>dir C:\app\orauser\oradata\orcl\pdborcl

Volume in drive C has no label.

Volume Serial Number is C2DD-F7B2

Directory of C:\app\orauser\oradata\orcl\pdborcl

09/09/2015 01:00  .

09/09/2015 01:00  ..

06/09/2015 22:41 104,865,792 COMPANYAB.DBF

09/09/2015 01:00 104,865,792 COMPANYCD.DBF

06/09/2015 22:41 1,456,218,112 EXAMPLE01.DBF

06/09/2015 22:32 20,979,712 PDBORCL_TEMP012014-12-09_02-14-58-AM.DBF

07/09/2015 18:54 22,290,432 SAMPLE_SCHEMA_USERS01.DBF

06/09/2015 22:41 734,011,392 SYSAUX01.DBF

06/09/2015 22:41 304,095,232 SYSTEM01.DBF

7 File(s) 2,747,326,464 bytes

2 Dir(s) 562,110,873,600 bytes free

c:\TEMP>exit

SYS@orcl >

Summary

The Oracle Corporation recommends that the separate tablespaces should be created for any customer new applications in order to guarantee isolation, security, and easy management of the application and its tablespaces.

In this task I have shown how fast and easy is to create a tablespace on the fly using EM12c when you are desperate to start a new project.

Review – Oracle Database 12c New SQL*Plus commands

SQL*Plus is the Command Line User Interface that is installed with every Oracle Database installation.
It allows the user to connect to the database, perform database administration, create and execute batch scripts, get the description of any schema object, and format, save, and print query result sets.

Every new release of the Oracle Database brings to the users a new SQL*Plus set of commands and options.

task#8 For this task we will use the table DEMO.TEXTS that was introduced in the previous post task#7. Also, the new procedure proc_impl_res was created to demonstrate the SQL*Plus support for implicit results.
step#1 Last Login Time

By default, SQL*Plus displays the time the user last logged on.
This security feature can be turned off with a SQLPLUS command option -NOLOGINTIME.

SQL*Plus Last Login Time

SQL*Plus Last Login Time

step#2 New Administrative Privileges

The new user privileges SYSBACKUP, SYSDG, and SYSKM are supported by SQLPLUS and CONNECT commands.
The existing privileges SYSASM, SYSDBA, and SYSOPER are supported too.

SQL*Plus new user privileges SYSBACKUP, SYSDG, and SYSKM

SQL*Plus new user privileges SYSBACKUP, SYSDG, and SYSKM

step#3 Support for Implicit Results

SQL*Plus can iteratively return results from a PL/SQL Statement without using a local ref cursor.

create or replace procedure proc_impl_res
authid current_user
as
  l_cursor_min sys_refcursor;
  l_cursor_max sys_refcursor;
begin
  open l_cursor_min for SELECT * FROM TEXTS where winter > 100;
  dbms_sql.return_result(l_cursor_min);
  open l_cursor_max for SELECT * FROM TEXTS where summer > 1000000;
  dbms_sql.return_result(l_cursor_max);
exception
  when others then
  dbms_output.put_line(SQLERRM);
end;
SQL*Plus Support for Implicit Results

SQL*Plus Support for Implicit Results

step#4 Displaying Invisible Columns

SQL*Plus has the command SET COLINVI[SIBLE] to allow invisible column
information to be viewed with the SQL*Plus DESCRIBE command.

SQL*Plus Displaying Invisible Columns

SQL*Plus Displaying Invisible Columns

step#5 Pluggable Database Support

The STARTUP command options support pluggable databases.
There are also SHOW command options to display information about pluggable
databases: SHOW CON_ID, SHOW CON_NAME and SHOW PDBS.

SQL*Plus Pluggable Database Support

SQL*Plus Pluggable Database Support

References

[ref8] SQL*Plus® Release Notes Release 12.1 E18402-06
[ref9] SQL*Plus® User’s Guide and Reference Release 12.1 E18404-12

Using DUMP, CHR, and REPLACE character functions

task#7 The set of sentences that will be analysed consist of character strings with the following pattern:

Number#1 Word#2 Number#2 Word#3 Number#3 Word#4 Number#4 Word#5 Number#5

Substrings Number#1, Number#2, Number#3, Number#4, Number#5 need to be extracted from the text and saved into separate table columns as numbers in order to be used as parameters in SQL aggregate functions for further analysis.

The length of each sentence is unknown as well as the format of Number# and Word# substrings.

step#1 – Start the SQL Developer

Start SQL Developer

Start SQL Developer

step#2 – The collected set of sentences is saved into the table DEMO.TEXTS. Each sentence is represented as a string of VARCHAR2(256) in the column WORD_SET.

As the table TEXTS is too long , I selected 6 rows that represent a typical characters sets for this task.

The Set Of Strings

The Set Of Strings

What is strange with this set of strings is that they appear to be of the similar sizes, but their exact lengths are different. The shortest string is 29 characters long, and the longest one is 166 characters long. Obviously, the longer strings contain some other ASCII non-printable characters. How to see them?

step#3 – Apply SQL DUMP function to reveal the content of strings.

Apply the DUMP Function

Apply the DUMP Function

The Result Set shows that our strings contain ASCII characters:

CHR(10) or Line feed, ‘\n’

CHR(13) or Carriage return, ‘\r’

CHR(32) or Space

step#4 – With the simple update statement and REPLACE function, characters chr(10), chr(13), and chr(32) will be removed from all strings:

The UPDATE Statement with REPLACE Functions

The UPDATE Statement with REPLACE Functions

step#5 – All strings are clean and contain only “0-9”, “a-z”, and “A-Z” characters.

The Clean Strings

The Clean Strings

step#6 – The typical string looks like

5,219Spring28.5KSummer44.1KAutumn2,557Winter6

The following procedure called proc_extract will extract substrings with numbers and save them in separate columns as NUMBER data type.

Those columns are table TEXTS columns named

TOTAL for Number#1,

SPRING for Number#2,

SUMMER for Number#3,

AUTUMN for Number#4, and

WINTER for Number#5 substrings.

create or replace procedure proc_extract

authid current_user

is

  type type_id is table of DEMO.texts.word_id%TYPE;

  l_my_id type_id := type_id();

  type t_stats is table of DEMO.texts.word_set%TYPE;

  l_my_list t_stats := t_stats();

  l_last_id number := 0;

  l_len1 number;

  l_len2 number;

  l_my_str varchar2(256);

  l_my_str2 varchar2(256);

  l_my_i DEMO.texts.word_id%TYPE;

begin

  select word_id, word_set bulk collect into l_my_id, l_my_list

  from texts

  where word_set <> 'X';

  dbms_output.put_line(l_my_list.COUNT);

  for i in l_my_id.FIRST..l_my_id.COUNT

  loop

    l_my_i := l_my_id(i);

    l_my_str := l_my_list(i);

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Winter', 1, 1) <> 0 ) then

      l_len1 := instr(l_my_str,'Winter', 1, 1);

      l_my_str2 := substr(l_my_str, l_len1, l_len2 - l_len1 + 1);

      l_my_str2 := funct_format_num(replace(l_my_str2, 'Winter') );

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set winter = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Autumn',1,1) <> 0 ) then

      l_len1 := instr(l_my_str, 'Autumn', 1, 1);

      l_my_str2:= substr(l_my_str, l_len1, l_len2 - l_len1 +1);

      l_my_str2 := funct_format_num(replace(l_my_str2,'Autumn'));

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set autumn = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Summer',1,1) <> 0 ) then

      l_len1 := instr(l_my_str,'Summer',1,1);

      l_my_str2:= substr(l_my_str, l_len1 , l_len2 - l_len1 + 1);

      l_my_str2 := funct_format_num(replace(l_my_str2,'Summer'));

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set summer = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Spring',1,1) <> 0 ) then

      l_len1 := instr(l_my_str,'Spring',1,1);

      l_my_str2:= substr(l_my_str, l_len1 , l_len2 - l_len1 + 1);

      l_my_str2 := funct_format_num(replace(l_my_str2,'Spring'));

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set spring = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if l_len2 > 0 then

      l_my_str := funct_format_num(l_my_str);

      dbms_output.put_line(l_my_str);

      update texts set total = to_number(l_my_str) where word_id = l_my_i;

    end if;
 
  end loop;

  commit;

exception

when others then

  dbms_output.put_line(SQLERRM);

end;

The function funct_format_num will transform the existing number format “9,999”, “99.9K”, “999K”, and “99.9M” into simple “9999999” format that is easy to use.

create or replace function funct_format_num (p_in_str varchar2)

return varchar2

authid current_user

as

  l_s varchar2(256);

begin

  l_s := p_in_str;

  if (instr(p_in_str,'.') <> 0 AND instr(p_in_str,'K') <> 0 ) then

    l_s := replace(replace(l_s,'.'),'K');

    l_s := l_s || '00';

  elsif instr(p_in_str,'K') <> 0 then

    l_s := replace(l_s,'K');

    l_s := l_s || '000';

  elsif ( instr(p_in_str,'.') <> 0 AND instr(p_in_str,'M')<> 0 ) then

    l_s:= replace(replace(l_s,'.'),'M');

    l_s:= l_s || '00000';

  elsif instr(p_in_str,',') <> 0 then

    l_s := replace(l_s,',');

  end if;

  return l_s;

exception

when others then

  raise;

end;

step#7 – After execution of the procedure proc_extract, the table is populated with correct values.

The Table DEMO.TEXTS

The Table DEMO.TEXTS

step#8 – Now, it is easy to execute any SQL aggregate function on TEXTS columns

Apply Aggregate Functions

Apply Aggregate Functions

Summary

Sometimes, we do not need to write long procedures with FOR loops and analyze each table column that contains character strings. Instead, the rule “Use SQL statement first.” is the best choice. Simple UPDATE statement combined with CHR and REPLACE functions will save the day and our time and the helpful DUMP function gives us the evidence what non-printable characters are inside our character strings.

The other story is when the once clean strings that we have, should be divided to several substrings. Then, SQL functions SUBSTR, INSTR, and LENGTH should be used and appropriate subprograms should be written as well to finish the task.

Reminder

DUMP (expr, return_fmt, start_position, length)

returns a VARCHAR2 value containing the data type code, length in bytes, and internal representation of expr. The returned result is always in the database character set. [ref2 ]

CHR (n USING NCHAR_CS)

returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set or, if you specify USING NCHAR_CS, the national character set. [ref2 ]

REPLACE (char, search_string, replacement_string)

returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned. [ref2 ]

Reference

[ref2] Oracle® Database SQL Language Reference 12c Release 1 (12.1) E41329-09

76% increase of developer’s productivity with Oracle EM12c, Enterprise Manager Database Express 12c

Oracle Enterprise Manager Database Express, also referred to as EM Express, is a web-based tool for managing Oracle Database 12c. Built inside the database server, it offers support for basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning.

task#6 Use Enterprise Manager Database Express 12c ( EM12c) and alter the state of the pluggable database PDBORCL from open to close, and again from close to open.
step#1 – Connect to EM 12c. Open the browser with “localhost:5500/em” and enter SYS username/password.

The Enterprise Manager Start Page

The Enterprise Manager Start Page

Then open the window that shows pluggable databases PDBs state.

The List of Oracle 12c Pluggable Databases

The List of Oracle 12c Pluggable Databases

The pluggable database PDBORCL is open in Read Write mode, it has been running for 6 hours and 31 minutes, and its size is 2GB.
step#2 – Open the List of possible Actions on pluggable databases.

The Action List

The Action List

The pluggable database PDBORCL could be:
-Cloned
-Plugged or Unplugged
-Dropped
-Closed

step#3 – Click on the Close option to close the pluggable database PDBORCL

The Pluggable Database is Closed

The Pluggable Database is Closed

The pluggable database pdborcl is now closed.
step#4 – Open the pluggable database pdborcl in Read Write mode.

The Pluggable Database

The Pluggable Database

step#5 – Open SQL window and see the SQL statements that will be executed to open the pluggable database pdborcl in Read Write mode.

SQL statements that will be executed

SQL statements that will be executed

step#6 – See the confirmation message that the pluggable database pdborcl is successfully opened.

The Pluggable Database is OPEN

The Pluggable Database is OPEN

Summary
The Enterprise Manager Database Express 12c allows the DBAs and Oracle Developers clean, fast, and effortless control over the root CDB and pluggable PDBs.

Reference

Oracle 12c documentation

EM12c

High Efficiency with SQL Developer 12c

Oracle SQL Developer is a graphical version of SQL*Plus that gives database developers a convenient way to perform basic tasks. You can browse, create, edit, and delete (drop) database objects; run SQL statements and scripts; edit and debug PL/SQL code; manipulate and export (unload) data; and view and create reports.

You can connect to any target Oracle database schema using standard Oracle database authentication. Once connected, you can perform operations on objects in the database.

You can connect to schemas for selected third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, and IBM DB2, as well as view metadata and data in these databases; also you can migrate third-party databases to Oracle

task#5 Use SQL Developer and do the following: without any SQL or PL/SQL code writing, alter the state of the pluggable database PDBORCL from open to close, then change its state again from close to open.

step#1 – Start SQL Developer, open the View menu DBA, and click on the Option Container Database. That option will allow the management of the Container Database CDB and the Pluggable Databases PDBs. Then, open the window with the status of the pluggable database PDBORCL.

Start SQL Developer

Start SQL Developer

Open View Menu and Option DBA

Open View Menu and Option DBA

Open Option Container Database

Open Option Container Database

step#2 – Open the list of actions on PDBORCL

Modify PDB state

Modify PDB state

step#3 – Close the pluggable database PDBORCL

Apply Modify PDB State to Close

Apply Modify PDB State to Close

Successful State Modification To Close

Successful State Modification To Close

PDB is Closed

PDB is Closed

step#4 – Open the pluggable database PDBORCL in Read Write mode

Modify PDB state From Close To Open

Modify PDB state From Close To Open

Apply Change From Close To Open

Apply Change From Close To Open

PDB is successfully OPENed

PDB is successfully Opened

PDB status is OPEN in Read Write mode

PDB status is OPEN in Read Write mode

Summary

SQL Developer offers to DBAs and Oracle Developers efficient control over root CDB and pluggable PDBs. Productivity of DBAs and Oracle Developers is increased more than ever with Oracle Database 12c Tools.

References

Oracle 12c documentation

[ref7] Oracle® SQL Developer User’s Guide Release 3.2 E35117-06

Upgrade to Oracle Database 12c

task#1 Check the version of your Oracle database and if it is not 12c, notify that it should be upgraded to the Oracle Database 12c.

step#1 – Connect to the Oracle Database as SYSDBA

c:TEMP>sqlplus / as sysdba
SYS@orcl > spool task#1.ext append

step#2 – Check the database version

SYS@orcl > select value from v$parameter where name='optimizer_features_enable';
VALUE
-----------------------------------------------------------
12.1.0.2
SYS@orcl > select value from v$parameter where name='db_unique_name';
VALUE
---------
orcl

The other way to know the Oracle Database version is the following:

--Oracle Database 10.2 
SQL> begin 
2 dbms_output.put_line(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE); 
3 end; 
4 / 
10.2
--Oracle Database 12.1
SYS@orcl > begin
  2  dbms_output.put_line(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE);
  3  end;
  4  /
12.1


step#3 – If the database is not Oracle Database 12c, show the following message ” current_version should be updated to Oracle database 12c. Go to the www.oracle.com ” , and go to step#8.

SYS@orcl > select value current_version,
case substr(value,1,2) 
  when '12' then 'The current version is ' || value
  else value || ' should be updated to Oracle Database 12c. Go to www.oracle.com'
end as status
from v$parameter
where name='optimizer_features_enable';
/
VERSION       STATUS
------------- -----------------------------------------------
12.1.0.2      The current version is 12.1.0.2


step#4 – Else, if it is Oracle Database 12c, show CDB/PDBs details

SYS@orcl > select con_id, name, open_mode, restricted from v$containers;
CON_ID     NAME         OPEN_MODE  RES
------     -----------  --------  ---
1          CDB$ROOT     READ WRITE NO
2          PDB$SEED     READ ONLY  NO
3          PDBORCL      MOUNTED    NO

step#5 – If any PDB is not opened, set its mode to READ ONLY or READ WRITE accordingly

SYS@orcl > select con_id, name
from v$containers
where open_mode not IN ('READ WRITE', 'READ ONLY');
CON_ID     NAME
---------- ------------------------------
3          PDBORCL
SYS@orcl > alter pluggable database pdborcl open read write;
Pluggable database altered.
SYS@orcl > select con_id, name, open_mode, restricted 
from v$containers 
where name='PDBORCL';
/
CON_ID     NAME                           OPEN_MODE  RES
---------- ------------------------------ ---------- ---
3          PDBORCL                        READ WRITE NO


step#6 – Connect to the last PDB that is opened in READ WRITE mode.

SYS@orcl > alter session set container=pdborcl;
Session altered.

step#7 – Show the container ID of the PDB

SYS@orcl > sho con_id
CON_ID
------------------------------
3
SYS@orcl > sho parameter plsql
NAME                   TYPE        VALUE
-------------------    ----------- ------------------------------
plsql_ccflags          string
plsql_code_type        string      INTERPRETED
plsql_debug            boolean     FALSE
plsql_optimize_level   integer     2
plsql_v2_compatibility boolean     FALSE
plsql_warnings         string      DISABLE:ALL

step#8 – Disconnect from the database.

SYS@orcl > exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
c:TEMP>type task#1.ext | more

task#2 Automate task#1. Create a standalone procedure that will return the same result as task#1.

create or replace function funct_db_version
  return number
  authid current_user
is
  l_intval number;
  l_strval varchar2(2000);
  l_type number;
begin
  l_type := dbms_utility.get_parameter_value ('optimizer_features_enable', l_intval, l_strval);
  return to_number(substr(l_strval,1,2));
exception
  when others then
    dbms_output.put_line(SQLERRM);
end;
/
--Oracle Database 10.2
select funct_db_version from dual;

FUNCT_DB_VERSION
----------------
              10
--Oracle Database 12.1
SYS@orcl > select funct_db_version from dual;

FUNCT_DB_VERSION
----------------
              12
create or replace procedure proc_upgrade_to_12c
  authid current_user
is
  l_stmt varchar2(500);
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  CURSOR cur_id_name
  is 
  select con_id, name from v$containers where open_mode not IN ('READ WRITE', 'READ ONLY');
  TYPE t_id is table of v$containers.con_id%TYPE;
  TYPE t_name is table of v$containers.name%TYPE;
  l_list_id t_id;
  l_list_name t_name;
  l_con_id varchar2(256);
  l_con_name varchar2(256);
$END
begin
  if not funct_db_version = 12 then
    dbms_output.put_line(' The database ' ||DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' should be updated to Oracle Database 12c. Go to www.oracle.com' );
    return;
  end if;
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if not cur_id_name%ISOPEN then
    open cur_id_name;
  end if; 
  fetch cur_id_name BULK COLLECT into l_list_id, l_list_name;
  if l_list_id.COUNT > 0 then
    for i in 1..l_list_id.COUNT
    loop
      l_stmt := 'alter pluggable database ' ||lower( l_list_name(i) )|| ' open read write';
      execute immediate l_stmt;
      dbms_output.put_line('Container ' || lower( l_list_name(i) )|| ' open READ WRITE');
    end loop;
  end if; 
  select sys_context('USERENV','CON_ID') into l_con_id from dual;
  select sys_context('USERENV','CON_NAME') into l_con_name from dual;
  dbms_output.put_line('Current container ' || l_con_name || ' has ID ' || l_con_id);
  dbms_output.put_line('$$PLSQL_CODE_TYPE = ' || $$PLSQL_CODE_TYPE);
  dbms_output.put_line('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
  dbms_output.put_line('$$PLSCOPE_SETTINGS = ' || $$PLSCOPE_SETTINGS);
  if cur_id_name%ISOPEN then
    close cur_id_name;
  end if;
$END
exception
  when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if cur_id_name%ISOPEN then
    close cur_id_name;
  end if;
$END
  dbms_output.put_line(SQLERRM);
end proc_upgrade_to_12c;
/
--Oracle Database 10.2
SQL>exec proc_upgrade_to_12c;

The database 10.2 should be updated to Oracle Database 12c. Go to www.oracle.com

PL/SQL procedure successfully completed.
--Oracle Database 12.1
SYS@orcl > exec proc_upgrade_to_12c;
Current container CDB$ROOT has ID 1
$$PLSQL_CODE_TYPE = INTERPRETED
$$PLSQL_OPTIMIZE_LEVEL = 2
$$PLSCOPE_SETTINGS = IDENTIFIERS:NONE

PL/SQL procedure successfully completed.

task#3 Create a standalone procedure that shows details of CDB/PDBs such as container id, name, and open mode.


create or replace procedure proc_show_details_12c
  authid current_user
  is
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  CURSOR cur_id_name_mode 
  is 
  select con_id, name, open_mode from v$containers;
  TYPE t_id is table of v$containers.con_id%TYPE;
  TYPE t_name is table of v$containers.name%TYPE;
  TYPE t_mode is table of v$containers.open_mode%TYPE;
  l_list_id t_id;
  l_list_name t_name;
  l_list_mode t_mode;
  l_con_id varchar2(256);
  l_con_name varchar2(256);
  l_stmt varchar2(500);
$END
begin
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if funct_db_version = 12 then
    if not cur_id_name_mode%ISOPEN then
      open cur_id_name_mode;
    end if; 
    fetch cur_id_name_mode BULK COLLECT into l_list_id, l_list_name, l_list_mode;
    if l_list_id.COUNT > 0 then
      for i in 1..l_list_id.COUNT
      loop
        if l_list_name(i) = 'CDB$ROOT' then
          dbms_output.put_line('Root ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        else
          dbms_output.put_line('Pluggable DB ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        end if;
      end loop; 
    end if;
    if cur_id_name_mode%ISOPEN then
      close cur_id_name_mode;
    end if; 
  end if;
$ELSE
  dbms_output.put_line('Version '|| DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || 
  ' does not support this code. Upgrade to 12c.');
$END
exception
  when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if cur_id_name_mode%ISOPEN then
    close cur_id_name_mode;
  end if;
$END
  dbms_output.put_line(SQLERRM);
end proc_show_details_12c;
/
--Oracle Database 10.2
SQL> exec proc_show_details_12c;
Version 10.2 does not support this code. Upgrade to 12c.

PL/SQL procedure successfully completed.
--Oracle Database 12.1
SYS@orcl > exec proc_show_details_12c;
Root 1, CDB$ROOT open as READ WRITE
Pluggable DB 2, PDB$SEED open as READ ONLY
Pluggable DB 3, PDBORCL open as READ WRITE

PL/SQL procedure successfully completed.

Or, you can see all the above actions in a SQL*Plus Command Line Window:

Alter The Mode Of The Pluggable Database

Alter The Mode Of The Pluggable Database

task#4 Create a package with procedures that will return the same result as task#2 and task#3.


create or replace package pkg_checker
is
  function funct_db_version return number;
  procedure proc_show_details_12c;
  procedure proc_upgrade_to_12c;
end;
/


create or replace package body pkg_checker
is
  function funct_db_version
  return number
  is
    l_intval number;
    l_strval varchar2(2000);
    l_type number;
  begin
    l_type := dbms_utility.get_parameter_value ('optimizer_features_enable', l_intval, l_strval);
    return to_number(substr(l_strval,1,2));
  exception
    when others then
      dbms_output.put_line(SQLERRM);
  end;

  procedure proc_show_details_12c
  is
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    CURSOR cur_id_name_mode
    is 
    select con_id, name, open_mode from v$containers;
    TYPE t_id is table of v$containers.con_id%TYPE;
    TYPE t_name is table of v$containers.name%TYPE;
    TYPE t_mode is table of v$containers.open_mode%TYPE;
    l_list_id t_id;
    l_list_name t_name;
    l_list_mode t_mode;
    l_con_id varchar2(256);
    l_con_name varchar2(256);
    l_stmt varchar2(500);
$END
  begin
    if not funct_db_version = 12 then
      dbms_output.put_line( 'The database ' || DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' should be updated to Oracle Database 12c. Go to www.oracle.com' );
      return;
    end if;
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if not cur_id_name_mode%ISOPEN then
      open cur_id_name_mode;
    end if; 
    fetch cur_id_name_mode BULK COLLECT into l_list_id, l_list_name, l_list_mode;
    if l_list_id.COUNT > 0 then
      for i in 1..l_list_id.COUNT
      loop
        if l_list_name(i) = 'CDB$ROOT' then
          dbms_output.put_line('Root ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        else
          dbms_output.put_line('Pluggable DB ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        end if;
      end loop; 
    end if;
    if cur_id_name_mode%ISOPEN then
      close cur_id_name_mode;
    end if; 
$END
  exception
    when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if cur_id_name_mode%ISOPEN then
      close cur_id_name_mode;
    end if;
$END
    dbms_output.put_line(SQLERRM);
  end;

  procedure proc_upgrade_to_12c
  is
    l_stmt varchar2(500);
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    CURSOR cur_id_name
    is 
    select con_id, name from v$containers where open_mode not IN ('READ WRITE', 'READ ONLY');
    TYPE t_id is table of v$containers.con_id%TYPE;
    TYPE t_name is table of v$containers.name%TYPE;
    l_list_id t_id;
    l_list_name t_name;
    l_con_id varchar2(256);
    l_con_name varchar2(256);
$END
  begin
    if not funct_db_version = 12 then
      dbms_output.put_line('The database ' || DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' should be updated to Oracle Database 12c. Go to www.oracle.com' );
      return;
    end if;
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if not cur_id_name%ISOPEN then
      open cur_id_name;
    end if; 
    fetch cur_id_name BULK COLLECT into l_list_id, l_list_name;
    if l_list_id.COUNT > 0 then
      for i in 1..l_list_id.COUNT
      loop
        l_stmt := 'alter pluggable database ' ||lower( l_list_name(i) )|| ' open read write';
        execute immediate l_stmt;
        dbms_output.put_line('Container ' || lower( l_list_name(i) )|| ' open READ WRITE' ) ;
      end loop;
    end if;
    select sys_context('USERENV','CON_ID') into l_con_id from dual;
    select sys_context('USERENV','CON_NAME') into l_con_name from dual;
    dbms_output.put_line('Container ' || l_con_name || ' has ID ' || l_con_id);
    dbms_output.put_line('$$PLSQL_CODE_TYPE = ' || $$PLSQL_CODE_TYPE);
    dbms_output.put_line('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
    dbms_output.put_line('$$PLSCOPE_SETTINGS = ' || $$PLSCOPE_SETTINGS);
    if cur_id_name%ISOPEN then
      close cur_id_name;
    end if;
$END
  exception
    when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if cur_id_name%ISOPEN then
      close cur_id_name;
    end if;
$END
    dbms_output.put_line(SQLERRM);
  end;
end;
/
--Oracle Database 10.2
SQL> exec pkg_checker.proc_show_details_12c;
The database 10.2 should be updated to Oracle Database 12c. Go to www.oracle.com

PL/SQL procedure successfully completed.
--Oracle Database 12.1
SYS@orcl > exec pkg_checker.proc_show_details_12c;
Root 1, CDB$ROOT open as READ WRITE
Pluggable DB 2, PDB$SEED open as READ ONLY
Pluggable DB 3, PDBORCL open as READ WRITE

PL/SQL procedure successfully completed.
--Oracle Database 10.2
SQL> exec pkg_checker.proc_upgrade_to_12c;
The database 10.2 should be updated to Oracle Database 12c. Go to www.oracle.com

PL/SQL procedure successfully completed.
--Oracle Database 12.1
SYS@orcl > exec pkg_checker.proc_upgrade_to_12c;
Container CDB$ROOT has ID 1
$$PLSQL_CODE_TYPE = INTERPRETED
$$PLSQL_OPTIMIZE_LEVEL = 2
$$PLSCOPE_SETTINGS = IDENTIFIERS:NONE

PL/SQL procedure successfully completed.

Now, you can repeat the process over and over again, see the SQL*Plus Command Line Window:

Repeat the Action Over and Over Again

Repeat the Action Over and Over Again

So far, the following schema objects were created:

--Oracle Database 10.2
SYS$SQL> 
select substr(object_name,1,20), substr(object_type,1,20)
from user_objects  
where created > sysdate - 1;

SUBSTR(OBJECT_NAME,1 SUBSTR(OBJECT_TYPE,
-------------------- -------------------
PROC_UPGRADE_TO_12C  PROCEDURE
PROC_SHOW_DETAILS_12 PROCEDURE
PKG_CHECKER          PACKAGE
PKG_CHECKER          PACKAGE BODY
FUNCT_DB_VERSION     FUNCTION
--Oracle Database 12.1
SYS@orcl > select lpad(substr(object_type,1,20),20) type, substr(object_name,1,20) name, created --12.1
  2  from user_objects
  3  where object_name like 'FUNCT%'
  4  or object_name like 'PROC%'
  5  or object_name like 'PKG%';

TYPE                 NAME                 CREATED
-------------------- -------------------- ---------
             PACKAGE PKG_CHECKER          21-AUG-15
        PACKAGE BODY PKG_CHECKER          21-AUG-15
           PROCEDURE PROC_SHOW_DETAILS_12 21-AUG-15
           PROCEDURE PROC_UPGRADE_TO_12C  21-AUG-15
            FUNCTION FUNCT_DB_VERSION     21-AUG-15

10 rows selected.

Final Task# Drop all objects from the database that were created in the above examples!

SYS@orcl > drop function funct_db_version;
SYS@orcl > drop procedure proc_upgrade_to_12c;
SYS@orcl > drop procedure proc_show_details_12c;
SYS@orcl > drop package pkg_checker;

Instead of Summary

The purpose of the task#1 is to show how many solutions you can create for the one task, in this case it was to find out the version and the release of the Oracle Database. This can also be done easily with this select statement:

c:TEMP>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 10 18:14:23 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@orcl > COL PRODUCT FORMAT A40
SYS@orcl > COL VERSION FORMAT A15
SYS@orcl > COL STATUS FORMAT A15
SYS@orcl > SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                               VERSION    STATUS
------------------------------------ ----------  ------------
NLSRTL                               12.1.0.2.0  Production
OracleDatabase12c Enterprise Edition 12.1.0.2.0  64bit Production
PL/SQL                               12.1.0.2.0  Production
TNS for 64-bit Windows:              12.1.0.2.0  Production

SYS@orcl >

Dictionary
sqlplus = “SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database installation. It has a command-line user interface.”, [ref2]
spool = SQL*Plus command that stores query results in a file, or optionally sends the file to a printer, [ref2]
.ext = the extension of the file where command spool stores the query results, [ref2]
append = The option that adds the contents of the buffer to the end of the file you specify in the spool command, [ref2]
CDB = a multitenant container database,
PDB = a pluggable database,
v$parameter = The dynamic performance view that displays information about the initialization parameters that are currently in effect for the session, [ref3]
v$containers = The dynamic performance view that displays information about PDBs and the root associated with the current instance, [ref3]
select = SQL statement that retrieves data from one or more tables, object tables, views, object views, or materialized views, [ref1]
CDB$ROOT = Oracle Database 12c root container, [ref4]
PDB$SEED = Oracle Database 12c seed PDB, [ref4]
PDBORCL = Oracle Database 12c pluggable database, a user-created entity that contains the data and code required for a specific set of features, [ref4]
READ ONLY = A database that is available for queries only and cannot be modified, [ref4]
READ WRITE = A database that is available for queries and that can be modified, [ref4]
MOUNTED = An database instance that is started and has the database control file open, [ref4]
alter pluggable database = SQL statement that modifies a pluggable database (PDB), [ref1]
alter session = SQL statement that sets or modifies any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database, [ref1]
show user = SQL*Plus command that shows the username you are currently using to access SQL*Plus, [ref2]
show con_id = SQL*Plus command that displays the id of the Container to which you are connected when connected to a Consolidated Database. If issued when connected to a non-Consolidated Database, this command returns 0, [ref2]
exit = SQL*Plus command that let you exit SQL*Plus, [ref2]
case = SQL expressions that let you use IF … THEN … ELSE logic in SQL statements without having to invoke procedures, [ref1]

Further reading
If you want to be familiar with Oracle SQL, please see [ref1].
If you want to know what kind of the CLI (Command Line Interface) SQL*Plus is, please see [ref2].
if you want to know details of the Oracle Database meta data, please see [ref3].
If you want to understand the Oracle Database architecture, please see [ref4].
If you want to know details of PL/SQL (Oracle extension of SQL), please see [ref5].

References
[ref1] SQL*Plus® User’s Guide and Reference Release 12.1 E18404-12
[ref2] Oracle® Database SQL Language Reference 12c Release 1 (12.1) E41329-09
[ref3] Oracle® Database Reference 12c Release 1 (12.1) E41527-15
[ref4] Oracle® Database Concepts 12c Release 1 (12.1) E41396-12
[ref5] Oracle® Database PL/SQL Language Reference 12c Release 1 (12.1) E50727-04
[ref6] Oracle® Database PL/SQL Packages and Types Reference 12c Release 1 (12.1)
E41829-05