DBA Blogs

ORA-06512 - PL/SQL: numeric or value error%s

Tom Kyte - 6 hours 6 min ago
Apologies... this is related to a previous question answered earlier today, but I did not know how to ask a 'follow up' question... I've written the code below to generate an email report on users in an instance which have not been logged into i...
Categories: DBA Blogs

Days since last login

Tom Kyte - Fri, 2020-05-29 17:46
I have some code (see below) which tells me the username and last_login for any login not used in the last 15 days. I would like to add a 3rd column to the results, which shows the number of days sine the last login, to make it easier for the perso...
Categories: DBA Blogs

Guidance on table design for batch processing

Tom Kyte - Fri, 2020-05-29 17:46
Hi Chris/Connor, UseCase --------- We need to design a table which will be used for batch processing for about 2.5M transactions at peak load. This table will be used for processing 100k in a batch and once the processing is complete we will be...
Categories: DBA Blogs

Bloom filters

Tom Kyte - Fri, 2020-05-29 17:46
Hi Tom, What are bloom filters? Is it new in 11g? How does it improve performance? How can I check, if bloom filter is happening in the DB or not? Regards, Ashish
Categories: DBA Blogs

OATUG Forum Online (or, the 2020 Version of Collaborate Las Vegas)

Pythian Group - Fri, 2020-05-29 08:58

Hi everyone!

It’s been a very busy year for me, so I haven’t had much time to blog. But, here we go!

This isn’t a technical post, but more of an opinion piece. I’ve split this post into two parts. Part one was written before I spoke at the OATUG (Oracle Applications and Technology Users Group) Forum online. Part two was written after I spoke.

Part 1: Before…

The global pandemic has disrupted virtually everything in our world. The same goes for the world of IT, including one of the best things about our industry: Conferences and live events.

I was supposed to attend two major conferences in the first quarter of 2020. One was cancelled and the other became an online event. Aborting major events like these is no trivial undertaking. Not only for the organizers themselves but also for all the sponsors and attendees (customers and vendors included).

Huge events like Oracle Open World, Google Next, or the one that will be the focus of most of this post, Collaborate, have a long-lasting impact that extends throughout the year. At these events, a casual meeting or a chat during a coffee break can kickstart a relationship. It might develop into a large services agreement, a partnership, or a whole new successful startup.

You have to agree that these events are important, if not crucial, for our industry.


As for Collaborate, well, I was very excited to not only attend but also speak at the event. After attending DOAG, BIWA, and UKOUG the previous years, Collaborate is the next big Oracle-related event in which I’d be participating. Then, of course, came COVID-19.

However, Collaborate isn’t cancelled. Instead, it’s now a fully virtual event. Here I’d like to give a massive KUDOS to the event organizers for their commitment to the event and the attendees. It certainly couldn’t have been easy to make the initial decision to proceed with Collaborate. Nor could it have been easy to completely undo and transform months and months of planning and logistics into a fully virtual conference in a matter of weeks.

I was very pleased to still be selected to speak at the (now) online conference. And, a big thanks to Pythian for their speaker program. It enables me to attend these gatherings, virtual or otherwise.

So, here I am, a few hours prior to my first big online presentation to an unknown number of people, and crossing my fingers that everything works as expected. This is basically how I feel before any “typical” presentation. The main difference? Well, the presentation won’t be quite the same. Interaction with the listening public is close to zero, and there is little practical opportunity for people to reach out to you with questions afterwards.

My Thoughts…

I know, I know… there’s always social media, and so on. However, it’s not the same, and you know it. There’s no immediate feedback from the attendees, and you can’t really tell if they find the content interesting or boring. Nor is it easy to tell whether you should slow down, or speed up. It definitely feels more impersonal.

This is my opinion from a “presenter” standpoint. As an “attendee,” there are two major issues for me: One is that it’s no longer a live in-person event, it’s awkward to ask your manager for time to attend it. The second big issue for me is the time zone. I’m in the Eastern time zone, and the event is based in Las Vegas, so sessions can go all the way from 4:30 PM to 10:30 PM EDT. This, on top of my regular eight-hour workday, is simply too much.

