SQL Riddle: Find the Sum of ASCII Codes of Employee Names (solution)


Zahar Hilkevich (from the blog – https://sqlpatterns.wordpress.com – cool blog, you should check it out) posted a riddle on Facebook.
The question was:

“For every employee find the sum of ASCII codes of all the characters in their names. Write a single SELECT statement only.”

----- ---------- ----------
 7788 SCOTT             397
 7876 ADAMS             358
 7566 JONES             383
 7499 ALLEN             364
 7521 WARD              302
 7934 MILLER            453
 7902 FORD              299
 7369 SMITH             389
 7844 TURNER            480
 7698 BLAKE             351
 7782 CLARK             365
 7654 MARTIN            459
 7839 KING              297
 7900 JAMES             368

Read more

My Upcoming Speaker Events

This is a heads up for some upcoming events I will be appearing on in the next few weeks. I was also accepted (as a standby session) to UKOUG Scotland (which I was at, last year) but unfortunately I will not be able to attend as it collides with some of my other speaking events.

Read more

RMAN no files found to be unknown to the database error

Here’s a weird error message for my collection…

I’ve been working with a customer to setup a RMAN backup on his standby database (backup is going to SBT). After a while, we decided that we couldn’t avoid using the RMAN catalog so we created one.
Now, when using the catalog, we can backup the database from either the primary or standby instances and that will register to a shared catalog so we could restore from either database.


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
1       ORCL     1415951511       PRIMARY          ORCL
1       ORCL     1415951511       STANDBY          ORCLDG

The thing is, that if we change the DB_UNIQUE_NAME of the backup pieces (using RMAN’s CHANGE command), they will now be “owned” by the wrong database server and the files will not be available when running CROSSCHECK command.


change backup piece db_unique_name
backup piece handle=/data/fast_recovery_area/ORCLDG/backupset/2016_05_01/o1_mf_nnndf_TAG20160501T174121_cld5dkrj_.bkp RECID=15 STAMP=910719681
change backup piece db_unique_name
backup piece handle=/data/fast_recovery_area/ORCLDG/autobackup/2016_05_01/o1_mf_s_910715737_cld5dm03_.bkp RECID=16 STAMP=910719682
Changed 2 objects db_unique_name

Read more

Automatic DB Startup: The Linux Part (OEL 6 and 7)

This is part 2 of the automatic startup article. In the previous part, we talked about the basic building blocks for the automatic Oracle database and listener startup. We talked about the orastart and orashut scripts and the /etc/oratab that control which instances are automatically started.

In this part, we will put everything together and see how to configure Linux to use the scripts for automatic start. I will demonstrate two version of Linux here. I used Oracle Enterprise Linux 6 and 7 – which are similar to RedHat Enterprise Linux 6 and 7 (but free). These startup procedures are similar to most of the other distributions including CentOS and Ubuntu.
Read more

Hacking Oracle Data Redaction

Last month Oracle ACE Director Oded Raz published an article about Data Redaction. This month, Oded will explain some of the vulnerabilities of data redaction and how to “hack it”. I would like to thank Oded for his contribution and invite him to publish more things in the future.

On my last article, I have introduced you the new oracle security feature – Data Redaction, selective, on-the-fly redaction of sensitive data in SQL query results prior to display by applications so that unauthorized users cannot view the sensitive data. Although I welcome this feature and think it’s a much-needed addition to Oracle database security features it has some limitations that you need to be aware of before using it to protect sensitive data.

Read more

Automatic Startup for Oracle on Linux

A few years back, I worked with a junior DBA who was asked to create a new instance on a brand new machine. He created the instance using DBCA and everything went smoothly – or so he thought. A few weeks later, after the system became production, a planned maintenance rebooted that Linux server, and once the machine came back up – the database and listener processes were nowhere to be found.

So that junior DBA, stressed because he thought the database was lost called me in the middle of the night. My first sleepy question for him was “did you even start the database?” and his panicked reply was “of course not – it should be done automatically, I used DBCA!”…

Well, one of the things new DBA’s find hard to realize is that after installing and creating a database (even if we’re using DBCA), it will not start automatically. I’ve seen too many new DBA’s being puzzled by the logic behind automatic startup of Oracle databases on Linux (and UNIX in general) than I care to admit so I decided to put it here for their reference…

In this article I will describe the building blocks for automatic startup, and in my next post I will show the Linux commands needed to put everything together.

Read more