Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
From Database 18.3 to 18.5 (on Windows)
Contrary to wild rumours on the internet, it was not a fear of the number 13 that led to a numbering jump from version 12c to version 18c. The jump was part of our new, more flexible release mechanism so that we can get fixes and enhancements to customers on a more frequent and predictable schedule. In a nutshell, smaller bundles of features and fixes, more frequently.
I won’t dwell on that – if you’re unfamiliar with the new strategy, the best place to start is MOS Note 2285040.1, which has a description and a FAQ. But in terms of (as the saying goes) eating one’s own dog food, I downloaded the 18.5 release update which came out this week, and applied it to my 18.3 installation and I thought I’d share the process.
You might be wondering: “Why am I skipping 18.4?” Well that can be summarised simply as ignorance on my part – I missed the email a couple of few months back that told me 18.4 was available 
You might also be wondering: “I read that there were 4 release updates per year, how come there is an 18.5?” That is a common misinterpretation. We aim to provide a release update each quarter, but we never said that this means there would only ever be 4.
Now I stress – I’m just sharing my experiences here, not claiming that this is your go-to guide for applying the release update. I was just updating 18c on a Windows laptop, not a server. You should always follow the installation documents that come with the release update. For example, I had to reboot my Windows machine a couple of times, because it had transpired that some webcam software I’d installed had opted to use the Microsoft C++ shared libraries in my 18c Oracle Home! I think we can safely assume your servers probably don’t have webcams on them!
Step 1: Download a fresh OPatch
Seriously…just do this every time. As it turned out, my OPatch was already at a high enough level to apply the release update, but for me, years ago I adopted the mindset: “If I am going to apply a patch, then download a new OPatch“. It just makes it a no-brainer.
Step 2: Shut everything down.
I am running single instance, no “rolling” patching for me.
Step 3: Unzip the patch, set the environment, and patch
C:\>set ORACLE_HOME=C:\oracle\product\18
C:\>set PATH=%ORACLE_HOME%\OPatch;%ORACLE_HOME%\perl\bin;%PATH%
C:\>set PERL5LIB=
C:\>cd \oracle\stage\29124511
C:\oracle\stage\29124511>opatch apply
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation. All rights reserved.
Oracle Home : C:\oracle\product\18
Central Inventory : C:\Program Files\Oracle\Inventory
from :
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.4.0
Log file location : C:\oracle\product\18\cfgtoollogs\opatch\opatch2019-01-18_09-36-46AM_1.log
Verifying environment and performing prerequisite checks...
Conflicts/Supersets for each patch are:
Patch : 29124511
Conflict with 28267731
Conflict details:
C:\oracle\product\18\javavm\admin\classes.bin
C:\oracle\product\18\javavm\admin\lfclasses.bin
C:\oracle\product\18\javavm\jdk\jdk8\admin\classes.bin
C:\oracle\product\18\javavm\jdk\jdk8\admin\lfclasses.bin
C:\oracle\product\18\rdbms\admin\oracle.sym
C:\oracle\product\18\bin\oracle.exe
Bug Superset of 27783303
Super set bugs are:
27748954, 27604293, 27747869, 27984028, 27550341, 27389352, 27433163, 27538461, 27341181,....
Well…that didn’t go so well Now the first thing confused me was: “How could there be any pre-existing patch to conflict with?”. After all, this was a complete 18c installation that I had downloaded from OTN when it first became available. But then I remembered, this is 18.3. So while it’s true that I download it as a standalone complete piece of software, it is still a patched release of the database. This is so much better than the old days where if you wanted (say) version 9.2.0.8, you had to download 9.2.0.1 and then apply the patch on top of it before using the software. A listing of the patch inventory showed that I already had some patches installed with my initial fresh download.
C:\oracle\stage\29124511>opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation. All rights reserved.
Oracle Home : C:\oracle\product\18
Central Inventory : C:\Program Files\Oracle\Inventory
from :
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.4.0
Log file location : C:\oracle\product\18\cfgtoollogs\opatch\opatch2019-01-18_09-39-23AM_1.log
Lsinventory Output file location : C:\oracle\product\18\cfgtoollogs\opatch\lsinv\lsinventory2019-01-18_09-39-23AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: gtx
ARU platform id: 233
ARU platform description:: Microsoft Windows (64-bit AMD)
Installed Top-level Products (1):
Oracle Database 18c 18.0.0.0.0
There are 1 products installed in this Oracle Home.
Interim patches (3) :
Patch 28267731 : applied on Sat Aug 18 17:34:22 AWST 2018
Unique Patch ID: 22301563
Patch description: "WINDOWS OJVM BUNDLE PATCH : 18.3.0.0.180717 (28267731)"
Created on 8 Jul 2018, 06:48:38 hrs PST8PDT
Bugs fixed:
27642235, 27952586, 27304131, 27461740, 27636900, 27539876
This patch overlays patches:
27783303
This patch needs patches:
27783303
as prerequisites
Patch 27908644 : applied on Sat Aug 18 17:31:26 AWST 2018
Unique Patch ID: 22299245
Patch description: "UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171"
Created on 29 Jun 2018, 02:51:19 hrs PST8PDT
Bugs fixed:
27908644
Patch 27783303 : applied on Sat Aug 18 17:25:42 AWST 2018
Unique Patch ID: 22238986
Patch description: "Windows Database Bundle Patch : 18.3.0.0.180717 (27783303)"
Created on 16 Aug 2018, 07:58:43 hrs PST8PDT
Bugs fixed:
27026401, 27994333, 27680509, 27314206, 27345231, 24925863, 27447452
26792891, 27236110, 27608669, 27670484, 27421101, 27240246, 27213140
27740844, 27616657, 18701017, 27177852, 27697092, 27379956, 26598422
27688099, 27580996, 27534509, 27602488, 27333978, 27098733, 27163313
27551855, 27012915, 27603841, 27224987, 28165545, 27259983, 27919283
28039953, 27357773, 27302730, 27263996, 27345498, 27517818, 27377219
26882126, 27396377, 27701279, 27285557, 27779886, 27739006, 27585755
27321834, 27748954, 27950708, 26646549, 26961415, 27061736, 27066519
27498477, 28174926, 21806121, 24489904, 27570318, 27365139, 27028251
27435537, 27869339, 27226913, 27192754, 27038986, 27537472, 27483974
27329812, 27356373, 27714373, 27422874, 27334648, 27339115, 25035594
27128580, 27952762, 27691717, 27534289, 27425622, 27434974, 27518227
27426363, 27352600, 26450454, 27631506, 27143882, 27346949, 27181521
27199245, 27252023, 27911160, 27365702, 27497950, 26586174, 12816839
27389352, 25287072, 27215007, 27345190, 27475272, 25634405, 27813267
27726269, 27463879, 27086406, 27283029, 27850736, 27338838, 27428790
27395404, 27924147, 27284286, 27430254, 27262945, 27250547, 27346329
27693713, 27347126, 27745220, 27341036, 27481765, 28174827, 27450355
27214085, 27503413, 27451182, 27359178, 27625274, 27587672, 28320117
27367194, 27782464, 27735534, 27396365, 27210872, 27501327, 27984028
27309182, 27520070, 27999597, 27381383, 27302415, 27378103, 27861909
27782339, 27254851, 27086821, 27101273, 27465480, 27232983, 27941514
27486253, 27489719, 27222626, 27560562, 27244785, 27458829, 27262650
27155549, 25743479, 27897639, 27615608, 27459909, 27267992, 27304936
27663370, 27602091, 27448162, 27434486, 26933599, 26928317, 27586810
27153755, 27348081, 27314390, 27786669, 27573408, 27532375, 26818960
25824236, 27563767, 27060859, 27126666, 27284499, 27210038, 25726981
9062315, 27301308, 27725967, 27452760, 28188330, 27834984, 27748321
26990202, 27811439, 27376871, 27654039, 27080748, 27333664, 28059199
27420715, 27315159, 27331374, 27398660, 27680162, 25035599, 27718914
27599689, 27595801, 26615291, 27040560, 26476244, 27801774, 27450783
27066451, 27935826, 28098865, 26226953, 27501465, 27558559, 27496806
27381656, 27299455, 27124867, 27496224, 27707544, 27163928, 27147979
27395416, 27532009, 21766220, 27727843, 27607805, 27271876, 26860285
27997875, 28204423, 27204133, 27627992, 27592466, 27395794, 27430802
27511196, 27302800, 27204476, 27941896, 27560702, 27053044, 24689376
27447687, 27451049, 27302695, 26843664, 27181537, 27744211, 27445462
26427905, 27106915, 27896388, 27812593, 27926113, 27487795, 27135647
27496308, 28239335, 27452897, 26986173, 27434050, 27513114, 27114112
27265816, 27274536, 27396624, 28090453, 27396666, 27573409, 27331069
27379846, 27270197, 27016503, 27934468, 27595973, 27410595, 27833369
27577758, 26966120, 27757979, 27434193, 27393421, 27032726, 27613080
27208953, 27538461, 27581484, 27321179, 27263677, 26898279, 27573154
27492916, 27961746, 27591842, 27868727, 27047831, 27783289, 27405242
27970265, 27333693, 27506774, 27112686, 27379233, 27471876, 27425507
28205874, 27544030, 27401637, 27740854, 27993298, 27193810, 27212208
27184253, 27288230, 27399499, 27786772, 27978668, 26423085, 27873643
27481406, 27182006, 27547732, 27889841, 27169796, 27501413, 27679488
27518310, 27545630, 27346644, 27625010, 27360126, 27378959, 27772815
27525909, 27945870, 27275136, 27321575, 28182503, 26336101, 27216224
27375260, 27249544, 27174948, 27294480, 27957892, 27774539, 27664702
27839732, 27166715, 27432355, 27257509, 27657920, 27190851, 27773602
27774320, 27508936, 27457666, 27330161, 27339396, 27092991, 27101652
27803665, 27396672, 27472969, 27579969, 27610269, 27302594, 27778433
27339495, 25724089, 27739957, 28023410, 27433163, 27222121, 27851757
28109698, 27732323, 27691809, 27503208, 26822620, 28264172, 26846077
27189611, 27222423, 28132287, 27121566, 27282707, 27133637, 27451531
27613247, 27560735, 27702244, 27341181, 28240153, 27479358, 27370933
27396357, 27153641, 26827699, 27238258, 27364916, 27307868, 27791223
27041253, 27090765, 27892488, 27034318, 27349393, 27412805, 27399762
27302960, 27679664, 27815347, 27399985, 27241247, 26654411, 27077948
26987877, 27354783, 27701795, 27304410, 27882176, 27119621, 26956033
27300007, 27339165, 28106402, 27451187, 27058530, 21547051, 28025398
27682288, 27398080, 27586895, 27679806, 27164122, 27243810, 13554903
27993289, 27504190, 26587652, 27212837, 27274143, 27768034, 27550341
27558861, 27060167, 27600706, 28022847, 27241221, 27131377, 26992964
27690578, 27747407, 27305318, 27230645, 27818871, 27346709, 28057267
27405696, 27523368, 27574335, 27526362, 27174938, 27931506, 27392187
27221900, 27797290, 28184554, 27401618, 27410300, 26313403, 27864737
27362190, 27439835, 24737581, 27368850, 27593587, 27751006, 23840305
26996813, 27625050, 27657467, 27073066, 27302711, 27453225, 27984314
27274456, 27522245, 27417186, 27469329, 27338946, 27396813, 27786699
27285244, 27692215, 27519708, 23003564, 27339483, 27783059, 26882316
27757567, 26527054, 27862636, 27563629, 27635508, 27508985, 26785169
27472898, 27971575, 28413955, 27302681, 27609819, 27345450, 27788520
27018734, 27766679, 27101527, 27906509, 27593389, 27625620, 27036408
27358232, 27335682, 23698980, 27144533, 27585800, 27458164, 22734786
27523800, 28169711, 27384222, 27723002, 27473800, 27310092, 27604293
27731346, 27365993, 27005278, 27320985, 27445330, 27649707, 27709046
27313687, 27249215, 25348956, 27065091, 26433972, 27339654, 27318988
27333658, 27533819, 27403244, 27520900, 27534651, 27030974, 27427805
27359368, 23310101, 27644757, 27314512, 27044575, 27223171, 27240570
27156355, 27528204, 27989849, 27143756, 27679961, 27110878, 25943740
27747869, 27734470, 27283960, 27682151, 27719187, 26894737, 27869283
27652302, 27182064, 27467543, 27334353, 26843558, 27840386, 27457891
27334316, 27372756, 27705761, 27484556, 27708711, 27753336, 27364891
27964051, 27503318, 27423251, 27865439, 27780562, 26731697, 27358241
27634676, 27726780, 27444727, 27441326, 27326204, 27812560, 27432338
27577122, 27177551, 27275776, 27558557, 27375542, 26299684, 27301568
27593263, 27258578, 27222938, 27703242, 27512439, 27508984, 27398223
27330158, 27807441, 27079545, 27404599, 27259386, 27688692, 28039471
27292213, 27392256, 27307934, 27617522, 27505603, 27450400, 27348707
27460675, 27238077, 27346984, 27364947, 26943660, 27236052, 27338912
27244337, 28021205, 28032758, 28033429, 27263276, 27579353, 27233563
27220610, 28099592, 27433870, 27426277, 26647619, 27847259, 25929650
27738679, 27502420, 25709124, 28045209, 27668379, 27318869, 27832643
27302777, 28072130, 27442041, 27430219, 27614272, 27930478
--------------------------------------------------------------------------------
OPatch succeeded.
The conflict was with 28267731, which is the OJVM patch. I remembered from older 12c versions that the remedy to this was to rollback the older JVM patch before applying the new one. So I ran
C:\oracle\stage\29124511>opatch rollback -id 28267731
and then downloaded the 18.5 OJVM patch (28790647) as well in readiness.
After that, everything went as planned. I applied the DB patch and then applied the 18.5 OJVM patch. The final step was to open my database (and all of the pluggable database) and run in the database-level patch changes using datapatch
Microsoft Windows [Version 10.0.17134.523]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\>set ORACLE_HOME=C:\oracle\product\18
C:\>set PATH=%ORACLE_HOME%\OPatch;%ORACLE_HOME%\perl\bin;%PATH%
C:\>set PERL5LIB=
C:\>cd C:\oracle\product\18\bin
C:\oracle\product\18\bin>sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jan 18 12:33:19 2019
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0
C:\oracle\product\18\bin>cd %ORACLE_HOME%/OPatch
C:\oracle\product\18\OPatch>datapatch -verbose
SQL Patching tool version 18.0.0.0.0 Production on Fri Jan 18 12:33:32 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: C:\oracle\cfgtoollogs\sqlpatch\sqlpatch_16792_2019_01_18_12_33_32\sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 28267731 (WINDOWS OJVM BUNDLE PATCH : 18.3.0.0.180717 (28267731)):
Binary registry: Not installed
PDB CDB$ROOT: Applied successfully on 23-AUG-18 10.31.18.372000 AM
PDB PDB$SEED: Applied successfully on 23-AUG-18 10.36.41.858000 AM
PDB PDB1: Applied successfully on 23-AUG-18 10.36.41.858000 AM
Interim patch 28790647 (OJVM RELEASE UPDATE: 18.5.0.0.190115 (28790647)):
Binary registry: Installed
PDB CDB$ROOT: Not installed
PDB PDB$SEED: Not installed
PDB PDB1: Not installed
Current state of release update SQL patches:
Binary registry:
18.5.0.0.0 Release_Update 1812202039: Installed
PDB CDB$ROOT:
Applied 18.3.0.0.0 Release_Update 1808132056 successfully on 23-AUG-18 10.31.18.366000 AM
PDB PDB$SEED:
Applied 18.3.0.0.0 Release_Update 1808132056 successfully on 23-AUG-18 10.36.41.852000 AM
PDB PDB1:
Applied 18.3.0.0.0 Release_Update 1808132056 successfully on 23-AUG-18 10.36.41.852000 AM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB1
The following interim patches will be rolled back:
28267731 (WINDOWS OJVM BUNDLE PATCH : 18.3.0.0.180717 (28267731))
Patch 29124511 (Windows Database Bundle Patch : 18.5.0.0.190115 (29124511)):
Apply from 18.3.0.0.0 Release_Update 1808132056 to 18.5.0.0.0 Release_Update 1812202039
The following interim patches will be applied:
28790647 (OJVM RELEASE UPDATE: 18.5.0.0.190115 (28790647))
Installing patches...
Patch installation complete. Total patches installed: 9
Validating logfiles...done
Patch 28267731 rollback (pdb CDB$ROOT): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\28267731\22301563/28267731_rollback_DB18_CDBROOT_2019Jan18_12_34_14.log (no errors)
Patch 29124511 apply (pdb CDB$ROOT): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\29124511\22646084/29124511_apply_DB18_CDBROOT_2019Jan18_12_34_47.log (no errors)
Patch 28790647 apply (pdb CDB$ROOT): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\28790647\22646085/28790647_apply_DB18_CDBROOT_2019Jan18_12_35_21.log (no errors)
Patch 28267731 rollback (pdb PDB$SEED): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\28267731\22301563/28267731_rollback_DB18_PDBSEED_2019Jan18_12_35_23.log (no errors)
Patch 29124511 apply (pdb PDB$SEED): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\29124511\22646084/29124511_apply_DB18_PDBSEED_2019Jan18_12_35_37.log (no errors)
Patch 28790647 apply (pdb PDB$SEED): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\28790647\22646085/28790647_apply_DB18_PDBSEED_2019Jan18_12_36_07.log (no errors)
Patch 28267731 rollback (pdb PDB1): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\28267731\22301563/28267731_rollback_DB18_PDB1_2019Jan18_12_35_24.log (no errors)
Patch 29124511 apply (pdb PDB1): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\29124511\22646084/29124511_apply_DB18_PDB1_2019Jan18_12_35_41.log (no errors)
Patch 28790647 apply (pdb PDB1): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\28790647\22646085/28790647_apply_DB18_PDB1_2019Jan18_12_36_12.log (no errors)
SQL Patching tool complete on Fri Jan 18 12:36:17 2019
C:\oracle\product\18\OPatch>
C:\oracle\product\18\OPatch>
C:\oracle\product\18\OPatch>sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jan 18 12:36:28 2019
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2936010432 bytes
Fixed Size 9033408 bytes
Variable Size 1023410176 bytes
Database Buffers 1895825408 bytes
Redo Buffers 7741440 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
----------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2019-01-18 12:37:58
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
----------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2019-01-18 12:38:01
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
================================
And that was all there was to it. I’ve now picked up over a 1000 fixes and improvements to the software. If you are one of those people who likes to patch rarely, I strongly recommend you have a re-think and take a closer look at the release update cycle. We want to get you better software, faster.
Enjoy your 18.5 release!
EXPORT not GATHER with DBMS_STATS
Just a short post today on something that came in as a question for the upcoming Office Hours session which I thought could be covered quickly in a blog post without needing a lot of additional discussion for which Office Hours is more suited to.
The question was:
“When I gather statistics using DBMS_STATS, can I just create a statistic table and pass that as a parameter to get the results of the gather”
And the answer simply is “No” 
but let me clear up the confusion.
Many DBMS_STATS routines allow you to pass the name of the “statistics table” into which statistical information for the table, schema, database etc will be stored. For example, you can see the STAT-prefixed parameter to GATHER_TABLE_STATS
PROCEDURE GATHER_TABLE_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
ESTIMATE_PERCENT NUMBER IN DEFAULT
BLOCK_SAMPLE BOOLEAN IN DEFAULT
METHOD_OPT VARCHAR2 IN DEFAULT
DEGREE NUMBER IN DEFAULT
GRANULARITY VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATTAB VARCHAR2 IN DEFAULT
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
STATTYPE VARCHAR2 IN DEFAULT
FORCE BOOLEAN IN DEFAULT
CONTEXT CCONTEXT IN DEFAULT
OPTIONS VARCHAR2 IN DEFAULT
The statistics table must be of a certain structure, which is managed via the API as well using the CREATE_STAT_TABLE routine.
SQL> exec dbms_stats.create_stat_table('','st')
PL/SQL procedure successfully completed.
SQL> desc ST
Name Null? Type
----------------------------- -------- --------------------
STATID VARCHAR2(128)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(128)
C2 VARCHAR2(128)
C3 VARCHAR2(128)
C4 VARCHAR2(128)
C5 VARCHAR2(128)
C6 VARCHAR2(128)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
N13 NUMBER
D1 DATE
T1 TIMESTAMP(6) WITH TI
ME ZONE
R1 RAW(1000)
R2 RAW(1000)
R3 RAW(1000)
CH1 VARCHAR2(1000)
CL1 CLOB
BL1 BLOB
But when calling the GATHER_xxx routines, if you pass the name of the statistic table, please note that this is for getting a copy of the relevant statistics before the fresh gathering of statistics is done. We can see this with a simple demo.
SQL> create table t1 as select * from dba_objects;
Table created.
SQL>
SQL> select num_rows
2 from user_tables
3 where table_name = 'T1';
NUM_ROWS
----------
83608
1 row selected.
So we can see that the table T1 has ~83,000 rows in it. Since I’m running this on 12c, there was no need to gather statistics after this initial load, because they were collected automatically as part of the loading process. (Very nifty!).
I now add another ~250,000 rows and perform a new GATHER_TABLE_STATS call, this time passing in the name of the statistics table (ST) that I just created.
SQL>
SQL> insert into t1
2 select * from t1;
83608 rows created.
SQL>
SQL> insert into t1
2 select * from t1;
167216 rows created.
SQL>
SQL> begin
2 dbms_stats.gather_table_stats
3 ( ownname=>'MCDONAC',
4 tabname=>'T1',
5 stattab=>'ST',
6 statid=>'STATS'
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
Digging into the statistic table data, you can see that the number of rows recorded for T1 is 83597, which is the before image of the optimizer stat, not the after image.
SQL> select * from st where c1 = 'T1' and type = 'T'
2 @pr
==============================
STATID : STATS
TYPE : T
VERSION : 8
FLAGS : 2
C1 : T1
C2 :
C3 :
C4 :
C5 : MCDONAC
C6 :
N1 : 83597
N2 : 1607
N3 : 129
N4 : 83597
N5 :
N6 :
N7 :
N8 :
N9 : 0
N10 :
N11 :
N12 :
N13 :
D1 : 16-JAN-19
T1 :
R1 :
R2 :
R3 :
CH1 :
CL1 :
BL1 :
PL/SQL procedure successfully completed.
So just remember that if you want to get the DBMS_STATS information that is the result of a GATHER_xxx call, then you can follow it up with the appropriate EXPORT_xxx call to dump the data.
My APEX was fine and then it wasn’t
I got a nasty shock this morning when I fired up my local Application Expression installation.
It had been working fine and all of a sudden…just dead. I sounded like all of those family members that as I.T practitioners we have to deal with (and that we’re so sceptical of) when they say: “I didn’t change anything…it just stopped!” 
In keeping with the treatment of family members, I then adopted the advice that I normally give them first.
and upon restart, I saw the following during the startup
C:\oracle\ords181>java -jar ords.war standalone
2019-01-11 11:47:36.071:INFO::main: Logging initialized @1378ms to org.eclipse.jetty.util.log.StdErrLog
Jan 11, 2019 11:47:36 AM
INFO: HTTP and HTTP/2 cleartext listening on port: 8080
Jan 11, 2019 11:47:36 AM
INFO: The document root is serving static resources located in: C:\oracle\ords181\conf\ords\standalone\doc_root
2019-01-11 11:47:36.409:INFO:oejs.Server:main: jetty-9.4.z-SNAPSHOT, build timestamp: 2017-11-22T05:27:37+08:00, git hash: 82b8fb23f757335bb3329d540ce37a2a2615f0a8
2019-01-11 11:47:36.422:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2019-01-11 11:47:36.423:INFO:oejs.session:main: No SessionScavenger set, using defaults
2019-01-11 11:47:36.423:INFO:oejs.session:main: Scavenging every 600000ms
Jan 11, 2019 11:47:37 AM
WARNING: The pool named: |apex|| is invalid and will be ignored: The connection pool named: apex is not correctly configured, due to the following error(s): ORA-28001: the password has expired
Jan 11, 2019 11:47:37 AM
WARNING: The pool named: |apex|al| is invalid and will be ignored: The connection pool named: apex_al is not correctly configured, due to the following error(s): ORA-28001: the password has expired
Since security is our #1 thing for 2019 and probably should be the #1 item on your agenda for 2019, this was caused by some improvements to the Oracle defaults when you perform a database installation. Rather than the default being an “flexible” (aka loose ) policy we used to have when it comes to password management, we’ve gone for some more sensible options out of the box.
SQL> select * from dba_profiles order by 1,2
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------------- -------------------------------- -------- -----------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT INACTIVE_ACCOUNT_TIME PASSWORD UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
So if you want your APEX public accounts (and I stress, no others!) to have a non-expiring password, then you should create a custom profile for those accounts and assign them accordingly.
create profile no_expire
limit password_life_time unlimited;
alter user apex_public_user profile no_expire ;
alter user apex_rest_public_user profile no_expire ;
alter user apex_listener profile no_expire ;
alter user ords_public_user profile no_expire ;
This will ensure you don’t get an unexpected drama next time you want to fire up Application Express.
2018-what grabbed your attention
Here are the blog posts that you hit on most this year, with the most viewed entry on top. Unsurprisingly it is about the release of 18c, but interestingly the ORA-14758 and the Active Sessions post have come up again from last years list, so it appears they are still common issues for the modern Oracle professional. And of course, it is nice to see that my Openworld Mega-download is helping the community.
Thanks for supporting the blog, and as always, there will be more content next year !
Your New Years Resolution
Aligning roughly with the calendar year, based on the Chinese zodiak we’re about to go from the year of the dog to the year of the pig. But for me, in the “Information Technology Zodiak” 
, 2018 was the year of the hack, just as it was in 2017 and just as it will be for 2019.
I’ve not dedicated much time to keeping a record of all of the high profile breaches this year, but just off the top of my head I can think of:
- The Elasticsearch breach,
- The Starwood breach which, as a frequent traveller, probably guarantees I’ve been impacted in some as yet unknown way,
- The Quora breach, a site that is very popular amongst I.T professionals.
- The Google+ breach, which peeved me more about the fact that it was hush hushed by Google in order to preserve reputational damage. Um…what happened to looking after customers?
Adding to that list, whilst we were discussing security this year at Sangam 18, indian friends reminded me of the enormous Aadhaar breach at the start of the year, which is perhaps one of the largest of all time in terms of numbers of people impact.
And to be honest, I lost track of how many, which, where etc the amount of dramas that Facebook had.
I’m a realist so I’m not saying that hacks can always be avoided, or that anyone who gets hacked is being negligent. Sometimes even the best efforts are not good enough, because the hacks get more and more sophisticated all the time. But at the very least, I think we should set a goal for 2019 that all of our data at rest should be encrypted. If we can at least start with that small step, then at least if our data at rest is somehow nefariously obtained, it will still have a veil of protection.
So what’s your I.T resolution for next year? I hope it’s security related.
Another day…another "use the right datatype" post
Here’s an interesting little oddity (aka bug) with scalar queries.
We’ll start with a simple working example
SQL> create table t1 ( c1 number );
Table created.
SQL> insert into t1 values (1);
1 row created.
SQL> create table t2 ( c1 int, c2 varchar2(10));
Table created.
SQL>
SQL> insert into t2 values(1,'t1');
1 row created.
SQL> insert into t2 values(1,'t01');
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','T1')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','T2')
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select a.c1,
2 ( select max(b.c2) from t2 b where a.c1 = b.c1 )
3 from t1 a;
C1 (SELECTMAX
---------- ----------
1 t1
1 row selected.
That all seems straightforward:
- We got the value 1 from T1.C1,
- used that as an input to the query into T2
- got the maximum of the 2 matching rows from table T2
- return the result as a column outer query
Let us now repeat the example, but we’ll now make column C1 in table T2 a VARCHAR2. The query will remain unchanged, so now we are comparing a numeric “1” with a varchar2 value of “1”.
SQL> create table t1 ( c1 number );
Table created.
SQL> insert into t1 values (1);
1 row created.
SQL> create table t2 ( c1 varchar2(10), c2 varchar2(10));
Table created.
SQL>
SQL> insert into t2 values('1','t1');
1 row created.
SQL> insert into t2 values('01','t01');
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','T1')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','T2')
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select a.c1,
2 ( select max(b.c2) from t2 b where a.c1 = b.c1 )
3 from t1 a;
C1 (SELECTMAX
---------- ----------
1 t1
1 t01
2 rows selected.
Whoops…that doesn’t look right.
Don’t get me wrong, that is an error in the way that we are processing the query, because we should not be getting 2 rows back from it. But it is another example of where you can encounter boundary cases (and hence bugs) when you stray from the well-trodden route. Something is obviously going awry during the implicit conversion, because if we explicitly take care of it, then things are fine.
SQL> select a.c1,
2 ( select max(b.c2) from t2 b where a.c1 = to_number(b.c1) )
3 from t1 a;
C1 (SELECTMAX
---------- ----------
1 t1
1 row selected.
I’d love it if there was an init.ora parameter that could be used in (say) development environments that would error out any SQL statement with an implicit data type conversion, but until that day comes (and it may be never!) then please make sure you take care with your data types!
Happy New Year everyone!
A Christmas Carol
You better watch out,
Let me tell you a fact.
If your SQL has literals,
You’re gonna be hacked.
SQL Injection is comin’ to town
We’ve got a library cache,
It’s memory all linked twice.
But it only works well,
When you’re binding all nice.
SQL Injection is comin’ to town
We know when you’ve been lazy,
And concatenated simple strings.
So just make sure you bind your stuff,
And don’t let the bad guys in.
So… you better watch out,
Let me tell you a fact.
If your SQL has literals,
You’re gonna be hacked.
SQL Injection is comin’ to town
Merry Christmas everyone! 
Your AskTOM Top 10 for 2018
Here we go folks…here are the top 10 most viewed questions in AskTOM this year!
We’ll count them down from 10 to 1
10) Inserting values into a table with ‘&’
Viewed 80,000 times.
First asked in 2012, this actually is not a database issue but a client issue. Many client tools view ampersand as a substitution variable, and this they intercept the execution before the command is sent to the database. Full details here
9) What is a SID, how to change it, how to find out what it is
Viewed 95,000 times.
A timeless classic. This question was asked in June 2000 – only 1 month after AskTOM went live. Given that most of our documentation contains, and most people are aware of the terms “instance” and “database”, I’ll never really understand why we didn’t go with ORACLE_INSTANCE instead of ORACLE_SID. Full details here.
8) How to Update millions or records in a table
Viewed 100,000 times.
Asked back in 2002, I can understand the popularity of this one. Perhaps the most common performance tuning issue is data conversion or data loading. Sometimes you just need to forget the word UPDATE and re-load the data – it can be much faster. Full details here.
7) How to connect SQLPlus without tnsnames.ora
Viewed 110,000 times.
Is it that much of a big deal to have a tnsnames.ora file? I guess so. Nowadays the EZ connect syntax makes connecting to any database trivial. Full details here.
6) How To FULL DB EXPORT/IMPORT
Viewed 130,000 times.
Another understandable one for the top 10 because of the transition from old style export/import to data pump around the timeframe that this question was first asked. There is also the classic chicken-and-egg issue to deal with, namely, that you need a database in order to import, but won’t a full import than clash with all the existing objects? Full details here.
5) Converting CLOBS TO VARCHAR
Viewed 132,000 times.
Once a golden oldie from 2001! It seems an odd proposition – if you have a CLOB, there’s a good chance you have it because it exceeds the allowable size for VARCHAR2. So converting back down seems a risky exercise. Full details here.
4) ORA-12560: TNS:protocol adapter error
Viewed 135,000 times.
Ah yes, not our finest hour this one. Try to use a database on Windows that has not been started, and do you get an error saying “Database not started”? No. You get a crytpic TNS error. Full details here.
3) Format the Number for display
Viewed 143,000 times.
There is such a thing as “too much of a good thing”. And perhaps our incredible flexibility when it comes to formatting data, and the range of format masks is tough for people to swallow. Full details here.
2) IN (vs) EXISTS and NOT IN (vs) NOT EXISTS
Viewed 144,000 times.
Well, there’s a simple reason this gem keeps on coming back year after year after year. For years, books on performance tuning and blogs on performance tuning would claim that EXISTS was better than IN, or vice versa. Amazingly it has never been the case that one was guaranteed to be better than the other, and more alarmingly a straight swap from one to the other can even impact the results you get back. I think we’ll see this guy in the list every year…..forever. Full details here.
And finally we have arrived at the number 1 most viewed question this year. Which begs me to ask the question:
Why do so many of you need to know how many are in each table? 
1) Finding the number of rows in each table by a single sql
Viewed 510,000 times.
Wow. Half a million people need to know how many rows are in their database tables. I’ve no idea why, because the moment you count them, you’re already out of date. Full details here.
And there you … our top 10 for 2018.
Have a great festive season!
It’s all downhill past 30
Yes, it sounds like a lamentation of the aging process, and from my own experience, it is definitely true that the wheels started falling off for my knees, back and plenty of other body parts once I turned 30. But that is perhaps a legacy of too much alcohol, not eating well in my youth and failing to stretch rather than any particular chronological milestone 
.
But this post is not about that. This one is about the magical 30 character limit on identifiers that we finally lifted in version 12c. For static SQL, this is obviously a no impact change – you either define your tables and columns with longer names or you don’t, and the static SQL you write reflects that position.
But dynamic SQL is a different proposition, in particular, this discovery I made with DBMS_SQL recently. DBMS_SQL has a number of package data types that reflect the maximum identifier length in the database, for example:
type desc_rec is record (
col_type binary_integer := 0,
col_max_len binary_integer := 0,
col_name varchar2(32) := '',
col_name_len binary_integer := 0,
col_schema_name varchar2(32) := '',
col_schema_name_len binary_integer := 0,
col_precision binary_integer := 0,
col_scale binary_integer := 0,
col_charsetid binary_integer := 0,
col_charsetform binary_integer := 0,
col_null_ok boolean := TRUE);
For dynamic SQL where we need to determine the column names dynamically with an arbitrary SQL statement, DBMS_SQL uses this data type in it’s API calls. The example below lists out some user names from the database and dynamically derives the column names (even though we obviously know them in advance for this simple example)
SQL> set serverout on format wrapped
SQL> declare
2 c clob := 'select username, created from dba_users';
3 l_cur int := dbms_sql.open_cursor;
4 l_val varchar2(4000);
5 l_status int;
6 l_desc dbms_sql.desc_tab;
7 l_cnt int;
8 l_longcol int := 0;
9 begin
10 dbms_sql.parse(l_cur,c,dbms_sql.native );
11 dbms_sql.describe_columns(l_cur,l_cnt,l_desc);
12 for i in 1 .. l_cnt loop
13 dbms_sql.define_column(l_cur,i,l_val,4000);
14 l_longcol := greatest(l_longcol,length(l_desc(i).col_name));
15 end loop;
16 l_status := dbms_sql.execute(l_cur);
17 while dbms_sql.fetch_rows(l_cur) > 0
18 loop
19 for i in 1 .. l_cnt
20 loop
21 dbms_sql.column_value(l_cur, i,l_val);
22 dbms_output.put_line(
23 rpad(l_desc(i).col_name,l_longcol)||': '||l_val );
24 end loop;
25 dbms_output.new_line;
26 end loop;
27 exception
28 when others then
29 dbms_output.put_line(dbms_utility.format_error_backtrace);
30 raise;
31 end;
32 /
USERNAME: SYS
CREATED : 08-MAR-17
USERNAME: SYSTEM
CREATED : 08-MAR-17
USERNAME: XS$NULL
CREATED : 08-MAR-17
USERNAME: OJVMSYS
CREATED : 08-MAR-17
USERNAME: SYSADMIN
CREATED : 19-JUL-18
It would seem a trivial change to increase the size limit as the version increases from 11g to 12c, but DBMS_SQL is caught between a rock and a hard place here. If you do change the limit, then you risk issues with backward compatibility for customers. If you do not change the limit, then you run into problems like the demo below:
SQL> create table t ( using_my_new_cool_long_column_names_in_12c )
2 as select 1 from dual;
Table created.
SQL>
SQL>
SQL> set serverout on format wrapped
SQL> declare
2 c clob := 'select * from t';
3 l_cur int := dbms_sql.open_cursor;
4 l_val varchar2(4000);
5 l_status int;
6 l_desc dbms_sql.desc_tab;
7 l_cnt int;
8 l_longcol int := 0;
9 begin
10 dbms_sql.parse(l_cur,c,dbms_sql.native );
11 dbms_sql.describe_columns(l_cur,l_cnt,l_desc);
12 for i in 1 .. l_cnt loop
13 dbms_sql.define_column(l_cur,i,l_val,4000);
14 l_longcol := greatest(l_longcol,length(l_desc(i).col_name));
15 end loop;
16 l_status := dbms_sql.execute(l_cur);
17 while dbms_sql.fetch_rows(l_cur) > 0
18 loop
19 for i in 1 .. l_cnt
20 loop
21 dbms_sql.column_value(l_cur, i,l_val);
22 dbms_output.put_line(
23 rpad(l_desc(i).col_name,l_longcol)||': '||l_val );
24 end loop;
25 dbms_output.new_line;
26 end loop;
27 exception
28 when others then
29 dbms_output.put_line(dbms_utility.format_error_backtrace);
30 raise;
31 end;
32 /
ORA-06512: at "SYS.DBMS_SQL", line 2084
ORA-06512: at line 11
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 30
ORA-06512: at "SYS.DBMS_SQL", line 2084
ORA-06512: at line 11
The solution here is a simple one. The existing definitions in DBMS_SQL were left unchanged to preserve that backward compatibility, and additional structures were added to handle longer column names. We need simply alter our code to use the new “desc_tab2” data type and it’s accompanying “describe_columns2” call.
SQL> declare
2 c clob := 'select * from t';
3 l_cur int := dbms_sql.open_cursor;
4 l_val varchar2(4000);
5 l_status int;
6 l_desc dbms_sql.desc_tab2;
7 l_cnt int;
8 l_longcol int := 0;
9 begin
10 dbms_sql.parse(l_cur,c,dbms_sql.native );
11 dbms_sql.describe_columns2(l_cur,l_cnt,l_desc);
12 for i in 1 .. l_cnt loop
13 dbms_sql.define_column(l_cur,i,l_val,4000);
14 l_longcol := greatest(l_longcol,length(l_desc(i).col_name));
15 end loop;
16 l_status := dbms_sql.execute(l_cur);
17 while dbms_sql.fetch_rows(l_cur) > 0
18 loop
19 for i in 1 .. l_cnt
20 loop
21 dbms_sql.column_value(l_cur, i,l_val);
22 dbms_output.put_line(
23 rpad(l_desc(i).col_name,l_longcol)||': '||l_val );
24 end loop;
25 dbms_output.new_line;
26 end loop;
27 exception
28 when others then
29 dbms_output.put_line(dbms_utility.format_error_backtrace);
30 raise;
31 end;
32 /
USING_MY_NEW_COOL_LONG_COLUMN_NAMES_IN_12C: 1
PL/SQL procedure successfully completed.
In fact, a column name could always be more than 32 characters because an un-aliased column expression can yield column names of almost arbitrary length, so these additions were made several releases ago, but 12c had brought that issue to the fore because even “normal” column names can now exceed the previous limit.
Now if I only I could add a “2” to the end of my aging body to get to handle years up to 128 just like the column names in 12c
Automatic sequences not being dropped
One of the nice new things in 12c was the concept of identity columns. In terms of the functionality they provide (an automatic number default) it is really no different from anything we’ve had for years in the database via sequences, but native support for the declarative syntax makes migration from other database platforms a lot easier.
Under the covers, identity columns are implemented as sequences. This makes a lot of sense – why invent a new piece of functionality when you can exploit something that already has been tried and tested exhaustively for 20 years? So when you create a table with an identity column, you’ll see the appearance of a system named sequence to support it.
Another nice thing about identity columns is that if you drop the table, the underlying sequence that supports the column is also automatically dropped….. (cue ominous music…) or is it? I had this demo come my way via email asking that exact question:
SQL> create table t1 ( x int generated always as identity );
Table created.
SQL> create table t2 ( x int generated by default as identity );
Table created.
SQL> select table_name, sequence_name
2 from user_tab_identity_cols;
TABLE_NAME SEQUENCE_NAME
-------------------- --------------------
T1 ISEQ$$_139912
T2 ISEQ$$_139914
6 rows selected.
SQL> drop table t1;
Table dropped.
SQL> drop table t2;
Table dropped.
SQL> select table_name, sequence_name
2 from user_tab_identity_cols;
no rows selected
SQL> select sequence_name from user_sequences;
SEQUENCE_NAME
--------------------
ISEQ$$_139912
ISEQ$$_139914
Is that a bug? Why are the sequences still there?
There is no need to panic – there is a very simple explanation to this. From 10g onwards, by default, when you drop a table we do not actually drop it (ie, release the data and the space it consumed). We simply rename it, and it becomes a member of the recycle bin. This is a nifty insurance policy against losing data inadvertently. The table “disappears” from standard dictionary views but the sequences remain which is what causes the confusion. We don’t destroy the sequences because recreating them if the table is brought back from the recycle bin would require knowing what the current high watermark for the sequence value would need to be.
Purging the recycle bin brings everything back into alignment.
SQL> purge recyclebin;
Recyclebin purged.
SQL> select sequence_name from user_sequences;
no rows selected
The phantom tablespace
(Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL?
OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick 
I’ve created a partitioned table called “T” (I’ll pause here for your applause at my incredible imagination skills for table naming ) and to show you the complete DDL, I’ll extract it using the familiar DBMS_METADATA package.
SQL> select dbms_metadata.get_ddl('TABLE','T','SCOTT') x from dual
X
-------------------------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "X" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO"
PARTITION BY LIST ("X")
(PARTITION "P1" VALUES (1)
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE (
INITIAL 8388608
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P2" VALUES (2)
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE (
INITIAL 8388608
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "LARGETS" )
With a little colour coding, you can see that there are three tablespaces that pertain to this table:
- DEMO
- USERS
- LARGETS
But look what happens when I query the data dictionary for those tablespaces:
SQL> select tablespace_name
2 from dba_tablespaces
3 where tablespace_name in ('DEMO','USERS','LARGETS');
TABLESPACE_NAME
------------------------
LARGETS
USERS
Where is DEMO? Where has it gone? More startlingly, how can I have an existing table that needs that tablespace, and yet the tablespace is not present in the database? Have I lost data? Is there corruption?
Fortunately, the answer to all of these questions do not involve data loss and/or corruption. It is a quirk of the syntax that can be used for partitioned tables. Here is the DDL as I wrote it for the table T.
SQL> create table t ( x int ) tablespace demo
2 partition by list ( x )
3 ( partition p1 values (1) tablespace users,
4 partition p2 values (2) tablespace largets
5 );
Table created.
And immediately after I created the table, I did the following
SQL> drop tablespace demo including contents and datafiles;
Tablespace dropped.
You might be thinking that such an operation would surely drop the table I just created as well, but it is still here just fine.
SQL> desc t
Name Null? Type
----------------------------------------------------------------- -------- ---------------
X NUMBER(38)
The specification of the tablespace at table level for a partitioned table is nominating the default tablespace for each partition in case it is not specified at partition level. Because I specified a tablespace explicitly for the two partitions on T, the tablespace DEMO does not contain any data, or any partitions for that matter. Which is why I was able to drop it without any problems. Compounding the confusion that often arises is that you won’t see the tablespace name DEMO listed in the USER_TABLES dictionary view even if I had not dropped the tablespace.
SQL> select tablespace_name from user_tables
2 where table_name = 'T';
TABLESPACE_NAME
------------------------
(null)
The tablespace name in USER_TABLES nominates the tablespace for the segment that will be associated with this table. You will see a similar null value in this column when the table is an Index Organized Table, because it is the underlying index that maps to a tablespace, not the table definition. For a partitioned table, to see which tablespace is the default tablespace, you need to query the USER_PART_TABLES dictionary view.
SQL> select def_tablespace_name
2 from user_part_tables
3 where table_name = 'T';
DEF_TABLESPACE_NAME
------------------------------
DEMO
Besides this being some trickery with tablespace definitions, there is a good reason why you should know about the segments tablespace and the default tablespace for partitioned objects. As it stands, I could run a datapump export command on the table T and it will successfully be unloaded to a data pump file. However, if I attempt to run a data pump import, the creation of the table will fail, because of the (now illegal) reference to the DEMO tablespace.
So if you are planning to run a data pump export, here’s a query I whipped up to run a check against your database to ensure that you don’t have any references to tablespaces that no longer exist in your database.
SQL> with all_possible_ts as
2 (
3 select tablespace_name from dba_lobs union all
4 select tablespace_name from dba_clusters union all
5 select tablespace_name from dba_indexes union all
6 select tablespace_name from dba_rollback_segs union all
7 select tablespace_name from dba_tables union all
8 select tablespace_name from dba_object_tables union all
9 select def_tablespace_name from dba_part_tables union all
10 select def_tablespace_name from dba_part_indexes union all
11 select tablespace_name from dba_tab_partitions union all
12 select tablespace_name from dba_ind_partitions union all
13 select tablespace_name from dba_tab_subpartitions union all
14 select tablespace_name from dba_ind_subpartitions union all
15 select def_tablespace_name from dba_part_lobs union all
16 select tablespace_name from dba_lob_partitions union all
17 select tablespace_name from dba_lob_subpartitions union all
18 select tablespace_name from dba_subpartition_templates union all
19 select tablespace_name from dba_lob_templates union all
20 select tablespace_name from dba_segments union all
21 select tablespace_name from dba_extents union all
22 select tablespace_name from dba_undo_extents
23 )
24 select tablespace_name from all_possible_ts
25 minus
26 select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
--------------------
DEMO
IRR function in PLSQL
We had an AskTOM question recently about how to calculate the IRR, ie, the Internal Rate of Return. To be honest, I had not really heard of the function, but readers were quick to point out to me that it was a commonly used function in the most “popular” database on earth, namely Microsoft Excel 
It turns out that it is an iterative function, ie, there is no equation that calculates it directly – you start with an opening guess and then iterate until each guess gets closer to the answer within an accepted tolerance. A little research took me on a nice trip down memory lane to my university (college) days of a maths major where we learned the Newton Raphson method (although apparently it is more commonly referred to as Newton’s method).
So a little PL/SQL later, I’ve re-embraced my youthful mathematical roots. If only we could iterate like this function back to our youth as well
Here is some test data that we can use
SQL> create table test_tbl
2 (
3 id number,
4 cash_flow number
5 );
Table created.
SQL>
SQL> insert into test_tbl (id,cash_flow) values (0,-6000000);
SQL> insert into test_tbl (id,cash_flow) values (1,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (2,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (3,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (4,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (5,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (6,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (7,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (8,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (9,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (10,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (11,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (12,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (13,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (14,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (15,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (16,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (17,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (18,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (19,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (20,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (21,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (22,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (23,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (24,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (25,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (26,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (27,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (28,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (29,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (30,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (31,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (32,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (33,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (34,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (35,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (36,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (37,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (38,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (39,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (40,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (41,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (42,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (43,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (44,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (45,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (46,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (47,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (48,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (49,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (50,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (51,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (52,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (53,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (54,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (55,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (56,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (57,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (58,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (59,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (60,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (61,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (62,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (63,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (64,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (65,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (66,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (67,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (68,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (69,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (70,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (71,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (72,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (73,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (74,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (75,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (76,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (77,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (78,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (79,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (80,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (81,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (82,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (83,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (84,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (85,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (86,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (87,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (88,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (89,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (90,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (91,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (92,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (93,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (94,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (95,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (96,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (97,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (98,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (99,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (100,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (101,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (102,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (103,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (104,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (105,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (106,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (107,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (108,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (109,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (110,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (111,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (112,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (113,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (114,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (115,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (116,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (117,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (118,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (119,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (120,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (121,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (122,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (123,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (124,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (125,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (126,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (127,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (128,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (129,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (130,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (131,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (132,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (133,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (134,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (135,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (136,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (137,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (138,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (139,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (140,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (141,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (142,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (143,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (144,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (145,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (146,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (147,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (148,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (149,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (150,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (151,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (152,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (153,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (154,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (155,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (156,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (157,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (158,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (159,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (160,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (161,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (162,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (163,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (164,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (165,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (166,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (167,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (168,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (169,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (170,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (171,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (172,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (173,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (174,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (175,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (176,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (177,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (178,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (179,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (180,54802.38);
And here is the function using Newton’s method. You can see in line 15, that we iterate until we get within a nominated threshold. I could code the function just to read my test data above, but that does not make it very practical for an arbitrary set of data. So I’ve parameterised the function to take a CURSOR variable so that any resultset can be passed to it. As long as the data is pass in the correct order, the function should operate just fine.
Warning: Because this is a function that iterates until a threshold is reached, if you send in garbage data it is quite possible that the function will either never return, or crash with a numeric overflow error. If you are planning on using the code below, you may want to add some sanity checks to “harden” it for production usage.
SQL> create or replace
2 function irr(rc sys_refcursor) return number is
3 type nlist is table of number index by pls_integer;
4 l_values nlist;
5
6 l_threshold number := 0.005;
7 l_guess number := l_threshold + 1;
8 l_next_guess number := 2;
9 l_irr number := 1;
10
11 begin
12 fetch rc bulk collect into l_values;
13 close rc;
14
15 while abs(l_guess) > l_threshold
16 loop
17 l_guess := 0;
18 l_next_guess := 0;
19 for i in 1 .. l_values.count
20 loop
21 l_guess := l_guess + l_values(i)/power(1+l_irr/100, i-1);
22 l_next_guess := l_next_guess + -i*l_values(i)/power(1+l_irr/100, i-1);
23 end loop;
24 l_irr := l_irr - l_guess/l_next_guess;
25
26 --dbms_output.put_line('l_irr='||l_irr);
27 --dbms_output.put_line('l_guess='||l_guess);
28 --dbms_output.put_line('l_next_guess='||l_next_guess);
29 end loop;
30 return l_irr;
31 end;
32 /
Function created.
And let’s give it a test.
SQL>
SQL> select irr(cursor(select cash_flow from test_tbl order by id asc )) irr
2 from dual;
IRR
----------
.490408759
1 row selected.
SQL>