So, I might be able to attend a session or two and watch saved sessions at a later time. This takes me back to my previous point about lost face-to-face interactions with people.

Anyway, I’m really interested to see how this plays out. I really hope the event is a big success because, unfortunately, our current “situation” might last a long time. We need to prepare for that possibility.

Part 2: After…

And… the event is over. Unfortunately, it wasn’t very satisfactory, which makes me a little sad. You see, Collaborate is one of the biggest Oracle events that I know of with a great many attendees, but only four showed up to my live presentation. Yes, the feedback was very good, and surely those people who attended were very interested in the topic I was speaking about. I thank each and every one of them.

A few days later, I checked my presentation statistics and there were a few views and downloads. But overall, the number is far below the 20 to 50 people that attended this same presentation at other on-site conferences.

Of course, I’m not a renowned speaker and the subject matter might not be of broader interest. However, the online experience is just “not the same,” and that takes a toll on the number of attendees.

Regardless, it was a good overall experience. I give my sincere congratulations to the organizers for the huge effort they clearly put into the entire event.

Hopefully, we can all meet in person soon and enjoy a truly magnificent Collaborate.

Categories: DBA Blogs

Oracle Cloud is Rich with Spatial

Pakistan's First Oracle Blog - Fri, 2020-05-29 03:47
Spatial is the technology of future. Massive amount of data will be generated, cleansed and stored. Oracle's autonomous databases in Oracle's cloud are ready to take on the challenge with a bang with a solid database offering, which is time tested.

Machine learning and graph are just the elementary building blocks of this spatial offering from Oracle.

Oracle Database includes native spatial data support, rich location query and analysis, native geocoding and routing, and map visualization, to support location-enabled business intelligence applications and services.With a network data model, raster and gridded data analysis, 3D and point cloud operations, a location tracking server, and topology management, Oracle Database provides a comprehensive platform for GIS.

It’s easy for developers to add spatial capabilties to applications – with standards-based SQL and Java APIs, JSON and REST support, and integration with Database tools, Oracle Business Intelligence, and Applications. With dramatically fast spatial index and query performance, Exadata integration, and support for Database features such as partitioning, security, distributed transactions, and sharding,

Oracle Database powers the most demanding, large scale geospatial applications – from cloud-based location services to transportation, utilities, agriculture, asset managmeent, LiDAR analysis, energy and natural resouces, and planning.

With 12.2, Oracle continues to deliver the most advanced spatial and graph database platform for applications from geospatial and location services to Internet of Things, social media, and big data.

Categories: DBA Blogs

Data Encryption in Oracle Cloud

Pakistan's First Oracle Blog - Fri, 2020-05-29 03:40
World's leading financial institutions run their mission critical databases on Oracle and the biggest concerns they have around moving their database to cloud is encryption in transit and at rest.
Oracle TDE prevents attacks from users attempting to read sensitive data from tablespace files and users attempting to read information from acquired disks or back ups by denying access to clear text data. Oracle TDE technology uses two-tier encryption key architecture to enforce clear separation of keys from encrypted data. The encryption keys for this feature are all managed by Oracle TDE. The encryption algorithm used is AES128.

Redaction is the process of censoring or obscuring part of a text for legal or security purposes. The Data Redaction feature redacts customer data in Responsys to obfuscate consumers' Personally Identifiable Information (PII) from Responsys users. 

For example, Responsys accounts may want to redact customer data such as Email Addresses and Mobile Phone Numbers in the profile list to ensure customer data is hidden from Responsys end users. Data redaction ensures that Responsys accounts are compliant with data protection regulations to keep consumers' PII or medical records (for HIPAA compliance) confidential.

It is imperative that you test your database migrations to cloud with these redaction techniques and your well architecture review must include these use cases.

Oracle has implemented a “ubiquitous encryption” program with the goal of encrypting all data, everywhere, always. For customer tenant data, we use encryption both at-rest and in-transit. The Block Volumes and Object Storage services enable at-rest data encryption by default, by using the Advanced Encryption Standard (AES) algorithm with 256-bit encryption. In-transit control plane data is encrypted by using Transport Layer Security (TLS) 1.2 or later.
Categories: DBA Blogs

