18c XE–running locally

There’s already a few blog posts out there, showing people (easily) getting their 18c XE instance downloaded, installed and running, so I won’t rehash that here.

For the majority of people, I’d imagine they’ll have their XE instance running on a small box and connecting to it from “afar”, namely, their own PC or an application server or similar.

But for those of you, especially in these early stages of release, who like me, want to just jump right into our VM’s and the like, and connect to the XE instance directly, there’s perhaps one thing we omitted as part of the automated install that might catch you out.

The default tnsnames.ora will look something like:


XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

The service of “XE” and its associated tnsnames.ora entry gives you connectivity into the container database. But we haven’t created a tnsnames.ora entry for the pluggable database which is probably what you’ll be using most of the time.

So a quick cut-paste of the existing entry will do the trick for you.


XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )

Of course, if you’re connecting via EZconnect or similar, you don’t explicitly need a tnsnames.ora entry, but many people prefer it.

Enjoy XE!

18c XE is live!

Just a quick post because this is perhaps what I think is one of the biggest game changers for the Oracle Database.

18c Express Edition (18x XE) is now available for general use. For those people with experience with 11g XE, this might not seem to be a big deal, but there is one crucial difference.

Unlike 11g XE, the new version has virtually no restrictions on the functionality offered by the database. And yes, we are talking Enterprise Edition features and options here.

So if you want to explore:

  • In-memory
  • Multi-tenant
  • Partitioning
  • Text

etc etc etc, the list goes on, then these will all be there in 18c XE for you.

And the product installs with just a couple of commands.

xe_sneak_peek

 

So don’t just sit there reading my post! Get out there and play!

https://www.oracle.com/database/technologies/appdev/xe.html

And of course, a huge shout out to Gerald Venzl who was the driving force in making this happen.

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 Smile

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

matrix_me_me_me

 

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 Smile 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 Smile

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!

image

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…

image

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 Smile 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 Smile

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!