Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
OpenWorld Wednesday – step right up!
If you’ve read my two previous posts on the OpenWorld schedule, you’re probably expecting a huge long list of sessions to pique your interest.
But no! There’s only ONE you need on your list 
The Fast Lane to Database Success [TIP4094]
Connor McDonald, Developer Advocate for SQL, Oracle
Wednesday, Oct 24, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3009
The skill set of a database practitioner is much more than what is read in the documentation, on blogs, or on StackOverflow. It is the knowledge from years of trial and error, experimentation, and sometimes painful failures. The problem is it takes time—a long, long time—to build that experience. This session aims to fast-track that path. Get a collection of hints, tips, features, and techniques picked up from the smartest people in the community.
After all… it’s all about
Just kidding! If you come along to my session, then that’s great! If you get a chance, come say “Hi” as well!
You can get primed for my session above by popping into the Exchange and checking out my Theater session on how to write pagination queries that won’t break your database server!
But here’s some other sessions to make for a great Wednesday at OpenWorld.
Creating REST-Enabled SQL References and Web Services with Oracle Application Express [HOL6326]
David Peake, Senior Principal Product Manager, Oracle
Marc Sewtz, Senior Software Development Manager, Oracle
Wednesday, Oct 24, 9:30 a.m. – 10:30 a.m. | Marriott Marquis (Yerba Buena Level) – Salon 3/4
Journey to the Cloud: Latin America Customer Success Showcase [CAS4333]
Gustavo Perez Seib, Latin America Vice President & General Manager, Oracle
Oscar Botto, CIO, Arcor
Mirela Siani, General Manager, VALE
Wednesday, Oct 24, 11:15 a.m. – 12:00 p.m. | Moscone North – Room 22
The Changing Role of the DBA [TIP5526]
Maria Colgan, master product manager at Oracle Corporation , Oracle
Penny Avril, VP Oracle Database Product Management, Oracle
Wednesday, Oct 24, 11:15 a.m. – 12:00 p.m. | Moscone West – Room 3003
The Next Big Things for Oracle’s Autonomous Cloud Platform [PKN5770]
Amit Zavery, Executive Vice President, Fusion Middleware and PaaS Development, Oracle
Wednesday, Oct 24, 11:15 a.m. – 12:00 p.m. | The Exchange @ Moscone South – The Arena
The 10 Dumbest Database Fads [BQS5533]
Juan Loaiza, Senior Vice President, Oracle
Wednesday, Oct 24, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3003
Performance Tuning Oracle Database 18c in Oracle Enterprise Manager Cloud Control 18c [TIP3234]
Tariq Farooq, CEO / CHAIRMAN, BrainSurface
Francisco Munoz Alvarez, Director of Innovation, Data Intensity
Kai Yu, Technical Staff, USA
Wednesday, Oct 24, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3002
Oracle SQL Developer Tips and Tricks [TIP4059]
Jeff Smith, Senior Principal Product Manager, Oracle
Wednesday, Oct 24, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3009
Oracle Database Meets Docker: How to Get Started [TIP1837]
Anton Els, Vice President Engineering, Dbvisit Software Limited
Wednesday, Oct 24, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3001
SmartDB and Edition-Based Redefinition: The Perfect Marriage [TIP4043]
Bryn Llewellyn, Distinguished Product Manager, Database Division, Oracle
Wednesday, Oct 24, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3005
Zero Data Loss Recovery Appliance: Introducing the Next Generation [PRO4217]
Kelly Smith, Senior Principal Product Manager, Oracle
Wednesday, Oct 24, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3007
Oracle Application Express Applications on Oracle Database Exadata Express Cloud Service [CAS1248]
Roel Hartman, Director, APEX Consulting
Alex Nuijten, Director, allAPEX
Wednesday, Oct 24, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3002
Running Oracle Database and Applications in Docker Containers on Windows [PRM4074]
Christian Shay, Product Manager – .NET and Windows Technologies, Oracle
Wednesday, Oct 24, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3010
Multitenant Security Features Clarify DBA Role in DevOps Cloud [TIP1259]
Franck Pachot, Computing Engineer, CERN
Pieter Van Puymbroeck, DBA, Exitas
Wednesday, Oct 24, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3006
Accelerate Application Performance: Tips for Faster Oracle Database .NET Programs [TIP4085]
Alex Keh, Senior Principal Product Manager, Oracle
Wednesday, Oct 24, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3010
Moving Your Oracle Databases to the Oracle Cloud [PRO1411]
Alex Zaballa, Senior Oracle Database Administrator, Accenture Enkitec Group
Wednesday, Oct 24, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3001
Compressed LOB–my table got bigger?
We had an interesting question on AskTOM the other day about LOB compression. Someone was very concerned that after applying compression to the LOBS in the column of their table, the table actually got larger! Cue much confusion and panic, but after a little exploration, the explanation was pretty simple.
By default, when you create a LOB column in a table, the default storage definition is ENABLE STORAGE IN ROW. As per the documentation:
If you enable storage in row, then the LOB value is stored in the row (inline) if its length is less than approximately 4000 bytes minus system control information
After getting some more information from the person who asked the question, the majority of their LOBs (they were CLOBs in this instance) sat around the 10 to 20kilobyte mark. What this means is that in uncompressed form, the LOB data would be deemed too large to be stored alongside the rest of the row – it would be moved to the separate LOB segment that holds the data for the LOB column. But after compressing the LOB, some of those LOBs then dropped to less than the ~4kilobyte threshold and thus would be now stored in the table itself. If you have a lot of such LOBs, then the table segment could indeed grow, even though the overall size of the table (ie, table data plus LOB data) should indeed shrink with the compression.
We can see that with a simple demo – I’ll put some very compress-able data into the LOB, but start with the segment being the default of not compressed:
SQL> create table t1
2 ( x int,
3 c clob
4 )
5 lob (c) store as securefile (
6 enable storage in row
7 nocompress);
Table created.
SQL>
SQL> insert into t1
2 select rownum, rpad('b',12000,'b')
3 from dual
4 connect by level <= 10000;
10000 rows created.
SQL> commit;
Commit complete.
Now I’ll copy that table into a replica, the only difference between that the LOB column will be marked as compressed.
SQL> create table t2
2 ( x int,
3 c clob
4 )
5 lob (c) store as securefile (
6 enable storage in row
7 compress);
Table created.
SQL>
SQL> insert into t2
2 select * from t1;
10000 rows created.
SQL>
SQL> commit;
Commit complete.
Now we can compare the size of the LOB segments as well as the size of table
SQL> select segment_name, bytes from user_segments
2 where segment_name = 'T1' or
3 segment_name = ( select segment_name from user_lobs
4 where table_name = 'T1' );
SEGMENT_NAME BYTES
------------------------------ ----------
T1 655360
SYS_LOB0000268696C00002$$ 277020672
2 rows selected.
SQL>
SQL> select segment_name, bytes from user_segments
2 where segment_name = 'T2' or
3 segment_name = ( select segment_name from user_lobs
4 where table_name = 'T2' );
SEGMENT_NAME BYTES
------------------------------ ----------
T2 3145728
SYS_LOB0000268699C00002$$ 131072
2 rows selected.
SQL>
SQL>
SQL>
So yes, the table (in red) has grown, but the size of the overall table (where “overall” means table plus LOB data), has shrunk thanks the compression of the LOB data.
LOBs vs Data – when compression occurs
Just a quick tip for SECUREFILE lobs.
You may be familiar with basic compression on data in tables. It is a dictionary attribute of the table, which is then taken into account when you perform:
- a SQL Loader direct load, or
- an INSERT with the APPEND hint, or
- a bulk bind INSERT in PLSQL using the APPEND_VALUE hint.
Whichever of the above you perform does not really matter – the key thing is that when you set the COMPRESS attribute on a table, this only applies on operations subsequent to the moment at which you altered the table. It does not compress the existing data. Hence when you alter a table to add the attribute, it is instantaneous.
SQL> create table t (x int, c clob ) lob (c) store as securefile;
Table created.
SQL>
SQL> insert into t
2 select rownum, dbms_random.string('x',20000)
3 from dual
4 connect by level < 100000;
99999 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> set timing on
SQL> alter table t compress;
Table altered.
Elapsed: 00:00:00.00
But do not make the mistake of assuming this is the case if you opt to set the compress attribute for a SECUREFILE LOB. Setting this attribute on a table will immediately read and update all of the existing LOB data in the table, which of course could be a very resource intensive operation.
SQL> alter table t modify lob (c ) ( compress );
Table altered.
Elapsed: 00:00:23.32
Whilst your best option is probably to specify LOB compression when you create the table, if you need to do it after the fact, be aware that you’ll probably want to schedule it in a quiet time.
ODC Appreciation Day–LOB compression
LOBs tend to be large. Well duh…it’s right there in the name! “Large Object”. So one of the cool things I like with the SECUREFILE option in recent releases of Oracle Database is the ability to compress LOBs. Here’s a quick demo of that in action:
SQL> create table t1
2 ( x int,
3 c clob
4 )
5 lob (c) store as securefile (
6 enable storage in row
7 nocompress);
Table created.
SQL>
SQL> create table t2
2 ( x int,
3 c clob
4 )
5 lob (c) store as securefile (
6 enable storage in row
7 compress);
Table created.
SQL>
SQL> insert into t1
2 select rownum, rpad(owner,6000,owner||','||object_type)
3 from dba_objects
4 commit;
82902 rows created.
SQL>
SQL> insert into t2
2 select * from t1;
82902 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select sum(bytes) from user_segments
2 where
3 segment_name = 'T1' or
4 segment_name = ( select segment_name from user_lobs
5 where table_name = 'T1' );
SUM(BYTES)
----------
1548943360
1 row selected.
SQL>
SQL> select sum(bytes) from user_segments
2 where
3 segment_name = 'T2' or
4 segment_name = ( select segment_name from user_lobs
5 where table_name = 'T2' );
SUM(BYTES)
----------
19005440
1 row selected.
Now as you can see, the compression achieved here is pretty spectacular but a careful eye will quickly spot why this is the case – the data is lot of repeated pairings of the owner and object_type values from DBA_OBJECTS.
Just to balance the ledger, I’ll repeat the example using totally random strings
SQL> create table t1
2 ( x int,
3 c clob
4 )
5 lob (c) store as securefile (
6 enable storage in row
7 nocompress);
Table created.
SQL>
SQL> create table t2
2 ( x int,
3 c clob
4 )
5 lob (c) store as securefile (
6 enable storage in row
7 compress);
Table created.
SQL>
SQL> insert into t1
2 select rownum, dbms_random.string('x',12000)
3 from dual
4 connect by level <= 10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> insert into t2
2 select * from t1;
10000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select sum(bytes) from user_segments
2 where
3 segment_name = 'T1' or
4 segment_name = ( select segment_name from user_lobs
5 where table_name = 'T1' );
SUM(BYTES)
----------
93126656
1 row selected.
SQL>
SQL> select sum(bytes) from user_segments
2 where
3 segment_name = 'T2' or
4 segment_name = ( select segment_name from user_lobs
5 where table_name = 'T2' );
SUM(BYTES)
----------
84017152
1 row selected.
As you can see, even in this case, we still managed to get some benefit from the compression, although (as expected) the gains are not as dramatic. The benefit that you get from your data will probably fall somewhere in between these values. Also note that there is an overhead to storing the metadata for any LOB, and you would expect the amount of metadata to be larger for a compressed LOB. So compressing tiny LOBs may yield an actual growth in segment size – so always perform some measurements on realistic data before embarking down this path.
And finally, in the vein of “There is no such thing as a free lunch”, compressing anything, LOBs or otherwise, takes processing power. So always keep in mind the trade-off between less storage, faster access for smaller LOBs versus the processing cost of compressing them in the first place.
Thanks ODC!
OpenWorld Tuesday … yep, still screwed
Well…I’m only planning day 2 of OpenWorld and already I’ve pretty much given up on being able to see all the talks that I want to see 
The challenge for me will be trying to coax those people that are inside Oracle to give me a synopsis of their talk after the event. That is probably my best hope here.
In any event, if you’re a database person like me, here’s my tips for what I’ll be trying to sneak into on Tuesday.
Tuesday morning
An Insider’s Guide to Oracle Autonomous OLTP Database Cloud [TRN3979]
Maria Colgan, master product manager at Oracle Corporation , Oracle
Robert Greene, Senior Director, Product Management, Oracle
Tuesday, Oct 23, 11:15 a.m. – 12:00 p.m. | Moscone West – Room 3003
which overlaps with
Finally: DevOps for Databases [TRN4088]
Gerald Venzl, Senior Principal Product Manager, Oracle
Kris Rice, Senior Director, Oracle
Tuesday, Oct 23, 11:15 a.m. – 12:00 p.m. | Moscone West – Room 3009
which overlaps with
Oracle Real Application Clusters 18c Internals [TRN4022]
Anil Nair, Senior Principal Product Manager, Oracle
Tuesday, Oct 23, 11:15 a.m. – 12:00 p.m. | Moscone West – Room 3007
which overlaps with
Upgrade to Oracle Database 18c: Live and Uncensored [TRN4029]
Kamran Aghayev, Dba team head, Azercell telecom
Roy Swonger, Vice President, Database Upgrade & Utilities, Oracle
Mike Dietrich, Master Product Manager – Database Upgrades and Migrations, Oracle
Tuesday, Oct 23, 11:15 a.m. – 12:00 p.m. | Moscone West – Room 3004
which overlaps with
DBAs Versus Autonomous Databases [PRO1072]
Julian Dontcheff, Global Database Lead, Accenture Oy
Tuesday, Oct 23, 11:15 a.m. – 12:00 p.m. | Moscone West – Room 3002
which overlaps with
Cool New Features for Developers in Oracle Database 18c and Oracle Database 12c [DEV6243]
Timothy Hall, DBA, Developer, Author, Trainer, oracle-base.com
Code One Tracks: Database, Big Data, and Data Science
Session Type: Developer Session
Tuesday, Oct 23, 11:30 a.m. – 12:15 p.m. | Moscone West – Room 2003
Tuesday lunchtime
Zero Downtime Upgrade of Core Banking Systems with Edition-Based Redefinition [CAS4042]
Martin Buechi, Systems Architect, Avaloq Evolution AG
Bryn Llewellyn, Distinguished Product Manager, Database Division, Oracle
Nicolas Blanc, Software Development Engineer, Avaloq Evolution AG
Tuesday, Oct 23, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3005
which overlaps with
Oracle Database In-Memory on Exadata: A Potent Combination [PRO4016]
Gurmeet Goindi, Technical Product Strategist, Oracle
Shasank Chavan, Vice President, In-Memory Technologies, Oracle
Tuesday, Oct 23, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3008
which overlaps with
Oracle Sharding: Geo-Distributed, Scalable, Multimodel Cloud-Native DBMS [PRO4037]
Mark Dilman, Director, Software Development, Oracle
Srinagesh Battula, Sr. Principal Product Manager, Oracle
Tuesday, Oct 23, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3007
which overlaps with
Oracle Database 18c: New Features for Agile Development [TIP2903]
Erik Benner, VP Enterprise Transformation, Mythics, Inc.
Tuesday, Oct 23, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3001
which overlaps with
Python and Oracle Database on the Table [TIP4076]
Christopher Jones, Senior Principal Product Manager, Oracle
Anthony Tuininga, Software Developer 5, Oracle
Tuesday, Oct 23, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3009
which overlaps with
Real-World Performance with Oracle Autonomous Data Warehouse Cloud [TRN4025]
John Clarke, Software Development Director, Real World Performance, Oracle
Steven Yang, Oracle
Tuesday, Oct 23, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3004
which overlaps with
Oracle Database: What’s New and Coming Next [PRO5512]
Dominic Giles, Master Product Manager, Oracle
Penny Avril, VP Oracle Database Product Management, Oracle
Tuesday, Oct 23, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 3003
which overlaps with
Modern (NoSQL) Database Application Development Everywhere [DEV5614]
Beda Hammerschmidt, Consulting Member of Technical Staff, Oracle
Vikas Arora, Senior Director, Oracle
Maxim Orgiyan, PMTS, Oracle
Code One Tracks: Database, Big Data, and Data Science, Development Tools
Session Type: Developer Session
Tuesday, Oct 23, 12:30 p.m. – 1:15 p.m. | Moscone West – Room 2003
Tuesday early afternoon
Python and Oracle Database 18c: Scripting for the Future [HOL6329]
Christopher Jones, Senior Principal Product Manager, Oracle
Anthony Tuininga, Software Developer 5, Oracle
Blaine Carter, Oracle Developer Advocate for Open Source, Oracle
Tuesday, Oct 23, 2:15 p.m. – 3:15 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 3/4
Tuesday mid afternoon
Oracle REST Data Services and REST APIs for Your Oracle Database [PRO4063]
Colm Divilly, Consulting Member of Technical Staff, Oracle
Tuesday, Oct 23, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3009
which overlaps with
Oracle JavaScript Extension Toolkit Composite Components Bring Agility to App Development [TIP2364]
Lucas Jellema, CTO, AMIS Services BV
Tuesday, Oct 23, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 302
which overlaps with
Even the ACEs make Mistakes – What did they learn?” [TIP1989]
Debra Lilley, VP Certus Cloud Services & Oracle Alliance, Certus Solutions an Accenture Company
Ralf Koelling, Senior Consultant, CGI Deutschland Ltd. & Co. KG
Fiona Martin, “Director, Strategic Alliances -Oracle”, KPMG LLP
Tuesday, Oct 23, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3001
which overlaps with
DevOps Tools for Database Developers [DEV5055]
Blaine Carter, Oracle Developer Advocate for Open Source, Oracle
Code One Tracks: DevOps and Pipelines, Development Tools
Session Type: Developer Session
Tuesday, Oct 23, 4:00 p.m. – 4:45 p.m. | Moscone West – Room 2010
Tuesday late afternoon
An Automatic Cloud Document Store Using Oracle Text [PRO4008]
Roger Ford, Product Manager, Oracle
Tuesday, Oct 23, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3005
which overlaps with
Multitenant: Best Practices for High Availability [PRO4004]
Patrick Wheeler, Senior Director, Product Management, Oracle Database, Oracle
Giridhar Ravipati, Consulting Member of Technical Staff, Oracle
Tuesday, Oct 23, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3004
which overlaps with
All You Need to Know About Backup and Recovery [TIP3266]
Francisco Munoz Alvarez, Director of Innovation, Red Stack Tech Ltd
Tuesday, Oct 23, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3001
which overlaps with
Driving Hands-Free with Oracle Autonomous OLTP Database Cloud [TRN3980]
Maria Colgan, master product manager at Oracle Corporation , Oracle
Robert Greene, Senior Director, Product Management, Oracle
Tuesday, Oct 23, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3003
which overlaps with
Saving Your Database from Memory Loss: Oracle Database In-Memory Analytics Improvements [TRN1263]
James Czuprynski, Advanced Technical Consultant Oracle Solutions, ViON Corporation
Tuesday, Oct 23, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3006
Tuesday evening
Autonomous and Beyond: Security in the Age of the Autonomous Database [PRM4108]
Russ Lowenthal, Director, Product Management, Oracle
Tuesday, Oct 23, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3006
which overlaps with
Oracle Autonomous Data Warehouse Cloud: What Every DBA Should Know [PRO4050]
George Lumpkin, Vice President, Product Management, Oracle
Tuesday, Oct 23, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3005
which overlaps with
Real-World DBA Best Practices for the Cloud [TIP2048]
Arup Nanda, VP, Data Services, Priceline
Tuesday, Oct 23, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3002
which overlaps with
How to Migrate to Oracle Autonomous Database Cloud [TRN4034]
Julian Dontcheff, Global Database Lead, Accenture Oy
Roy Swonger, Vice President, Database Upgrade & Utilities, Oracle
William Beauregard, Oracle
Tuesday, Oct 23, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3004
Partial Indexes–Take Care With Truncate
Partial indexes are a very cool feature that came along with Oracle 12c. The capability at partition level to control index existence allows for a couple of obvious use cases:
1) You index the data in recent partitions only, because small amounts of data are aggressively searched by applications and/or users, but not the older data because the queries for older data are either less frequent or are more analytical in nature.
2) You index the data in older partitions only, because you are putting the recent data into the in-memory store so almost any kind of access is very fast, but you do not want to swamp the entire in-memory store with older data that people rarely query. But you still want reasonable access performance on that old data.
Whatever your use-case, there is currently an outstanding issue with partial indexes that you need to be aware of. (Note: This is not correct functionality and will be fixed in a future release). If you truncate a table, then the partial index dictionary definition may not be correctly observed for partitions.
Here’s an example:
SQL> create table t ( x int, y int )
2 partition by range ( x )
3 (
4 partition p1 values less than ( 1000 ) indexing on,
5 partition p2 values less than ( 2000 ) indexing off
6 );
Table created.
SQL> insert into t
2 select rownum, rownum from dual connect by level < 2000;
1999 rows created.
SQL> create index ix on t ( x ) local indexing partial;
Index created.
SQL> select segment_name, partition_name
2 from user_segments
3 where segment_name = 'IX';
SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
IX P1
SQL> select partition_name, status
2 from user_ind_partitions
3 where index_name = 'IX';
PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 UNUSABLE
So far so good… partial indexing is working as expected. Then I truncate the table:
SQL> truncate table t;
Table truncated.
SQL>
SQL> select segment_name, partition_name
2 from user_segments
3 where segment_name = 'IX';
SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
IX P1
IX P2
SQL> select partition_name, status
2 from user_ind_partitions
3 where index_name = 'IX';
PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
SQL> select segment_name, partition_name
2 from user_segments
3 where segment_name = 'IX';
SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
IX P1
IX P2
SQL> select partition_name, status
2 from user_ind_partitions
3 where index_name = 'IX';
PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
And now we seem to have dropped our knowledge of partial indexing and the second index partition springs into existence. The dictionary for the table remains unaffected
SQL> select partition_name, indexing
2 from user_tab_partitions
3 where table_name = 'T';
PARTITION_NAME INDE
------------------------------ ----
P1 ON
P2 OFF
The workaround is easy. You simply re-issue the partial indexing command
SQL> alter table t modify partition p2 indexing off;
Table altered.
SQL>
SQL> select segment_name, partition_name
2 from user_segments
3 where segment_name = 'IX';
SEGMENT_NAME PARTITION_NAME
------------------------------ ---------------------------
IX P1
But obviously, you need to remember to do this.
Easy as pi…. hole.
A slight digression from my normal database-focussed content today 
In internet bandwidth and latency strapped Perth (Western Australia), every last drop of internet counts. Recently I stumbled upon this blog post by Troy Hunt about using a Raspberry Pi to serve as a local DNS to filter out unnecessary content. Personally, I don’t care about the content as such (I don’t like ads, but I do acknowledge that they are generally a necessary “evil”), but for me it is about getting the most performance out of my lowly internet connection until the technology evolves in Australia.
So I headed over to https://pi-hole.net/ and downloaded the installation script. I don’t have a Raspberry Pi, which is its intended platform, but I do have Virtualbox floating around, because I use that extensively for the Oracle VM’s that I need for tackling AskTOM questions on various versions of the database. I initially tried to install pi-hole on an existing Oracle Enterprise Linux installation, but the script makes a fair few assumptions about platform, and I had a lot of dramas. So I just downloaded a fresh install of Fedora 28, got it up and running in a small VM and voila! Pi-hole installed pretty much straight out of the gate.
Subjectively, things definitely seem a lot snappier now. My console suggests that a quarter of my traffic is being filtered out!
And I’m only using the pi-hole on a few machines at home currently. I’ll give it a few more days before I take the big jump and make it the DNS for my router so that all my devices can get the benefit.
But so far, so good. If you’re laden with slow clunky internet like I am, perhaps give pi-hole a look.
My APEX 18.2 upgrade in a nutshell
As always, you should read the Installation/Upgrade manual from top to bottom before upgrading any piece of software, and be aware of all of the pre-requisites and processes. But for me, my Application Express 18.2 upgrade was as simple as:
- Download Application Express
- Unzip to my target location
- sqlplus / as sysdba
- SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
- Go to my ORDS installation
- java -jar ords.war validate
- sqlplus / as sysdba
- SQL> @apex_rest_config.sql
And voila! A freshly upgraded Application Express in a matter of minutes…
Sweet!
OpenWorld Monday…I’m so screwed
As I mentioned in a previous blog post, I whipped up a small Application Express application to let me plan out my Openworld activities. But there’s a small problem with being able to quickly and easily find thing based on the schedule…. There is too much to see!!!
I had a quick glance through the schedule just for Monday, and already I can see myself being very frustrated with all of the content that I am going to have miss out on, simply because it clashes with other content I’d like to see.
And just to compound things 
with any luck, I’ll be able to score some time in the theatre in the demo grounds to give some lightning talks in both the morning and afternoon. More details on that soon!
But here is my wish list below – just smashed full of conflicts… sigh
Monday morning
The New Auto-Upgrade for Oracle Databases [TRN4031]
Daniel Overby Hansen, Database Administrator, SimCorp
Mike Dietrich, Master Product Manager – Database Upgrades and Migrations, Oracle
Monday, Oct 22, 9:00 a.m. – 9:45 a.m. | Moscone West – Room 3004
which overlaps with
High Availability and Sharding Deep Dive with Next-Generation Oracle Database [TRN4032]
Wei Hu, Vice President of Product Development, Oracle
Monday, Oct 22, 9:00 a.m. – 9:45 a.m. | Moscone West – Room 3007
which overlaps with
Oracle Linux and Oracle VM VirtualBox: The Enterprise Development Platform [PRO4724]
Simon Coter, Director of Product Management – Oracle VM & VirtualBox, Oracle
Sergio Leunissen, Vice President of Product Management, Oracle
Monday, Oct 22, 9:00 a.m. – 9:45 a.m. | Moscone South – Room 152
which overlaps with
A Day in the Life of a DBA in an Autonomous World [PRO4376]
Akshay Sangaonkar, Senior Principal Product Manager, Oracle
Karl Dias, Vice President, Database Manageability, Oracle
Monday, Oct 22, 9:00 a.m. – 9:45 a.m. | Marriott Marquis (Golden Gate Level) – Golden Gate C3
which overlaps with
Make AppDev Fun Again, with the Oracle RAD Stack [PRO4087]
Kris Rice, Senior Director, Oracle
Michael Hichwa, Software Development VP, Oracle
Joel Kallman, Senior Director, Software Development, Oracle
Monday, Oct 22, 9:00 a.m. – 9:45 a.m. | Moscone West – Room 3009
which overlaps with
Using Deep Learning and Neural Networks in Oracle Database 18c [BUS1891]
Brendan Tierney, ., Oralytics
Neil Chandler, Database Administrator, Chandler Systems Ltd
Monday, Oct 22, 9:00 a.m. – 9:45 a.m. | Moscone West – Room 3001
which overlaps with
Analytic Functions: A Developer’s Best Friend [DEV6244]
Timothy Hall, DBA, Developer, Author, Trainer, oracle-base.com
Code One Tracks: Database, Big Data, and Data Science
Session Type: Developer Session
Monday, Oct 22, 9:00 a.m. – 9:45 a.m. | Moscone West – Room 2003
Monday mid-morning
Oracle Linux and Oracle VM: Get Trained for Cloud, Hybrid, and On-Premises [TRN5828]
Avi Miller, Product Management Director, Oracle
Antoinette O’SULLIVAN, Director of Training and Documentation for Linux and Virtualization, Oracle
Monday, Oct 22, 10:30 a.m. – 11:15 a.m. | Moscone South – Room 154
which overlaps with
Why Citizen Developers Should Be Your New Best Friend [TRN4091]
David Peake, Senior Principal Product Manager, Oracle
Monday, Oct 22, 10:30 a.m. – 11:15 a.m. | Moscone West – Room 3009
which overlaps with
Oracle Autonomous Database Cloud [PKN3947]
Andrew Mendelsohn, Executive Vice President Database Server Technologies, Oracle
Monday, Oct 22, 11:00 a.m. – 12:15 p.m. | The Exchange @ Moscone South – The Arena
which overlaps with
3D: Docker for Database Developers [TIP1247]
Roel Hartman, Director, APEX Consulting
Monday, Oct 22, 11:30 a.m. – 12:15 p.m. | Moscone West – Room 3020
which overlaps with
Oracle Linux: State of the Penguin [PRO4720]
Wim Coekaerts, Senior Vice President, Operating Systems and Virtualization Engineering, Oracle
Monday, Oct 22, 11:30 a.m. – 12:15 p.m. | Moscone West – Room 2000
which overlaps with
50 Shades of Data: How, When, Why—Big, Relational, NoSQL, Elastic, Graph, Event [DEV4976]
Lucas Jellema, CTO, AMIS Services BV
Code One Tracks: Database, Big Data, and Data Science, Java Server-Side Development and Microservices
Session Type: Developer Session
Monday, Oct 22, 10:30 a.m. – 11:15 a.m. | Moscone West – Room 2007
which overlaps with
Microservices: Get Rid of Your DBA and Send the DB into Burnout [DEV5504]
Franck Pachot, Computing Engineer, CERN
Code One Tracks: Database, Big Data, and Data Science
Session Type: Developer Session
Monday, Oct 22, 10:30 a.m. – 11:15 a.m. | Moscone West – Room 2003
which overlaps with
REST-Enabled Neural Networks in Oracle Database 18c [DEV5026]
Brendan Tierney, ., Oralytics
Neil Chandler, Database Architect, Chandler Systems
Code One Tracks: Database, Big Data, and Data Science
Session Type: Developer Session
Monday, Oct 22, 11:30 a.m. – 12:15 p.m. | Moscone West – Room 2001
which overlaps with
Real-Life SQL Tuning: From Four Minutes to Eight Seconds in an Hour [DEV5668]
Liron Amitzi, Senior Database Consultant, Brillix LTD
Code One Tracks: Database, Big Data, and Data Science
Session Type: Developer Session
Monday, Oct 22, 11:30 a.m. – 12:15 p.m. | Moscone West – Room 2003
Monday afternoon
Oracle Database in an Asynchronous World [TRN5515]
Dominic Giles, Master Product Manager, Oracle
Monday, Oct 22, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3003
which overlaps with
A Day in the Life of a Real-World Performance Engineer [TRN4026]
Graham Wood, Software Architect, Oracle
Robert Carlin, Software Development Manager, Real World Performance, Oracle Database Development, Oracle
Mihajlo Tekic, Oracle
Monday, Oct 22, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3004
which overlaps with
Build a Web App with Oracle REST Data Services and Oracle JavaScript Extension Toolkit [HOL6325]
Jeff Smith, Senior Principal Product Manager, Oracle
Ashley Chen, Senior Product Manager, Oracle
Colm Divilly, Consulting Member of Technical Staff, Oracle
Elizabeth Saunders, Principal Technical Staff, Oracle
Monday, Oct 22, 3:45 p.m. – 4:45 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 3/4
Monday late afternoon
Oracle Optimizer and the Road to the Latest Generation of Oracle Database [PRO4009]
Nigel Bayliss, Senior Principal Product Manager, Oracle
Monday, Oct 22, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3005
which overlaps with
Oracle Autonomous OLTP Database Cloud Overview and Roadmap [PRO3978]
Maria Colgan, master product manager at Oracle Corporation , Oracle
Juan Loaiza, Senior Vice President, Oracle
Monday, Oct 22, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3003
which overlaps with
Data Warehouse Like a Tech Startup with Oracle Autonomous Data Warehouse Cloud [BUS3194]
Mark Rittman, Managing Director, MJR Analytics ltd
Monday, Oct 22, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3006
Monday evening
Data Management in a Microservices World [TIP4175]
Gerald Venzl, Senior Principal Product Manager, Oracle
Monday, Oct 22, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3003
which overlaps with
18(ish) Things Developers Will Love About Oracle Database 18c [PRO4093]
Chris Saxon, Developer Advocate for SQL, Oracle
Monday, Oct 22, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3009
which overlaps with
What’s New for Oracle SQL Developer [PRO4058]
Jeff Smith, Senior Principal Product Manager, Oracle
Monday, Oct 22, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3006
which overlaps with
What Everyone Should Know About Oracle Partitioning [PRO4046]
Hermann Baer, Senior Director Product Management, Oracle
Monday, Oct 22, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3005
Correcting datatypes with minimal downtime
Just a quick post here by request of an attendee of the September Office Hours. I had a demo converting the data type of a primary key without losing data and with minimal impact to the availability of the database using DBMS_REDEFINITION. You can see that video here
but here is the script used to run that demo.
SQL> drop table t purge;
Table dropped.
SQL> drop table t_interim purge;
Table dropped.
--
-- Source table with string primary key that we want to convert to numeric
--
SQL>
SQL> create table t as
2 select
3 to_char(object_id) pk
4 ,owner
5 ,object_name
6 ,subobject_name
7 ,object_id
8 ,data_object_id
9 from all_objects
10 where object_id is not null;
Table created.
SQL>
SQL> alter table t add primary key ( pk );
Table altered.
--
-- Empty interim table with numeric string primary, representing our corrected data type
--
SQL>
SQL> create table t_interim as
2 select
3 object_id pk
4 ,owner
5 ,object_name
6 ,subobject_name
7 ,object_id
8 ,data_object_id
9 from all_objects
10 where 1=0;
Table created.
SQL>
SQL> desc t
Name Null? Type
----------------------------------------------------------------------- -------- -------------------------------------
PK NOT NULL VARCHAR2(40)
OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
SQL>
SQL> desc t_interim
Name Null? Type
----------------------------------------------------------------------- -------- -------------------------------------
PK NOT NULL NUMBER
OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
--
-- Standard call will fail, because the columns do not align
--
SQL>
SQL> begin
2 dbms_redefinition.start_redef_table
3 ( uname => user,
4 orig_table => 'T',
5 int_table => 'T_INTERIM'
6 );
7 end;
8 /
begin
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 109
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3887
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5208
ORA-06512: at line 2
--
-- Column mapping call fails, because the columns being manipulated is the primary key
--
SQL>
SQL> declare
2 l_colmap varchar2(200) :=
3 q'{ to_number(pk) as pk
4 ,owner
5 ,object_name
6 ,subobject_name
7 ,object_id
8 ,data_object_id}';
9 begin
10 dbms_redefinition.start_redef_table
11 ( uname => user,
12 orig_table => 'T',
13 int_table => 'T_INTERIM',
14 col_mapping => l_colmap
15 );
16 end;
17 /
declare
*
ERROR at line 1:
ORA-42008: error occurred while instantiating the redefinition
ORA-12016: materialized view does not include all primary key columns
ORA-06512: at "SYS.DBMS_REDEFINITION", line 109
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3887
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5208
ORA-06512: at line 10
--
-- Abort this run to reset our objects
--
SQL>
SQL> begin
2 dbms_redefinition.abort_redef_table
3 ( uname => user,
4 orig_table => 'T',
5 int_table => 'T_INTERIM'
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
--
-- Use the ROWID in lieu of the primary key
--
SQL>
SQL> declare
2 l_colmap varchar2(200) :=
3 q'{ to_number(pk) as pk
4 ,owner
5 ,object_name
6 ,subobject_name
7 ,object_id
8 ,data_object_id}';
9 begin
10 dbms_redefinition.start_redef_table
11 ( uname => user,
12 orig_table => 'T',
13 int_table => 'T_INTERIM',
14 col_mapping => l_colmap,
15 options_flag=>DBMS_REDEFINITION.cons_use_rowid
16 );
17 end;
18 /
PL/SQL procedure successfully completed.
--
-- Add our subordinate objects
--
SQL>
SQL> alter table t_interim add primary key ( pk );
Table altered.
--
-- And finally finish off the process (this is the window of unavailability)
--
SQL>
SQL> begin
2 dbms_redefinition.finish_redef_table
3 ( uname => user,
4 orig_table => 'T',
5 int_table => 'T_INTERIM');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> desc t
Name Null? Type
----------------------------------------------------------------------- -------- -------------------------------------
PK NOT NULL NUMBER
OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
Enjoy!
Datatype conversion laziness … yet another reason
I’ve got two words to describe my thoughts on being lazy with data types …. JUST DON’T! 
There’s been many blog posts, videos, AskTOM questions etc over the years with numerous examples of how the optimizer gets confused, how the SQL engine gets confused…and even how developers will ultimately get confused if you do not spend that little tiny bit of extra effort in ensuring data type consistency.
I personally wish we had an init.ora parameter called (say) “response_to_data_type_conversion” = IMPLICIT (default) or ERROR, so that when we encountered a data type conversion we could choose to report it back to the developer as an error, or implicitly try to convert it as we currently do. Then we could have it set to “error” at least in non-production environments to get a handle on where we are not being diligent.
But anyway, enough ranting . The reason for this blog post to give yet another example of how making assumptions about data type handling can lead to spurious errors.
Let’s assume we have a simple requirement – to collapse a result set into a single CSV result stored in a CLOB. Here is my first cut at the code
SQL> set serverout on
SQL> declare
2 l_csv clob := empty_clob();
3 begin
4 for i in (select *
5 from dba_objects )
6 loop
7 l_csv := l_csv || i.owner||','||i.object_name||','||i.object_id;
8 end loop;
9 dbms_output.put_line('length=' || dbms_lob.getlength(l_csv));
10 end;
11 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7
ORA-06512: at line 7
And it fails. To get an idea as to why and where it fails, I’ll add an exception handler to capture the state of the CLOB.
SQL> set serverout on
SQL> declare
2 l_csv clob := empty_clob();
3 begin
4 for i in (select *
5 from dba_objects )
6 loop
7 l_csv := l_csv || i.owner||','||i.object_name||','||i.object_id;
8 end loop;
9 dbms_output.put_line('length=' || dbms_lob.getlength(l_csv));
10 exception
11 when others then
12 dbms_output.put_line('length=' || dbms_lob.getlength(l_csv));
13 raise;
14 end;
15 /
length=32776
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13
ORA-06512: at line 7
ORA-06512: at line 7
Now that number looks “interesting” in that it is right on the length limit for a VARCHAR2 in PL/SQL. But why would a VARCHAR2 come into play here? We are dealing with a CLOB and that should be allowed to get much larger. To further confuse things, look what happens when I perform functionally the same operation, but using an intermediate variable for each row fetched from the loop.
SQL> declare
2 l_csv clob := empty_clob();
3 l_line varchar2(255);
4 begin
5 for i in (select *
6 from dba_objects )
7 loop
8 l_line := i.owner||','||i.object_name||','||i.object_id;
9 l_csv := l_csv || l_line;
10 end loop;
11 dbms_output.put_line('length ' || length(l_csv));
12 end;
13 /
PL/SQL procedure successfully completed.
And now it works! That seems extraordinary because the logic would appear to be identical.
The answer here is once again – not taking sufficient care with our data type conversions. The OBJECT_ID we are fetching is numeric. Because we are simply slamming that into a concatenation operator (||), we need to do some implicit data type conversion, and to achieve that, we need to do some casting into VARCHAR2. We are not privy to how the PL/SQL execution engine is performing the conversion, but the error suggests that all components of the expression (including the left hand side) are being casted to VARCHAR2 and hence our “clob” ultimately exceeds the 32k limit.
The resolution is simple – take control of the data type conversion as we should have done anyway:
SQL> set serverout on
SQL> declare
2 l_csv clob := empty_clob();
3 begin
4 for i in (select *
5 from dba_objects )
6 loop
7 l_csv := l_csv || i.owner||','||i.object_name||','||to_char(i.object_id);
8 end loop;
9 dbms_output.put_line('length=' || dbms_lob.getlength(l_csv));
10 end;
11 /
length=3491433
PL/SQL procedure successfully completed.
The moral of the story remains the same. Consistent and explicit handling of data type conversions will give you more robust code.
Another little 12c improvement
You’ve got a huge table right? Massive! Immense! And then something bad happens. You get asked to remove one of the columns from that table.
“No problem” you think. “I won’t run the ‘drop column’ command because that will visit every block and take forever!”
So you settle on the perfect tool for such a scenario – simply mark the column as unused so that it is no longer available to application code and the developers that write that code.
But there’s a catch that not many people know about. SET UNUSED is meant to be just a trivial database dictionary operation. We don’t touch the data, we only manipulate the column definition and thus an execution of SET UNUSED should be instantaneous. This is indeed the case most of the time, but as the example below shows – the way the column was added to the table, can have a bearing on what it costs to have that column removed.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--
-- large-ish table to monitor the impact
--
SQL> create table t1
2 as
3 select d.*
4 from dba_objects d,
5 ( select 1 from dual connect by level <= 100);
Table created.
SQL> set timing on
SQL> select count(*) from t1;
COUNT(*)
----------
8713700
Elapsed: 00:00:01.92
--
-- Take an existing column to UNUSED ... instantaneous!
--
SQL> alter table t1 set unused column created;
Table altered.
Elapsed: 00:00:00.06
--
-- Add a new column, and then make it UNUSED ... instantaneous!
--
SQL> alter table t1 add new_col1 timestamp;
Table altered.
Elapsed: 00:00:00.01
SQL> alter table t1 set unused column new_col1;
Table altered.
Elapsed: 00:00:00.00
--
-- Add a new columns with a not null default, and then make it UNUSED ... careful!
--
SQL> alter table t1 add new_col2 timestamp default sysdate not null;
Table altered.
Elapsed: 00:00:00.00
SQL> alter table t1 set unused column new_col2;
Table altered.
Elapsed: 00:01:35.39
You will not get the same issue in 12c.
SQL> select banner from v$version;
BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> create table t1 nologging
2 as
3 select d.*
4 from dba_objects d,
5 ( select 1 from dual connect by level <= 100);
Table created.
SQL>
SQL> set timing on
SQL> select count(*) from t1;
COUNT(*)
----------
7951500
Elapsed: 00:00:02.20
SQL> alter table t1 set unused column created;
Table altered.
Elapsed: 00:00:00.08
SQL>
SQL> alter table t1 add new_col1 timestamp;
Table altered.
Elapsed: 00:00:00.00
SQL> alter table t1 set unused column new_col1;
Table altered.
Elapsed: 00:00:00.00
SQL>
SQL> alter table t1 add new_col2 timestamp default sysdate not null;
Table altered.
Elapsed: 00:00:00.01
SQL> alter table t1 set unused column new_col2;
Table altered.
Elapsed: 00:00:00.01
SQL>