MLOps and Data Mining in Oracle 19c

Pakistan's First Oracle Blog - Fri, 2020-05-29 03:34
Machine learning operations or aka MLOps is getting quick traction even in database arena and Oracle is not behind. They are heavily using it in their data mining techniques and introducing new alogs and other frameworks.

Data mining is a technique that discovers previously unknown relationships in data. Data mining is the practice of automatically searching large stores of data to discover patterns and trends that go beyond simple analysis. Data mining uses sophisticated mathematical algorithms to segment the data and to predict the likelihood of future events based on past events. Data mining is also known as Knowledge Discovery in Data (KDD).

This is especially very much pertinent when it comes to OLAP. On-Line Analytical Processing (OLAP) can be defined as fast analysis of multidimensional data. OLAP and data mining are different but complementary activities. Data mining and OLAP can be integrated in a number of ways. OLAP can be used to analyze data mining results at different levels of granularity. Data Mining can help you construct more interesting and useful cubes.

Data mining does not automatically discover information without guidance. The patterns you find through data mining are very different depending on how you formulate the problem. Each data mining model is produced by a specific algorithm. Some data mining problems can best be solved by using more than one algorithm. This necessitates the development of more than one model. For example, you might first use a feature extraction model to create an optimized set of predictors, then a classification model to make a prediction on the results.

In Oracle Data Mining, scoring is performed by SQL language functions. Understand the different ways involved in SQL function scoring. Oracle Data Mining supports attributes in nested columns. A transactional table can be cast as a nested column and included in a table of single-record case data. Similarly, star schemas can be cast as nested columns. With nested data transformations, Oracle Data Mining can effectively mine data originating from multiple sources and configurations.

Categories: DBA Blogs

CPU Conundrum in Oracle 19c

Pakistan's First Oracle Blog - Fri, 2020-05-29 03:29
If you have managed Oracle databases on any kind of hardware, you know how important the CPU is for the optimal performance of database. Over the years, Oracle has tried hard to come up with efficient strategies to make sure that CPU is utilized efficiently or caged properly.

Configuring the machine to optimally leverage CPU is a big ask. Normally onn top of the Bare Metal or Virtual instances, each instance of an Oracle database is configured to use a number of vCPUs by enabling Oracle Database Resource Manager (DBRM) and setting the CPU_COUNT parameter. If DBRM is not configured, the CPU_COUNT setting simply reflects the total vCPUs on the system. Enabling DBRM allows the CPU_COUNT setting to control the number of vCPUs available to the database. This applies at both the CDB (Container Database) and PDB (Pluggable Database) levels.

The most common approach to managing CPU resources is to NOT over-provision and simply allocate CPU according to what’s available. Whether CPU is allocated to Virtual Machines that each contain a single database, or CPU is allocated to individual databases residing on a single Virtual Machine, the result is the same.

Oracle offers the ability to configure “shares” for each Pluggable Database within a Container Database.  Each instance of a Container Database is given an amount of vCPU to use by enabling DBRM and setting CPU_COUNT. The Pluggable Databases within that Container Database are then given “shares” of the vCPU available to the Container Database. Each Pluggable Database then receives the designated share of CPU resources, and the system is not over-subscribed.

DBRM constantly monitors demand for CPU resources within each Pluggable Database, as well as the overall availability of CPU resources at the Container Database level. DBRM allows each Pluggable Database to automatically and immediately scale up to use more CPU resources if available in the Container Database. The ability to use Dynamic CPU Scaling is a new feature of Oracle Database 19c that allows Pluggable Databases to automatically scale CPU resources up and down in response to user demand.

Categories: DBA Blogs

Converged Oracle

Pakistan's First Oracle Blog - Fri, 2020-05-29 03:25
Its very interesting to note down that where some database providers especially in the cloud are going about diverging the database offering for various use cases, whereas Oracle is talking about converged or unified databases for various on-prem and Cloud use-cases.

