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
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
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
Climbing Olympus 