A few days back I posted a video how Unified Auditing is now the only support auditing option in 23c, and gave you some easy steps on how to migrate your existing traditional audit settings to the equivalent audit policies under Unified Audit. As the video also reassures – when you upgrade your database, your existing traditional audit command will still continue to work in 23c, but any attempt to change them or add new ones will be blocked. A few people reached out to me with a concern about this message, namely: “What if we are not using an upgrade strategy? What if we plan to simply DataPu ..read more

Yeah, it was a hideous bug-ridden piece of junk, and best thing was … it didn’t really matter. First let me explain what led to my terrible app. Good mate Dom Giles posted this message on our internal Slack channel the other day. “I need to give myself a goal, and I noticed that Cancer Research UK is doing the 100 press-ups a day challenge for April. So doing this will benefit others as I fail hopelessly at it. But I’d also like to drag as many people into this wretched cause in the hope they will succeed when I don’t. I’m looking for other volunteers to commit to the challenge. So who’s up fo ..read more

On my last Office Hours session, I handled a question from someone comparing Oracle to SQL Server, and they pointed out (paraphrasing) “SQL Server has a ton of date related functions to make dealing with dates or date components easy. How come Oracle doesn’t do that?” That’s probably a fair point, because a quick look through the SQL Server docs shows a plethora of functions related to date and date composition, with some of these presented below Pretty much all of these can be performed with standard Oracle functionality using TO_NUMBER, TO_CHAR, TO_DATE functions and the like, but sometimes ..read more

At the start of the year, Twitter become all a flutter when people started posting their results for the 1 billion row challenge. The challenge was thrown out to the community as an exercise in Java processing (because as we know, people love dumping on Java performance) . My Java interest (and skills) are low so I only had a passing interest in the challenge, besides seeing the results that people been posting. But when it comes to billions of rows, then besides doing benchmarks and the like, then typically the best place for storing data like that is … in a database! So I posted a quick tw ..read more

The internet heralded a very common evolution in databases. You started with a database that backed a physical store front, or the data was created/entered by human beings who sat in a location near to the data centre because that was governed by the network technology of the time. Even so, you had a lot of data (or at least, what was considered a lot of data for the time) and so you might opt to partition your tables by date. SQL> create table par_ts 2 ( 3 a number, 4 b number, 5 c date 6 ) 7 partition by range(c) 8 interval (numtoyminterval(1,'MONT ..read more

If you’re a command line dinosaur like me, maybe you’d still like to occasionally step out of the dark ages and have your output in a nice colour. Maybe in the query below, I’d like some highlighting when the hiredate is (say) more recent than 1982. Well, if you are really keen, then you can add the following functions to your database, and then it is easy! (well, sortta easy ) SQL> create or replace 2 function Black(p_str varchar2) return varchar2 is 3 begin return chr(27)||'[30m'||p_str||chr(27)||'[0m'; 4 end; 5 / Function created. SQL> create or replace 2 function ..read more

This is surely one of the most debated topics that has existed in SQL forums for the past 30 years., and you’re probably thinking “Surely this one has been done to death! Surely there is nothing more to day” which of course compels me to reference one of my all time favourite movies Man oh man did we burn some internet bandwidth and brain cycles on this topic. A quick google search comes up with a plethora of links, none of which are probably new to many of you https://www.linkedin.com/pulse/never-use-count-your-sql-count1-instead-myth-michael-papadopoulos https://stackoverflow.com/question ..read more

A post on reddit created the usual reddit storm the other day due to its title line: and whenever you make a claim on reddit (even though I think the original poster was actually posing this as a request for clarification), then rest assured there is going to be avalanche of people jumping into the fray to aggressively push their view . “If truncate was DML it would be transaction safe. Sounds like DDL to me” “Truncate is not changing any structure, just data, so I’d put it solidly in the DML bucket.” “TRUNCATE TABLE does implicitly commit the current transaction which is consistent with DDL ..read more

This blog post is a slight departure from my normal Oracle content. As some of you know may know, I also host a YouTube channel with over 700  tech videos on the Oracle Database. Our corporate editing tool is Camtasia, and as anyone that edits video knows, no matter how powerful your machine, video editing can bog your machine down to a crawl, especially if you’re dealing with large video files (eg if you have recorded an hour long webinar). Here’s a quick tip to help the editing process. On each large video in your Media bin, right-click and choose “Create Proxy” (and then go grab a coff ..read more

I had a customer ask me recently why XMLTYPE is disallowed on Autonomous Database. This surprised me, because I had not heard anything along those lines. But they sent me the following test case, where they simply extracted the DDL from their existing on-premises database and could not run it on Autonomous. SQL> create table some_xml 2 (pk number(5) primary key, 3 xml_col xmltype) 4 xmltype xml_col store as basicfile clob; create table some_xml * ERROR at line 1: ORA-01031: insufficient privileges So I fired up my Always Free database (and me being the la ..read more