Converged databases support Spatial data for location awareness, JSON for document stores, IoT for device integration, in-memory technologies for real-time analytics, and of course, traditional relational data.

Mainly these so-called converged databases are aimed at supporting mixed work loads or keeping your data at one location for disparate applications. You won't have to worry abotu managing and more importantly integrating different systems. You will synergize everything into one.

Now that looks great in theory but we have seen adverse impacts when you try to combine things such as OLTP and DWH or graph databases with spatial and so on. The marriage of these different uses cases might become a performance nightmare if not handled rightly.

Oracle Database at core is a good manifestation of a converged database, as it provides support for Machine Learning, Blockchain, Graph, Spatial, JSON, REST, Events, Editions, and IoT Streaming as part of the core database
Categories: DBA Blogs

Adding an optional json array to a json object

Tom Kyte - Thu, 2020-05-28 23:26
I have a parent table and child table with a 1 to 0-many relationship. I need to create a json object that includes parent data and a list of child table data. The goal is an object that includes: <b>"child":[]</b> when there is no matching data in ...
Categories: DBA Blogs

multi-table insert

Tom Kyte - Thu, 2020-05-28 05:06
Tom: I was interested in using the multi-table insert feature in 9i but am stumped by the restriction that the subquery can't contain a sequence. I use a sequence to assign a Primary Key in one of the tables that is the target of the multi-table i...
Categories: DBA Blogs

How to find the begining of path and end of path

Tom Kyte - Thu, 2020-05-28 05:06
Hi, I would like to know if there is anyway it return the beginning of the hierarchy and end of hierarchy for the code below. <code> WITH o AS ( SELECT 'A' as obj, 'C' as link FROM dual UNION ALL SELECT 'C', ...
Categories: DBA Blogs

bulk operation on multilevel collection -

Tom Kyte - Thu, 2020-05-28 05:06
Example 3-22 Using UPDATE to Insert an Entire Multilevel Collection INSERT INTO region_tab (region_id, region_name) VALUES(2, 'Americas'); DECLARE v_country nt_country_typ; BEGIN v_country := nt_country_typ( country_typ( 'US', 'Uni...
Categories: DBA Blogs

Audit records no longer being captured

Tom Kyte - Thu, 2020-05-28 05:06
Hi all, I want to ask about auditing database. So I have a problem like this. I have a table that I have audited. Example we called it table x. Audit data in table X is recorded until the 14th. However, on the 15th onwards, no audit data is record...
Categories: DBA Blogs

Difference Between IMPDP Method

Tom Kyte - Thu, 2020-05-28 05:06
Hello Team a while ago, I had a problem with a DB import. I managed to get it, but there's a question still remain in my head 1. what is the difference between <code> expdp system/system schemas=ieulive directory=dir1 dumpfile=dump1.dmp logfi...
Categories: DBA Blogs

Listagg Vs Stragg

Tom Kyte - Wed, 2020-05-27 10:46
Tom: I was benchmarking stragg with listagg and results below, is that stragg is better than listagg? Please let me know your thoughts on this. <code>drop table t purge; create table t as select * from ( select * from all_objects ...
Categories: DBA Blogs

Difference between select * and select a.* with alias on a table

Tom Kyte - Wed, 2020-05-27 10:46
Hi Tom, What is the difference between using "select * from table1" and "selelct a.* from table1 a". Observed that while doing the select statement "select a.* from table1 a" locks the data for update (like if we use the statement from PLSQL dev...
Categories: DBA Blogs

BLOB Columns In tables

Tom Kyte - Wed, 2020-05-27 10:46
Hi Tom, This is a general question about the design of the tables in a DAILY use database. The users will uploading and downloading pdf files from a web app daily. What i want to ask is it a good idea to create my tables with BLOB columns and savi...
Categories: DBA Blogs

CLOB to XMLTYPE out of memory issue

Tom Kyte - Wed, 2020-05-27 10:46
I am copying XML data from Operating System file into CLOB and then casting as XMLTYPE for extracting information into relational database. This solution is working for years but I received a business requirement to process big XML files ranging fro...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs