The WordPress.com stats helper monkeys prepared a 2015 annual report for this blog.
Here’s an excerpt:
A San Francisco cable car holds 60 people. This blog was viewed about 840 times in 2015. If it were a cable car, it would take about 14 trips to carry that many people.
Thanks to the Mozilla Open Badges Standard the real benefit for certified software developers started to show up.
According to the OPN, Oracle PartnerNetwork, the individuals with the open LinkedIn account are likely to receive 6 times more profile views if they are in the possession of one of four possible digital badges issued by Oracle.
Read more …
https://blogs.oracle.com/oraclepartners/entry/oracle_certification_issues_digital_certification
Acclaim Home Page
You’ve worked hard to earn this achievement. Share the good news with your professional network, friends and family by claiming your badge.
You can click on my badge (in the main sidebar on my Home Page, or on the About page) and see my credentials.
The badge is issued on the platform Acclaim which
is a badging platform that works with credible organizations to recognize, manage and share professional achievements. Acclaim is backed by Pearson, the world’s leading learning company.
The badge could be shared on the Social Media Platforms like Facebook, Twitter, LinkedIn, and Google+, and could be sent in an e-mail.
Acclaim’s explanation about Open Badges Usefulness is the following:
Representing your credentials as Open Badges gives you the ability to broadcast your skills across the online destinations you are already using to tell your professional story. More importantly, they differentiate you by offering employers single-click access to validated information that is provided in the context necessary to evaluate it. While it is true that many employers are still learning about Open Badges, there is no doubt that the additional efficiency they offer will make them a convenient currency for communicating professional skills over time.
The Mozilla’s short video explains the purpose of Open Badges
References:
Mozilla Open Badges Standard http://openbadges.org/
Acclaim https://www.youracclaim.com/
Pearson Education https://www.pearson.com/
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
step#6 Open the list Datafiles in the DBA View and show that there is no additional datafiles for CDB$ROOT.
Datafiles
step#7 Open the list Tablespaces in the DBA View and show that there is no added tablespaces for CDB$ROOT either.
Tablespaces
step#8 Create a new connection to BOOKSTORE database in the Connection View
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
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.
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
step#11 Open the Database Configuration Assistant and create EM12c port 5504 as the entry point for BOOOKSTORE database.
The Configuration Assistant
step#12 Open the browser with “localhost:5504/em” and login to BOOKSTORE as SYSDBA
BOOKSTORE EM12c
step#13 Show how many tablespaces are there in the BOOKSTORE database
Tablespaces Of BOOKSTORE
step#14 Create a new 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
step#16 Back to SQL Developer again, the BOOKSTORE database is ready for the new elements.
SQL Developer worksheet
step#17 Click on the BOOKSTORE connection in the Connect View and choose the option Create User.
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
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
step#20 Use SQL*Plus and connect to the BOOKSTORE database as user code/code@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.
The investment in the Oracle Database once done has never been lost. Now, almost any older versions of the Oracle Database could be upgraded to the latest Oracle Database 12c which is 12.1.0.2.
The Premier Support for Oracle Database Version 11.2 has ended on Jan 31, 2015.
It is now 9 months over!
But, the Extended Period of Oracle Support for the current database version 12.1.0.2 is June 2021.
There are two ways to upgrade to Oracle database 12c:
1) The Direct Path
10.2.0.5 11.1.0.7 11.2.0.2 and Higher
2) The Indirect Path
7.3.3 (lower) to 7.3.4 to 9.2.0.8 to 10.2.0.5 to 12.1.x 8.0.5 (or lower) to 8.0.6 to 9.2.0.8 to 10.2.0.5 to 12.1.x 8.1.7(or lower) to 8.1.7.4 to 10.2.0.5 to 12.1.x 9.0.1.3 (or lower) to 9.0.1.4 to 10.2.0.5 to 12.1.x 9.2.0.7 (or lower)to 10.2.0.5 to 12.1.x 10.2.0.4(or lower) to 10.2.0.5 to 12.1.x 11.1.0.6 to 11.1.0.7 to 12.1.x 11.2.0.1 to 11.2.0.2 to 12.1.x
The direct path allows you to upgrade directly from versions 10.2.0.5, 11.1.0.7, and 11.2.0.2 and Higher to 12.1.0.2. The Premium Period of Support for 12.1.0.2 is until June 2018.
The indirect path requires you to plan and organize the upgrade to 12.1.0.2 in several steps. The older the version of the Oracle database that you are going to upgrade, the longer the process.
The oldest version that could be upgraded to 12.1.0.2 is 7.3.3 or lower to 7.3.4.
As of 8i and 9i upgrade, there are 2 to 4 steps to do it, for an example, versions 8.1.7(or lower) are first to be upgraded to 8.1.7.4, then 8.1.7.4 will be upgraded to 10.2.0.5, and finally the version 10.2.0.5 will be upgraded to 12.1.0.2.
Again, think about it, it is a big advantage! The Premium Period of Oracle Support for 12.1.0.2 is until June 2018 and the Extended Period of Oracle Support for 12.1.0.2 is June 2021. It is about 6 years from today!
The Oracle Corporation recommendations about each possible path of the upgrade, direct or indirect, are explained in detail in the document
Oracle® Database Upgrade Guide 12c Release 1 (12.1) E41397-11
The cost of the upgrade includes in some extreme cases:
The longer you avoid the upgrade to the Oracle Database 12c, the harder and more expensive it becomes.
To speed up the hole process of the upgrade, the Oracle Corporation advises you to run the new preupgrade script preupgrd.sql which will generate the fix up scripts and gives you the starting information about issues that might be present both before and after the upgrade. That would be the base of how to organize your upgrade and calculate the cost of it.
For the owners of the large systems with more than 100 Oracle databases running, there is the option of the PARALLEL UPGRADE that guarantee you:
– FAST UPGRADE and
– LOW DOWNTIME
Beside the command line script upgrade, there is the Database Upgrade Assistant (DBUA) which automates the hole upgrade process.
The Hands-On-Lab “Upgrade, Migrate and Consolidate to Oracle Database 12c” is available for DOWNLOAD from the website http://blogs.oracle.com/UPGRADE.
The lab will guide you through the following tasks:
If you are in a doubt about when to start the upgrade, now or later, the Hands-On Lab is a good starting point as it will show you how to do it, and allow you to experience the features of 12.1.0.2 straight forward.
The Oracle Database 12c with a new architecture allows a multitenant container database to hold up to 252 pluggable databases.
The main features of the Oracle Database 12c are:
Summary
The process of upgrading to Oracle Database 12.1.0.2 may be costly and long and separated in many steps that should be done as required, but the final product is worth it. In the end, the Oracle Database 12c replaces all previous database versions and gives you the new start. Remember, the Premium Period of Oracle Support for 12.1.0.2 is until June 2018 and the Extended Period of Oracle Support for 12.1.0.2 is June 2021.
The above facts are taken from the following sources:
1) Oracle® Database Upgrade Guide 12c Release 1 (12.1) E41397-11
https://docs.oracle.com/database/121/UPGRD/title.htm
2) Nitin Maheshwari, PDF document “upgrade-to-oracle-database-12c-2412040-en-in”
3) Mike Dietrich, “Upgrade, Migrate, and Consolidate to Oracle Database 12c” , Oracle Webcast, 16 June 2015
http://medianetwork.oracle.com/video/player/4301086284001
4) Database Upgrade OTN website
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
step#2 Open the Tablespace control window.
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
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
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
step#6 The new tablespace has been created successfully.
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
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.
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
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
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
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
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
References
[ref8] SQL*Plus® Release Notes Release 12.1 E18402-06
[ref9] SQL*Plus® User’s Guide and Reference Release 12.1 E18404-12
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
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
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
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
step#5 – All strings are clean and contain only “0-9”, “a-z”, and “A-Z” characters.
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
step#8 – Now, it is easy to execute any SQL aggregate function on TEXTS columns
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
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
Then open the window that shows pluggable databases PDBs state.
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 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 pdborcl is now closed.
step#4 – Open the pluggable database pdborcl in Read Write mode.
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
step#6 – See the confirmation message that the pluggable database pdborcl is successfully opened.
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 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
Open View Menu and Option DBA
Open Option Container Database
step#2 – Open the list of actions on PDBORCL
Modify PDB state
step#3 – Close the pluggable database PDBORCL
Apply Modify PDB State to Close
Successful State Modification To Close
PDB is Closed
step#4 – Open the pluggable database PDBORCL in Read Write mode
Modify PDB state From Close To Open
Apply Change From Close To Open
PDB is successfully Opened
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
[ref7] Oracle® SQL Developer User’s Guide Release 3.2 E35117-06
Climbing Olympus 