Oracle

Epoch time is common in some situations because it is easier to do date calculations than with normal dates. The most popular is UNIX Epoch time which is the number of seconds since the start of January 1, 1970. If you have your data in Oracle you don’t have to worry about date arithmetic, since Oracle handles that for you. So in case you have such a table with epoch time and want to query it with functions that expect the DATE datatype, you can add a virtual column that returns the epoch time converted to DATE like this: ...

Oracle Developer Community Most of my work in the Oracle world has been DBA-oriented. But for the last years, I have rediscovered the joy of development and data analysis. When I haven’t been writing the code myself, I have often helped other developers when they connect to the Oracle database, and their program does not perform as expected. Developers coding in Java, Python, or most other programming languages spend time on figuring out how to get the work done. They learn algorithms to discover the fastest way to do it, or the lazy coders just punch out code without wondering too much about efficiency. If their test data is small, performance problems usually get detected after release to production with ensuing frustrations. ...

I’m writing this post so it can be found by googlers struggling with ORA-01861. Usually, ORA-01861 means that you missed something when using a date conversion function. But, here the other day I had created a table using some code generation tool, and I had accidentally declared a column with datatype DATE instead of NUMBER. When you are using the wrong datatype in SQL, Oracle tries to convert it for you. If you try to insert letters into a DATE column, Oracle will return error message ORA-01858 - a non-numeric character was found where a numeric was expected, if you try to insert a number into it, you’ll get ORA-00932 - inconsistent datatypes: expected DATE got NUMBER. But if you insert a number in quotes, and the string is in the right format, Oracle may succeed. ORA-01861 comes when you submit a number in quotes, but is not a complete date. ...

In an attempt to compare how long it will take to build a Docker image of Oracle XE (11.2) vs EE (12.2) on my Mac I ran into a problem. The building of XE failed, it complained that only 1023 MB swap space was available. I thought that adding swap during build with this workaround would do, but it turns out that with Docker things are different. I found suggestions here , but since I was not reading carefully enough, I missed the point that you add swap to the hypervisor Docker for Mac is running on. This is what I did to solve it. You connect to the hypervisor with: ...

Queries from monitoring software like BMC are not supposed to be among Top SQL, but that was happening in many databases at one site. This may happen if the dictionary statistics are not updated, leading Oracle to choose the wrong plan. Document with ID 1328789.1 on MOS shows an example of this. So the SQL that came from BMC sometimes took up to a minute and was executed every 5 minutes, easily making it to the Top SQL list in Enterprise Manager / Cloud Control. This is the SQL: ...

It is probably the developers’ responsibility to run tests, but they may not be aware of features in the database that makes the testing easier. Here the other day an ETL developer asked for help with resetting tables after ETL-testing. But there was no need to create complicated scripts to do so. I showed him flashback tables so he could easily fix it himself and run the tests over and over again without any pain. ...

A colleague said that he had some tables that showed up in one tool, but not in another, I think it was Enterprise Manager/Cloud Control. This is easy to forget if you don’t work with non-relational tables regularly. What happens is that only relational tables are listed in DBA_TABLES or USER_TABLES, but all tables can be found if you search DBA_OBJECTS / USER_OBJECTS for objects with OBJECT_TYPE=TABLE. The documentation actually says: USER_TABLES describes the relational tables owned by the current user. You can try this code to demonstrate it: ...

Have you been in a presentation with the feeling that the speaker is talking over your head? As if he is speaking to a select group, maybe trying to impress them? The selection of words and expressions, subject for the talk may sometimes give a feeling of cleverness, yet you can’t relate to it. Instead you walk away thinking you are not remotely smart enough to give a presentation. ...

Here the other day I was helping my son with his homework. His task was to create a simple data model, create it in MySQL, and make a simple web page to access the data. I thought that after creating a few INSERT statements on his own I could help him with demo data. Since he had decided to create a movie database I googled for some movie data sets and decided to use the IMDB 5000. You can download it from Kaggle.com (free registration probably required). ...

If you need to delete all rows in a table that has parent keys for other tables’ foreign keys, and the foreign keys constraints have not been defined with “on delete cascade”, you can do a recursive delete with the following simple procedure. This is typically something you will do only in a test or development database, and not in production. As always, it is a good thing to understand this procedure before you execute it: ...